Export multiple files (with different file types) from database (Byte Array) to single zipped file

Question

I need to export multiple files with varying file types (pdf, xlsx, .docx) stored in a database (Byte Array) and save them as a single zipped file. How should I handle the multiple files? I'm assuming I would need to first store the files in a list and using MemoryStream? I'm using the ZipArchive class to export the files as a zip file. Assuming this approach will work, I'm unsure of how to pass the list as an argument to the ZipArchive (DownloadMultipleFiles) method.

protected void lnkExport_Click(object sender, EventArgs e)
{
    string applicationID = ((sender as LinkButton).CommandArgument);
    var myList = GetFilesandConvertToList(applicationID);
    DownloadMultipleFiles(myList); //How would I pass myList as an argument here? Obviously, this would not work.
}

Call stored procedure to get the files and place them in a list:

public List<ZipList> GetFilesandConvertToList(string applicationID)
{           
    List<ZipList> fileList = new List<ZipList>();
    SqlCommand cmd = new SqlCommand("dbo.spGetFilesByID", ConnC.con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@ApplicationID", applicationID); //This will return several files (different file types using the ID)
    ConnC.con.Open();
    using (SqlDataReader sdr = cmd.ExecuteReader())
    {
        if (sdr.HasRows)
        {
            while(sdr.Read())
            {
                ZipList zl = new ZipList();
                sdr.Read();
                zl.bytes = (byte[])sdr["FILE_CONTENT"];
                zl.contentType = sdr["FILE_TYPE"].ToString();
                zl.fileName = sdr["FILE_NAME"].ToString();
                fileList.Add(zl);
            }                    
         }
    }
    return fileList;
}

Using ZipArchive to place the list in a MemoryStream and export as a zip file:

public void DownloadMultipleFiles(List<byte[]> byteArrayList)
{
    using (MemoryStream ms = new MemoryStream())
    {
        using (ZipArchive archive = new ZipArchive(ms, ZipArchiveMode.Create, true))
        {
            foreach (byte[] file in byteArrayList)
            {
                ZipArchiveEntry entry = archive.CreateEntry(file.fileName + ".pdf", CompressionLevel.Fastest);
                using (Stream zipStream = entry.Open())
                {
                    zipStream.Write(file, 0, file.Length);
                }
            }
        }
        return File(ms.ToArray(), "application/zip", "Archive.zip");
    }
}
 
public class ZipList
{
    internal byte[] bytes;
    internal string contentType;
    internal string fileName;
}

---

UPDATE: I've updated this method with a slightly modified answer from @Andy. This works great:

protected void lnkExport_Click(object sender, EventArgs e)
{
    string applicationID = ((sender as LinkButton).CommandArgument);
    var myList = GetFilesandConvertToList(applicationID);
    //Download Zipped File
    byte[] fileBytes = GetZipFileForApplicationId(applicationID);
    Response.Clear();
    Response.Buffer = true;
    Response.Charset = "";
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    Response.ClearContent();
    Response.AppendHeader("Content-Disposition", "attachment; filename=Application.zip");
    Response.AppendHeader("Content-Type", "application/zip");
    Response.BinaryWrite(fileBytes);
    HttpContext.Current.Response.Flush();
    HttpContext.Current.Response.SuppressContent = true;
    HttpContext.Current.ApplicationInstance.CompleteRequest();
}

Using the method suggested from @Andy to get the files into a memory stream and return a byte array:

public byte[] GetZipFileForApplicationId(string applicationID)
{
    byte[] fileBytes = null;
    SqlCommand cmd = new SqlCommand("dbo.spGetFilesByID", ConnC.con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@ApplicationID", applicationID);
    ConnC.con.Open();
    using (SqlDataReader sdr = cmd.ExecuteReader())
    {
        if (sdr.HasRows)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                using (ZipArchive archive = new ZipArchive(ms, ZipArchiveMode.Create, true))
                {
                    while (sdr.Read())
                    {
                        byte[] bytes = (byte[])sdr["FILE_CONTENT"];
                        string contentType = sdr["FILE_TYPE"].ToString();
                        string fileName = sdr["FILE_NAME"].ToString();

                        ZipArchiveEntry entry = archive.CreateEntry(fileName);
                        using (Stream zipStream = entry.Open())
                        {
                            zipStream.Write(bytes, 0, bytes.Length);
                        }
                    }
                }
                ms.Position = 0;
                fileBytes = ms.ToArray();

            }
        }
    }
    return fileBytes;
}

Answer 1

You could kill a couple birds with one stone and do it all at once without extra models (this hasn't been tested, but you'll get the gist):

public byte[] GetZipFileForApplicationId(string applicationID)
{
    SqlCommand cmd = new SqlCommand("dbo.spGetFilesByID", ConnC.con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@ApplicationID", applicationID);
    ConnC.con.Open();
    using (SqlDataReader sdr = cmd.ExecuteReader())
    {
        if (sdr.HasRows)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                using (var archive = new ZipArchive(ms, ZipArchiveMode.Create, true))
                {
                    while(sdr.Read())
                    {
                        var bytes = (byte[])sdr["FILE_CONTENT"];
                        var contentType = sdr["FILE_TYPE"].ToString();
                        var fileName = sdr["FILE_NAME"].ToString();
                        
                        var entry = archive.CreateEntry(fileName);
                        using (var zipStream = entry.Open())
                        {
                            zipStream.Write(bytes, 0, bytes.Length);
                        }
                    } 
                }
                ms.Position = 0;
                // This is kind of redundant. You should return the
                // MemoryStream object instead of duplicating it's data.
                // I'll let you play with that.
                return ms.ToArray();
            }
        }
    }
    return null;
}