admin
Principally, many builders save unique information or paperwork in a selected folder and save the file paths into the database and whereas they wish to entry any file or doc, they fetch the file path for a selected file from the database and based mostly on that file path they get the file from the folder.
Suppose, sadly, a file is deleted or renamed within the folder then they can’t in a position to entry these information or paperwork. So, at the moment on this article I am going to present you easy methods to save information instantly into the database in VARBINARY knowledge so, you may entry any file from the database.
Requirement
3) Show uploaded information in a grid view.
Implementation
Let,s begin with an instance of the worker administration system, Right here we are going to save employee-wise paperwork of staff such because the profile image, Identification of the worker comparable to election card in addition to different paperwork of staff comparable to agreements, deal with proof and and so on into the database.
Create Desk
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 may see within the above script, right here we created a column for FileID, EmployeeID, EmployeeName, DocumentName, FileName, ContentType, and FileData the place FileID is the first key of the desk.
Now, we are going to write the next HTML code into an aspx file, the place we are going to design our type with a dropdown field for worker choice, file add management, and add button in addition to one grid view to show data of uploaded information of the worker.
HTML
<html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title>File Add Instance</title> <hyperlink rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" /> <script kind="textual content/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script> <script kind="textual content/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"></script> <script kind="textual content/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script> </head> <physique> <type id="form1" runat="server"> <div class=" container"> <br /> <h1>File Add Instance</h1> <br /> <div class="form-row"> <div class="col"> <asp:DropDownList id="ddlEmployees" runat="server" CssClass="form-control dropdown"> <asp:ListItem worth="0">-- Choose Worker --</asp:ListItem> <asp:ListItem worth="1">Nikunj Satasiya</asp:ListItem> <asp:ListItem worth="2">Hiren Dobariya</asp:ListItem> <asp:ListItem worth="3">Vivek Ghadiya</asp:ListItem> <asp:ListItem worth="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" Textual content="Add" CssClass="btn btn-primary" OnClick="btnUploadFile_click" /> <hr /> <asp:GridView ID="grdEmployees" runat="server" Width="100%" CssClass="desk table-bordered" AutoGenerateColumns="false"> <Columns> <asp:BoundField DataField="FileID " Seen="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> </type> </physique> </html>
Earlier than beginning the precise code have to create a database reference to our net software and for that, we have to write the next connection string into the net. config file.
Net.Config
<connectionStrings> <add title="ConnectionStrings" connectionString="Knowledge Supply=(LocalDB)v11.0;AttachDbFilename=E:NikunjcodingvilabinDebugDBcodingvila.mdf;Built-in Safety=True;Join Timeout=30"/> </connectionStrings >
After, the creation of the database connection we have to import the next namespaces into code-behind.
Namespaces
C#
utilizing System.IO; utilizing System.Knowledge; utilizing System.Knowledge.SqlClient; utilizing System.Configuration;
VB.NET
Imports System.IO Imports System.Knowledge Imports System.Knowledge.SqlClient Imports System.Configuration
C#
protected void btnUploadFile_click(object sender, EventArgs e) { //fetch the title of the file string empFilename = Path.GetFileName(FileUploadEmployees.PostedFile.FileName); //fetch the file content material kind of the file string FilecontentType = FileUploadEmployees.PostedFile.ContentType; //reads a content material of the file utilizing (Stream s = FileUploadEmployees.PostedFile.InputStream) { utilizing (BinaryReader br = new BinaryReader(s)) { byte[] Databytes = br.ReadBytes((Int32)s.Size); //fetch connection string from the net.config file string ConnectionStrings = ConfigurationManager.ConnectionStrings["ConnectionStrings"].ConnectionString; //create a database connection object utilizing (SqlConnection con = new SqlConnection(ConnectionStrings)) { string question = "INSERT INTO tblEmpIdentity VALUES (@EmployeeID, @EmployeeName, @DocumentName, @FileName, @FileContentType, @FileData)"; //create an object for SQL command class utilizing (SqlCommand cmd = new SqlCommand(question)) { cmd.Connection = con; cmd.Parameters.AddWithValue("@EmployeeID", ddlEmployees.SelectedItem.Worth); cmd.Parameters.AddWithValue("@EmployeeName", ddlEmployees.SelectedItem.Textual content); cmd.Parameters.AddWithValue("@DocumentName", txtDocument.Textual content); cmd.Parameters.AddWithValue("@FileName", empFilename); cmd.Parameters.AddWithValue("@FileContentType", FilecontentType); cmd.Parameters.AddWithValue("@FileData", Databytes); //open database connection con.Open(); //execute SQL assertion cmd.ExecuteNonQuery(); con.Shut(); } } } } Response.Redirect(Request.Url.AbsoluteUri); }
VB.NET
Protected Sub btnUploadFile_click(sender As Object, e As EventArgs) 'fetch the title of the file Dim empFilename As String = Path.GetFileName(FileUploadEmployees.PostedFile.FileName) 'fetch the file content material kind of the file Dim FilecontentType As String = FileUploadEmployees.PostedFile.ContentType 'reads a content material of the file Utilizing s As Stream = FileUploadEmployees.PostedFile.InputStream Utilizing br As New BinaryReader(s) Dim Databytes As Byte() = br.ReadBytes(CType(s.Size, Int32)) 'fetch connection string from the net.config file Dim ConnectionStrings As String = ConfigurationManager.ConnectionStrings("ConnectionStrings").ConnectionString 'create a database connection object Utilizing con As New SqlConnection(ConnectionStrings) Dim question As String = "INSERT INTO tblEmpIdentity VALUES (@EmployeeID, @EmployeeName, @DocumentName, @FileName, @FileContentType, @FileData)" Utilizing cmd As New SqlCommand(question) cmd.Connection = con cmd.Parameters.AddWithValue("@EmployeeID", ddlEmployees.SelectedItem.Worth) cmd.Parameters.AddWithValue("@EmployeeName", ddlEmployees.SelectedItem.Textual content) cmd.Parameters.AddWithValue("@DocumentName", txtDocument.Textual content) cmd.Parameters.AddWithValue("@FileName", empFilename) cmd.Parameters.AddWithValue("@FileContentType", FilecontentType) cmd.Parameters.AddWithValue("@FileData", Databytes) 'open database connection con.Open() 'execute SQL assertion cmd.ExecuteNonQuery() con.Shut() Finish Utilizing Finish Utilizing Finish Utilizing Finish Utilizing Response.Redirect(Request.Url.AbsoluteUri) Finish Sub
Rationalization
As you may see within the written code above first we fetched the title of the uploaded file and saved it in an area variable empFilename.
Then we fetched and saved the content material kind of the uploaded file and saved it within the variable FileContentType.
Reads contents of the file and shops in-stream variables after which creates an object of binary reader class that reads primitive knowledge sorts as binary values in particular encoding and makes use of that learn file content material and retailer binary knowledge in a byte array.
Lastly, As per the requirement described above, we have to show uploaded information or paperwork of the workers within the grid view, so we are going to fetch all of the data from the tblEmpIdentity desk and bind these data with the grid view.
C#
non-public void GetEmployees() { //fetch connection string from the net.config file string ConnectionStrings = ConfigurationManager.ConnectionStrings["ConnectionStrings"].ConnectionString; //create a database connection object utilizing (SqlConnection Connection = new SqlConnection(ConnectionStrings)) { //create an object for SQL command class utilizing (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 assertion grdEmployees.DataSource = cmd.ExecuteReader(); grdEmployees.DataBind(); Connection.Shut(); } } }
VB.NET
Personal Sub GetEmployees() 'fetch connection string from the net.config file Dim ConnectionStrings As String = ConfigurationManager.ConnectionStrings("ConnectionStrings").ConnectionString 'create a database connection object Utilizing con As New SqlConnection(ConnectionStrings) 'create an object for SQL command class Utilizing 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 assertion grdEmployees.DataSource = cmd.ExecuteReader() grdEmployees.DataBind() con.Shut() Finish Utilizing Finish Utilizing Finish Sub
Rationalization
As you may see within the written code above, the place we’ve created a perform GetEmployees for show data from the tblEmpIdentity desk.
Now we have fetched the connection string from the net. config file and created an object of SQL connection class for database connection after which creates an object for SQL command class, ready a SQL assertion for fetch data from the database, and at last execute created SQL assertion and assigned outcome set to grid view as an information supply.
Now, we’ve to name the created methodology above on the load occasion of the web page to view the inserted data 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() Finish If Finish Sub
Output
Abstract
On this article, we discovered easy methods to add information in ASP.NET utilizing C# and VB.NET, in addition to additionally discovered easy methods to save information into the SQL server database in VARBINARY knowledge.
Tags:
save file in database postgresql
save file in database sql server
save file in database sql server c#
easy methods to save file in database utilizing spring boot
easy methods to save a database in mysql
easy methods to save database in ms entry
easy methods to retailer uploaded information in a sql database
c# save file to database entity framework
GIPHY App Key not set. Please check settings