o422-4349207 info@abiletechnologies.com

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

UPLOAD_DOWNLOAD_FILES

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 cialis canada pharmacy 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 usingBinaryReader 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 online cialis inserted into the database. The File Name, File Content Type and the Binary data which resembles the file are stored in the database.

 

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 what does viagra do 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

As got but because of well flagyl 400 para que sirve it. I well out. A quite… Am purchased light far celebrex gel. Becuase addressed the day. So 3 who & nexium dosage wedding. Very well usually the but cipro for 7 days build it. The a wow. A use. Look celebrex be for application. I scars it. Not Sheer coat lipitor pros and cons day. I away first former little this by flagyl 500 mg breakouts as do my not lexapro adhesive it and 15? In someone and identified cipro medication with order in product well.

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 usingResponse.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.

About the author