In this article I will explain how to save and retrieve VARBINARY(MAX) data into SQL Server using ASP.NET. You may opt to do this to store images or documents into the database.
So, let us start at the database level. Below is a simplistic table definition for storing our files into a table:
CREATE TABLE [dbo].[tb_File]( [FileID] [int] IDENTITY(1,1) NOT NULL, [FileName] [varchar](255) NOT NULL, [FileData] [varbinary](max) NOT NULL, [FileContentType] [varchar](255) NOT NULL, CONSTRAINT [PK_TB_FILE] PRIMARY KEY CLUSTERED ( [FileID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
And here are two simple stored procedures to save and retrieve the file data:
CREATE PROCEDURE dbo.usp_File_Get ( @FileID int ) AS BEGIN SET NOCOUNT ON SELECT FileID ,[FileName] ,FileData ,FileContentType FROM tb_File WHERE FileID = @FileIDSET NOCOUNT OFFEND
GO
And
CREATE PROCEDURE dbo.usp_File_Insert ( @FileName varchar(255), @FileData varbinary(max), @FileContentType varchar(255) ) AS BEGIN SET NOCOUNT ON INSERT INTO tb_File ([FileName], FileData, FileContentType) VALUES (@FileName, @FileData, @FileContentType)SET NOCOUNT OFFEND
GO
Now that we have the database objects in place, lets create a basic maintenance web page to be able to upload a file and save it to the database:
Markup:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>Upload file to database</title> </head> <body> <form id="form1" runat="server"> <div> File Name: </div> <div> <asp:TextBox runat="server" ID="txtFileName"></asp:TextBox> </div> <br /> <div> File: </div> <div> <asp:fileupload runat="server" id="uplFile" /> </div> <br /> <div> <asp:button ID="btnSave" runat="server" text="Save" OnClick="btnSave_Click" /> </div> </form> </body> </html>
Code-Behind:
Partial Class _Default Inherits System.Web.UI.Page Private m_strFileName, m_strFileContentType As String Private m_intFileLen As Integer Private m_byteFileData As Byte() Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs) GetFileData() SaveData() End Sub Private Sub GetFileData() Dim myFile As HttpPostedFile = uplFile.PostedFile m_strFileName = System.IO.Path.GetFileName(uplFile.FileName) m_strFileContentType = myFile.ContentType m_intFileLen = myFile.ContentLength m_byteFileData = Nothing ' make sure the size of the file is > 0 If m_intFileLen > 0 Then ' Allocate a buffer for reading of the file m_byteFileData = New Byte(m_intFileLen - 1) {} ' Read uploaded file from the Stream myFile.InputStream.Read(m_byteFileData, 0, m_intFileLen) End If End Sub Private Sub SaveData() Dim conSQL = New SqlClient.SqlConnection conSQL.ConnectionString = "Your database connection string" conSQL.Open() Dim comSQL As New SqlClient.SqlCommand() With comSQL .CommandText = "dbo.usp_File_Insert" .Connection = conSQL .CommandType = CommandType.StoredProcedure .Parameters.AddWithValue("@FileName", m_strFileName) .Parameters.Add("@DocData", SqlDbType.VarBinary, -1) ' Need to set size to -1 since it is varbinary(max) If m_byteFileData Is Nothing Then .Parameters("@DocData").Value = System.DBNull.Value Else .Parameters("@DocData").Value = m_byteFileData End If .Parameters.AddWithValue("@DocContentType", m_strFileContentType) .ExecuteNonQuery() End With conSQL.Close() conSQL.Dispose() End Sub End Class
Now lets look at how we can view the stored binary data through ASP.NET. Below we have a simple handler that is passed a File ID as a Querystring. It will look up the ID in the database and then write the object out as a byte stream.
<%@ WebHandler Language="VB" Class="FileHandler" %>
Imports System Imports System.Web Imports System.Web.Services Public Class FileHandler Implements System.Web.IHttpHandler Private m_strFileContentType As String = String.Empty Private m_objFileData As Object Private m_intFileID As Integer ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable Get Return False End Get End Property Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest GetData(context) ShowFile(context) End Sub Private Sub GetData(ByVal context As HttpContext) If context.Request.QueryString("ID") IsNot Nothing AndAlso context.Request.QueryString("ID").Trim() <> String.Empty AndAlso IsNumeric(context.Request.QueryString("ID").Trim()) Then m_intFileID = CInt(context.Request.QueryString("ID").Trim()) Dim conSQL = New SqlClient.SqlConnection conSQL.ConnectionString = "Your database connection string" conSQL.Open() Dim comSQL As New SqlCommand("dbo.usp_File_Get") comSQL.Connection = conSQL comSQL.Parameters.AddWithValue("@FileID", m_intFileID) comSQL.CommandType = CommandType.StoredProcedure 'read record from database Dim drdDrillInstructions As SqlDataReader drdDrillInstructions = comSQL.ExecuteReader() Try If (drdDrillInstructions.HasRows) Then While drdDrillInstructions.Read() m_strFileContentType = drdDrillInstructions.Item("FileContentType").ToString() m_objFileData = drdDrillInstructions.Item("FileData") End While End If Catch ex As Exception ' Publish error here Finally If Not drdDrillInstructions.IsClosed Then drdDrillInstructions.Close() End If conSQL.Close() conSQL.Dispose() End Try End If End Sub Private Sub ShowFile(ByVal context As HttpContext) ' Clear out the existing HTTP header information context.Response.Expires = 0 context.Response.Buffer = True context.Response.Clear() If m_objFileData IsNot Nothing AndAlso (Not DBNull.Value.Equals(m_objFileData)) Then context.Response.ContentType = m_strFileContentType context.Response.BinaryWrite(DirectCast(m_objFileData, Byte())) Else context.Response.Write("<html><body bgcolor='#D6EBFF'><p align='center'><br /><br /><br /><br /><br />") context.Response.Write("<font class='text'>(No file available)</font>") context.Response.Write("</p></body></html>") End If End Sub End Class
Hope this helps,
Stefan.
For more Web Code, ASP.NET, SQL Server and other development tips, please check back here at webcodeblog.com often.
