none
How to read file (stored - blob) from database? RRS feed

  • Question

  • Hi all, 

    I am trying to select a file, then store it to database and then read it again and show it.

    I have done (I think) first part => to store file into database

    But how to read this file from database and show it?

    So my code of how to sotre file into database .. please if it is wrong, tell me, thanks

    string item = String.Empty;
        item = (dtgDocuments.SelectedItem as Documents).path.ToString(); }
        byte[] file;
        using (var stream = new FileStream(item, FileMode.Open, FileAccess.Read))
        {
            using (var reader = new BinaryReader(stream))
            {
                file = reader.ReadBytes((int)stream.Length);
            }
        }
        using (SQLiteConnection con = new SQLiteConnection(conString))
        {
            con.Open();
            using (SQLiteCommand cmd = new SQLiteCommand("INSERT INTO Documents (document) Values(@document)", con))
            {
                cmd.Parameters.AddWithValue("@document", file);
                cmd.ExecuteNonQuery();
            }
        }

    Sunday, August 11, 2019 3:40 PM

Answers

  • Hi TakeshiKitano,

    Thank you for posting here.

    Based on your description, you want to read file from database.

    I used the following code to store a txt file into database and read it from database.

    Code:

         private void Button1_Click(object sender, EventArgs e)
            {
                SQLiteConnection.CreateFile("MyDatabase.sqlite");
    
                SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite");
                m_dbConnection.Open();
    
                string sql = "create table Documents (path varchar(20), document BLOB)";
    
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                command.ExecuteNonQuery();
                string path = "D:\\new.txt";
                byte[] file = null;
                using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read))
                {
                    using (var reader = new BinaryReader(stream))
                    {
                        file = reader.ReadBytes((int)stream.Length);
                    }
                }
    
                using (SQLiteConnection con = new SQLiteConnection("Data Source=MyDatabase.sqlite"))
                {
                    con.Open();
                    using (SQLiteCommand cmd = new SQLiteCommand("INSERT INTO Documents (document) Values(@document)", con))
                    {
                        cmd.Parameters.AddWithValue("@document", file);
                        cmd.ExecuteNonQuery();
                    }
                }
    
                SQLiteCommand sqlCom = new SQLiteCommand("Select * From Documents", m_dbConnection);
    
                SQLiteDataReader sqlDataReader = sqlCom.ExecuteReader();
    
                while (sqlDataReader.Read())
                {
                    string text= System.Text.Encoding.Default.GetString((byte [])sqlDataReader["document"]);
                    MessageBox.Show(text);
    
                }
    
            }

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by TakeshiKitano Wednesday, August 21, 2019 9:42 AM
    Monday, August 12, 2019 9:02 AM
    Moderator