Utilize ADO.NET and C# to work with BLOB data

by Tony Patton | Jul 01, 2005 7:00:00 AM

Takeaway: While working on a project that involved storing and retrieving data with SQL Server, Tony Patton encountered a new twist: dealing with BLOBs. Find out how he used ADO.NET to access and store BLOBs.

Storing and retrieving data with Microsoft SQL Server is a common task associated with most applications. On a past project, I was presented with a new twist on this interaction: large data types or BLOBs. The project entailed storing and retrieving Microsoft Word documents and image files. This article covers the backend aspect of the task with ADO.NET, and a future column will cover the presentation side with ASP.NET.

What is a BLOB?

BLOB is an acronym for binary large object, a collection of binary data stored as a single entity in SQL Server. BLOBs are used primarily to hold multimedia objects such as images, videos, and sound, but they can also store programs or even fragments of code. While SQL Server does support BLOBs, not all database servers do.

Accessing BLOB data

There are a couple of ways that you can read data from a SQL Server database. First, you may read the data row by row; or, you may choose to read data from specific columns. The simplest approach is accessing the specific column, so let's tackle that approach first.

Reading BLOB data requires working at the byte level. Luckily, the SqlDataReader object contains the GetBytes method for accessing a column's data as bytes. Once the bytes are read, they may be combined with a FileStream object to save the BLOB object to a file. The following C# code provides an example:

Byte[] blob = null;
FileStream fs = null;
const string sConn = "server=(local);Initial
Catalog=Northwind;UID=ctester;PWD=password";
try {
SqlConnection conn = new SqlConnection(sConn);
SqlCommand cmd = new SqlCommand("SELECT Picture FROM Categories WHERE
CategoryName='Builder'", conn);
cn.Open();
SqlDataReader sdr = cmd.ExecuteReader();
sdr.Read();

blob = new Byte[(sdr.GetBytes(0, 0, null, 0, int.MaxValue))];
sdr.GetBytes[0, 0, blob, 0, blob.Length);
sdr.Close();
conn.Close();
fs = new FileStream("c:\\Builder.doc", FileMode.Create, FileAccess.Write);

fs.Write(blob, 0, blob.Length);
fs.Close();
} catch (SqlException e){
Console.WriteLine("SQL Exception: " + e.Message);
} catch (Exception e) {
Console.WriteLine("Exception: "+ e.Message);
}

This code reads a BLOB object from a specific database cell (row and column intersection). Note: It utilizes both the System.Data.SqlClient and System.IO namespaces (via Using statements). The Write method of the FileStream class is used to store the BLOB to a local drive. In this case, we know we're working with documents and image files, so it makes sense to save them.

Another scenario involves reading a database row where one column will be a BLOB and the others are not. The default behavior of the SqlDataReader object is to load incoming data as a row as soon as an entire row of data is available. The size of BLOB objects makes this approach incompatible, so they're treated differently. This is accomplished via an overload of the ExecuteReader method of the SqlCommand class. You can pass CommandBehavior.SequentialAccess to the ExecuteReader method to modify the default behavior of the SqlDataReader so that instead of loading rows of data, it will load data sequentially as it's received.

The major aspect of using SequentialAccess is being certain of the sequence in which the columns are accessed. The default behavior of the SqlDataReader is allowing you to access any column as you wish, but SequentialAccess makes you access the fields in the order returned by the SqlDataReader object. The data is read sequentially, so once the data is read, it is no longer available.

When accessing the data in the BLOB field, the GetBytes, GetString, and GetChars methods of the SqlDataReader class may be used to retrieve the data. GetBytes and GetChars allow you to fill an array with the data (they both return long values). The following code shows how this might be used:

SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader sdr = null;
FileStream fs = null;
BinaryWriter bw = null;
long blob;
byte[] outBuffer = new byte[255];
const string sConn = "server=(local);Initial
Catalog=Northwind;UID=ctester;PWD=password";
try {
conn = new SqlConnection(sConn);
cmd = new SqlCommand("SELECT CategoryID, Picture FROM Categories WHERE
CategoryName='Builder'",conn);
conn.Open();
sdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (sdr.Read()) {
id = sdr.getString(0);
fs = new FileStream("c:\\Builder.doc", FileMode.OpenOrCreate,
FileAccess.Write);
bw = new BinaryWriter(fs);
blob = sdr.GetBytes(1, startIndex, outBuffer, 0, 255);
while (blob == 255) {
bw.Write(outBuffer);
bw.Flush();
startIndex += 255;
bw.Flush();
bw.Close();
fs.Close();
}
sdr.Close();
conn.Close();
} }

Storing BLOB data

Retrieving and saving BLOB objects is a straightforward process; and the reverse process, storing BLOB objects in SQL Server, is just as easy. I should note that the previous examples take advantage of the BLOB stored in the table by way of this sample code (Builder.doc):

SqlConnection conn =null;
SqlCommand cmd = null;
SqlParameter param = null;
FileStream fs = null;
const string sConn = "server=(local);Initial
Catalog=Northwind;UID=ctester;PWD=password";
try {
conn = new SqlConnection(sConn);
cmd = new SqlCommand("UPDATE Categories SET Picture = @Picture WHERE
CategoryName = 'Seafood'", conn);
fs = new FileStream("c:\\Builder.doc", FileMode.Open, FileAccess.Read);
Byte[] blob = new Byte[fs.Length];
fs.Read(blob, 0, blob.Length);
fs.Close();
param = new SqlParameter("@Picture", SqlDbType.VarBinary, blob.Length,
ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, blob);
cmd.Parameters.Add(param);
conn.Open();
cmd.ExecuteNonQuery();
} catch (SqlException e){
Console.Write("SQL Exception: " + e.Message());
} catch (Exception e) {
Console.Write("Exception: " e.Message());
}

The sample code inserts a Word document from the local file system into the database. It resembles a normal database update operation, however, FileStream and Byte objects are used to work with the Word document inserted into the database. Another variation is the use of the SqlParameter object to put the BLOB into the database column. This allows the data to be written directly from memory to the database.

All data is not equal

While string values are the most common data type developers use when interacting with a database, other data types such as numbers and BLOBs are often utilized. You treat these objects as binary streams when interacting with the database programmatically.

TechRepublic's free .NET newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET. Automatically sign up today!

4 comment(s)