Overview

DataAccess is a C# framework based on generics, delegates, anonymous delegates, iterator blocks, events, Enterprise Library, Spring.Net and convention for data access to relational databases. The main purpose for the DataAccess framework is to alleviate the developer from the complexities of data access system code. Out of the box DataAccess provides the basic crud operations.

This framework relies heavily on convention for ease of use. For data access development against legacy database (where convention is not possible) the DataAccess framework provides a Generic Data Access framework that supports everything from ADO.Net to the Data Application Block from Enterprise Library in an easier to use API.

DataAccess is not an object relational mapping tool and does not support complex associations out of the box. Although it is possible to create complex associations by dropping down into the Generic Data Access API provided by DataAccess. Another caveat of DataAccess is that it does not support the notion of mapping object identity to database identity and monitoring the use of stale objects. It is possible for to objects in memory to point to the same row in a database at the same time. Future version of DataAccess will support identity maps and caching for object identity.

DataAccess

DataAccess provides the ability to map a generic entity of type T to a database entity. Out of the box DataAccess provides operations for selecting, inserting, deleting, and updating entities in the database. A DataAccess object is not constrained to any one specific database entity. DataAccess is a generic data access object and can bind to any entity type defined as the generic type T.

Database result sets from select operations are translated to C# types via the RowMapper delegate type. The generic RowMapper API allows DataAccess to be extensible and adapt to any generic entity of type T.

DataAccess also provides the ability of retrieving multiple rows from a database entity in the form of an EntityCollection of the generic type T. EntityCollection supports various operators for restriction, partitioning, concatenation, ordering, and conversion.

Internally DataAccess relies on convention. For example, a C# type’s name and properties are mapped to the name and columns of a database entity. Operations like insert, update and delete rely on convention for the execution of stored procedures that map to the database entities. The convention used by DataAccess makes a natural fit for code generation. Code generation can highly decrease the risk of errors that violate the conventions used by the DataAccess framework.

Configuration

A DataAccess object can be associated to SQL Server or Oracle via configuration. The configuration parameter required by the DataAccess object is the database instance name. A database instance name is a key that maps to a connection string defined in the applications App.config or WebApp.config file.

The database instance name must be defined in a connection String node defined within the applications XML configuration file. For example, the following configuration code defines a connection to the SQL Server host “fortune.800-comm.com,” database “SpecialFinance,” user “ibuser,” and password “mypassword”.

<connectionStrings>
    <add name="Fortune" providerName="System.Data.SqlClient" connectionString="Data
             Source=192.168.1.1; Initial Catalog=MyDatabase;
             User ID=MyUser; Password=MyPassword" />
</connectionStrings>

Once a database instance name is defined in the applications configuration file, a DataAccess object can be instantiated by calling the overloaded constructor that takes the database instance name as a parameter. Internally the DataAccess object will take the specified database instance name and look for a matching configuration in the applications configuration file. If no configuration matching the specified database instance name is found the DataAccess object will throw a DAOException. For more information on DAOException please look at the section for DAOException.

RowMapper

RowMapper is a generic delegate type that can translate a database row from an IDataReader to a generic entity of type T. A RowMapper is required by all Select operations in DataAccess. The Select operations in DataAccess will invoke the RowMapper delegate for each row retrieved from the database. The RowMapper will return an entity of type T for each row retrieved from the database.

For example, the following code displays a RowMapper delegate that point’s to a method that can translate from IDataReader to a TestEntity.

RowMapper<TestEntity> rowMapper = GetTestEntity;

public TestEntity GetTestEntity(IDataReader reader)
{
    TestEntity entity = new TestEntity();
    entity.Id = (long)reader["Id"];
    entity.Name = reader["Name"] as string;
    entity.Description = reader["Description"] as string;
    return entity;
 }

EntityCollection

EntityCollection<T> is a generic collection of type T that contains generic entities of type T. EntityCollection<T> supports various operators for restriction, partitioning, concatenation, ordering, and conversion. Many of the operations in EntityCollection<T> mimic the features in the upcoming LINQ framework of C# 3.0. EntityCollection is the return type for operations that return more than one entity in DataAccess. For example, the SelectAll operation in DataAccess<T> returns an EntityCollection<T>.

Select

The Select operation in DataAccess provides the ability to retrieve a single entity from the database that maps to the specified id parameter. If the database contains more than one entity that maps to the specified id parameter, the Select operation will only yield the first entity found. If no entity is found in the database that matches the specified id parameter, a DAOException will be thrown. The DAOException can be used as an indicator that no record was found in the database matching the specified id parameter. Internally the Select operation will attempt to select all from a table that matches the same name as the generic entity of type T and use the specified id parameter is the column to match on.

The Select operation in DataAccess requires a delegate RowMapper in order to translate the database result set to an entity of the generic type T. For more information on RowMapper see the section on RowMapper.

The following code displays how to get a TestEntity from the database that maps to the id 100.

DataAccess<TestEntity> dataAccess = new DataAccess<TestEntity>(“IB”);
TestEntity result =  dataAccess.Select<TestEntity>(100, rowMapper);

SelectAll

The SelectAll operation in DataAccess provides the ability to retrieve all entities of the generic type T from the database. If the database does not contain any entities of the generic type T then an empty EntityCollection<T> is returned. Internally the SelectAll operation will attempt to select all from a table that matches the same name as the generic entity of type T.

The SelectAll operation in DataAccess requires a delegate RowMapper in order to translate the database result set to an EntityCollection of the generic type T. For more information on RowMapper see the section on RowMapper.

The following code displays how to get all the TestEntity’s from the database.

DataAccess<TestEntity> dataAccess = new DataAccess<TestEntity>(“IB”);
EntityCollection<TestEntity> results =  dataAccess.SelectAll<TestEntity>(rowMapper);

Insert

The Insert operation in DataAccess provides the ability to insert a new row that represents an entity of the generic type T. Internally the Insert operation will expect to find a stored procedure with the name of the generic entity of type T pre-pended with the term “Save”. For example, given the code below the DataAccess object will attempt to invoke the “SaveTestEntity” stored procedure.

The following code displays how to get all the TestEntity’s from the database.

DataAccess<TestEntity> dataAccess = new DataAccess<TestEntity>(“IB”);
TestEntity entity = new TestEntity(“test-entity”, “simple test entity”);
dataAccess.Insert<TestEntity>(entity, rowMapper);

Insert operations is not executed within a transaction.

Delete

The Delete operation in DataAccess provides the ability to delete a database row that maps to the generic entity of type T with the specified id. If no entity is found to match the specified id parameter, the Delete operation will simply return. Internally the Delete operation will expect to find a stored procedure with the name of the generic entity of type T pre-pended with the term “Delete”. For example, given the code below the DataAccess object will attempt to invoke the “DeleteTestEntity” stored procedure.

The following code displays how to delete the database row that matches the specified id for the generic entity of type T.

DataAccess<TestEntity> dataAccess = new DataAccess<TestEntity>(“IB”);
dataAccess.Delete<TestEntity>(100);

Delete operations are executed within a transaction.

Update

The Update operation in DataAccess provides the ability to update an existing row in the database that maps to a generic entity of type T that matches the specified id parameter. If now row in the database matches the specified id parameter, the Update operation will simply return. Internally the Update operation will expect to find a stored procedure with the name of the generic entity of type T pre-pended with the term “Update”. For example, given the code below the DataAccess object will attempt to invoke the “UpdateTestEntity” stored procedure. DataAccess will take each property defined in the generic entity of type T to the parameters required by the stored procedure. Note the stored procedure parameters must match each column in the database entity and each property in the generic entity of type T.

The following code displays how to update the row in the database that matches the specified id for the generic entity of type T.

DataAccess<TestEntity> dataAccess = new DataAccess<TestEntity>(“IB”);
TestEntity result =  dataAccess.Select<TestEntity>(100, rowMapper);

result.Description = “test entity has been update”;
dataAccess.Update(result);

Update operations are executed within a transaction.

GenericDataAccess

DataAccess provides access to its internal Generic Data Access framework via the GenericDataAccess property. The Generic Data Access layer is a thin layer of abstraction over the Data Application Block in Enterprise Library; that provides a set of overloaded crud operations. The lower level operations do not rely on convention and are free to be used in any custom way. For example, the FindAll operation can be used with a query that joins across multiple tables and uses a custom RowMapper that can translate the results of the joined tables into an aggregate entity.

The following code displays how to use the generic data access framework to get a collection of aggregate entities from several entities in the database.

String query = 
    @"select a.name, b.desc from TestEntity a inner join TestEntityReference b, on a.id = b.id";

RowMapper<TestEntity> rowMapper = GetAggregateEntity;

public AggregateEntity GetAggregateEntity(IDataReader reader)
{
    AggregateEntity entity = new AggregateEntity();
    entity.Name = reader["name"] as string;
    entity.Description = reader["desc"] as string;
    return entity;
 }

EntityCollection<AggregateEntity> result = 
dataAccess.FindAll<AggregateEntity>(query, rowMapper);

DAOException

DAOException is the exception type that will be thrown whenever there’s an internal error within the DataAccess object. It is safe to assume that no other exception type will ever be thrown from DataAccess. The root cause of any error encountered by DataAccess will be encapsulated as an inner exception of DAOException. The message property of DAOException contains a brief description about the operation on DataAccess that failed.