% title # Introduction to abstract data accessors %> <% order # 1 %>
Before we start talking about the data accessors, let us create a few examples of typical data accessor methods.
The following table contains three stored procedures and three data access methods implementing the stored procedure calls.
Stored procedure | Data access method |
---|---|
The first stored procedure takes filter and page parameters and returns recordset from the Person table. | |
<% sql # CREATE Procedure GetPersonListByName( @firstName varchar(50), @lastName varchar(50), @pageNumber int, @pageSize int) AS -- stored procedure implementation -- %> | <% cs #
public List |
Second example will return single Person record by id. | |
<% sql # CREATE Procedure GetPersonByID(@id int) AS -- stored procedure implementation -- %> | <% cs # public Person GetPersonByID(int id) { // method implementation. } %> |
The last example will delete a record from the database by id. | |
<% sql # CREATE Procedure DeletePersonByID(@id int) AS -- stored procedure implementation -- %> | <% cs # public void DeletePersonByID(int id) { // method implementation. } %> |
Now lets see what we can say if we compare the stored procedure and C# method signatures.
As demonstrated above method definition contains all the information we need to implement the method body. Actually, by defining the method signatures, we completed the most intelligent part of data accessor development. The rest of work is definitely a monkey's job. Honestly, I got bored of being just a coding machine writing the same data access code over and over again, especially understanding that this process can be automated.
This introduction shows how to avoid the implementation step of data access development and how to reduce this routine process to the method declaration.
Unfortunately, mainstream .NET languages still do not have a compile-time transformation system like some functional or hybrid languages do. All we have today is pre-compile- and run-time code generation.
This introduction concentrates on run-time code generation and its support by Business Logic Toolkit for .NET.
Let us step back and bring the methods from the previous examples together in one class. Ideally, this data accessor class could look like the following:
<% cs # using System; using System.Collections.Generic; public class PersonAccessor { public ListThe bad news about this sample is that we cannot use such syntax as the compiler expects the method's body implementation.
The good news is we can use abstract classes and methods that give us quite similar, compilable source code.
<% cs # using System; using System.Collections.Generic; public /*[a]*/abstract/*[/a]*/ class PersonAccessor { public /*[a]*/abstract/*[/a]*/ ListThis code is 100% valid and our next step is to make it workable.
Business Logic Toolkit provides the DataAccessor class, which is used as a base class to develop data accessor classes. If we add DataAccessor to our previous example, it will look like the following:
<% cs # using System; using System.Collections.Generic; public abstract class PersonAccessor : /*[a]*/DataAccessor/*[/a]*/ { public abstract ListThat's it! Now this class is complete and fully functional. The code below shows how to use it:
<% cs # using System; using System.Collections.Generic; using BLToolkit.Reflection; namespace DataAccess { class Program { static void Main(string[] args) { PersonAccessor pa = /*[a]*/TypeAccessor/*[/a]*/The only magic here is the TypeAccessor.CreateInstance method. First of all this method creates a new class inherited from the PersonAccessor class and then generates abstract method bodies depending on each method declaration. If we wrote those methods manually, we could get something like the following:
<% cs # using System; using System.Collections.Generic; using BLToolkit.Data; namespace Example.BLToolkitExtension { public sealed class PersonAccessor : Example.PersonAccessor { public override List(The DbManager class is another BLToolkit class used for 'low-level' database access).
Every part of the method declaration is important. Method's return value specifies one of the Execute methods in the following way:
Return Type | Execute Method |
---|---|
IDataReader interface | ExecuteReader |
Subclass of DataSet | ExecuteDataSet |
Subclass of DataTable | ExecuteDataTable |
Class implementing the IList interface | ExecuteList or ExecuteScalarList |
Class implementing the IDictionary interface | ExecuteDictionary or ExecuteScalarDictionary |
void | ExecuteNonQuery |
string, byte[] or value type | ExecuteScalar |
In any other case | ExecuteObject |
The PersonAccessor class above is a very simple example and, of course, it seems too ideal to be real. In real life, we need more flexibility and more control over the generated code. BLToolkit contains a bunch of attributes to control DataAccessor generation in addition to DataAccessor virtual members.
By default, this method creates a new instance of DbManager that uses default database configuration. You can change this behavior by overriding this method. For example:
<% cs # public abstract class OracleDataAccessor : DataAccessor { protected override DbManager CreateDbManager() { return new DbManager("Oracle", "Production"); } } %>This code will use the Oracle data provider and Production configuration.
As I mentioned, the method name explicitly defines the so-called action name. The final stored procedure name is created by the GetDefaultSpName method. The default implementation uses the following naming convention:
You can easily change this behavior. For example, for the naming convention "p_Person_GetAll", the method implementation can be the following:
<% cs # public abstract class MyBaseDataAccessorBy default, the table name is the associated object type name (Person in our examples). There are two ways to associate an object type with an accessor. By providing generic parameter:
<% cs # public abstract class PersonAccessor : DataAccessorAnd by the ObjectType attribute:
<% cs # [ObjectType(typeof(Person))] public abstract class PersonAccessor : DataAccessor { } %>If you want to have different table and type names in your application, you may override the GetTableName method:
<% cs # public abstract class OracleDataAccessorAlso, you can change the table name for a particular object type by decorating this object with the TableNameAttribute attribute:
<% cs # [TableName("PERSON")] public class Person { public int ID; public string FirstName; public string LastName; } %>This attribute allows changing the action name.
<% cs # public abstract class PersonAccessor : DataAccessorThis attribute associates the action name with a stored procedure name:
<% cs# [ActionSprocName("Insert", Ýsp_Person_Insert")] public abstract class PersonAccessor : DataAccessorThis attribute can be useful when you need to reassign a stored procedure name for a method defined in your base class.
The regular way to assign deferent from default sproc name for a method is the SprocName attribute.
<% cs # public abstract class PersonAccessor : DataAccessorBy default, the DataAccessor generator uses method's return value to determine which Execute method should be used to perform the current operation. The DestinationAttribute indicates that target object is a parameter decorated with this attribute:
<% cs # public abstract class PersonAccessor : DataAccessorDataAccessor generator can map provided business object to stored procedure parameters. Direction attributes allow controlling this process more precisely.
<% cs # public abstract class PersonAccessor : DataAccessorIn addition, BLToolkit provides two more direction attributes: Direction.InputOutputAttribute and Direction.ReturnValueAttribute.
By default, BLToolkit expects method parameter names to match stored procedure parameter names. The sequential order of parameters is not important in this case. This attribute enforces BLToolkit to retrieve parameter information from the sproc and to assign method parameters in the order they go. Parameter names are ignored.
This attribute indicates that the specified parameter is used to construct the stored procedure name or SQL statement:
<% cs # public abstract class PersonAccessor : DataAccessorIf you want your method to return a dictionary, you will have to specify fields to build the dictionary key. The Index attribute allows you to do that:
<% cs # public abstract class PersonAccessor : DataAccessorNote: if your key has more than one field, the type of this key should be CompoundValue.
If the field name starts from '@' symbol, BLToolkit reads the field value from data source, otherwise from an object property/field.
By default, the method parameter name should match the stored procedure parameter name. This attribute specifies the sproc parameter name explicitly.
<% cs # public abstract class PersonAccessor : DataAccessorIf your method returns a dictionary of scalar values, you will have to specify the name or index of the field used to populate the scalar list. The ScalarFieldName attribute allows you to do that:
<% cs # public abstract class PersonAccessor : DataAccessorIf a method returns a scalar value, this attribute can be used to specify how database returns this value. The ScalarSource attribute take a parameter of the ScalarSourceType type:
ScalarSourceType | Description |
---|---|
DataReader | Calls the DbManager.ExecuteReader method, and then calls IDataReader.GetValue method to read the value. |
OutputParameter | Calls the DbManager.ExecuteNonQuery method, and then reads value from the IDbDataParameter.Value property. |
ReturnValue | Calls the DbManager.ExecuteNonQuery method, and then reads return value from command parameter collection. |
AffectedRows | Calls the DbManager.ExecuteNonQuery method, and then returns its return value. |
This attribute allows specifying SQL statement.
<% cs # public abstract class PersonAccessor : DataAccessorI hope this brief tutorial demonstrates one of the simplest, quickest and most low-maintenance ways to develop your data access layer. In addition, you will get one more benefit, which is incredible object mapping performance. But that is a topic we will discuss later.