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 ) ;
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
