Insert Image into SQL Server using C#
OpenFileDialog for browse the Image and showing in picture box and Convert image into Byte using MemoryStream in GetImage method.
GetImage method has a return value as btye array.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
private byte[] GetImage() { string sFile; OpenFileDialog openFileDialog1 = new OpenFileDialog(); byte[] imgByte = null; openFileDialog1.Filter = "Image File (*.jpg;*.bmp;*.gif)|*.jpg;*.bmp;*.gif"; if (openFileDialog1.ShowDialog() == DialogResult.OK) { sFile = openFileDialog1.FileName; pictureBox1.Image = System.Drawing.Bitmap.FromFile(sFile); pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage; using (MemoryStream mStream = new MemoryStream()) { pictureBox1.Image.Save(mStream, pictureBox1.Image.RawFormat); imgByte = mStream.ToArray(); } } return imgByte; } |
Open SQL Connection and write an Insert statement for SQL query. After Store image as Binary value using SQLParameter.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
private void button1_Click(object sender, EventArgs e) { byte[] imgByte; string connetionString = null; SqlConnection cnn; //Windows Auth connetionString = "Server = localhost; Database = Images; Integrated Security = SSPI; "; cnn = new SqlConnection(connetionString); try { imgByte = GetImage();// get image as bytearray cnn.Open(); String query = "INSERT INTO Picture VALUES(@name,@img)"; SqlCommand command = new SqlCommand(query, cnn); command.Parameters.AddWithValue("@name", "new Name"); command.Parameters.AddWithValue("@img", imgByte); command.ExecuteNonQuery(); cnn.Close(); } catch (Exception ex) { MessageBox.Show("Can not open connection ! "); } } |
Get Image from database using C#
Retrieve Image from database and set in Picture box and Using select command for retrieving data from database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
private void button2_Click(object sender, EventArgs e) { string connetionString = null; SqlConnection cnn; //Windows Auth connetionString = "Server = localhost; Database = Images; Integrated Security = SSPI; "; cnn = new SqlConnection(connetionString); try { cnn.Open(); String query = "SELECT TOP 1 * FROM Picture ORDER BY NEWID()"; // Get a random image. SqlCommand command = new SqlCommand(query, cnn); //command.Parameters.AddWithValue("@id", 4); // if you get by id, you can use this block. SqlDataReader dr = command.ExecuteReader(); dr.Read(); if (!DBNull.Value.Equals(dr[2])) //2 is database column number { byte[] imageBtye = (byte[])dr[2]; MemoryStream ms = new MemoryStream(imageBtye); pictureBox1.Image = new Bitmap(ms); } else { MessageBox.Show("NULL Data"); } cnn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); ; } } |
