Save files to a table in Database using FileUpload control

 

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 –

DownloadFromDB-DatabaseTable

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 –

DownloadFromDB-DatabaseStructure

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.

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

DownloadFromDB-FolderStructure

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

OUTPUT

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

Default.aspx – Before Upload ->

SaveFileToDBBeforeUpload

Default.aspx – After Upload ->

SaveFileToDBAfterUpload

Inserted Records to DB ->

SaveToDBInsertedRecords

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.

You may also like...

  • santosh

    Thanks ,

    it’s an excellent demo . i hve ever seen .

    Thanks once again.

  • sumana

    Hai thankyou for the code….

    But i need code for uploading zip files and save zip files in database(oracle or sql)

  • RamiVemula

    @Sumana – Same like in this tutorial

  • Deon

    This code is exellent but I need to view it in a rss feed. How do I create link to the PDF? Can I upload the actual document? In this example it looks like I’m uploading the path.

  • RamiVemula

    @Deon – Yes you can upload actual file to Database. This tutorial actually narrates you that you can upload the file to DB.

  • Shivani

    it was simple and understandable….really acknowledgeable…..sadho sadho sadho

  • Tom

    This is a beautiful thing. Thank you. There is only one thing I am still having trouble with. In the script, you reference ‘sf.fileExtension = Path.GetExtension(ItemImage1.PostedFile.FileName); ‘ . When I try to save the aspx file, it generates an error on ‘path’. This was resolved by adding ‘System.IO.’ then ‘Path.GetExtension(ItemImage1.PostedFile.FileName); ‘ .
    Thanks again!!!!

  • Rash

    Hi, im trying to fetch mhtml files using your code. everythin is working just fine except tht when i click on “open” button ond dialog box, for every alternate clicks, a temporary file name is displayed of html type. and i need to click on open twice to open the actual mhtml file. could u suggest wats wrong?

  • RamiVemula

    @Rash – Not sure about your problem. Can you elaborate more?

  • Abed

    the code showed error:

    Line 10: public string fileName { set; get; } public string fileExtension { set; get; } public byte[] data { set; get; }

  • RamiVemula

    @Abed – Can you please let me know what was that error? I am not getting any error on my side.

  • madhu

    In this code only save the value in data base……..can u tell me show the pic when upload a file………

  • OFIENBOR CHRISTIAN

    I LUV UR DEMO N SINCERELY IT IS EASY TO UNDERSTAND BUT I NID D CODE TO STORE BINARY DIRECTLY ON THE TABLE.
    THANKS IN ANTICIPATION.

  • Kelly

    I new to this. Can a aspx file be retrieved this way?