Upload and Save File in Database as VARBINARY Data in ASP.NET

This article gives an explanation about how to upload and save the file in the database as VARBINARY Data in asp.net using c# and vb.net. Here I'll also explain how to upload files in asp.net as well as how to save the file in the SQL Server database as VARBINARY data.

In my previous article, I explained Merge Multiple PDF Files Into a Single PDF Using Itextsharp in C# and how to read CSV files using C# and Export All The Excel Sheets to DataSet in C# and VB.NET as well as Export Dataset/Datatable to CSV File Using C# and VB.NET and many other helpful articles on ASP.NET technology.

how to save file path in database using c#, upload file and save in database in MVC, how to upload and download files using database in c#.net windows application, save file in database c# entity framework, upload file from local machine to server in c#, how to upload multiple files in database using asp.net c#, save file in sql server database using c#, How to save image in database in binary format in asp net MVC c#

Upload and Save File in Database as VARBINARY Data in ASP.NET using C# and VB.NET

While we working with any web, windows, or mobile application sometimes we need to upload/save some documents or files such as Word, Excel, CSV, PDF, images, audio and video, and many other files into a database.

Basically, many developers save original files or documents in a specific folder and save the file paths into the database and while they want to access any file or document, they fetch the file path for a specific file from the database and based on that file path they get the file from the folder.

Suppose, unfortunately, a file is deleted or renamed in the folder then they can not able to access those files or documents. So, today in this article I'll show you how to save files directly into the database in VARBINARY data so, you can access any file from the database.

Here, I'll explain how to convert any files such as Word, Excel, CSV, PDF, images, audio and video, and many other files into VARBINARY data and save into the SQL server database with a simple, easy and understandable example using C# and VB.NET with bootstrep4.

Requirement

1) Explain file upload in ASP.NET using C#, and VB.NET with Bootstrap 4.

2) Save uploaded files or documents into the SQL server database in VARBINARY format.

3) Display uploaded files in a grid view.

Implementation

Let,s start with an example of the employee management system, Here we will save employee-wise documents of employees such as the profile picture, Identity of the employee such as election card as well as other documents of employees such as agreements, address proof and etc into the database.

To save VARBINARY data of the uploaded documents of the employee into the SQL server database, first, we need to create a table into the database, so first we will create a table with the name tblEmpIdentity. To create a table in the SQL server database you need to execute the following SQL script as given below.

Create Table

CREATE TABLE [dbo].[tblEmpIdentity] (
    [FileID]          INT             IDENTITY (1, 1) NOT NULL,
    [EmployeeID]      INT             NULL,
    [EmployeeName]    VARCHAR (50)    NULL,
    [DocumentName]    VARCHAR (50)    NULL,
    [FileName]        VARCHAR (50)    NULL,
    [FileContentType] NVARCHAR (200)  NULL,
    [FileData ]       VARBINARY (MAX) NULL,
    CONSTRAINT [PK_tblEmpIdentity] PRIMARY KEY CLUSTERED ([FileID] ASC)
);

As you can see in the above script, here we created a column for FileID, EmployeeID, EmployeeName, DocumentName, FileName, ContentType, and FileData where FileID is the primary key of the table.

Now, we will write the following HTML code into an aspx file, where we will design our form with a dropdown box for employee selection, file upload control, and upload button as well as one grid view to display information of uploaded files of the employee.

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>File Upload Example</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" />
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"></script>
    <script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>
</head>
<body>
    <form id="form1" runat="server">
        <div class=" container">
            <br />
            <h1>File Upload Example</h1>
            <br />
            <div class="form-row">
                <div class="col">
                    <asp:DropDownList id="ddlEmployees" runat="server" CssClass="form-control dropdown">
                        <asp:ListItem value="0">-- Select Employee --</asp:ListItem>
                        <asp:ListItem value="1">Nikunj Satasiya</asp:ListItem>
                        <asp:ListItem value="2">Hiren Dobariya</asp:ListItem>
                        <asp:ListItem value="3">Vivek Ghadiya</asp:ListItem>
                        <asp:ListItem value="3">Shreya Patel</asp:ListItem>
                    </asp:DropDownList>
                </div>
                <div class="col">
                    <asp:TextBox ID="txtDocument" runat="server" CssClass="form-control" placeholder="DocumentName"></asp:TextBox>
                </div>
 
            </div>
            <br />
            <div class=" row">
                <asp:FileUpload ID="FileUploadEmployees" runat="server" CssClass="btn" />
            </div>
            <br />
            <asp:Button ID="btnUploadFile" runat="server" Text="Upload" CssClass="btn btn-primary" OnClick="btnUploadFile_click" />
            <hr />
            <asp:GridView ID="grdEmployees" runat="server" Width="100%" CssClass="table table-bordered" AutoGenerateColumns="false">
                <Columns>
                    <asp:BoundField DataField="FileID " Visible="false" HeaderText="FileID " />
                    <asp:BoundField DataField="EmployeeName" HeaderText="EmployeeName" />
                    <asp:BoundField DataField="DocumentName" HeaderText="DocumentName" />
                    <asp:BoundField DataField="FileName" HeaderText="FileName" />
                    <asp:BoundField DataField="FileData" HeaderText="FileData" />
                </Columns>
            </asp:GridView>
        </div>
    </form>
</body>
</html>

As you can see in the HTML code written above, where we have linked CSS and Javascript for bootsrap4, and with help of the bootstrap class we designed a form using the dropdown box for employee selection, file upload control for brows files from the system, an upload button for convert and upload files into the database in VARBINARY format as well as a grid view for display uploaded records.

Before starting the actual code need to create a database connection with our web application and for that, we need to write the following connection string into the web. config file.

Web.Config

<connectionStrings>
    <add name="ConnectionStrings" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=E:\Nikunj\codingvila\bin\Debug\DBcodingvila.mdf;Integrated Security=True;Connect Timeout=30"/>
  </connectionStrings >

After, the creation of the database connection we need to import the following namespaces into code-behind.

Namespaces

C#

using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.NET

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Now, We need to write a C# and VB.NET code for brows and read file content in BINARY data and store it in the SQL server database. and for that, we need to write the following code in on click event of the upload button.

C#

protected void btnUploadFile_click(object sender, EventArgs e)
    {
        //fetch the name of the file
        string empFilename = Path.GetFileName(FileUploadEmployees.PostedFile.FileName);
        //fetch the file content type of the file
        string FilecontentType = FileUploadEmployees.PostedFile.ContentType;
        //reads a content of the file
        using (Stream s = FileUploadEmployees.PostedFile.InputStream)
        {
            using (BinaryReader br = new BinaryReader(s))
            {
                byte[] Databytes = br.ReadBytes((Int32)s.Length);
                //fetch connection string from the web.config file
                string ConnectionStrings = ConfigurationManager.ConnectionStrings["ConnectionStrings"].ConnectionString;
                //create a database connection object
                using (SqlConnection con = new SqlConnection(ConnectionStrings))
                {
                    string query = "INSERT INTO tblEmpIdentity VALUES (@EmployeeID, @EmployeeName, @DocumentName, @FileName, @FileContentType, @FileData)";
                    //create an object for SQL command class
                    using (SqlCommand cmd = new SqlCommand(query))
                    {
                        cmd.Connection = con;
                        cmd.Parameters.AddWithValue("@EmployeeID", ddlEmployees.SelectedItem.Value);
                        cmd.Parameters.AddWithValue("@EmployeeName", ddlEmployees.SelectedItem.Text);
                        cmd.Parameters.AddWithValue("@DocumentName", txtDocument.Text);
                        cmd.Parameters.AddWithValue("@FileName", empFilename);
                        cmd.Parameters.AddWithValue("@FileContentType", FilecontentType);
                        cmd.Parameters.AddWithValue("@FileData", Databytes);
                        //open database connection
                        con.Open();
                        //execute SQL statement 
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                }
            }
        }
        Response.Redirect(Request.Url.AbsoluteUri);
    }

VB.NET

Protected Sub btnUploadFile_click(sender As Object, e As EventArgs)
        'fetch the name of the file
        Dim empFilename As String = Path.GetFileName(FileUploadEmployees.PostedFile.FileName)
        'fetch the file content type of the file
        Dim FilecontentType As String = FileUploadEmployees.PostedFile.ContentType
        'reads a content of the file
        Using s As Stream = FileUploadEmployees.PostedFile.InputStream
            Using br As New BinaryReader(s)
                Dim Databytes As Byte() = br.ReadBytes(CType(s.Length, Int32))
                'fetch connection string from the web.config file
                Dim ConnectionStrings As String = ConfigurationManager.ConnectionStrings("ConnectionStrings").ConnectionString
                'create a database connection object
                Using con As New SqlConnection(ConnectionStrings)
                    Dim query As String = "INSERT INTO tblEmpIdentity VALUES  (@EmployeeID, @EmployeeName, @DocumentName, @FileName, @FileContentType, @FileData)"
                    Using cmd As New SqlCommand(query)
                        cmd.Connection = con
                        cmd.Parameters.AddWithValue("@EmployeeID", ddlEmployees.SelectedItem.Value)
                        cmd.Parameters.AddWithValue("@EmployeeName", ddlEmployees.SelectedItem.Text)
                        cmd.Parameters.AddWithValue("@DocumentName", txtDocument.Text)
                        cmd.Parameters.AddWithValue("@FileName", empFilename)
                        cmd.Parameters.AddWithValue("@FileContentType", FilecontentType)
                        cmd.Parameters.AddWithValue("@FileData", Databytes)
                        'open database connection
                        con.Open()
                        'execute SQL statement 
                        cmd.ExecuteNonQuery()
                        con.Close()
                    End Using
                End Using
            End Using
        End Using
        Response.Redirect(Request.Url.AbsoluteUri)
    End Sub

Explanation

As you can see in the written code above first we fetched the name of the uploaded file and stored it in a local variable empFilename.

Then we fetched and stored the content type of the uploaded file and stored it in the variable FileContentType.

Reads contents of the file and stores in-stream variables and then creates an object of binary reader class that reads primitive data types as binary values in specific encoding and uses that read file content and store binary data in a byte array.

Then we created a database connection and command object as well as also prepared a parameterized SQL query for inserting records into the tblEmpIdentity table and passing required parameters with values and executing SQL statements and inserting a record into the SQL server database.

Finally, As per the requirement described above, we need to display uploaded files or documents of the employees in the grid view, so we will fetch all the records from the tblEmpIdentity table and bind those records with the grid view.

C#

private void GetEmployees()
    {
        //fetch connection string from the web.config file
        string ConnectionStrings = ConfigurationManager.ConnectionStrings["ConnectionStrings"].ConnectionString;
        //create a database connection object
        using (SqlConnection Connection = new SqlConnection(ConnectionStrings))
        {
            //create an object for SQL command class
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "SELECT FileID, EmployeeName, DocumentName, FileName, CONVERT(VARCHAR(50), FileData, 1) AS FileData  from tblEmpIdentity WITH (NOLOCK)";
                cmd.Connection = Connection;
                //open database connection
                Connection.Open();
                //execute SQL statement 
                grdEmployees.DataSource = cmd.ExecuteReader();
                grdEmployees.DataBind();
                Connection.Close();
            }
        }
    }

VB.NET

Private Sub GetEmployees()
        'fetch connection string from the web.config file
        Dim ConnectionStrings As String = ConfigurationManager.ConnectionStrings("ConnectionStrings").ConnectionString
        'create a database connection object
        Using con As New SqlConnection(ConnectionStrings)
            'create an object for SQL command class
            Using cmd As New SqlCommand()
                cmd.CommandText = "SELECT FileID, EmployeeName, DocumentName, FileName, CONVERT(VARCHAR(50), FileData, 1) AS FileData  from tblEmpIdentity WITH (NOLOCK)"
                cmd.Connection = con
                'open database connection
                con.Open()
                'execute SQL statement 
                grdEmployees.DataSource = cmd.ExecuteReader()
                grdEmployees.DataBind()
                con.Close()
            End Using
        End Using
    End Sub

Explanation

As you can see in the written code above, where we have created a function GetEmployees for display records from the tblEmpIdentity table.

We have fetched the connection string from the web. config file and created an object of SQL connection class for database connection and then creates an object for SQL command class, prepared a SQL statement for fetch records from the database, and finally execute created SQL statement and assigned result set to grid view as a data source.

Now, we have to call the created method above on the load event of the page to view the inserted records into the database.

C#

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GetEmployees();
        }
    }

VB.NET

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            GetEmployees()
        End If
    End Sub

Output

Summary

In this article, we learned how to upload files in ASP.NET using C# and VB.NET, as well as also learned how to save files into the SQL server database in VARBINARY data.

Tags:

save file in database postgresql

save file in database sql server

save file in database sql server c#

how to save file in database using spring boot

how to save a database in mysql

how to save database in ms access

how to store uploaded files in a sql database

c# save file to database entity framework