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

Question

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.

please use ***parameterised queries*** - building SQL queries by concatenation etc. is a recipe for disaster. not only is it a source for many hard to debug syntax errors, it's also a wide, open gate for ***[SQL Injection attacks](https://bobby-tables.com/)***. — Franz Gleichmann, Dec 20 '22 at 15:31
also: you're only _executing_ your query ***once*** because it's outside of the loop. — Franz Gleichmann, Dec 20 '22 at 15:32
To move `cmd.ExecuteNonQuery();` INSIDE the loop would be a good idea. — Roman Ryzhiy, Dec 20 '22 at 15:32
@FranzGleichmann wow, there really is a xkcd for everything. Thanks for pointing it out — Ahmed Sherif, Dec 20 '22 at 15:41

Vivek Nuna · Accepted Answer · 2022-12-20T15:51:52.483

0

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

edited Dec 20 '22 at 15:51

answered Dec 20 '22 at 15:38

Vivek Nuna

25,472
25
109
197

3

Only `cmd.ExecuteNonQuery();` needs to be inside the loop. Opening and closing the connection on every iteration is costly and totally unnecessary. – madreflection Dec 20 '22 at 15:49

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

1 Answers1