Saving and Displaying Photos in SQL Server using ASP.NET and FileUpload Control

Saving Photo in SQL Server Using SqlClient

Rather than using the Enterprise Library Data Access Application Block, let’s use the SQL Server .NET Managed Provider to store photos uploaded via a FileUpload Control in ASP.NET.

The code is very simple as shown below:

protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
using (BinaryReader reader = new BinaryReader
(FileUpload1.PostedFile.InputStream))
{
byte[] image = reader.ReadBytes
(FileUpload1.PostedFile.ContentLength);
SaveImage(image);
}
}
}

private int SaveImage(byte[] image)
{
int rowsAffected;

using (SqlConnection connection = new SqlConnection(“…“))
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = “INSERT INTO Photos
(Photo) VALUES (@Photo)";
command.Parameters.AddWithValue(“@Photo", image);

connection.Open();
rowsAffected = command.ExecuteNonQuery();
}
}

return rowsAffected;
}

Once the user clicks a button, the code verifies that the FileUpload Control indeed has a photo. The photo is converted to a byte array for storage in SQL Server. In SQL Server 2000, the photo will be stored as an Image DataType. In SQL Server 2005, the photo will be stored in the preferred VARBINARY(MAX) DataType. At this point, the database connection is opened and the code executes an INSERT T-SQL Statement that stores the photo as a byte array ( byte[] ) into SQL Server.

Display Photo In SQL Server Using ASP.NET

Displaying the photo in SQL Server using ASP.NET is just as easy. Just reverse the process as shown in the code below:

protected void Page_Init(object sender, EventArgs e)
{
if (Request.QueryString[“id"] != null)
{
int id;

if (Int32.TryParse(Request.QueryString[“id"], out id))
{
Response.Clear();
Response.ContentType = “image/jpeg";

Image image = RetrieveImage(id);
image.Save(Response.OutputStream, ImageFormat.Jpeg);
}
}
}

private Image RetrieveImage(int photoId)
{
Image image = null;

using (SqlConnection connection = new SqlConnection(“…"))
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = “SELECT Photo FROM Photos
WHERE PhotoId = @PhotoId";
command.Parameters.AddWithValue(“@PhotoId", photoId);

connection.Open();
byte[] imageData = (byte[])command.ExecuteScalar();

MemoryStream memStream = new MemoryStream(imageData);
image = Image.FromStream(memStream);
}
}

return image;
}

The photo id is read in from a querystring. If it parses correctly, the response buffer is cleared and reset to display a photo as opposed to HTML. The photo is read from the SQL Server Database as a byte array and it gets converted to a System.Drawing.Image with a little help from the MemoryStream Class. I recommend adding a bit of exception handling, but assuming the photo exists, the code works fine by displaying the appropriate image in the browser window.

Source: David Hayden ( ASP.NET Developer )

Filed: ADO.NET Tutorials