Storing and Retrieving Files from SQL Database Using ASP.NET 2.0

Most of our application might need to store images, PDFs, Word documents, Excel documents, or other binary data. there are two ways: on the web server's file system, with a reference to the file in the database; or directly in the database itself. In this Article we will discuss the second way storing directly in the database itself.

1. Creating a Database Table to Store Files:

We need to create simple table to holds Binary Data, (I will use MS SQL Server 2005). Also we need to Create Stored Procedure to use it to Insert record into UserFile Table, look to the following code:

CREATE PROCEDURE InsertUserFileInfo ( @FileName nvarchar(400), @ContentType varchar(100), @FileLength int, @FileContent image ) AS INSERT INTO UserFile ( FileName, ContentType, FileLength, FileContent ) VALUES ( @FileName, @ContentType, @FileLength, @FileContent )

2. Creating ASP.NET Page to upload file

We will add only FileUpload Control with Button to upload file, look to the following code:

UploadFile.aspx <asp:FileUpload ID="FileUpload1" runat="server" /> <asp:Button ID="btnUpload" runat="server" Text="Upload" onclick="btnUpload_Click" />

3. Adding Code to Upload.aspx Page

Once the user has selected a file and posted back the form (by clicking the "Upload" button, for example), the binary contents of the specified file are posted back to the web server. From the server-side code, this binary data is available through the FileUpload control's PostedFile.InputStream property, as the following markup and code illustrates:

UploadFile.aspx.cs protected void btnUpload_Click(object sender, EventArgs e) { if (FileUpload1.HasFile) { // string to store allowed file extension string strAllowedExtenstion = ".doc, .zip, .rar, .docx, .xls, .xlsx, .pdf, .jpg, .gif, .png"; // string to get user file extension string strFileExtenstion = System.IO.Path.GetExtension(FileUpload1.FileName); if (strAllowedExtenstion.IndexOf(strFileExtenstion) > -1) { // IO Stream to hold uploaded file stream System.IO.Stream stream = FileUpload1.PostedFile.InputStream; // array of bytes to hold file stream byte[] fileData = new byte[stream.Length]; stream.Read(fileData, 0, Convert.ToInt32(stream.Length)); stream.Close(); // now call function to store binary data in DB _saveDataToDB(fileData, FileUpload1.FileName, _getContentType(strFileExtenstion) ); } else { // Not Allowed file type } } else { // no file selected } } private string _getContentType(string pFileExtension) { switch (pFileExtension) { case ".doc": return "application/vnd.ms-word"; case ".docx": return "application/vnd.ms-word"; case ".xls": return "application/vnd.ms-excel"; case ".xlsx": return "application/vnd.ms-excel"; case ".jpg": return "image/jpg"; case ".png": return "image/png"; case ".gif": return "image/gif"; case ".pdf": return "application/pdf"; case ".rar": return "application/rar"; case ".zip": return "application/zip"; } return ""; } // Store File data into SQL DB using Stored Procedure (InsertUserFileInfo) private void _saveDataToDB(byte[] pFileData , string pFileName,string pContentType) { using (SqlConnection connection = new SqlConnection(ConnectionString)) { connection.Open(); using (SqlCommand command = new SqlCommand("InsertUserFileInfo", connection)) { command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.Add(new SqlParameter("@FileName", pFileName)); command.Parameters.Add(new SqlParameter("@ContentType", pContentType)); command.Parameters.Add(new SqlParameter("@FileLength", pFileData.Length)); command.Parameters.Add(new SqlParameter("@FileContent", pFileData)); command.ExecuteNonQuery(); } } }

3. Retrieving File from SQL DB:

The first step is creating new page and call it ViewFile.aspx The next Step is creating Stored Procedure to retrieve File Data

CREATE PROCEDURE [dbo].[GetUserFileInfo] ( @FileID INT ) AS SELECT FileID, FileName, ContentType, FileLength, FileContent FROM UserFile WHERE (@FileID =FileID)

The Final step is adding code to page behind (into ViewFile.aspx.cs), look down:

ViewFile.aspx.cs protected void Page_Load(object sender, EventArgs e) { string pContentType; Int32 pFileLength; byte[] pFileContent; string pFileName; _getDataFromDB(ref pFileLength, ref pFileName, ref pFileContent, ref pContentType) Response.Clear(); Response.Charset = "utf-8"; Response.Buffer = true; this.EnableViewState = false; Response.ContentEncoding = System.Text.Encoding.UTF8; Response.AddHeader("Content-Disposition", "attachment;filename=" + pFileName); Response.BinaryWrite(pFileContent); Response.ContentType = pContentType; Response.Flush(); Response.Close(); Response.End(); } private void _getDataFromDB(ref Int32 pFileLength, ref string pFileName, ref byte[] pFileContent, ref string pContentType) { using (SqlConnection connection = new SqlConnection(ConnectionString)) { connection.Open(); using (SqlCommand command = new SqlCommand()) { command.Connection = connection; command.CommandType = System.Data.CommandType.StoredProcedure; command.CommandText = "GetUserFileInfo"; // FileID from QueryString e.g when calling this page just type ViewFile.aspx?FileID=3 // you can use any other way to determine which FileID you want command.Parameters.Add(new SqlParameter("@FileID", Convert.ToInt32(Request.QueryString["FileID"]))); SqlDataReader sqlDataReader = command.ExecuteReader(); while (sqlDataReader.Read()) { pContentType = sqlDataReader.GetString(2); pFileContent = (byte[])sqlDataReader[4]; ; pFileLength = sqlDataReader.GetInt32(3); pFileName = sqlDataReader.GetString(1); } } } }

After runuing and calling this page, the following message will appear: now you can save this file or open it directly

Thanks