At times, it is required to store and retrieve user data in the form of files (typically say PDF, Word, Excel, Text files etc) to and from the database. Making database transactions with files is bit different procedure than compared with the normal database records. We make use of binary data (obtained from the conversion of file) for this purpose. It is been observed that in recent past, many young programmers taking it hard to accomplish this requirement, so the main motive behind writing this article is to show young coders on how to make database transactions with files.

In this tutorial, we first save files to the database in binary format, then in the second part of the tutorial we see how to provide a download option to the files residing in database. For uploading the files to DB, we use a FileUpload control and to provide download facility we make use of a Generic handler. The plot behind saving files is to take the file from the FileUpload control, convert the same to the binary format, then save it to the DB. To make our accomplishment more flexible and maintainable, we use stored procedures along with custom classes for database transactions. And the plot behind retrieving files is to use a handler which process the incoming request which are made to the files in database.

PART – II – Retrieve files from a table in database using Generic Handler – Click Here

Lets get started by creating a new Database –

================================================================================================

Database Design

Create a sample ‘ExampleDB’ in VS 2010, which holds the table for storing files.

================================================================================================

1. Open VS 2010 -> View -> Server Explorer
2. Right Click ‘Data Connections’ node -> Add new SQL Server Database
3. Select a Server (from DropDownList) -> Prefer Windows Authentication -> Give a Name to the new database, here we use ‘ExampleDB’.

The database has been created, now we create a table.

1. Expand ‘ExampleDB’ in the Server Explorer -> Right Click ‘Tables’ folder -> Select ‘Add New Table’.

Give the details of the table fields as shown below –

Save (press CTRL + SHFT + S) the table, it asks to give a table name; in this case we use ‘FilesData’.

Save the Database – This completes the Database design. Now lets write some code to save files into the database. First we get started by creating a StoredProcedure to save files into a table.

================================================================================================

Stored Procedure

Create a Stored Proc – ‘SaveFilesProc’– which is used to save files into the database.

================================================================================================

To create a Stored Proc –

1. Expand ExampleDB in the Server Explorer -> Right Click -> Stored Procedures Folder -> Select -> Add new Stored Procedure

Remove complete code from the Stored Procedure and place the below code –

ALTER PROCEDURE dbo.SaveFilesProc
    (
    @Data Varbinary(MAX),
    @FileName Varchar(50),
    @FileExtension Varchar(50)
    )
AS
BEGIN

insert into FilesData(Data,FileName,FileExtension) values (@Data,@FileName,@FileExtension)

END

And then save the Stored Procedure. This creates a Stored Proc with name –‘SaveFilesProc’ with a simple insert statement which inserts data into the table ‘FilesData’.

With this we complete the preparation on the Database side. The Database structure would be –

As the Database is ready, now we can start writing code to save our files into the DB from a Webform. Before we code a Webform, lets create a class – which will hold the code for Database activity. So lets get started with this task by creating a website.

================================================================================================

SaveToDB Website

Create a sample ‘SaveToDB’ website in VS 2010.

================================================================================================

1. Open Vs 2010 –> File –> New –> Website.
2. select –> C#
3. Select ‘Asp.Net Empty Website’ –> name – ‘SaveToDB’.

================================================================================================

SaveFile.cs

This class holds the code to access the stored proc (we created above) and store the file from the FileUpload control to DB.

================================================================================================

1. Right Click ‘SaveToDB’ folder in Solution explorer of VS 2010.
2. Select ‘Add Asp.Net Folder…’ –> App_code.
3. Add SaveFile.cs (Right Click App_Code folder in Solution Explorer –> Add New Item –> Class).

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

public class SaveFile
{
    public string fileName { set;  get; }
    public string fileExtension { set;  get; }
    public byte[] data { set;  get; }

    public string SaveFileToDB()
    {
        using (SqlConnection conn = new SqlConnection("Data Source=RAMILU-PC\\SQLEXPRESS;" +
             "Initial Catalog=ExampleDB;Integrated Security=True;Pooling=False"))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SaveFilesProc";
            cmd.Connection = conn;

            cmd.Parameters.AddWithValue("@Data", data);
            cmd.Parameters.AddWithValue("@FileName", fileName);
            cmd.Parameters.AddWithValue("@FileExtension", fileExtension);

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                return "File stored Successfully!!!";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            finally
            {
                conn.Close();
                cmd.Dispose();
                conn.Dispose();
            }
        }
    }
}

================================================================================================

Default.aspx

This is the page which holds the UI for selecting a file, also it uses SaveFile.cs and stores the file to the DB.

================================================================================================

1. Right Click ‘SaveToDB’ folder in Solution explorer of VS 2010.
2. Select ‘Add New Item’ –> Webform
3. Give name Default.aspx

<%@ Page Language="C#" %>
<%@ Import Namespace="System.IO" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    protected void Button1_Click(object sender, EventArgs e)
    {
        SaveFile sf = new SaveFile();

        if (FileUpload1.HasFile)
        {
            sf.fileName = FileUpload1.PostedFile.FileName;
            sf.fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            sf.data = FileUpload1.FileBytes;

            Label1.Text = sf.SaveFileToDB();
        }
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

        Select a File to upload the same to DB - <br />

        <asp:FileUpload
            ID="FileUpload1"
            runat="server" />

        <asp:RequiredFieldValidator
            ID="RequiredFieldValidator1"
            runat="server"
            ControlToValidate="FileUpload1"
            ErrorMessage="RequiredFieldValidator">
        </asp:RequiredFieldValidator><br />

        <asp:Button
            ID="Button1"
            runat="server"
            Text="Save to DB" OnClick="Button1_Click" /><br />

        <asp:Label
            ID="Label1"
            runat="server">
        </asp:Label>

    </div>
    </form>
</body>
</html>

================================================================================================

Folder Structure

The complete Solution Explorer structure of ‘SaveToDB’ website.

================================================================================================

================================================================================================

OUTPUT

================================================================================================

Default.aspx – Before Upload ->

Default.aspx – After Upload ->

Inserted Records to DB ->

PART – II – Retrieve files from a table in database using Generic Handler – Click Here

Disclaimer:

All coding and suggestions made in the above discussion is strictly in point of view of the author. It is neither mentioned any where nor even concluded that this is the only possible way to go with, as there will be always a chance for a new approach in achieving the same requirement (in context of programming). The above mentioned code is strictly written and explained for the sake of new comers to C# and ASP.NET world.