Storing and reading images from SQL in a ASP.NET details view
So I needed a quick lost pets page for a community site I run, my ISP is not super friendly about file I\O (security reasons), so I needed to use the SQL D\B they provide. I figured I would share this little app as I had to play around a bit to get it working. Notes:
I stayed away from stored procedures for simplicity.
I do not validate image uploads, you should!
Dates are handled as strings - handing them correctly is another blog :)
In my actual app I am using auth - I cut that for simplicity of this blog entry
I removed all state managment: user deleting of a row
Ok step one is a database - you need to have one :) I used an auto incrementing ID field set as a prmary key - this is used for master\details syncing and image gathering (explored later in this blog). Below is a picture of my SQL table. Things of imortance are the ID column (primary auto incrementing field) and the image column (type is Image).
Now the main Page -
And now for the code:
<%
@ Page Language="C#" %>
<%@ Import Namespace=System.Data.SqlClient %>
<script runat="server">
protected void DetailsView1_ItemInserted(Object sender, System.Web.UI.WebControls.DetailsViewInsertedEventArgs e)
{
GridView1.DataBind();
}
protected void DetailsView1_ItemUpdated(Object sender, System.Web.UI.WebControls.DetailsViewUpdatedEventArgs e)
{
GridView1.DataBind();
}
protected void SqlDataSource3_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
FileUpload stateTextBox = (FileUpload)DetailsView1.Rows[2].Cells[0].Controls[1];
System.IO.Stream inputStream = stateTextBox.PostedFile.InputStream;
int imageLength = stateTextBox.PostedFile.ContentLength;
byte[] imageBinary = new byte[imageLength];
int inputRead = inputStream.Read(imageBinary, 0, imageLength);
byte[] imageData = imageBinary;
System.Data.SqlClient.SqlParameter uploadData = new System.Data.SqlClient.SqlParameter("@photo", System.Data.SqlDbType.Image);
uploadData.Value = imageData;
e.Command.Parameters.Add(uploadData);
}
protected void New_Click(object sender, EventArgs e)
{
DetailsView1.ChangeMode(DetailsViewMode.Insert);
}
</script>
<html>
<body>
<form id="form1" runat="server">
<table>
<tr>
<td colspan="3" style="text-align: center">
<asp:Button ID="New" runat="server" OnClick="New_Click" Text="New Record" />
</td>
</tr>
<tr>
<td colspan="3" rowspan="2">
<asp:GridView ID="GridView1" AllowSorting="True" AllowPaging="True" runat="server" DataSourceID="SqlDataSource1" DataKeyNames="id" AutoGenerateColumns="False" Width="427px" >
<Columns>
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Select" Text="View Details"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Pet Name" SortExpression="petname">
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("petname") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="datelost" HeaderText="Date Lost" SortExpression="datelost" />
</Columns>
</asp:GridView>
</td>
</tr>
</table>
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" SelectCommand="SELECT [id], [petname], [datelost] from jdtest" ConnectionString="<%$ ConnectionStrings:VenusTestsConnectionString1 %>" >
</asp:SqlDataSource>
<asp:DetailsView AutoGenerateRows="False" DataKeyNames="id" DataSourceID="SqlDataSource3"HeaderText="Lost Pet Details" ID="DetailsView1" runat="server" Width="275px"
OnItemUpdated="DetailsView1_ItemUpdated" OnItemInserted="DetailsView1_ItemInserted" >
<Fields>
<asp:TemplateField ShowHeader="False">
<InsertItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert Record" Font-Bold="True"></asp:LinkButton>
<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel Insertion" Font-Bold="True"></asp:LinkButton>
</InsertItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Pet Name" ShowHeader="False" SortExpression="petname">
<InsertItemTemplate>
<strong>Pet Name:</strong>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("petname") %>'></asp:TextBox>
</InsertItemTemplate>
<ItemTemplate>
<strong>Pet Name:</strong>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("petname") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<img src="Handler.ashx?PhotoID=<%# Eval("id") %>" />
</ItemTemplate>
<InsertItemTemplate>
<strong>Image of your pet: </strong>
<asp:FileUpload ID="FileUpload1" runat="server" />
</InsertItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Details" ShowHeader="False" SortExpression="Data">
<InsertItemTemplate>
<strong>Details:</strong><br />
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("Data") %>' Height="138px" TextMode="MultiLine" Width="225px"></asp:TextBox>
</InsertItemTemplate>
<ItemTemplate>
<strong>Details:</strong><br />
<asp:TextBox ID="TextBox4" runat="server" Height="136px" ReadOnly="True" Text='<%# Bind("Data") %>' TextMode="MultiLine" Width="219px"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="datelost" ShowHeader="False" SortExpression="datelost">
<InsertItemTemplate>
<strong>DateLost:<br />
<asp:TextBox ID="TextBox5" runat="server" Text='<%# Bind("datelost") %>'></asp:TextBox></strong>
</InsertItemTemplate>
<ItemTemplate>
<strong>DateLost:</strong><br />
<asp:Label ID="Label2" runat="server" Text='<%# Bind("datelost") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Fields>
<HeaderStyle Font-Bold="True" Font-Size="Large" HorizontalAlign="Center" />
</asp:DetailsView>
<asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:VenusTestsConnectionString1 %>" ID="SqlDataSource3" runat="server"
SelectCommand="SELECT [id], [datelost], [petname], [contactPhone], [Data], [photo] FROM [jdtest] WHERE ([id] = @id)"
InsertCommand="INSERT INTO [jdtest] ([petname], [Data], [photo], [datelost]) VALUES (@petname, @data, @photo, @datelost)"
OnInserting="SqlDataSource3_Inserting">
<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="id" PropertyName="SelectedValue" Type="String" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="petname" Type="String" />
<asp:Parameter Name="data" Type="String" />
<asp:Parameter Name="datelost" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</form>
</body>
</html>
So there are some intersting technology notes here. The SQLDataSource InsertParameters collection is strongly typed. It does not have an Image type - so decaring the image field here is not possible. I handle this in the SqlDataSource3_Inserting event as a SqlParamaeter - here I can specify the type as a System.Data.SqlDbType.Image - which is what I need (that is what the database has been told to expect). Next I can add it to the SQlDataSource control's Command paramater collection:
e.Command.Parameters.Add(fuData);
OK - so I lied - a bit of state management is left in my code - to insert a new record I have a button that calls ChangeMode on the detailsView:
DetailsView1.ChangeMode(
DetailsViewMode.Insert);
So far we have looked at the code for getting images into the database - now lets examine how we get them back out, without file disk I\O.
A high level conceptual of this process is that we somehow get the data from the table - stream it into a byte array and dump that to the current page's Response being dished out.
Here is the handlers code (for those not familiar a handler isusually a .ASHX file):
<%
@ WebHandler Language="C#" Class="Handler" %>
using System;
using System.IO;
using System.Web;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Drawing.Imaging;
public class Handler : IHttpHandler {
public bool IsReusable {
get {
return true;
}
}
public static Stream GetPhoto2(int photoid)
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["VenusTestsConnectionString"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("select [photo] FROM [jdtest] WHERE ([id]=@id)", connection))
{
command.Parameters.Add(new SqlParameter("@id", photoid));
connection.Open();
object result = command.ExecuteScalar();
try
{
return new MemoryStream((byte[])result);
}
catch
{
return null;
}
}
}
}
public void ProcessRequest (HttpContext context)
{
//Set up the response settings
context.Response.ContentType = "image/jpeg";
context.Response.Cache.SetCacheability(HttpCacheability.Public);
context.Response.BufferOutput = false;
// Setup the PhotoID Parameter
Int32 id = -1;
Stream stream = null;
id = Convert.ToInt32(context.Request.QueryString["PhotoID"]);
stream = GetPhoto2(id);
const int buffersize = 1024 * 16;
byte[] buffer2 = new byte[buffersize];
int count = stream.Read(buffer2, 0, buffersize);
while (count > 0) {
context.Response.OutputStream.Write(buffer2, 0, count);
count = stream.Read(buffer2, 0, buffersize);
}
}
}
To do this we leverage a few pieces of ASP.NET technology - the first being the response object. To display our image we gather it and send it to the response ala:
context.Response.OutputStream.Write(...)
We do this ia an HttpHandler so we can set an Image control's URL property to a URL that simply outputs an image - the cool thing here is we can put this Image control in a templete, set the URL to the Handler and the image is displayed exactly where we want it.
OK - some notes on this - the Handler still needs to know which image to go and get - so we need to send that along - I used the query string feaure to send and retrieve the selected row in the DetailsView- I would think you could use cookies or session as well - but again for simpliity I used the querystring. So examining the details view ItemTemplate for the image we see:
<
img src="Handler.ashx?PhotoID=<%# Eval("id") %>" />
We are appending the current DetailsView's records ID - which in turn will be parsed by the handler to allow it to go to the database and gather the correct image (well actually a byte[]) and ultimately return the image to the response right where it should be.
Some of the feedback I have received has referenced that the SQL Image type will be depricated (and is in SQL2005). You can update the above sample to use VARBINARY(MAX) in the table as well as update the code that inserts the type to:
System.Data.SqlClient.SqlParameter uploadData = new System.Data.SqlClient.SqlParameter("@photo", System.Data.SqlDbType.VarBinary);
Past that things should still work. Note that I left the sample as is as older SqlServers still like and use Image, and within Visual Studio I could not find a way to push VARBINARY past 8000 - thus causing data truncation issues as well as insertion issues. Thanks for the feedback - keep it coming.
I guess I should state that storing images in a database is not the best design. Situations do arise though where it is needed - thus this article.
Comments
Anonymous
September 30, 2013
how to get images from sql serverAnonymous
September 30, 2013
I'd look into Entity Framework. They create code wrappers automatically and should easily accommodate gathering blob data from SQL.