August 29, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

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

  • July 28, 2008
  • By Bipin Joshi
  • Send Email »
  • More Articles »

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 server 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 TypeSQL Server Data TypeMax. 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 NameData TypeDescription
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.





Page 1 of 7



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel