How to Insert and download Images, Word file, Audio and video files in ASP.NET

Hai Folks,            In this post I will explain how to insert images, audio, video, word document, power point presentation and PDF files to Database(Sql Server 2005) and how to download using asp.net.            As we all know about how to insert data into database. But inserting these kind (Image, video, audio etc) of data is little bit different from the normal data insertion.           To insert these kinds of data we have to convert these data into a binary format and then insert. In Sql server 2005 we have the data type named “VarBinary(MAX)” that is used for inserting these type of data to data base. If you choose “VarBinary(500)” instead of “VarBinary(MAX)” means, sometimes it will throw the Error named “String or binary data would be truncated”. So be aware of it.        First we will design the database creation: Go to Microsoft Sql Server 2005 Sql server management studio from all programs menu. Then create database and named it. Then create a new table with the necessary fields that is listed below.  

Note that, Here the Name is File name and the ContentType is the type of the File (.doc , .docx, .jpg, .png, .ppt, .pdf etc). Next design the following Front-End for inserting files to DB.

Now Double click the button and go to coding page. protected void Page_Load(object sender, EventArgs e)
{
Session [“uname”]=TextBox1.Text.ToString();
}
protected void Button1_Click(object sender, EventArgs e)
{
string filePath = frmUpload.PostedFile.FileName;
string filename = Path.GetFileName(filePath);
string ext = Path.GetExtension(filename);
string contenttype = String.Empty;
//Set the contenttype based on File Extension
switch (ext)
{
case ".doc":
contenttype = "application/vnd.ms-word";
break;
case ".docx":
contenttype = "application/vnd.ms-word";
break;
case ".ppt":
contenttype = "application/vnd.ms-PowerPoint";
break;
case ".pptx":
contenttype = "application/vnd.ms-PowerPoint";
break;
case ".xls":
contenttype = "application/vnd.ms-excel";
break;
case ".xlsx":
contenttype = "application/vnd.ms-excel";
break;
case ".jpg":
contenttype = "image/jpg";
break;
case ".png":
contenttype = "image/png";
break;
case ".gif":
contenttype = "image/gif";
break;
}
if (contenttype != String.Empty)
{
Stream fs = frmUpload.PostedFile.InputStream;
BinaryReader br = new BinaryReader(fs);
Byte[] bytes = br.ReadBytes((Int32)fs.Length);
//insert the file into database
string strQuery = "insert into quotation(uname,Name, ContentType, Data) values (@uname, @Name, @ContentType, @Data)";
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Parameters.Add("@uname", SqlDbType.VarChar).Value = uname;
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename;
cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = contenttype;
cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;
InsertUpdateData(cmd);
}
else
{
}
}
private Boolean InsertUpdateData(SqlCommand cmd)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "data source=.;Initial Catalog=DB_Name;uid=sa;password=hi";
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
try
{
con.Open();
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
Response.Write(ex.Message);
return false;
}
finally
{
con.Close();
con.Dispose();
}
}
Here the Boolean return type named InsertUpdateData is mainly used to insert/Execute the given query and return the true/false value. Next we simply redirect this insert page to some other page using the following code. Now type this coding at the end of the button1_click event  Response.Redirect(“download.aspx”);  Now this is to be redirected the “download.aspx” from the current page. Now design the download.aspx page with the following Controls  

After that goto download.aspx.cs page and do the following things. Get the session value first using the following coding. string uname = Session[“uname”]. ToString();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.;
Initial Catalog=DB_Name;uid=sa;password=hi";
con.Open();
string str = "Select Name from quotation where uname=@tig ";
SqlCommand cmd = new SqlCommand(str, con);
cmd.Parameters.Add("@tig", SqlDbType.VarChar).Value = uname;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
DropDownList1.DataSource = ds.Tables[0];
DropDownList1.DataTextField                                 =ds.Tables[0].Columns["Name"].ColumnName.ToString();
DropDownList1.DataBind();
con.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string strQuery ="select uname,ContentType,Data from quotation where uname=@cid";
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Parameters.Add("@cid",SqlDbType.VarChar).Value = DropDownList1.Text;
DataTable dt = GetData(cmd);
if (dt != null)
{
download(dt);
}
}
private DataTable GetData(SqlCommand cmd)
{
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection();
con.ConnectionString = "data source=.;Initial Catalog=DB_Name;uid=sa;password=hi";
SqlDataAdapter sda = new SqlDataAdapter();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
try
{
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
return dt;
}
catch
{
return null;
}
finally
{
con.Close();
sda.Dispose();
con.Dispose();
}
}
private void download(DataTable dt)
{
Byte[] bytes = (Byte[])dt.Rows[0]["Data"];
Response.Buffer = true;
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = dt.Rows[0]["ContentType"].ToString();
Response.AddHeader("content-disposition","attachment;filename="+  
dt.Rows[0]["Name"].ToString());
Response.BinaryWrite(bytes);
Response.Flush();
Response.End();
}
Now your selected file has to be download in your desired location. Happy Coding..!