Here's a helper class I use. It's lightweight but contains a few useful methods. Create a new class in your project and call it 'DbAssist' and copy this code in there. I haven't documented the methods so if you have trouble understanding anything just reply and I will assist. Cheers
using System;
using System.Configuration;
using System.Data;
namespace Common.Data {
public static class DbAssist {
private static void CheckConnectionClosed(IDbConnection connection) {
if (connection.State != ConnectionState.Closed) {
throw new InvalidOperationException("An error has occurred: connection is not in closed state.");
}
}
// Connection help
public static string GetConnectionString(string key) {
try {
return ConfigurationManager.ConnectionStrings\[key\].ConnectionString;
} catch (NullReferenceException ex) {
throw new ConfigurationErrorsException("An error has occurred: "
+ key + " does not exist in config.", ex);
}
}
// Scalar help
public static object ExecScalar(string sqlText, IDbConnection connection) {
return ExecScalar(sqlText, connection, CommandType.Text);
}
public static object ExecScalar(string sqlText, IDbConnection connection, CommandType cmdType) {
return ExecScalar(sqlText, connection, cmdType, null);
}
public static object ExecScalar(string sqlText, IDbConnection connection,
CommandType cmdType, IDataParameter\[\] parameters) {
CheckConnectionClosed(connection);
using (connection) {
connection.Open();
IDbCommand command = connection.CreateCommand();
command.CommandText = sqlText;
command.CommandType = CommandType.StoredProcedure;
if (parameters != null) {
foreach (IDataParameter p in parameters) {
command.Parameters.Add(p);
}
}
return command.ExecuteScalar();
}
}
// IDataReader help
public static IDataReader ExecReader(string sqlText, IDbConnection connection) {
return ExecReader(sqlText, connection, CommandBehavior.CloseConnection);
}
public static IDataReader ExecReader(string sqlText, IDbConnection connection,
