Data access in C#

2008/12/29

Introduction

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.

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:

CREATE TABLE t_person
(
   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>(
   person,
@"
BEGIN
   <Id> := sys_guid;
   INSERT INTO
      t_account
      (
         pk_account,
         s_login,
         s_password
      )
      VALUES
      (
         <Id>,
         <Account.Login>,
         <Account.Password>
      );
   INSERT INTO
      t_person
      (
         fk_account,
         s_first_name,
         s_last_name
      )
      VALUES
      (
         <Id>,
         <FirstName>,
         <LastName>
      );
END;");
insert.Execute();

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>(
   person,
@"
UPDATE
      t_person
   SET
      s_first_name = <FirstName>
   WHERE
      fk_account = <Id>;");
update.Execute();

Lets delete Person from database:

var delete= new EntityScriptCommand<Person>(
   person,
@"
DELETE
      t_person
   WHERE
      fk_account = <Id>;");
delete.Execute();

Lets select Persons from database:

var persons = new List<Person>();

var reader = new EntityReaderCommand<Person>(
   delegate(EntityReaderArguments<Person> args)
   {
      persons.Add(args.Entity);
   },
@"
SELECT
      p.fk_account <Id>,
      p.s_first_name <FirstName>,
      p.s_last_name <LastName>,
      a.s_login <Account.Login>,
      a.s_password <Account.Password>
   FROM
      t_person p
   INNER JOIN
      t_account a ON
         a.pk_account = p.fk_account");
reader.Execute();

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

About these ads

10 Responses to “Data access in C#”

  1. varun pant Says:

    Great article….. Love your work……

  2. 6opuc Says:

    Thanks! I’m glad you like it!

  3. richard Says:

    “When fetching one object, then all dependent objects are fetched too, how can i disable this feature?” You should check out LLBLGen Pro, it handles this perfectly!

  4. 6opuc Says:

    Of course it does, but every ORM does it differently… I just wanted to point out additional learning curve

    Anyway, thanks for link! I heard that LLBLGen is great…

  5. Rcastro Says:

    very interesant article, thanks

  6. haitham Says:

    very nice article, i like your approach but i want to ask a question here, parameters are not just for inserting or updating data to database, the can be used in reading to. for example when you have searching windows form and you want to search based on user data entery i guess here you will need parameters

  7. 6opuc Says:

    I always use parameters in select queries. It is not safe to substitute criterias directly as a part of a query.
    So parameters are supported in select queries, you can find samples

    there
    .

  8. Matt Stark Says:

    Brilliant adapt.

    I get a good laugh when I read how complicated / slow some of these ORM layers are for particular tasks. Not to mention over complicated cough cough …

    How easily could your solution be adapted to use Stored Procedures over scripts / is there any risk of SQL injection when using this methodology (seems to me you could just change the command text to stored proc no?)?

    You should check out Omar Al Zambir’s (Pageflakes.com fame) AspectF library – seems if this technique was coupled with the CacheList call you could get a pretty darn sweet disconnected cache CRUD layer that was super easy to debug :)

    Nice Work! I will be testing this today / will report my findings.

  9. 6opuc Says:

    SQL injection is possible only if you are passing user input directly in sql query. If you are using parameters for user input all will be safe.
    Property paths in < and > are substituted by parameters in query, so the answer is:
    no, there is no any risk of SQL injection.

    Thanks!

  10. Matt Stark Says:

    I noticed that after I commented – my fault for not being comprehensive – thanks for your replies!!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: