As much as I love me some ORM's (I've used LINQ to SQL quite a bit, and for the MSDN/TechNet Profile and Forums we're using NHibernate more and more), there are times when it's appropriate, and in some ways more simple, to just throw up so old school ADO.NET connections, commands, readers and such. It still feels like a pain though to new up all the stuff, make sure it's closed, blah blah blah. It's pretty much the least favorite task of writing data access code. To minimize the pain, I have a set of extension methods that I like to use that drastically reduce the code you have to write. Here they are...
public static void Using(this SqlConnection connection, Action<SqlConnection> action)
{
connection.Open();
action(connection);
connection.Close();
}
public static SqlCommand Command(this SqlConnection connection, string sql){ var command = new SqlCommand(sql, connection); return command;}public static SqlCommand AddParameter(this SqlCommand command, string parameterName, object value){ command.Parameters.AddWithValue(parameterName, value); return command;}public static object ExecuteAndReturnIdentity(this SqlCommand command){ if (command.Connection == null) throw new Exception("SqlCommand has no connection."); command.ExecuteNonQuery(); command.Parameters.Clear(); command.CommandText = "SELECT @@IDENTITY"; var result = command.ExecuteScalar(); return result;}public static SqlDataReader ReadOne(this SqlDataReader reader, Action<SqlDataReader> action){ if (reader.Read()) action(reader); reader.Close(); return reader;}public static SqlDataReader ReadAll(this SqlDataReader reader, Action<SqlDataReader> action){ while (reader.Read()) action(reader); reader.Close(); return reader;}
It has been awhile since I've really revisited these, so you will likely find opportunity for further optimization. The bottom line here is that you can chain together a bunch of these methods to make a much more concise database call, in terms of the code on your screen, anyway. Here are some examples:
public Dictionary<string, string> Get(){ var dictionary = new Dictionary<string, string>(); _sqlHelper.GetConnection().Using(connection => connection.Command("SELECT Setting, [Value] FROM Settings") .ExecuteReader() .ReadAll(r => dictionary.Add(r.GetString(0), r.GetString(1)))); return dictionary;}
or...
public void ChangeName(User user, string newName){ _sqlHelper.GetConnection().Using(connection => connection.Command("UPDATE Users SET Name = @Name WHERE UserID = @UserID") .AddParameter("@Name", newName) .AddParameter("@UserID", user.UserID) .ExecuteNonQuery());}
The _sqlHelper.GetConnection() is just some other code that gets a connection object for you. You might have an even cleaner way to take that step out entirely. This looks more fluent, and the real magic sauce for me is the reader bits where you can put any kind of arbitrary method in there to iterate over the results.
