今天调试到方法中代码：
String hotelCodes =”’’,’’,’’”;
string sqltext ="select * from HotelMedalInfo where hotelCode in（@hotelCodes）";
SqlParameter[] parameters = {
new SqlParameter("@hotelCodes", hotelCodes)
};
DataTable dt = DBHelper.ExecuteData(sqlText, parameters); if (dt == null || dt.Rows.Count <= )
{
return null;
}
返回数据一直不正确。
调试后，发现应该是ExecuteData生成的Sql有问题。于是想看看到底是怎么回事。我一步步的找到了SqlCommand下的BuildExecuteSql 方法。
其代码如下：
private void BuildExecuteSql(CommandBehavior behavior, string commandText, SqlParameterCollection parameters, ref _SqlRPC rpc)
{
int num;
int num2 = this.CountSendableParameters(parameters);
if (num2 > )
{
num = ;
}
else
{
num = ;
}
this.GetRPCObject(num2 + num, ref rpc);
rpc.ProcID = ;
rpc.rpcName = "sp_executesql";
if (commandText == null)
{
commandText = this.GetCommandText(behavior);
}
SqlParameter parameter = new SqlParameter(null, ((commandText.Length << ) <= 0x1f40) ? SqlDbType.NVarChar : SqlDbType.NText, commandText.Length);
parameter.Value = commandText;
rpc.parameters[] = parameter;
if (num2 > )
{
string str = this.BuildParamList(this._stateObj.Parser, this.BatchRPCMode ? parameters : this._parameters);
parameter = new SqlParameter(null, ((str.Length << ) <= 0x1f40) ? SqlDbType.NVarChar : SqlDbType.NText, str.Length);
parameter.Value = str;
rpc.parameters[] = parameter;
bool inSchema = CommandBehavior.Default != (behavior & CommandBehavior.SchemaOnly);
this.SetUpRPCParameters(rpc, num, inSchema, parameters);
}
} 其中有用到BuildParamList方法： internal string BuildParamList(TdsParser parser, SqlParameterCollection parameters)
{
StringBuilder builder = new StringBuilder();
bool flag = false;
bool isYukonOrNewer = parser.IsYukonOrNewer;
int count = ;
count = parameters.Count;
for (int i = ; i < count; i++)
{
SqlParameter p = parameters[i];
p.Validate(i, CommandType.StoredProcedure == this.CommandType);
if (ShouldSendParameter(p))
{
if (flag)
{
builder.Append(',');
}
builder.Append(p.ParameterNameFixed);
MetaType internalMetaType = p.InternalMetaType;
builder.Append(" ");
if (internalMetaType.SqlDbType == SqlDbType.Udt)
{
string udtTypeName = p.UdtTypeName;
if (ADP.IsEmpty(udtTypeName))
{
throw SQL.MustSetUdtTypeNameForUdtParams();
}
builder.Append(this.ParseAndQuoteIdentifier(udtTypeName, true));
}
else if (internalMetaType.SqlDbType == SqlDbType.Structured)
{
string typeName = p.TypeName;
if (ADP.IsEmpty(typeName))
{
throw SQL.MustSetTypeNameForParam(internalMetaType.TypeName, p.ParameterNameFixed);
}
builder.Append(this.ParseAndQuoteIdentifier(typeName, false));
builder.Append(" READONLY");
}
else
{
internalMetaType = p.ValidateTypeLengths(isYukonOrNewer);
builder.Append(internalMetaType.TypeName);
}
flag = true;
if (internalMetaType.SqlDbType == SqlDbType.Decimal)
{
byte actualPrecision = p.GetActualPrecision();
byte actualScale = p.GetActualScale();
builder.Append('(');
if (actualPrecision == )
{
if (this.IsShiloh)
{
actualPrecision = 0x1d;
}
else
{
actualPrecision = 0x1c;
}
}
builder.Append(actualPrecision);
builder.Append(',');
builder.Append(actualScale);
builder.Append(')');
}
else if (internalMetaType.IsVarTime)
{
byte num6 = p.GetActualScale();
builder.Append('(');
builder.Append(num6);
builder.Append(')');
}
else if (((!internalMetaType.IsFixed && !internalMetaType.IsLong) && ((internalMetaType.SqlDbType != SqlDbType.Timestamp) && (internalMetaType.SqlDbType != SqlDbType.Udt))) && (SqlDbType.Structured != internalMetaType.SqlDbType))
{
int size = p.Size;
builder.Append('(');
if (internalMetaType.IsAnsiType)
{
object coercedValue = p.GetCoercedValue();
string str = null;
if ((coercedValue != null) && (DBNull.Value != coercedValue))
{
str = coercedValue as string;
if (str == null)
{
SqlString str4 = (coercedValue is SqlString) ? ((SqlString) coercedValue) : SqlString.Null;
if (!str4.IsNull)
{
str = str4.Value;
}
}
}
if (str != null)
{
int num4 = parser.GetEncodingCharLength(str, p.GetActualSize(), p.Offset, null);
if (num4 > size)
{
size = num4;
}
}
}
if (size == )
{
size = internalMetaType.IsSizeInCharacters ? 0xfa0 : 0x1f40;
}
builder.Append(size);
builder.Append(')');
}
else if ((internalMetaType.IsPlp && (internalMetaType.SqlDbType != SqlDbType.Xml)) && (internalMetaType.SqlDbType != SqlDbType.Udt))
{
builder.Append("(max) ");
}
if (p.Direction != ParameterDirection.Input)
{
builder.Append(" output");
}
}
}
return builder.ToString();
}
看到这里我有点明白为什么了。原来其内部根据参数构建sql时用到了‘，’。我们为参数中包含的逗号，应该是被它误解或者屏蔽了。
对此，我们可以创建一个函数，根据字符串hotelCodes，返回一个表。Sql条件根据函数的结果来判断。
1.创建函数
CREATE FUNCTION [dbo].[f_split](@c varchar(2000),@split varchar(2))
returns @t TABLE(col varchar(20))
AS
begin
while(charindex(@split,@c)<>0)
begin
INSERT @t(col) VALUES (substring(@c,1,charindex(@split,@c)-1))
SET @c = stuff(@c,1,charindex(@split,@c),'')
end
INSERT @t(col) VALUES (@c)
RETURN
end
GO
2.程序sql文本部分做如下修改：
string sqltext ="select * from HotelMedalInfo where hotelCode in (select * from dbo.f_split(@hotelCodes,’,'))";