In this article I will explain with an example, how to upload files to SQL Server Database in ASP.Net using C# and VB.Net.
The Files will be uploaded using FileUpload control and will be inserted into SQL Server Database Table.
A GridView control will display the list 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 table named tblFiles whose schema is defined as follows.
![Upload and Download files from SQL Server Database in ASP.Net]()
Note: You can download the database table SQL by clicking the download link below.
Download SQL file
HTML Markup
The HTML Markup consists of an ASP.Net FileUpload control, a Button and a GridView control.
<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="false">
<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 will need to import the following 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
Uploading the files and then saving in SQL Server Database table
When the Upload Button is clicked, the uploaded File is converted into Byte Array format.
Then File name, its Content Type (MIME type) and the File in Byte Array format are inserted into the SQL Server Database table.
Note: The Content type (MIME type) is very important while downloading the files as it notifies the browser about type of the File.
C#
protected void Upload(object sender, EventArgs e)
{
string 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);
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
string query = "insert into tblFiles values (@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.Close();
}
}
}
}
Response.Redirect(Request.Url.AbsoluteUri);
}
VB.Net
Protected Sub Upload(sender As Object, e As EventArgs)
Dim filename As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
Dim contentType As 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 String = 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.Connection = 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
End Using
End Using
Response.Redirect(Request.Url.AbsoluteUri)
End 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 e)
{
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.Connection = con;
con.Open();
GridView1.DataSource = cmd.ExecuteReader();
GridView1.DataBind();
con.Close();
}
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
BindGrid()
End If
End Sub
Private Sub BindGrid()
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand()
cmd.CommandText = "select Id, Name from tblFiles"
cmd.Connection = con
con.Open()
GridView1.DataSource = cmd.ExecuteReader()
GridView1.DataBind()
con.Close()
End Using
End Using
End Sub
Downloading particular file from Database Table using the Download Button in GridView
When the Download LinkButton inside 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 Array are fetched from the database.
Finally, using the BinaryWrite method of the Response class, 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, Data, ContentType from tblFiles where Id=@Id";
cmd.Parameters.AddWithValue("@Id", id);
cmd.Connection = 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.End();
}
VB.Net
Protected Sub DownloadFile(sender As Object, e As EventArgs)
Dim id As Integer = Integer.Parse(TryCast(sender, LinkButton).CommandArgument)
Dim bytes As Byte()
Dim fileName As String, contentType As String
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand()
cmd.CommandText = "select Name, Data, ContentType from tblFiles where Id=@Id"
cmd.Parameters.AddWithValue("@Id", id)
cmd.Connection = con
con.Open()
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()
End Using
End Using
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.End()
End Sub
Screenshot
![Upload and Download files from SQL Server Database in ASP.Net]()
Downloads
