Monday, 1 April 2013

Save and Retrieve Files from SQL Server Database using ASP.Net


 
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.



No comments:

Post a Comment