foreach loop does not encode and insert all bitmaps into my sqlite DB

December 20, 2022

My code:

private void BTN_Save_Click(object sender, EventArgs e)
        {
            string DBpath = @"Data Source=.\StudentDB.db;Version=3;";
            Bitmap[] PictureBoxesBitmaps = { FirstPictureBitmap, SecondPictureBitmap, ThirdPictureBitmap };
            using SQLiteConnection connection = new(DBpath);
            using SQLiteCommand cmd = new(DBpath, connection);
            foreach (Bitmap bitmap in PictureBoxesBitmaps)
            {
                System.IO.MemoryStream ms = new();
                bitmap?.Save(ms, ImageFormat.Png);
          
                    byte[] BitmapByteArray = ms.ToArray();
                    var PictureBox64  = Convert.ToBase64String(BitmapByteArray);
                    cmd.CommandText = @"INSERT INTO PictureBoxes(Encoded) VALUES('" + PictureBox64 + "')";
             
            }
            connection.Open();
            cmd.ExecuteNonQuery();
            connection.Close();
}

Only the first bitmap is encoded and inserted into the database.

My intention is to encode all bitmaps present in the PictureBoxexBitmaps array then insert them into the database.

I tried putting connection.Open(); cmd.ExecuteNonQuery(); and connection.Close(); under the foreach loop but that only encoded and inserted the first bitmap three times.

>Solution :

The command is being run only once, To fix the issue you need to move the below line inside the loop.

connection.Open();
cmd.ExecuteNonQuery();
connection.Close();

and as a suggestion use using with the SQLiteConnection and please use the parameterized query to avoid SQL injection.

Edit:

As @madreflection suggested you could only move the cmd.ExecuteNonQuery(); inside the loop to avoid opening/closing the connection again and again. Thank you