none
c# sqlite blob RRS feed

  • Question

  • Hi everyone, I am stucked on how to retrieve image (blob) in sqlite db. I retrieve it as byte array but when I convert it into image using memoryStream then ->imageFromStream, it gave me "Parameter is not valid".
    Saturday, May 25, 2013 3:56 AM

Answers

  • That is the expected behavior, there is nothing to read after write, as the cursor is placed at the end of the stream. If you want to read/write from start, reset the cursor.

    I am not sure if SQLite has something like SqlFileStream, but if not, it is not hard to write one that takes a reader as input.



    Visual C++ MVP


    Saturday, May 25, 2013 5:27 PM

All replies

  • You can just implement IStream in your own custom object that reads the bytes from SqliteDataReader. There is no need to get the whole file into memory as a byte[]. 


    Visual C++ MVP

    Saturday, May 25, 2013 3:06 PM
  • **How can I retrieve image blob from db then put into picturebox?  Here is my code:**        
    `public byte[] GetImageFromDB(string name){  
    SQLiteCommand cmd;  
    SQLiteDataAdapter adptr = new SQLiteDataAdapter();  
    SQLiteConnection con = new SQLiteConnection("Data Source=database.db;Version=3;New=False;Compress=True");`    con.Open();
                cmd = con.CreateCommand();
                string sql = "SELECT img FROM images where name ='" + name + "' ";
                cmd.CommandText = sql;
                adptr.SelectCommand = cmd;
                cmd.ExecuteNonQuery();
                
                byte[] buffer = null;
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        buffer = GetBytes(reader);
                    } 
                    con.Close();
                    return buffer;
                    
                } 
            }  
            static byte[] GetBytes(SQLiteDataReader reader)
            {
                const int CHUNK_SIZE = 2 * 1024;
                byte[] buffer = new byte[CHUNK_SIZE];
                long bytesRead;
                long fieldOffset = 0;
                using (MemoryStream stream = new MemoryStream())
                {
                    while ((bytesRead = reader.GetBytes(0, fieldOffset, buffer, 0, buffer.Length)) > 0)
                    {
                        stream.Write(buffer, 0, (int)bytesRead);
                        fieldOffset += bytesRead;
                    }
                    return stream.ToArray();
                }
            }   

       **Here is my code in forms:**  
    `byte[] b = db.GetImageFromDB(treeView1.GetNodeAt(e.X, e.Y).Text);
                            imgForm(treeView1.GetNodeAt(e.X, e.Y).Text, b, Mode.edit, true);`  
    **another method**  
    `private void imgForm(string name, byte[] b, Mode mode, bool tv)  
    {  
    ImgForm editForm = new ImgForm("");  
    editForm.passInfo(name, b, mode);  
    editForm.edit = true;  
    editForm.ShowDialog();  
    }`  
    **Another form**  
    `public void passInfo(string name, byte[] b, Mode mode)  
    {  
    txtName.Text =name;  
    pictureBox1.Image = ByteToImage(b);  
    }`  
    **another method**  
    `public Image ByteToImage(byte[] b)  
    {  
    MemoryStream ms = new MemoryStream(b);  
    ms.Position = 0;  
    ms.Write(b, 0, b.Length);  
    Image image = Image.FromStream(ms, true);  
    return image;  
    }`  
    **But at the end, it gave an error "The parameter is not valid"**              
    Saturday, May 25, 2013 3:09 PM
  • That is the expected behavior, there is nothing to read after write, as the cursor is placed at the end of the stream. If you want to read/write from start, reset the cursor.

    I am not sure if SQLite has something like SqlFileStream, but if not, it is not hard to write one that takes a reader as input.



    Visual C++ MVP


    Saturday, May 25, 2013 5:27 PM
  • If you don't mind, could you help with the code you have said on how to reset the cursor? What do you mean "nothing to read after write"? See this code as I insert the blob:

            public void addImage(string path,string name)
            {
                byte[] image = GetImageFromFile(path);
                SQLiteCommand cmd;
                SQLiteDataAdapter adptr = new SQLiteDataAdapter(); ;
                SQLiteConnection con = new SQLiteConnection("Data Source=database.db;Version=3;New=False;Compress=True");
                con.Open();
                cmd = con.CreateCommand();
                string sql1 = "INSERT INTO parent(name, imgbool) VALUES ('"+name+"', 'true');";
                cmd.CommandText = sql1;
                cmd.ExecuteNonQuery();
                string sql = "INSERT INTO images(name, img) VALUES ('" + name + "', '" + image + "');";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                con.Close();
            }
            public static byte[] GetImageFromFile(string filePath)
            {
                FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                BinaryReader br = new BinaryReader(fs);

                byte[] photo = br.ReadBytes((int)fs.Length);

                br.Close();
                fs.Close();

                return photo;
            }

    Monday, May 27, 2013 2:40 AM
  • MemoryStream ms = new MemoryStream(b);  
    ms.Position = 0;  
    ms.Write(b, 0, b.Length);  

    //should change the cursor position here
    Image image = Image.FromStream(ms, true);  //read past stream end



    Visual C++ MVP

    Monday, May 27, 2013 4:01 AM
  • but how can I change the cursor position.  I could not make it.
    Monday, May 27, 2013 3:10 PM
  • So what does the ms.Position = 0 do in your code?


    Visual C++ MVP

    Tuesday, May 28, 2013 3:11 PM