C#.NET Save and Retrieve Image From Database. ( Part 4 – Save image data in Database)

This is the fourth part of this tutorial series. In this part I will show you how to save image in database. In third part of this tutorial I have shown you how to gather connectionString from app.config file.  We will use that function here. So lets began.

First we will add a messageBox with confirmation message. We will give Yes/No option for the user. If the user select No then rest of the code will not be executed. 

if (MessageBox.Show("Do you want to save this image to database?", "Confirmation") == DialogResult.No)

 { return; }

We will put rest of the code in Try/Catch. First we have to declare a new connection and assign connectionString for that connection. Then open the connectin and declare a new command. Set connection that we declared earlier for this command.

string connString = GenerateString();

SqlConnection Conn = new SqlConnection(connString);

                if (Conn.State == ConnectionState.Closed)

                {

                    Conn.Open();

                }

                SqlCommand cmd = new SqlCommand();

                cmd.Connection = Conn;

We can’t insert image directly in the sql database. We have to convert the image in bytes arrey. And the insert the bytes arrey into sql table. But you can’t add the bytes array veriable directly in sql command text. In this case you have to use parameter. Finally close the connection.

byte[] img = null;

                FileStream fs = new FileStream(@txtImagePath.Text, FileMode.Open, FileAccess.Read);

                BinaryReader br = new BinaryReader(fs);

                img = br.ReadBytes((int)fs.Length);

                string sql = "INSERT INTO SavedImages(ImageName,ImageData, OriginalPath) VALUES('"+ txtFileName.Text +"',@IMG,'" + txtImagePath.Text + "')";

             

                cmd.CommandText = sql;

                cmd.Parameters.Add(new SqlParameter("@IMG", img));

                cmd.ExecuteNonQuery();

                Conn.Close();

Note : To use FileStream you must  have to use System.IO; namespace.  using System.IO;

Now we have to refresh image Names listbox. We will use a function for this purpose. We will create a function named LoadImageNames() . We will use function because we have to refresh the image name listBox several times.

Here is the function.

private void LoadImageNames()

        {

            try

            {

                string connString = GenerateString();

                SqlConnection Conn = new SqlConnection(connString);

                if (Conn.State == ConnectionState.Closed)

                {

                    Conn.Open();

                }

                SqlCommand cmd = new SqlCommand();

                cmd.Connection = Conn;

                Cursor = Cursors.WaitCursor;

                listImageName.Items.Clear();

                cmd.CommandText = "SELECT ImageName FROM SAVEDIMAGES ORDER BY ImageName";

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())

                {

                    listImageName.Items.Add(reader[0].ToString());

                }

                reader.Close();

                Conn.Close();

                Cursor = Cursors.Default;

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message); Cursor = Cursors.Default;

            }

        }

Finally here’s the full code for the Save button click event.

private void btnSave_Click(object sender, EventArgs e)

        {

            string connString = GenerateString();

            if (MessageBox.Show("Do you want to save this image to database?", "Confirmation") == DialogResult.No)

                return;

           

            try

            {

                SqlConnection Conn = new SqlConnection(connString);

                if (Conn.State == ConnectionState.Closed)

                {

                    Conn.Open();

                }

                SqlCommand cmd = new SqlCommand();

                cmd.Connection = Conn;

                Cursor = Cursors.WaitCursor;

                byte[] img = null;

                FileStream fs = new FileStream(@txtImagePath.Text, FileMode.Open, FileAccess.Read);

                BinaryReader br = new BinaryReader(fs);

                img = br.ReadBytes((int)fs.Length);

                string sql = "INSERT INTO SavedImages(ImageName,ImageData, OriginalPath) VALUES('"+ txtFileName.Text +"',@IMG,'" + txtImagePath.Text + "')";

                cmd.CommandText = sql;

                cmd.Parameters.Add(new SqlParameter("@IMG", img));

                cmd.ExecuteNonQuery();

                Conn.Close();

                Cursor = Cursors.Default;

                MessageBox.Show("Image saved to database.", "Saved!");

                LoadImageNames();

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message); Cursor = Cursors.Default;

            }

        }