Introduction:
First Design table like this in your SQL Server database and give name as varbinary.
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.
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 .................
<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