This article describes two simple ASP.NET pages for handling the stuff; one page for uploading and saving files to a SQL Server table and one for sending the selected file back to the user when requested. There is a db class for the database jobs, separating the logic from the presentation layer.
Why save files to a SQL Server table? One great benefit of storing files in a SQL Server table is that you can apply logic to display or give users access to the files in your pages. You could for example add two date time columns on the table, making it possible to validate the record for a specific period of time.
Okay, let's get on … first we need to create a database object to help us with saving, storing and the output of data.
1. Table to hold the data, aspnet_docs
2. A procedure saving the file, add_aspnet_doc
3. A procedure selecting all the current saved files, get_aspnet_docs
4. A procedure for fetching a specific file, get_aspnet_doc
Here is the SQL statement creating the table aspnet_docs which holds the data itself.
CREATE TABLE [dbo].[aspnet_docs](
[docId] [int] IDENTITY(1,1) NOT NULL,
[docContentType] [varchar](50) NOT NULL,
[fileName] [varchar](150) NOT NULL,
[doc] [varbinary](max) NOT NULL
) ON [PRIMARY]
The docId column creates an identity column to give the documents a unique value for each insertion. Now we need to create the stored procedures; first the stored procedure for saving the file:
CREATE PROCEDURE [dbo].[add_aspnet_doc]
@ContentType varchar(50), @FileName varchar(150), @Doc varbinary(MAX)
AS
BEGIN
insert aspnet_docs values(@ContentType, @FileName, @Doc)
END
Then the stored procedure for fetching all the current documents
CREATE PROCEDURE [dbo].[get_aspnet_docs]
AS
BEGIN
select docId, fileName, docContentType from aspnet_docs
END
And last, the stored procedure for fetching a specific docId:
CREATE PROCEDURE [dbo].[get_aspnet_doc]
@DocId int
AS
BEGIN
select * from aspnet_docs where docId = @DocIdEND
Okay, now we are set to start creating the two pages and the class of the solution: frmUploadFile.aspx for uploading the file, frmRespondFile.aspx for output file to user and db.cs for handling the logic.
Let's start with the db class which fetches the connectionstring from the web.config file:
private static string c_string = ConfigurationManager.ConnectionStrings["sql_conn_string"].ToString();
(Don't forget to change the settings in the web.config file to meet your SQL Server login details!)
<connectionStrings>
<add name="sql_conn_string" connectionString="Data Source=your_sql_server;Initial Catalog=the_db;User Id=the_user;Password=the_pwd;" />
</connectionStrings>
Then the class exposes 4 static methods taking care of the db jobs:
public static void AddFile(string ContentType, string FileName, byte [] FileBytes)
{
try
{
using (SqlConnection c = new SqlConnection(c_string))
{
SqlCommand cmd = new SqlCommand("add_aspnet_doc", c);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ContentType", ContentType);
cmd.Parameters.AddWithValue("@FileName", FileName);
cmd.Parameters.AddWithValue("@Doc", FileBytes);
c.Open();
cmd.ExecuteNonQuery();
c.Close();
}
}
catch(Exception rror) {throw new Exception("Couldn't fetch files from database. Msg: " + rror.Message);}
}
public static DataSet UploadedFiles()
{
try
{
using (SqlConnection c = new SqlConnection(c_string))
{
using (SqlCommand cmd = new SqlCommand("get_aspnet_docs", c))
{
cmd.CommandType = CommandType.StoredProcedure;
using (DataSet dsDoc = new DataSet())
{
using (System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(cmd))
{
c.Open();
da.Fill(dsDoc);
c.Close();
return dsDoc;
}
}
}
}
}
catch(Exception rror) {throw new Exception("Couldn't fetch files from database. Msg: " + rror.Message);}
}
public static DataSet GetFileById(int FileId)
{
try
{
using (SqlConnection conn = new SqlConnection(c_string))
{
using (SqlCommand cmd = new SqlCommand("get_aspnet_doc", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@DocId", FileId);
using (DataSet dsDoc = new DataSet())
{
using (System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(cmd))
{
conn.Open();
da.Fill(dsDoc);
conn.Close();
return dsDoc;
}
}
}
}
}
catch (Exception rror){throw new Exception("Couldn't fetch file " + FileId + " from database. Msg: " + rror.Message);}
}
public static string ContentType(string FileName)
{
string f = FileName.ToLower();
if (f.IndexOf(".pdf") != -1)
return "pdf/application";
else if (f.IndexOf(".csv") != -1)
return "text/comma-separated-values";
else if (f.IndexOf(".txt") != -1)
return "text/plain";
else if (f.IndexOf(".jpg") != -1)
return "image/jpeg";
else if (f.IndexOf(".png") != -1)
return "image/png";
else
return "";}
Okay, that's the db class for handling the database jobs. Let's take a look at the page handling the fileupload.
The page frmUploadFile.aspx is the page responsible for uploading the files and also displaying the files already uploaded. The page consists of three controls; one FileUpload, one Button and a Label control.
<form id="form1" runat="server">
<div>
<h3>FileUpload - Saving posted file to a SQL Server table</h3>
<asp:FileUpload ID="FileUpload1" runat="server" /> <br /><br />
<asp:Button ID="btnUpload" runat="server" Text="Upload file" onclick="btnUpload_Click" /> <br /><br />
<asp:Label ID="lblFiles" runat="server"></asp:Label>
</div></form>
The buttons has the btnUpload_Click methods and that is what saves the document to the table.
protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
// Is there a posted file?
if (FileUpload1.HasFile)
{
// Store the filename. Use to check the content type for later on display
string ct = db.ContentType(FileUpload1.FileName);
// Create an Stream from the posted file if the contenttype is given
if (ct != "")
{
// Get the stream
using (System.IO.Stream fs = FileUpload1.PostedFile.InputStream)
{
// Get the filesize and read the file in to the byte array
byte[] fb = new byte[FileUpload1.PostedFile.ContentLength];
fs.Read(fb, 0, FileUpload1.PostedFile.ContentLength);
fs.Close();
// Save the file to your table by using the db class
db.AddFile(ct, FileUpload1.FileName, fb);
// Reload the page with fresh data
Response.Redirect("frmUploadFile.aspx");
}
}
else
{
lblFiles.Text = "Unknown content type...";
}
}
}
catch (Exception rror) { lblFiles.Text = rror.Message; }}
There is also a method in the page displaying the uploaded files and it runs only if the page isn't loaded thru a postback.
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
showUploadedFiles();
}
This method displays the files and I really would like to mention that this is not the recommended way to display HTML on a page, but that is not the main objective of this article.
private void showUploadedFiles()
{
try
{
// Receive the files from the db
using (DataSet dsDoc = db.UploadedFiles())
{
// Ugly, not the way to do it, but it works for now...
System.Text.StringBuilder sb = new System.Text.StringBuilder();
foreach (DataRow dr in dsDoc.Tables[0].Rows)
{
sb.Append("<div><a href='frmRespondFile.aspx?fileId=" + dr["docId"].ToString() + "'>" + dr["fileName"].ToString() + "</a></div>");
}
lblFiles.Text = sb.ToString();
}
}
catch (Exception rror) { lblFiles.Text = rror.Message; }
}
Okay, now we are able to upload the files and store them in the database and also display them on the page with a link to a page which can send the file back to the user. Let's create the frmRespondFile.aspx page now.
This page doesn't contain any controls, only one main method for fetching the file from the database and adding it to the output and one for giving the file the correct file extension. Here is the method for receiving it from the database; it runs from the page load event:
protected void Page_Load(object sender, EventArgs e)
{
respondFile();
}
The method itself:
private void respondFile()
{
try
{
if (Request.QueryString.HasKeys())
{
// Fetch the querystring holding the fileId
int fileId = Int32.Parse(Request.QueryString["fileId"].ToString());
// Get the file from the database
using (DataSet dsDoc = db.GetFileById(fileId))
{
// Check wether we got a file to return or not
if (dsDoc.Tables[0].Rows.Count == 1)
{
DataRow dr = dsDoc.Tables[0].Rows[0];
byte[] fb = (byte[])dr["doc"];
// Set the correct ContentType based upon the filetype
Response.ContentType = dr["docContentType"].ToString();
//Add the filename for the attachment
Response.AddHeader("content-disposition", "attachment;filename=" + dr["fileName"].ToString());
// Add the file to the output
Response.BinaryWrite(fb);
}
}
}
}
catch(Exception rror){Response.Write(rror.Message);}
}
Well, that's all there is to it!