Save files into SQL Server Database using File Uploader Control

The following guide shows the steps to use File Uploader Control to upload multiple files to the database asynchronously. You can download the code example from here.

Introduction

This sample code uses Ajax Uploader control to upload multiple files to the database asynchronously.  Ajax Uploader control allows you select multiple files to upload by using the SHIFT key or CTRL key when selecting files on the file upload dialog.

Ajax Uploader control automatically determines the browser that has requested the page and render the appropriate solution for that browser. If browser supports HTML5, It will use the HTML5 solution. If browser supports SilverLight, It will use SilverLight solution. If browser supports Flash, It will use Flash solution. If browser supports Iframe, It will use Iframe solution.

Ajax Uploader supports client side and server side validation of the file size/type. Client side validation of file size/types prior to uploading the files to a server increases usability and reduces consumption of network and server resources.

Uploading files is a common activity in Asp.net application, in general we upload the files and save them to the file system and store the URL of the saved file in the database, but there are specific scenarios which demand us to store the entire files into the database in binary format.

1. Database Design

First we need to create a table to store the files uploaded, add a column with type varbinary (MAX), this column will be used to store the content of the uploaded file in binary format.

Save file to table

Field Relevance
id Identification Number
Name File Name
FileType Content Type for the file
Data File stored as Binary Data

2. Add Uploader control into the page

In an .aspx page add a file uploader control as follows:

<CuteWebUI:UploadAttachments ID="UploadAttachments1" OnFileUploaded="UploadAttachments1_FileUploaded" runat="server">
</CuteWebUI:UploadAttachments>

3. C# Code Snippet - uploader FileUploaded event

protected void UploadAttachments1_FileUploaded(object sender, UploaderEventArgs args)
{
   //set connection string
    string connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
    // Read the file and convert it to Byte Array
    byte[] data = new byte[args.FileSize];
   //get file extension
    string extensioin = args.FileName.Substring(args.FileName.LastIndexOf(".") + 1);
    string fileType = null;
   //set the file type based on File Extension
    switch (extensioin)
    {
        case "doc":
            fileType = "application/vnd.ms-word";
            break;
        case "docx":
            fileType = "application/vnd.ms-word";
            break;
        case "xls":
            fileType = "application/vnd.ms-excel";
            break;
        case "xlsx":
            fileType = "application/vnd.ms-excel";
            break;
        case "jpg":
            fileType = "image/jpg";
            break;
        case "png":
            fileType = "image/png";
            break;
        case "gif":
            fileType = "image/gif";
            break;
        case "pdf":
            fileType = "application/pdf";
            break;
    }


    using (Stream stream = args.OpenStream())
    {
       //read the file as stream
        stream.Read(data, 0, data.Length);
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
       //set parameters
        SqlParameter p1 = new SqlParameter("@Name", SqlDbType.VarChar);
        SqlParameter p2 = new SqlParameter("@FileType", SqlDbType.VarChar);
        SqlParameter p3 = new SqlParameter("@Data", SqlDbType.VarBinary);
        p1.Value = args.FileName;
        p2.Value = fileType;
        p3.Value = data;
        com.Parameters.Add(p1);
        com.Parameters.Add(p2);
        com.Parameters.Add(p3);
        com.CommandText = "Insert into Files (Name,FileType,Data) VALUES (@Name,@FileType,@Data)";
        con.Open();
       //insert the file into database
        com.ExecuteNonQuery();
        con.Close();
    }
}

4. VB Code Snippet - uploader FileUploaded event

Protected Sub UploadAttachments1_FileUploaded(ByVal sender As Object, ByVal args As UploaderEventArgs)
   'set connection string
    Dim connectionString As String = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString")
    ' Read the file and convert it to Byte Array
    Dim data() As Byte = New Byte((args.FileSize) - 1) {}
    'get file extension
    Dim extensioin As String = args.FileName.Substring((args.FileName.LastIndexOf(".") + 1))
    Dim fileType As String = ""
    'set the file type based on File Extension
    Select Case (extensioin)
        Case "doc"
        fileType = "application/vnd.ms-word"
        Case "docx"
        fileType = "application/vnd.ms-word"
        Case "xls"
        fileType = "application/vnd.ms-excel"
        Case "xlsx"
        fileType = "application/vnd.ms-excel"
        Case "jpg"
        fileType = "image/jpg"
        Case "png"
        fileType = "image/png"
        Case "gif"
        fileType = "image/gif"
        Case "pdf"
        fileType = "application/pdf"
    End Select
    Dim stream As Stream = args.OpenStream
    'read the file as stream
    stream.Read(data, 0, data.Length)
    Dim con As SqlConnection = New SqlConnection(connectionString)
    Dim com As SqlCommand = New SqlCommand
    com.Connection = con
    'set parameters
    Dim p1 As SqlParameter = New SqlParameter("@Name", SqlDbType.VarChar)
    Dim p2 As SqlParameter = New SqlParameter("@FileType", SqlDbType.VarChar)
    Dim p3 As SqlParameter = New SqlParameter("@Data", SqlDbType.VarBinary)
    p1.Value = args.FileName
    p2.Value = fileType
    p3.Value = data
    com.Parameters.Add(p1)
    com.Parameters.Add(p2)
    com.Parameters.Add(p3)
    com.CommandText = "Insert into Files (Name,FileType,Data) VALUES (@Name,@FileType,@Data)"
    con.Open()
    'insert the file into database
    com.ExecuteNonQuery()
    con.Close()
End Sub