Gal Ratner
Gal Ratner is a Techie who lives and works in Los Angeles. Follow galratner on Twitter Google
Introducing the Inverted Software Data Block. Simple ADO.NET to objects with no boilerplate code

I get asked many times by readers that have read To O/R Map or not to O/R Map - The Data Layer debate what framework I use and recommend when coding high volume systems. I always send them to Move a DataReader to an Object with reflection Revisited, but the truth is that article contains only a partial answer.

Sure you can use dynamic records, static reflection and many more techniques, but to truly make a system fast, you need to cache anything you can in memory, avoid complexity, keep Big O notation to a minimum and in general just go with a “less is better” attitude.


I therefore decided to skip to the bottom line of this article and show you what I use and how I use it.

After you got a sense of how easy it is to work with the Inverted Software DataBlock, I shell break down the framework into the underlying components.


Using the DataBlock

Getting an object from the output of a stored procedure:

Customer customer = CRUDHelper.GetObject<Customer>(parameters, generator, sprocName, stringConnection);



Getting a list of objects:

List<Customer> customers = CRUDHelper.GetObjectList<Customer>(generator, sprocName, stringConnection);



Getting a paged list of objects:

List<Customer> customers = CRUDHelper.GetObjectList<Customer>(generator, pageIndex, rowsPerPage, sprocName, stringConnection, out virtualTotal);



Updating a stored procedure from an object:

 

CRUDHelper.UpdateObject<Customer>(objectToUpdate, sprocName, stringConnection);



A generator will be a function that creates a customer object:

 

Func<Customer> generator = () => new Customer();



An older version of the Block was using Activator.CreateInstance<T>() which has proven to be far too slow.


This is my Customer object:

 

public class Customer
    {
        [CrudField(UsedFor = CrudFieldType.Delete | CrudFieldType.Read | CrudFieldType.Update)]
        public int CustomerID { getset; }
        [CrudField(UsedFor = CrudFieldType.Read | CrudFieldType.Update | CrudFieldType.Create)]
        public string Name { getset; }
        [CrudField(UsedFor = CrudFieldType.Read | CrudFieldType.Update | CrudFieldType.Create)]
        public string Email { getset; }
        [CrudField(UsedFor = CrudFieldType.Read | CrudFieldType.Update | CrudFieldType.Create)]
        public bool Active { getset; }
    }



It is simply a POCO object with an attribute, signaling the DataBlock how to use its properties.


The block also includes two convenience methods:

 

CRUDHelper.UpdateRecord(parameters, sprocName, stringConnection);
CRUDHelper.DeleteRecord(rowID, parameterName, sprocName, stringConnection);



When parameters is a Dictionary<string, object>.


Now that you know how to use the Inverted Software DataBlock let’s see how it’s built:
There are only six files making up the Block:


CrudField .cs Contains a custom attribute, signaling the Block what property is used in what operation type.
CRUDHelper.cs This is the main class and the entry point to the Block.
DataBlockException.cs Defines a custom exception the Block will throw on errors.
ObjectHelper.cs Contains cached information about objects properties, attributes and stored procedures output.
ObjectPool.cs Was taken from the ParallelExtensionsExtras to help with SQLCommands creation.
SQLHelper.cs A takeoff from the original PetShop example SQLHelper. Although this class does not resemble the original Microsoft example, it still contains basic ADO.NET code.


There is no magic here, just highly optimized code. For example: let’s look at a function in ObjectHelper:

 

public static List<string> GetColumnNames(SqlDataReader reader, string sprocName)
        {
            return QueryColumnNamesCache.GetOrAdd(sprocName, (key) =>
            {
                List<string> columnNames = new List<string>();
                System.Data.DataTable readerSchema = reader.GetSchemaTable();
                for (int i = 0; i < readerSchema.Rows.Count; i++)
                    columnNames.Add(readerSchema.Rows[i]["ColumnName"].ToString());
                return columnNames;
            });
        }



This function keeps a query’s list of output fields in memory and is used to iterate thought an object’s properties in order to load field to property by matching their names.


I have attached the complete source code to this article and you can feel free to download and use it in your systems.


If you simply want to use the final product, please install the Nuget Package.

Note: I would like to thank Alexandru Botez for his help with the Emit magic in ObjectHelper.cs


Posted 8 Jun 2012 6:24 PM by Gal Ratner
Filed under:

Powered by Community Server (Non-Commercial Edition), by Telligent Systems