Sql Databe Where Documents Can Be Uploaded
In this article I will explicate with an example, how to upload files to SQL Server Database in ASP.Net using C# and VB.Net.
The Files volition be uploaded using FileUpload control and volition exist inserted into SQL Server Database Table.
A GridView command volition display the listing of Files present in the SQL Server Database Table along with an option to download the selected file from Database in ASP.Net.
Database
This article makes use of a tabular array named tblFiles whose schema is defined as follows.
Note : You lot tin download the database table SQL by clicking the download link beneath.
Download SQL file
HTML Markup
The HTML Markup consists of an ASP.Internet FileUpload control, a Push button and a GridView command.
< asp : FileUpload ID ="FileUpload1" runat ="server" />
< asp : Button ID ="btnUpload" runat ="server" Text ="Upload" OnClick ="Upload" />
< hr />
< asp : GridView ID ="GridView1" runat ="server" HeaderStyle-BackColor ="#3AC0F2" HeaderStyle-ForeColor ="White"
RowStyle-BackColor ="#A1DCF2" AlternatingRowStyle-BackColor ="White" AlternatingRowStyle-ForeColor ="#000"
AutoGenerateColumns ="imitation">
< Columns >
< asp : BoundField DataField ="Name" HeaderText ="File Name"/>
< asp : TemplateField ItemStyle-HorizontalAlign = "Center">
< ItemTemplate >
< asp : LinkButton ID ="lnkDownload" runat ="server" Text ="Download" OnClick ="DownloadFile"
CommandArgument =' <% # Eval("Id") %> '></ asp : LinkButton >
</ ItemTemplate >
</ asp : TemplateField >
</ Columns >
</ asp : GridView >
Namespaces
You volition need to import the following namespaces.
C#
using Organisation.IO;
using System.Data;
using System.Data.SqlClient;
using Organization.Configuration;
VB.Net
Imports Arrangement.IO
Imports System.Information
Imports System.Data.SqlClient
Imports Organization.Configuration
Uploading the files so saving in SQL Server Database table
When the Upload Button is clicked, the uploaded File is converted into Byte Assortment format.
Then File proper noun, its Content Type (MIME type) and the File in Byte Array format are inserted into the SQL Server Database table.
Annotation : The Content type (MIME blazon) is very important while downloading the files as it notifies the browser nigh type of the File.
C#
protected void Upload(object sender, EventArgs east)
{
cord filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
string contentType = FileUpload1.PostedFile.ContentType;
using (Stream fs = FileUpload1.PostedFile.InputStream)
{
using (BinaryReader br = new BinaryReader(fs))
{
byte[] bytes = br.ReadBytes((Int32)fs.Length);
cord constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
string query = "insert into tblFiles values (@Proper name, @ContentType, @Data)";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@Name", filename);
cmd.Parameters.AddWithValue("@ContentType", contentType);
cmd.Parameters.AddWithValue("@Data", bytes);
con.Open();
cmd.ExecuteNonQuery();
con.Shut();
}
}
}
}
Response.Redirect(Asking.Url.AbsoluteUri);
}
VB.Cyberspace
Protected Sub Upload(sender Every bit Object, e As EventArgs)
Dim filename As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
Dim contentType Equally String = FileUpload1.PostedFile.ContentType
Using fs As Stream = FileUpload1.PostedFile.InputStream
Using br As New BinaryReader(fs)
Dim bytes As Byte() = br.ReadBytes(DirectCast(fs.Length, Long))
Dim constr As Cord = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Dim query As String = "insert into tblFiles values (@Name, @ContentType, @Data)"
Using cmd As New SqlCommand(query)
cmd.Connectedness = con
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename
cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = contentType
cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
Finish Using
Terminate Using
Response.Redirect(Request.Url.AbsoluteUri)
Stop Sub
Displaying the uploaded files from Database Table in ASP.Net GridView
Inside the Page Load event, the GridView is populated using the records from the SQL Server Database Table.
C#
protected void Page_Load(object sender, EventArgs east)
{
if (!IsPostBack)
{
BindGrid();
}
}
private void BindGrid()
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "select Id, Name from tblFiles";
cmd.Connexion = con;
con.Open();
GridView1.DataSource = cmd.ExecuteReader();
GridView1.DataBind();
con.Shut();
}
}
}
VB.Net
Protected Sub Page_Load(sender Every bit Object, e As EventArgs) Handles Me.Load
If Not IsPostBack So
BindGrid()
End If
Finish Sub
Private Sub BindGrid()
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con Equally New SqlConnection(constr)
Using cmd Equally New SqlCommand()
cmd.CommandText = "select Id, Name from tblFiles"
cmd.Connectedness = con
con.Open()
GridView1.DataSource = cmd.ExecuteReader()
GridView1.DataBind()
con.Close()
End Using
Finish Using
End Sub
Downloading particular file from Database Table using the Download Push button in GridView
When the Download LinkButton within the GridView is clicked, first the File ID is fetched from the CommandArgument property of the LinkButton which was clicked.
Then the details of the File such as Name, Content Type and the File in Byte Assortment are fetched from the database.
Finally, using the BinaryWrite method of the Response course, the File is written to the Response Stream using its Path and File is downloaded.
C#
protected void DownloadFile(object sender, EventArgs e)
{
int id = int.Parse((sender as LinkButton).CommandArgument);
byte[] bytes;
string fileName, contentType;
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "select Name, Information, ContentType from tblFiles where Id=@Id";
cmd.Parameters.AddWithValue("@Id", id);
cmd.Connexion = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
sdr.Read();
bytes = (byte[])sdr["Data"];
contentType = sdr["ContentType"].ToString();
fileName = sdr["Name"].ToString();
}
con.Close();
}
}
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = contentType;
Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(bytes);
Response.Flush();
Response.Terminate();
}
VB.Cyberspace
Protected Sub DownloadFile(sender Equally Object, east As EventArgs)
Dim id As Integer = Integer.Parse(TryCast(sender, LinkButton).CommandArgument)
Dim bytes As Byte()
Dim fileName As String, contentType Every bit String
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd Equally New SqlCommand()
cmd.CommandText = "select Name, Information, ContentType from tblFiles where Id=@Id"
cmd.Parameters.AddWithValue("@Id", id)
cmd.Connection = con
con.Open up()
Using sdr As SqlDataReader = cmd.ExecuteReader()
sdr.Read()
bytes = DirectCast(sdr("Data"), Byte())
contentType = sdr("ContentType").ToString()
fileName = sdr("Name").ToString()
End Using
con.Close()
Finish Using
Finish Using
Response.Articulate()
Response.Buffer = True
Response.Charset = ""
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.ContentType = contentType
Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName)
Response.BinaryWrite(bytes)
Response.Flush()
Response.Finish()
End Sub
Screenshot
Downloads
Source: https://www.aspsnippets.com/Articles/Upload-and-Download-files-from-SQL-Server-Database-in-ASPNet.aspx
0 Response to "Sql Databe Where Documents Can Be Uploaded"
Post a Comment