Background
Many questions have been posted from developers trying to develop applications that use databases.
These developers have read the MSDN documentation for one or another of the ADO.NET Providers,
but get stumped when their needs exceed the simple examples provided.
One of the problems is that the samples in the MSDN documentation only show the concrete classes of the Provider being discussed.
In fact, ADO.NET is much easier to work with if you use the Interfaces -- IDbConnection, IDbCommand, IDataReader, IDataParameter.
All ADO.NET Providers implement these Interfaces,
so a good degree of provider independence can be achieved by coding to the Interfaces rather then the concrete classes of a particular Provider
(differences in SQL syntax will still be an issue, but the code to execute the SQL statements needn't be).
We also get a lot of questions that can be solved simply by the use of parameters.
The MSDN documentation doesn't lead new developers to the use of parameters.
A new developer may get the idea that parameters are difficult to use and that only complex advanced applications need them and having ill-advised Code Projectors say things like:
"The parameterized string should be used only in the case when the SQL is made by using user input, but here is not the case!"
really doesn't help the cause of robust and reliable data access code.
Parameters are actually very easy to use and your code can be more robust and easier to read if you use them.
While there are a few operations that can't be done with parameters, they are far outweighed by the operations that can't be performed without parameters.
The use of parameters can also make an application more efficient when you need to execute the same statement many times in quick succession.
I strongly believe that every developer should make parameters the primary tool for passing values to the database;
toward that end, a framework must make parameters at least as easy to use as concatenation.
MSDN has this to say about parameters: http://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.110).aspx
Microsoft's Data Access Application Block (DAAB)
MSDN does include something that addresses some of the same issues.
It's way beyond anything I need, but you should probably check it out.
http://msdn.microsoft.com/en-us/library/dn440726(v=pandp.60).aspx
The DAAB has been around since at least .net 2.
Here on CodeProject I usually see (and use) the term "database-agnostic",
but I notice that the DAAB page uses the term "provider independent".
I'm pretty sure we're talking about the same thing,
and I find Microsoft's term to be more concise,
so I shall endeavor to use it from here on.
Introduction
This article is in large part due to a need to provide a full answer to questions on Code Project,
rather than just my common refrain of "don't use concatenation; use parameterized queries".
I have been writing applications using ADO.NET Providers since .net 1.1 and I have written a half dozen or so provider-independent frameworks --
I have even written articles about two of them -- this is yet another.
My primary frameworks are actually rather large and heavy and too big for an article.
The goal of this framework is really just as an example of what can easily be created from the information I intend to put in this article.
All the sample code is in C# because that's what I use,
but VB.net is just as capable at using these Interfaces and producing robust database access code.
I haven't really used this code yet, and I've only tested a few parts, but it does reflect more than ten years of experience with ADO.NET.
I hope to exercise this code with some future projects and I welcome feedback from any of you who actually use it.
IDbConnection
Refer to http://msdn.microsoft.com/en-us/library/system.data.idbconnection(v=vs.110).aspx
In order to communicate with a database, you need a Connection from a Provider.
An application may use multiple Connections to any number of databases.
Any number of Commands may use the same Connection, but only one Command may be Executing on a particular Connection at a time
(note that that includes when a DataReader is open on a Connection).
Instantiating the Connection is the only time you actually need to know which Provider you are using and which concrete class to use.
(It's actually possible to write an application in such a way that it doesn't even need to know that, but I won't address that in this article.)
After that, it's all Interfaces all the way up.
Rather than
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection ( ... )
use
System.Data.IDbConnection con = new System.Data.SqlClient.SqlConnection ( ... )
And a method that accepts a Connection as a parameter:
public int DoSomething ( System.Data.IDbConnection con ) { ... }
There
are times when you need to know the actual type of the object,
but they aren't common and can probably be hidden from the application.
IDbCommand
Refer to http://msdn.microsoft.com/en-us/library/system.data.idbcommand(v=vs.110).aspx
By now you may be wondering how to instantiate a Command without knowing the actual concrete type to use.
The quick answer is "you don't" -- you let the Connection do it for you; it knows what type to use.
Let the framework help you.
The IDbConnection Interface has a method called CreateCommand that instantiates and returns a Command of the correct type.
You can then set the CommandText and other properties as required.
So use:
System.Data.IDbCommand cmd = con.CreateCommand()
and
public int DoSomething ( System.Data.IDbCommand cmd ) { ... }
IDbCommand also has the three main Execute methods you may be familiar with using:
-
ExecuteScalar
-
ExecuteNonQuery
-
ExecuteReader
An application may execute any of these methods on a Command without knowing what specific type of database is in use.
(I'll say again, that yes, there are syntax considerations, but the data access classes don't really care.)
IDataReader
Refer to http://msdn.microsoft.com/en-us/library/system.data.idatareader(v=vs.110).aspx
Speaking of ExecuteReader, it returns an instance of IDataReader.
So use:
System.Data.IDataReader rdr = cmd.ExecuteReader()
and
public int DoSomething ( System.Data.IDataReader rdr ) { ... }
Additionally, an IDataReader implements IDataRecord, so consider using that when you need to perform some operation on one result from a DataReader.
public int DoSomething ( System.Data.IDataRecord rec ) { ... }
http://msdn.microsoft.com/en-us/library/system.data.idatarecord(v=vs.110).aspx
IDataParameter
Refer to http://msdn.microsoft.com/en-us/library/system.data.idataparameter(v=vs.110).aspx
Don't confuse this with IDbDataParameter.
(I say this knowing full well that you will now get them confused.)
Just as a Connection can instantiate a Command for you,
a Command can instantiate a Parameter for you:
System.Data.IDataParameter prm = cmd.CreateParameter()
You can then set the
ParameterName and Value and Add the Parameter to the Command's
ParameterCollection.
OK, I heard someone shout "AddWithValue!"
AddWithValue is a very handy member of some classes that implement the IDataParameterCollection Interface,
such as SqlParameterCollection and OleDbParameterCollection,
but it is not a member of the IDataParameterCollection Interface so you can't rely on it being there when you want provider independence.
It also didn't exist prior to .net 2.0.
Before I get off the subject of parameters I also want to mention that some developers take an awful lot of effort setting the DbType of a parameter when it is instantiated.
This is wasted effort because the Provider will infer the DbType when the Value is set anyway -- perhaps overriding the specified DbType.
Just set the Value, then alter the DbType if you really need to.
IDataParameterCollection
Refer to http://msdn.microsoft.com/en-us/library/system.data.idataparametercollection(v=vs.110).aspx
A Command has a Parameters property that gives access to an IDataParameterCollection.
I wouldn't mention this interface except that it has a small flaw.
Bear in mind that these Interfaces were created before .net 2.0 and therefore there are no generics.
A side-efect of this is that this Collection treats its members as objects rather than IDataParameters.
The consequence of this is that when you access a Parameter in the Collection, you have to cast it.
This doesn't impact efficiency much, but it's a pain in the assembly if you have to do that frequently; it's best hidden inside a framework.
IDisposable
Refer to http://msdn.microsoft.com/en-us/library/system.idisposable(v=vs.110).aspx
The samples in the MSDN documentation don't show the use of the using statement,
so many beginners don't learn to use it and their code suffers.
The using statement is one of the simplest ways to improve the robustness and maintainability of your code.
Putting it all together, but not particularly well
Let's say for the sake of this discussion that you receive an Excel file and want to load some of the data into an SQL Server database.
Granted, there are tools out there that let you do that, but the point here is to show what can be done with ADO.NET.
I'll even forgo much of my usual formatting, just for you.
using ( IDbConnection
srccon = new OleDbConnection ( excelconnectionstring )
, dstcon = new SqlConnection ( sqlserverconnectionstring )
)
{
int lastcopiedID = -1 ;
using ( IDbCommand dstcmd = dstcon.CreateCommand() )
{
dstcmd.CommandText = sqlserverquerystatement ;
dstcon.Open() ;
object tmp = dstcmd.ExecuteScalar() ;
dstcon.Close() ;
if ( ( tmp != null ) && ( tmp != DBNull.Value ) ) lastcopiedID = (int) tmp ;
}
using ( IDbCommand
srccmd = srccon.CreateCommand()
, dstcmd = dstcon.CreateCommand()
)
{
IDataParameter prm ;
srccmd.CommandText = excelquerystatement ;
prm = srccmd.CreateParameter() ;
prm.ParameterName = "@ID" ;
prm.Value = lastcopiedID ;
srccmd.Parameters.Add ( prm ) ;
dstcmd.CommandText = sqlserverinsertstatement ;
prm = dstcmd.CreateParameter() ;
prm.ParameterName = "@ID" ;
dstcmd.Parameters.Add ( prm ) ;
scrcon.Open() ;
using ( IDataReader scrrdr = srccmd.ExecuteReader() )
{
dstcon.Open() ;
while ( srcrdr.Read() )
{
for ( int i = 0 ; i < srcrdr.FieldCount ; i++ )
prm = ((IDataParameter) dstcmd.Parameters ( i )).Value = srcrdr [ i ] ?? DBNull.Value ;
dstcmd.ExecuteNonQuery() ;
}
dstcon.Close() ;
}
}
}
That's not very exciting,
one of the few improvements it provides is that fewer
using statements are required because we can double up the Connections and the Commands.
On the downside, the open/close state of the Connections isn't handled very well and there's more work with the parameters.
Putting it all together, in a small generic framework
The code I present here was written specifically as an example of what can be accomplished if you embrace the power of the ADO.NET Interfaces.
You are quite welcome to alter the code or write your own -- really, I want you to.
Using the code is as easy as:
using ( PIEBALD.Data.IDbConnection con = PIEBALD.Data.SqlServer.Connect ( servername , databasename ) )
{
using ( PIEBALD.Data.IDbCommand cmd = con.CreateCommand
( @"SELECT fizz , buzz FROM foo WHERE bar=@Param0"
, 42
) )
{
using ( System.Data.IDataReader rdr = cmd.ExecuteReader() ) { ... }
}
}
I never store an actual ConnectionString; storing the parts required to create one makes for a more versatile application.
Also consider console applications that take their information from the command line -- will the user want to type a connectionstring?
Interfaces
To achive this simplicity and to ease extensibility I'll start by defining two Interfaces, for Connection and Command, that include only what I need:
public interface IDbConnection : System.IDisposable
{
IDbConnection Clone() ;
PIEBALD.Data.IDbCommand CreateCommand ( string CommandText , params object[] ParameterList ) ;
}
public interface IDbCommand : System.IDisposable
{
int ParameterCount { get ; }
System.Data.IDataParameter Parameter ( int Index ) ;
System.Data.IDataParameter Parameter ( string Name ) ;
T ExecuteScalar<T> ( T IfNull ) ;
int ExecuteNonQuery() ;
System.Data.IDataReader ExecuteReader() ;
}
Other members may be added as you see fit.
I didn't include Transaction handling; you might want to add that.
DbCommand
DbCommand is a wrapper around a System.Data.IDbCommand that hides a lot of the details,
especially those associated with parameters.
You may add properties for CommandType and CommandTimeout if you need those.
You can derive from DbCommand, but if you do you will likely also have to derive a new DbConnection.
A simpler way to add members is via partial class files -- embrace the tools that the language provides.
Each of the Execute methods will ensure that the Connection is Open and that no other method is using the Connection*.
The Dispose method will ensure that the Connection is Closed.
DbCommand provides a generic version of ExecuteScalar that hides the casting of the returned value to the proper datatype.
* The developer will need to ensure that no DataReader is active on the Connection.
namespace PIEBALD.Data
{
public partial class DbCommand : IDbCommand
{
protected System.Data.IDbCommand Command { get ; private set ; }
protected internal DbCommand ( System.Data.IDbCommand Command )
{
this.Command = Command ;
return ;
}
The constructor allows you to specify values for the parameters.
This does require you to use the parameter names Param0 ... Paramn,
but that seems a small price to pay considering you don't have to create them manually.
Another option could be to pass in a Tuple<string,object>* for each parameter,
but I've been doing it this way for a long time and I don't mind.
If you provide parameter values, then the CommandText and Parameters will be passed through System.String.Format;
this allows you to specify things that you can't specify with actual parameters.
This feature can be misused, but I'll trust you to use it only when you really need to.
* Refer to http://msdn.microsoft.com/en-us/library/dd268536(v=vs.110).aspx ;
Tuple didn't enter .net until version 4.0.
protected internal DbCommand
( System.Data.IDbCommand Command
, string CommandText
, params object[] ParameterList
)
: this ( Command )
{
if ( ( ParameterList == null ) || ( ParameterList.Length == 0 ) )
{
this.Command.CommandText = CommandText ;
}
else
{
this.Command.CommandText = System.String.Format ( CommandText , ParameterList ) ;
for ( int i = 0 ; i < ParameterList.Length ; i++ )
{
System.Data.IDataParameter prm = this.Command.CreateParameter() ;
prm.ParameterName = System.String.Format ( "Param{0}" , i ) ;
prm.Value = ParameterList [ i ] ?? System.DBNull.Value ;
this.Command.Parameters.Add ( prm ) ;
}
}
return ;
}
public virtual void
Dispose()
{
lock ( this.Command.Connection )
{
if ( this.Command.Connection.State == System.Data.ConnectionState.Open )
this.Command.Connection.Close() ;
}
this.Command.Dispose() ;
return ;
}
The only access to the Command's Parameter collection is via these members.
public virtual int
ParameterCount { get { return ( this.Command.Parameters.Count ) ; } }
public virtual System.Data.IDataParameter
Parameter ( int Index ) { return ( this.Command.Parameters [ Index ] as System.Data.IDataParameter ) ; }
public virtual System.Data.IDataParameter
Parameter ( string Name ) { return ( this.Command.Parameters [ Name ] as System.Data.IDataParameter ) ; }
ExecuteNonQuery and
ExecuteReader demonstrate a few good practices when executing commands:
- Locking the Connection instance -- for thread safety, just in case
- Opening the Connection
- Simple error handling
- Catching Exceptions
- Adding Data to the Exception
- Rethrowing the Exception
public virtual int
ExecuteNonQuery()
{
lock ( this.Command.Connection )
{
if ( this.Command.Connection.State != System.Data.ConnectionState.Open )
this.Command.Connection.Open() ;
try
{
return ( this.Command.ExecuteNonQuery() ) ;
}
catch ( System.Exception err )
{
err.Data [ "CommandText" ] = this.Command.CommandText ;
for ( int i = 0 ; i < this.ParameterCount ; i++ )
{
System.Data.IDataParameter prm = this.Parameter ( i ) ;
err.Data [ prm.ParameterName ] = prm.Value ;
}
throw ;
}
}
}
public virtual System.Data.IDataReader
ExecuteReader()
{
lock ( this.Command.Connection )
{
if ( this.Command.Connection.State != System.Data.ConnectionState.Open )
this.Command.Connection.Open() ;
try
{
return ( this.Command.ExecuteReader() ) ;
}
catch ( System.Exception err )
{
err.Data [ "CommandText" ] = this.Command.CommandText ;
for ( int i = 0 ; i < this.ParameterCount ; i++ )
{
System.Data.IDataParameter prm = this.Parameter ( i ) ;
err.Data [ prm.ParameterName ] = prm.Value ;
}
throw ;
}
}
}
}
}
ExecuteScalar has the same features as the other Execute methods, but it also has to transform the return value.
The complexity of
ExecuteScalar is to allow some flexibility in casting and converting the value in the database to the datatype you want to use in your code.
The main reason I use it is to convert a numeric value in the database to an enumerated value in the code.
public virtual T
ExecuteScalar<T> ( T IfNull )
{
object result ;
lock ( this.Command.Connection )
{
if ( this.Command.Connection.State != System.Data.ConnectionState.Open )
this.Command.Connection.Open() ;
try
{
result = this.Command.ExecuteScalar() ;
}
catch ( System.Exception err )
{
err.Data [ "CommandText" ] = this.Command.CommandText ;
for ( int i = 0 ; i < this.ParameterCount ; i++ )
{
System.Data.IDataParameter prm = this.Parameter ( i ) ;
err.Data [ prm.ParameterName ] = prm.Value ;
}
throw ;
}
}
if ( ( result == null ) || ( result == System.DBNull.Value ) )
{
result = IfNull ;
}
else if ( ! ( result is T ) )
{
System.Type t = typeof(T) ;
if ( t.IsEnum )
{
if ( result is string )
{
result = System.Enum.Parse ( t , (string) result ) ;
}
else
{
result = System.Convert.ChangeType ( result , System.Enum.GetUnderlyingType ( t ) ) ;
}
}
else if ( result is System.IConvertible )
{
result = System.Convert.ChangeType ( result , t ) ;
}
}
return ( (T) result ) ;
}
DbConnection<T>
Here is the definition of an abstract generic wrapper for a System.Data.IDbConnection:
namespace PIEBALD.Data
{
public abstract partial class DbConnection<T> : IDbConnection
where T : System.Data.IDbConnection
{
protected System.Data.IDbConnection Connection { get ; private set ; }
protected DbConnection ( T Connection )
{
this.Connection = Connection ;
return ;
}
public virtual void
Dispose()
{
this.Connection.Dispose() ;
return ;
}
public abstract IDbConnection Clone() ;
The
CreateCommand method, as written, will only work with
DbCommand;
if you derive a new
DbCommand, then you will also need to derive a new
DbConnection with a
CreateCommand that returns one.
Or perhaps
CreateCommand could be made generic, whatever, have at it.
public virtual PIEBALD.Data.IDbCommand
CreateCommand
( string CommandText
, params object[] ParameterList
)
{
return ( new PIEBALD.Data.DbCommand
( this.Connection.CreateCommand()
, CommandText
, ParameterList
) ) ;
}
}
}
SqlServer.Connection
To use these classes you will need to derive specific DbConnections for the Providers you wish to use.
Here I'll present the one I wrote for SQL Server.
namespace PIEBALD.Data.SqlServer
{
public partial class Connection : PIEBALD.Data.DbConnection<System.Data.SqlClient.SqlConnection>
{
protected Connection
( System.Data.SqlClient.SqlConnection Connection )
: base ( Connection )
{
return ;
}
protected Connection
( string ConnectionString )
: this ( new System.Data.SqlClient.SqlConnection ( ConnectionString ) )
{
return ;
}
public override PIEBALD.Data.IDbConnection
Clone()
{
return ( new Connection ( this.Connection.ConnectionString ) ) ;
}
I use a static method to form the
ConnectionString and call the constructor.
You can create similar methods as your needs require, but this provides the basics.
public static Connection
Connect ( string Server
, string Database
)
{
return ( new Connection ( System.String.Format
( "Server={0};Database={1};Trusted_Connection=yes"
, Server
, Database
) ) ) ;
}
}
}
The zip file also includes DbConnections for Access and Excel.
If you review these three Provider-specific classes you should be able to easily see ho wlittle code needs to be written to add support for a new Provider;
this is all due to the power of the Interfaces.
History
2014-04-01 First version
