zip files direct from SQL Select Query

Apr 29, 2011 at 4:58 PM

able to zip files that are on my drive, neat and simple!


But my goal is asp.net VB web application where user clicks a button command, which runs a "select" query on MSSql table with 3 fields:
-uniqueID  (integer)

-DATfile (varbinary - each row is a small data file~14kB each)

-catLabel (string)


I am new to asp.net, but I think I am stuck getting files to the arraylist.  In examples you often use the variable: filesToInclude for the arraylist.


any snippet or help to get files from the database to that variable would be appreciated!!!


(typically about 10 to 30 files need to zipped & downloaded at a time)
thanks

Coordinator
May 7, 2011 at 2:22 PM

The DATfile column holds the bytes that make up the file to be included into the zip, is that right?

I'm not a database expert, so I don't know how precisely to do this, but....  What you need to do is get a readable stream from that DATfile column; that will make it easy to embed into a zipfile.

var c1= new System.Data.SqlClient.SqlConnection(connstring1);
var cmd=  new System.Data.SqlClient.SqlCommand(strSelect, c1);

var sqlReader = cmd.ExecuteReader();

using(Ionic.Zip.ZipFile zip = new Ionic.Zip.ZipFile())
{
    while (reader.Read()) 
    {
         zip.AddEntry(zipEntryName, (name,stream) => {
             byte[] rawData = new byte[size];
             reader.GetBytes(2,0,rawData,0,rawData.Length);
             stream.Write(rawData,0,rawData.Length);
         });
   }
    reader.Close();
     zip.Save(Response.OutputStream);
}

The above uses the ZipFile.AddEntry() method that accepts a WriteDelegate. Check the documentation for more information on it.