DatabaseAccessor

• Download source - 9.3 KB

Introduction

This article introduces my DatabaseAccessor class, which hides several details of using the basic ADO.NET classes.

Background

Using the basic ADO.NET classes (Connections, Commands, DataReaders, DataAdapters) is fairly easy, but also rather repetitive. Plus, many beginners don't learn to use Transactions and Parameters.

When I first started learning ADO.NET, I quickly decided I wanted a layer between my Data Access Layer and the basic classes. This article presents the lighter-weight of two implementations of such a layer I have developed over the years and currently use. The only database engine I currently support with this implementation is SQL Server.

Using the Code

Files included in the ZIP:

• DatabaseAccessor.cs -- Defines the abstract base class for deriving Accessors
• DatabaseException.cs -- Defines a few simple Exceptions
• SqlServerAccessor.cs -- A derived DatabaseAccessor that uses the classes in System.Data.SqlClient
• SqlServerErrorInfo.cs -- This helps determine what type of error occurred
• DbSamp.cs -- Sample program

Connecting to a Database

Creating a Connection to a database is simplified by not requiring the application to know the complete ConnectionString. For example, one of the constructors for SqlServerAccessor requires only the names of the server and the database. Plus, the constructor doesn't only create the Connection, but also a Command, saving the developer from having to do so.

PIEBALD.Database.SqlServerAccessor db = 
    new PIEBALD.Database.SqlServerAccessor ( "localhost" , "Northwind" ) )
PIEBALD.Database.SqlServerAccessor db = 
    new PIEBALD.Database.SqlServerAccessor ( ConnectionString ) )

Using the Connection

DatabaseAccessor provides access to the Connection's Open and Close methods and State.

db.Open() ;
db.Close() ;
bool b = db.IsClosed ;

Setting the Command

Setting the CommandText, Parameters and CommandType can be done with the SetCommand method. CommandType defaults to System.Data.CommandType.Text. The Parameters collection gets populated with any values provided after the CommandText. The Parameters will be named Param0 through Paramn; the SqlServerAccessor's implementation of CreateParameter adds the @.

db.SetCommand ( "SELECT * FROM Employees" ) ;
db.SetCommand ( "SELECT * FROM Employees WHERE EmployeeID=@Param0" , 42 ) ;
db.SetCommand ( "SELECT * FROM Employees WHERE EmployeeID=@Param0" , 
    System.Data.CommandType.Text , 42 ) ;

The CommandTimeout of Command may be set via the DatabaseAccessor's CommandTimeout property.

db.CommandTimeout = 30 ;

The Parameters of Command may be accessed via DatabaseAccessor's Parameters property.

db.Parameters [ "@Param0" ].Value = 42 ;

Executing Commands

DatabaseAccessor supports the three execution methods of the underlying Command, but also checks the state of the Connection so the Connection can be opened and closed automatically, much like DataAdapter's Fill method does. Plus, the CommandText and parameter values may be passed, saving the developer from having to call SetCommand.

ExecuteNonQuery

int i = db.ExecuteNonQuery() ; // If SetCommand has been used
int i = db.ExecuteNonQuery ( 
    "UPDATE Employees SET ReportsTo=42 WHERE ReportsTo!=42" ) ;
int i = db.ExecuteNonQuery ( 
    "UPDATE Employees SET ReportsTo=@Param0 WHERE ReportsTo!=@Param0", 42);

ExecuteReader

System.Data.IDataReader r = db.ExecuteReader() ; 
    //If SetCommand has been used
System.Data.IDataReader r = db.ExecuteReader ( "SELECT * FROM Employees" ) ;
System.Data.IDataReader r = db.ExecuteReader (
    "SELECT * FROM Employees WHERE EmployeeID!=42" ) ;
System.Data.IDataReader r = db.ExecuteReader (
    "SELECT * FROM Employees WHERE EmployeeID!=@Param0" , 42 ) ;

DatabaseAccessor also provides access to DataReader via its DataReader property.

while ( db.DataReader.Read ) { ... }

The preferred way to close DataReader is with DatabaseAccessor's CloseReader method.

db.CloseReader() ;

ExecuteScalar

DatabaseAccessor's ExecuteScalar method is Generic, saving the developer from having to declare an object to receive the value, execute, test for null, and then perform a cast and/or assign some default value. The first parameter is a value to use if the result is null.

int i = db.ExecuteScalar<int> ( -1 ) ; // If SetCommand has been used
int i = db.ExecuteScalar<int> ( -1 , 
    "SELECT ReportsTo FROM Employees WHERE EmployeeID=42" ) ;
int i = db.ExecuteScalar<int> ( -1 , 
    "SELECT ReportsTo FROM Employees WHERE EmployeeID=@Param0" , 42 ) ;

The normal operation of ExecuteScalar can be requested by specifying object as the type and null as the default result.

object o = db.ExecuteScalar<object> ( null ) ; // If SetCommand has been used
object o = db.ExecuteScalar<object> ( null , 
    "SELECT ReportsTo FROM Employees WHERE EmployeeID=42" ) ;
object o = db.ExecuteScalar<object> ( null , 
    "SELECT ReportsTo FROM Employees WHERE EmployeeID=@Param0" , 42 ) ;

ExecuteDataTable

DatabaseAccessor also simplifies the common task of filling a DataTable.

System.Data.DataTable d = db.ExecuteDataTable() ;
    //If SetCommand has been used
System.Data.DataTable d = db.ExecuteDataTable ( 
     "SELECT * FROM Employees" ) ;
System.Data.DataTable d = db.ExecuteDataTable ( 
    "SELECT * FROM Employees WHERE EmployeeID!=42" ) ;
System.Data.DataTable d = db.ExecuteDataTable ( 
    "SELECT * FROM Employees WHERE EmployeeID!=@Param0" , 42" ) ;

Retrieving basic information about the database schema is also built-in.

System.Data.DataTable d = db.ListTables() ;
System.Data.DataTable d = db.ListColumns ( "Employees" ) ;

Transactions

DatabaseAccessor simplifies the somewhat confusing details of using Transactions. The default IsolationLevel is System.Data.IsolationLevel.ReadCommitted.

try
{
    db.BeginTransaction() ;
    ...
    db.CommitTransaction() ;
}
catch ( System.Exception err )
{
    db.RollbackTransaction() ;
    
    throw ( err ) ;
}

DatabaseAccessor also has a TransactionInProgess property and an IsolationLevel property.

Exceptions

DatabaseAccessor wraps any Exception it encounters in a ConnectionException or an OperationFailedException, depending on the context of the error. SqlServerAccessor has some ability to determine what the underlying problem is.

Deriving New DatabaseAccessors

The DatabaseAccessor class is abstract; the following methods must be implemented by derived classes:

• CreateParameter ( string Name , object Value )
• ExecuteDataTable ( System.Data.DataTable DataTable )
• ListTables()

The derived class must also implement at least one constructor that instantiates a Connection and passes it to the base constructor. The derived class may also override other methods as necessary (SqlServerAccessor implements its own Parameters property and WrapDataExecption method). SqlServerAccessor may serve as a template for deriving other DatabaseAccessors.

Writing Data Access Layers

There should be at least one more layer of abstraction between the database and the application: the Data Access Layer. A Data Access Layer frees the application from having to know anything about the actual database implementation in use. The application can then use conceptual terms rather than SQL statements, for example "GetEmployeeList" rather than "SELECT * FROM Employees". Such a Data Access Layer implementation may use DatabaseAccessor by either deriving from it:

public class NorthwindAccessor : PIEBALD.Database.SqlServerAccessor
{
    public NorthwindAccessor() : base ( "localhost" , "Northwind" ) {}
    ...
}

Or by declaring a field to hold a reference to DatabaseAccessor:

public class NorthwindAccessor
{
    private PIEBALD.Database.SqlServerAccessor db = 
        new PIEBALD.Database.SqlServerAccessor ( "localhost" , "Northwind" );
    ...
}

The main difference between these two techniques is that the former allows the application the ability to execute ad hoc SQL statements, which is convenient, but may violate the concept of a Data Access Layer. The latter does not allow such access to the database and is therefore preferable. Some minor variations of the second technique are:

public class NorthwindAccessor
{
    private PIEBALD.Database.SqlServerAccessor db ;
    
    public NorthwindAccessor()
    {
        this.db = new PIEBALD.Database.SqlServerAccessor ( "localhost" , 
            "Northwind" ) ;
        return ;
    }
    ...
}
public class NorthwindAccessor
{
    private PIEBALD.Database.SqlServerAccesor db ;

    private NorthwindAccessor()
    {
        this.db = new PIEBALD.Database.SqlServerAccessor ( "localhost" , 
            "Northwind" ) ;
        return ;
    }
    public static NorthwindAccessor
    Connect()
    {
        return ( new NorthwindAccessor() ) ;
    }
    ...
}

Then simply add methods to the class to implement the necessary database requests. These methods are provided for illustration purposes only; I make no claims that these are "good" implementations. Please note that I also added an implementation of IDisposable and sealed the class.

public sealed class NorthwindAccessor : System.IDisposable
{
    private PIEBALD.Database.SqlServerAccessor db ;

    private NorthwindAccessor()
    {
        this.db = new PIEBALD.Database.SqlServerAccessor 
        ( 
            "localhost" 
        , 
            "Northwind" 
        ) ;
        return ;
    }
    public static NorthwindAccessor
    Connect()
    {
        return ( new NorthwindAccessor() ) ;
    }
    public void Dispose()
    {
        db.Dispose() ;
        return ;
    }
    public System.Data.DataTable GetEmployeeList()
    {
        return ( db.ExecuteDataTable 
        ( 
            @"
            SELECT * 
            FROM Employees
            " 
        ) ) ;
    }    
    public System.Data.DataRow GetEmployee ( int EmployeeID )
    {
        return ( db.ExecuteDataTable 
        ( 
            @"
            SELECT * 
            FROM Employees 
            WHERE EmployeeID=@Param0
            " 
        ,
            EmployeeID 
        ).Rows [ 0 ] ) ;
    }    
    public System.DateTime GetEmployeeHireDate ( int EmployeeID )
    {
        return ( db.ExecuteScalar<System.DateTime>
        ( 
            System.DateTime.MaxValue 
        , 
            @"
            SELECT HireDate 
            FROM Employees 
            WHERE EmployeeID=@Param0
            " 
        , 
            EmployeeID 
        ) ) ;
    }
}

This example is from the included DbSamp.cs file.

History

• 2007-08-07 First submitted
• 2007-08-21 Added test for System.DBNull.Value in ExecuteScalar