Working with Binary Large Objects (BLOBs) Using SQL Server and ADO.NET

Introduction

Sometimes, your data is not limited to strings and numbers. You need to store a large amount of data in a SQL server table. Documents, raw files, XML documents and photos are some examples. SQL sever provides special data types for such large volumes of data. In this article, I will discuss how to read and write Binary Large Objects (BLOBs) using SQL Server 2005 and ADO.NET.

Flavors of LOBs

Large objects come in many flavors. The LOBs can be broadly classified as Character Large Objects (CLOBs) or Binary Large Objects (BLOBs). Luckily, SQL server provides different data types to handle these different flavors of data. Have a look at the following table that lists various SQL Server data types that are used in conjunction with LOBs.

Table 1: Large Objects data types of SQL Server

LOB Type SQL Server Data Type Max. Size
BLOB varbinary(MAX)
Image
2,147,483,647
CLOB varchar(MAX)
Text
2,147,483,647
CLOB – Unicode nvarchar(MAX)
NText
1,073,741,823
XML data xml 2,147,483,647

The Text, NText, and Image data types come from the previous versions of SQL Server. It is recommended that you use the newer varchar(MAX), nvarchar(MAX), and varbinary(MAX) data types instead.

As you can see from Table 1, varbinary(MAX) is the data type that allows you to deal with large binary data; therefore, the discussion will focus on using varbinary(MAX) data in .NET applications.

Storing BLOBs in SQL Server

Binary data comes in variety of flavors. Word documents, PDF invoices, Photos, product images, raw data are all examples of BLOB data. When storing BLOBs as a part of your .NET application, you have two choices:

  1. Store BLOBs as disk files
  2. Store BLOBs directly inside a SQL Server database

Both the choices have some pros and cons. The first approach is good if:

  1. You have few hosting resources in terms of SQL Server space.
  2. The BLOB data is being used by third parties. For example, you might have images that are being created by a graphics designer.
  3. You want to use a file system-based editor to manipulate the binary data. For example, you might be using Microsoft Office Suite to edit Word and Excel files.

The biggest drawback of storing binary data as a physical file is that your database easily can lose track of the binary data. Say, for example, you have a column that stores URLs of product images. If you move your application from one server to another, you must manually ensure that physical files representing product images are shipped along with the rest of the application. Also, any deletions to the database records also must be played on the physical image files.

Storing BLOBs directly inside SQL server provides the following advantages:

  1. Because BLOBs and related data are always together, there is no possibility of mismatch.
  2. BLOBs are transferred from one server to another along with the usual data transfer process. There is no need for any manual efforts to keep them in sync.
  3. BLOB data is also backed up along with routine SQL Server data.
  4. Applications, such as Content management Systems and Photo Albums, are tightly dependent on the BLOB data they use. Such applications are greatly benefited by storing BLOBs directly in the database.

In summary, depending on the application being developed, you should decide whether storing BLOB data in SQL Server is the way to go. That said, generally CMS systems, photo albums, slide show applications, document management systems, and so forth are good candidates for storing BLOBs inside SQL Server.

BLOBs and ADO.NET

BLOB data is represented as a byte array in .NET. To read BLOB data, you need to use the SqlDataReader class of ADO.NET. The use of SqlDataReader class to read BLOB data can be best understood with an example.

You will develop a simple application that manages photos stored in a SQL Server database. To work through this example, you will need a SQL Server database with a table named Photos. Table 2 shows the schema of Photos table.

Table 2: Schema of Photos table

Column Name Data Type Description
PhotoID int (Identity, Primary Key) Represents a unique ID for a photo.
Title varchar(255) Stores the title of the photo.
Description varchar(MAX) Stores the description of the photo.
Photo varbinary(MAX) Stores the actual photo in binary form.

Notice that you have used the varbinary(MAX) data type for Photo column instead of the traditional Image data type.

Reading and Writing BLOBs

You will now develop a web site that allows the users to add, modify, and delete individual records of the Photos table. Begin by creating a new web site. If you wish, you can add a new database to its App_Data folder to create the Photos table.

Next, add App_Code folder to the web site and add a new class in it. Name the class as Photo. Listing 1 shows the completed Photo class.

Listing 1: Properties of Photo class

public class Photo
{
   public int PhotoID { get; set; }
   public string Title { get; set; }
   public string Description { get; set; }
   public byte[] PhotoData { get; set; }
}

The Photo class has four public properties: PhotoID, Title, Description, and PhotoData. They represent the respective columns of the Photos table. Notice that you have used the auto-implemented property syntax of C# to define the properties.

Add another class, PhotoHelper, that does the job of executing database queries. The PhotoHelper class consists of five methods, as shown in Table 3:

Table 3: Methods of the PhotoHelper class

Method Name Description
int Insert (Photo) Inserts a new photo entry into the Photos table and returns 1 if the operation is successful.
int Update (Photo) Updates an existing photo entry with new details and returns 1 if the operation is successful.
int Delete (int) Deletes an existing photo entry.
List<Photo> GetAll() Returns a generic list of Photo objects for all the records in the Photos table.
Photo GetByID (int) Returns a single Photo object representing a photo entry identified by the supplied PhotoID.

Before you code these methods, first you will add a static constructor to the PhotoHelper class, as shown in Listing 2:

Listing 2: Constructor of PhotoHelper class

private static string strConn;

static PhotoHelper()
{
   strConn = ConfigurationManager.ConnectionStrings
             ["connstr"].ConnectionString;
}

The static constructor simply reads the database connection string from the web.config using ConfigurationManager class. The retrieved connection string is stored in a static variable for later use.

The Insert() method is straightforward. It is shown in Listing 3:

Listing 3: Insert() method of PhotoHelper class

public static int Insert(Photo p)
{
   SqlConnection cnn = new SqlConnection(strConn);
   SqlCommand cmd = new SqlCommand();

   cmd.Connection = cnn;
   cmd.CommandText = "insert into photos(title,description,photo)
                      values(@title,@description,@photo)";

   SqlParameter title = new SqlParameter("@title", p.Title);
   SqlParameter description = new SqlParameter("@description",
      p.Description);
   SqlParameter photo=new SqlParameter("@photo",
      SqlDbType.VarBinary);
   photo.Value=p.PhotoData;
   cmd.Parameters.Add(title);
   cmd.Parameters.Add(description);
   cmd.Parameters.Add(photo);

   cnn.Open();
   int i=cmd.ExecuteNonQuery();
   cnn.Close();

   return i;
}

The Insert() method uses SqlCommand object’s ExecuteNonQuery() method to add a new photo record into the Photos table. Notice the code marked in bold letters. The photo parameter that represents binary data is constructed with the VarBinary data type. The Value property of the photo parameter holds a byte array containing the photo data.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read