Click here to Skip to main content
Click here to Skip to main content

DatabaseAccessor

, 23 Aug 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
A simple base class that encapsulates basic database access functionality

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

License

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

Share

About the Author

PIEBALDconsult
Software Developer (Senior)
United States United States
BSCS 1992 Wentworth Institute of Technology
 
Originally from the Boston (MA) area. Lived in SoCal for a while. Now in the Phoenix (AZ) area.
 
OpenVMS enthusiast, ISO 8601 evangelist, photographer, opinionated SOB, acknowledged contrarian
 
---------------
 
"If you need help knowing what to think, let me know and I'll tell you." -- Jeffrey Snover [MSFT]
 
"Typing is no substitute for thinking." -- R.W. Hamming
 
"I find it appalling that you can become a programmer with less training than it takes to become a plumber." -- Bjarne Stroustrup
 
ZagNut’s Law: Arrogance is inversely proportional to ability.
 
"Well blow me sideways with a plastic marionette. I've just learned something new - and if I could award you a 100 for that post I would. Way to go you keyboard lovegod you." -- Pete O'Hanlon
 
"linq'ish" sounds like "inept" in German -- Andreas Gieriet
 
"Things would be different if I ran the zoo." -- Dr. Seuss
 
"Wrong is evil, and it must be defeated." – Jeff Ello
 
"A good designer must rely on experience, on precise, logical thinking, and on pedantic exactness." -- Nigel Shaw
 
“It’s always easier to do it the hard way.” -- Blackhart

“If Unix wasn’t so bad that you can’t give it away, Bill Gates would never have succeeded in selling Windows.” -- Blackhart

"Omit needless local variables." -- Strunk... had he taught programming
 

 
"We learn more from our mistakes than we do from getting it right the first time."
 
My first rule of debugging: "If you get a different error message, you're making progress."
 
My golden rule of database management: "Do not unto others' databases as you would not have done unto yours."
 
My general rule of software development: "Design should be top-down, but implementation should be bottom-up."

Comments and Discussions

 
GeneralMy vote of 5 PinmemberDhavalkumar P Patel17-Sep-11 1:16 
GeneralMy vote of 5 PinmemberRoger Wright7-Jul-11 21:12 
GeneralImage field db type problem Pinmemberkdryksk29-Jan-09 10:07 
GeneralRe: Image field db type problem [modified] PinmemberPIEBALDconsult29-Jan-09 10:10 
GeneralRe: Image field db type problem Pinmemberkdryksk29-Jan-09 21:38 
GeneralRe: Image field db type problem PinmemberPIEBALDconsult30-Jan-09 5:53 
GeneralSaved me a lot of time PinmemberMember 471270528-Feb-08 4:46 
GeneralRe: Saved me a lot of time PinmemberPIEBALDconsult28-Feb-08 13:08 
GeneralExecuteScalar Conversion Error PinmemberAriMatSoft13-Dec-07 15:16 
QuestionDatabaseAccessor with multiple parameters? Pinmembervikrun30-Oct-07 6:39 
AnswerRe: DatabaseAccessor with multiple parameters? PinmemberPIEBALDconsult30-Oct-07 16:02 
GeneralRe: DatabaseAccessor with multiple parameters? Pinmembervikrun30-Oct-07 22:42 
GeneralRe: DatabaseAccessor with multiple parameters? PinmemberPIEBALDconsult31-Oct-07 9:35 
GeneralRe: DatabaseAccessor with multiple parameters? Pinmembervikrun1-Nov-07 0:22 
GeneralRe: DatabaseAccessor with multiple parameters? PinmemberPIEBALDconsult1-Nov-07 7:10 
GeneralHey looks familier PinmemberTim Schwallie28-Aug-07 18:42 
GeneralRe: Hey looks familier Pinmemberwobjla30-Aug-07 8:14 
GeneralRe: Hey looks familier PinmemberPIEBALDconsult30-Aug-07 14:27 
GeneralRe: Hey looks familier PinmemberPIEBALDconsult30-Aug-07 14:21 
GeneralRe: Hey looks familier PinmemberTim Schwallie30-Aug-07 19:12 
GeneralRe: Hey looks familier Pinmemberwobjla31-Aug-07 9:56 
GeneralRe: Hey looks familier PinmemberPIEBALDconsult31-Aug-07 14:23 
GeneralRe: Hey looks familier PinmemberPIEBALDconsult31-Aug-07 14:27 
GeneralVery Nice PinmemberPaul Conrad26-Aug-07 10:40 
GeneralRe: Very Nice PinmemberPIEBALDconsult26-Aug-07 14:43 
Generalhttp://www.bltoolkit.com/ PinmemberMarc Leger23-Aug-07 17:22 
GeneralRe: http://www.bltoolkit.com/ PinmemberPIEBALDconsult23-Aug-07 18:05 
GeneralFine Job PinmemberOneSoftware21-Aug-07 21:18 
GeneralRe: Fine Job PinmemberPIEBALDconsult22-Aug-07 5:28 
Generalit's a good approach PinmemberAlan Zhang21-Aug-07 17:11 
GeneralRe: it's a good approach PinmemberPIEBALDconsult21-Aug-07 18:00 
Generalsee also -DAAB & Mono.Data PinmemberJay R. Wren21-Aug-07 9:43 
GeneralRe: see also -DAAB & Mono.Data PinmemberPIEBALDconsult21-Aug-07 16:52 
GeneralRe: see also -DAAB & Mono.Data PinmemberJay R. Wren22-Aug-07 2:52 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 23 Aug 2007
Article Copyright 2007 by PIEBALDconsult
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid