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