Jonas Stawski

Everything .NET and More

Save documents with SQL Server and display them

So you have the need to let the user upload documents of any type to your server, but you don't know how to implement it. Some people say store them in the DB, others hate that and store the files on the file system. Which way do you go? Well, that depends on the requirements and the way the application is designed. I really try to stay away from storing binary content on the DB, but sometimes it is the best approach. On my case I didn't want a user accessing the file in any other way than through the User Interface of the application. And since the web application was using Windows Authentication and impersonation if I would have stored the files on the file system I would have had to give access to those files to the users accesing the app, which in turn would have given them access to the users to access the files through the network. So DB was the answer to my problems.

Ok, so how do I go about doing this?

First you need a table where the files will be stored. My table looks like this:

FileId [int], FileName [nvarchar(100)], Extension [nvarchar(10)], Content [binary(max)]

The FileId is the key of the table, the FileName is the original name of the file, the Extension is the file extension, and the Content is the content of the file. Why do we need an extension column? You don't really need it, but it's nice to have it, because if you want to have Full-Text index on the content of the file (for searching content inside the binary data), SQL server needs to know the extension of the file.

Below is the code used to insert the data into the table:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
//Split the filename to get the original filename 
//and the extension
String[] FileParts = upl.PostedFile.FileName.Split('.');
String[] FileParts2 = upl.PostedFile.FileName.Split('\\');

SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "insert into FileContent (FileName, ";
cmd.CommandText += "Extension, Content) ";
cmd.CommandText += "values (@FileName, ";
cmd.CommandText += " @Extension, @Content) ";
cmd.Parameters.Add("@FileName", SqlDbType.Nvarchar);
cmd.Parameters[0].Value = FileParts2[FileParts2.Length - 1];
cmd.Parameters.Add("@Extension", SqlDbType.Nvarchar);
cmd.Parameters[1].Value = FileParts[FileParts.Length - 1];
//Get the binary data of the file
Stream fs = upl.PostedFile.InputStream;
BinaryReader br = new BinaryReader(fs);
Byte[] ba = br.ReadBytes(fs.Length);
cmd.Parameters.Add("@Content", SqlDbType.VarBinary);
cmd.Parameters[2].Value = ba;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
      

Great, I now have the data stored on the DB, how do I display it? Well, we could take advantage of contenttypes of the browser and open a web form passing a querystring parameter with the id of the file to display.

 

1
2
3
4
5
int docId = Convert.ToInt32(Request.QueryString("DocId"));
GetDocument(docId);
Response.ContentType = GetContentType(extension);
Response.BinaryWrite(b);
Response.End();

where GetDocument function looks like this:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
private void GetDocument(int docId)
{
   SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "Select Extension, Content";
cmd.CommandText += "from FileContent where FileId = @FileId";

cmd.Parameters.Add("@FileId", SqlDbType.Int);
cmd.Parameters.[0].Value = docId;

conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
   extension = dr["Extension"].ToString();
b = (Byte[])dr["Content"];
}
conn.Close();
}

and GetContentType function looks like this:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
private string GetContentType(string ext)
{
   //Here you will have a case for each type you use
switch(ext)
{
   case "PDF":
   return "application/pdf";
      case "DOC":
   return "application/msword";
case "XLS":
   return "application/vnd.ms-excel";
case "PPS":
   return "application/vnd.ms-powerpoint";
default:
   return "text/html";
   }
}

Hope this helps you.

Happy programming!

Comments (8) -

this is an excellent article

Reply

This is a great article

Reply

Nice !!!

Reply

Prahlad Borah
Prahlad Borah

how can i do the same thing using vb code

Reply

that was helpful, thanks

Reply

how saved documents can be edited and saved again without uploading again.

Reply

shibathethinker
shibathethinker

Cool article..thank you

Reply

Jayesh Sorathia
Jayesh Sorathia

Very Good article.

To learn more on this Visit this article.

jayeshsorathia.blogspot.com/.../...base-table.html" title="jayeshsorathia.blogspot.com/.../...base-table.html">jayeshsorathia.blogspot.com/...

Reply

Pingbacks and trackbacks (1)+

Add comment

biuquote
Loading