Using a generic data abstraction layer for database access in .NET

• Download source files - 29 Kb

Preface

This document is not yet finished, but because I think some of you may like this solution, I decided to publish it. I will add sample code, source code and solution documentation later. Please let me know if you like it.

Introduction

This document describes pro’s and con’s of the use of a so-called data interface abstraction layer that separates the logic of the application from the functions that retrieve data from a database. The described solution is mainly focused on the retrieval of data, considering that most of the application of this library is placed at the front-end of a database-driven website.

The proposed solution removes the hard coding of SQL statements in the application, therefore maximizing the portability of the program, while minimizing the risk of introducing new bugs. The solution uses strongly typed classes for retrieval of data, thereby removing the need of tedious conversion of database column types to .NET data types.

Area of use

The following article does intentionally not talk about updates, inserts or deletes. The reason for this is that the proposed solution was applied in a framework for a website that had its content only retrieved from a database. All updates on the data were performed by a different content management tool.

Background

I've seen a lot of attempts that enable a programmer to simplify data access. I decided to try it myself. This is not rocket science, but this IS my first article and I hope you all like it.

Data abstraction layer?

In any multi-tier solution, there typically is a lower layer of software that implements database access. Instead of having SQL statements all over the solution, all of the functions and statements that are somehow related to database interactivity are separated in a group of functions, that are to be called by other components of the entire application.

The usage of such an application is best described by a small piece of fictional business logic:

public double CalculateOrderTotal(int ordernumber)
{
    OrderDB db_order = new OrderDB();
    double total = 0;

    foreach(row in db_order.GetOrderRows())
    {
        total += row.Price & row.AmountOrdered;
    }
    return total;
}

The above sample clearly separates the logic from the way the database is accessed.

Nothing new

Now how would one implement the database functions required, like the OrderDB class in the previous example? The standard .NET solution would look something like this:

Public class OrderDB
{
    public DataSet GetOrderRows(int ordernum)
    {
        SqlConnection conn = new SqlConnection("Server=etc;");
        SqlDataAdapter adapter = new SqlDataAdapter();
        Adapter.Connection = conn;
        Adapter.Command.CommandText = 
          "SELECT * FROM OrderRows WHERE OrderNum = " + ordernum;

        DataSet data = new DataSet();
        Adapter.Fill(data, "orderlines");
        DataTable table = data.Tables["orderlines"];
        return table.Rows;
    }
}

Note: the example above is just intended as an example.

Now, this looks very simple indeed, but now imagine having to write the above stuff about 40 times or more. In a typical application, 40 different kinds of SELECT statements isn’t that much. You could decide to do all your filtering and where clauses by hand in your business logic, but that would be a waste of CPU cycles, isn’t it?

Next to this, the above samples don’t even provide the amount of functionality required to make the above solution work, as there are not properties in an untyped DataSet like .Price and .AmountOrdered.

In summary, although this looks like the way Microsoft wants us to program, this is not the way we want to go. Lets look at the options:

Options

At this point, I'm sure you agree that there must be different solutions to coding all statements yourself. Let me propose a couple of alternatives:

1. Implement all of this using a strongly typed DataSet.
2. Write a set of functions that minimize the code required to do a select statement.
3. Buy a very expensive framework that does this all for you.. naah, lets not go there. We’re programmers.
4. More solutions you may have in mind?

Let us look at these options:

Option 1: Implement all of this using a strongly typed DataSet. Now this option has some very strong arguments to go with itself.

Option 2: Write a set of functions that minimizes the code required to do a select statement. We all have seen a lot of standardized SqlHelper type of classes. But still you will need to write a separate function for each SELECT statement.

Option 3: Buy a very expensive framework that does this all for you.. naah, lets not go there. We’re programmers. Its no fun, its always lacking in support, it does waaay to much you would need most of the time and it goes straight against everything we learned from the ‘not invented here syndrome’.

Option 4: More solutions you may have in mind. I do not have enough knowledge to say anything about anything you may have in mind, so just let me tell you what I came up with.

A solution

I call it A solution, because mileage may vary for each project. But maybe its good enough for you. Its name is “EntityPool”, simply because I couldn’t come up with a better name.

EntityPool is a generic solution, which means that you would never have to write data access classes to retrieve data and convert it into objects. You just define your objects and tell Entitypool how to retrieve them by adding attributes to the class containing the metadata.

What can it do?

• Perform database calls
• Convert the resulting record or records into a real object
• It is not tied to a particular provider (e.g. Sql, Oracle, OleDb, Access, XML)
• Expandable by implementing interfaces.
• Makes use of DataReaders for fast access
• Generates its own SQL statements derived from a class.
• Caches all generated command objects automatically

EntityPool works by performing the following steps:

1. EntityPool inspects a class to find out what columns it must select by using rReflection
2. Calls on the configuration file to determine what provider must be used.
3. Calculates the SQL statement or stored procedure to perform and derives the required parameters
4. Creates a connection to that database
5. Perform the command on the database
6. Converts all the returned records into objects by filling the properties of the class.

Using Entitypool

Now for some examples. Note that the examples are only examples on how to use the library. There will be an attached bigger example later.

Example #1 is the most basic sample. It shows how to retrieve all customers.

Consider the following definition of a customer object:

public class Customer
{
    int customerID;
    string customerName;
    bool activeAccount;
}

Assumed that you have a database table named Customer, with columns similar of name and type. You can retrieve a list of customers by:

public Customer[] GetAllCustomers
{
    //Setup a connection to the pool.
    EntityPool.Manager manager = new EntityPool.Manager();
    EntityPool.Connection conn = manager.CreateConnection();

    //retrieve the data
    EntityPool.EntityCollection results =
        Conn.GetEntities(typeof(Customer));

    return (Customer[])results.ToArray(typeof(Customer));
}

That is all, really. Note, that the above code does not contain any SQL statements or connection string. The connection string was taken from the application or web configuration file as follows:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
 <configSections>
    <section name="entityPool" 
           type="EntityPool.ConfigurationHandler, EntityPool"/>
 </configSections>

 <entityPool>
   <provider type="EntityPool.Providers.SqlProvider, EntityPool.Providers"
     connectionString=
       "Provider=SqlOleDb;Server=(local);Database=****;UID=***;PWD=******"
             />
 </entityPool>
</configuration>

Example #2 shows how to retrieve a single customer based on some key.

public class Customer
{
    [EntityPool.KeyColumn()]
    int customerID;

    string customerName;
    bool activeAccount;
}

The KeyColumn attribute defines that CustomerID is the key column in the database. You now have the ability to select a single record using that column.

public Customer GetSingleCustomer(int id);
{
    EntityPool.Manager manager = new EntityPool.Manager();
    EntityPool.Connection conn = manager.CreateConnection();

    return (Customer)conn.GetEntity(typeof(Customer), id);
}

Now wasn’t that easy?

Example #3 shows how to retrieve a subset of customers by using a defined filter.

It also demonstrates how to loop through the results.

[DefineFilter("FindCustomers", 
       "customerName LIKE @namefilter", 
       new string[] { "@namefilter"})
      ]
public class Customer
{
    ….
}

Usage of the iterator:

foreach(Customer customer in conn.GetEntities(typeof(Customer), 
                                 "FindCustomers", new string { "%Bel%" } )
{
    Trace.WriteLine(customer.customerName);
}

Example #4 shows how to retrieve a subset of customers by using a stored procedure

TODO

Example #5 shows how to add attributes to the class to map different column names to the correct class and field names.

TODO

Future expansions

In future, I may add functions that allow you to persist the changes you made on an entity back into the database. However, the enormous increase in complexity you get when trying to do these things may not live up to expectations.

Summary

In this short document, I explained the advantages of using a separate group of functions that contained all your database logic.

Also, we saw how much code you would have to write just to select a couple of records from a database. We also found out that there are a lot of options, each and everyone having their advantages and disadvantages.

Next, we found out that EntityPool could save you a lot of work and a lot of typing to achieve the same results, while maintaining enough flexibility.