Introduction
This article is about storing and retrieving images from database in Microsoft .NET using C#.
Tools Used
- SQL Server 2000
- Microsoft .NET Version 1.1
- C# (Windows Forms based application)
Storing Images
- Create a table in a SQL Server 2000 database which has at least one field of type
IMAGE.
Here is the script I used:
CREATE TABLE [dbo].[tblImgData] (
[ID] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Picture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
- Actually
IMAGE field is just holding the reference to the page containing the binary data so we have to convert our image into bytes.
- I used a file open dialog box to locate the file.
this.openFileDialog1.ShowDialog(this);
string strFn=this.openFileDialog1.FileName;
- By using
FileInfo class, I retrieved the file size: FileInfo fiImage=new FileInfo(strFn);
- Declare an array of that size.
this.m_lImageFileLength=fiImage.Length;
m_barrImg=new byte[Convert.ToInt32(this.m_lImageFileLength)];
- By using
FileStream object, I filled the byte array. FileStream fs=new FileStream(strFn,FileMode.Open,
FileAccess.Read,FileShare.Read);
int iBytesRead=fs.Read(m_barrImg,0,
Convert.ToInt32(this.m_lImageFileLength));
fs.Close();
Complete Load Image Code
protected void LoadImage()
{
try
{
this.openFileDialog1.ShowDialog(this);
string strFn=this.openFileDialog1.FileName;
this.pictureBox1.Image=Image.FromFile(strFn);
FileInfo fiImage=new FileInfo(strFn);
this.m_lImageFileLength=fiImage.Length;
FileStream fs=new FileStream(strFn,FileMode.Open,
FileAccess.Read,FileShare.Read);
m_barrImg=new byte[Convert.ToInt32(this.m_lImageFileLength)];
int iBytesRead = fs.Read(m_barrImg,0,
Convert.ToInt32(this.m_lImageFileLength));
fs.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
- Saving byte array data to database.
- Create command text to insert record.
this.sqlCommand1.CommandText=
"INSERT INTO tblImgData(ID,Name,Picture)" +
" values(@ID,@Name,@Picture)";
- Create parameters.
this.sqlCommand1.Parameters.Add("@ID",
System.Data.SqlDbType.Int, 4);
this.sqlCommand1.Parameters.Add("@Name",
System.Data.SqlDbType.VarChar, 50);
this.sqlCommand1.Parameters.Add("@Picture",
System.Data.SqlDbType.Image);
Notice �@Picture� has �SqlDbType.Image� because it is of IMAGE type Field.
- Provide the value to the parameters.
this.sqlCommand1.Parameters["@ID"].Value=this.editID.Text;
this.sqlCommand1.Parameters["@Name"].Value=this.editName.Text;
this.sqlCommand1.Parameters["@Picture"].Value=this.m_barrImg;
�this.m_barrImg� is a byte array which we filled in the previous step.
- Now execute non-query for saving the record to the database.
int iresult=this.sqlCommand1.ExecuteNonQuery();
Complete Save Image Code
private void btnSave_Click(object sender, System.EventArgs e)
{
try
{
this.sqlConnection1.Open();
if (sqlCommand1.Parameters.Count ==0 )
{
this.sqlCommand1.CommandText="INSERT INTO tblImgData(ID," +
" Name,Picture) values(@ID,@Name,@Picture)";
this.sqlCommand1.Parameters.Add("@ID",
System.Data.SqlDbType.Int,4);
this.sqlCommand1.Parameters.Add("@Name",
System.Data.SqlDbType.VarChar,50);
this.sqlCommand1.Parameters.Add("@Picture",
System.Data.SqlDbType.Image);
}
this.sqlCommand1.Parameters["@ID"].Value=this.editID.Text;
this.sqlCommand1.Parameters["@Name"].Value=this.editName.Text;
this.sqlCommand1.Parameters["@Picture"].Value=this.m_barrImg;
int iresult=this.sqlCommand1.ExecuteNonQuery();
MessageBox.Show(Convert.ToString(iresult));
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
this.sqlConnection1.Close();
}
}
Retrieving Image
Retrieving images from the database is the exact reverse process of saving images to the database.
- First create command text to retrieve record.
SqlCommand cmdSelect = new SqlCommand("select Picture" +
" from tblImgData where ID=@ID",
this.sqlConnection1);
- Create parameter for the query.
cmdSelect.Parameters.Add("@ID",SqlDbType.Int,4);
- Provide value to the parameter.
cmdSelect.Parameters["@ID"].Value=this.editID.Text;
- Open database connection and execute �
ExecuteScalar� because we want only �IMAGE� column data back. byte[] barrImg=(byte[])cmdSelect.ExecuteScalar();
As the execute scalar returns data of �Object� data type, we cast it to byte array.
- Save this data to a temporary file.
string strfn=Convert.ToString(DateTime.Now.ToFileTime());
FileStream fs=new FileStream(strfn,FileMode.CreateNew,FileAccess.Write);
fs.Write(barrImg,0,barrImg.Length);
fs.Flush();
fs.Close();
- And display the image anywhere you want to display.
pictureBox1.Image=Image.FromFile(strfn);
Complete Image Retrieving Code
private void btnLoad_Click(object sender, System.EventArgs e)
{
try
{
SqlCommand cmdSelect=new SqlCommand("select Picture" +
" from tblImgData where ID=@ID",this.sqlConnection1);
cmdSelect.Parameters.Add("@ID",SqlDbType.Int,4);
cmdSelect.Parameters["@ID"].Value=this.editID.Text;
this.sqlConnection1.Open();
byte[] barrImg=(byte[])cmdSelect.ExecuteScalar();
string strfn=Convert.ToString(DateTime.Now.ToFileTime());
FileStream fs=new FileStream(strfn,
FileMode.CreateNew, FileAccess.Write);
fs.Write(barrImg,0,barrImg.Length);
fs.Flush();
fs.Close();
pictureBox1.Image=Image.FromFile(strfn);
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
this.sqlConnection1.Close();
}
}
Bibliography
