Upload to Database ... Dowload by Zip File

• Download source files - 3.39 Kb

Introduction

I've been developing interactive web pages for close to 5 years now and I always enjoy figuring out how to do fun and new things. I was recently tasked with making an application that would allow users to upload documents that their customers could then download from a website. Through this experience I found many helpful articles at Code Project and a few other places, and I decided to write up an article describing the techniques along with some helpful tips I discovered along the way.

What the sample does

The code that I have included in this article demonstrates the following:

• Handling the uploading of a file and immediately storing it into a database (in this case an Oracle 9i database).
• Displays the files within the database inside a DataGrid showing the file name and the file size (in kilobytes) along with a checkbox to select individual files
• How to find what files were selected in the DataGrid, zip and compress them, then prompt for download

This is all accomplished with help from Oracle via their Oracle Data Provider for .NET (ODP.NET) and the .NET Zip Library (#ziplib) from the folks at ic#code. Both of these assemblies are freely available and can be added easily to any .NET application. If you have questions about how to use these assemblies keep reading ... I am going to include my experiences of how I got them to work in this project.

About uploading

If you have come to developing with ASP.NET, by having roots of Classic ASP, then you know how fun it can be to try and upload a file to your web server. Then, once you finally get the file on your web server, try and store that into a database ... way too much time is wasted with researching a way to do this, with most of the options leading to dead ends. Most of the time we would have to rely on a 3rd party component to handle these tasks, and that in itself can open a whole set of problems of its own! (any sympathisers? empathisers?)

Fortunately, the times have changed. The underlying concepts are still the same but much of the burden has been taken off of our (the developer's) shoulders. Take the following code snippet for example:

HttpPostedFile filPosted = filUpload.PostedFile;
int intFileLength = System.Convert.ToInt32(filPosted.ContentLength);
byte[] byteData = new byte[intFileLength];
filPosted.InputStream.Read(byteData, 0, intFileLength);

With some minimal checks added to ensure that a file was actually uploaded the above code gets the file's contents into a very usable format, which we can then store into a database, out to a file or whatever may meet our needs. The only thing that you need along with this code is the file input HTML control, a way to submit the form (usually a button), and ensure that the form's attribute enctype is set to multipart/form-data. For example:

<form runat="server"
      id="myForm"
      name="myForm"
      method="post"
      enctype="multipart/form-data">
      <input runat="server"
             id="filUpload"
             type="file">
      <asp:Button runat="server"
                  id="btnUpload"
                  OnClick="btnUpload_Click"
                  Text="Begin Upload" />
</form>

The database portion

In this example program I use ODP.NET for my interaction with an Oracle database. This is not a necessary component in order to get either the upload or download functionality working, however, since I used it and found some interesting topics, I thought I would share this with those who might find it interesting.

The main reason that I decided to use ODP.NET is that it has a more direct path to communicate with an Oracle database than the other available options and for the simple fact that I could not get the OleDb provided by the .NET Framework to work correctly with an Oracle BLOB field. Enough talk, let's get to an example:

// Open the DB connection and query the table with the BLOB field
string strConn = "Data Source=MyDB;User ID=MyUser;Password=MyPWord";
OracleConnection oConn = new OracleConnection(strConn);
OracleDataAdapter oDA = new OracleDataAdapter("SELECT * FROM FILES", oConn);
// Command Builders make it easy to do commands against our table
OracleCommandBuilder oCB = new OracleCommandBuilder(oDA);
// Adds the primary key information
oDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// The dataset will hold the schema information for the table
DataSet ds = new DataSet();
// The datarow will hold the new record information
DataRow dr;
// Open the connection and load the schema for the dataset
oConn.Open();
oDA.FillSchema(ds, SchemaType.Source, "FILES");
// Create a blank row to insert into then set the values
dr = ds.Tables["FILES"].NewRow();
dr["FILE_ID"] = 1;
dr["FILE_NAME"] = System.IO.Path.GetFileName(filPosted.FileName);
dr["FILE_DATA"] = byteData;
// Append the new row to the dataset
ds.Tables["FILES"].Rows.Add(dr);
// Update the table using our dataset against our data adapter
oDA.Update(ds, "FILES");
// Close our connection
oConn.Close();

The above sample continues where the file upload sample left off. Once the file was read in, we then needed to put it somewhere and in my case, I put it in an Oracle database. There are some interesting things to point out in this code though:

1. The OracleCommandBuilder - before I found sample code from Oracle about ODP.NET I was unaware of this wonderful little helper. By using a command builder (which is available in .NET to OleDb and SQL Server as well) the only SQL I had to write was a very simple query statement. Any modifications to the table are then built automatically for me. The only limitation is that it can only handle one table.
2. Schema information - when you pull information from a database into a DataSet it will not bring back any of the schema information by default. Handy things like primary keys or other constraints are not included, but they can be added manually. However, a DataAdapter does allow you to retrieve this information and apply to it a DataSet. Very handy to ensure your data's integrity without much hassle.
3. Primary Key Sequence - the example above shows an 1 being inserted into the primary key field of the table, and this happens every time a file is uploaded to the server and then saved to the database. This is not something I originally expected to have to do, but when I added the schema information it does not allow nulls in the primary key field. Understandable. So I insert an arbitrary number and when the sequence (an increment of one) fires, it must trash the original data sent, because the field contains the correctly incremented number.

Displaying the Files

To display the available files I used the almighty DataGrid with some small modifications.

<asp:TemplateColumn>
     <HeaderTemplate>
          <input
               runat="server"
               type="checkbox"
               ID="chkSelectAll"
               title="Select All Files"
               onclick="checkAll(this);" />
     </HeaderTemplate>
     ...

This gives us a checkbox that will be in the header and give the ability to check (or uncheck) all of the files in the DataGrid. This is accomplished with a small JavaScript:

function checkAll(o) {
    for (var i = 0; i < document.myForm.elements.length; i++) {
        if (document.myForm.elements[i].type == "checkbox") {
            document.myForm.elements[i].checked = o.checked;
        }
    }
}

This gives a quick and simple way to choose one or more (or all) files to download. Additional things can be added for visitors' convenience such as an estimated time of download and the file size (as shown in the image at the top of the page). But those things are easy to figure out ... let's get to the good part.

Zip and download

When the download is initiated the first thing I need to check is which files were selected for downloading. This can be accomplished by iterating through the DataGrid:

// A temp object to be used in iterating through the datagrid
CheckBox chkControl;
// will hold a comma seperated list of the IDs that were selected
string strIDs = "";

// Find the checked items in the datagrid and append them to the string
foreach (DataGridItem dgi in dgdFiles.Items) {
    chkControl = (CheckBox)dgi.FindControl("chkSelection");
    if (chkControl.Checked) {
        strIDs += ((Label)dgi.FindControl("lblFileID")).Text.ToString() 
                                        + ",";
    }
}

When I find checkboxes that are checked I append the corresponding ID to our string, so it can be used to query the database. As long as one or more files were chosen I proceed to open the database and request the particular records. The information is placed in a DataSet which will create our zip file ... for example:

// Create the ZIP file that will be downloaded. Need to name 
// the file something unique ...
string strNow = String.Format("{0:MMM-dd-yyyy_hh-mm-ss}", 
                    System.DateTime.Now);
ZipOutputStream zipOS = new ZipOutputStream(File.Create
                          (Server.MapPath("./") + strNow + ".zip"));
// ranges 0 to 9 ... 0 = no compression : 9 = max compression
zipOS.SetLevel(5);

// Loop through the dataset to fill the zip file
foreach (DataRow dr in ds.Tables["FILES"].Rows) {
    ZipEntry zipEntry = new ZipEntry(dr["FILE_NAME"].ToString());
    zipOS.PutNextEntry(zipEntry);
    zipOS.Write((byte[])dr["FILE_DATA"], 0, 
            System.Convert.ToInt32(dr["FILE_SIZE"]));
}

// Compress and close the zip file
zipOS.Finish();
zipOS.Close();

Once the ZIP file has been created and the page has been reloaded to the user, I wanted to prompt the user to download the newly created file. Once again I decided to use JavaScript for the seamless feel of never leaving the actual application for continued use.

// When the page reloads we need to redirect the user to the file
string script1 = "<script language="javascript">function gotoZip()";
script1 += "{document.location.href = '" + strNow + ".zip';}</" + "script>";
string script2 = "<script language="javascript">document.body.onload = 
                            gotoZip;</" + "script>";
Page.RegisterStartupScript("download1", script1);
Page.RegisterStartupScript("download2", script2);

Lessons learned

The Global Assembly Cache Utility (gacutil.exe) is a handy item to have when installing an ASP.NET application on a server, that needs assemblies that are not included in the normal install of the .NET Framework. The gacutil along with a particular DLL are required to add new assemblies to a machine, but are not included in the non-development distributable. I am not sure if creating an install package for a web application would solve this issue ... does anyone know offhand?

The gacutil is also a great way to quickly resolve what assemblies you have available on a given machine. Midway through my development I tested my web application on another server and received an error regarding my reference to the ODP.NET. I knew that I had installed and registered the assembly with the gacutil so it must be something else. After examining the reference and the listing in the gacutil more closely I found that Oracle had updated the ODP.NET and moved to a newer version. A simple change in my code to reflect the version number, and everything was fine once again.

References

• CodeProject
  • "Adding a Checkbox column to your DataGrid" by Shaun Wilde
  • "File Upload with ASP.NET" by Konstantin Vasserman
• IC#Code.net
  • the #ziplib and documentation
• Oracle.com
  • ODP.NET - Sample Corner