This tutorial shows you how to retrieve raw binary data from a SQL Database using VB.NET and ASP.NET 3.5
In this tutorial, we will be following on from our earlier tutorial, Storing Binary Data in SQL Database with VB.NET, 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 Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Try
Const SQL As String = "INSERT INTO [BinaryTable] ([FileName], [DateTimeUploaded], [MIME], [BinaryData]) VALUES (@FileName, @DateTimeUploaded, @MIME, @BinaryData)"
Dim cmd As New SqlCommand(SQL, Conn)
cmd.Parameters.AddWithValue("@FileName", FileName.Text.Trim())
cmd.Parameters.AddWithValue("@MIME", fileType)
Dim imageBytes(FileToUpload.PostedFile.InputStream.Length) As Byte
FileToUpload.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
cmd.Parameters.AddWithValue("@BinaryData", imageBytes)
cmd.Parameters.AddWithValue("@DateTimeUploaded", DateTime.Now)
Conn.Open()
cmd.ExecuteNonQuery()
lit_Status.Text = "<br />File successfully uploaded - thank you.<br />"
Conn.Close() Catch ex As Exception
Conn.Close() End Try |
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 Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Try
Const SQL As String = "INSERT INTO [BinaryTable] ([FileName], [DateTimeUploaded], [MIME], [BinaryData]) VALUES (@FileName, @DateTimeUploaded, @MIME, @BinaryData) SELECT SCOPE_IDENTITY()"
Dim cmd As New SqlCommand(SQL, Conn)
cmd.Parameters.AddWithValue("@FileName", FileName.Text.Trim())
cmd.Parameters.AddWithValue("@MIME", fileType)
Dim imageBytes(FileToUpload.PostedFile.InputStream.Length) As Byte
FileToUpload.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
cmd.Parameters.AddWithValue("@BinaryData", imageBytes)
cmd.Parameters.AddWithValue("@DateTimeUploaded", DateTime.Now)
Conn.Open()
Dim theInt As Integer = Convert.ToInt32(cmd.ExecuteScalar())
lit_Status.Text = "<br />File successfully uploaded - thank you.<br />"
lnk_Picture.NavigateUrl = "~/ShowImage.aspx?ID=" & theInt.ToString()
Conn.Close() Catch ex As Exception
Conn.Close() End Try |
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" /> |
We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect's help, we were able to avoid any headaches!
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 reference for this new page:
| Imports System.Data.SqlClient |
Next, add the following to the Page Load event:
Try
Dim PictureID As Integer = Convert.ToInt32(Request.QueryString("ID")) Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Const SQL As String = "SELECT [MIME], [BinaryData] FROM [BinaryTable] WHERE [ID] = @ID" Dim myCommand As New SqlCommand(SQL, myConnection) myCommand.Parameters.AddWithValue("@ID", PictureID) myConnection.Open() Dim myReader As SqlDataReader = myCommand.ExecuteReader() If myReader.Read Then
Response.ContentType = myReader("MIME").ToString()
Response.BinaryWrite(myReader("BinaryData")) End If myReader.Close() myConnection.Close() End Using Response.Write(ex.ToString()) End Try |
We chose Server Intellect for its dedicated servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.
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:
Imports System.Data.SqlClient
Partial Class ShowImage
Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Try
Dim PictureID As Integer = Convert.ToInt32(Request.QueryString("ID")) Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Const SQL As String = "SELECT [MIME], [BinaryData] FROM [BinaryTable] WHERE [ID] = @ID" Dim myCommand As New SqlCommand(SQL, myConnection) myCommand.Parameters.AddWithValue("@ID", PictureID) myConnection.Open() Dim myReader As SqlDataReader = myCommand.ExecuteReader() If myReader.Read Then
Response.ContentType = myReader("MIME").ToString()
Response.BinaryWrite(myReader("BinaryData")) End If myReader.Close() myConnection.Close() End Using Catch ex As Exception
Response.Write(ex.ToString()) End Try End Sub |
Looking for more ASP.NET Database Tutorials? Click Here!
|