Follow @PDSAInc January 2013 - Posts - Paul Sheriff's Blog for the Real World

Paul Sheriff's Blog for the Real World

This blog is to share my tips and tricks garnered over 25+ years in the IT industry

Paul's Favorites

January 2013 - Posts

Building Collections of Entity Classes using a DataReader

As discussed in the last blog post, it is a best practice to build entity classes. In the last post we filled a DataTable with Category data and then iterated over that DataTable to create a collection of Entity classes. In this blog post we will use a SqlDataReader to fill the Entity classes.

When using a SqlDataReader you must ensure that you close the data reader after you are done with it. You can write a try…catch…finally and close the data reader in the finally block, or you can use the using statement. I like the using statement because you do not have to write as much code. In my tests with VS 2010, both ways run just as fast.

I am going to use a new table that I created called Product for this sample. Here is the definition of the Product table. I am switching to another table because I wanted to have a lot of data to run some timing comparisons. I have filled this Product table with over 6200 rows of data. In addition, I wanted some different data types such as DateTime, decimal and boolean to show how to perform conversions and take into account null values in a database.

CREATE TABLE Product
(
  ProductId int PRIMARY KEY NONCLUSTERED
                IDENTITY(1,1) NOT NULL,
  ProductName varchar(50) NOT NULL,
  IntroductionDate datetime NULL,
  Cost money NULL,
  Price money NULL,
  IsDiscontinued bit NULL
)

We need to also create a Product class that looks like the following:

C#
public class Product
{
  public int ProductId { get; set; }
  public string ProductName { get; set; }
  public DateTime IntroductionDate { get; set; }
  public decimal Cost { get; set; }
  public decimal Price { get; set; }
  public bool IsDiscontinued { get; set; }   
}

Visual Basic
Public Class Product
  Public Property ProductId As Integer
  Public Property ProductName As String
  Public Property IntroductionDate As DateTime
  Public Property Cost As Decimal
  Public Property Price As Decimal
  Public Property IsDiscontinued As Boolean
End Class

Now, here is the code to load a collection of Product data into a collection of Product objects.

C#
private List<Product> GetProducts()
{
  SqlCommand cmd = null;
  List<Product> ret = new List<Product>();
  Product entity = null;

  cmd = new SqlCommand("SELECT * FROM Product");
  using (cmd.Connection = new SqlConnection(
        "Server=Localhost;Database=Sandbox;Integrated
          Security=Yes"))
  {
    cmd.Connection.Open();
    using (var rdr =
           cmd.ExecuteReader(CommandBehavior.CloseConnection))
    {
      while (rdr.Read())
      {
        entity = new Product();

        // ProductId is a NOT NULL field
        entity.ProductId = Convert.ToInt32(rdr["ProductId"]);
        // Strings automatically convert to "" if null.
        entity.ProductName = rdr["ProductName"].ToString();
        entity.IntroductionDate =
                DataConvert.ConvertTo<DateTime>(
                  rdr["IntroductionDate"],
                   default(DateTime));
        entity.Cost =
                DataConvert.ConvertTo<decimal>(rdr["Cost"],
                  default(decimal));
        entity.Price =
                DataConvert.ConvertTo<decimal>(rdr["Price"],
                  default(decimal));
        entity.IsDiscontinued =
                DataConvert.ConvertTo<bool>(
                  rdr["IsDiscontinued"],
                    default(bool));
                      
        ret.Add(entity);
      }
    }
  }

  return ret;
}

Visual Basic
Private Function GetProducts() As List(Of Product)
  Dim cmd As SqlCommand = Nothing
  Dim ret As New List(Of Product)()
  Dim entity As Product = Nothing

  cmd = New SqlCommand("SELECT * FROM Product")
  Using cnn As SqlConnection = _
  New SqlConnection( _
      "Server=Localhost;Database=Sandbox;Integrated
         Security=Yes")
    cmd.Connection = cnn
    cmd.Connection.Open()
    Using rdr As SqlDataReader = _
     cmd.ExecuteReader(CommandBehavior.CloseConnection)
      While rdr.Read()
        entity = New Product()

        ' ProductId is a NOT NULL field
        entity.ProductId = Convert.ToInt32(rdr("ProductId"))
        ' Strings automatically convert to "" if null.
        entity.ProductName = rdr("ProductName").ToString()
        entity.IntroductionDate = _
           DataConvert.ConvertTo(Of DateTime) _
             (rdr("IntroductionDate"), DateTime.MinValue)
        entity.Cost = DataConvert.ConvertTo(Of Decimal) _
             (rdr("Cost"), 0D)
        entity.Price = DataConvert.ConvertTo(Of Decimal) _
             (rdr("Price"), 0D)
        entity.IsDiscontinued = _
              DataConvert.ConvertTo(Of Boolean) _
                (rdr("IsDiscontinued"), False)

        ret.Add(entity)
      End While
    End Using
  End Using

  Return ret
End Function

The above code is fairly straight forward. Loop through each row and grab each column of data. Convert the data coming from the column into an appropriate value based on the data type. Remember when reading from a DataRow or from a column in the SqlDataReader that the data comes in as an "object" data type. So you must convert it in order to put it into a strongly typed property in your Product object. Of course, you must also handle null values and that is where the DataConvert class comes in.

The DataConvert Class

Whether you use a DataTable/DataSet like in my last blog post or whether you use a DataReader, you will need to check to see if the data read in from the database is a null value. If so, you either need to use Nullable data types in all of your classes, or you need to convert the null to some valid value for the appropriate data type. In the above code I used a class to check for and convert a null value into a default value for the data. The DataConvert class looks like the following:

C#
public class DataConvert
{
  public static T ConvertTo<T>(object value,
    T defaultValue) where T : struct
  {
    if (value.Equals(DBNull.Value))
      return defaultValue;
    else
      return (T)value;
  }
}

Visual Basic
Public Class DataConvert
  Public Shared Function ConvertTo(Of T As Structure) _
    (value As Object, defaultValue As T) As T
    If value.Equals(DBNull.Value) Then
      Return defaultValue
    Else
      Return DirectCast(value, T)
    End If
  End Function
End Class

I used a generic to specify the data type to convert to and then passed in the value from the column and a default value to return if the value is a null.

Summary

In this blog post saw how to create entity classes using a SqlDataReader instead of a Data Table as shown in the previous blog post. In addition you learned how to handle null values by using a DataConvert class.

Building Collections of Entity Classes

What is an Entity Class

An Entity class has properties and typically no methods. An entity class is generally used to hold a single row of data from a table. So, if you have a Category table with the fields CategoryId, CategoryName and Description, you will create a Category class with properties of the same name. For example:

C#
public class Category
{
  public int CategoryId { get; set; }
  public string CategoryName { get; set; }
  public string Description { get; set; }
}

Visual Basic
Public Class Category
  Public Property CategoryId() As Integer
  Public Property CategoryName() As String
  Public Property Description() As String
End Class

You would then create a collection class to hold 1 or more entity classes. So if your Category table had 100 rows and you read all 100 using a DataSet/DataTable, then you would end up with 100 Category classes in a collection. Below is a definition of a collection class using the Generic List<> class.

C#
public class Categories : List<Category>
{
}

Visual Basic
Public Class Categories
  Inherits List(Of Category)
End Class

There are many reasons for building an entity class and a collection class. Using an Entity class allows you to serialize this object and send across the web to any other application that needs a structure of the data. When you use an Entity class you get IntelliSense on the properties as opposed to a DataTable where you have to remember the name of the column in the collection. An Entity class also strongly types the data to what it was in the database. When the data is placed into a DataTable, each column goes in as the data type of object and comes back out as object. That means each time you access it you must convert it into the correct data type. There are many more reasons as well, but these are the main ones.

Reading Data into a Collection

Let's use the Categories table in the Northwind database to create a collection of entity classes. You will use a DataTable to read all of the rows in the table, then move each row of data into a new Category class.

C#
private List<Category> GetAllCategories()
{
  List<Category> ret = new List<Category>();
  Category cat;
  SqlDataAdapter da;
  DataTable dt = new DataTable();

  da = new SqlDataAdapter("SELECT CategoryId, CategoryName,
                           Description FROM Categories",
       "Server=Localhost;Database=Northwind;Integrated Security=Yes");

  da.Fill(dt);

  foreach (DataRow dr in dt.Rows)
  {
    cat = new Category();

    cat.CategoryId = Convert.ToInt32(dr["CategoryId"]);
    cat.CategoryName = Convert.ToString(dr["CategoryName"]);
    cat.Description = Convert.ToString(dr["Description"]);

    ret.Add(cat);
  }

  return ret;
}

Visual Basic
Private Function GetAllCategories() As List(Of Category)
  Dim ret As New List(Of Category)()
  Dim cat As Category
  Dim da As SqlDataAdapter
  Dim dt As New DataTable()

  da = New SqlDataAdapter("SELECT CategoryId, CategoryName,
                          Description FROM Categories",
       "Server=Localhost;Database=Northwind;Integrated Security=Yes")

  da.Fill(dt)

  For Each dr As DataRow In dt.Rows
    cat = New Category()

    cat.CategoryId = Convert.ToInt32(dr("CategoryId"))
    cat.CategoryName = Convert.ToString(dr("CategoryName"))
    cat.Description = Convert.ToString(dr("Description"))

    ret.Add(cat)
  Next

  Return ret
End Function

Summary

In this blog post you learned how to create an entity class and a collection of entity classes. You should try to use entity classes in your programming instead of data tables as you get IntelliSense, strong typing and much more flexibility than you do with loosely-typed objects such as data tables.

More Posts