This project is read-only.

Saving zip file to SQL

Mar 16, 2010 at 11:58 PM

Hello, great software you have, very easy to use! 

I'm trying to figure out the best way to save my zipe file to SQL (VarBinary[MAX] col), I believe that's the best column type to use?

So I'm reading the zipfile using a Filestream obj and then converting that into a byte array and saving to the DB. 

My problem is, I can read back from DB but don't know how to convert back into a Zipfile obj and save back out to file system. I tried to serialize the Zipfile class , but the exception I got was "Not serializable". 

Any help would be greatly appreciated,

Mar 17, 2010 at 6:07 AM

Hello! and thanks for using the forum.

First, I'm going to recommend that you use streams.  Both SQL Server and DotNetZip support streams nicely.

In SQL Server, it's possible to open a stream directly onto a BLOB (or VarBinary MAX), for reading or writing.   With DotNetZip, you can save a ZipFile into any Stream object, and you can read a zip file from any stream.  You can plug the two together fairly easily.  There are some gotchas or limitations.

SQL 2008's FILESTREAM makes this easy

If you have SQL 2008, and you've enabled the FILESTREAM capability, the type you would use is the SqlFileStream.  This works for reading or writing.  Microsoft has a nice article with examples.

Reading a zip file from SQL2008 is as simple as opening the SqlFileStream and instantiating a ZipFile type, passing that stream to the constructor.  Saving a zip file into SQL2008 works similarly; just call the ZipFile.Save() method that accepts a stream, and pass the SqlFileStream.  This will work nicely. 


If you're using an older version of SQL, or SQL2008 without FILESTREAM enabled, you have to take other approaches.  For inserting data into SQL Server, as you would when saving a zip into the db, you can use the UPDATETEXT verb, following the example given by Microsoft.   But there's a catch - with UPDATETEXT your app must provide a buffer that contains the data, which SQL Server then *reads*.  Thsi isn't how a DotNetZip save works.  So you need a two-step save process:  Save the zip to a filesystem file, and then use UPDATETEXT to insert that data into the database. You could also save the zipfile into a MemoryStream.  But this of course consumes more memory. 

For reading, if you use a SqlDataReader, you can get a SqlBytes data type, and that type exposes a Stream property. You can then read from that stream as from any stream.  You can then instantiate a ZipFile, passing that stream to the constructor.  That will work nicely, if you'd like to extract files from that zip file, into the filesystem.  The downside is, I believe,  that the SqlBytes.Stream implementation apparently keeps all the varbinary(max) data in memory.  So it is a memory hungry implementation.  If you have a large zip file, say, 500MB, this may present a problem for you. Putting all that into memory might not be practical.

To avoid that, assuming you are NOT using the FILESTREAM capability, you could extract all the data from SQL Server and write it to a filesystem file.  Then instantiate the ZipFile from the file, as normal. You can't use SqlBytes to do the extraction, though - that would cause the same memory consumption problem you're trying to avoid. 

Also consider ZipInputStream

There's also a forward-only zip stream decorator type in DotNetZip called the ZipInputStream.  It wraps around another stream, and can be used to extract zip entries.  Consider it an alternative interface to the ZipFile class, specially designed to do read-only operations on zip files. It should work nicely with the SqlBytes.Stream that you obtain from SQL Server, or the SqlFileStream if you have that capability.  Where ZipFile provides a random-access capability for the entries in a zip file - in other words you can retrieve entries by name, by size, by anything you like - ZipInputStream provides a sequential access model.  For each entry, you can extract it, or skip to the next entry. 

You said:

I can read back from DB but don't know how to convert back into a Zipfile obj and save back out to file system. I tried to serialize the Zipfile class , but the exception I got was "Not serializable".

That's a little different than the title of the thread, where you seem to ask about saving a zip INTO sql server.

I don't understand what part you are saving into the filesystem.  If you only want to save the zipfile into the filesystem, then you can just read from SQL Server, and copy those bytes into a  filesystem file, and name the file with the .zip extension.  That's clear, right?  If you want to extract from the zip file, then you can follow the suggestions I gave above.

I hope this is helpful.

Good luck.