In this article, I discuss a simple application to store and retrieve images in Sql Server Database using Asp.NET 2.0.
The following are what we use in this project:
Follow the steps given below:
First create a new website either with Visual Studio.Net 2005 or Visual Web Develoer Express Edition 2005 using File -> New Website. Give any name you like to this new project.
Connect to MSDB database in Sql Server 2005 Express edition using Database Explorer/Server Explorer. You can use any other database or standard/enterprise edition. In this case change connection string accordingly.
create table Persons( name varchar(30) primary key, photo image)
Add a new ASP.NET page with name AddPerson.aspx and select the language as C#.
The following is the source code of Addperson.aspx.
<%@ Page Language="C#" %> <%@ Import Namespace="System.Data.SqlClient"%> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.IO "%> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server"> protected void Button1_Click(object sender, EventArgs e) { Stream imagestream; int len = FileUpload1.PostedFile.ContentLength; // get length of the file imagestream = FileUpload1.PostedFile.InputStream; // get stream for the image Byte [] imagecontent = new Byte[len]; // create an array of bytes to hold image data imagestream.Read(imagecontent, 0, len); // read image into array SqlConnection con = new SqlConnection("Data Source=localhost\\sqlexpress;Initial Catalog=msdb;Integrated Security=True"); con.Open(); SqlCommand cmd = new SqlCommand("insert into persons values (@name,@photo)",con); cmd.Parameters.Add("@name", SqlDbType.VarChar, 20).Value = TextBox1.Text; cmd.Parameters.Add("@photo", SqlDbType.Image).Value = imagecontent; cmd.ExecuteNonQuery(); // insert con.Close(); Response.Write("Person Added Successfully"); } </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Add Person</title> </head> <body> <h2>Add Person</h2> <form id="form1" runat="server"> <div> <table> <tr> <td>Person Name </td> <td> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> </td> </tr> <tr> <td>Person's Photo </td> <td> <asp:FileUpload ID="FileUpload1" runat="server" /> </td> </tr> </table> <p /> <asp:Button ID="Button1" runat="server" Text="Add Person" OnClick="Button1_Click" /> <p /> <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="persons.aspx">Show Persons</asp:HyperLink> </div> </form> </body> </html>
When your click on link Show Persons then it will display name and photo of each person stored in PERSONS table using persons.aspx. So add persons.aspx file to your project. This page contains SqlDataSource and GridView controls.
Persons.aspx uses SQLDataSource control to retrieve data from PERSONS table. The data is displayed using GridView with a simple bound column and a template column. Template column contains itemtemplate, which contains Image web control of ASP.NET.
The following is the code for persons.aspx.
<%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server"> String GetUrl(Object name) { return "getphoto.aspx?name=" + name.ToString(); } protected void GridView1_SelectedIndexChanged(object sender, EventArgs e) { } </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <h2>List Of Persons </h2> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1" CellPadding="5" CellSpacing="1" OnSelectedIndexChanged="GridView1_SelectedIndexChanged"> <Columns> <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" /> <asp:TemplateField HeaderText="Photo"> <ItemTemplate> <asp:Image ImageUrl='<%# GetUrl(Eval("name"))%>' runat="server" Width="100" Height="100"/> </ItemTemplate> </asp:TemplateField> </Columns> <HeaderStyle BackColor="#FF8080" /> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:msdbConnectionString %>" SelectCommand="SELECT [name], [photo] FROM [persons]"></asp:SqlDataSource> </form> </body> </html>
<%@ Page Language="C#" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.SqlClient" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server"> protected void Page_Load(object sender, EventArgs e) { String name = Request.QueryString["name"]; SqlConnection myConnection = new SqlConnection("Data Source=localhost\\sqlexpress;Initial Catalog=msdb;Integrated Security=True"); myConnection.Open(); SqlCommand myCommand = new SqlCommand("select photo from persons where name ='" + name + "'", myConnection); SqlDataReader myDataReader; myDataReader = myCommand.ExecuteReader(); myDataReader.Read(); // goto first row Response.BinaryWrite( (byte[]) myDataReader["photo"]); myConnection.Close(); } </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> </div> </form> </body> </html>