Generator-less! C# wrapper for SQL Stored Procedures

Introduction

If you've dealt with SQL from C#, you know how contrived it is. A whole lot of repetition that begs for a better way. Several on this site have already documented the pain and submitted a "better" way here, here, and here. These solutions involve an intermediate code generation step that requires you to paste in a bunch of code for each sproc (stored procedure), which allows you to then call the sproc like a normal procedure from C#. This extra code generation step, while kinda cool, is not without pain. The solutions are somewhat unwieldy, and some involve external dependencies.

A better way

There's a way to call an sproc from C# almost like a regular procedure, without any intermediate generating, pasting, or dependencies, and with reasonable performance (sproc info is cached). Here's how you might use the code:

    MySqlConn _sqlConn = new MySqlConn(
               "provide a valid connection string here");
    ...
    _sqlConn.ExecSProc("AddStaff", staffName, isDoctor);
    _sqlConn.ExecSProc("AddRoom", roomName);

Honest, that's it! You just called two sprocs; one named "AddStaff" with two input parameters, then "AddRoom" passing in one parameter. About the only drawback (compared to the code generation method) is that since parameters can be of any type, we won't know until runtime if there was a type mismatch, or if we passed the wrong number of parameters. Wah. Also note that neither this method nor automatic code generation will keep you from going stale - if you change the number or type or order of parameters in your sproc, you'll have to remember to update your C# code.

Does your sproc return a value and/or have output parameters? Well, if your sproc returns an error code and sets an out Param @roomId, then it might look like this:

    int err = _sqlConn.ExecSProc("AddRoom", roomName);
    if (err == 0)    //all ok?
        roomId = (int)_sqlConn.Param("@roomId");

How much does it cost, you ask? About 130 lines of code that you can copy, paste, and forget about. Here it is:

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace CodeProjectRocks
{
    public class MySqlConn
    {
        SqlConnection _dbConn;
        SProcList  _sprocs;  //sproc parameter info cache
        SqlParameterCollection _lastParams; //used by Param()

        public MySqlConn(string connStr)
        {
            _dbConn = new SqlConnection(connStr);
            _sprocs = new SProcList(this);
        }

        void Open()   
         { if (_dbConn.State != ConnectionState.Open) _dbConn.Open(); }
        void Close()  
         { if (_dbConn.State == ConnectionState.Open) _dbConn.Close(); }

        SqlCommand NewSProc(string procName)
        {
            SqlCommand cmd = new SqlCommand(procName, _dbConn);
            cmd.CommandType = CommandType.StoredProcedure;

#if EmulateDeriveParameters   //see below for our 
                              //own DeriveParameters
            MySqlCmdBuilder.DeriveParameters(cmd);
#else
            Open();
            SqlCommandBuilder.DeriveParameters(cmd);
           //SQL treats OUT params as REF params 
           //(thus requiring those parameters to be passed in)
           //if that's what you really want, remove 
           //the next three lines
            foreach (SqlParameter prm in cmd.Parameters)
                if (prm.Direction == ParameterDirection.InputOutput)
                    //make param a true OUT param
                    prm.Direction = ParameterDirection.Output; 
#endif

            return cmd;
        }

        SqlCommand FillParams(string procName, 
                                params object[] vals)
        {
            //get cached info (or cache if first call)
            SqlCommand cmd = _sprocs[procName];  

            //fill parameter values for stored procedure call
            int i = 0;
            foreach (SqlParameter prm in cmd.Parameters)
            {
                //we got info for ALL the params - only 
                //fill the INPUT params
                if (prm.Direction == ParameterDirection.Input
                 || prm.Direction == ParameterDirection.InputOutput)
                    prm.Value = vals[i++];
            }
            //make sure the right number of parameters was passed
            Debug.Assert(i == (vals == null ? 0 : vals.Length));

            //for subsequent calls to Param()
            _lastParams = cmd.Parameters;    
            return cmd;
        }

        //handy routine if you are in control of the input.
        //but if user input, vulnerable to sql injection attack
        public DataRowCollection QueryRows(string strQry)
        {
            DataTable dt = new DataTable();
            new SqlDataAdapter(strQry, _dbConn).Fill(dt);
            return dt.Rows;
        }

        public int ExecSProc(string procName, 
                              params object[] vals)
        {
            int retVal = -1;  //some error code

            try
            {
                Open();
                FillParams(procName, vals).ExecuteNonQuery();
                retVal = (int)_lastParams[0].Value;
            }
            //any special handling for SQL-generated error here
            //catch (System.Data.SqlClient.SqlException esql) {}
            catch (System.Exception e)
            {
                //handle error
            }
            finally
            {
                Close();
            }
            return retVal;
        }

        public DataSet ExecSProcDS(string procName, 
                                     params object[] vals)
        {
            DataSet ds = new DataSet();

            try
            {
                Open();
                new SqlDataAdapter(
                      FillParams(procName, vals)).Fill(ds);
            }
            finally
            {
                Close();
            }
            return ds;
        }

        //get parameter from most recent ExecSProc
        public object Param(string param)
        {
            return _lastParams[param].Value;
        }

        class SProcList : DictionaryBase
        {
            MySqlConn _db;
            public SProcList(MySqlConn db)        
               { _db = db; }

            public SqlCommand this[string name]
            { get {      //read-only, "install on demand"
                if (!Dictionary.Contains(name))
                    Dictionary.Add(name, _db.NewSProc(name));
                return (SqlCommand)Dictionary[name];
            } }
        }
    }
}

As an FYI only to show how to query SQL for param info (you do not need this code, unless, as pointed out by a reader, you are inside a SQL transaction, which the framework apparently does not handle):

#if EmulateDeriveParameters
    class MySqlCmdBuilder {
        static SqlTypeMap _sqlTypeMap = null;
        class SqlTypeMap : DictionaryBase {
            public SqlDbType this[string key]
              { get { return (SqlDbType)Dictionary[key]; }}
            public void Add(string key, SqlDbType value)  
              { Dictionary.Add(key, value); }
        }

        //static helper class - don't allow instantiation
        private MySqlCmdBuilder() {}    

        public static void DeriveParameters(SqlCommand cmd)
        {
            EnsureTypeMap();

            //cmd.Parameters[0] will always hold 
            //the sproc return value
            SqlParameter prmRet = 
                new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
            prmRet.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(prmRet);

            string qrySProc = 
                "SELECT parameter_name as name"
                    + ", data_type as xtype"
                    + ", cast(isnull(character_maximum_length, " + 
                                       "numeric_scale) as int) as prec"
                    + ", case when parameter_mode like '%out%' " + 
                                       "then 1 else 0 end as isoutparam"
                + " FROM INFORMATION_SCHEMA.PARAMETERS"
                + " WHERE specific_name = '" + cmd.CommandText + "'"
                + " ORDER BY ordinal_position";

            //query SQL-server for given sproc's parameter info
            DataTable dt = new DataTable(); 
            new SqlDataAdapter(qrySProc, cmd.Connection).Fill(dt);
            foreach (DataRow dr in dt.Rows)
            {
                SqlParameter prm = new SqlParameter(
                        (string)dr[0],               //dr["name"] 
                        _sqlTypeMap[(string)dr[1]],  //dr["xtype"]
                        (int)dr[2]);                 //dr["prec"]
                if ((int)dr[3] == 1)                 //isoutparam?
                    prm.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(prm);
            }
        }

        static void EnsureTypeMap()
        {
            if (_sqlTypeMap == null) {
                _sqlTypeMap = new SqlTypeMap();
                _sqlTypeMap.Add("bit",          SqlDbType.Bit);
                _sqlTypeMap.Add("int",          SqlDbType.Int);
                _sqlTypeMap.Add("smallint",     SqlDbType.SmallInt);
                _sqlTypeMap.Add("tinyint",      SqlDbType.TinyInt);
                _sqlTypeMap.Add("datetime",     SqlDbType.DateTime);
                _sqlTypeMap.Add("smalldatetime",SqlDbType.SmallDateTime);
                _sqlTypeMap.Add("char",         SqlDbType.Char);
                _sqlTypeMap.Add("varchar",      SqlDbType.VarChar);
                _sqlTypeMap.Add("nchar",        SqlDbType.NChar);
                _sqlTypeMap.Add("nvarchar",     SqlDbType.NVarChar);
                //add more here if SqlTypeMap[...] throws an exception
            }
        }
    }
#endif

The magic here lies in the fact that SQL knows all about its sprocs and lets us derive the parameter info. Also nifty here is the use of lookup tables - or in C#, DictionaryBase-derived collections - that lets us cache that info. So, when we ExecSProc, we get _sprocs[procName] which looks up the sproc name in our SProcList. If it's the first time calling this sproc, we NewSProc and add it to the lookup table. From there we just fill and go.

Thanks go to Ian Barker for pointing out the improved syntax via the params keyword. Barry Solomon provided ExecSProcDS, which is handy for returning datasets where your SELECT statement is in an sproc. This routine could be easily modified to return a DataRowCollection like QueryRows, which is a favorite of mine. My queries don't rely on user input (thus not vulnerable to sql injection attack - thanks Curt Drake for the heads up), so I have my SELECT statements in my C# code, and use QueryRows as follows:

string qryStr = "SELECT id, name FROM ...";
foreach (DataRow dr in _sqlConn.QueryRows(qryStr)) { ... }

string qryStr = "SELECT count(*) FROM ...";
if ((int)_sqlConn.QueryRows(qryStr)[0][0] == ...

A couple of readers have pointed out that all of this looks a whole lot like MS Application Data Block. I haven't tried it, and don't know exactly what is involved in getting it installed and working. Perhaps one of you will provide some info. *_*

Hope this helps!