Data access in C#



There are a lot of solutions which helps to simplify data access layer implementation in C#. As for me, i’ve used different data access libraries from primitive DatabaseHelper(Enterprise Library) in my first .net projects to excelent NHibernate wrapper called ActiveRecord(CastleProject) in recent ones. But what ever you use, IDbProvider with pure SQL or special ORM’s SQL-dialect like HQL or others, there some pros and cons in each approach.

The main advantage of using native database driver directly gives a lot of abilities specific to database, such as hierarchical queries. You have full control of query generation with full featured native SQL. But with all these native features your code becomes tightly coupled with this native driver, and the problem not only with specific SQL syntax… For example, always setting BindByName = true in OracleCommand, troubles with decimal precision, modifying FetchSize in OracleCommand to make fetches not as slow as by default… there are a lot of driver specific logic to write, and this cannot be done through ADO.NET Idb* interfaces. So, you have to write your own “DatabaseHelper”(or use one of thousands already implemented of course) or always “copy-paste” this logic everywhere in your data access layer.

Another problem is mapping between database objects and domain objects. It needs to much code to write for simple CRUD operations: open connection, create command, create parameter for each property of domain object , execute command and retrieve parameter values back into properties, and all this code is just the same everywhere. So, here comes an ORM tool…

ORM simplifies data access layer development greatly: you don’t have to add a lot of parameters, you use simplified syntax for session and transaction management etc… But where is hierarchical queries? What is supported by this SQL dialect at all? When fetching one object, then all dependent objects are fetched too, how can i disable this feature? Why i can’t update an object, fetched from different session? In spite of its powerful features there are a lot ofdifferent constraints depending on ORM version you choose.  Some ORM’s just generate code and as your database schema changes you always need to regenerate your objects definitions. Others use xml mappings between classes and database tables, or maybe attributes, defined on classes. 

As for me, i’m not a big fan of object-relational mappings defined through attributes, i don’t like xml mappings and i really hate code generation…

The only thing i need from data access layer is strongly typed CRUD commands on lightweight data contracts, SQL syntax should be as near to native as possible, there should not be any attribute(or xml) mappings, i want to define mappings as near to query as possible and it should be as fast as IDataReader can be. So here is example of my point of view on data access layer in c#.

Lightweight Data Contracts.

Data contract  just describes data to be exchanged between Data Accees Layer and Business Layer of your application. So an instance of data contract class can be object oriented representation of corresponding database table row. Classes used as data contracts should be as light as possible, just properties and equality implementation. Use your own implementations of ICollection<T> or IList<T> for object lists, forget about DataTable even if it is strongly typed! Fetching list of custom objects will much faster than filling DataTable.

Data contract should not require any attributes or base classes, no constraints on DataContexts. Data contracts are disconnected from database.

So, we have simple and lightweight data contracts and now we need tools for read/modify data, using our contracts.

Strongly typed CRUD

All we need is two types of commands: Readers and Scripts.

Readers are just for fetching data from database(just like IDataReader), with each fetch client code can obtain only one instance of data contract class. Client code should be able to terminate fetching before or after each record. Reader is not constrained on any type of collection types, client code chooses one. Reader fetches should work as close to speed of IDataReader as possible. 

Scipts are some blocks of code to be executed with no return on an instance of data contract class. This can be simple insert statement or an PL/SQL block. It is just like IDbCommand.ExecuteNonQuery(), but it really differs, the only parameter to passed into command should be the instance of data contract class, you don’t have to specify all those parameters for insert statement! 

Both command types  should support simple input parameter substitution, no ugly command.Parameters.Add(…). 

Of course both commands should be strongly typed, and support custom types of data contract properties.

If something is not clear, please take a look at examples.


Lets define several classess which we will use as data contracts. These classes will be very light, their purpose is just to describe data model.

public class Person
   public Guid Id { get; set; }
   public String FirstName { get; set; }
   public String LastName { get; set; }
   public Account Account { get; set; }

public class Account
   public String Email { get; set; }
   public String Login { get; set; }
   public String Password { get; set; }

Lets create database tables:

   fk_account VARCHAR2(50),
   s_first_name VARCHAR2(50),
   s_last_name VARCHAR2(50)

CREATE TABLE t_account
   pk_account VARCHAR2(50),
   s_login VARCHAR2(50),
   s_password VARCHAR2(50)

Lets create new Person in database:

var person = new Person {
   FirstName = "Vasily",
   LastName = "Pupkin",
   Account = new Account{
      Login = "Vasya",
      Password = "Password"

var insert = new EntityScriptCommand<Person>(
   <Id> := sys_guid;

Note, that after insert.Execute() person.Id will be retrieved from database with new generated value!

Lets change Person’s FirstName and update changes to database:

person.FirstName = "Vasya";

var update = new EntityScriptCommand<Person>(
      s_first_name = <FirstName>
      fk_account = <Id>;");

Lets delete Person from database:

var delete= new EntityScriptCommand<Person>(
      fk_account = <Id>;");

Lets select Persons from database:

var persons = new List<Person>();

var reader = new EntityReaderCommand<Person>(
   delegate(EntityReaderArguments<Person> args)
      p.fk_account <Id>,
      p.s_first_name <FirstName>,
      p.s_last_name <LastName>,
      a.s_login <Account.Login>,
      a.s_password <Account.Password>
      t_person p
      t_account a ON
         a.pk_account = p.fk_account");

So, we can use these commands without any mappings, all work done directly in queries.
We have full control on CRUD operations and we don’t have to add those ugly parameters into commands.
This is my point of view on data access layer,
Comments appreciated!

UPD: If you are interested, you can download solution from here


Get every new post delivered to your Inbox.