dcsimg
July 16, 2018
Hot Topics:

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

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

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



Comment and Contribute

 


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

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.

Sitemap

Thanks for your registration, follow us on our social networks to keep up-to-date