Jump To:

DataAccessLayer

back to top...
The DataAccessLayer class is an abstract, generic class that has a number of protected methods for implementing ADO.NET wrappers for different types of database servers. Currently, implementations exist as sub-classes of the DataAccessLayer class, one for SQL Server called SqlClientDataAccessLayer, one for MySQL called MySqlDataAccessLayer, and one for MS Access called OleDbAccessLayer. You implement one of these sub-classes as part of your data access class, giving you access to these protected methods for handling data processing tasks.

The following examples will be written in terms of the SqlClientDataAccessLayer, but they hold true for the other sub-classes as well.

Implementing a Data Access Layer

back to top...
When creating your data access layer, implement a class that sub-classes the SqlClientDataAccessLayer class. Then, create at least one constructor that forwards the constructor call to the base class constructor.

SqlClientDataAccessLayer(string connectionString)

back to top...
The simplest constructor to implement is the bare connection string constructor. This constructor will create a new SqlConnection object and immediately open a connection to the database, leaving your data access layer in a state ready to query and execute stored procedures on the database. This constructor also takes ownership of the connection, and will manage the life of the connection with the life of the object.

Example:
public class MyDataAccessLayer : SqlClientDataAccessLayer
{
    public MyDataAccessLayer() : base("Data Source=myServerAddress;Initial Catalog=myDatabase;Integrated Security=True")
    {
    }
}

SqlClientDataAccessLayer(SqlConnection connection)

back to top...
If you would prefer to manage your own connection, a second constructor is available. This constructor creates a data access layer around an existing database connection. It does not take ownership of the connection, and does not manage the life of the connection at all. You may use this in conjunction with the SqlClientDataAccessLayer::Connection property to manage multiple SqlSiphon-based data access classes through one database connection.

Example:
public class MyDataAccessLayer : SqlClientDataAccessLayer
{
    public MyDataAccessLayer(SqlClientDataAccessLayer data) : base(data.Connection)
    {
    }
}

void Dispose()

back to top...
At the end of the life of your data access operations, the Dispose method "cleans up" the database connections automatically. If the data access layer has ownership of the database connection, the Dispose method will close the connection, call Dispose() on the connection, and nullify its reference to the connection to ensure that the data access object can't be used for database operations anymore.

Example:
public static void Main(string[] args)
{
    using(MyDataAccessLayer data = new MyDataAccessLayer())
    {
        // operations involving the "data" object
    } // data.Dispose() automatically called;
}

NOTE: do not implement a finalizer (i.e. .NET class destructor) to call the Dispose method. This does not match .NET best practice and may lead to difficult to diagnose errors.

Data Operations

back to top...
Several methods exist to retrieve data and execute queries and stored procedures on the database. Most of them are generic and polymorphic. Most of them take a variable parameter list as it's argument, which means different things depending on the expected return type. Methods that return a result take a type parameter T that follows the Entity Class rules. The methods that take a variable parameter list are processed by the SqlSiphon stack trace analyzer to determine necessary parameters to construct a SqlCommand object and process it.

T Get<T>(params object[] parameters)

List<T> GetList<T>(params object[] parameters)

back to top...
This method will invoke the SqlSiphon stack trace analyzer to figure out the SQL command details from the method that calls it and map the results to the Entity Class type provided by T. There is another method, GetList<T>(), that operates exactly like Get<T>(), except that it returns a System.Collections.Generic.List<T> of the Entity Class type T.

To use this method, call it from a method marked with the MappedMethodAttribute. For example:
[MappedMethod]
[MethodImpl(MethodImplOptions.NoInlining | MethodImplOptions.NoOptimization | MethodImplOptions.PreserveSig | MethodImplOptions.Synchronized)]
public string sp_MyStoredProcedure(string Parameter1)
{
    return this.Get<string>(0, Parameter1);
}

This method call will result in a stored procedure named sp_MyStoredProcedure in the default schema for the current connection being called with the stored procedure parameter @Parameter1 set to the value of the Parameter1 local parameter, and return the first column (as specified with the "0" value) of the first row of the result set as a string. If the field is not of string type, the method will create an InvalidCastException. This procedure might look like:
create procedure sp_MyStoredProcedure
    @Parameter1 nvarchar(max)
as begin
    set nocount on;
    select top 1 UserName
    from Users
    where UserType = 'Admin';
end

void Execute(params object[] parameters)

back to top...
This method will invoke the SqlSiphon stack trace analyzer to figure out the SQL command details from the method that calls it, with no mapping to an Entity Class, for use with stored procedures that do not return a result set (e.g. Insert, Update, and Delete operations).

To use this method, call it from a method marked with the MappedMethodAttribute. For example:
[MappedMethod]
[MethodImpl(MethodImplOptions.NoInlining | MethodImplOptions.NoOptimization | MethodImplOptions.PreserveSig | MethodImplOptions.Synchronized)]
public void sp_DeleteSomething(int Parameter1)
{
    return this.Get<string>(0, Parameter1);
}

This method call will result in a stored procedure named sp_DeleteSomething in the default schema for the current connection being called with the stored procedure parameter @Parameter1 set to the value of the Parameter1 local parameter. This procedure might look like:
create procedure sp_MyStoredProcedure
    @Parameter1 int
as begin
    set nocount on;
    
    delete from Logs
    where LogID = @Parameter1
end

Manual Result Set Data Processing

back to top...
There are occasions where it may not be desirable to map the results sets to an Entity Class type. In these cases, there are two methods for getting direct access to the underlying ADO.NET data types.

DataSet GetDataSet(params object[] parameters)

back to top...
This method invokes the SqlSiphon stack trace analyzer in the same manner as Get<T> and GetList<T>, but instead of mapping the results to an Entity Class type, it returns a System.Data.DataSet object with the data loaded.

SqlDataReader GetReader(params object[] parameters)

back to top...
This method invokes the SqlSiphon stack trace analyzer in the same manner as Get<T> and GetList<T>, but instead of mapping the results to an Entity Class type, it returns a System.Data.SqlClient.SqlDataReader object, ready to read data from the database.

Dynamic Text Queries

back to top...
The MappedMethodAttribute provides a means for executing parameterized text queries. It is recommended to use stored procedures in favor of any type of text query. However, if a dynamic text query (one that is constructed through string concatenation operations, which are susceptible to SQL Injection attacks), then there are two methods available for such queries. These methods do not invoke the SqlSiphon stack trace analyzer, as the entire query is defined as a parameter to the method.

T GetQuery<T>(string query)

void ExecuteQuery(string query)

back to top...
These method executes a dynamic text query on the database. The GetQuery method maps the results to the Entity Class in the same way as the other parameters. It is not necessary to implement the MappedMethodAttribute, or the anti-optimization attribute. It is recommended to mark these method calls with the ObsoleteAttribute to remind to take their use seriously.

Example:
[Obsolete]
public int GetRowCount(string tableName)
{
    return this.Get<int>(0, string.Format("select count(*) from {0}", tableName));
}

Last edited Oct 8, 2012 at 3:17 PM by capn_midnight, version 6

Comments

No comments yet.