Saving Image in Sql Server Database

 Saving Image in Sql Server Database


In this blog I am going to told you that how to save image in a sql server table. For completing this blog I had used Sql Server 2005 and Visual Studio 2010. I had used C# as a programing language.

1) Before we are starting to make application in C# firstly we need to create a table in sql server where we store out image. Here I will create a table named tbl_Image in which I will store image. tbl_Image contains two column one is imageID which will store id of image in image table. And other column which have name imageData is stored image in format of binary.
Here I will provide a sample sql code snippet to create ImageTable
create table tbl_Image
(
imageID int,
imageData image
)

2) Now import System.Data.SqlClient namespace in your project. This namespace provide you all necessary classes and interfaces which is necessary to establish connection to SqlServer.

3) Create following methods and call it according to your requiremnt.

/// <summary>
/// On click event of browse button write down following code. This
/// Will display a open file dialog box which will ask to choose you
/// image and will store path of image in textbox.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private voidbutton1_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = newOpenFileDialog();
if (DialogResult.OK == ofd.ShowDialog())
{
textBox1.Text = ofd.FileName; //storing path of image in textbox.
}
}
/// <summary>
/// On the click of SaveButton call saveImageInDataBase(int imgId) method
/// which will save image in database.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private voidbutton2_Click(object sender, EventArgs e)
{
saveImageInDataBase(1);
}
/// <summary>
/// This is a required method which will save image in database.
/// </summary>
/// <param name="imageID"></param>
public voidsaveImageInDataBase(int imageID)
{
byte[] imageData = ReadImageFile(textBox1.Text); //This nethod returns image in byte array format.
SqlConnection con = newSqlConnection();
con.ConnectionString = "Data Source=aaaa;User ID=sa;Password=abcd;Initial Catalog=Workbook"; //provide connection string of your server.
con.Open(); //open connection to server.
string query = "insert into tbl_Image values(@imageId,@imageData)"; //create a query variable.
SqlCommand cmd = newSqlCommand(query, con); //create a sqlcommand object.
cmd.Parameters.Add(new SqlParameter("@imageId", imageID)); //add first parameters.
cmd.Parameters.Add(new SqlParameter("@imageData", imageData)); //add second parameters.
int rows = cmd.ExecuteNonQuery(); //execute query.
if (rows > 0)
MessageBox.Show("Image saved.");
else
MessageBox.Show("Unable to save image.");
con.Close();
}
/// <summary>
/// This method will converts image in byte array format and returns to its caller.
/// use System.IO namespace regarding streaming concept.
/// </summary>
/// <param name="imageLocation"></param>
/// <returns></returns>
public byte[] ReadImageFile(string imageLocation)
{
byte[] imageData = null;
FileInfo fileInfo = newFileInfo(imageLocation);
long imageFileLength = fileInfo.Length;
FileStream fs = newFileStream(imageLocation, FileMode.Open, FileAccess.Read);
BinaryReader br = newBinaryReader(fs);
imageData = br.ReadBytes((int)imageFileLength);
return imageData;
}
}
By Implementing above code you can successfully insert an image in a database.