I have already explained how to save and retrieve files in SQL Server database using ASP.Net in my article Save and Retrieve Files from SQL Server Database using ASP.Net

This article is an extension of the same since was frequently asked on how to save file directly to database using the ASP.Net FileUpload Control

You can upload any files like images, Word document. Excel document, Portable Document Format (PDF), Text Files sand save them to Database

Database Design

Here I have created a Database called dbFiles and it has a table called tblFiles.

It has 4 Fields. The complete description is available in the Figure below



Table Structure

As you can see above for the id field I have set Identity Specification true, so that it automatically increments itself.

 

Field

Relevance

id

Identification Number

Name

File Name

Content Type

Content Type for the file

Data

File stored as Binary Data

 

Connection String

 

Below is the connection string to the database. You can modify it to suit yours

<connectionStrings>

<add name="conString" connectionString="Data Source=.\SQLEXPRESS;database=dbFiles; Integrated Security=true"/>

</connectionStrings >

 

To start with I have added a FileUpload control, a button and a Label to show messages

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

<asp:Button ID="btnUpload" runat="server" Text="Upload"

OnClick="btnUpload_Click" />

<br />

<asp:Label ID="lblMessage" runat="server" Text=""

Font-Names = "Arial"></asp:Label>


And here is the snippet which is called on the Upload Button Click event

C#     

protected void btnUpload_Click(object sender, EventArgs e)

{

    // Read the file and convert it to Byte Array

    string filePath = FileUpload1.PostedFile.FileName;  

    string filename = Path.GetFileName(filePath);

    string ext = Path.GetExtension(filename);

    string contenttype = String.Empty;

 

    //Set the contenttype based on File Extension

    switch(ext)

    {

        case ".doc":

            contenttype = "application/vnd.ms-word";

            break;

        case ".docx":

            contenttype = "application/vnd.ms-word";

            break;

        case ".xls":

            contenttype = "application/vnd.ms-excel";

            break;

        case ".xlsx":

            contenttype = "application/vnd.ms-excel";

            break;

        case ".jpg":

            contenttype = "image/jpg";

            break;

        case ".png":

            contenttype = "image/png";

            break;

        case ".gif":

            contenttype = "image/gif";

            break;

        case ".pdf":

            contenttype = "application/pdf";

            break;

    }

    if (contenttype != String.Empty)

    {

 

        Stream fs = FileUpload1.PostedFile.InputStream;

        BinaryReader br = new BinaryReader(fs);

        Byte[] bytes = br.ReadBytes((Int32)fs.Length);

 

        //insert the file into database

        string strQuery = "insert into tblFiles(Name, ContentType, Data)" +

           " values (@Name, @ContentType, @Data)";

        SqlCommand cmd = new SqlCommand(strQuery);

        cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename;

        cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value

          = contenttype;

        cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;

        InsertUpdateData(cmd);

        lblMessage.ForeColor = System.Drawing.Color.Green;  

        lblMessage.Text = "File Uploaded Successfully";

    }

    else

    {

        lblMessage.ForeColor = System.Drawing.Color.Red;   

        lblMessage.Text = "File format not recognised." +

          " Upload Image/Word/PDF/Excel formats";

    }

}

 

 

VB.Net

  

Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As EventArgs)

  ' Read the file and convert it to Byte Array

  Dim filePath As String = FileUpload1.PostedFile.FileName

  Dim filename As String = Path.GetFileName(filePath)

  Dim ext As String = Path.GetExtension(filename)

  Dim contenttype As String = String.Empty

 

  'Set the contenttype based on File Extension

  Select Case ext

    Case ".doc"

      contenttype = "application/vnd.ms-word"

      Exit Select

    Case ".docx"

      contenttype = "application/vnd.ms-word"

      Exit Select

    Case ".xls"

      contenttype = "application/vnd.ms-excel"

      Exit Select

    Case ".xlsx"

      contenttype = "application/vnd.ms-excel"

      Exit Select

    Case ".jpg"

      contenttype = "image/jpg"

      Exit Select

    Case ".png"

      contenttype = "image/png"

      Exit Select

    Case ".gif"

      contenttype = "image/gif"

      Exit Select

    Case ".pdf"

      contenttype = "application/pdf"

      Exit Select

    End Select

    If contenttype <> String.Empty Then

      Dim fs As Stream = FileUpload1.PostedFile.InputStream

      Dim br As New BinaryReader(fs)

      Dim bytes As Byte() = br.ReadBytes(fs.Length)

 

      'insert the file into database

       Dim strQuery As String = "insert into tblFiles" _

       & "(Name, ContentType, Data)" _

       & " values (@Name, @ContentType, @Data)"

       Dim cmd As New SqlCommand(strQuery)

       cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename

       cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value _

       = contenttype

       cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes

       InsertUpdateData(cmd)

       lblMessage.ForeColor = System.Drawing.Color.Green

       lblMessage.Text = "File Uploaded Successfully"

     Else

       lblMessage.ForeColor = System.Drawing.Color.Red

       lblMessage.Text = "File format not recognised." _

       & " Upload Image/Word/PDF/Excel formats"

     End If

  End Sub

 

The above code simply reads the uploaded File as Stream and then converts the Stream to Byte array using Binary Reader and then the finally the byte arrays is saved to the database InsertUpdateData method executes the query to save the data in database

The InsertUpdateData function is given below

    

C#

private Boolean InsertUpdateData(SqlCommand cmd)

{

    String strConnString = System.Configuration.ConfigurationManager

    .ConnectionStrings["conString"].ConnectionString;

    SqlConnection con = new SqlConnection(strConnString);

    cmd.CommandType = CommandType.Text;

    cmd.Connection = con;

    try

    {

        con.Open();

        cmd.ExecuteNonQuery();

        return true;

    }

    catch (Exception ex)

    {

        Response.Write(ex.Message);

        return false;

    }

    finally

    {

        con.Close();

        con.Dispose();

    }

}

 

VB.Net

 

Public Function InsertUpdateData(ByVal cmd As SqlCommand) As Boolean

    Dim strConnString As String = System.Configuration.

    ConfigurationManager.ConnectionStrings("conString").ConnectionString

    Dim con As New SqlConnection(strConnString)

    cmd.CommandType = CommandType.Text

    cmd.Connection = con

    Try

      con.Open()

      cmd.ExecuteNonQuery()

      Return True

    Catch ex As Exception

      Response.Write(ex.Message)

      Return False

    Finally

      con.Close()

      con.Dispose()

    End Try

End Function

 

This completes the article. You can download the source code in VB.Net and C# from the link below.

UploadFilesDataBase.zip (1.31 mb)