What's the most DRY-appropriate way to execute an SQL command?

Question

I'm looking to figure out the best way to execute a database query using the least amount of boilerplate code. The method suggested in the SqlCommand documentation:

private static void ReadOrderData(string connectionString)
{
    string queryString = "SELECT OrderID, CustomerID FROM dbo.Orders;";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        try
        {
            while (reader.Read())
            {
                Console.WriteLine(String.Format("{0}, {1}",     reader[0], reader[1]));
            }
        }
        finally
        {
            reader.Close();
        }
    }
}

mostly consists of code that would have to be repeated in every method that interacts with the database.

I'm already in the habit of factoring out the establishment of a connection, which would yield code more like the following. (I'm also modifying it so that it returns data, in order to make the example a bit less trivial.)

private SQLConnection CreateConnection()
{
    var connection = new SqlConnection(_connectionString);
    connection.Open();
    return connection;
}

private List<int> ReadOrderData()
{
    using(var connection = CreateConnection())
    using(var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT OrderID FROM dbo.Orders;";

        using(var reader = command.ExecuteReader())
        {
            var results = new List<int>();
            while(reader.Read()) results.Add(reader.GetInt32(0));
            return results;
        }
    }
}

That's an improvement, but there's still enough boilerplate to nag at me. Can this be reduced further? In particular, I'd like to do something about the first two lines of the procedure. I don't feel like the method should be in charge of creating the SqlCommand. It's a tiny piece of repetition as it is in the example, but it seems to grow if transactions are being managed manually or timeouts are being altered or anything like that.

edit: Assume, at least hypothetically, there's going to have to be a bunch of different types of data being returned. And consequently the solution can't be just one one-size-fits-all method, there will have to be a few different ones depending, at minimum, on whether ExecuteNonQuery, ExecuteScalar, ExecuteReader, ExecuteReaderAsync, or any of the others are being called. I'd like to cut down on the repetition among those.

Answer 1

Tried Dapper?

Granted this doesn't get you a DataReader but you might just prefer it this way once you've tried it.

It's about the lightest-weight an ORM can be while still being called an ORM. No more methods to map between DataReader and strong types for me.

Used right here on all the StackExchange sites.

using (var conn = new SqlConnection(cs))
{
    var dogs = connection.Query("select name, age from dogs");

    foreach (dynamic dog in dogs)
    {
        Console.WriteLine("{0} age {1}", dog.name, dog.age);
    }
}

or

using (var conn = new SqlConnection(cs))
{
    var dogs = connection.Query<Dog>("select Name, Age from dogs");

    foreach (Dog dog in dogs)
    {
        Console.WriteLine("{0} age {1}", dog.Name, dog.Age);
    }
}

class Dog
{
    public string Name { get; set; }
    public int Age { get; set; }
}

Answer 2

Doing this in comments was too much.

I would suggest that the boilerplate code around

    using(conn = new sqlconnection)
    using(cmd = new sqlcommand) {
          // blah blah blah
    }

isn't something to be lightly removed and instead would encourage that you keep it exactly where it's at. Resources, especially unmanaged ones, should be opened and released at the closest point to execution as possible IMHO.

In no small part due to the ease with which other developers will fail to follow the appropriate clean up conventions.

If you do something like

private SQLConnection CreateConnection()   
{   
    var connection = new SqlConnection(_connectionString);   
    connection.Open();   
    return connection;   
}   

Then you are inviting another programmer to call this method and completely fail to release the resource as soon as the query is executed. I don't know what kind of app you are building, but in a web app such a thing will lead to memory / connection / resource errors of types that are difficult to debug, unless you've been through it before.

Instead, I'd suggest you look into a lightweight ORM such as Dapper.net or similar to see how they approached it. I don't use dapper, but I hear it's pretty good. The reason I don't use it is simply that we don't allow inline sql to be executed against our databases (but that's a very different conversation).

---

Here's our standard:

public static DataTable StatisticsGet( Guid tenantId ) {
    DataTable result = new DataTable();
    result.Locale = CultureInfo.CurrentCulture;

    Database db = DatabaseFactory.CreateDatabase(DatabaseType.Clients.ToString());

    using (DbCommand dbCommand = db.GetStoredProcCommand("reg.StatsGet")) {
        db.AddInParameter(dbCommand, "TenantId", DbType.Guid, tenantId);

        result.Load(db.ExecuteReader(dbCommand));
    } // using dbCommand

    return result;
} // method::StatisticsGet

We make heavy use of Enterprise Library. It's short, simple and to the point and very well tested. This method just returns a datatable but you could easily have it return an object collection.. or nothing.

Answer 3

If you want to roll data access on your own, this pattern of help methods could be one way to remove duplication:

private List<int> ReadOrderData()
{
    return ExecuteList<int>("SELECT OrderID FROM dbo.Orders;", 
        x => x.GetInt32("orderId")).ToList();
}

private IEnumerable<T> ExecuteList(string query, 
    Func<IDataRecord, T> entityCreator)
{
    using(var connection = CreateConnection())
    using(var command = connection.CreateCommand())
    {
        command.CommandText = query;
        connection.Open();
        using(var reader = command.ExecuteReader())
        {
            while(reader.Read()) 
               yield return entityCreator(reader);
        }
    }
}

You'll have to add support for parameters and this might not compile, but the pattern is what I'm trying to illustrate.

Answer 4

The first two line are the most important thing you need...

but if you still wish to do it, you can turn them to a database handler class, yes it will become more of code, but in refactoring concept, every thing will move to the related topic...

try to write a singleton class, that receive a command and do action, so return result of type SqlDataReader reader...

Answer 5

What I typically do is use a custom class that I wrote a while back that accepts a SQL string, and optionally a list of parameters and it returns a DataTable.

Since the thing that changes between invocations is typically just the SQL that is optimal IMHO.

If you truly do need to use a DataReader you can do something like this:

public void ExecuteWithDataReader(string sql, Action<DataReader> stuffToDo) {
    using (SqlConnection connection = new SqlConnection(connectionString)) {
        using (SqlCommand command = new SqlCommand(sql, connection)) {
            connection.Open();

            using (SqlDataReader reader = command.ExecuteReader()) {
                try {
                    while (reader.Read()) {
                        stuffToDo(reader);
                    }
                }
                finally {
                    reader.Close();
                }
            }
        }
    }
}


private static void ReadOrderData(string connectionString) {
    string sql = "SELECT OrderID, CustomerID FROM dbo.Orders;";

    ExecuteWithDataReader(sql, r => Console.WriteLine(String.Format("{0}, {1}", r[0], r[1])));
}