Microsoft & .NET.NETWorking with Binary Large Objects (BLOBs) Using SQL Server and ADO.NET

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

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

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.

Learn more about SQL at TechRepublic Academy!

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.

The Update() method is similar to Insert() method except that it executes UPDATE statement (see Listing 4).

Listing 4: Update() method of PhotoHelper class

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

   cmd.Connection = cnn;
   cmd.CommandText = "update photos set title=@title,description=
                      @description,photo=@photo
                      where photoid=@photoid";

   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;
   SqlParameter photoid = new SqlParameter("@photoid",
      p.PhotoID);

   cmd.Parameters.Add(title);
   cmd.Parameters.Add(description);
   cmd.Parameters.Add(photo);
   cmd.Parameters.Add(photoid);

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

   return i;
}

I won’t cover the Update() and Delete() methods in detail here. You can have a look at them from the source code accompanying this article.

Now, see the GetAll() and GetByID() methods that read BLOB photo data from the Photos table (see Listing 5).

Listing 5: The GetAll() and GetByID() methods of the PhotoHelper class

public static List<Photo> GetAll()
{
   SqlConnection cnn = new SqlConnection(strConn);
   SqlCommand cmd = new SqlCommand();
   List<Photo> photos = new List<Photo>();
   byte[] data = new byte[1000];

   cmd.CommandText = "select * from photos order by photoid";
   cmd.Connection = cnn;

   cnn.Open();
   SqlDataReader reader = cmd.ExecuteReader();
   while (reader.Read())
   {
      Photo p = new Photo();
      p.PhotoID = reader.GetInt32(reader.GetOrdinal("PhotoID"));
      p.Title = reader.GetString(reader.GetOrdinal("Title"));
      p.Description =
         reader.GetString(reader.GetOrdinal("Description"));
      p.PhotoData =
         (byte[])reader.GetValue(reader.GetOrdinal("Photo"));
      photos.Add(p);
   }
   cnn.Close();
   return photos;
}

public static Photo GetByID(int photoid)
{
   SqlConnection cnn = new SqlConnection(strConn);
   SqlCommand cmd = new SqlCommand();
   byte[] data = new byte[1000];

   cmd.CommandText = "select * from photos
                      where PhotoID=@photoid";
   cmd.Connection = cnn;

   SqlParameter pId = new SqlParameter("@photoid", photoid);
   cmd.Parameters.Add(pId);

   cnn.Open();
   SqlDataReader reader = cmd.ExecuteReader();
   Photo p = new Photo();
   while (reader.Read())
   {
      p.PhotoID = reader.GetInt32(reader.GetOrdinal("PhotoID"));
      p.Title = reader.GetString(reader.GetOrdinal("Title"));
      p.Description =
         reader.GetString(reader.GetOrdinal("Description"));
      p.PhotoData =
         (byte[])reader.GetValue(reader.GetOrdinal("Photo"));
    }
   cnn.Close();
   return p;
}

The GetAll() method fires a SELECT query against the Photos table and fetches all the rows. The rows then are transferred into Photo objects. A generic List filled with Photo objects is then returned. Similarly, theGetByID() method fetches a record corresponding to the supplied PhotoID and returns a single Photo object.

What is more important for for you is the code marked in bold letters. This is how you can read a varbinary(MAX) column in ADO.NET. The GetValue() method of SqlDataReader returns data as an object. You typecast the object into a byte array. There is a better way of doing the same; I will explain them in later sections.

This completes your classes. Now, it’s time to develop the web forms.

Open the default web form and drag and drop a DetailsView control on it. Add four fields to the DetailsView control as shown in Figure 1:

Figure 1: Adding fields to DetailsView

The Title bound field is data bound with Title property of Photo class. The Description and Photo fields are template fields. The CommnadField adds insert, update and delete abilities to the DetailsView. The markup of these four fields is shown in Listing 6:

Listing 6: Fields of DetailsView

<asp:BoundField DataField="Title" HeaderText="Title :" />
<asp:TemplateField HeaderText="Description :">
   <EditItemTemplate>
      <asp:TextBox ID="TextBox1"
                   runat="server"
                   Columns="40"
                   Rows="5"
                   Text='<%# Bind("Description") %>'
                   TextMode="MultiLine">
      </asp:TextBox>
   </EditItemTemplate>
   <InsertItemTemplate>
      <asp:TextBox ID="TextBox1"
                   runat="server"
                   Columns="40"
                   Rows="5"
                   Text='<%# Bind("Description") %>'
                   TextMode="MultiLine">
      </asp:TextBox>
   </InsertItemTemplate>
   <ItemTemplate>
      <asp:Label ID="Label1"
                 runat="server"
                 Text='<%# Bind("Description")
                 %>'>
      </asp:Label>
   </ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Photo :">
   <EditItemTemplate>
      <asp:FileUpload ID="FileUpload1"
                      runat="server" />
   </EditItemTemplate>
   <InsertItemTemplate>
      <asp:FileUpload ID="FileUpload2"
                      runat="server" />
   </InsertItemTemplate>
   <ItemTemplate>
      <asp:Image ID="Image1"
                 runat="server"
                 ImageUrl='<%# Eval("PhotoID","
                 ~/showphoto.aspx?photoid={0}") %>' />
   </ItemTemplate>
</asp:TemplateField>
<asp:CommandField ButtonType="Button"
                  ShowDeleteButton="True"
                  ShowEditButton="True"
                  ShowInsertButton="True" />

As you can see from Listing 6, the Description template field consists of a multiline textbox in its EditItemTemplate and InsertItemTemplate. This textbox is bound with the Description property of the Photo class. The Photo template field consists of an Image control in its ItemTemplate and FileUpload controls in its EditItemTemplate and InsertItemTemplate. Carefully observe the line marked in bold letters. The ImageUrl property of the Image control is set to ShowPhoto.aspx, a web form you will develop in later sections. Notice how the PhotoID is passed to this web form as a query string parameter using the {0} syntax.

Next, switch to the code behind file of the web form and add a private method (BindData) as shown in Listing 7:

Listing 7: Binding DetailsView with generic List

private void BindData()
{
   List<Photo> photos = PhotoHelper.GetAll();
   DetailsView1.DataSource = photos;
   DetailsView1.DataBind();
}

The BindData() method calls the GetAll() method of the PhotoHelper class. The returned generic list of Photo objects then is bound with the DetailsView. Then, handle the Page_Load event, as shown in Listing 8:

Listing 8: Page_Load event handler of the default web form

protected void Page_Load(object sender, EventArgs e)
{
   if (!IsPostBack)
   {
      BindData();
      if (DetailsView1.Rows.Count == 0)
      {
         DetailsView1.ChangeMode(DetailsViewMode.Insert);
      }
   }
}

Here, you simply call the BindData() method. If there are no rows to show in the DetailsView, you change its mode to insert. Because the DetailsView is not bound with any data source control, you need to handle its PageIndexChanging and ModeChanging events manually (see Listing 9).

Listing 9: Implementing paging feature for DetailsView

protected void DetailsView1_PageIndexChanging
(object sender, DetailsViewPageEventArgs e)
{
   DetailsView1.PageIndex = e.NewPageIndex;
   BindData();
}
protected void DetailsView1_ModeChanging
(object sender, DetailsViewModeEventArgs e)
{
   DetailsView1.ChangeMode(e.NewMode);
   BindData();
}

The code in Listing 9’s event handlers is straightforward and needs no explanation. When a user enters data for a new photo to be stored and clicks on Insert button, the ItemInserting event of the DetailsView is fired. The ItemInserting event hander looks as shown in Listing 10:

Listing 10: Inserting a photo

protected void DetailsView1_ItemInserting
(object sender, DetailsViewInsertEventArgs e)
{
   Photo p = new Photo();

   TextBox t1=((TextBox)DetailsView1.Rows[0].Cells[1].Controls[0]);
   TextBox t2=((TextBox)DetailsView1.Rows[1].Cells[1].Controls[1]);
   FileUpload fu=
      ((FileUpload)DetailsView1.Rows[2].Cells[1].Controls[1]);

   p.Title = t1.Text;
   p.Description = t2.Text;

   Stream imgdatastream = fu.PostedFile.InputStream;
   int imgdatalen = fu.PostedFile.ContentLength;
   byte[] imgdata = new byte[imgdatalen];
   int n = imgdatastream.Read(imgdata, 0, imgdatalen);

   p.PhotoData = imgdata;

   PhotoHelper.Insert(p);
   BindData();
}

Here, you grab the new values entered in the DetailsView and fill them into a Photo object. See how the photo uploaded by the user is converted into a byte array. The InputStream of the FileUpload control gives you access to the incoming data. You then read this stream into a byte array. Finally, the Insert() method of PhotoHelper class is called to add a record into the Photos table.

The update operation is similar to insert, with a few changes. Listing 11 gives you the complete event handler for the ItemUpdating event.

Listing 11: Updating a photo

protected void DetailsView1_ItemUpdating(object sender,
   DetailsViewUpdateEventArgs e)
{
   Photo p = new Photo();

   TextBox t1 =
      ((TextBox)DetailsView1.Rows[0].Cells[1].Controls[0]);
   TextBox t2 =
      ((TextBox)DetailsView1.Rows[1].Cells[1].Controls[1]);
   FileUpload fu =
      ((FileUpload)DetailsView1.Rows[2].Cells[1].Controls[1]);

   p.PhotoID = Convert.ToInt32(DetailsView1.DataKey[0]);
   p.Title = t1.Text;
   p.Description = t2.Text;

   Stream imgdatastream = fu.PostedFile.InputStream;
   int imgdatalen = fu.PostedFile.ContentLength;
   byte[] imgdata = new byte[imgdatalen];
   int n = imgdatastream.Read(imgdata, 0, imgdatalen);

   p.PhotoData = imgdata;

   PhotoHelper.Update(p);
   BindData();
}

Notice the line marked in bold letters. You get the PhotoID of the row being updated by using the DataKey collection and then calling the Update() method of rthe PhotoHelper class.

The delete operation is simple enough. It is shown in Listing 12:

Listing 12: Deleting a photo

protected void DetailsView1_ItemDeleting(object sender,
   DetailsViewDeleteEventArgs e)
{
   int photoid = Convert.ToInt32(DetailsView1.DataKey[0]);
   PhotoHelper.Delete(photoid);
   BindData();
}

That completes your default web form. You also need to develop ShowPhoto.aspx that supplies the photo image to the image control. Add another web form to your web site and name it ShowPhoto.aspx. This web form doesn’t have any controls on it. The Page_Load event handler of ShowPhoto.aspx looks like what you see in Listing 13:

Listing 13: Showing a photo from database in an Image control

protected void Page_Load(object sender, EventArgs e)
{
   int photoid = Convert.ToInt32(Request.QueryString["photoid"]);
   Photo p = PhotoHelper.GetByID(photoid);

   Response.Clear();
   Response.ContentType = "image/pjpeg";
   Response.BinaryWrite(p.PhotoData);
   Response.End();
   }

Here, you retrieve a single photo by using the GetByID() method of the PhotoHelper class. Recollect that ShowPhoto.aspx receives a query string parameter specifying the photo ID to be retrieved. The byte array containing photo data in raw form then is written to the response stream using the BinaryWrite() method of the Response object. Notice that you have cleared the response and set its content type to image/pjpeg before writing the response.

This completes your web site. You now can run the default web form and try adding, updating, and deleting records. Figure 2 shows you a sample run of the web site:

Figure 2: Default web form showing photos from the database

Improving the Reading Operation

In the example so far, you read the photo data as follows:

p.PhotoData = (byte[])reader.GetValue(reader.GetOrdinal("Photo"));

The GetValue() method returns all the data from the specified column as an object. What if your photo size is big? Huge byte arrays can degrade the performance of your application. Luckily, the SqlDataReader class provides a more elegant alternative. The GetBytes() method of the SqlDataReader class allows you to read a column value in chunks. To see the GetBytes() method in action, modify the GetByID() method as shown in Listing 14:

Listing 14: Using GetBytes() method of SqlDataReader class

public static Photo GetByID(int photoid)
{
   SqlConnection cnn = new SqlConnection(strConn);
   SqlCommand cmd = new SqlCommand();
   byte[] data = new byte[1000];

   cmd.CommandText = "select * from photos
                      where PhotoID=@photoid";
   cmd.Connection = cnn;

   SqlParameter pId = new SqlParameter("@photoid", photoid);
   cmd.Parameters.Add(pId);

   cnn.Open();
   SqlDataReader reader = cmd.ExecuteReader();
   Photo p = new Photo();
   while (reader.Read())
   {
      p.PhotoID = reader.GetInt32(reader.GetOrdinal("PhotoID"));
      p.Title = reader.GetString(reader.GetOrdinal("Title"));
      p.Description =
         reader.GetString(reader.GetOrdinal("Description"));
      MemoryStream ms = new MemoryStream();
      int index = 0;
      while (true)
      {
         long count = reader.GetBytes(reader.GetOrdinal("Photo"),
            index, data, 0, data.Length);
         if (count == 0)
         {
            break;
         }
         else
         {
            index = index + (int)count;
            ms.Write(data, 0, (int)count);
         }
      }
      p.PhotoData = ms.ToArray();
   }
   cnn.Close();
   return p;
}

Notice the highlighted code. You first declare a byte array to store chunks of data being read. You then declare a memory stream to store the complete data. A white loop calls the GetBytes() method on SqlDataReader until all the bytes are read. The first parameter of the GetBytes() method specifies the column index; the second parameter indicates the position within the column from where the reading should start. The third parameter indicates the write position in a byte array, and the last parameter indicates the maximum number of bytes to read. The GetBytes() method returns the number of bytes successfully read. If the return value is 0, it indicates that no data could be read. Once the data is read and stored in the memory stream, you call the ToArray() method of the MemoryStream class to convert it into a byte array.

Improving the Update Operation

If the photo being updated is too large, the performance of your application will be affected. The update operation also can be performed in chunks in such cases. Listing 15 shows the modified Update() method of the PhotoHelper class.

Listing 15: Using .WRITE clause of UPDATE statement

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

   cmd.Connection = cnn;
   cmd.CommandText =
      "update photos set title=@title,description=
   @description,photo.write(@photo,@offset,@length)
      where photoid=@photoid";

   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;
   SqlParameter offset =
      new SqlParameter("@offset",SqlDbType.BigInt);
   offset.Value = 0;
   SqlParameter length = new SqlParameter("@length",
      p.PhotoData.Length);
   SqlParameter photoid = new SqlParameter("@photoid",
      p.PhotoID);

   cmd.Parameters.Add(title);
   cmd.Parameters.Add(description);
   cmd.Parameters.Add(photo);
   cmd.Parameters.Add(offset);
   cmd.Parameters.Add(length);
   cmd.Parameters.Add(photoid);

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

   return i;
}

Carefully observe the highlighted line of the code. You have modified the UPDATE statement to use the .WRITE() clause. The .WRITE() clause works with the varchar(MAX), nvarchar(MAX), and varbinary(MAX) columns and allows you to change only a part of the column rather than replacing the entire column value. Obviously, it can be of great help when dealing with a large amount of data. The .WRITE() clause takes three parameters: the data to be written to the column, the start position within the column from where the writing operation should begin, and the length of data to be written. You also can update a column value in multiple chunks by executing the UPDATE statement in a loop.

Summary

Applications that deal with Binary Large Objects, or BLOBs, need to evaluate whether to store data inside SQL Server or as a physical file. SQL Server provides the varbinary(MAX) data type to store BLOBs although the older Image data type is still available. The BLOB data can be read in .NET applications by using the SqlDataReader class. The GetBytes() method of SqlDataReader allows you to read chunks of binary data from a column. When writing the binary data back to the database, the SQL server .WRITE clause can be used to write data in chunks. SQL server and ADO.NET together make it easy for you to deal with large binary data such as documents, files, and photos.

Download the Code

You can download the code that accompanies this article here.

About the Author

Bipin Joshi is the proprietor of BinaryIntellect Consulting where he conducts premier training programs on a variety of .NET technologies. He wears many hats, including a software consultant, mentor, prolific author, webmaster, Microsoft MVP, and a member of ASPInsiders. Having adopted the Yoga way of life, Bipin also teaches Kriya Yoga to interested individuals. He can be reached via his blog at www.bipinjoshi.com.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories