This
tutorial will show you how to store and retrieve image from database. I have
used FileUpload control to upload image from our computer and it’ll be stored
to database as binary format.
First we have to create a table with
three fields, ImageID, ImageName and Image. We need to store Image as binary
format so we have to use varbinary() datatype.
Store Image:
Once we created our table then we have
to design our ASPX page. First we have add TextBox control for image name,
FileUpload control for image and Button to initiate upload.
<div>
<asp:TextBox ID="txtImageName"
runat="server">
</asp:TextBox>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnSubmit" runat="server" Text="Submit"
OnClick="btnSubmit_Click"
/>
</div>
Connection string:
Here
we have to add connection string to the web.config file
<connectionStrings>
<add name="myConnectionString" connectionString="Data
Source=localhost; Initial Catalog=Demo; User Id=sa;Password=sa123"/>
</connectionStrings>
On button click event we have to write
following code to store image to database. Here we are going to convert image
to binary format and it’ll be stored along with Image name. You will see that BinaryReader receives filestream to
convert Image as binary data and it’ll be stored to byte array.
protected void btnSubmit_Click(object sender, EventArgs
e)
{
try {
Byte[] bytes = null;
if (FileUpload1.HasFile)
{
string filename = FileUpload1.PostedFile.FileName;
string filePath = Path.GetFileName(filename);
Stream fs = FileUpload1.PostedFile.InputStream;
BinaryReader br = new
BinaryReader(fs);
bytes = br.ReadBytes((Int32)fs.Length);
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(
"INSERT INTO tblImage (ImageName,
Image) " +
"Values(@ImageName, @Image)",
connection);
command.Parameters.Add("@ImageName",
SqlDbType.NVarChar, 20).Value = txtImageName.Text;
command.Parameters.Add("@Image",
SqlDbType.Binary).Value = bytes;
connection.Open();
command.ExecuteNonQuery();
}
}
catch (Exception)
{ //error }
}
Retrieve Image:
We have Image that has been stored to
database as binary format. Now we are going to retrieve Image from database and
we will store that to Image control. For that we have to add Image Control to
our .aspx page and we have to create a Handler that will return Image from
query string.
Image
control look like this
<asp:Image ID="imgPreview"
ImageUrl="~/ImageHandler.ashx?imgID=2"
runat="server"
/>
Create Handler:
Right click root folder -> Add New
Item and add Generic Handler(ashx).
Now we have Handler file containing "ProcessRequest(HttpContext
context)" method which gets triggered automatically.
public void ProcessRequest(HttpContext
context)
{
SqlDataReader rdr = null;
SqlConnection
conn = null;
SqlCommand
selcmd = null;
try
{
conn = new
SqlConnection (ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
selcmd = new SqlCommand("select Image from tblImage where ImageID="
+ context.Request.QueryString["imgID"],
conn);
conn.Open();
rdr = selcmd.ExecuteReader();
while
(rdr.Read())
{
context.Response.ContentType = "image/jpg";
context.Response.BinaryWrite((byte[])rdr["Image"]);
}
rdr.Close();
}
finally
{
conn.Close();
}
}
I
hope you understand how to store and retrieve image from database. You can
download source code which is available along with this article.
Happy
coding.. J