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