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

• facebook
• twitter
• in
• Email

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

Page 3 of 7