Wednesday, April 15, 2009

Retreiving Images from Database in SQL Server with ASP.NET using C#

Now in this blog post i explain how to retreive images from the database.

This article is continuation of previous post (where i have mentioned database design of the image table and stuff- refer the previous post).
Open VS 2008, Add an aspx page to existing Website.

1) add a drop down list(of id "DropDownList1") to the webpage, and enable the post back feature.

2) add a button (of id "ViewButton") to the webpage.

3)In Page_Load function/method add the following code to populate the dropdownlist with the list of images available in the database.

SqlConnection sqlcon= new SqlConnection("Data Source=SERVERNAME\\SQLEXPRESS; Initial Catalog= DB_NAME; Integrated Security=SSPI");
string sqltext = "select Img_Id from Image_Gallery";
SqlCommand com = new SqlCommand(sqltext, sqlcon);
sqlcon.Open();
SqlDataReader dr = com.ExecuteReader();
while (dr.Read())
{
DropDownList1.Items.Add(new ListItem(dr.GetInt32(0).ToString(), dr.GetInt32(0).ToString()));
DropDownList2.DataBind();
}
sqlcon.Close();
sqlcon.Dispose();

4) Now add the following code to the event handler of the button.

string ImageId = DropDownList1.SelectedValue.ToString();

//build our query statement
string sqlText = "SELECT Image_Content FROM Image_Gallery WHERE Img_Id ="+ImageId+";";

SqlConnection connection = new SqlConnection("Data Source = SERVERNAME\\SQLEXPRESS; Initial Catalog= DB_NAME; Integrated Security= SSPI");
SqlCommand command = new SqlCommand(sqlText, connection);

//open the database and get a datareader
connection.Open();
SqlDataReader dr = command.ExecuteReader();
if (dr.Read()) //yup we found our image
{
//Response.ContentType = dr["img_contenttype"].ToString();
Response.ContentType = "image";
Response.BinaryWrite((byte[])dr["Image_Content"]);
}
connection.Close();

5) save the page and run the page. You see something similar to this





6) Select the a option from the list of available images and click the button and its done.

No comments:

Post a Comment