Post
Ask Question

Upload And Save File In Database As VARBINARY Data In ASP.NET Using C# And VB.NET

Introduction

 
This article gives an explanation about how to upload and save a 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.
 
 
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 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 are 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. This will allow you to 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 them into the SQL server database with a simple, easy, and understandable example using C# and VB.NET with Bootstrap 4.
 
Requirement
  • File upload in ASP.NET using C# and VB.NET with Bootstrap 4.
  • Save uploaded files or documents into the SQL server database in VARBINARY format.
  • Display uploaded files in a grid view.

Implementation

 
Let's start with an example of the employee management system. Here we will save employee-related  documents 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 the SQL server database you need to execute the following SQL script as given below.
 
Create Table
  1. CREATE TABLE [dbo].[tblEmpIdentity] (  
  2.     [FileID]          INT             IDENTITY (1, 1) NOT NULL,  
  3.     [EmployeeID]      INT             NULL,  
  4.     [EmployeeName]    VARCHAR (50)    NULL,  
  5.     [DocumentName]    VARCHAR (50)    NULL,  
  6.     [FileName]        VARCHAR (50)    NULL,  
  7.     [FileContentType] NVARCHAR (200)  NULL,  
  8.     [FileData ]       VARBINARY (MAX) NULL,  
  9.     CONSTRAINT [PK_tblEmpIdentity] PRIMARY KEY CLUSTERED ([FileID] ASC)  
  10. );  
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
  1. <html xmlns="http://www.w3.org/1999/xhtml">    
  2. <head id="Head1" runat="server">    
  3.     <title>File Upload Example</title>    
  4.     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" />    
  5.     <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>    
  6.     <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"></script>    
  7.     <script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>    
  8. </head>    
  9. <body>    
  10.     <form id="form1" runat="server">    
  11.         <div class=" container">    
  12.             <br />    
  13.             <h1>File Upload Example</h1>    
  14.             <br />    
  15.             <div class="form-row">    
  16.                 <div class="col">    
  17.                     <asp:DropDownList id="ddlEmployees" runat="server" CssClass="form-control dropdown">    
  18.                         <asp:ListItem value="0">-- Select Employee --</asp:ListItem>    
  19.                         <asp:ListItem value="1">Nikunj Satasiya</asp:ListItem>    
  20.                         <asp:ListItem value="2">Hiren Dobariya</asp:ListItem>    
  21.                         <asp:ListItem value="3">Vivek Ghadiya</asp:ListItem>    
  22.                         <asp:ListItem value="3">Shreya Patel</asp:ListItem>    
  23.                     </asp:DropDownList>    
  24.                 </div>    
  25.                 <div class="col">    
  26.                     <asp:TextBox ID="txtDocument" runat="server" CssClass="form-control" placeholder="DocumentName"></asp:TextBox>    
  27.                 </div>    
  28.      
  29.             </div>    
  30.             <br />    
  31.             <div class=" row">    
  32.                 <asp:FileUpload ID="FileUploadEmployees" runat="server" CssClass="btn" />    
  33.             </div>    
  34.             <br />    
  35.             <asp:Button ID="btnUploadFile" runat="server" Text="Upload" CssClass="btn btn-primary" OnClick="btnUploadFile_click" />    
  36.             <hr />    
  37.             <asp:GridView ID="grdEmployees" runat="server" Width="100%" CssClass="table table-bordered" AutoGenerateColumns="false">    
  38.                 <Columns>    
  39.                     <asp:BoundField DataField="FileID " Visible="false" HeaderText="FileID " />    
  40.                     <asp:BoundField DataField="EmployeeName" HeaderText="EmployeeName" />    
  41.                     <asp:BoundField DataField="DocumentName" HeaderText="DocumentName" />    
  42.                     <asp:BoundField DataField="FileName" HeaderText="FileName" />    
  43.                     <asp:BoundField DataField="FileData" HeaderText="FileData" />    
  44.                 </Columns>    
  45.             </asp:GridView>    
  46.         </div>    
  47.     </form>    
  48. </body>    
  49. </html>    
As you can see in the HTML code written above, where we have linked CSS and Javascript for Bootstrap 4 and with help of bootstrap class we designed a form using the dropdown box for employee selection. File upload control is for browsing a file from the system, and upload button is for converting and uploading files into the database in VARBINARY format as well as a grid view for displaying uploaded records.
 
Before we start the actual code we 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
  1. <connectionStrings>    
  2.     <add name="ConnectionStrings" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=E:\Nikunj\codingvila\bin\Debug\DBcodingvila.mdf;Integrated Security=True;Connect Timeout=30"/>    
  3.   </connectionStrings >    
After the creation of a database connection we need to import the following namespaces into code-behind.
 
Namespaces
 
C#
  1. using System.IO;  
  2. using System.Data;  
  3. using System.Data.SqlClient;  
  4. using System.Configuration;  
VB.NET
  1. Imports System.IO  
  2. Imports System.Data  
  3. Imports System.Data.SqlClient  
  4. Imports System.Configuration  
Now, we need to write a C# code for browsing and reading file content in BINARY data and storing it into the SQL server database. and for that, we need to write the following code in the on click event of the upload button.
 
C#
  1. protected void btnUploadFile_click(object sender, EventArgs e)  
  2.     {  
  3.         //fetch the name of the file  
  4.         string empFilename = Path.GetFileName(FileUploadEmployees.PostedFile.FileName);  
  5.         //fetch the file content type of the file  
  6.         string FilecontentType = FileUploadEmployees.PostedFile.ContentType;  
  7.         //reads a content of the file  
  8.         using (Stream s = FileUploadEmployees.PostedFile.InputStream)  
  9.         {  
  10.             using (BinaryReader br = new BinaryReader(s))  
  11.             {  
  12.                 byte[] Databytes = br.ReadBytes((Int32)s.Length);  
  13.                 //fetch connection string from the web.config file  
  14.                 string ConnectionStrings = ConfigurationManager.ConnectionStrings["ConnectionStrings"].ConnectionString;  
  15.                 //create a database connection object  
  16.                 using (SqlConnection con = new SqlConnection(ConnectionStrings))  
  17.                 {  
  18.                     string query = "INSERT INTO tblEmpIdentity VALUES (@EmployeeID, @EmployeeName, @DocumentName, @FileName, @FileContentType, @FileData)";  
  19.                     //create an object for SQL command class  
  20.                     using (SqlCommand cmd = new SqlCommand(query))  
  21.                     {  
  22.                         cmd.Connection = con;  
  23.                         cmd.Parameters.AddWithValue("@EmployeeID", ddlEmployees.SelectedItem.Value);  
  24.                         cmd.Parameters.AddWithValue("@EmployeeName", ddlEmployees.SelectedItem.Text);  
  25.                         cmd.Parameters.AddWithValue("@DocumentName", txtDocument.Text);  
  26.                         cmd.Parameters.AddWithValue("@FileName", empFilename);  
  27.                         cmd.Parameters.AddWithValue("@FileContentType", FilecontentType);  
  28.                         cmd.Parameters.AddWithValue("@FileData", Databytes);  
  29.                         //open database connection  
  30.                         con.Open();  
  31.                         //execute SQL statement   
  32.                         cmd.ExecuteNonQuery();  
  33.                         con.Close();  
  34.                     }  
  35.                 }  
  36.             }  
  37.         }  
  38.         Response.Redirect(Request.Url.AbsoluteUri);  
  39.     }  
VB.NET 
  1. Protected Sub btnUploadFile_click(sender As Object, e As EventArgs)  
  2.         'fetch the name of the file  
  3.         Dim empFilename As String = Path.GetFileName(FileUploadEmployees.PostedFile.FileName)  
  4.         'fetch the file content type of the file  
  5.         Dim FilecontentType As String = FileUploadEmployees.PostedFile.ContentType  
  6.         'reads a content of the file  
  7.         Using s As Stream = FileUploadEmployees.PostedFile.InputStream  
  8.             Using br As New BinaryReader(s)  
  9.                 Dim Databytes As Byte() = br.ReadBytes(CType(s.Length, Int32))  
  10.                 'fetch connection string from the web.config file  
  11.                 Dim ConnectionStrings As String = ConfigurationManager.ConnectionStrings("ConnectionStrings").ConnectionString  
  12.                 'create a database connection object  
  13.                 Using con As New SqlConnection(ConnectionStrings)  
  14.                     Dim query As String = "INSERT INTO tblEmpIdentity VALUES  (@EmployeeID, @EmployeeName, @DocumentName, @FileName, @FileContentType, @FileData)"  
  15.                     Using cmd As New SqlCommand(query)  
  16.                         cmd.Connection = con  
  17.                         cmd.Parameters.AddWithValue("@EmployeeID", ddlEmployees.SelectedItem.Value)  
  18.                         cmd.Parameters.AddWithValue("@EmployeeName", ddlEmployees.SelectedItem.Text)  
  19.                         cmd.Parameters.AddWithValue("@DocumentName", txtDocument.Text)  
  20.                         cmd.Parameters.AddWithValue("@FileName", empFilename)  
  21.                         cmd.Parameters.AddWithValue("@FileContentType", FilecontentType)  
  22.                         cmd.Parameters.AddWithValue("@FileData", Databytes)  
  23.                         'open database connection  
  24.                         con.Open()  
  25.                         'execute SQL statement   
  26.                         cmd.ExecuteNonQuery()  
  27.                         con.Close()  
  28.                     End Using  
  29.                 End Using  
  30.             End Using  
  31.         End Using  
  32.         Response.Redirect(Request.Url.AbsoluteUri)  
  33.     End Sub   

Explanation

 
As you can see in the written code above, first we fetched a 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 variable FileContentType. Then we read the contents of the file and stored in-stream variables and then created an object of binary reader class that reads primitive data types as binary values in specific encoding and uses that file content and stored binary data in a byte array. Then we have created a database connection and command object as well as prepared a parameterized SQL query for inserting records into the tblEmpIdentity table and passing required parameters with values and executes SQL statement and inserts 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 into the grid view, so we will fetch all the records from the tblEmpIdentity table and bind those records with the grid view.
 
C#
  1. private void GetEmployees()  
  2.     {  
  3.         //fetch connection string from the web.config file  
  4.         string ConnectionStrings = ConfigurationManager.ConnectionStrings["ConnectionStrings"].ConnectionString;  
  5.         //create a database connection object  
  6.         using (SqlConnection Connection = new SqlConnection(ConnectionStrings))  
  7.         {  
  8.             //create an object for SQL command class  
  9.             using (SqlCommand cmd = new SqlCommand())  
  10.             {  
  11.                 cmd.CommandText = "SELECT FileID, EmployeeName, DocumentName, FileName, CONVERT(VARCHAR(50), FileData, 1) AS FileData  from tblEmpIdentity WITH (NOLOCK)";  
  12.                 cmd.Connection = Connection;  
  13.                 //open database connection  
  14.                 Connection.Open();  
  15.                 //execute SQL statement   
  16.                 grdEmployees.DataSource = cmd.ExecuteReader();  
  17.                 grdEmployees.DataBind();  
  18.                 Connection.Close();  
  19.             }  
  20.         }  
  21.     }  
VB.NET
  1. Private Sub GetEmployees()  
  2.         'fetch connection string from the web.config file  
  3.         Dim ConnectionStrings As String = ConfigurationManager.ConnectionStrings("ConnectionStrings").ConnectionString  
  4.         'create a database connection object  
  5.         Using con As New SqlConnection(ConnectionStrings)  
  6.             'create an object for SQL command class  
  7.             Using cmd As New SqlCommand()  
  8.                 cmd.CommandText = "SELECT FileID, EmployeeName, DocumentName, FileName, CONVERT(VARCHAR(50), FileData, 1) AS FileData  from tblEmpIdentity WITH (NOLOCK)"  
  9.                 cmd.Connection = con  
  10.                 'open database connection  
  11.                 con.Open()  
  12.                 'execute SQL statement   
  13.                 grdEmployees.DataSource = cmd.ExecuteReader()  
  14.                 grdEmployees.DataBind()  
  15.                 con.Close()  
  16.             End Using  
  17.         End Using  
  18.     End Sub  

Explanation

 
As you can see in the written code above, we have created a function GetEmployees for displaying records from the tblEmpIdentity table. We have fetched connection string from web.config file and created an object of SQL connection class for database connection and then created an object for SQL command class, prepared a SQL statement for fetching records from the database and finally executed the created SQL statement and assigned  a 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#
  1. protected void Page_Load(object sender, EventArgs e)  
  2.     {  
  3.         if (!IsPostBack)  
  4.         {  
  5.             GetEmployees();  
  6.         }  
  7.     }  
VB.NET  
  1. Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load  
  2.         If Not IsPostBack Then  
  3.             GetEmployees()  
  4.         End If  
  5.     End Sub  

Output
 

Summary

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


Similar Articles
Codingvila
Codingvila is an educational website, developed to help tech specialists/beginners.