Upload any file into a database

Upload and download any file to a database:
Any type of file can be saved in the database as a binary stream.Sql server 2005 has a datatype varbinary(max).We can store the file in the database using this varbinary(max) datatype.Here I will explain how to save and retrieve files from database.

Database Design :Here I have created a Database called dbFiles and it has a table called tblFiles.
It has 4 Fields as follows

id contains Identification Number (Autogenerate ID),Name contains File Name ,Content Type contains Content Type for the file and Data contains File stored as Binary Data.

Depending on the type of the file below are the content types

Content Type of "Word Document" is "application/vnd.ms-word"

Content Type of "Excel Document" is "application/vnd.ms-excel"

Content Type of "JPEG Image" is "image/jpeg"

Content Type of "Portable Document Format " is "application/pdf "

Code:Insert the file to the database

// Read the file and convert it to Byte Array
string filePath = Server.MapPath("APP_DATA/TestDoc.docx");
string filename = Path.GetFileName(filePath);

FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
Byte[] bytes = br.ReadBytes((Int32)fs.Length);
br.Close();
fs.Close();

//insert the file into database
string strQuery = "insert into tblFiles(Name, ContentType, Data) values (@Name, @ContentType, @Data)";
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename;
cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = "application/vnd.ms-word";
cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;
InsertUpdateData(cmd);

private Boolean InsertUpdateData(SqlCommand cmd)
{
String strConnString = System.Configuration.ConfigurationManager
.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
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();
}
}

Retrieving the File from Database

string strQuery = "select Name, ContentType, Data from tblFiles where id=@id";
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1;
DataTable dt = GetData(cmd);
if (dt != null)
{
download(dt);
}

private DataTable GetData(SqlCommand cmd)
{
DataTable dt = new DataTable();
String strConnString = System.Configuration.ConfigurationManager
.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
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();
}