Retrieving and Showing an Image from a Database

only for RuBoard

In this example, the image is created from the Image field in the Images table from the Northwind database on-the-fly and once again is never saved to hard disk. Then, the image is displayed using the Image server control. We will use one Web Form to create and implement an image, and another to display it. Listing 13.6 contains the image Web Form, which will retrieve the image from the database and create a Web-usable image.

Listing 13.6 Web Form Used to Create the Image from a Database for Web Use

 [VisualBasic.NET] 01: <%@ OutputCache Duration="30" VaryByParam="ImageID" %> 02: <%@ Import Namespace="System.Data" %> 03: <%@ Import Namespace="System.Data.SqlClient" %> 04: 05: <script language="vb" runat="server"> 06: 07: protected sub Page_Init(sender as Object, e as EventArgs) 08: 09:  dim ImageID as string = Request.QueryString("ImageID") 10: 11:  if not ImageID = nothing then 12: 13:  dim SqlCon as new SqlConnection("server=localhost;uid=sa;pwd=;database=northwind") 14:  dim SqlCmd as new StringBuilder() 15:   SqlCmd.Append("SELECT Image, ContentType, ByteSize ") 16:   SqlCmd.Append("FROM Images WHERE ImageID = @ImageID") 17: 18:  dim sqlcommand as new SqlCommand(SqlCmd.ToString(), SqlCon) 19:   sqlcommand.Parameters.Add(new SqlParameter("@ImageID", SqlDbType.Int)) 20:   sqlcommand.Parameters("@ImageID").Value = ImageID 21:  SqlCon.Open() 22:  dim SqlDr as SqlDataReader =  sqlcommand.ExecuteReader(CommandBehavior.CloseConnection) 23: 24:  SqlDr.Read() 25: 26:  Response.ContentType = SqlDr("ContentType") 27: 28:  Response.OutputStream.Write(SqlDr("Image"), 0, SqlDr("ByteSize")) 29: 30:  Response.End() 31: 32:  end if 33: 34: end sub 35: 36: </script> [C#.NET] 01: <%@ OutputCache Duration="30" VaryByParam="ImageID" %> 02: <%@ Import Namespace="System.Data" %> 03: <%@ Import Namespace="System.Data.SqlClient" %> 04: 05: <script language="c#" runat="server"> 06: 07: protected void Page_Init(Object sender, EventArgs e) { 08: 09:  string ImageID = Request.QueryString["ImageID"]; 10: 11:  if (ImageID != null) { 12: 13:  SqlConnection SqlCon = new  SqlConnection("server=localhost;uid=sa;pwd=;database=northwind"); 14:  StringBuilder SqlCmd = new StringBuilder(); 15:   SqlCmd.Append("SELECT Image, ContentType, ByteSize "); 16:   SqlCmd.Append("FROM Images WHERE ImageID = @ImageID"); 17: 18:  SqlCommand sqlcommand = new SqlCommand(SqlCmd.ToString(), SqlCon); 19:   sqlcommand.Parameters.Add(new SqlParameter("@ImageID", SqlDbType.Int)); 20:   sqlcommand.Parameters["@ImageID"].Value = ImageID; 21:  SqlCon.Open(); 22:  SqlDataReader SqlDr = sqlcommand.ExecuteReader(CommandBehavior.CloseConnection); 23: 24:  SqlDr.Read(); 25: 26:  Response.ContentType = (string)SqlDr["ContentType"]; 27: 28:  Response.OutputStream.Write((byte[])SqlDr["Image"], 0, (int)SqlDr["ByteSize"]); 29: 30:  Response.End(); 31: 32:  } 33: 34: } 35: 36: </script> 

In Listing 13.6, you will notice I enable output caching using the @OuputCache directive. The @OuputCache directive VaryByParam attribute varies caching based on the value of the parameter specified (ImageID); so for every ImageID sent to this page as a parameter, a separate cache item will be made. Hence subsequent calls for the same image will be served from the cache rather than dynamically generating them each time. (I will dive deep into caching in Chapter 16, "Data Caching.")

On line 9 I create an ImageID variable, which determines which image from the database will be shown. Its value is determined by the query string ImageID parameters value. On line 11 I use an if statement to verify that there is an ImageID value present. If it doesn't have a value then code execution stops and no image will be shown. On a production site this would probably not be an option so in this case there should be a mechanism in place for recovery such as a default ImageID value.

If the ImageID value is present, then the code to generate the image is executed (lines 13 through 32). I create a new connection, construct the SQL statement, and execute a query to retrieve the Image , ContentType , and ByteSize fields for the ImageID specified (lines 13 “22). You might be wondering why I chose to use an SqlDataReader rather than a DataSet to retrieve the data. Well, there is a good reason for this: PERFORMANCE. Remember, the SqlDataReader and OleDbDataReader objects read a data stream directly from the database connection. And since we are working with rather large objects we will use less memory and CPU time by streaming the data in.

On line 26 I set the pages ContentType equal to the ContentType field in the database. Next, on line 28, I write the images binary data to the response stream by using the HttpResponse.OutputStream . There are 3 paramaters passed into the Write method ”first is the buffer (the contents of the Image field) which is cast into a byte array. Second is the offset ” where to begin copying bytes into the stream, in this case the very beginning - . Finally, count ”the number of bytes from the buffer that should be written to the stream we use the ByteSize field as this value.

Even though this page will execute by itself and will show an image (if you hardcode the ImageID value), you couldn't write out any other HTML content because the ContentType of the page will be that of an image.

Let's now consume or use this "image utility" page from another page to illustrate how multiple images can be derived from a database. Listing 13.7 shows how to use the Listing13.6.aspx page to show images.

Listing 13.7 Web Form Client That Uses the Images.aspx page to Show Images.

 [VisualBasic.NET] 01: <%@ Import Namespace="System.Data" %> 02: <%@ Import Namespace="System.Data.SqlClient" %> 03: 04: <script language="vb" runat="server"> 05: 06: public sub Page_Load(sender as Object, e as EventArgs) 07: 08:  dim Url as string = "Listing13.6.aspx?ImageID=" 09:  dim sdr as SqlDataReader 10:  dim sCon as new SqlConnection("server=localhost; uid=sa;pwd=;database=northwind") 11:  dim SqlCmd as string = "SELECT ImageID,ImageDescription FROM Images" 12:  dim sCmd as new SqlCommand(SqlCmd,sCon) 13:  sCon.Open() 14:  sdr = sCmd.ExecuteReader() 15: 16:  dim row as TableRow 17:  dim cell as TableCell 18:  dim ICtrl as Image 19: 20:  while (sdr.Read()) 21: 22:    row = new TableRow() 23:    cell = new TableCell() 24:    cell.Controls.Add(new LiteralControl("<b>" & sdr.GetString(1) & "</b>")) 25:    cell.VerticalAlign = VerticalAlign.Top 26:    cell.Width = Unit.Pixel(400) 27:    row.Cells.Add(cell) 28: 29:    cell = new TableCell() 30:    ICtrl = new Image() 31:    ICtrl.ImageUrl = Url & sdr.GetSqlInt32(0).ToString() 32:    ICtrl.Width = Unit.Pixel(200) 33:    ICtrl.AlternateText = sdr.GetString(1) 34:    cell.Controls.Add(ICtrl) 35:    row.Cells.Add(cell) 36: 37:    ImageTable.Rows.Add(row) 38: 39:   end while 40: 41: end sub 42: </script> [C#.NET] 01: <%@ Import Namespace="System.Data" %> 02: <%@ Import Namespace="System.Data.SqlClient" %> 03: 04: <script language="c#" runat="server" > 05: 06: void Page_Load(Object sender, EventArgs e) { 07: 08:  string Url = "Listing13.6.aspx?ImageID="; 09:  SqlDataReader sdr; 10:  SqlConnection sCon = new SqlConnection("server=localhost;  uid=sa;pwd=;database=northwind"); 11:  string SqlCmd = "SELECT ImageID,ImageDescription FROM Images"; 12:  SqlCommand sCmd = new SqlCommand(SqlCmd,sCon); 13:  sCon.Open(); 14:  sdr = sCmd.ExecuteReader(); 15: 16:  Image ICtrl; 17:  TableRow row; 18:  TableCell cell; 19: 20:  while (sdr.Read()) { 21: 22:    row = new TableRow(); 23:    cell = new TableCell(); 24:    cell.Controls.Add(new LiteralControl("<b>" + sdr.GetString(1) + "</b>")); 25:    cell.VerticalAlign = VerticalAlign.Top; 26:    cell.Width = Unit.Pixel(400); 27:    row.Cells.Add(cell); 28: 29:    cell = new TableCell(); 30:    ICtrl = new Image(); 31:    ICtrl.ImageUrl = Url + sdr.GetSqlInt32(0).ToString(); 32:    ICtrl.Width = Unit.Pixel(200); 33:    ICtrl.AlternateText = sdr.GetString(1); 34:    cell.Controls.Add(ICtrl); 35:    row.Cells.Add(cell); 36: 37:    ImageTable.Rows.Add(row); 38: 39:   } 40: 41: } 42: </script> [VisualBasic.NET & C#.NET] 44: <html> 45: <body> 46:  <asp:Table id="ImageTable" runat="server" cellpadding="4" 47:   cellspacing="4" width="600" align="left" /> 48: </body> 49: </html> 

To display the image with the Images.aspx Web form (the image generator), use the following code

 <img src="Listing13.6.aspx?ImageID=  ImageID  > 

where ImageID is equal to the ImageID field in the Images table of the database. I wanted to show every image in the Images table in Listing 13.7, so you first retrieve all the records and use a DataReader to work with them (lines 8 through 14). The fields I am returning in the SQL query are ImageDescription and ImageID . In lines 16 through 18, you create the building blocks for the Table and Image controls. Then, starting on line 20, I loop through the result set dynamically constructing the Table that will contain the images and descriptions.

The dynamically generated table will have two columns ”one for the description and the other for the image. On lines 22 and 23 I create a new TableRow and the first TableCell object. I insert a new LiteralControl into the cell containing the image description, and then I set some of the TableCells attributes and add the TableCell to the TableRow . On line 29 the second TableCell is created ”this TableCell will hold the image. On line 30 I create a new Image control and set its ImageUrl attribute equal to the Url variable and concatenate the ImageId field to the end. Next I set some of its attributes and add it to the TableCell . Then I immediately add the TableCell to the TableRow and then the TableRow to the Table . Figure 13.4 illustrates this page.

Figure 13.4. All images from the Images table.

only for RuBoard