locked
Extract multiple files from a Database table, then zip them into one file. RRS feed

  • Question

  • User1831766732 posted

    The subject says it all.  I've searched but haven't found a sample of what I'm trying to acomplish. 

    Basically I have files stored in a SQL Server table and I want to be able to select the files from a datagrid with a checkbox (this part is done) then extract the files selected and place them all in a ZIP file.  I've downloaded the SharpZipLib.dll, but the samples I've seen all show files physically in a folder or in a file structure, not embedded in a table.

     Here's quick run down of what I have.

    Files stored in a table

     

    CREATE TABLE [dbo].[W_Attachments](
    	[AttachmentID] [int] IDENTITY(1,1) NOT NULL,
    	[WID] [int] NULL,
    	[DocName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[FileSize] [real] NULL,
    	[FileName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[FileData] [image] NULL,
    	[ContentType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[NTLogin] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[DisplayName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[UploadDate] [smalldatetime] NULL CONSTRAINT [DF_Well_Attachments_UploadDate]  DEFAULT (getdate()),
    	[FromType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[DocType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	[DocStatus] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

     When listing a file on the datagrid (not gridview) I have a  link displayed as such in on of the grid view columns.

     
    <a href=viewFile.aspx?AttachmentID=' + CONVERT(varchar, a.AttachmentID) 
                          + ' target=_blank>' + a.FileName + '</a>
     

    So onclick the viewFile.aspx is invoked.  That code writes to the OuputStream for dowload.  The code looks like this

    1        Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    2            'Put user code to initialize the page here
    3    
    4            conn = New SqlConnection(ECO)
    5            conn.Open()
    6            cmd = New SqlCommand("SELECT FileSize, FileName, FileData, ContentType FROM W_Attachments WHERE (AttachmentID = @AttachmentID) ")
    7            cmd.Connection = conn
    8    
    9            cmd.Parameters.Add("@AttachmentID", SqlDbType.Int)
    10           cmd.Parameters("@AttachmentID").Value = Request("AttachmentID").ToString
    11   
    12           Rdr = cmd.ExecuteReader
    13           If Rdr.Read Then
    14               Response.ContentType = Rdr("ContentType").ToString
    15               Response.OutputStream.Write(CType(Rdr("FileData"), Byte()), 0, CInt(Rdr("FileSize")))
    16               Response.AddHeader("Content-Disposition", "attachment;filename=" + Rdr("FileName").ToString())
    17           Else
    18               Response.Write("File Not Found.")
    19           End If
    20   
    21       End Sub
    
     

     

    Viola...you can open or save the file....Now to the real problem.  I've created a FileSearch page to list all the files the user wants to find in a Gridview, and have one column with a checkbox in it.  The user can select multiple files listed, click a button and I want it to extract the files from the W_Attachment table and place them in a sigle zip file, preferably the same way I'm extracting a single file to the output stream, so I'm not storing physical files locally.

    Here's the code that walks thru the data grid.. i just need that little bit that will extract and zip it up into one file

    1        Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
    2            '***********************
    3            '*  ZIP CHECKED FILES  *
    4            '***********************
    5            Dim Item As DataGridItem
    6            Dim cbItem As New HtmlInputCheckBox
    7            Dim key As String
    8    
    9            For Each Item In Datagrid1.Items
    10               cbItem = Item.FindControl("cbItem")
    11               If (cbItem.Checked) Then
    12                   ' Grab the value of the checkbox which is the KeyId field
    13                   key = cbItem.Value ' Value of Attachment_ID in the W_Attachment table
    14   
    15                   '  OK EXTRACT EACH FILE HERE, THEN ADD TO ZIP FILE
    16   
    17   
    18               End If
    19           Next
    20   
    21   
    22   
    23       End Sub
    

     

     

    Thanks for your help

    Thursday, November 13, 2008 9:51 AM

Answers

  • User1831766732 posted

    I discovered that the problem appeared to be writing directly to the Response.OutputStream. So, I modified the code so that the initial stream being passed to the ZipOutputStream constructor was a MemoryStream.  The end result is below and it works like a charm!  Problem solved.

     

    1        Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
    2            '***********************
    3            '*  ZIP CHECKED FILES  *
    4            '***********************
    5            Dim Item As DataGridItem
    6            Dim cbItem As New HtmlInputCheckBox
    7            Dim key As String
    8            '  Create a MemoryStream to write to
    9            Dim ms As MemoryStream = New MemoryStream
    10   
    11           Dim strmZipOutputStream As ZipOutputStream
    12           'strmZipOutputStream = New ZipOutputStream(File.Create(Server.MapPath("test.zip")))
    13           strmZipOutputStream = New ZipOutputStream(ms)
    14           strmZipOutputStream.SetLevel(9)
    15   
    16           For Each Item In Datagrid1.Items
    17               cbItem = Item.FindControl("cbItem")
    18               If (cbItem.Checked) Then
    19                   ' Grab the value of the checkbox which is the KeyId field
    20                   key = cbItem.Value
    21   
    22                   conn = New SqlConnection(dsn)
    23                   conn.Open()
    24                   cmd = New SqlCommand("SELECT FileSize, FileName, FileData, ContentType FROM Well_Attachments WHERE (AttachmentID = " & key & ") ")
    25                   cmd.Connection = conn
    26   
    27                   Rdr = cmd.ExecuteReader
    28                   If Rdr.Read Then
    29                       Dim objZipEntry As ZipEntry = New ZipEntry(Rdr("FileName").ToString())
    30                       objZipEntry.DateTime = DateTime.Now
    31                       objZipEntry.Size = CInt(Rdr("FileSize"))
    32                       strmZipOutputStream.PutNextEntry(objZipEntry)
    33                       strmZipOutputStream.Write(CType(Rdr("FileData"), Byte()), 0, CInt(Rdr("FileSize")))
    34   
    35                   End If
    36   
    37               End If
    38           Next
    39   
    40           strmZipOutputStream.Finish() 'save zip 
    41           Response.ContentType = "application/zip"
    42           Response.AddHeader("Content-Disposition", "attachment; filename=TrackingSearch.zip")
    43           ms.WriteTo(Response.OutputStream)
    44           Response.End()
    45           strmZipOutputStream.Close()
    46   
    47       End Sub
    
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 18, 2008 9:42 AM

All replies

  • User1831766732 posted

    No takers yet?

    I found a sample to zip up an entire directory to the output stream.  I guess I just need to replace that with something similar to my viewfile.aspx code

     

    1    Dim astrFileNames() As String = Directory.GetFiles(sourceDir) '<---- Directory to ZIP
    2                Dim strmZipOutputStream As ZipOutputStream
    3    
    4                strmZipOutputStream = New ZipOutputStream(File.Create(targetName))
    5    
    6                REM Compression Level: 0-9
    7                REM 0: no(Compression)
    8                REM 9: maximum compression
    9                strmZipOutputStream.SetLevel(9)
    10   
    11               Dim strFile As String
    12   
    13               For Each strFile In astrFileNames  '  <-- Instead use an ExecReader loop?
    14                   Dim strmFile As FileStream = File.OpenRead(strFile)
    15                   Dim abyBuffer(strmFile.Length - 1) As Byte
    16   
    17                   strmFile.Read(abyBuffer, 0, abyBuffer.Length)
    18                   Dim objZipEntry As ZipEntry = New ZipEntry(strFile)
    19   
    20                   objZipEntry.DateTime = DateTime.Now
    21                   objZipEntry.Size = strmFile.Length
    22                   strmFile.Close()
    23                   strmZipOutputStream.PutNextEntry(objZipEntry)
    24                   strmZipOutputStream.Write(abyBuffer, 0, abyBuffer.Length)
    25   
    26               Next
    27   
    28               strmZipOutputStream.Finish()
    29               strmZipOutputStream.Close()
    

      

    Something like this possibly?

    'INSIDE MY For Each Item In Datagrid1.Items LOOP

     conn = New SqlConnection(ECO)

     conn.Open()

               cmd = New SqlCommand("SELECT FileSize, FileName, FileData, ContentType FROM W_Attachments WHERE (AttachmentID = " & KEYID & ) ") ' ATTACHMENT ID FROM SELECTED DATAGRID
              cmd.Connection = conn
      
              Rdr = cmd.ExecuteReader
              If Rdr.Read Then

     strmZipOutputStream.PutNextEntry (CType(Rdr("FileData"))

              End If

     

     

     

    Monday, November 17, 2008 1:46 PM
  • User1831766732 posted

     ok, here's what I came up with.  The end result is an error "The Compressed (zipped) Folder is invalid or corrupted". Can someone show me the error of my ways?

    1        Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
    2            '***********************
    3            '*  ZIP CHECKED FILES  *
    4            '***********************
    5            Dim Item As DataGridItem
    6            Dim cbItem As New HtmlInputCheckBox
    7            Dim key As String
    8    
    9    
    10           Response.ContentType = "application/octet-stream" 'set response to give a file 
    11           Response.AddHeader("Content-Disposition", "attachment; filename=""test.zip""")
    12   
    13   
    14           Dim strmZipOutputStream As ZipOutputStream
    15           strmZipOutputStream = New ZipOutputStream(File.Create(Server.MapPath("test.zip")))
    16           strmZipOutputStream.SetLevel(9)
    17   
    18           For Each Item In Datagrid1.Items
    19               cbItem = Item.FindControl("cbItem")
    20               If (cbItem.Checked) Then
    21                   ' Grab the value of the checkbox which is the KeyId field
    22                   key = cbItem.Value
    23   
    24                   conn = New SqlConnection(dsn)
    25                   conn.Open()
    26                   cmd = New SqlCommand("SELECT FileSize, FileName, FileData, ContentType FROM Well_Attachments WHERE (AttachmentID = " & key & ") ")
    27                   cmd.Connection = conn
    28   
    29                   Rdr = cmd.ExecuteReader
    30                   If Rdr.Read Then
    31                       Dim objZipEntry As ZipEntry = New ZipEntry(Rdr("FileName").ToString())
    32                       objZipEntry.DateTime = DateTime.Now
    33                       objZipEntry.Size = CInt(Rdr("FileSize"))
    34                       strmZipOutputStream.PutNextEntry(objZipEntry)
    35                       strmZipOutputStream.Write(CType(Rdr("FileData"), Byte()), 0, CInt(Rdr("FileSize")))
    36   
    37                  End If
    38   
    39   
    40   
    41               End If
    42           Next
    43   
    44   
    45           strmZipOutputStream.Finish() 'save zip 
    46           strmZipOutputStream.Close() 'close zip 
    47   
    48       End Sub
    

     

     

    Monday, November 17, 2008 3:43 PM
  • User1831766732 posted

    I discovered that the problem appeared to be writing directly to the Response.OutputStream. So, I modified the code so that the initial stream being passed to the ZipOutputStream constructor was a MemoryStream.  The end result is below and it works like a charm!  Problem solved.

     

    1        Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
    2            '***********************
    3            '*  ZIP CHECKED FILES  *
    4            '***********************
    5            Dim Item As DataGridItem
    6            Dim cbItem As New HtmlInputCheckBox
    7            Dim key As String
    8            '  Create a MemoryStream to write to
    9            Dim ms As MemoryStream = New MemoryStream
    10   
    11           Dim strmZipOutputStream As ZipOutputStream
    12           'strmZipOutputStream = New ZipOutputStream(File.Create(Server.MapPath("test.zip")))
    13           strmZipOutputStream = New ZipOutputStream(ms)
    14           strmZipOutputStream.SetLevel(9)
    15   
    16           For Each Item In Datagrid1.Items
    17               cbItem = Item.FindControl("cbItem")
    18               If (cbItem.Checked) Then
    19                   ' Grab the value of the checkbox which is the KeyId field
    20                   key = cbItem.Value
    21   
    22                   conn = New SqlConnection(dsn)
    23                   conn.Open()
    24                   cmd = New SqlCommand("SELECT FileSize, FileName, FileData, ContentType FROM Well_Attachments WHERE (AttachmentID = " & key & ") ")
    25                   cmd.Connection = conn
    26   
    27                   Rdr = cmd.ExecuteReader
    28                   If Rdr.Read Then
    29                       Dim objZipEntry As ZipEntry = New ZipEntry(Rdr("FileName").ToString())
    30                       objZipEntry.DateTime = DateTime.Now
    31                       objZipEntry.Size = CInt(Rdr("FileSize"))
    32                       strmZipOutputStream.PutNextEntry(objZipEntry)
    33                       strmZipOutputStream.Write(CType(Rdr("FileData"), Byte()), 0, CInt(Rdr("FileSize")))
    34   
    35                   End If
    36   
    37               End If
    38           Next
    39   
    40           strmZipOutputStream.Finish() 'save zip 
    41           Response.ContentType = "application/zip"
    42           Response.AddHeader("Content-Disposition", "attachment; filename=TrackingSearch.zip")
    43           ms.WriteTo(Response.OutputStream)
    44           Response.End()
    45           strmZipOutputStream.Close()
    46   
    47       End Sub
    
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 18, 2008 9:42 AM