Files can be easily saved in the SQL Server Database Saving it in database makes it easily manageable.

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. The complete description is available in the Figure below




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


In the source code attached I have added the database files. You will need to attach it to your SQL Server.


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



VB.Net


' Read the file and convert it to Byte Array

Dim filePath As String = Server.MapPath("APP_DATA/Testxls.xlsx")

Dim filename As String = Path.GetFileName(filePath)

 

Dim fs As FileStream = New FileStream(filePath, FileMode.Open, FileAccess.Read)

Dim br As BinaryReader = New BinaryReader(fs)

Dim bytes As Byte() = br.ReadBytes(Convert.ToInt32(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);

 


VB.Net

'insert the file into database

Dim strQuery As String = "insert into tblFiles(Name, ContentType, Data) values (@Name, @ContentType, @Data)"

Dim cmd As SqlCommand = New SqlCommand(strQuery)

cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename

cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = "application/vnd.ms-excel"

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

    }

}

 


VB.Net

Public Function InsertUpdateData(ByVal cmd As SqlCommand) As Boolean

    Dim strConnString As String = System.Configuration.

    ConfigurationManager.ConnectionStrings("conString").ConnectionString

    Dim con As New SqlConnection(strConnString)

    cmd.CommandType = CommandType.Text

    cmd.Connection = con

    Try

      con.Open()

      cmd.ExecuteNonQuery()

      Return True

    Catch ex As Exception

      Response.Write(ex.Message)

      Return False

    Finally

      con.Close()

      con.Dispose()

    End Try

End Function

 

For more details on how to Execute Parameterized queries refer Using Parameterized queries to prevent SQL Injection Attacks in SQL Server.


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

}



VB.Net


  Dim strQuery As String = "select Name, ContentType, Data from tblFiles where id=@id"

  Dim cmd As SqlCommand = New SqlCommand(strQuery)

  cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1

  Dim dt As DataTable = GetData(cmd)

  If dt IsNot Nothing Then

       download(dt)

  End If


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

More details on the function you can refer here.


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

    }

}



VB.Net


Public Function GetData(ByVal cmd As SqlCommand) As DataTable

   Dim dt As New DataTable

   Dim strConnString As String = System.Configuration

   .ConfigurationManager.ConnectionStrings("conString").ConnectionString()

   Dim con As New SqlConnection(strConnString)

   Dim sda As New SqlDataAdapter

   cmd.CommandType = CommandType.Text

   cmd.Connection = con

   Try

      con.Open()

      sda.SelectCommand = cmd

      sda.Fill(dt)

      Return dt

   Catch ex As Exception

      Response.Write(ex.Message)

      Return Nothing

   Finally

      con.Close()

      sda.Dispose()

      con.Dispose()

   End Try

End Function


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

}


VB.Net


Protected Sub download(ByVal dt As DataTable)

  Dim bytes() As Byte = CType(dt.Rows(0)("Data"), Byte())

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

End Sub



Here I have described only for a word file similarly you can save Excel, Image and PDF. In the source code I have done the same for Excel, Image and PDF files too.

You can download the complete Source code here.

SaveRetrieveFiles.zip (1.67 mb)