This code shows you how to upload media files to the SQL Server Database to store it and then how to download it from the Database in your ASP.NET website using C#.
First you have to create a database named “MediaDB” in your SQL Server. Then create a table named “MediaTable” in your newly created database. Now create 4 columns in “MediaTable” as given below-
- ID (int, not null, IsIndentity: Yes)
- FileName (text, allow nulls)
- MediaData (varbinary(MAX), allow nulls)
- Extension (text, allow nulls)
Then save all.
Now create a new Visual C# type website in your Visual Studio.
Here is the code of your website’s “Default.aspx” page code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<center>
<asp:Label ID="Label1" runat="server"></asp:Label>
</center>
<center>
<asp:FileUpload ID="FileUpload1" runat="server" />
</center>
<center>
<asp:Button ID="Upload_Btn" runat="server" Text="Upload"
onclick="Upload_Btn_Click" />
</center>
<center>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataSourceID="SqlDataSource1"
onselectedindexchanging="GridView1_SelectedIndexChanging"
AllowPaging="True" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None"
BorderWidth="1px" Caption="Uploaded Media" CaptionAlign="Top" CellPadding="4"
DataKeyNames="ID" ForeColor="Black" GridLines="Horizontal" PageSize="5">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"
ReadOnly="True" SortExpression="ID" />
<asp:BoundField DataField="FileName" HeaderText="FileName"
SortExpression="FileName" />
<asp:BoundField DataField="Extension" HeaderText="Extension"
SortExpression="Extension" />
<asp:ButtonField ButtonType="Button" CommandName="Select" Text="Download"
HeaderText="Option" ShowHeader="True" />
</Columns>
<FooterStyle BackColor="#CCCC99" ForeColor="Black" />
<HeaderStyle BackColor="#333333" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="Black" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F7F7F7" />
<SortedAscendingHeaderStyle BackColor="#4B4B4B" />
<SortedDescendingCellStyle BackColor="#E5E5E5" />
<SortedDescendingHeaderStyle BackColor="#242121" />
</asp:GridView>
</center>
<center>
</center>
<center>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="Data Source=.\SQLEXPRESS;Initial Catalog=MediaDB;Integrated Security=True"
ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [MediaTable]"></asp:SqlDataSource>
</center>
</form>
</body>
</html>
And here is the code of your website’s “Default.aspx.cs” page code:
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Data.Sql;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Upload_Btn_Click(object sender, EventArgs e)
{
String fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
if (FileUpload1.HasFile)
{
try
{
SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=MediaDB;Integrated Security=True");
con.Open();
byte[] MediaBytes = new byte[FileUpload1.PostedFile.InputStream.Length];
FileUpload1.PostedFile.InputStream.Read(MediaBytes, 0, MediaBytes.Length);
string qry = "Insert into MediaTable(FileName, MediaData, Extension) values(@FName, @MediaData, @Ext)";
SqlCommand cmd = new SqlCommand(qry, con);
cmd.Parameters.AddWithValue("@FName", FileUpload1.FileName);
cmd.Parameters.AddWithValue("@MediaData", MediaBytes);
cmd.Parameters.AddWithValue("@Ext", fileExtension);
cmd.ExecuteNonQuery();
con.Close();
Label1.Text = "Media Uploaded Successfully!";
GridView1.DataBind();
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
}
else
{
Label1.Text = "No File is Selected!";
}
}
protected void GridView1_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
{
try
{
SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=MediaDB;Integrated Security=True");
con.Open();
String ID = (GridView1.DataKeys[e.NewSelectedIndex].Value).ToString();
String qry = "Select * From MediaTable Where(ID=" + ID + ")";
SqlCommand cmd = new SqlCommand(qry, con);
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
string filename = (String)reader.GetValue(1);
byte[] fileToDownload = (byte[])reader.GetValue(2);
String fileExtension = (String)reader.GetValue(3);
//Add the file name and attachment, which will force the open/cancel/save dialog to show, to the header
Response.AddHeader("Content-Disposition", "attachment; filename=" + filename);
Response.ContentType = fileExtension;
Response.AddHeader("Content-Length", fileToDownload.Length.ToString());
Response.BinaryWrite(fileToDownload);
Response.Flush();
Response.End();
}
catch (System.Exception exp)
{
Label1.Text = (exp.Message);
}
}
}