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

watch_later Wednesday, October 2, 2019
comment 1 Comment

Introduction


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 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.

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 file path into the database and while they want to access any file or document, they fetch file path for specific file from the database and based on that file path they get file from the folder. Suppose, unfortunately, 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 file 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#, 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 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 SQL server datbase you need to execute 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, FileData where FileID is the primary key of the table.

Now, we will write the following HTML code into aspx file, where we will design our form with a dropdown box for employee selection, file upload control, 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 bootstrap class we designed a form using dropdown box for employee selection,file upload control for brows a files from system, an upload button for convert and upload files into database in VARBINARY format as well as a grid view for display uploaded records.

Before, start actual code needs 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, creation of database connection we need to import 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 into 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 a name of the uploaded file and stored in a local variable empFilename, then we have fetched and stored content type of the uploaded file and stored in variable FileContentType, then reads contents of the file and store in-stream variable s and then creates an object of binary reader class that reads primitive data types as binary values in specific encoding and using that read file content and stored binary data in byte array. Then we have created a database connection and command object as well as also prepared a parameterized SQL query for insert records into the tblEmpIdentity table and pass required parameters with values and execute SQL statement and insert record into SQL server database.

Finally, As per the requirement described above, we need to display uploaded files or documents of the employees into 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 connection string from web.config file and create an object of SQL connection class for database connection and then creates an object for SQL command class, preapred 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 EventArgsHandles Me.Load
        If Not IsPostBack Then
            GetEmployees()
        End If
    End Sub

Output

Upload and Save File in Database

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 file into the SQL server database in VARBINARY data.  

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.

avatar

Please keep sharing such great piece of article so we can get more knowledge about this type of topic. w3codemaster

delete October 29, 2019 at 8:26 PM



sentiment_satisfied Emoticon