Pages

10/27/2010

Image Save/Retrieve C# Windows Form SQL Server

This tutorial will guide you to save or retrieve image from SQL Server in C#.

1. First of all we need a database in SQL Server which contains a table with only two columns one is 'id' of type 'int' and second is of 'image' of type 'image'.

2. Open SQL Server 2005 Express Management Studio and create new database. Name it according to your choice.

3. After successfully creating database create a table with two columns described in step 1.

4. Now Open Visual Studio 2008/2010.

5. Create a project of windows form.

6. In Design View of Form1(you can rename it to your choice) drag a control of picture box to the form with two buttons.

7. Change text of one button to Save and second to Retrieve.

8. Double click Save button to create event for double click.

9. In this event function, create an object of FileStream, a string and byte array. Or you can simply paste the code below.


FileStream fs;


string path = "C:\\Sunset.jpg";


fs = new FileStream(path, FileMode.Open, FileAccess.Read);


//a byte array to read the image


byte[] picbyte = new byte[fs.Length];


fs.Read(picbyte, 0, System.Convert.ToInt32(fs.Length));


fs.Close();

10. Now below this create database connectivity and store this byte array(picbyte) to the image column in the database table you created in starting steps. OR you can use this code.

string cString = "Initial Catalog=DB NAME;Data Source=SQL SERVER INSTANCE;User ID=DB USER;Password=DB USER PASSWORD;";


SqlConnection connection = new SqlConnection(cString);


string query = "insert into TableName (ColumnName) values (@pic)";


connection.Open();
SqlParameter picparameter = new SqlParameter();


picparameter.SqlDbType = SqlDbType.Image;


picparameter.ParameterName = "pic";


picparameter.Value = picbyte;


SqlCommand cmd = new SqlCommand(query, connection);


cmd.Parameters.Add(picparameter);
if (cmd.ExecuteNonQuery() > 0)
MessageBox.Show("Image Saved");
connection.Close();

11. Now the image is saved in the database. You can see this by using query analyzer. Confirm it by the length of image column you will see when you will query it.

12. Now the image is successfully saved, its time to Retrieve it.

13. Drag another picture box to the Designer.

14. Double click the Retrieve button to create the double click event.

15. You can simply use the code below to show the image you saved in the second picture box.

string cString = "Initial Catalog=DatabaseName;Data Source=SQL Instance Name;User ID=DB user;Password=Db User Password;";


SqlConnection connection = new SqlConnection(cString);


SqlCommand command = new SqlCommand("select ColumnName from tableName where ID=1", connection);


connection.Open();


SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();


sqlDataAdapter.SelectCommand = new SqlCommand("select userimage from UserImages where imageID=" + Convert.ToInt32(txtID.Text), connection);


DataSet dSet = new DataSet();


sqlDataAdapter.Fill(dSet);


if (dSet.Tables[0].Rows.Count > 0)
{
MemoryStream ms = new MemoryStream((byte[])dSet.Tables[0].Rows[0]["userimage"]);
pictureBox2.Image = new Bitmap(ms);
}

Note: ID in the command is the id you will see in the database of the image just stored. You can get this id form UI by just giving a textbox to the user.

Now you have successfully saved/retrieved image in the SQL Database using C#.

Any queries then you may ask....


12 comments:

rose said...

i want multiple images show in picture .... like sideshow (or) next button to click that type pls reply to me.........

rose said...

in need window application only sir pls help to me ......kindly request to uuu..........

Unknown said...

By multiple image u mean many image in one image or one image after another..

U can show images in slideshow by using a timer. And the retrieval part will be done in the timer. Try it if need help with the code i will post it.

Unknown said...

This is very useful.....

thanks.....

Anonymous said...

nice work..
great mind...

Sudhir said...

Thanks This good

Sunlad said...

thanks nice piece

Anonymous said...

i receive "Parameter not valid error" when i retrive image like ..
MemoryStream ms = new MemoryStream((byte[])dSet.Tables[0].Rows[0]["userimage"]);
pictureBox2.Image = new Bitmap(ms);

give me suggest

Anonymous said...

Nice

Anonymous said...

I want to do a graphical password authentication project .so how can i show 9 x 9 matrix with images and if any user select wrong images then next time it automatically randomly change and replace.

Unknown said...

is it web based or windows based? also if u can explain a bit more

Anonymous said...

Adding slide view to images in C# Windows Forms

Post a Comment

Twitter Delicious Facebook Digg Favorites More