in ms sql 集合参数传递_SqlHelper中IN集合场景下的参数处理

我手头有个古老的项目，持久层用的是古老的ADO.net。前两天去昆明旅游，其中的一个景点是云南民族村，通过导游介绍知道了一个古老的民族——基诺族，“基”在这个族内代表舅舅，“基诺”意为“跟在舅舅后边”，加以引申即为“尊崇舅舅的民族”，很有意思吧，这是我国最后一个被发现并确认下来的少数民族，即第56个民族。  项目里的ado.net和基诺族一样古老。

话说，项目里数据访问层，好多都是拼的sql，这给sql注入提供了可乘之机，为了系统安全，决定在有限的时间内，将它改成参数化。

其中，有个根据多个订单号查询支付单的方法，签名如下：

public DataTable GetAlipayNotifyRecords(AlipayPaymentStatus status, params string[] trade_no)

那么，问题来了，因为sql里有in， 而 in(@no)的方式是行不通的。

怎么办呢？  首先想到的是对参数做处理：

public DataTable GetAlipayNotifyRecords(AlipayPaymentStatus status, params string[] trade_no)

{string sql = @"select * from T_AlipayNotityRecord where trade_status=@trade_status and trade_no in(@trade_no)";//string inValue = "'" + string.Join("','", trade_no) + "'";//= string.Join(",", trade_no)

string inValue = "";

trade_no.ToList().ForEach(no=> inValue += "union all select '" + no+"'");

inValue= inValue.Substring("union all".Length);

List paramList = new List()

{new SqlParameter("@trade_status",status.ToString()),new SqlParameter("@trade_no",inValue),

};var ds =SqlHelper.SqlDataSet(ConfigFile.PayCenterConnection, sql, CommandType.Text, paramList.ToArray());if (ds == null || ds.Tables.Count == 0)return null;return ds.Tables[0];

}

经测试，无效。经分析可知，sqlhelper会把你参数值当成字符串，不会对其做转义。所以，不管怎么对参数值处理，都还是一串字符串。

按这样的原理往下想，只能是将单号分开来传递给sql了。那么正好sql的in可以通过如下几种方式等效实现：

sql里有临时表，可以in一个临时表--这时，可以考虑and trade_no in(select @p1 union all select @p2 union all...)的方式

把sql的in集合，转换为一个用or拼接起来的集合---即，and (trade_no=@p1 or trade_no=@p2 or trade_no=@p3 or...)

如下代码是按照后者的思路解决了这个问题：

public DataTable GetAlipayNotifyRecords(AlipayPaymentStatus status, params string[] trade_no)

{string sql = @"select * from T_AlipayNotityRecord where trade_status=@trade_status and ({0})";

List paramList = new List()

{new SqlParameter("@trade_status",status.ToString()),

};string sql1 = "";for (int i=0;i

{

sql1+= "or trade_no=@no" +i;

paramList.Add(new SqlParameter("@no" +i, trade_no[i]));

}

sql= string.Format(sql, sql1.Substring("or".Length));var ds =SqlHelper.SqlDataSet(ConfigFile.PayCenterConnection, sql, CommandType.Text, paramList.ToArray());if (ds == null || ds.Tables.Count == 0)return null;return ds.Tables[0];

}

-----2016-12-13 09:39:32

【续】方法总比问题多

今天早上刷牙时，灵光一现，对于昨天的方案，不需要通过借助or或union的方式来更改sql的in了，即，可以直接生成如下的sql语句：

select * from T_AlipayNotityRecord where trade_status=@trade_status and trade_no in(@no0,@no1,...)

程序代码在上面的基础上稍做处理：

public DataTable GetAlipayNotifyRecords(AlipayPaymentStatus status, params string[] trade_no)

{string sql = @"select * from T_AlipayNotityRecord where trade_status=@trade_status and trade_no in({0})";

List paramList = new List()

{new SqlParameter("@trade_status",status.ToString()),

};string sql1 = "";for (int i = 0; i < trade_no.Length; i++)

{

sql1+= ",@no" +i;

paramList.Add(new SqlParameter("@no" +i, trade_no[i]));

}

sql= string.Format(sql, sql1.Substring(",".Length));var ds =SqlHelper.SqlDataSet(ConfigFile.PayCenterConnection, sql, CommandType.Text, paramList.ToArray());if (ds == null || ds.Tables.Count == 0)return null;return ds.Tables[0];

}

这样子生成的sql就很直观了。比上面方案的还简短。