4:05 PM
Unknown
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....
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....
Posted in
