Pages

Thursday 20 June 2013

Store and Retrieve files from SQL Server DataBase

Introduction:

Here I will explain how to Store and Retrieve files from SQL Server DataBase.

Description:

In previous articles I explained how to insert and retrieve images from database. Now I will explain how to Store and Retrieve files from SQL Server DataBase.
 
First Design table like this in your SQL Server database and give name as varbinary.
 
Column Name
Data Type
Allow Nulls
FieldId
uniqueidentifier
No
FileName
nvarchar(255)
No
FileContent
varbinary(max)
Yes
And the I set FileId default to newId() and FileContent default to NULL.

 
ALTER TABLE [dbo].[tblFiles] ADD CONSTRAINT [DF_tblFiles_FileId] DEFAULT (newid()) FOR [FileId]

ALTER TABLE [dbo].[tblFiles] ADD CONSTRAINT [DF_tblFiles_FileContent] DEFAULT (NULL) FOR [FileContent]
 
I wrote the code in C#. I am having a Asp FileUpload control and a button to upload the file, and a Repeater control with two controls, a label for displaying the filename and hyper link control for downloading the file.
 <body>
<form id="form1" runat="server">
<asp:FileUpload runat="server" ID="fileUploadImage" /><asp:Button runat="server"
ID="cmdUpload" Text="Upload File" OnClick="cmdUpload_Click" /><asp:Repeater runat="server" ID="rptrFiles">
<HeaderTemplate>
<table>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td>
<asp:Label runat="server" ID="lblFilename" Text='<%# Eval("FileName")%>' />
</td>
<td>
<asp:HyperLink runat="server" Target="_blank" ID="lbtDownload" Text="Download" NavigateUrl='<%# "Download.aspx?File=" + Eval("FileId").ToString() %>' />
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
</form>
</body> Uploading the File to the Database Code behind protected void cmdUpload_Click(object sender, EventArgs e)
{


string fileName = Path.GetFileName(this.fileUploadImage.FileName);byte[] fileContent = this.fileUploadImage.FileBytes;
using (SqlConnection connection = new SqlConnection("Server=.;User Id=sa;Password=pass@word1;Database=Test"))
{
connection.Open();
using (SqlCommand command = new SqlCommand("INSERT INTO tblFiles(Filename, FileContent) VALUES(@Filename, @FileContent)", connection)){SqlParameter fileNameParameter = new SqlParameter("@Filename", System.Data.SqlDbType.NVarChar, 255);
fileNameParameter.Value = fileName;SqlParameter fileContentParameter = new SqlParameter("@FileContent", System.Data.SqlDbType.VarBinary);
fileContentParameter.Value = fileContent;
command.Parameters.AddRange(new SqlParameter[] {fileNameParameter, fileContentParameter });
command.ExecuteNonQuery(); }} Binddata(); } And here is code to bind the data to repeater control retrieving from the Database. private void Binddata(){DataTable dtFiles = new DataTable("Files");
using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT FileId, FileName FROM tblFiles", "Server=.;User Id=sa;Password=pass@word1;Database=Test"))
{
adapter.Fill(dtFiles);}
this.rptrFiles.DataSource = dtFiles; this.rptrFiles.DataBind();
} And page load bind the data.
 protected void Page_Load(object sender, EventArgs e){if (!IsPostBack)
{
Binddata(); } } Download / Read the file from Database And to download / read the file from Database, I am passing the File unique id to another page(download.aspx).I this page I am checking for the File querysting and based on that reading filecontent from Sql and writing it to Asp.net output stream.
 protected void Page_Load(object sender, EventArgs e){
if (!IsPostBack)
{
if (Request.QueryString["File"] != null)
{
string fileId = Request.QueryString["File"];
using (SqlConnection connection = new SqlConnection("Server=.;User Id=sa;Password=pass@word1;Database=Test"))
{
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT Filename, FileContent FROM tblFiles WHERE FileId = @FileId", connection))
{
command.Parameters.AddWithValue(
"@FileId", fileId);
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
if (reader.HasRows)
{
reader.Read();
byte[] content = reader["FileContent"] as byte[];
string filename = reader["FileName"].ToString();
Response.Clear();
Response.ClearContent();
Response.AddHeader(

"Content-Disposition", "attachment; filename=" + filename);
Response.AddHeader(
"Content-Length", content.Length.ToString());
Response.OutputStream.Write(content, 0, content.Length);
Response.End();
}
}
}
}
}
}

Please write to me if I missed something.
Happy Programming .................

 

No comments:

Post a Comment