upload and download file from sqlserver

Creating sqlserver table

create table (id int,content_type varcahr(50),filename varcahr(50),filecontent varbinary(8000))


Code for c#.net



using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;

public partial class filestore : System.Web.UI.Page
{
SqlConnection ObjSQlConnectionString = new SqlConnection("Data source=server_name;Initial Catalog=table_name;User Id=userid;Pwd=password");

protected void btn_upload_file_Click(object sender, EventArgs e)
{


string upload_path_name = upload.PostedFile.FileName.ToString();
string upload_file_name = Path.GetFileName(upload_path_name);
FileStream Upload_filestream = new FileStream(upload_path_name, FileMode.Open, FileAccess.Read);
BinaryReader upload_binaryreader = new BinaryReader(Upload_filestream);
Byte[] byte_data = upload_binaryreader.ReadBytes ((Int32)Upload_filestream.Length);
upload_binaryreader.Close();
Upload_filestream.Close();

if (upload.PostedFile.ContentType == "application/vnd.ms-excel")
{
//upload file in a seperate folder
upload.SaveAs("C:\\Inetpub\\wwwroot\\tools_test\\excel files\\"+upload_file_name);
}

Response.Write(byte_data.Length);


//insert data in sqlserver table

string insert_query = "insert into filestroage(content_type,filename,filecontent)values(@content_type,@filename,@filecontent)";

SqlCommand sql_command = new SqlCommand(insert_query, ObjSQlConnectionString);

sql_command.CommandType = CommandType.Text;

sql_command.Parameters.Add("@content_type", SqlDbType.VarChar).Value = upload.PostedFile.ContentType.ToString();
sql_command.Parameters.Add("@filename", SqlDbType.VarChar).Value = upload_file_name;
sql_command.Parameters.Add("@filecontent", SqlDbType.Binary).Value = byte_data;

ObjSQlConnectionString.Open();

sql_command.ExecuteNonQuery();

ObjSQlConnectionString.Close();

}

//Read files data from table
protected void Button1_Click(object sender, EventArgs e)
{
string strQuery = "select content_type,filename,filecontent from filestroage where id=@id";

SqlCommand sql_command = new SqlCommand(strQuery);

sql_command.Parameters.Add("@id", SqlDbType.Int).Value = 2;

DataTable data_table_query = GetData_table(sql_command);

if (data_table_query != null)
{
file_download (data_table_query);
}
}



private DataTable GetData_table(SqlCommand sql_command)
{
DataTable data_table = new DataTable();

SqlDataAdapter sql_adapter = new SqlDataAdapter();

sql_command.CommandType = CommandType.Text;

sql_command.Connection = ObjSQlConnectionString;

try
{
ObjSQlConnectionString.Open();

sql_adapter.SelectCommand = sql_command;

sql_adapter.Fill(data_table );

return data_table;

}

catch
{

return null;

}

finally
{

ObjSQlConnectionString.Close();

sql_adapter.Dispose();

ObjSQlConnectionString.Dispose();

}

}

//Download read data in their own file format
private void file_download(DataTable data_table_rows)
{

Byte[] data_bytes = (Byte[])data_table_rows.Rows[0]["filecontent"];

Response.Buffer = true;

Response.Charset = "";

Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.ContentType = data_table_rows.Rows[0]["content_type"].ToString();

Response.AddHeader("content-disposition", "attachment;filename="+ data_table_rows .Rows[0]["filename"].ToString());

Response.BinaryWrite(data_bytes);

Response.Flush();

Response.End();
}
}