Click here to Skip to main content
15,868,016 members
Articles / Programming Languages / C#
Article

Uploading and Downloading BLOBs to Microsoft Access

Rate me:
Please Sign up or sign in to vote.
4.71/5 (6 votes)
21 Dec 20063 min read 179.7K   8.1K   38   19
Illustrates how to upload and download BLOB data to Microsoft Access.

AccessBlob

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.

SQL
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:

C#
// 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.

C#
// 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:

C#
// 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:

C#
// 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:

C#
// 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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Architect
United States United States
Michael Carey is the Head of Development for an Automation Integrator in Philadelphia, PA. Michael specializes in Batch Automation, Process History, and Factory to Enterprise Integration.

Comments and Discussions

 
GeneralMy vote of 5 Pin
RichRiedling3-Jan-11 10:55
RichRiedling3-Jan-11 10:55 
GeneralThank you and question. Pin
Michael B Pliam25-Apr-10 16:59
Michael B Pliam25-Apr-10 16:59 
GeneralInseting BLOB values into MySql database using OLEDB Pin
thiru_58627-Aug-09 2:17
thiru_58627-Aug-09 2:17 
Hi,
I want to insert photo into one of the blob field of MYSQL Database.
I want to do the above said using oledb.
I face problem in sending the parameter in the query.

Can anyone help me out...pls....
GeneralRe: Inseting BLOB values into MySql database using OLEDB Pin
zam6647-Aug-09 5:37
zam6647-Aug-09 5:37 
GeneralRe: Inseting BLOB values into MySql database using OLEDB Pin
thiru_58629-Aug-09 21:01
thiru_58629-Aug-09 21:01 
GeneralRe: Inseting BLOB values into MySql database using OLEDB Pin
zam66410-Aug-09 6:39
zam66410-Aug-09 6:39 
GeneralRe: Inseting BLOB values into MySql database using OLEDB Pin
thiru_586210-Aug-09 22:18
thiru_586210-Aug-09 22:18 
GeneralRe: Inseting BLOB values into MySql database using OLEDB Pin
zam66411-Aug-09 7:03
zam66411-Aug-09 7:03 
GeneralSomethig....wrong ... Pin
blue300024-Feb-08 18:12
blue300024-Feb-08 18:12 
GeneralRe: Somethig....wrong ... Pin
zam66425-Feb-08 3:22
zam66425-Feb-08 3:22 
GeneralRe: Somethig....wrong ... Pin
Member 114347896-Sep-15 23:20
Member 114347896-Sep-15 23:20 
GeneralCannot download the file from the database Pin
ananth_8213-Feb-07 22:37
ananth_8213-Feb-07 22:37 
GeneralRe: Cannot download the file from the database Pin
zam66410-Jul-07 10:01
zam66410-Jul-07 10:01 
Generalerror "Parameter is not valid." Pin
BorisAro12-Feb-07 1:13
BorisAro12-Feb-07 1:13 
GeneralRe: error "Parameter is not valid." Pin
zam66412-Feb-07 9:38
zam66412-Feb-07 9:38 
GeneralRe: error "Parameter is not valid." Pin
The_Chado10-Jul-07 6:50
The_Chado10-Jul-07 6:50 
GeneralRe: error "Parameter is not valid." Pin
zam66410-Jul-07 9:30
zam66410-Jul-07 9:30 
GeneralRe: error "Parameter is not valid." Pin
The_Chado11-Jul-07 5:46
The_Chado11-Jul-07 5:46 
GeneralRe: error "Parameter is not valid." Pin
zam66411-Jul-07 7:45
zam66411-Jul-07 7:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.