How to Save and Retrieve Files from MS SQL Server Database using ASP.Net

Step 1.

Make a table named tblFiles for storing files in SQL.

Step 2.

Make required HTML changes and add this piece of code to browse, choose and upload files.

<div>

<asp:FileUpload ID="fileUpload1" runat="server" /><br />

<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />

</div>

<div>

<asp:GridView ID="gv" runat="server" AutoGenerateColumns="false" DataKeyNames="id">

<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />

<Columns>

<asp:BoundField DataField="id" HeaderText="Id" />

<asp:BoundField DataField="Name" HeaderText="FileName" />

<asp:BoundField DataField="ContentType" HeaderText="type" />

<asp:BoundField DataField="Data" HeaderText="Data" />

<asp:TemplateField HeaderText="FilePath">

<ItemTemplate>

<asp:LinkButton ID="lnkDownload" runat="server" Text="Download" OnClick="lnkDownload_Click"></asp:LinkButton>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</div>

Step 3.

In code behind file make sure that these namespaces are added.

using System;

using System.Data.SqlClient;

using System.IO;

using System.Web.UI.WebControls;

Step 4.

Now in code behind file add this piece of code.

string strCon = "Data Source=JITENDRA-PC\\SQLEXPRESS;Initial Catalog=Vaibhav_Task;Integrated Security=True";

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindGridviewData();

}

}

// Bind Gridview Data

private void BindGridviewData()

{

using (SqlConnection con = new SqlConnection(strCon))

{

using (SqlCommand cmd = new SqlCommand())

{

cmd.CommandText = "select * from tblFiles";

cmd.Connection = con;

con.Open();

gv.DataSource = cmd.ExecuteReader();

gv.DataBind();

con.Close();

}

}

}

// Save files to Folder and files path in database

protected void btnUpload_Click(object sender, EventArgs e)

{

string filename = Path.GetFileName(fileUpload1.PostedFile.FileName);

Stream str = fileUpload1.PostedFile.InputStream;

BinaryReader br = new BinaryReader(str);

string ext = System.IO.Path.GetExtension(fileUpload1.PostedFile.FileName);

Byte[] size = br.ReadBytes((int)str.Length);

using (SqlConnection con = new SqlConnection(strCon))

{

using (SqlCommand cmd = new SqlCommand())

{

cmd.CommandText = "insert into tblFiles(Name,ContentType,Data) values(@Name,@Type,@Data)";

cmd.Parameters.AddWithValue("@Name", filename);

cmd.Parameters.AddWithValue("@Type", ext);

cmd.Parameters.AddWithValue("@Data", size);

cmd.Connection = con;

con.Open();

cmd.ExecuteNonQuery();

con.Close();

BindGridviewData();

}

}

}

// This button click event is used to download files from gridview

protected void lnkDownload_Click(object sender, EventArgs e)

{

LinkButton lnkbtn = sender as LinkButton;

GridViewRow gvrow = lnkbtn.NamingContainer as GridViewRow;

int fileid = Convert.ToInt32(gv.DataKeys[gvrow.RowIndex].Value.ToString());

using (SqlConnection con = new SqlConnection(strCon))

{

using (SqlCommand cmd = new SqlCommand())

{

cmd.CommandText = "select Name, ContentType, Data from tblFiles where id=@Id";

cmd.Parameters.AddWithValue("@Id", fileid);

cmd.Connection = con;

con.Open();

SqlDataReader dr = cmd.ExecuteReader();

if (dr.Read())

{

Response.ContentType = dr["ContentType"].ToString();

Response.AddHeader("Content-Disposition", "attachment;filename=\"" + dr["Name"] + "\"");

Response.BinaryWrite((byte[])dr["Data"]);

Response.End();

}

}

}

}

Output-