.Net Tips, C# Tips : Insert or Save file into SQL Server Database Table with C# Examples and VB.Net Examples

There is a need in application to upload a file and store into SQL Server Database table.
You can do this very simple way.
File is stored in sql server as binary data. You need to set "varbinary" data type of the column in which you want to store file. You need to take "varbinary(MAX)" data type.

Here is example for this.
In this example we take one "product_master" table. This tables contains columns like "product_id" , "product_name", "product_document_filename" and "product_document".
This "product_document" column store whole file as binary. so that we need to set it's datatype ""varbinary(MAX)". We are inserting uploaded file name in "product_document_filename" column whose datatype is nvarchar(1000).
In this example we take product name as textbox and for Upload file we take FileUpload control.

ASPX Code :

        <table>
            <tr>
                <td>
                    Product Title :
                </td>
                <td>
                    <asp:TextBox runat="server" ID="txtProductTitle"  ></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Product Document :
                </td>
                <td>
                    <asp:FileUpload runat="server" ID="fuDocument" />
                </td>
            </tr>
        </table>
        <asp:Button runat="server" ID="btnSave" Text="Save" onclick="btnSave_Click" />

C# Examples :

    protected void btnSave_Click(object sender, EventArgs e)
    {
        SqlCommand objCmd = new SqlCommand();
        SqlConnection objConn = new SqlConnection();
        objConn.ConnectionString = @"Data Source=.\SQLEXPRESS;" +
                                   "Initial Catalog=TempDatabase;" +
                                   "User ID=sa;Password=sa;";

        objConn.Open();
        objCmd.Connection = objConn;

        objCmd.CommandText = "insert into product_master(product_name,product_document_filename,product_document) values(@product_name,@product_document_filename,@product_document)";
        objCmd.Parameters.AddWithValue("@product_name", txtProductTitle.Text.Trim());

        if (fuDocument.HasFile == true)
        {
            objCmd.Parameters.AddWithValue("@product_document_filename", fuDocument.FileName);
            objCmd.Parameters.AddWithValue("@product_document", fuDocument.FileBytes);
        }
        else
        {
            objCmd.Parameters.AddWithValue("@product_document_filename", DBNull.Value);
            objCmd.Parameters.AddWithValue("@product_document", DBNull.Value);
        }
        objCmd.ExecuteNonQuery();
        objConn.Close();
    }

VB.net Examples :

    Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSave.Click
        Dim objCmd As New SqlCommand()
        Dim objConn As New SqlConnection()
        objConn.ConnectionString = "Data Source=.\SQLEXPRESS;" & "Initial Catalog=TempDatabase;" & "User ID=sa;Password=sa;"

        objConn.Open()
        objCmd.Connection = objConn

        objCmd.CommandText = "insert into product_master(product_name,product_document_filename,product_document) values(@product_name,@product_document_filename,@product_document)"
        objCmd.Parameters.AddWithValue("@product_name", txtProductTitle.Text.Trim())

        If fuDocument.HasFile = True Then
            objCmd.Parameters.AddWithValue("@product_document_filename", fuDocument.FileName)
            objCmd.Parameters.AddWithValue("@product_document", fuDocument.FileBytes)
        Else
            objCmd.Parameters.AddWithValue("@product_document_filename", DBNull.Value)
            objCmd.Parameters.AddWithValue("@product_document", DBNull.Value)
        End If
        objCmd.ExecuteNonQuery()
        objConn.Close()
    End Sub

Output (Database Table Structure) : 

Output (Create Product Screen) : 

Output (Inserted Product Details in Table) : 

 

This is very useful .Net Tips.

For Beginning .Net articles. Click Here...

Learn other ADO.Net Examples over here. Click Here...

Note : Give Us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.