Encapsulating ADO.NET Core Components - A C# Class Library

Introduction  

Background        

Many of you might already be using an Object-relational Mapping (ORM) tool in your projects. I do too. However, the main reason why I created this class library is because recently, I faced a situation wherein I needed something that is straightforward, gives me full control in writing the SQL statements, and does not hide the mapping between retrieved database records and CLR objects.  

Nice to know        

Before we move on to the "how-to" section, it would be nice to touch up on the following topics: 

1. Generics 
2. DbProviderFactories 
3. Custom configuration section 
4. Delegates and lambda expressions
   
5. The yield keyword 
6. Extension methods  

While some of the topics are not directly used in the example codes of this article, knowing them would give you a better understanding of how the library works.  

Using the class library  

• Download OpenEm.QLite.bin.zip 

The class library is just a single DLL file named OpenEm.QLite. You can download this file from the link above. Add a reference of this assembly to your project. Once you have done that, add the OpenEm.QLite namespace to your code:     

using OpenEm.QLite;          

The complete list of namespaces in the OpenEm.QLite assembly is as follows:  

<li>OpenEm.QLite  </li><li>OpenEm.QLite.Configuration  </li><li>OpenEm.QLite.Extensions     </li>

The OpenEm.QLite namespace contains the Database class which is the most important component of this library. Most of the coding will revolve around this class which encapsulates the core components of ADO.NET.

Using the OpenEm.QLite.Database class is pretty straightforward. We can divide its usage into 3 easy steps: 

1. Configure (optional)   
2. Connect  
3. Execute  

The next sections of this article will discuss these 3 steps in detail. From here on out, I will refer to OpenEm.QLite.Database as Database for brevity. 

Configuring the Database component  

Configuring the Database component is not mandatory. However, I always choose to setup the configuration so I can easily change the values of the global properties without going into my code.  

To setup the configuration, you must add the OpenEm.QLite.Configuration.DatabaseConfigurationSection section on your .config file. An example is provided below:   

<configSections>
  <sectionGroup name="openEm">
    <section name="qLite" 
             type="OpenEm.QLite.Configuration.DatabaseConfigurationSection, OpenEm.QLite" />
  </sectionGroup>
</configSections> 

Next, you must add the custom configuration section. Take note that you can add different configurations for different databases. See the example below:  

<openEm>
    <qLite default="main">
      <databases>
        <add name="main"
             connectionStringName="Main"
             alwaysDisconnectAfterExecution="true"
             commandTimeout="60"
             commandType="StoredProcedure" />
        <add name="admin"
             connectionStringName="Admin"
             alwaysDisconnectAfterExecution="false"
             commandType="Text" />
      </databases>
    </qLite>
</openEm>  

Let's discuss the XML above:  

1. <openEm> is an optional section group.  
2. <qLite> is mandatory. You can include a default attribute to this element to specify which configuration is the default database configuration. In the example above, main is the default configuration. 
3. The following are the configuration properties you can define: 
• name - This is mandatory and must be unique. It is used as a key by OpenEm.QLite.Configuration.DatabaseConfigurationCollection. 
• connectionStringName - The value must be the name of a specified connection string on your .config file. This is mandatory. It means that you are required to place your connection string in the <connectionStrings> collection. 
• alwaysDisconnectAfterExecution - This is optional. If set to true, connection is closed every after execution of a CRUD-related Database method. By default, it is set to false. 
• commandTimeout - This is optional. If this is not specified, the default value of the underlying CommandTimeout is used. 
• commandType - This is optional. If this is not specified, the default value of the underlying CommandType is used.  

Again, you are required to place your connection string in the <connectionStrings> collection. You are also required to specify the providerName. The class library uses System.Data.Common.DbProviderFactories which requires a provider name to get the appropriate System.Data.Common.DbProviderFactory. The underlying System.Data.Common.DbProviderFactory then creates the appropriate connection object for you when you call the Database.Connect() method.

Finally, to use the configuration in your code, you must call the Database.Configure() method. It takes a single parameter of type string which must be the name of the configuration section you have specified on your .config file. 

Database.Configure("openEm/qLite");   

You only need to call this method once in your project. If you are building a web application, you can call this in the Application_Start() event handler of Global.asax. 

Connecting to the database 

There are 3 ways you can connect to your database using the Database class. If you're using a configuration, you can simply do the following: 

using (var db = Database.Connect())
{
    // some code here
}    

In the example above, the connectionStringName of the default configuration is used inside the Database.Connect() method to create and open the connection object for you. If you did not specify a default configuration, it will then get the connectionStringName from the first element of the <databases> collection.  

Another way of connecting to your database is to pass the name of your preferred configuration to the Database.Connect() method (you can refer to the previous section for the sample XML configuration):  

using (var db = Database.Connect("admin"))
{
    // some code here
}    

Lastly, if you do not wish to use any of your configuration settings, you can use the overload of the Database.Connect() method from the previous example, but this time, pass the name of your connection string as the method parameter. See the example below: 

<connectionStrings>
    <add name="Developer" 
         connectionString="Data Source=.;Initial Catalog=DevDB;Integrated Security=SSPI" 
         providerName="System.Data.SqlClient" />
</connectionStrings>  

using (var db = Database.Connect("Developer"))
{
    // some code here
}    

Please take note that the Database class implements the System.IDisposable interface. It is preferred to use the using keyword when initializing the Database object to ensure that it is disposed automatically at the end of the using block. Alternatively, you can call Database.Disconnect(), an instance method, to close the connection. 

Executing CRUD operations 

In this class library, I spent more time and effort in designing and writing methods for retrieval operations. However, I did create a Database.ExecuteNonQuery() method for Create, Update and Delete operations. This method just invokes the <code>ExecuteNonQuery() method of the underlying command object.  

Create, Update and Delete operations  

The example below shows how to add a record in the database. If you want to use the Database.ExecuteNonQuery() method to update or delete a record, just pass an update or a delete SQL statement to the method. 

var person = new Person // this is just a sample object
{
    ID = 0,
    FirstName = "John",
    LastName = "Doe"
    // some more properties
};
 
var sql = "INSERT INTO [Person] ([FirstName], [LastName]) VALUES (@FirstName, @LastName)" +
" SET @ID = @@IDENTITY";
Parameterize param = (parameters) =>
{
    parameters.Add("@FirstName", person.FirstName);
    parameters.Add("@LastName", person.LastName);
    parameters.Add("@ID", ParameterDirection.Output, DbType.Int32);
};
CommandCallback onClosing = (command) => person.ID = Convert.ToInt32(command.Parameters["@ID"]);
 
using (var db = Database.Connect())
{
    var result = db.ExecuteNonQuery(sql, param, onClosing);
}      

Let me explain the 3 parameters used in the example above:

1. sql - This parameter must be the SQL statement or the name of the stored procedure that you want to be executed. 
2. param - This is an OpenEm.QLite.Parameterize delegate that takes an OpenEm.QLite.CommandParameters object as a parameter. The OpenEm.QLite.CommandParameters object is where you add your SQL parameters.
3. onClosing - This is an OpenEm.QLite.CommandCallback delegate that takes a System.Data.Common.DbCommand object as a parameter. This callback is invoked before the command object is closed or disposed and is the best place where you can retrieve values of output or return parameters.  

Retrieve operation   

For data retrieval, you have more options. I created 3 different methods to help you in this area:

• GetAll<T>() - As the name suggests, it retrieves all data and returns a System.Collections.Generic.IEnumerable<T> object. There is no way you can add SQL parameters through this method to filter your query.  
• Get<T>() - This method gives you the option to add parameters to filter your query or retrieve an output or return parameter. It returns a System.Collections.Generic.IEnumerable<T> object. 
• GetOne<T>() - This method is similar to Get<T>(), but it returns only 1 instance of T. 

Each of the methods above has multiple overloads and each overload has its own way of mapping retrieved database records to CLR objects. The following are the different ways of mapping for all 3 "get" methods:   

• OpenEm.QLite.Mapping<T> - This delegate takes a System.Data.Common.DbDataReader object as a parameter which you can use to read the retrieved values. You don't need to iterate through the reader object. That's already done internally. An example of how to use this delegate is provided below: 

C#

var sql = "SELECT [ID], [FirstName], [MiddleName], [LastName] FROM [Person]";
Mapping<Person> mapping = (reader) =>
{
    return new Person
    {
        ID = reader.GetInt32("ID"),
        FirstName = reader.GetString("FirstName"),
        LastName = reader.GetString("LastName")
        // some more properties
    };
}; 
 
using (var db = Database.Connect())
{
    var people = db.GetAll(sql, mapping).ToList(); 
}   

• OpenEm.QLite.IMapper<T> - This interface has a Map() method that also takes a System.Data.Common.DbDataReader object as a parameter. The concept is pretty much the same as the OpenEm.QLite.Mapping<T> delegate. You can use this option if you'd like to implement some more logic other than just the mapping itself. An example is provided below: 

C#

public class PersonMapper : IMapper<Person>
{ 
    public int CountOfMothers { get; private set; } 
 
    public Person Map(DbDataReader reader)
    { 
        var personType = reader.GetString("Type");
        if (peronType == "Mother")
            CountOfMothers += 1; 
        return new Person
        {
            ID = reader.GetInt32("ID"),
            FirstName = reader.GetString("FirstName"),
            LastName = reader.GetString("LastName"),
            Type = personType
            // some more properties
        }; 
    }
}  

C#

var sql = "SELECT [ID], [FirstName], [MiddleName], [LastName] FROM [Person]"; 
var mapper = new PersonMapper();
 
using (var db = Database.Connect())
{
    var people = db.GetAll(sql, mapper).ToList();
    var countOfMothers = mapper.CountOfMothers;
}     

• Mapping through reflection - If you do not wish to control the mapping process, there are "get" method overloads that do not require an OpenEm.QLite.Mapping<T> delegate or an OpenEm.QLite.IMapper<T> interface parameter. Those methods use reflection internally to map database records to CLR objects. However, I should also mention that when reflection is used, only retrieved columns that have matching entity properties are mapped. Using this method is only advised for very simple mapping scenarios. For complex or not-so-simple mapping scenarios, it is advised that you use the other techniques mentioned above. 

Finally, you can implement the OpenEm.QLite.ICollectionMapper<T> interface. It inherits the OpenEm.QLite.IMapper<T> interface which has a Map() method. OpenEm.QLite.ICollectionMapper<T> can only be used with "get" methods that return a System.Collections.Generic.IEnumerable<T> object. It gives you the control to iterate through the System.Data.Common.DbDataReader object. See the example provided below:  

public class OrdersMapper : ICollectionMapper<Order>
{
    public IEnumerable<Order> Map(DbDataReader reader)
    { 
        var order = default(Order);
        var dictionary = new Dictionary<Guid, Order>(); 
        while (reader.Read())
        {
            var id = reader.GetGuid("ID");
            if (dictionary.ContainsKey(id))
                order = dictionary[id];
            else
            {
                order = MapOrder(reader);
                order.ID = id;
                dictionary.Add(id, order);
            }
            var orderItem = MapOrderItem(reader);
            order.OrderItems.Add(orderItem);
        }
        return dictionary.Values.ToList();
    }
    private Order MapOrder(DbDataReader reader)
    { 
        // do individual mapping here
        // this method is not an ICollectionMapper<T> member
    }
    private OrderItem MapOrderItem(DbDataReader reader)
    {
        // do individual mapping here
        // this method is not an ICollectionMapper<T> member
    }
}  

var sql = "SELECT [o].[ID], [o].[Number], [o].[DateOrdered], [oi].[ID] AS [OrderItemID]," +
" [oi].[OrderID], [oi].[Item], [oi].[Description] FROM [Order] [o]" +
" INNER JOIN [OrderItem] [oi] ON [o].[ID] = [oi].[OrderID]";
ICollectionMapper<Order> collectionMapper = new OrdersMapper(); 
 
using (var db = Database.Connect())
{
    var orders = db.GetAll(sql, collectionMapper);
} 

Take note that "get" methods that do not take an OpenEm.QLite.ICollectionMapper<T> interface as a parameter and do not return a System.Collections.Generic.IEnumerable<T> object use the yield keyword internally which means the execution is deferred. Be cautious when using these methods. The example below will throw an exception:  

var sql = "SELECT [ID], [FirstName], [MiddleName], [LastName] FROM [Person]";
Mapping<Person> mapping = (reader) =>
{
    return new Person
    {
        ID = reader.GetInt32("ID"),
        FirstName = reader.GetString("FirstName"),
        LastName = reader.GetString("LastName")
        // some more properties
    };
}; 
 
using (var db = Database.Connect())
{
    var collection = db.GetAll(sql, mapping); 
    var count = collection.Count(); // the DbDataReader object will be closed here 
    var people = collection.ToList(); // exception is thrown here
}     

More method overloads   

The Database class has more CRUD-related method overloads that can be found in the OpenEm.QLite.Extensions namespace. These methods are extension methods. The reason why I isolated them in another namespace is because I felt that these methods would be rarely used. The reason I wrote them is because I felt that it's nice to have them.

If you wish to use the extension methods, just add the OpenEm.QLite.Extensions namespace to your code:  

using OpenEm.QLite.Extensions;  

Conclusion 

I just want to say that this is my very first post on this site. I don't normally write blogs or online articles. I've been away from friends and family for the past 6 months working here in Singapore with not much to do during my free time. Working on this article made my "alone" moments in a foreign land very productive and quite enjoyable. I enjoyed every single day working on this article and creating the class library. I know it's not one of the most exciting articles out there (I know this, because as the author, I fell asleep while proof-reading it), but I hope you find it helpful and informative. For questions or comments, feel free to contact me. 

History     

2012-06-07 : First post