Uploading and Downloading BLOBs to Microsoft Access

• Download demo project - 16.3 Kb
• Download source - 4.08 Kb

Introduction

AccessBlob illustrates how to upload and download Binary Large OBjects (BLOB) to a Microsoft Access database. The simplest way of describing a BLOB is as a file stored in a field in a relational database. A BLOB may be an image file, a spreadsheet, a word processor file, etc. There are many code snippets on the web for uploading and downloading BLOBs to SQL Server, but I could not find anything for Microsoft Access.

The sample application included with this article is just used to illustrate how to upload and download BLOB data to a Microsoft Access database. The sample application comes with a Microsoft Access 2000 database. When you use the application, the text box for the Data File contains the full path name to the file on your disk. This field is used for the file that you want to upload and for the file that you want to download. When you download, you must also select the file that you want to download from the listview.

Background

Microsoft Access stores BLOB data in the a field with an OLE Object data type (if you are creating the table from Design View). If you are creating the table from a SQL statement, the data type is IMAGE (similar to SQL Server). For this example application, the following Access table is used.

CREATE TABLE File (
     FileName VARCHAR(255),
     Size INT,
     Type VARCHAR(255),
     DateUploaded DATETIME,
     File IMAGE,
     CONSTRAINT File_PK PRIMARY KEY(FileName)
)

Upload the File

The file is uploaded by assigning a memory resident version of the file to a parameter of the OleDbCommand object. The file is loaded into memory using a BinaryReader object because we do not want a reader to process any of the data. The BinaryReader's constructor cannot take a file name, so we must create a FileStream first and pass that to the BinaryReader's constructor.

The variables that we are using are:

// the connection to the database
System.Data.OleDb.OleDbConnection conn = null;
// the command
System.Data.OleDb.OleDbCommand cmd = null;
// the data reader used to determine if the
// an insert or update command should be used
System.Data.OleDb.OleDbDataReader dr = null;
// the parameter used to store the BLOB
System.Data.OleDb.OleDbParameter param = null;
// the file stream for the source file
System.IO.FileStream fileStream;
// the reader used to read the source file
System.IO.BinaryReader reader = null;
// the entire file
byte[] data = null;

We need to get the file name, type, and date that the file was uploaded. The following code does this for us.

// DataFile.Text is the source file name with full path.  we
// need to determine the file name and the type.
string[] arr = DataFile.Text.Split(new char[]{'\\'});
string fileName = arr[arr.Length - 1];
arr = DataFile.Text.Split(new char[]{'.'});
string type = (arr.Length > 1) ? arr[arr.Length - 1] : "";
// get the time stamp when the file was uploaded
string dateUploaded = System.DateTime.Now.ToShortDateString() + 
                      " " + System.DateTime.Now.ToShortTimeString();
int numRecords = 0;

The rest of the procedure is to:

1. Load the file into memory,
2. Open a connection to the database,
3. Determine if the record already exists; if the record does not exist, we add the record without the BLOB data, and finally
4. Update the record with the BLOB data. Please note that the BLOB data is passed via the parameter.

You may find that it is odd that we add the record and then update the BLOB data. This was the only way that I was able to get the BLOB data into the database. I was not able to get the BLOB data into the database using the INSERT statement. The code is:

// load the file that we want to upload into memory
fileStream = new System.IO.FileStream(DataFile.Text, 
             System.IO.FileMode.Open, System.IO.FileAccess.Read);
reader = new System.IO.BinaryReader(fileStream);
data = reader.ReadBytes((int)fileStream.Length);

// open the connection to the database
conn = new System.Data.OleDb.OleDbConnection("Provider=" + 
       "Microsoft.Jet.OLEDB.4.0;Data Source=" + AccessFile.Text);
conn.Open();

// determine if the file already exists.
// If the file does not already
// exist, add the file to the table.
cmd = new System.Data.OleDb.OleDbCommand("SELECT " + 
      "COUNT(*) FROM [File] WHERE [FileName]='" + fileName + "'",conn);
dr = cmd.ExecuteReader();
dr.Read();
numRecords = dr.GetInt32(0);
dr.Close();
if(numRecords == 0) {
    cmd = new System.Data.OleDb.OleDbCommand("INSERT INTO" + 
          " [File] ([FileName],[Size],[Type]," + 
          "[DateUploaded]) VALUES('" + fileName + "'," + 
          data.Length + ",'" + type + "', #"+ 
          dateUploaded + "#)",conn);
    cmd.ExecuteNonQuery();
}
            
// update the BLOB data based for the record.
cmd = new System.Data.OleDb.OleDbCommand("UPDATE [File] " + 
      "SET [File]=@file, [Size]=" + data.Length + 
      ", [DateUploaded]=#" + dateUploaded + 
      "# WHERE [FileName]='" + fileName + "'",conn);
param = cmd.Parameters.Add("@file", 
        System.Data.OleDb.OleDbType.Binary);
param.Value = data;
cmd.ExecuteNonQuery();

Download the File

When the file is downloaded, the file is read from the database into a buffer, then the buffer is written to the disk. Because we are working with raw binary data, we use the BinaryWriter to write the data. The BinaryWriter's constructor cannot take a file name, so we must create a FileStream first and pass that to the BinaryWriter's constructor.

The variables that we are using to download the file are:

// the connection to the database
System.Data.OleDb.OleDbConnection conn = null;
// the command
System.Data.OleDb.OleDbCommand cmd = null;
// reads the data
System.Data.OleDb.OleDbDataReader reader = null;
// the file stream for the destination file 
System.IO.FileStream fileStream = null;
// the writer used to create the destination file
System.IO.BinaryWriter writer = null;
// the size of the buffer that is read from the database
// an written to the file
int bufferSize = 1000;
// the buffer for the data being transfered from
// the database to the file
byte[] buffer = new byte[bufferSize];
// the start index of the data in the database
long startIndex = 0;
// the number of bytes read from the database
long numberOfBytes = 0;

The core of the download code is:

// create the connection to the database: AccessFile.Text is the full
// path to the Access file.
conn = new System.Data.OleDb.OleDbConnection("Provider" + 
       "=Microsoft.Jet.OLEDB.4.0;Data Source=" + AccessFile.Text);
// create the SQL command: FileListView.SelectedItems[0].Text is the name
// of the file taken from the List View.
cmd = new System.Data.OleDb.OleDbCommand("SELECT [File] " + 
      "FROM [File] WHERE [FileName] = '" + 
      FileListView.SelectedItems[0].Text + "'", conn);
// open up the connection to the database
conn.Open();

// create the DataReader that will get the blob one buffer at a time
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
// we advance to the first record returned.  for this application we
// know that there is atleast one record because we got the file name
// from the List View
reader.Read();
// create the file stream that will save the file to DataFile.Text
fileStream = new System.IO.FileStream(DataFile.Text, 
             System.IO.FileMode.OpenOrCreate, 
             System.IO.FileAccess.Write);
// create the writer from the file stream
writer = new System.IO.BinaryWriter(fileStream);

// read in file from the database one
// buffer at a time.  when the number
// of bytes read is zero then we know that we are done.
do {
    numberOfBytes = reader.GetBytes(0, 
                    startIndex, buffer, 0, bufferSize);
    if(numberOfBytes == 0) {
        break;
    }
    writer.Write(buffer, 0, (int) numberOfBytes);
    startIndex += numberOfBytes;
} while (true);
writer.Flush();

Conclusion

AccessBlob demonstrates how to upload and download BLOB data to and from a Microsoft Access database. You can also upload and download BLOB data using an OleDbDataAdapter and a DataSet. If you are familiar with using OleDbDataAdapters, you should have no problem converting this code to use OleDbDataAdapters and DataSets. I personally find using OleDbDataAdapters cumbersome, and prefer to keep my code simple by just using OleDbCommand objects.