非常实用：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就不能通用了。

 

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

转载于:https://www.cnblogs.com/flashbar/archive/2010/06/27/1766127.html