.net SQL Server stored procs wrapper class structure

I'm looking to write a C# SQL Server wrapper to call some stored procedures. If I was writing a single function I'd do something like the following (which I think is correct/proper):

void RunS开发者_开发知识库toredProc1(object arg1)
{
  using(SqlConnection conn = new SqlConnection(connStr)){
    try{
      SqlCommand cmd = new SqlCommand("storedProc1", conn);
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.AddWithValue("@input1", arg1);

      conn.Open();
      cmd.ExecuteNonQuery();
    } catch (Exception ex){
      //handle the exception appropriately.
    }
  }
}

The problem I'm having is that it seems like a lot of repeated code... every function will have the same using/try(open/execute)/catch code, and it'd be nice to have it all in only one place. Is there a clean way of doing this? How about for queries that I'd want to use a data reader on?

---

Something like this should do:

void RunStoredProc(string storedProcName, IDictionary<string, object> args)
{
    using (SqlConnection conn = new SqlConnection(connStr))
    using (SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = storedProcName;
        cmd.CommandType = CommandType.StoredProcedure;

        foreach (KeyValuePair<string, object> kvp in args)
        {
            cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
        }

        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

The connection object itself would probably also be better off as a parameter to this helper method, so you could make it static. It might be interesting to write it as an extension method on SqlConnection.

I would keep the exception handling in your RunStoredProc1 method or even more likely: in the methods that call RunStoredProc1, because exception handling will likely differ on a case by case basis.

void RunStoredProc1(object input1)
{
    var args = new Dictionary<string, object>()
               {
                   { "input1", input1 }
               };

    try
    {
        RunStoredProc("storedProc1", args);
    }
    catch (Exception ex)
    {
        // Handle exception properly
    }
}

---

Just a fun exercise for me, and not necessarily the way you'd want to implement it. I wrote a quick fluent interface for building and executing SqlCommands.

A couple of sample usages:

    int i = Sql.UsingConnection("sample")
        .GetTextCommandFor("Select Top 1 ActorID From Actor Where FirstName = @fname")
        .AddParameters(new {fname = "Bob"})
        .OnException(e => Console.WriteLine(e.Message))
        .ExecuteScalar<int>();

    var q = Sql.UsingConnection("sample")
        .GetTextCommandFor("Select * From Actor Where FirstName=@fname and ActorID > @id")
        .AddParameters(new {id = 1000, fname = "Bob"});

    using(var reader = q.ExecuteReader())
    {
        while(reader.Read())
        {
            // do something
        }
    }

The actual class(es) and Interfaces are below:

public class Sql
{
    public static ISqlCommandTypeSelector UsingConnection(string connection)
    {
        return new SqlBuilder(connection);
    }

    private class SqlBuilder : ISqlCommandTypeSelector, ISqlParameterManager, ISqlExecutor
    {
        private string _connection;
        private string _sqltext;
        private CommandType _commandtype;
        private Action<Exception> _exceptionBehavior = DefaultExceptionBehavior;

        private IList<SqlParameter> _inParams;


        public SqlBuilder(string connection)
        {
            _connection = ConfigurationManager.ConnectionStrings[connection].ConnectionString;
            _inParams = new List<SqlParameter>();
        }

        public ISqlParameterManager GetTextCommandFor(string text)
        {
            _sqltext = text;
            _commandtype = CommandType.Text;
            return this;
        }

        public ISqlParameterManager GetProcCommandFor(string proc)
        {
            _sqltext = proc;
            _commandtype = CommandType.StoredProcedure;
            return this;
        }

        public ISqlExecutor OnException(Action<Exception> action)
        {
            _exceptionBehavior = action;
            return this;
        }

        public void ExecuteNonQuery()
        {
            try
            {
                using (var connection = new SqlConnection(_connection))
                using (var cmd = connection.CreateCommand())
                {
                    ConfigureCommand(cmd);
                    PopulateParameters(cmd);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                _exceptionBehavior(ex);
            }
        }

        public T ExecuteScalar<T>()
        {
            T result = default(T);
            try
            {
                using (var connection = new SqlConnection(_connection))
                using (var cmd = connection.CreateCommand())
                {
                    ConfigureCommand(cmd);
                    PopulateParameters(cmd);
                    connection.Open();
                    result = (T) cmd.ExecuteScalar();
                    return result;
                }
            }
            catch(InvalidCastException ex)
            {
                // rethrow?
            }
            catch(Exception ex)
            {
                _exceptionBehavior(ex);
            }
            return result;
        }

        public IDataReader ExecuteReader()
        {
            try
            {
                var connection = new SqlConnection(_connection);
                var cmd = connection.CreateCommand();
                ConfigureCommand(cmd);
                PopulateParameters(cmd);
                connection.Open();
                var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch(Exception ex)
            {
                _exceptionBehavior(ex);
            }
            return null;
        }

        public ISqlExecutor AddParameters(object @params)
        {
            var type = @params.GetType();
            var props = type.GetProperties();
            foreach (var propertyInfo in props)
            {
                var param = new SqlParameter("@" + propertyInfo.Name, propertyInfo.GetValue(@params, null));
                param.Direction = ParameterDirection.Input;
                _inParams.Add(param);
            }
            return this;
        }

        public ISqlExecutor WithoutParams()
        {
            return this;
        }

        private void ConfigureCommand(SqlCommand cmd)
        {
            cmd.CommandText = _sqltext;
            cmd.CommandType = _commandtype;
        }

        private void PopulateParameters(SqlCommand cmd)
        {
            cmd.Parameters.AddRange(_inParams.ToArray());
        }

        private static void DefaultExceptionBehavior(Exception e)
        {
            // do something
        }
    }


}

public interface ISqlCommandTypeSelector
{
    ISqlParameterManager GetTextCommandFor(string text);
    ISqlParameterManager GetProcCommandFor(string proc);
}

public interface ISqlExecutor
{
    ISqlExecutor OnException(Action<Exception> action);

    void ExecuteNonQuery();
    T ExecuteScalar<T>();
    IDataReader ExecuteReader();
}

public interface ISqlParameterManager
{
    ISqlExecutor AddParameters(object @params);
    ISqlExecutor WithoutParams();
}

There is some repeated code that could probably be refactored some more if you really hate repeated code. This is just a fun exercise, and probably not how you want to do your data access however. This also doesn't support out parameters as it is written.

---

The Microsoft Enterprise Library Data Access Application Block can help to reduce redundant code like that, if you're sticking to pure ADO.NET for your data layer. See http://msdn.microsoft.com/en-us/library/ff664408(v=PandP.50).aspx. There are lots of code samples online and in the download as well, i.e. http://msdn.microsoft.com/en-us/library/ff664702(v=PandP.50).aspx.

---

I'm a big fan of letting computers do the rote, repetitive work. They're very good at it. I only have to teach them to do it once. So I wrote a code generator that uses a reference database to generate strongly typed access code. The advantage of this technique is that if you change the stored procedure's signatures, all you have to do it re-gen your data access layer. Any breaking changes will cause compile errors.

The code generate I wrote reads an XML file identifying the stored procedures of interest and retrieves their metadata from the specified reference database(s).

The XML file contains flags identifying whether each stored procedure returns

• multiple result sets (dataset)
• a single result set (datatable)
• a single row (datarow)
• a single row with a single column (a scalar value)
• a DataReader
• an XmlReader
• or nothing (nonquery)

From that it generates appropriate code, 1 class per stored procedure. The generated code provides access to the stored procedure's return code as well as the returned value for any output parameters.

It also parses the declaration for the stored procedure in the stored procedure's source code to identify any optional arguments (those with default values): the generated code allows those to be omitted in the call to execute the stored procedure.

Invoking the generated code goes like this:

    public DataTable GetRiskFactorsForPatient( int patientID )
    {
        dbo_GetRiskbyPatient sp = new dbo_GetRiskbyPatient( CONNECT_STRING_ID ) ;
        int                  rc = sp.Exec( patientID ) ;
        DataTable            dt = sp.ResultSet ;

        if ( dt == null ) throw new InvalidOperationException( "nothing returned from stored procedure" ) ;
        return dt ;
    }

---

Personally, i prefer

void RunStoredProc1(object arg1)
{
    try
    {
        using(SqlConnection conn = new SqlConnection(connStr))
        {
            using SqlCommand cmd = new SqlCommand("storedProc1", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@input1", arg1);

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }
    } 
    catch (Exception ex)
    {
      //handle the exception appropriately.
    }
}

Over the traditional try catch finally that you would need to do to manage your resources.

But overall, I like doing it with separate methods, so that you can custom tailor your catch blocks for the sproc.

Also, You might need more than one parameter down the road, and you would just be making a mess of a fairly straight-forward function