How to upload and download Media to or from MSSQL Server 2005 Database


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-
1. ID (int, not null, IsIndentity: Yes)
2. FileName (text, allow nulls)
3. MediaData (varbinary(MAX), allow nulls)
4. Extension (text, allow nulls)
Then save all.

Now create a new Visual C# type website in your Visual Studio.

Download this Default.aspx to understand what asp controls you have to put in your "Default.aspx" page.

And here is the code of your website's "Default.aspx.cs" page:


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);
}

}
}


Comments

Guest Author: Yash11 Sep 2013

Working for all files but not for video,mp3.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: