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

Database Connector

, 28 Jan 2009
Rate this:
Please Sign up or sign in to vote.
A system of plug-ins that provide very simple database access

Introduction

This is not the first database access article to be published here. It's not even the first by me. I do, however, hope it fills a niche that none of the other articles fill.

Background

A small utility I'm working on (to be published later) needs access to a database. It needs only to connect, execute one query, and disconnect. Certainly that's a simple task and there are many solutions. However, I don't want to hard-code or otherwise limit the choice of databases that may be used with the utility. Nor do I want to require the user to recompile the utility to add support for a new database. Obviously, plug-ins are the answer to this type of situation.

Neither of my other database access libraries will work as a plug-in, so I had to create a new suite of classes. This article presents those classes and how they interact.

IExecuteSql.cs

Plug-ins generally use an interface. Each plug-in class implements the interface and the consumer class need know nothing else about the plug-in.

Well, that's almost true; as implemented in .NET, an interface can't specify constructors, so just knowing the interface won't help instantiate the plug-in. That's a sizable limitation, and I know of no suitable work-around.

I'll discuss instantiation more later. Once the plug-in is instantiated, the only method my plug-ins require is ExecuteSql, but I also added CommandTimeout and IsolationLevel for convenience. The definition of the IExecuteSql interface is:

public interface IExecuteSql : System.IDisposable
{
    System.Collections.Generic.List<System.Data.DataTable>
    ExecuteSql
    (
        string          Statements
    ,
        params object[] Parameters
    ) ;

    int CommandTimeout { get ; set ; }

    System.Data.IsolationLevel IsolationLevel { get ; set ; }
}

I chose to require IDisposable because database connections implement it.

Note that the method returns a List of DataTables, this is because Statements may actually be a semi-colon-separated list of SQL statements.

GenericDatabaseConnector.cs

In the pursuit of a very simple system of classes, I eventually chose to use a generic class to provide the basic functionality:

public class GenericDatabaseConnector
<
    ConnectionType
> : PIEBALD.Data.IExecuteSql
where ConnectionType : System.Data.IDbConnection , new()
{
    private readonly ConnectionType             connection      ;
    private          string                     parameterprefix = "" ;
    private          int                        timeout         = -1 ;
    private          System.Data.IsolationLevel isolation =
        System.Data.IsolationLevel.Unspecified ;

<<Discussed below>>
}

Constructor

The class provides one constructor, it simply instantiates the connection and sets the connection string:

public GenericDatabaseConnector
(
    string ConnectionString
)
{
    this.connection = new ConnectionType() ;

    this.connection.ConnectionString = ConnectionString ;

    return ;
}

ParameterPrefix

The value of ParameterPrefix is used when creating parameter names. It's the developer's responsibility to set the proper value. I'll discuss the creation of parameter names later.

public virtual string
ParameterPrefix
{
    get
    {
        lock ( this.connection )
        {
            return ( this.parameterprefix ) ;
        }
    }

    set
    {
        lock ( this.connection )
        {
            if ( value == null )
            {
                this.parameterprefix = "" ;
            }
            else
            {
                this.parameterprefix = value.Trim() ;
            }
        }

        return ;
    }
}

CommandTimeout

Allows the user to specify a time limit for executing SQL statements. Not all implementations of IDbCommand support this; the ones that do, provide a default. If the IDbCommand you are using supports altering the CommandTimeout you may specify a value for it. The default value of -1 (or any negative value) will suppress altering the IDbCommand's value.

public virtual int
CommandTimeout
{
    get
    {
        lock ( this.connection )
        {
            return ( this.timeout ) ;
        }
    }

    set
    {
        lock ( this.connection )
        {
            this.timeout = value ;
        }

        return ;
    }
}

IsolationLevel

Allows the user to specify that SQL statements executed within a single call to ExecuteSql should be executed within a transaction with the specified IsolationLevel. The default value of Unspecified results in non-transacted execution.

public virtual System.Data.IsolationLevel
IsolationLevel
{
    get
    {
        lock ( this.connection )
        {
            return ( this.isolation ) ;
        }
    }

    set
    {
        lock ( this.connection )
        {
            this.isolation = value ;
        }

        return ;
    }
}

Dispose

Dispose simply calls the connection's Dispose:

public virtual void
Dispose
(
)
{
    lock ( this.connection )
    {
        this.connection.Dispose() ;
    }

    return ;
}

ExecuteSql

ExecuteSql is the primary method of the class. It's somewhat lengthy, so I'll present it in segments.

The outermost part locks and opens the connection, creates a command, and closes the command when it's done. And, of course, it handles returning the List of DataTables.

public virtual System.Collections.Generic.List<system.data.datatable />
ExecuteSql
(
    string          Statements
,
    params object[] Parameters
)
{
    System.Collections.Generic.List<system.data.datatable /> result =
        new System.Collections.Generic.List<system.data.datatable />() ;

    lock ( this.connection )
    {
        this.connection.Open() ;

        try
        {
            using
            (
                System.Data.IDbCommand cmd
            =
                this.connection.CreateCommand()
            )
            {
                if ( this.timeout >= 0 )
                {
                    cmd.CommandTimeout = this.timeout ;
                }

                if ( this.isolation != System.Data.IsolationLevel.Unspecified )
                {
                    cmd.Transaction = 
			this.connection.BeginTransaction ( this.isolation ) ;
                }

<<Discussed below>>

                if ( cmd.Transaction != null )
                {
                    cmd.Transaction.Commit() ;
                }
            }
        }
        finally
        {
            this.connection.Close() ;
        }
    }

    return ( result ) ;
}

The next part instantiates parameters for any parameter values that were provided, declares local variables that are used in the next section, splits the SQL statements (see below), and then enumerates those SQL statements:

if ( Parameters != null )
{
    for ( int par = 0 ; par < Parameters.Length ; par++ )
    {
        System.Data.IDbDataParameter param = new ParameterType() ;

        param.ParameterName = string.Format
        (
            "{0}Param{1}"
        ,
            this.ParameterPrefix
        ,
            par.ToString()
        ) ;

        param.Value =
            Parameters [ par ] == null ?
            System.DBNull.Value :
            Parameters [ par ] ;

        cmd.Parameters.Add ( param ) ;
    }
}

int                 table = -1 ;
System.Data.DataRow row   ;

foreach
(
    string sql
in
    PIEBALD.Lib.LibSql.SplitSqlStatements ( Statements )
)
{

<<Discussed below>>

}

Note how the value of the ParameterPrefix property is used when naming the parameters. By default, the parameter names are Param0 through Paramn, but specialized implementations may change that by specifying a prefix, such as "@".

The next part creates a DataTable to hold the results of the next SQL statement, sets the commandtext to the current SQL statement, and calls ExecuteReader.

If the ExecuteReader throws an Exception, it will be caught and the DataTable will contain information about the Exception. If the ExecuteReader had succeeded and an Exception was thrown by the next section, then any data will be cleared before putting the Exception information into the DataTable. The catch also has a break to terminate the processing of statements.

result.Add ( new System.Data.DataTable ( string.Format
(
    "Result {0}"
,
    ++table
) ) ) ;

cmd.CommandText = sql ;

try
{
    using
    (
        System.Data.IDataReader rdr
    =
        cmd.ExecuteReader()
    )
    {

<<Discussed below>>

    }
}
catch ( System.Exception err )
{
    if ( cmd.Transaction != null )
    {
        cmd.Transaction.Rollback() ;
    }

    result [ table ].Rows.Clear() ;
    result [ table ].Columns.Clear() ;

    result [ table ].Columns.Add
    (
        "Message"
    ,
        typeof(string)
    ) ;

    row = result [ table ].NewRow() ;

    row [ 0 ] = sql ;

    result [ table ].Rows.Add ( row ) ;

    while ( err != null )
    {
        row = result [ table ].NewRow() ;

        row [ 0 ] = err.Message ;

        result [ table ].Rows.Add ( row ) ;

        err = err.InnerException ;
    }

    break ;
}

The innermost part is what populates a DataTable with the results of a successful execution. If the DataReader has fields (columns), then I create Columns in the DataTable and add rows for the data. If the DataReader does not have fields (as with DML and DDL statements), then I simply report the value of RecordsAffected.

if ( rdr.FieldCount > 0 )
{
    for ( int col = 0 ; col < rdr.FieldCount ; col++ )
    {
        result [ table ].Columns.Add
        (
            rdr.GetName ( col )
        ,
            rdr.GetFieldType ( col )
        ) ;
    }

    while ( rdr.Read() )
    {
        row = result [ table ].NewRow() ;

        for ( int col = 0 ; col < rdr.FieldCount ; col++ )
        {
            row [ col ] = rdr [ col ] ;
        }

        result [ table ].Rows.Add ( row ) ;
    }

    rdr.Close() ;
}
else
{
    rdr.Close() ;

    result [ table ].Columns.Add
    (
        "RecordsAffected"
    ,
        typeof(int)
    ) ;

    row = result [ table ].NewRow() ;

    row [ 0 ] = rdr.RecordsAffected ;

    result [ table ].Rows.Add ( row ) ;
}

Specialized Implementations

Using GenericDatabaseConnector as a base, creating specialized connectors is ridiculously easy. All you need do is derive from GenericDatabaseConnector providing the IDbConnection class to use and add a constructor.

Because the only constructor GenericDatabaseConnector has requires a string parameter, any derived class must have a constructor which calls that base constructor. The simplest way to do that, of course, is to provide the derived class with a constructor that takes a string parameter. This is the closest I've come to having the constructor in the interface.

The following files contain connectors for the three primary ADO.NET providers. An application can use these connectors directly, they don't have to be used as plug-ins.

PIEBALD.Data.OdbcDatabaseConnector.cs

public class OdbcDatabaseConnector : PIEBALD.Data.GenericDatabaseConnector
<
    System.Data.Odbc.OdbcConnection
>
{
    public OdbcDatabaseConnector
    (
        string ConnectionString
    )
    : base
    (
        ConnectionString
    )
    {
        this.ParameterPrefix = "@" ;

        return ;
    }
}

PIEBALD.Data.OleDbDatabaseConnector.cs

public class OleDbDatabaseConnector : PIEBALD.Data.GenericDatabaseConnector
<
    System.Data.OleDb.OleDbConnection
>
{
    public OleDbDatabaseConnector
    (
        string ConnectionString
    )
    : base
    (
        ConnectionString
    )
    {
        this.ParameterPrefix = "@" ;

        return ;
    }
}

PIEBALD.Data.SqlServerDatabaseConnector.cs

public class SqlServerDatabaseConnector : PIEBALD.Data.GenericDatabaseConnector
<
    System.Data.SqlClient.SqlConnection
>
{
    public SqlServerDatabaseConnector
    (
        string ConnectionString
    )
    : base
    (
        ConnectionString
    )
    {
        this.ParameterPrefix = "@" ;

        return ;
    }
}

LibSql.SplitSqlStatements.cs

BONUS!! Here, at no additional cost to you, is a method that will split a semi-colon-separated list of SQL statements!

A Regular Expression is used; I would appreciate any comments on improvements I could make to it. Semi-colons within string literals will not cause a split. There is no special handling of comments. Substrings will be trimmed. Empty substrings will not be returned.

private static readonly System.Text.RegularExpressions.Regex splitter =
    new System.Text.RegularExpressions.Regex
    (
        "('[^']*'|\"[^\"]*\"|[^;])*"
    ,
        System.Text.RegularExpressions.RegexOptions.Compiled
    ) ;

public static System.Collections.Generic.List<string>
SplitSqlStatements
(
    string Statements
)
{
    if ( Statements == null )
    {
        throw ( new System.ArgumentNullException
        (
            "Statements"
        ,
            "Statements must not be null"
        ) ) ;
    }

    System.Collections.Generic.List<string> result =
        new System.Collections.Generic.List<string>() ;

    foreach
    (
        System.Text.RegularExpressions.Match mat
    in
        splitter.Matches ( Statements )
    )
    {
        string temp = mat.Value.Trim() ;

        if ( temp.Length > 0 )
        {
            result.Add ( temp ) ;
        }
    }

    return ( result ) ;
}

DatabaseConnector.cs

This is a static class, it contains only the following method.

Connect is what handles loading the plug-in assembly, getting the plug-in type, and returning an instance of the type. Some validation is performed along the way. It's this method that requires that the plug-in have a constructor that takes the connection string as a parameter.

Due to length, I'll document the Connect method in sections.

Connect requires the name of the file to load and the connection string to use. The file must be named the same as the plug-in class, this may make for somewhat unwieldy filenames (as with the above implementations), but otherwise the user would need to provide both names; I prefer this solution, at least it enforces my one-plug-in-per-file rule.

public static IExecuteSql
Connect
(
    string Filename
,
    string ConnectionString
)
{
    IExecuteSql result = null ;

    string name ;

    System.Reflection.Assembly assm ;

    try
    {
        name = System.IO.Path.GetFileNameWithoutExtension ( Filename ) ;

        /*\
        |*| This is the common way to load an assembly:
        |*| assm = System.Reflection.Assembly.LoadFrom ( Filename ) ;
        |*|
        |*| The following is my take on a technique suggested by Sacha Barber:
        \*/

        assm = System.AppDomain.CreateDomain ( name ).
            Load ( System.IO.File.ReadAllBytes ( Filename ) ) ;
    }
    catch ( System.Exception err )
    {
        throw ( new System.InvalidOperationException
        (
            string.Format
            (
                "Could not load an assembly from file {0}"
            ,
                Filename
            )
        ,
            err
        ) ) ;
    }

<<Discussed below>>

    return ( result ) ;
}

Once the assembly is loaded, the next section attempts to get and validate the type:

System.Type type = assm.GetType ( name ) ;

if ( type == null )
{
    throw ( new System.InvalidOperationException
    (
        string.Format
        (
            "The assembly in file {0} does not contain a public class named {1}"
        ,
            Filename
        ,
            name
        )
    ) ) ;
}

if ( !typeof(PIEBALD.Data.IExecuteSql).IsAssignableFrom ( type ) )
{
    throw ( new System.InvalidOperationException
    (
        string.Format
        (
            "Type {0} in file {1} does not implement PIEBALD.Data.IExecuteSql"
        ,
            type.Name
        ,
            Filename
        )
    ) ) ;
}

And finally, attempt to get and invoke the required constructor:

System.Reflection.ConstructorInfo cons = type.GetConstructor
(
    new System.Type[] { typeof(string) }
) ;

if ( cons == null )
{
    string.Format
    (
        "Type {0} in file {1} does not have a constructor that takes a string"
    ,
        type.Name
    ,
        Filename
    )
}

try
{
    result = (IExecuteSql) cons.Invoke
    (
        new string[] { ConnectionString }
    ) ;
}
catch ( System.Exception err )
{
    throw ( new System.InvalidOperationException
    (
        string.Format
        (
            "Unable to instantiate a {0} with connection string {1}"
        ,
            type.Name
        ,
            ConnectionString
        )
    ,
        err
    ) ) ;
}

DatabaseConnectorTest.cs

The zip file also includes a rather simple test/demo console application.

The main thing I want to point out is how simple it is to connect to the database of choice and execute some SQL. The demo prints out the results, but doesn't do any fancy formatting.

using
(
    PIEBALD.Data.IExecuteSql con
=
    PIEBALD.Data.DatabaseConnector.Connect
    (
        args [ 0 ]
    ,
        args [ 1 ]
    )
)
{
    con.IsolationLevel = System.Data.IsolationLevel.ReadCommitted ;

    for ( int i = 2 ; i < args.Length ; i++ )
    {
        foreach
        (
            System.Data.DataTable dt
        in
            con.ExecuteSql ( args [ i ] )
        )
        {
            System.Console.WriteLine() ;
            System.Console.WriteLine ( dt.TableName ) ;
            System.Console.WriteLine() ;

            foreach ( System.Data.DataColumn col in dt.Columns )
            {
                System.Console.Write ( " {0}" , col.ColumnName ) ;
            }

            System.Console.WriteLine() ;

            foreach ( System.Data.DataRow row in dt.Rows )
            {
                for ( int col = 0 ; col < dt.Columns.Count ; col++ )
                {
                    System.Console.Write ( " {0}" , row [ col ].ToString() ) ;
                }

                System.Console.WriteLine() ;
            }

            System.Console.WriteLine() ;
        }
    }
}

If your application doesn't need to use the connection multiple times, you can omit the using statement:

foreach
(
    System.Data.DataTable dt
in
    PIEBALD.Data.DatabaseConnector.Connect
    (
        args [ 0 ]
    ,
        args [ 1 ]
    ).ExecuteSql
    (
        args [ 2 ]
    )
)
{

...

}

Using the Code

The zip file contains the eight C# files described above, plus a bat file and an Access (MDB) file.

build.bat

I use build.bat for testing the classes. How you build them for your own projects is up to you; just remember that if you use the DatabaseConnector.Connect method, each connector will need to be in its own assembly.

@rem Compile these four files to form DatabaseConnector.dll
csc /t:library DatabaseConnector.cs GenericDatabaseConnector.cs 
		IExecuteSql.cs LibSql.SplitSqlStatements.cs

@rem Compile the test app with a reference to DatabaseConnector.dll
@rem Note that it does not need references to the following dlls
csc DatabaseConnectorTest.cs /r:DatabaseConnector.dll

@rem Compile each of these into its own dll with a reference to DatabaseConnector.dll
csc /t:library PIEBALD.Data.OdbcDatabaseConnector.cs /r:DatabaseConnector.dll
csc /t:library PIEBALD.Data.OleDbDatabaseConnector.cs /r:DatabaseConnector.dll
csc /t:library PIEBALD.Data.SqlServerDatabaseConnector.cs /r:DatabaseConnector.dll

build.bat also tests the classes against the supplied MDB file (you have the Jet Engine, right?), but the lines are so long I won't describe them here. A regular application probably won't take the required values as command-line parameters anyway.

Conclusion

If there's an easier way to connect to a database engine that isn't known at compile-time and execute some SQL, I haven't found it. As I wrote this article, I realized that two of my existing applications that currently rely on my heavy-weight database access library could easily be converted to use this one instead.

History

  • 2009-01-26 First submitted

License

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

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
 
---------------
 
"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 1 Pinmemberricrodrigues15-Jun-10 23:12 
GeneralMy vote of 2 Pinmemberbb_snowman1-Jul-09 22:56 
GeneralMy vote of 2 PinmemberPeter_Bernheim6-Feb-09 3:36 
GeneralRe: My vote of 2 PinmemberPIEBALDconsult14-Apr-09 6:45 
GeneralMy vote of 2 Pinmemberchewzoey30-Jan-09 3:04 
GeneralRe: My vote of 2 [modified] PinmemberPIEBALDconsult30-Jan-09 4:01 
GeneralCode Formatting Pinmemberchewzoey30-Jan-09 3:00 
GeneralRe: Code Formatting PinmemberPIEBALDconsult30-Jan-09 3:53 
GeneralMy vote of 2 Pinmemberjgauffin28-Jan-09 19:51 
GeneralRe: My vote of 2 PinmemberPIEBALDconsult29-Jan-09 4:37 
GeneralAmaizing coding method PinmemberMember 531489028-Jan-09 19:06 
GeneralRe: Amaizing coding method PinmemberPIEBALDconsult29-Jan-09 3:56 

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 | Mobile
Web01 | 2.8.140721.1 | Last Updated 29 Jan 2009
Article Copyright 2009 by PIEBALDconsult
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid