July 24, 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 »

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.





Page 6 of 7



Comment and Contribute

 


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

 

 


Sitemap | Contact Us

Rocket Fuel