This tutorial shows you how to retrieve raw binary data from a SQL Database using C# and ASP.NET 3.5
In this tutorial, we will be following on from our earlier tutorial, Storing Binary Data in SQL Database with C#, and see how we can take it a step further and display the JPEG or GIF image directly from the database.
We will be building upon the existing code in the earlier project, so if you want to go through that tutorial first, the link is above. The only thing we need to add to the existing page is to display a link to view the recently uploaded image. To do this, we will change the way we execute the inserting of the file. Below is what we used in the first example:
using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
try {
const string SQL = "INSERT INTO [BinaryTable] ([FileName], [DateTimeUploaded], [MIME], [BinaryData]) VALUES (@FileName, @DateTimeUploaded, @MIME, @BinaryData)";
SqlCommand cmd = new SqlCommand(SQL, Conn);
cmd.Parameters.AddWithValue("@FileName", FileName.Text.Trim());
cmd.Parameters.AddWithValue("@MIME", fileType);
byte[] imageBytes = new byte[FileToUpload.PostedFile.InputStream.Length + 1];
FileToUpload.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length);
cmd.Parameters.AddWithValue("@BinaryData", imageBytes);
cmd.Parameters.AddWithValue("@DateTimeUploaded", DateTime.Now);
lit_Status.Text = "<br />File successfully uploaded - thank you.<br />";
Conn.Open();
cmd.ExecuteNonQuery();
Conn.Close(); } catch {
Conn.Close(); } |
I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.
Instead of calling the ExecuteNonQuery method, we will call the ExecuteScalar method and change the SQL statement to return the new ID that has just been inserted:
using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
try {
const string SQL = "INSERT INTO [BinaryTable] ([FileName], [DateTimeUploaded], [MIME], [BinaryData]) VALUES (@FileName, @DateTimeUploaded, @MIME, @BinaryData) SELECT SCOPE_IDENTITY()";
SqlCommand cmd = new SqlCommand(SQL, Conn);
cmd.Parameters.AddWithValue("@FileName", FileName.Text.Trim());
cmd.Parameters.AddWithValue("@MIME", fileType);
byte[] imageBytes = new byte[FileToUpload.PostedFile.InputStream.Length + 1];
FileToUpload.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length);
cmd.Parameters.AddWithValue("@BinaryData", imageBytes);
cmd.Parameters.AddWithValue("@DateTimeUploaded", DateTime.Now);
Conn.Open();
int theInt = Convert.ToInt16(cmd.ExecuteScalar());
lit_Status.Text = "<br />File successfully uploaded - thank you.<br />";
lnk_Picture.NavigateUrl = "~/ShowImage.aspx?ID=" + theInt.ToString();
Conn.Close(); } catch {
Conn.Close(); } |
Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.
We use the SQL Method SELECT SCOPE_IDENTITY() to return the ID of the record that has just been inserted. We then utilize this ID with the ExecuteScalar method and put the ID into the NavigateUrl of a HyperLink control we added to the form to utilize a querystring. Our form now looks like this:
<form id="form1" runat="server">
Please upload either a JPEG or a GIF:<br /> <asp:Literal ID="lit_Status" runat="server" /><br /> <asp:HyperLink ID="lnk_Picture" runat="server" text="Click Here" /> <b>Name:</b> <asp:TextBox ID="FileName" runat="server" /> <br /> <b>File:</b> <asp:FileUpload ID="FileToUpload" runat="server" /> <br /> <asp:Button ID="btn_Upload" runat="server" Text="Upload" /> |
Just the one change on the form - the addition of the HyperLink.
We are going to use a new page to display the image, so add a new WebForm to your project named ShowImage.aspx
First, we need to add the following assembly references for this new page:
using System.Data.SqlClient;
using System.Configuration; |
Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!
Next, add the following to the Page Load event:
try
{
int PictureID = Convert.ToInt32(Request.QueryString["ID"]); using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)) {
const string SQL = "SELECT [MIME], [BinaryData] FROM [BinaryTable] WHERE [ID] = @ID"; SqlCommand myCommand = new SqlCommand(SQL, myConnection); myCommand.Parameters.AddWithValue("@ID", PictureID); myConnection.Open(); SqlDataReader myReader = myCommand.ExecuteReader(); if (myReader.Read()) {
Response.ContentType = myReader["MIME"].ToString();
Response.BinaryWrite((byte[]) myReader["BinaryData"]); } myReader.Close(); myConnection.Close(); }
catch (Exception ex)
{
Response.Write(ex.ToString()); } |
We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.
Here, we are simply using the querystring to locate the image in the database, and then displaying it with the SqlDataReader and BinaryWrite.
The entire code-behind of the new page should look something like this:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
public partial class ShowImage : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e) {
try {
int PictureID = Convert.ToInt32(Request.QueryString["ID"]); using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)) {
const string SQL = "SELECT [MIME], [BinaryData] FROM [BinaryTable] WHERE [ID] = @ID"; SqlCommand myCommand = new SqlCommand(SQL, myConnection); myCommand.Parameters.AddWithValue("@ID", PictureID); myConnection.Open(); SqlDataReader myReader = myCommand.ExecuteReader(); if (myReader.Read()) {
Response.ContentType = myReader["MIME"].ToString();
Response.BinaryWrite((byte[]) myReader["BinaryData"]); } myReader.Close(); myConnection.Close(); } } catch (Exception ex) {
Response.Write(ex.ToString()); } } |
Looking for more ASP.NET Database Tutorials? Click Here!
|