Click here to Skip to main content
Click here to Skip to main content

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

, 22 Oct 2003
Rate this:
Please Sign up or sign in to vote.
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.

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Rob van der Veer
Web Developer
Netherlands Netherlands
Rob has been a professional web developer since 1998, and is working with C# and ASP.Net since early 2002.
 
Most of the time, his focus is on creating a clean and simple solution to development problems. (Although that sometimes means that he needs to do some hard work to make somebody else's life more easier.).

Comments and Discussions

 
GeneralMy vote of 5 Pinmembereonsimi4-Mar-12 8:35 
GeneralYou did 6 years befor Microsoft PinmemberAnjielun13-Nov-07 0:35 
GeneralCollaboration PinmemberSalvatore Vetro27-Mar-06 23:31 
GeneralDriebier.net is down PinmemberTessyThampan14-Jan-06 4:45 
AnswerRe: Driebier.net is down PinmemberRob van der Veer28-Sep-06 13:03 
AnswerRe: Driebier.net is down PinmemberRob van der Veer5-Dec-07 11:17 
GeneralRe: Driebier.net is down PinmemberTessyThampan14-Jul-08 20:24 
GeneralRe: Driebier.net is down PinmemberTessyThampan14-Jul-08 21:36 
GeneralRe: Driebier.net is down PinmemberRob van der Veer17-Jul-08 7:59 
GeneralRe: Driebier.net is down PinmemberTessyThampan17-Jul-08 20:15 
GeneralRe: Driebier.net is down PinmemberRob van der Veer18-Jul-08 2:26 
GeneralLicense Information PinmemberNeil Beutler24-Aug-05 6:29 
GeneralRe: Forums moved PinmemberRob van der Veer13-Mar-05 6:52 
GeneralRe: Forums moved PinmemberRob van der Veer5-Dec-07 11:21 
QuestionOleDb Access? PinsussPolis Pilavas28-Feb-05 11:27 
AnswerRe: OleDb Access? PinmemberRob van der Veer28-Feb-05 12:48 
GeneralRe: OleDb Access? PinsussPolis Pilavas28-Feb-05 13:21 
GeneralGetting started Pinmemberjanigorse20-Jan-05 5:03 
GeneralRe: Getting started Pinmemberhungn22-Feb-05 13:40 
GeneralRe: Getting started PinmemberRob van der Veer28-Feb-05 12:51 
GeneralThe code and example PinmemberJuan Manuel Bastidas Bonilla13-Dec-04 11:14 
GeneralLack of update PinmemberRob van der Veer21-Mar-04 4:12 
GeneralRe: Lack of update PinmemberArthur Warren22-Apr-04 4:56 
GeneralRe: Lack of update PinsussAnonymous22-Apr-04 7:53 
GeneralGentle.NET PinmemberFriendlyFiend18-Mar-04 20:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.1411022.1 | Last Updated 23 Oct 2003
Article Copyright 2003 by Rob van der Veer
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid