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 = @FileID
SET NOCOUNT OFF
END
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 OFF
END
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.
