When considering how to store documents in SQL Server database it is worth considering that a database is designed to store data, while a file system is designed for storing files.
That said, there will be occasions where you will want to store documents in SQL Server. This isn’t just for the convenience of having everything in one place to simplify backup – in many systems, documents can form part of the data being modelled so should be included in the logical and physical design of the database.
The capabilities for handling document storing in previous versions of SQL Server were cantankerous at best. The Binary Large Object (BLOB) data types in SQL Server 2000 were workable, but required very different code for accessing and updating data than other data types. These have been deprecated from SQL Server 2005 in favour of the large value data types, which are an improvement yet still feel somehow unsatisfactory.
Using the XML data type to store a document
The introduction of the XML data type into SQL Server 2005 does offer us the opportunity to take a different approach for document storage. The key is the CDATA section of an XML document – you can use this section to store pretty much anything that you want.
The System.Convert namespace provides us with the means of serialising any disk file into text, whether it has been passed in a web request or saved as a disk file. Once the file has been read by a Stream object the resulting byte[] array representing the file can be converted into a string using the System.Convert.ToBase64String method.
The code sample below shows how to serialise a disk file ready for inclusion in an XML document:
public string ReadDiskFile(string filepath) { FileStream inputFile = new FileStream(filepath, FileMode.Open); byte[] fileContents = new byte[inputFile.Length]; int readOffset = 0; int bytesRemaining = (int)(inputFile.Length); //* Read the disk file, ensuring that we read the entire file while (bytesRemaining > 0) { int read = inputFile.Read(fileContents, readOffset, bytesRemaining); bytesRemaining -= read; readOffset += read; } //* Don’t forget to close and dispose of the FileStream object inputFile.Close(); inputFile.Dispose(); //* Convert our byte stream into a Base64 string return System.Convert.ToBase64String(fileContents); }
Once we have a string representing the file content then we can place it in an XML CDATA section. The code sample below creates a simple XML document, providing a single element that contains the file content.
public XmlDocument FormatXmlDocument(string fileContent, string filename) { //* Create an XML document XmlDocument fileXml = new XmlDocument(); //* Create a first node for the document XmlElement documentElement = fileXml.CreateElement("file"); documentElement.SetAttribute("filename", filename); //* Create a CDATA section containing the Base64 string XmlCDataSection cdataContents = fileXml.CreateCDataSection(fileContent); //* Append our nodes to the XML document documentElement.AppendChild(cdataContents); fileXml.AppendChild(documentElement); return fileXml; }
This XML document can be readily written to and read from a SQL Server 2005 database using the SQL XML data type. Retrieving and re-creating the file is straightforward – the System.Convert.FromBase64String() method allows you to create a byte array from the string ready for writing to a file or any other stream. The example below shows how to write a disc file from an XML document.
public void WriteXmlDocument(XmlDocument fileXml, string path) { //* Get the filename of the document string fileName = fileXml.DocumentElement.GetAttribute(“filename”); //* Get the filecontent byte[] fileContent = System.Convert.FromBase64String(fileXml.DocumentElement.ChildNodes[0].Value); //* Write the file to the specified path FileStream newFile = new FileStream(path + filename, FileMode.CreateNew); newFile.Write(fileContent, 0, (int)fileContent.Length); newFile.Flush(); //* Don’t forget to close and dispose of the FileStream object newFile.Close(); newFile.Dispose(); }