非常实用：C#中SQL传参的自动设置和赋值
引子：某年某日，公司Web程序被注入，导致数据库一塌糊涂。CIO大怒，要求以后所有数据库的交互行为都只能用SP，于是乎，即使是没有任何参数的一条查询语句也必须去到DB中加一条SP...
项目实例：新入手一个项目，业务流程比较复杂，数据库交互很多，一条条的SP写的煞是烦人，尤其是在DAL中设置Command的Parameters，繁琐而又费眼睛，写多了很容易出错，因为不仅仅要考虑参数的个数，而且要考虑传的值阿对，值的类型阿对，对于Update之类的动作尤其烦人，一个Table，几十个字段，一个个写进去实在不是个简单的事情。这一块的典型代码如下：
典型设置SQL参数
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和你需要查询的实体。
GetConn()：获取Connection
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;
}
设置SqlCommand的参数
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来保存这个映射。修改后的第二个版本如下：
DAL层相关Code
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);
//...
}
Utility层的SetSqlParameters类
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;
}
}
}
}
}
Web层的Xml
<?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就不能通用了。
目前来看，程序可改进的地方仍然很多，欢迎各位牛人补充，指正。
