无生成器！ C# SQL 存储过程包装器

引言

如果你用 C# 处理过 SQL，你就知道它有多么做作。 大量的重复代码，需要一个更好的方法。 本网站上的几个人已经记录了这种痛苦，并提交了一个“更好”的方法 这里，这里，和 这里。 这些解决方案涉及一个中间代码生成步骤，需要你为每个存储过程（stored procedure）粘贴一堆代码，然后允许你像从 C# 中调用普通过程一样调用存储过程。 这个额外的代码生成步骤虽然有点酷，但并非没有痛苦。 这些解决方案有点笨拙，有些涉及外部依赖项。

一个更好的方法

有一种方法可以从 C# 中几乎像常规过程一样调用存储过程，无需任何中间生成、粘贴或依赖项，并且具有合理的性能（存储过程信息被缓存）。 下面是如何使用该代码

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

真的，就是这样！ 你刚刚调用了两个存储过程；一个名为 "AddStaff"，带有两个输入参数，然后 "AddRoom"，传入一个参数。 与代码生成方法相比，唯一的缺点是，由于参数可以是任何类型，所以我们直到运行时才会知道是否存在类型不匹配，或者我们是否传递了错误的参数数量。 唉。 另请注意，无论此方法还是自动代码生成都不会让你保持最新 - 如果你更改存储过程中参数的数量、类型或顺序，你必须记住更新你的 C# 代码。

你的存储过程是否返回值和/或有输出参数？ 好吧，如果你的存储过程返回一个错误代码并设置一个输出 Param @roomId，那么它可能看起来像这样

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

你问它要多少钱？ 大约 130 行代码，你可以复制、粘贴并忘记它。 就在这里

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];
            } }
        }
    }
}

仅供参考，以展示如何查询 SQL 获取参数信息（你不需要此代码，除非，正如一位读者指出的，你位于 SQL 事务中，该框架显然无法处理）

#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

这里的魔力在于 SQL 了解其所有的存储过程，并允许我们推导出参数信息。 这里的另一个巧妙之处是使用查找表 - 或 C# 中，派生自 DictionaryBase 的集合 - 这使我们可以缓存该信息。 因此，当我们 ExecSProc 时，我们得到 _sprocs[procName]，它在我们的 SProcList 中查找存储过程名称。 如果这是第一次调用此存储过程，我们 NewSProc 并将其添加到查找表。 从那里我们只需填充并继续。

感谢 Ian Barker 指出通过 params 关键字改进的语法。 Barry Solomon 提供了 ExecSProcDS，它对于返回 SELECT 语句位于存储过程中的数据集非常有用。 可以轻松修改此例程以返回 DataRowCollection，如 QueryRows，这是我最喜欢的。 我的查询不依赖于用户输入（因此不易受到 SQL 注入攻击 - 感谢 Curt Drake 的提示），因此我在 C# 代码中包含 SELECT 语句，并按如下方式使用 QueryRows

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] == ...

一些读者指出，所有这些看起来很像 MS 应用程序数据块。 我没有尝试过，也不知道安装和使用它需要做些什么。 也许你们中的一个会提供一些信息。 *_*

希望这有帮助！