Storing binary data on SQL Server
On my current project, an ASP .NET application, the customer expressed the desire to save in the database letters, based on Word templates and automatically generated by the application. Being official government letters, if in the future you need print a copy of that document it has to be exactly the same document, although some of the data on which the document was originally based could have changed. So they need them to be stored in the database exactly as they were generated once. Therefore, we need a way to store the Word files as a binary format, as a data stream to be able to reload them again in the future and have the exact same document.
Please note: it’s not the objective of this post to show how to create Word files from C#. You can use for it Word automatization, or third-party components such as the excellent ones made by Aspose.
Let’s create a simple SQL Server table to store the documents. For the sake of this example, we call it BinaryDocs, and it will have the following design:
| Field | Type | Nullable? |
| DocId | Int (identity) | No |
| Document | Image | No |
| DocName | VarChar(100) | No |
Pretty self-explanatory: DocId is an autonumeric primary key field. Document is the one where the bit stream is going to be stored, that is the proper Word document. DocName will store the original name the document had when it was generated.
After that will create an stored procedure, UploadDoc, which will handle the actual insertion of records on BinaryDocs:
AS
INSERT INTO BinaryDocs (Document, DocName) values (@doc, @name)
GO
Colorized by: CarlosAg.CodeColorizer
Quite self-explanatory too: a simple INSERT INTO instruction which receives an Image and a VarChar fields as parameters.
And this is the method that stores the data on the database:
{
//New FileStream object for reading the Word file as a binary stream
System.IO.FileStream fs = new FileStream(sPath + sFile,
System.IO.FileMode.Open);
//On this byte array we’ll store the binary data read by fs.
Byte[] data = new byte[fs.Length];
//Like this
fs.Read(data, 0, Convert.ToInt32(fs.Length));
//Open up a SQL Server connection. In this particular case the database
//connection string is recovered from the web.config file
//using the ConfigurationSettings class
SqlConnection cnn =
new SqlConnection(ConfigurationSettings.AppSettings["DBConnection"]);
cnn.Open();
//An StoredProcedure SqlCommand object to invoke the
//UploadDoc stored procedure created earlier
SqlCommand cmd = new SqlCommand("UploadDoc", cnn);
cmd.CommandType = CommandType.StoredProcedure;
//This are the expected parameters
cmd.Parameters.Add("@doc", data); //the Word file data
cmd.Parameters.Add("@name", sFichero); //and its name
//We execute the stored procedure, which inserts a new
//row on BinaryDocs which the our data
cmd.ExecuteNonQuery();
//Let’s close the connection and the file (althought //we could have closed the file earlier)
cnn.Close();
fs.Close();
}
Colorized by: CarlosAg.CodeColorizer
Now let’s take a look at the method to retrieve that stream from the database and display it as a Word file:
{
//Let’s open the connection, exactly as before
SqlConnection cnn =
new SqlConnection(ConfigurationSettings.AppSettings["DBConnection"]);
cnn.Open();
//This is the command to open the record we want. For this
//example we open the first record, you should have to modify this //code and give the method a parameter stating which record
//to retrieve from the database.
SqlCommand comm = new SqlCommand("SELECT * FROM BinaryDocs " +
" WHERE docId = 1", cnn);
comm.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds = new DataSet("BinaryData");
da.Fill(ds);
//We create a byte array which will have the bytes stored on the
//table’s Document field
byte[] bits = ((byte[])(ds.Tables[0].Rows[0].ItemArray[1]));
cnn.Close();
//now let’s save that data stream as a normal file on the HDD,
//a temporal file you could later delete.
//For avoiding possible concurrency problems,
//we generate a unique name for the file.
string sFile = "tmp" + makeFileName() + ".doc";
//A new FileStream object, which will be the one to create
//the file with the specified name.
FileStream fs = new FileStream(Server.MapPath(".") +
@"\GenDocs\" + sFile, FileMode.Create);
//And we save on disk the byte array which contains the Word data
fs.Write(bits, 0, Convert.ToInt32(bits.Length));
fs.Close();
//For displaying the file, we use a Javascript function
//called showFile, which simply loads the specified
//file, and we show it on a pop-up
string script = "<script languaje=’javascript’> ";
script += "showFile(’GenDocs/" + sFile + "’) ";
script += "</script>" + Environment.NewLine;
Page.RegisterStartupScript("showFile",script);
}
Colorized by: CarlosAg.CodeColorizer
And that’s it. With this last method the Word file is loaded from the database as a byte stream and it’s displayed on a popup browseer window.
Just for the sake of completion, this is the Javascript function that loads the generated file from the database:
function showFile(sFile) {
window.open(sFile,null,"directories=no,height=600,
width=800,left=0,top=0,location=no,menubar=yes,
status=no,toolbar=yes,resizable=yes")
document.forms(0).submit();
}
</script>
Colorized by: CarlosAg.CodeColorizer
And this is the method makeFileName(), which uses the server’s tick count for creating randomly unique filenames.
{
int lastTick = 0;
while(lastTick==Environment.TickCount)
{
System.Threading.Thread.Sleep(1);
}
lastTick=Environment.TickCount;
return DateTime.Now.ToString("yyyyMMddhhmmss") + "." +
lastTick.ToString();
}
Colorized by: CarlosAg.CodeColorizer
