In this post we will take a look at how to store data in a MS-SQL Server database using ASP.NET. We will start by creating the database table in which we will store the actual file data, and then create a new Web Form with a file upload control and an upload button which will save the uploaded file to the database. After that we need to create a generic HTTP Handler which will be responsible for flushing out the file to the client. The reason to why we will use a HTTP Handler for this is that we don’t need all the functionality that the System.Web.UI.Page provides, so having both performance and simplicity in mind a HTTP Handler is a better choice.

1. Start by creating a table named File in your database, add the following fields to it:
database

2. Create a new Web Form, give it the name UploadFile.aspx and add the following code:

C#:
<%@ Page Language="C#" AutoEventWireup="false" CodeFile="UploadFile.aspx.cs" Inherits="UploadFile" %>
<!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</title>
</head>
<body>
    <form id="form1" runat="server">
	<asp:FileUpload runat="server" ID="ctrFile" /> <asp:Button ID="btnUploadFile" runat="server" Text="Upload" />
	<asp:HyperLink runat="server" ID="ctrResult" Target="_blank" />
    </form>
</body>
</html>

VB.NET:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="FileUpload.aspx.vb" Inherits="FileUpload" %>
<!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</title>
</head>
<body>
<form id="form1" runat="server">
	<asp:FileUpload runat="server" ID="ctrFile" /> <asp:Button ID="btnUploadFile" runat="server" Text="Upload" />
	<asp:HyperLink runat="server" ID="ctrResult" Target="_blank" />
</form>
</body>
</html>

UploadFile.aspx.cs:

C#:
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

/// <summary>
/// Handles uploading- and storing a file in a database.
/// </summary>
public partial class UploadFile : System.Web.UI.Page
{
	protected override void OnInit(EventArgs e)
	{
		base.OnInit(e);

		btnUploadFile.Click += new EventHandler(btnUploadFile_Click);
	}
	void btnUploadFile_Click(object sender, EventArgs e)
	{
		// exit if file-upload has no file
		if (!ctrFile.HasFile) return;

		// generate new fileId
		Guid fileId = Guid.NewGuid();
		// create insert query
		using (SqlCommand command = new SqlCommand())
		{
			command.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
			command.CommandText = @"insert into [File](FileId, FileName, FileType, FileSize, FileContent)
						values(@FileId, @FileName, @FileType, @FileSize, @FileContent)";
			command.Parameters.Add("@FileId", SqlDbType.UniqueIdentifier).Value = fileId;
			command.Parameters.Add("@FileName", SqlDbType.NVarChar, 100).Value = Path.GetFileName(ctrFile.PostedFile.FileName);
			command.Parameters.Add("@FileType", SqlDbType.NVarChar, 100).Value = ctrFile.PostedFile.ContentType;
			command.Parameters.Add("@FileSize", SqlDbType.Int).Value = ctrFile.PostedFile.ContentLength;
			// filecontent, convert from stream to byte array
			byte[] fileContent = new byte[ctrFile.PostedFile.ContentLength];
			ctrFile.PostedFile.InputStream.Read(fileContent, 0, ctrFile.PostedFile.ContentLength);
			command.Parameters.Add("@FileContent", SqlDbType.VarBinary, -1).Value = fileContent;
			command.Connection.Open();
			command.ExecuteNonQuery();
		}
		// show result
		ctrResult.NavigateUrl = "FileStream.ashx?FileId=" + fileId.ToString();
		ctrResult.Text = "Click here to view the uploaded file";
	}
}

VB.NET:
Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

"' <summary>
"' Handles uploading- and storing a file in a database.
"' </summary>
Partial Class UploadFile
	Inherits System.Web.UI.Page

	Protected Sub btnUploadFile_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUploadFile.Click
		' exit if file-upload has no file
		If Not ctrFile.HasFile Then Exit Sub

		' generate new fileId
		Dim fileId As Guid = Guid.NewGuid()
		' create insert query
		Dim command As New SqlCommand()
		Try
			command.Connection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
			command.CommandText = "insert into [File](FileId, FileName, FileType, FileSize, FileContent)" _
									& "values(@FileId, @FileName, @FileType, @FileSize, @FileContent)"
			command.Parameters.Add("@FileId", SqlDbType.UniqueIdentifier).Value = fileId
			command.Parameters.Add("@FileName", SqlDbType.NVarChar, 100).Value = Path.GetFileName(ctrFile.PostedFile.FileName)
			command.Parameters.Add("@FileType", SqlDbType.NVarChar, 100).Value = ctrFile.PostedFile.ContentType
			command.Parameters.Add("@FileSize", SqlDbType.Int).Value = ctrFile.PostedFile.ContentLength
			' filecontent, convert from stream to byte array
			Dim fileContent(ctrFile.PostedFile.ContentLength) As Byte
			ctrFile.PostedFile.InputStream.Read(fileContent, 0, ctrFile.PostedFile.ContentLength)
			command.Parameters.Add("@FileContent", SqlDbType.VarBinary, -1).Value = fileContent
			command.Connection.Open()
			command.ExecuteNonQuery()
		Finally
			command.Dispose()
		End Try
		' show result
		ctrResult.NavigateUrl = "FileStream.ashx?FileId=" + fileId.ToString()
		ctrResult.Text = "Click here to view the uploaded file"
	End Sub
End Class

3. Create a new Generic Handler (available under Add New Item > Generic Handler), give it the name FileStream.ashx and add the following code:

C#:
<%@ WebHandler Language="C#" Class="FileStream" %>
using System;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Reponsible for flushing out the file from the database to the user.
/// </summary>
public class FileStream : IHttpHandler
{
    public void ProcessRequest (HttpContext context)
    {
	// get the file id
	Guid fileId = new Guid(context.Request.QueryString["FileId"]);

	using (SqlCommand command = new SqlCommand())
	{
		// get the file from database
		command.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
		command.CommandText = "select * from [File] where FileId = @FileId";
		command.Parameters.Add("@FileId", SqlDbType.UniqueIdentifier).Value = fileId;
		command.Connection.Open();
		SqlDataReader reader = command.ExecuteReader();
		if (reader.Read())
		{
			// flush out the binary data to the user
			context.Response.Clear();
			context.Response.ContentType = (string) reader["FileType"];
			context.Response.AddHeader("Content-Disposition", String.Format("inline;filename={0};", reader["FileName"].ToString()));
			context.Response.AddHeader("Content-Length", reader["FileSize"].ToString());
			context.Response.BinaryWrite((byte[]) reader["FileContent"]);
			context.Response.End();
		}
	}
    }
    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

VB.NET:
<%@ WebHandler Language="VB" Class="FileStream" %>
Imports System
Imports System.Web
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

"' <summary>
"' Reponsible for flushing out the file from the database to the user.
"' </summary>
Public Class FileStream : Implements IHttpHandler
	Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
		' get the file id
		Dim fileId As New Guid(context.Request.QueryString("FileId"))

		Dim command As New SqlCommand()

		Try
			' get the file from database
			command.Connection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
			command.CommandText = "select * from [File] where FileId = @FileId"
			command.Parameters.Add("@FileId", SqlDbType.UniqueIdentifier).Value = fileId
			command.Connection.Open()
			Dim reader As SqlDataReader = command.ExecuteReader()
			If reader.Read() Then
				' flush out the binary data to the user
				context.Response.Clear()
				context.Response.ContentType = reader("FileType").ToString()
				context.Response.AddHeader("Content-Disposition", String.Format("inline;filename={0};", reader("FileName").ToString()))
				context.Response.AddHeader("Content-Length", reader("FileSize").ToString())
				context.Response.BinaryWrite(DirectCast(reader("FileContent"), Byte()))
				context.Response.End()
			End If
		Finally
			command.Dispose()
		End Try
	End Sub
	Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
		Get
			Return False
		End Get
	End Property
End Class

4. Run your code, upload a file using UploadFile.aspx, and then click on the hyperlink “Click here to view the uploaded file” to view the uploaded file.


This entry was posted on Saturday, April 12th, 2008 at 11:39 am and is filed under ASP.NET, SQL Server, Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.