Resolved Display picture from SQLite to pictureBox

Kostakis45

Active member
Joined
Apr 3, 2022
Messages
34
Programming Experience
Beginner
Hello everyone.I'm new here so please show some mercy on me.
In my program I store some data to SQLite database in which some data are pictures which in SQLite db are stored as System.Drawing.Bitmap
Picture 1
Now my main problem is that i cannot or know how to load these pictures back to pictureBox's.Bellow is my code so far.Any help and suggestions?

Database Path:
 string path ="Injection_Settings.db";
        string connection = @"URI=file:"+Application.StartupPath+"\\Injection_Settings.db";

Database Creation:
 private void Create_Database()
        {
            if(!System.IO.File.Exists(path))
            {
                SQLiteConnection.CreateFile(path);
                using (var sqlite = new SQLiteConnection(@"Data Source = " + path))
                {
                    sqlite.Open();
                    string dn_table = "CREATE TABLE IF NOT EXISTS Description(Mould_Code INTEGER NOT NULL PRIMARY KEY,Machine_Number INTEGER NOT NULL,Machine_Type TEXT NOT NULL,Supplier TEXT,Colour TEXT NOT NULL,Plastic_Type_1 TEXT,Plastic_Type_2 TEXT,Plastic_Type_3 TEXT,Plastic_Type_1_Val INTEGER,Plastic_Type_2_Val INTEGER,Plastic_Type_3_Val INTEGER,Date_Time TEXT NOT NULL, Item_Name TEXT NOT NULL,Image_1 BLOB,Image_2 BLOB)";
                    SQLiteCommand command = new SQLiteCommand(dn_table,sqlite);
                    command.ExecuteNonQuery();
                }
            }
            else
            {
                if(System.IO.File.Exists(path))
                {
                    using (var sqlite = new SQLiteConnection(@"Data Source = " + path))
                    {
                        sqlite.Open();
                        string dn_table = "SELECT * FROM Description";
                        SQLiteCommand command = new SQLiteCommand(dn_table, sqlite);
                        command.ExecuteNonQuery();

                        if(dn_table == null)
                        {
                            MessageBox.Show("Αδυναμία δημιουργίας βάσης δεδομένων");
                        }
                    }                
                }            
                return;
            }
        }
Data Insertion:
private void AddToDatatable_Click(object sender, EventArgs e)
        {
            var con = new SQLiteConnection(connection);
            con.Open();
            var cmd = new SQLiteCommand(con);

            cmd.CommandText = "INSERT INTO Description (Mould_Code,Machine_Number,Machine_Type,Supplier,Colour,Plastic_Type_1,Plastic_Type_2,Plastic_Type_3,Plastic_Type_1_Val,Plastic_Type_2_Val,Plastic_Type_3_Val,Date_Time,Item_Name,Image_1,Image_2) VALUES (@Mould_Code,@Machine_Number,@Machine_Type,@Supplier,@Colour,@Plastic_Type_1,@Plastic_Type_2,@Plastic_Type_3,@Plastic_Type_1_Val,@Plastic_Type_2_Val,@Plastic_Type_3_Val,@Date_Time,@Item_Name,@Image_1,@Image_2)";

            string Mould_Code = mould_code_input.Text;
            string Machine_Number = machine_number_input.Text;
            string Machine_Type = machine_type_input.Text;
            string Supplier = supplier_input.Text;
            string Colour = colour_input.Text;
            string Plastic_Type_1 = comboBox1.Text;
            string Plastic_Type_2 = comboBox2.Text;
            string Plastic_Type_3 = comboBox3.Text;
            string Plastic_Type_1_Val = numericUpDown1.Text;
            string Plastic_Type_2_Val = numericUpDown2.Text;
            string Plastic_Type_3_Val = numericUpDown3.Text;
            string Date_Time = dateTimePicker1.Text;
            string Item_Name = item_name_input.Text;
            Image Image_1 = pictureBox3.Image;
            Image Image_2 = pictureBox4.Image;

            if (mould_code_input == null
               || machine_number_input == null
               || machine_type_input.Text == null
               || colour_input.Text == null
               || dateTimePicker1.Text == null
               || item_name_input.Text == null)            
            {
                MessageBox.Show("Τα πεδία με * δεν μπορούν να είναι κενά.");            
            }

            cmd.Parameters.AddWithValue("@Mould_Code", Mould_Code);
            cmd.Parameters.AddWithValue("@Machine_Number", Machine_Number);
            cmd.Parameters.AddWithValue("@Machine_Type", Machine_Type);
            cmd.Parameters.AddWithValue("@Supplier", Supplier);
            cmd.Parameters.AddWithValue("@Colour", Colour);        
            cmd.Parameters.AddWithValue("@Plastic_Type_1", Plastic_Type_1);
            cmd.Parameters.AddWithValue("@Plastic_Type_2", Plastic_Type_2);
            cmd.Parameters.AddWithValue("@Plastic_Type_3", Plastic_Type_3);
            cmd.Parameters.AddWithValue("@Plastic_Type_1_Val", Plastic_Type_1_Val);
            cmd.Parameters.AddWithValue("@Plastic_Type_2_Val", Plastic_Type_2_Val);
            cmd.Parameters.AddWithValue("@Plastic_Type_3_Val", Plastic_Type_3_Val);
            cmd.Parameters.AddWithValue("@Date_Time", Date_Time);
            cmd.Parameters.AddWithValue("@Item_Name", Item_Name);
            cmd.Parameters.AddWithValue("@Image_1", Image_1);
            cmd.Parameters.AddWithValue("@Image_2", Image_2);
            cmd.ExecuteNonQuery();
         
            MessageBox.Show("Επιτυχής καταχώρηση στοιχείων");
            con.Close();
        }
Searching and display data when press OK button:
 private void Search_Ok_Click(object sender, EventArgs e)
        {
            tabControl2.Visible = true;
            tabControl1.Visible = false;

            string search_values = Search_Box.Text; // Τιμή απο το Search_Box

            var con = new SQLiteConnection(connection);
            con.Open();
            var cmd = new SQLiteCommand(con);        
            cmd.CommandText = "SELECT * FROM Description WHERE Mould_Code = '" + search_values + "'";        
            SQLiteDataReader reader = cmd.ExecuteReader();
            reader.Read();

            //Περιγραφή
            mould_lbl.Text = reader["Mould_Code"].ToString();
            machine_number_lbl.Text = reader["Machine_Number"].ToString();
            machine_lbl.Text = reader["Machine_Type"].ToString();
            supplier_lbl.Text = reader["Supplier"].ToString();
            colour_lbl.Text = reader["Colour"].ToString();
            plastic1_lbl.Text = reader["Plastic_Type_1"].ToString();
            plastic2_lbl.Text = reader["Plastic_Type_2"].ToString();
            plastic3_lbl.Text = reader["Plastic_Type_3"].ToString();
            plastic_val_1_lbl.Text = reader["Plastic_Type_1_Val"].ToString();
            plastic_val_2_lbl.Text = reader["Plastic_Type_2_Val"].ToString();
            plastic_val_3_lbl.Text = reader["Plastic_Type_3_Val"].ToString();
            date_time_lbl.Text = reader["Date_Time"].ToString();
            item_name_lbl.Text = reader["Item_Name"].ToString();
            pictureBox1.Image = (Bitmap)(new ImageConverter()).ConvertFrom);//Trying to make it work
            //pictureBox2.Image = reader["Image_2"].ToString;

            //Εκχυση

            con.Close();
        }
Searching and display data when press ENTER key:
 private void Search_Box_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                tabControl2.Visible = true;
                tabControl1.Visible = false;

                string search_values = Search_Box.Text; // Τιμή απο το Search_Box

                var con = new SQLiteConnection(connection);
                con.Open();
                var cmd = new SQLiteCommand(con);
                cmd.CommandText = "SELECT * FROM Description WHERE Mould_Code = '" + search_values + "'";
                SQLiteDataReader reader = cmd.ExecuteReader();
                reader.Read();            

                //Περιγραφή
                mould_lbl.Text = reader["Mould_Code"].ToString();
                machine_number_lbl.Text = reader["Machine_Number"].ToString();
                machine_lbl.Text = reader["Machine_Type"].ToString();
                supplier_lbl.Text = reader["Supplier"].ToString();
                colour_lbl.Text = reader["Colour"].ToString();
                plastic1_lbl.Text = reader["Plastic_Type_1"].ToString();
                plastic2_lbl.Text = reader["Plastic_Type_2"].ToString();
                plastic3_lbl.Text = reader["Plastic_Type_3"].ToString();
                plastic_val_1_lbl.Text = reader["Plastic_Type_1_Val"].ToString();
                plastic_val_2_lbl.Text = reader["Plastic_Type_2_Val"].ToString();
                plastic_val_3_lbl.Text = reader["Plastic_Type_3_Val"].ToString();
                date_time_lbl.Text = reader["Date_Time"].ToString();
                item_name_lbl.Text = reader["Item_Name"].ToString();

                //Εκχυση
            }
        }

After reading all the day all kind of Documantations and searching all the web for a solution I found this code:

Convert to Base64 and reverse:
public string ImageToBase64(Image image, System.Drawing.Imaging.ImageFormat format)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                // Convert Image to byte[]
                image.Save(ms, format);
                byte[] imageBytes = ms.ToArray();

                // Convert byte[] to Base64 String
                string base64String = Convert.ToBase64String(imageBytes);
                return base64String;
            }
        }

        public Image Base64ToImage(string base64String)
        {
            // Convert Base64 String to byte[]
            byte[] imageBytes = Convert.FromBase64String(base64String);
            MemoryStream ms = new MemoryStream(imageBytes, 0,
              imageBytes.Length);

            // Convert byte[] to Image
            ms.Write(imageBytes, 0, imageBytes.Length);
            Image image = Image.FromStream(ms, true);
            return image;
        }

But i dunno how to implement it to my needs,because I don't see how to add the image to be converted or how to take the converted image and display it again in another pictureBox.
 
Last edited:

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,467
Location
Sydney, Australia
Programming Experience
10+
If your PictureBox.Image property is null then you want to save DBNull.Value, which is what ADO.NET uses to represent a database NULL. Likewise, if you get DBNull.Value back from the database, you want to set your PictureBox.Image property to null. You could incorporate that functionality into those methods to make using them easier. You could do this:
C#:
private object ConvertImageToBytes(Image image)
{
    if (image == null)
    {
        return DBNull.Value;
    }

    using (var stream = new MemoryStream())
    {
        image.Save(stream, image.RawFormat);

        return stream.ToArray();
    }
}

private Image ConvertBytesToImage(object data)
{
    var bytes = data as byte[];

    if (bytes == null)
    {
        return null;
    }

    using (var stream = new MemoryStream(bytes))
    {
        stream.Position = 0L;

        return Image.FromStream(stream);
    }
}
and then you could do this:
C#:
myCommand.Parameters.Add("@Image", SqlDbType.VarBinary).Value = ConvertImageToBytes(myPictureBox.Image);
and this:
C#:
myPictureBox.Image = ConvertBytesToImage(myDataReader["Image"]);
 

Kostakis45

Active member
Joined
Apr 3, 2022
Messages
34
Programming Experience
Beginner
If your PictureBox.Image property is null then you want to save DBNull.Value, which is what ADO.NET uses to represent a database NULL. Likewise, if you get DBNull.Value back from the database, you want to set your PictureBox.Image property to null. You could incorporate that functionality into those methods to make using them easier. You could do this:
C#:
private object ConvertImageToBytes(Image image)
{
    if (image == null)
    {
        return DBNull.Value;
    }

    using (var stream = new MemoryStream())
    {
        image.Save(stream, image.RawFormat);

        return stream.ToArray();
    }
}

private Image ConvertBytesToImage(object data)
{
    var bytes = data as byte[];

    if (bytes == null)
    {
        return null;
    }

    using (var stream = new MemoryStream(bytes))
    {
        stream.Position = 0L;

        return Image.FromStream(stream);
    }
}
and then you could do this:
C#:
myCommand.Parameters.Add("@Image", SqlDbType.VarBinary).Value = ConvertImageToBytes(myPictureBox.Image);
and this:
C#:
myPictureBox.Image = ConvertBytesToImage(myDataReader["Image"]);
I'll give it a try.Thanks for advise.I was wondering if I could make it 1 line in parameter.Also do you know if it's possible to copy an image from word or pdf or by taking a screenshot and save it to db?
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,467
Location
Sydney, Australia
Programming Experience
10+
Also do you know if it's possible to copy an image from word or pdf or by taking a screenshot and save it to db?
That's a completely separate question so it doesn't belong in this thread.
 
Top Bottom