Tag: SQL

One thing that most developers loath is writing mapping classes. One common need is stuffing datasets into your objects from a return of your SQL Client code. Yes, I know that there are utilities that are out there like AutoMapper, but here’s something to consider.

The power of many of these utilities are their ability to map properties that have the same name, but different case without any code. This is definitely a time saver. I would highly recommend their use in the service layer of your UI if you’re mapping one object, or a small collection of objects like a paged response of 25 or 50 objects. But don’t use these tools if you are processing batch size collections. Why you ask?

Think about it, how would you find out if there was a property in an object with a particular name regardless of case? Reflection of course, but Reflection has a serious processing cost. So using it for a small collection of objects being displayed to a user is one thing, but when you’re processing thousands, or millions of objects, there is nothing that beats declarative code for speed.

I want to make it clear, this method has lots of advantages, as well as disadvantages, there’s never a panacea…

Advantages

1. One standard place that maps ALL your return values to your DTO
2. No maintenance of numerous areas mapping data. Think of the scenario where a PK or FK is changed from int to long…
3. The entire team ALWAYS knows where code is mapped, they don’t have to hunt through multiple service layers and mapping classes
4. Your object can consume DataSets from multiple stored procedures seamlessly and still populate, even if the field names are different.

Disadvantages

1. This is only recommended if you’re getting back DataSets, as otherwise, you’d be putting a dependency for an EF entity in your DTO. (Don’t do this, as then you have to reference your Repository in your UI project, which is, how do doctors say it, contra-recommended)
2. If you’re filling your object from multiple stored procedures, it will hide the fact that a particular column your expecting is absent, which is a code smell.

With the aforementioned caveats, let’s show the concept. The reason I only recommend this for DataSets is that it’s part of the .NET framework. I vehemently recommend that DTO’s don’t have any references to objects that don’t exist either 1) In the .NET framework, or 2) In your common utilities libraries.

Here’s an example of the constructors you would have in such an object:

        /// <summary>
        /// Default constructor
        /// </summary>
        public SalesProduct() { }

        /// <summary>
        /// Constructor with a DataSet to populate
        /// </summary>
        /// <param name="p_DataRow"></param>
        public SalesProduct(DataRow p_DataRow)
        {
            Populate(p_DataRow);
        }

Remember that when you code a constructor, like the second one, you stop the ability to instantiate your object without a parameter. So, in most cases, you want to declare an empty “Default” constructor as well. Remember, if you don’t have an empty constructor, you won’t be able to deserialize the object from a web service call…

Next step is to have the Populate(DataSet) method in your object like this example:

        /// <summary>
        /// Populate this object from a DataRow
        /// www.ytgi.com
        /// </summary>
        /// <param name="p_DataRow"></param>
        private void Populate(DataRow p_DataRow)
        {

            if (p_DataRow.Table.Columns.Contains("ProductID"))
            { this.ProductId = p_DataRow.IsNull("ProductID") ? 0 : p_DataRow.Field<int>("ProductID"); }

            if (p_DataRow.Table.Columns.Contains("ProductName"))
            { this.ProductName = p_DataRow.IsNull("ProductName") ? string.Empty : p_DataRow.Field<string>("ProductName"); }
            if (p_DataRow.Table.Columns.Contains("Description"))
            { this.ProductName = p_DataRow.IsNull("Description") ? string.Empty : p_DataRow.Field<string>("Description"); }


            if (p_DataRow.Table.Columns.Contains("UnitPrice"))
            { this.UnitPrice = p_DataRow.IsNull("UnitPrice") ? 0m : p_DataRow.Field<decimal>("UnitPrice"); }

            if (p_DataRow.Table.Columns.Contains("OnlineOrderFlag"))
            { this.OnlineOrderFlag = p_DataRow.IsNull("OnlineOrderFlag") ? false : p_DataRow.Field<bool>("OnlineOrderFlag"); }

            if (p_DataRow.Table.Columns.Contains("ShipDate"))
            { this.ShipDate = p_DataRow.IsNull("ShipDate") ? DateTime.MinValue : p_DataRow.Field<DateTime>("ShipDate"); }

        }

Notice one thing in the above code that’s my favorite feature of using this method, the ProductName property is being populated by either the “ProductName” column or the “Description” column, depending on the stored procedure you’re calling.

I typically use this implementation when I return a DataSet from a stored procedure and load the objects like this:

List<Models.SalesProduct> _sales = new List<Models.SalesProduct>();

// Would normally do this in the ServiceLayer
if (_returnData != null)
{
   foreach (DataRow _row in _returnData.Tables[0].Rows)
   {
       _sales.Add(new Models.SalesProduct(_row));
   }
}

I hope this helps folks to think of different ways to consolidate code that doesn’t violate the patterns they like to use.

If you’re going through the Paging series, visit the next post to continue: Business Objects for Paging.