I'm currently working on a project that requires storing a substantial amount of text in a SQL server. The project is a rewrite of an older solution that was implemented using MySQL, and the MySQL table file currently is something like 6 GIG!!! We're constantly battling corruption with the table, so amongst other reasons we are rewriting to run with SQL Server.

Now, the text contained in this column does not need to be queried, and will only be brought back one record at a time, so I figure, why not ZIP the data?

As it turns out, this is much easier than one would think. The wonderful SharpZipLib (part of the SharpDevelop project), makes zipping files and data as easy as writing to a stream. One thing that was lacking though, was a good example of zipping STRING data. We have a string, and we are zipping that and storing the bytes in the database. It's not too terribly difficult, but I figure I'll post the code here in case it helps somebody out...

Most of the code below was gleened from this page...

Public Function ZipString(ByVal stringToZip As String) _
    As Byte()
    Dim inputByteArray As Byte()
inputByteArray = Encoding.UTF8.GetBytes(stringToZip)
    Dim ms As New MemoryStream
    Dim zipOut As New ZipOutputStream(ms)
    Dim entry As New ZipEntry("ZippedFile")
zipOut.PutNextEntry(entry)
zipOut.SetLevel(9)
zipOut.Write(inputByteArray, 0, inputByteArray.Length)
zipOut.Finish()
zipOut.Close()
    'return the zipped contents...
    Return ms.ToArray
End Function

Public Function UnZipString(ByVal bytesToUnzip As Byte()) _
    As String
    Dim ms As New MemoryStream(bytesToUnzip)
    Dim ret As New MemoryStream
    Dim zipIn As New ZipInputStream(ms)
    Dim entry As ZipEntry = zipIn.GetNextEntry()
    Dim buffer(2047) As Byte
    Dim size As Integer = 2048
    While (True)
size = zipIn.Read(buffer, 0, buffer.Length)
        If (size > 0) Then
ret.Write(buffer, 0, size)
        Else
            Exit While
        End If
    End While
    Return Encoding.UTF8.GetString(ret.ToArray())
End Function