Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server / SQL Server 2008

Simple Data Access in C#

Rate me:
Please Sign up or sign in to vote.
4.11/5 (15 votes)
11 Jan 2009CPOL5 min read 83.4K   1.4K   58   17
Fast and easy to use data access class library.

assembly_graph.jpg

Introduction

I have implemented this class library during the development of several database-driven projects. Some of these projects utilized large amounts of data, and it was needed to implement a fast working and easy to use data access layer. I will not describe my reasons why to not use typed DataSets, NHibernate, DLINQ, or something similar (but if you are interested, here I've described some of them). I think that if you are reading this article you have your own ones.

Instead, I'll describe what this class library provides:

Strongly typed SQL commands

I've implemented support for two SQL command types: select queries (EntityReaderCommand<TEntity>) and scripts (EntityScriptCommand<TEntity>).

With EntityReaderCommand<TEntity>, you can read entities from the database by executing a specified SQL query. It works by analogy with IDataReader implementations, but when the IDataReader with each fetch returns an IDataRecord, the EntityReaderCommand<TEntity> returns an instance of the TEntity class.

This code sample will fill List<Person> from the database table "Person":

C#
var persons = new List<Person>();
var reader = new EntityReaderCommand<Person>(
   args => persons.Add(args.Entity),
   @"
SELECT
      Id <Id>,
      FirstName <FirstName>,
      LastName <LastName>
   FROM
      Person");
reader.Execute();

This code sample will read a Rectangle instance from the database:

C#
var rectangle = new Rectangle();
new EntityReaderCommand<Rectangle>(
   delegate(EntityReaderArguments<Rectangle> args)
   {
      rectangle = args.Entity;
      args.Terminate = true;
   },
   @"
SELECT
      1 <Location.X>,
      2 <Location.Y>,
      3 <Width>,
      4 <Height>").Execute();

This code sample does the same, but using EntityScriptCommand<TEntity>:

C#
var command = new EntityScriptCommand<Rectangle>(
   new Rectangle(),
   @"
SET <Location.X> = 1
SET <Location.Y> = 2
SET <Width> = 3
SET <Height> = 4");
command.Execute();
var rectangle = command.Entity;

The following code sample inserts a new Person instance in the database table "Person". Note, the Person object will have a new Id after insert.Execute().

C#
var person = new Person{
   FirstName = "Boris",
   LastName = "Nadezhdin"
};
var insert = new EntityScriptCommand<Person>(
   person,
   @"
SET <Id> = newid()
INSERT INTO
   Person
   (
      Id,
      FirstName,
      LastName
   )
   VALUES
   (
      <Id>,
      <FirstName>,
      <LastName>
   )");
insert.Execute();

So, as you can see from the samples above, the query syntax is native to the SQL client, the only difference is mapping between the entity properties and the column aliases. Commands support composite properties (like Rectangle.Location). Entities and their properties can be reference or value types. There is no need for special XML or attribute based O/R mappings, all mappings are specified directly in the queries.

There are only two restrictions on the types of entities and their properties:

  • The type should have a default constructor.
  • If the property is used in a command, it should have a public getter and setter.

Disconnected entities

There is no proxy generation for entities in the command execution logic, so if you are using an EntityReaderCommand<TEntity>, you will read the TEntity, not a generic ancestor.

There is no constraints on entities and session scopes, you can read an entity from the database in one session and update it back in another one.

There is no entity cache, so you can have several instances of the same database entity.

Simple syntax for command parameters

Sometimes it needs to pass some additional arguments into the command. For example, to find a Person by Id:

C#
public Person FindOne(Guid id)
{
   Person person = null;
   var reader = new EntityReaderCommand<Person>(
      delegate(EntityReaderArguments<Person> args)
      {
         person = args.Entity;
         args.Terminate = true;
      },
      @"
   SELECT
         Id <Id>,
         FirstName <FirstName>,
         LastName <LastName>
      FROM
         Person
      WHERE
         Id = {0}", id);
   reader.Execute();
   return person;
}

As you can see from the sample above, the syntax is similar to String.Format(String, params Object[] args), but in that case, id is not substituted by its string representation, it is passed into the command as a parameter.

Here is another sample in which you can see how to pass an argument and get its modified value back after the command execution:

C#
const int one = 1;
var command = new SimpleCommand(
   "SET {0} = {0} + 1", one);
command.Execute();
Assert.AreEqual(one + 1, command.GetArgs()[0]);

Transparent session and transaction management

Each command needs an open database session; if there is no open session in the current thread, the command opens a new session and closes it after execution.

To open new session manually, you should create a new instance of SessionScope; to close it, call Dispose() on the instance of the SessionScope. So, all commands in the scope of the SessionScope object will use one session, created by the SessionScope object.

If you are working on a web application and you want to have a database session per web request, you can create an instance of the SessionScope (and store it in items of HttpContext, for example) in the BeginRequest event handler and dispose it in the EndRequest event handler.

In addition to SessionScope, there is another scope of command execution, TransactionScope. It is used for transaction management.

To start a new transaction, you should create a new instance of TransactionScope. To end it, call Dispose() on the instance of the TransactionScope. So, all command in scope of that TransactionScope object will work in the transaction created by the TransactionScope object. Transactions are marked to rollback by default. So, to commit a transaction, you should call Commit() on the instance of the TransactionScope. Note, that no actual commit is performed before the disposal of TransactionScope.

Another interesting feature of command scopes is that they are local to thread there they were instantiated. So commands in different threads will always work in different database sessions.

Native database client abstraction layer

Objects from this class library are not coupled with any concrete database client. Session and transaction management, command execution, all work with native database clients only through the IDatabaseProvider interface. All methods specific to native database clients are extracted in this interface. Currently, there is only one implementation of IDatabaseProvider, for Microsoft SQL Server, but it is really simple to implement ones for Oracle, PgSQL, or MySQL.

The concrete IDatabaseProvider implementation and database connection string can be defined declaratively in the application configuration file, or programmatically via Scope.Configuration. Application configuration file is used by default, for example:

XML
<?xml version="1.0"?>
<configuration>
   <configSections>
      <section
         name="dataAccess"
         type="Yap.Data.Client.ConfigurationSectionHandler, Yap.Data.Client"/>
   </configSections>

   <dataAccess
      providerType="Yap.Data.Client.Sql.SqlProvider, Yap.Data.Client.Sql"
      providerAssembly="Yap.Data.Client.Sql.dll"
      connectionString="Data Source=.\SQLEXPRESS; Database=YAP;Integrated Security=True;"/>
</configuration>

Sources

The solution was created in Visual Studio 2008, and consists of three projects:

  • Yap.Data.Client
  • Yap.Data.Client.Sql
  • Yap.Data.UnitTest

The main object model is defined in Yap.Data.Client. The IDatabaseProvider implementation for Microsoft SQL Server is defined in Yap.Data.Client.Sql. Yap.Data.UnitTest contains test fixtures for the Yap.Data.Client assembly. Yap.Data.UnitTest uses MSTest, but can be easily converted to NUnit.

To run Unit Tests, it needs to create a database, execute create-script.sql from the folder SQL, and modify the connection string in the app.config file.

Binaries

You need only three things to use this class library in your project:

  • Modify the application configuration file as it was shown in the sample above
  • In your data access layer project, add a reference to Yap.Data.Client
  • Copy Yap.Data.Client.Sql to the application output folder

History

  • Version 0.0.0.1.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Russian Federation Russian Federation
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralConfiguration Section Issues Pin
MattStark24-Feb-10 11:08
MattStark24-Feb-10 11:08 
GeneralRe: Configuration Section Issues [modified] Pin
6opuc24-Feb-10 18:27
6opuc24-Feb-10 18:27 
GeneralRe: Configuration Section Issues Pin
MattStark25-Feb-10 4:28
MattStark25-Feb-10 4:28 
GeneralRe: Configuration Section Issues Pin
6opuc25-Feb-10 4:30
6opuc25-Feb-10 4:30 
GeneralMySql Pin
mtnz27-Aug-09 16:08
mtnz27-Aug-09 16:08 
GeneralRe: MySql Pin
6opuc29-Aug-09 1:51
6opuc29-Aug-09 1:51 
GeneralRe: MySql Pin
6opuc29-Aug-09 1:59
6opuc29-Aug-09 1:59 
GeneralGood article Pin
Donsw16-Jan-09 6:44
Donsw16-Jan-09 6:44 
GeneralRe: Good article Pin
6opuc16-Jan-09 6:57
6opuc16-Jan-09 6:57 
GeneralMisleading categorization Pin
John Whitmire15-Jan-09 7:47
professionalJohn Whitmire15-Jan-09 7:47 
Not much you can do about it now, but the categorizations of this article (in the top banner) include wrong items. Specifically, VS 2005, C# 1.0, and C# 2.0 should not be included. You use language features that aren't available in those products.

Being personally confined for now to VS 2005/C# 2.0, your article makes me hungrier for VS 2008/C# 3.0. How useful it would be as we are about to embark on a DAL development effort. Sigh | :sigh:
GeneralRe: Misleading categorization Pin
6opuc15-Jan-09 19:28
6opuc15-Jan-09 19:28 
GeneralMy Dal Pin
rcastrogo13-Jan-09 0:18
rcastrogo13-Jan-09 0:18 
GeneralRe: My Dal Pin
6opuc13-Jan-09 0:40
6opuc13-Jan-09 0:40 
GeneralRe: My Dal Pin
rcastrogo13-Jan-09 20:58
rcastrogo13-Jan-09 20:58 
GeneralRe: My Dal Pin
6opuc13-Jan-09 23:17
6opuc13-Jan-09 23:17 
GeneralRe: My Dal Pin
oluckyman16-Jan-09 23:03
oluckyman16-Jan-09 23:03 
GeneralRe: My Dal Pin
6opuc17-Jan-09 0:22
6opuc17-Jan-09 0:22 

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

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