关于SqlParameter中IN子句查询的问题

今天调试到方法中代码：

String hotelCodes =”’000000’,’111111’,’222222’”;
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 <= 0)
            {
                return null;
            }

返回数据一直不正确。

调试后，发现应该是ExecuteData生成的Sql有问题。于是想看看到底是怎么回事。我一步步的找到了SqlCommand下的BuildExecuteSql 方法。

其代码如下：

  1 private void BuildExecuteSql(CommandBehavior behavior, string commandText, SqlParameterCollection parameters, ref _SqlRPC rpc)
  2 {
  3     int num;
  4     int num2 = this.CountSendableParameters(parameters);
  5     if (num2 > 0)
  6     {
  7         num = 2;
  8     }
  9     else
 10     {
 11         num = 1;
 12     }
 13     this.GetRPCObject(num2 + num, ref rpc);
 14     rpc.ProcID = 10;
 15     rpc.rpcName = "sp_executesql";
 16     if (commandText == null)
 17     {
 18         commandText = this.GetCommandText(behavior);
 19     }
 20     SqlParameter parameter = new SqlParameter(null, ((commandText.Length << 1) <= 0x1f40) ? SqlDbType.NVarChar : SqlDbType.NText, commandText.Length);
 21     parameter.Value = commandText;
 22     rpc.parameters[0] = parameter;
 23     if (num2 > 0)
 24     {
 25         string str = this.BuildParamList(this._stateObj.Parser, this.BatchRPCMode ? parameters : this._parameters);
 26         parameter = new SqlParameter(null, ((str.Length << 1) <= 0x1f40) ? SqlDbType.NVarChar : SqlDbType.NText, str.Length);
 27         parameter.Value = str;
 28         rpc.parameters[1] = parameter;
 29         bool inSchema = CommandBehavior.Default != (behavior & CommandBehavior.SchemaOnly);
 30         this.SetUpRPCParameters(rpc, num, inSchema, parameters);
 31     }
 32 }
 33 
 34 其中有用到BuildParamList方法：
 35 
 36 internal string BuildParamList(TdsParser parser, SqlParameterCollection parameters)
 37 {
 38     StringBuilder builder = new StringBuilder();
 39     bool flag = false;
 40     bool isYukonOrNewer = parser.IsYukonOrNewer;
 41     int count = 0;
 42     count = parameters.Count;
 43     for (int i = 0; i < count; i++)
 44     {
 45         SqlParameter p = parameters[i];
 46         p.Validate(i, CommandType.StoredProcedure == this.CommandType);
 47         if (ShouldSendParameter(p))
 48         {
 49             if (flag)
 50             {
 51                 builder.Append(',');
 52             }
 53             builder.Append(p.ParameterNameFixed);
 54             MetaType internalMetaType = p.InternalMetaType;
 55             builder.Append(" ");
 56             if (internalMetaType.SqlDbType == SqlDbType.Udt)
 57             {
 58                 string udtTypeName = p.UdtTypeName;
 59                 if (ADP.IsEmpty(udtTypeName))
 60                 {
 61                     throw SQL.MustSetUdtTypeNameForUdtParams();
 62                 }
 63                 builder.Append(this.ParseAndQuoteIdentifier(udtTypeName, true));
 64             }
 65             else if (internalMetaType.SqlDbType == SqlDbType.Structured)
 66             {
 67                 string typeName = p.TypeName;
 68                 if (ADP.IsEmpty(typeName))
 69                 {
 70                     throw SQL.MustSetTypeNameForParam(internalMetaType.TypeName, p.ParameterNameFixed);
 71                 }
 72                 builder.Append(this.ParseAndQuoteIdentifier(typeName, false));
 73                 builder.Append(" READONLY");
 74             }
 75             else
 76             {
 77                 internalMetaType = p.ValidateTypeLengths(isYukonOrNewer);
 78                 builder.Append(internalMetaType.TypeName);
 79             }
 80             flag = true;
 81             if (internalMetaType.SqlDbType == SqlDbType.Decimal)
 82             {
 83                 byte actualPrecision = p.GetActualPrecision();
 84                 byte actualScale = p.GetActualScale();
 85                 builder.Append('(');
 86                 if (actualPrecision == 0)
 87                 {
 88                     if (this.IsShiloh)
 89                     {
 90                         actualPrecision = 0x1d;
 91                     }
 92                     else
 93                     {
 94                         actualPrecision = 0x1c;
 95                     }
 96                 }
 97                 builder.Append(actualPrecision);
 98                 builder.Append(',');
 99                 builder.Append(actualScale);
100                 builder.Append(')');
101             }
102             else if (internalMetaType.IsVarTime)
103             {
104                 byte num6 = p.GetActualScale();
105                 builder.Append('(');
106                 builder.Append(num6);
107                 builder.Append(')');
108             }
109             else if (((!internalMetaType.IsFixed && !internalMetaType.IsLong) && ((internalMetaType.SqlDbType != SqlDbType.Timestamp) && (internalMetaType.SqlDbType != SqlDbType.Udt))) && (SqlDbType.Structured != internalMetaType.SqlDbType))
110             {
111                 int size = p.Size;
112                 builder.Append('(');
113                 if (internalMetaType.IsAnsiType)
114                 {
115                     object coercedValue = p.GetCoercedValue();
116                     string str = null;
117                     if ((coercedValue != null) && (DBNull.Value != coercedValue))
118                     {
119                         str = coercedValue as string;
120                         if (str == null)
121                         {
122                             SqlString str4 = (coercedValue is SqlString) ? ((SqlString) coercedValue) : SqlString.Null;
123                             if (!str4.IsNull)
124                             {
125                                 str = str4.Value;
126                             }
127                         }
128                     }
129                     if (str != null)
130                     {
131                         int num4 = parser.GetEncodingCharLength(str, p.GetActualSize(), p.Offset, null);
132                         if (num4 > size)
133                         {
134                             size = num4;
135                         }
136                     }
137                 }
138                 if (size == 0)
139                 {
140                     size = internalMetaType.IsSizeInCharacters ? 0xfa0 : 0x1f40;
141                 }
142                 builder.Append(size);
143                 builder.Append(')');
144             }
145             else if ((internalMetaType.IsPlp && (internalMetaType.SqlDbType != SqlDbType.Xml)) && (internalMetaType.SqlDbType != SqlDbType.Udt))
146             {
147                 builder.Append("(max) ");
148             }
149             if (p.Direction != ParameterDirection.Input)
150             {
151                 builder.Append(" output");
152             }
153         }
154     }
155     return builder.ToString();
156 }

View Code

看到这里我有点明白为什么了。原来其内部根据参数构建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,’,'))";