Q
Problem solve Get help with specific problems with your technologies, process and projects.

How can I store Word document in a SQL Server table?

I want to store versions of a Word document in SQL Server via ASP.NET pages with VB.NET code behind. I know you can link to Word documents and have them displayed, but for auditing purposes I would like to store the actual Word document in a SQL Server table along with the login ID of the person storing the document and when it was last stored. Thanks for any help.
You should use a column of type IMAGE in SQL Server to store the document. Next, you should create a pair of stored procedures to insert and retrieve that field. The insert SP should receive a parameter of type IMAGE. If you have your document in a Stream, you can pass it to the SP using the following code (and the MS Data Access Application Block):

Stream doc;
//Get your doc some way.
try
{
 byte[] bytes = new byte[doc.Length];
 doc.Seek(0, SeekOrigin.Begin);
 doc.Read(bytes, 0, (int) doc.Length);
 SqlHelper.ExecuteNonQuery(_dbstore, "Document_Insert", 
  new object[] { docId, new SqlBinary(bytes) });
}
finally 
{
 doc.Close();
} 

Note that I read the stream data into an array of bytes, so that I can create the SqlBinary object with them. I downcasted doc.Length to an int because that's what Stream.Read expects, even if the stream length is a Long data type. Downcasting it to an int may result in an InvalidCastException -- but only if the doc has more than 2,147,483,647 bytes! (That's an Int32 maximum value.) That's a 2 GB document, and I'd say that if you expect that size to be reached, you should think of another solution instead of storing in databases.

On the other hand, retrieving it is simply enough that maybe you don't even need an SP:

byte[] data = (byte[])
 SqlHelper.ExecuteScalar(_dbstore, "Document_Select", 
  new object[] { docId });

This SP would simply perform a select with the ID and return the IMAGE field. With those bytes you can load a Stream or whatever.

Dig Deeper on Win Development Resources

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchCloudComputing

SearchSoftwareQuality

TheServerSide.com

Close