Thursday, September 23, 2010

Save and display images to and from SQL Database C#

Save images on the Database
Let say, I've a fileupload control to browse images and a button to upload images on SQL database.

<asp:FileUpload ID="FileUpload1" runat="server"/>

<asp:Button ID="btnSubmit" runat="server" Text="Submit"
onclick="btnSubmit_Click"/>


write this code inside the button click event:

protected void btnSubmit_Click(object sender, EventArgs e)
{
//to store image into sql database.
if (FileUpload1.PostedFile != null &&
FileUpload1.PostedFile.FileName != "")
{
byte[] imageSize = new byte[FileUpload1.PostedFile.ContentLength];
HttpPostedFile uploadedImage = FileUpload1.PostedFile;
uploadedImage.InputStream.Read(imageSize, 0, (int)FileUpload1.PostedFile.ContentLength);


// Create SQL Command
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO Pictures(ImageName,Image)" +
" VALUES (@ImageName,@Image)"
;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;

SqlParameter ImageName = new SqlParameter
("@ImageName", SqlDbType.VarChar, 50);
ImageName.Value = strImageName.ToString();
cmd.Parameters.Add(ImageName);

SqlParameter UploadedImage = new SqlParameter("@Image", SqlDbType.Image, imageSize.Length);
UploadedImage.Value = imageSize;
cmd.Parameters.Add(UploadedImage);
conn.Open();
int result = cmd.ExecuteNonQuery();
conn.Close();
if (result > 0)
lblMessage.Text = "File Uploaded";
lblSuccess.Text = "Successful !";

}
}

Display the images from database:
Let's display the image on Gridview
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1"
AutoGenerateColumns="False" DataKeyNames="ID" Width="245px">
<Columns>
<asp:BoundField DataField="TreatmentID" HeaderText="ID" Visible="false"
SortExpression="TreatmentID" />
<asp:BoundField DataField="imageName" HeaderText="ImageName"
SortExpression="imageName" />
<asp:TemplateField HeaderText="Image">
<ItemTemplate>
<asp:Image ID="Image1" runat="server"
ImageUrl='<%#"Handler.ashx?ID=" + Eval("ID")%>'/>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TreatmentConnectionString %>"
SelectCommand="SELECT * FROM [Pictures]">

and add a handler class with the below code:


using System;
using System.Web;
using System.Data.SqlClient;

public class Handler : IHttpHandler {

public void ProcessRequest (HttpContext context)
{

try
{
SqlConnection con = new SqlConnection(GetConnectionString());

// Create SQL Command
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Select ID,imageName, Image from Pictures" +
" where ID =@ID";

cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;

SqlParameter ImageID = new SqlParameter
("@ID", System.Data.SqlDbType.Int);
ImageID.Value = context.Request.QueryString["ID"];
cmd.Parameters.Add(ImageID);
con.Open();
SqlDataReader dReader = cmd.ExecuteReader();
dReader.Read();
context.Response.BinaryWrite((byte[])dReader["Image"]);
dReader.Close();
con.Close();
}

catch(Exception ex)
{
ex.Message.ToString();
}
}

public bool IsReusable {
get {
return false;
}
}

}