非常实用：C#中SQL传参的自动设置和赋值

引子：某年某日，公司Web程序被注入，导致数据库一塌糊涂。CIO大怒，要求以后所有数据库的交互行为都只能用SP，于是乎，即使是没有任何参数的一条查询语句也必须去到DB中加一条SP...

　　项目实例：新入手一个项目，业务流程比较复杂，数据库交互很多，一条条的SP写的煞是烦人，尤其是在DAL中设置Command的Parameters，繁琐而又费眼睛，写多了很容易出错，因为不仅仅要考虑参数的个数，而且要考虑传的值阿对，值的类型阿对，对于Update之类的动作尤其烦人，一个Table，几十个字段，一个个写进去实在不是个简单的事情。这一块的典型代码如下：

 

private readonly string _mConnectionString = ConstParameter.ConnectionString; //获得ConnectionString，ConstParameter是用来根据Web.Config来获取对应开发环境的String
var conn = new SqlConnection(_mConnectionString); //New一个Connection
cmd = conn.CreateCommand();  //New一个Command
cmd.CommandType = CommandType.StoredProcedure;  //设置 Command 类型
cmd.CommandText = “Store Procedure Name”; //设置 SP Name
//以下是本文要解决的主要代码段
 .....
cmd.Parameters.Add(new SqlParameter("@Para1" , SqlDbType"));
                        cmd.Parameters["@Para1"gt;].Value = Para1Value;
cmd.Parameters.Add(new SqlParameter("@Para2" , SqlDbType"));
                        cmd.Parameters["@Para2"].Value = Para2Value;
cmd.Parameters.Add(new SqlParameter("@Para3" , SqlDbType"));
                        cmd.Parameters["@Para3"].Value = Para3Value;
.....
 
 

 

　　过程：很痛苦的很煎熬的一条条的SP写完了，调试通过，进入UAT的阶段，我也相对轻松了。逛逛博客园，偶然看到一篇博文  Attribute在.NET编程中的应用（三） ，发现和我遇到的情况基本一样，完整的看完了，感觉用Attribute确实比较另类，看看实现方法，主要也是运用了反射，于是乎，我想何不用反射写个简单的类呢？实现起来应该不复杂的吧？ 两个小时后，我交出了第一个版本，废话少说，看代码先：

 

    public class ModelTestEvents
    {
        public int TestEventID { get; set; }
        public string EpisodeStatus { get; set; }
        public string SerialNumber { get; set; }
        public string MaterialID { get; set; }
        public int TestSeqNo { get; set; }
        public string MaturityAtTest { get; set; }
        public string TestStatus { get; set; }
        public DateTime CreateDateTime { get; set; }
        public string Tester { get; set; }
        public string TestStation { get; set; }
        public string WorkOrderID { get; set; }
        public string TestComments { get; set; }
        public string TestRev { get; set; }
    }

 

下面自动设置SQL参数需要调用的方法 

SqlCommand cmd;
SqlConnection conn = GetConn(mConnectionString, out cmd, te);
//GetConn() 用来返回一个设置好了SqlCommand的SqlConnection，四个参数分别是ConnectionString，SqlCommand和你需要查询的实体。 

 

private SqlConnection GetConn(string mConnectionString, out SqlCommand cmd, ModelTestEvents te)
        {
            var conn = new SqlConnection(mConnectionString);
            cmd = conn.CreateCommand();
 
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "TotalFailedTe";
 
            SetParameters(cmd, te);return conn;
        }

 

private static void SetParameters(SqlCommand cmd, object obj)
        {
            System.Reflection.PropertyInfo[] properties = obj.GetType().GetProperties();
 
 foreach (var propertyInfo in properties)
            {
                object value = propertyInfo.GetValue(obj, null);
                if (value != null)
                {
                    SqlDbType st = new SqlDbType();
 
 bool isAdd = false;
                    switch (propertyInfo.PropertyType.ToString())
                    {
                        case "System.SmallInt":
                            st = SqlDbType.SmallInt;
                            if (Convert.ToInt32(value) != 0)
                                isAdd = true;
                            break;
                        case "System.Int32":
                            st = SqlDbType.Int;
                            if (Convert.ToInt32(value) != 0)
                                isAdd = true;
                            break;
                        case "System.String":
                            st = SqlDbType.VarChar;
                            isAdd = true;
                            break;
                        case "System.DateTime":
                            st = SqlDbType.DateTime;
                            if (value.ToString() != "1/1/0001 12:00:00 AM")
                                isAdd = true;
                            break;
                        case "System.Bool":
                            st = SqlDbType.Bit;
                            isAdd = true;
                            break;
                        case "System.Guid":
                            st = SqlDbType.UniqueIdentifier;
                            isAdd = true;
                            break;
                    }
                    if (!isAdd) continue;
                    cmd.Parameters.Add(new SqlParameter("@" + propertyInfo.Name, st));
                    cmd.Parameters["@" + propertyInfo.Name].Value = value;
                }
            }
        }

　　思考：以上是很不成熟的第一个版本，也就停留在能够调试通过，能够不出错的运行下去而已，正好下班了。回家后，想了一下，觉得这段代码虽然实现了我的开始的需求（根据传入的实体和SP的名字，自动设置SQL的传参），但是可定制性不强，SetParameters()复杂且不易维护，并且性能也不是很好，还是有比较大的优化余地。理了下思路后，决定用Xml做一个Map来规范SystemType的Type和SqlDbType以及过滤条件的映射关系，并且用Cache来保存这个映射。修改后的第二个版本如下：

 

private readonly string _mConnectionString = ConstParameter.ConnectionString;
public DataSet GetTestEvents(ModelTestEvents te,string spName,out string errMsg)
        {
            errMsg = "";
            var ds = new DataSet();
            var conn = new SqlConnection(_mConnectionString);
            SqlCommand cmd;
            SetSqlParameters.GetCmd(conn, out cmd, te, spName);
 
 //...
        }

 

    public class SetSqlParameters
    {
 
 public static SqlConnection GetCmd(SqlConnection conn, out SqlCommand cmd, object obj, string spName)
        {
            cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = spName;
            SetParameters(cmd, obj);
            return conn;
        }
 
 private static void SetParameters(SqlCommand cmd, object obj)
        {
            System.Reflection.PropertyInfo[] properties = obj.GetType().GetProperties();
            foreach (var propertyInfo in properties)
            {
                object value = propertyInfo.GetValue(obj, null);
                if (value != null)
                {
                    if (HttpContext.Current.Cache["SqlParasMap"] == null)
                    {
                        var ds = new DataSet();
                        ds.ReadXml(HttpContext.Current.Server.MapPath("SqlParasMap.xml"));
                        HttpContext.Current.Cache["SqlParasMap"] = ds.Tables[0];
                    }
                    DataRow[] dr =
                        ((DataTable) HttpContext.Current.Cache["SqlParasMap"]).Select("System='" +
                                                                                      propertyInfo.PropertyType.Name +
                                                                                      "'");
                    if (dr.Count() > 0 && !value.ToString().Equals(dr[0]["Filter"]))
                    {
                        cmd.Parameters.Add(new SqlParameter("@" + propertyInfo.Name, dr[0]["SqlDbType"]));
                        cmd.Parameters["@" + propertyInfo.Name].Value = value;
                    }
                }
            }
        }
    }

 

<?xml version="1.0" encoding="utf-8" ?>
<Types>
  <Type>
    <System>SmallInt</System>
    <SqlDbType>SmallInt</SqlDbType>
    <Filter>0</Filter>
  </Type>
  <Type>
    <System>Int32</System>
    <SqlDbType>Int</SqlDbType>
    <Filter>0</Filter>
  </Type>
  <Type>
    <System>String</System>
    <SqlDbType>Varchar</SqlDbType>
    <Filter>Null</Filter>
  </Type>
  <Type>
    <System>DateTime</System>
    <SqlDbType>DateTime</SqlDbType>
    <Filter>1/1/0001 12:00:00 AM</Filter>
  </Type>
  <Type>
    <System>Boolean</System>
    <SqlDbType>Bit</SqlDbType>
    <Filter>Null</Filter>
  </Type>
  <Type>
    <System>Guid</System>
    <SqlDbType>UniqueIdentifier</SqlDbType>
    <Filter>Null</Filter>
  </Type>
</Types>

　　补充：关于这个这个Xml有必要说两句，<System>节点定义.Net类型，<SqlDbType>节点定义对应的Sql类型，<Filter>节点定义需要过滤的，也就是不需要加入参数的值，比如例中对于String没有限制，对于Int类型不为0。这里需要注意的是，如果有bool类型，在定义实体属性的时候，需要定义成Bool?，并且Set的时候把Bool值的起始值设为Null，不然在程序中无法判断，到底是用户设置的还是Bool的初始值。

其它一些使用说明：

（1） 实体类中的属性名字一定要和SQL中的参数名字一致

（2） 对于需要复杂定义的实体属性没法用，因为无法判定到底属性值是否为用户设置，当然你可以在Xml中设置Filter，不过如果这样搞，你的Xml就不能通用了。

 

　　目前来看，程序可改进的地方仍然很多，欢迎各位牛人补充，指正。