How To Save Uploaded File In Database In Vb.net

Introduction

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

While nosotros working with any spider web, windows, or mobile application sometimes we need to upload/salve some documents or files such as Word, Excel, CSV, PDF, images, audio and video, and many other files into a database. Basically, many developers salve 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 become the file from the binder. Suppose, unfortunately, a file is deleted or renamed in the folder then they are not able to access those files or documents. Then, today in this commodity I'll testify yous how to relieve files directly into the database in VARBINARY data. This will allow you to access any file from the database.

Hither, I'll explain how to catechumen whatever files such as Word, Excel, CSV, PDF, images, audio and video, and many other files into VARBINARY data and salve them into the SQL server database with a uncomplicated, easy, and understandable example using C# and VB.NET with Bootstrap iv.

Requirement

File upload in ASP.Net using C# and VB.Cyberspace with Bootstrap 4.
Save uploaded files or documents into the SQL server database in VARBINARY format.
Display uploaded files in a filigree view.

Implementation

Let's outset with an example of the employee management system. Hither nosotros will save employee-related documents such as contour moving-picture show, identity of the employee such as election card too as other documents of employees such equally 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 demand to create a table into the database, so starting time we will create a table with the proper noun tblEmpIdentity. To create a tabular array in the SQL server database you need to execute the post-obit SQL script equally given below.

Create Table

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

As you can see in the above script, hither nosotros 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 lawmaking into aspx file, where we will blueprint our form with a dropdown box for employee selection, file upload control, upload button likewise as i grid view to display information of uploaded files of the employee.

HTML

<html xmlns= "http://world wide web.w3.org/1999/xhtml" >
<head id="Head1" runat= "server" >
<title>File Upload Example</championship>
<link rel="stylesheet" href= "https://maxcdn.bootstrapcdn.com/bootstrap/four.3.1/css/bootstrap.min.css" />
<script blazon="text/javascript" src= "https://ajax.googleapis.com/ajax/libs/jquery/iii.4.1/jquery.min.js" ></script>
<script type="text/javascript" src= "https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.vii/umd/popper.min.js" ></script>
<script type="text/javascript" src= "https://maxcdn.bootstrapcdn.com/bootstrap/4.3.ane/js/bootstrap.min.js" ></script>
</head>
<trunk>
<form id="form1" runat= "server" >
<divclass = " container" >
<br />
<h1>File Upload Example</h1>
<br />
<divgrade = "form-row" >
<divclass = "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>
<divclass = "col" >
<asp:TextBox ID="txtDocument" runat= "server" CssClass= "form-control" placeholder= "DocumentName" ></asp:TextBox>
</div>
</div>
<br />
<divclass = " 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>
</course>
</body>
</html>

As you lot can see in the HTML code written above, where we have linked CSS and Javascript for Bootstrap four and with assistance of bootstrap course we designed a grade using the dropdown box for employee choice. File upload control is for browsing a file from the organization, 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 offset the bodily code we demand to create a database connection with our spider web application and for that, we need to write the following connection string into the spider 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=Truthful;Connect Timeout=thirty" />
</connectionStrings >

Later on the creation of a database connectedness we need to import the post-obit namespaces into lawmaking-behind.

Namespaces

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

VB.Internet

Imports Organization.IO
Imports System.Information
Imports Organisation.Data.SqlClient
Imports System.Configuration

Now, we need to write a C# lawmaking for browsing and reading file content in BINARY data and storing information technology into the SQL server database. and for that, we need to write the following code in the on click event of the upload button.

protected void btnUploadFile_click( object sender, EventArgs due east)
{
string empFilename = Path.GetFileName(FileUploadEmployees.PostedFile.FileName);
string FilecontentType = FileUploadEmployees.PostedFile.ContentType;
using (Stream s = FileUploadEmployees.PostedFile.InputStream)
{
using (BinaryReader br = new BinaryReader(s))
{
byte [] Databytes = br.ReadBytes((Int32)due south.Length);
string ConnectionStrings = ConfigurationManager.ConnectionStrings[ "ConnectionStrings" ].ConnectionString;
using (SqlConnection con = new SqlConnection(ConnectionStrings))
{
string query = "INSERT INTO tblEmpIdentity VALUES (@EmployeeID, @EmployeeName, @DocumentName, @FileName, @FileContentType, @FileData)" ;
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);
con.Open();
cmd.ExecuteNonQuery();
con.Shut();
}
}
}
}
Response.Redirect(Request.Url.AbsoluteUri);
}

VB.Cyberspace

Protected Sub btnUploadFile_click(sender Every bit Object , e Every bit EventArgs)
Dim empFilename As String = Path.GetFileName(FileUploadEmployees.PostedFile.FileName)
Dim FilecontentType Equally String = FileUploadEmployees.PostedFile.ContentType
Using sAs Stream = FileUploadEmployees.PostedFile.InputStream
Using brEqually New BinaryReader(s)
Dim Databytes As Byte () = br.ReadBytes( CType (south.Length, Int32))
Dim ConnectionStrings As Cord = ConfigurationManager.ConnectionStrings( "ConnectionStrings" ).ConnectionString
Using conAs New SqlConnection(ConnectionStrings)
Dim query As String = "INSERT INTO tblEmpIdentity VALUES (@EmployeeID, @EmployeeName, @DocumentName, @FileName, @FileContentType, @FileData)"
Using cmdAs 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)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
Finish Using
Stop Using
Response.Redirect(Request.Url.AbsoluteUri)
Stop Sub

Caption

As y'all can see in the written code above, first we fetched a proper noun of the uploaded file and stored it in a local variable empFilename, so nosotros 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 information types as binary values in specific encoding and uses that file content and stored binary data in a byte array. And so nosotros accept created a database connexion 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.

private void GetEmployees()
{
string ConnectionStrings = ConfigurationManager.ConnectionStrings[ "ConnectionStrings" ].ConnectionString;
using (SqlConnection Connectedness = new SqlConnection(ConnectionStrings))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText ="SELECT FileID, EmployeeName, DocumentName, FileName, Catechumen(VARCHAR(fifty), FileData, 1) AS FileData from tblEmpIdentity WITH (NOLOCK)" ;
cmd.Connection = Connection;
Connection.Open();
grdEmployees.DataSource = cmd.ExecuteReader();
grdEmployees.DataBind();
Connexion.Close();
}
}
}

VB.NET

Private Sub GetEmployees()
Dim ConnectionStrings As Cord = ConfigurationManager.ConnectionStrings( "ConnectionStrings" ).ConnectionString
Using conAs New SqlConnection(ConnectionStrings)
Using cmdEqually New SqlCommand()
cmd.CommandText ="SELECT FileID, EmployeeName, DocumentName, FileName, CONVERT(VARCHAR(50), FileData, one) AS FileData from tblEmpIdentity WITH (NOLOCK)"
cmd.Connectedness = con
con.Open up()
grdEmployees.DataSource = cmd.ExecuteReader()
grdEmployees.DataBind()
con.Close()
End Using
Cease Using
End Sub

Explanation

Every bit yous can come across in the written code above, nosotros take created a function GetEmployees for displaying records from the tblEmpIdentity tabular array. Nosotros take fetched connection string from web.config file and created an object of SQL connection class for database connection and so created an object for SQL command grade, prepared a SQL statement for fetching records from the database and finally executed the created SQL argument and assigned a effect set to filigree view as a data source.

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

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

VB.Internet

Protected Sub Page_Load(sender Every bit Object , e As EventArgs) Handles Me .Load
If Not IsPostBack And so
GetEmployees()
Terminate If
End Sub

Output

Summary

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