SAVE AND RETRIEVE FILES FROM SQL SERVER DATABASE USING ASP.NET

As you can see above for the id field I have set Identity Specification true, so that it automatically increments itself.

Field	Relevance
id	Identification Number
Name	File Name
Content Type	Content Type for the file
Data	File stored as Binary Data

Content Type

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

File Type	Content Type
Word Document	application/vnd.ms-word
Excel Document	application/vnd.ms-excel
JPEG Image	image/jpeg
Portable Document Format	application/pdf

Connection String

Below is the connection string to the database. You can modify it to suit yours

<connectionStrings>

<add name="conString" connectionString="Data Source=.\SQLEXPRESS;database=dbFiles; Integrated Security=true"/>

</connectionStrings >

Reading the File

The files will be read into a File Stream and then the File Stream will be converted into byte array using BinaryReader in order to save into the database table.

C#

// 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();                                                                                  

Saving the File to Database

Once the File is converted into Byte Array it will be inserted into the database. The File Name, File Content Type and the Binary data which resembles the file are stored in the database.

The figure below shows the data being stored in the table.

C#

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

And the function InsertUpdateData accepts the SqlCommand object, executes the query and inserts the data into the database.

C#

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

To retrieve the file from the database, a select query is executed and the ID of the file is passed as the parameter.

The command object is prepared and is passed to the GetData which returns the DataTable which contains the desired file data.

Then the DataTable is passed to the download function which starts the download of the file.

C#

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

}

Below is the code for the GetData function. It is a simple function that executes the Select query.

C#

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

    }

}

Download the File

Here is the function which initiates the download of file. It basically reads the file contents into a Byte array and also gets the file name and the Content Type. Then it writes the bytes to the response using Response.BinaryWrite

C#

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

}