Sample Compression Projects
You can download three sample .NET projects from the SQL Server Magazine Web site (http://www.sqlmag.com, InstantDoc ID 49065) to see how to use the System.IO .Compression namespace to compress and decompress data from within SQL Server 2005 or outside of it in a standard Windows Forms application. These sample projects use the AdventureWorks database because it contains data well suited for a compression test.The Document table in the Production schema has uncompressed Word documents stored in the Document column, which has a data type of varbinary(max).
To keep things simple in the first sample project,we didn't perform any database access. The program is a simple Windows Forms application that lets you pick a file, specify a compression method, and see the percentage of compression.The application also displays the elapsed time to compress the file and the time to decompress the file, as Figure 1 shows.
Because the process of compressing and decompressing data by using DeflateStream and GZip Stream are the same, you can encapsulate the logic for both compression algorithms into the same subroutine or function. As Listing 1 shows, the Compress Wrapper class contains the Compress and Decompress methods, both of which have a parameter to specify your choice of compression algorithm.The compression code needs to copy an uncompressed stream to a compressed stream.The decompression code needs to copy a compressed stream to a decompressed stream.You can encapsulate these two stream-copy operations into one generic stream-copy function. (For a C# version of CompressWrapper, see Web Listing 1.)
Notice at callout A in Listing 1 that the CopyStream method accepts Stream objects as parameters. Because both the Deflate Stream and GZipStream classes inherit from the Stream class, they are of type Stream.When Deflate Stream and GZipStream stream objects are instantiated, the second parameter of the constructor indicates whether the stream will contain compressed or decompressed data.When your application passes Stream objects as parameters to a method, you can call the Stream objects' Read and Write methods from within the method, which is what CopyStream does. If, for example, an uncompressed stream object is in the input parameter and a GZipStream object is the output parameter, CopyStream uses the uncompressed stream's Read method to get a chunk of uncompressed data from the input stream.The application then uses the compressed stream's Write method to compress the chunk and transfer the compressed data into the GZipStream output stream. Similarly, if a compressed GZipStream object is the input parameter and an uncompressed stream object is the output parameter, CopyStream uses the compressed stream's Read method to get a chunk of compressed data and uncompress it. The application then uses the compressed stream's Write method to transfer the uncompressed chunk into the uncompressed output stream. CopyStream uses a loop to call the Read and Write methods in chunks so that you don't have to specify the total number of bytes to compress or decompress.
Now that you've seen the basic approach to compression and decompression, the next step is to create a SQL Server project and run the CompressWrapper class in the SQLCLR, which is what our second sample project shows. (For more information about how to write and deploy CLR code in SQL Server 2005, see "Making the Most of the CLR," May 2005, InstantDoc ID 45673.) In the Solution Explorer, right-click the SQL Server project and select Add, New Item from the context menu. You'll see several available database object templates; click User-Defined Function.VS will create a class with the necessary Imports statements along with a sample scalar function. Before you modify this class, add our compression wrapper class (CompressWrapper.vb) to the project by clicking Add, Existing Item. As mentioned earlier, this class encapsulates compression/decompression logic for reusability. You might be thinking that for true reusability, we should compile this class into a separate assembly—and you'd be right.With that said, using custom assemblies in a SQL Server project requires a bit more setup. (See the Web-exclusive sidebar "Can I Have a Reference?" http://www.sqlmag.com, InstantDoc ID 49067, for tips about referencing custom .NET assemblies). So, we chose to stick with reuse at the class level. Import CompressWrapper.vb into your function class file, and add the two SQL function calls that Web Listing 2 shows. (For a C# version of these UDFs, see Web Listing 3.) Deploy the project by using the VS 2005 Deploy option.
Varbinary(max) to the Rescue
By looking at the SqlType class name, you can usually easily map the class to the corresponding SQL Server data type (e.g., Sql Money, SqlInt32). Because the compressed data is binary data, it's stored in the new varbinary(max) data type, which maps to the SqlBytes data type.
As we noted earlier, Microsoft has deprecated the image data type.Varbinary(max) replaces the image data type and is critical for our functions. If you've tried to work with an image data type inside a SQL Server 2000 stored procedure, you know that image data types are invalid for local variables. Moreover, although you can pass an image variable into a stored procedure, you can't change or select the value of the variable into other variables within the procedure itself. Varbinary(max) makes working with large binary data as easy as working with numeric data.
Prev. page
1
[2]
3
next page 
