Download Source Code
Introduction
With all the attention recently being given to Entity Framework and LINQ to SQL, we might sometimes forget about ADO.NET. However, these newer technologies are based on ADO.NET. ADO.NET is still there and still a valid choice when accessing a database from .NET applications.
In fact, while I've been working with Entity Framework for some in-house software, I still go directly to ADO.NET for my websites. The truth is that Entity Framework is slower than using ADO.NET directly. I've also experienced a few quirks with Entity Framework, which I expect to go away as the technology matures.
This is not to say Entity Framework is not both useful and cool. It is. It is also much easier to use, once you get up to speed. I remain very interested in Entity Framework and intend to continue to use it in the future.
But, for now, I will continue to use ADO.NET for performance-critical applications such as my websites.
ADO.NET Suffers from Verbosity
One of the downsides with ADO.NET is that it requires a lot of typing. You must create and initialize several objects to perform a query. And each parameter to the query is, of course, yet another object. Listing 1 shows a typical query using ADO.NET.
This code executes a simple query that returns rows matching two criteria. Note that the code must create a SqlConnection, a SqlCommand, a SqlDataReader, and two SqlParameter objects. Obviously, this can become cumbersome for more complex queries.
Listing 1: Sample Code Using ADO.NET
protected const string ConnectionString = "...";
protected void TestAdoNet()
{
string qry = "SELECT ArtID, ArtTitle, ArtSlug" +
" FROM Article WHERE ArtApproved = @Approved AND ArtUpdated > @Updated";
SqlParameter parmApproved = new SqlParameter("@Approved", true);
SqlParameter parmUpdated = new SqlParameter("@Updated", new DateTime(2011, 3, 1));
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(qry, conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(parmApproved);
cmd.Parameters.Add(parmUpdated);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
// Get row of data from rdr
}
}
}
}
}
I wasn't crazy about this and so I decided to create an ADO.NET helper class to reduce the amount of typing required.
An ADO.NET Helper Class
Before I present the class itself, Listing 2 shows some sample code that uses my class to perform the same query performed in Listing 1. It still requires the creation of a few objects but, as you can see, not as many.
The static member ConnectionString can be set once in your application and all instances will use that connection string. (The connection string can also be overridden in the instance constructor to override the connection string for a particular instance.)
Then, to perform a query, create an instance of the AdoHelper class and call one or more of its methods.
Instead of SqlParameter objects, the query methods accept parameter arguments in pairs. The first argument specifies the name of the parameter and the second one specifies its value. In addition, the last argument can optionally be a SqlParameter object for specifying custom parameters (such as output parameters).
Listing 2: Sample Code Using my AdoHelper Class
AdoHelper.ConnectionString = "...";
protected void TestAdoHelper()
{
string qry = "SELECT ArtID, ArtTitle, ArtSlug" +
" FROM Article WHERE ArtApproved = @Approved AND ArtUpdated > @Updated";
using (AdoHelper db = new AdoHelper())
using (SqlDataReader rdr = db.ExecDataReader(qry, "@Approved", true,
"@Updated", new DateTime(2011, 3, 1)))
{
while (rdr.Read())
{
// Get row of data from rdr
}
}
}
The AdoHelper Class
Listing 3 shows my AdoHelper class. As previously described, the ConnectionString property is a static member that is shared by all instances of the class. In addition, the constructor is overloaded to allow you to override the connection string for a particular instance of the class.
This class provides a number of methods for performing queries including ExecNonQuery(), ExecScalar(), ExecDataReader(), and ExecDataSet() for working with text queries, and ExecNonQueryProc(), ExecScalarProc(), ExecDataReaderProc(), and ExecDataSetProc() for working with stored procedures.
AdoHelper also provides the BeginTransaction(), Commit() and Rollback() methods, providing support for transactions.
The class also implements IDispose to simplify ensuring proper clean up with using.
Listing 3: The AdoHelper Class
/// <summary>
/// An ADO.NET helper class
/// </summary>
class AdoHelper : IDisposable
{
// Internal members
protected string _connString = null;
protected SqlConnection _conn = null;
protected SqlTransaction _trans = null;
protected bool _disposed = false;
/// <summary>
/// Sets or returns the connection string use by all instances of this class.
/// </summary>
public static string ConnectionString { get; set; }
/// <summary>
/// Returns the current SqlTransaction object or null if no transaction
/// is in effect.
/// </summary>
public SqlTransaction Transaction { get { return _trans; } }
/// <summary>
/// Constructor using global connection string.
/// </summary>
public AdoHelper()
{
_connString = ConnectionString;
Connect();
}
/// <summary>
/// Constructure using connection string override
/// </summary>
/// <param name="connString">Connection string for this instance</param>
public AdoHelper(string connString)
{
_connString = connString;
Connect();
}
// Creates a SqlConnection using the current connection string
protected void Connect()
{
_conn = new SqlConnection(_connString);
_conn.Open();
}
/// <summary>
/// Constructs a SqlCommand with the given parameters. This method is normally called
/// from the other methods and not called directly. But here it is if you need access
/// to it.
/// </summary>
/// <param name="qry">SQL query or stored procedure name</param>
/// <param name="type">Type of SQL command</param>
/// <param name="args">Query arguments. Arguments should be in pairs where one is the
/// name of the parameter and the second is the value. The very last argument can
/// optionally be a SqlParameter object for specifying a custom argument type</param>
/// <returns></returns>
public SqlCommand CreateCommand(string qry, CommandType type, params object[] args)
{
SqlCommand cmd = new SqlCommand(qry, _conn);
// Associate with current transaction, if any
if (_trans != null)
cmd.Transaction = _trans;
// Set command type
cmd.CommandType = type;
// Construct SQL parameters
for (int i = 0; i < args.Length; i++)
{
if (args[i] is string && i < (args.Length - 1))
{
SqlParameter parm = new SqlParameter();
parm.ParameterName = (string)args[i];
parm.Value = args[++i];
cmd.Parameters.Add(parm);
}
else if (args[i] is SqlParameter)
{
cmd.Parameters.Add((SqlParameter)args[i]);
}
else throw new ArgumentException("Invalid number or type of arguments supplied");
}
return cmd;
}
#region Exec Members
/// <summary>
/// Executes a query that returns no results
/// </summary>
/// <param name="qry">Query text</param>
/// <param name="args">Any number of parameter name/value pairs and/or SQLParameter arguments</param>
/// <returns>The number of rows affected</returns>
public int ExecNonQuery(string qry, params object[] args)
{
using (SqlCommand cmd = CreateCommand(qry, CommandType.Text, args))
{
return cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Executes a stored procedure that returns no results
/// </summary>
/// <param name="proc">Name of stored proceduret</param>
/// <param name="args">Any number of parameter name/value pairs and/or SQLParameter arguments</param>
/// <returns>The number of rows affected</returns>
public int ExecNonQueryProc(string proc, params object[] args)
{
using (SqlCommand cmd = CreateCommand(proc, CommandType.StoredProcedure, args))
{
return cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Executes a query that returns a single value
/// </summary>
/// <param name="qry">Query text</param>
/// <param name="args">Any number of parameter name/value pairs and/or SQLParameter arguments</param>
/// <returns>Value of first column and first row of the results</returns>
public object ExecScalar(string qry, params object[] args)
{
using (SqlCommand cmd = CreateCommand(qry, CommandType.Text, args))
{
return cmd.ExecuteScalar();
}
}
/// <summary>
/// Executes a query that returns a single value
/// </summary>
/// <param name="proc">Name of stored proceduret</param>
/// <param name="args">Any number of parameter name/value pairs and/or SQLParameter arguments</param>
/// <returns>Value of first column and first row of the results</returns>
public object ExecScalarProc(string qry, params object[] args)
{
using (SqlCommand cmd = CreateCommand(qry, CommandType.StoredProcedure, args))
{
return cmd.ExecuteScalar();
}
}
/// <summary>
/// Executes a query and returns the results as a SqlDataReader
/// </summary>
/// <param name="qry">Query text</param>
/// <param name="args">Any number of parameter name/value pairs and/or SQLParameter arguments</param>
/// <returns>Results as a SqlDataReader</returns>
public SqlDataReader ExecDataReader(string qry, params object[] args)
{
using (SqlCommand cmd = CreateCommand(qry, CommandType.Text, args))
{
return cmd.ExecuteReader();
}
}
/// <summary>
/// Executes a stored procedure and returns the results as a SqlDataReader
/// </summary>
/// <param name="proc">Name of stored proceduret</param>
/// <param name="args">Any number of parameter name/value pairs and/or SQLParameter arguments</param>
/// <returns>Results as a SqlDataReader</returns>
public SqlDataReader ExecDataReaderProc(string qry, params object[] args)
{
using (SqlCommand cmd = CreateCommand(qry, CommandType.StoredProcedure, args))
{
return cmd.ExecuteReader();
}
}
/// <summary>
/// Executes a query and returns the results as a DataSet
/// </summary>
/// <param name="qry">Query text</param>
/// <param name="args">Any number of parameter name/value pairs and/or SQLParameter arguments</param>
/// <returns>Results as a DataSet</returns>
public DataSet ExecDataSet(string qry, params object[] args)
{
using (SqlCommand cmd = CreateCommand(qry, CommandType.Text, args))
{
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapt.Fill(ds);
return ds;
}
}
/// <summary>
/// Executes a stored procedure and returns the results as a Data Set
/// </summary>
/// <param name="proc">Name of stored proceduret</param>
/// <param name="args">Any number of parameter name/value pairs and/or SQLParameter arguments</param>
/// <returns>Results as a DataSet</returns>
public DataSet ExecDataSetProc(string qry, params object[] args)
{
using (SqlCommand cmd = CreateCommand(qry, CommandType.StoredProcedure, args))
{
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapt.Fill(ds);
return ds;
}
}
#endregion
#region Transaction Members
/// <summary>
/// Begins a transaction
/// </summary>
/// <returns>The new SqlTransaction object</returns>
public SqlTransaction BeginTransaction()
{
Rollback();
_trans = _conn.BeginTransaction();
return Transaction;
}
/// <summary>
/// Commits any transaction in effect.
/// </summary>
public void Commit()
{
if (_trans != null)
{
_trans.Commit();
_trans = null;
}
}
/// <summary>
/// Rolls back any transaction in effect.
/// </summary>
public void Rollback()
{
if (_trans != null)
{
_trans.Rollback();
_trans = null;
}
}
#endregion
#region IDisposable Members
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
if (!_disposed)
{
// Need to dispose managed resources if being called manually
if (disposing)
{
if (_conn != null)
{
Rollback();
_conn.Dispose();
_conn = null;
}
}
_disposed = true;
}
}
#endregion
}
Conclusion
I've been using variations of this class for a while now and it's hard for me to imagine using ADO.NET without it. If you haven't yet moved to LINQ to SQL or Entity Framework, you might find it useful as well.
End-User License
Use of this article and any related source code or other files is governed
by the terms and conditions of
.
Author Information
Jonathan Wood
I'm a software/website developer working out of the greater Salt Lake City area in Utah. I've developed many websites including Black Belt Coder, Insider Articles, and others.