November 25, 2014
Hot Topics:

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

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

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 NameDescription
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.





Page 2 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.

Sitemap | Contact Us

Rocket Fuel