2023年6月21日发(作者：)
关于SqlParameter中IN⼦句查询的问题今天调试到⽅法中代码：String hotelCodes =”’000000’,’111111’,’222222’”;string sqltext ="select * from HotelMedalInfo where hotelCode in（@hotelCodes）"; SqlParameter[] parameters = {new SqlParameter("@hotelCodes", hotelCodes)};DataTable dt = eData(sqlText, parameters); if (dt == null || <= 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 = endableParameters(parameters); 5 if (num2 > 0) 6 { 7 num = 2; 8 } 9 else 10 { 11 num = 1; 12 } 13 Object(num2 + num, ref rpc); 14 = 10; 15 e = "sp_executesql"; 16 if (commandText == null) 17 { 18 commandText = mandText(behavior); 19 } 20 SqlParameter parameter = new SqlParameter(null, (( << 1) <= 0x1f40) ? ar : , ); 21 = commandText; 22 ters[0] = parameter; 23 if (num2 > 0) 24 { 25 string str = aramList(this._, PCMode ? parameters : this._parameters); 26 parameter = new SqlParameter(null, (( << 1) <= 0x1f40) ? ar : , ); 27 = str; 28 ters[1] = parameter; 29 bool inSchema = t != (behavior & Only); 30 PCParameters(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 = nOrNewer; 41 int count = 0; 42 count = ; 43 for (int i = 0; i < count; i++) 44 { 45 SqlParameter p = parameters[i]; 46 te(i, Procedure == dType); 47 if (ShouldSendParameter(p)) 48 { 49 if (flag) 50 { 51 (','); 52 } 53 (terNameFixed); 54 MetaType internalMetaType = alMetaType; 55 (" "); 56 if (ype == ) 57 { 58 string udtTypeName = eName; 59 if (y(udtTypeName)) 60 { 61 throw tUdtTypeNameForUdtParams(); 62 } 63 (ndQuoteIdentifier(udtTypeName, true)); 64 } 65 else if (ype == ured) 66 { 67 string typeName = me; 68 if (y(typeName)) 69 { 70 throw tTypeNameForParam(me, terNameFixed); 71 } 72 (ndQuoteIdentifier(typeName, false)); 73 (" READONLY"); 74 } 75 else 76 { 77 internalMetaType = teTypeLengths(isYukonOrNewer); 78 (me); 79 } 80 flag = true; 81 if (ype == l) 82 { 83 byte actualPrecision = ualPrecision(); 84 byte actualScale = ualScale(); 85 ('('); 86 if (actualPrecision == 0) 87 { 88 if (oh) 89 { 90 actualPrecision = 0x1d; 91 } 92 else 93 { 94 actualPrecision = 0x1c; 95 } 96 } 97 (actualPrecision); 98 (','); 99 (actualScale);100 (')');101 }102 else if (ime)103 {104 byte num6 = ualScale();105 ('(');106 (num6);107 (')');108 }109 else if (((!d && !) && ((ype != amp) && (ype != ))) && (ured != internalMetaType.S110 {111 int size = ;112 ('(');113 if (Type)114 {115 object coercedValue = rcedValue();116 string str = null;117 if ((coercedValue != null) && ( != coercedValue))118 {119 str = coercedValue as string;120 if (str == null)121 {122 SqlString str4 = (coercedValue is SqlString) ? ((SqlString) coercedValue) : ;123 if (!)124 {125 str = ;126 }127 }128 }129 if (str != null)130 {131 int num4 = odingCharLength(str, ualSize(), , null);132 if (num4 > size)133 {134 size = num4;135 }136 }137 }138 if (size == 0)139 {140 size = InCharacters ? 0xfa0 : 0x1f40;141 }142 (size);143 (')');144 }145 else if (( && (ype != )) && (ype != ))146 {147 ("(max) ");148 }149 if (ion != )150 {151 (" output");152 }153 }154 }155 return ng();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
GO2.程序sql⽂本部分做如下修改： string sqltext ="select * from HotelMedalInfo where hotelCode in
(select * from dbo.f_split(@hotelCodes,’,'))";
2023年6月21日发(作者：)
关于SqlParameter中IN⼦句查询的问题今天调试到⽅法中代码：String hotelCodes =”’000000’,’111111’,’222222’”;string sqltext ="select * from HotelMedalInfo where hotelCode in（@hotelCodes）"; SqlParameter[] parameters = {new SqlParameter("@hotelCodes", hotelCodes)};DataTable dt = eData(sqlText, parameters); if (dt == null || <= 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 = endableParameters(parameters); 5 if (num2 > 0) 6 { 7 num = 2; 8 } 9 else 10 { 11 num = 1; 12 } 13 Object(num2 + num, ref rpc); 14 = 10; 15 e = "sp_executesql"; 16 if (commandText == null) 17 { 18 commandText = mandText(behavior); 19 } 20 SqlParameter parameter = new SqlParameter(null, (( << 1) <= 0x1f40) ? ar : , ); 21 = commandText; 22 ters[0] = parameter; 23 if (num2 > 0) 24 { 25 string str = aramList(this._, PCMode ? parameters : this._parameters); 26 parameter = new SqlParameter(null, (( << 1) <= 0x1f40) ? ar : , ); 27 = str; 28 ters[1] = parameter; 29 bool inSchema = t != (behavior & Only); 30 PCParameters(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 = nOrNewer; 41 int count = 0; 42 count = ; 43 for (int i = 0; i < count; i++) 44 { 45 SqlParameter p = parameters[i]; 46 te(i, Procedure == dType); 47 if (ShouldSendParameter(p)) 48 { 49 if (flag) 50 { 51 (','); 52 } 53 (terNameFixed); 54 MetaType internalMetaType = alMetaType; 55 (" "); 56 if (ype == ) 57 { 58 string udtTypeName = eName; 59 if (y(udtTypeName)) 60 { 61 throw tUdtTypeNameForUdtParams(); 62 } 63 (ndQuoteIdentifier(udtTypeName, true)); 64 } 65 else if (ype == ured) 66 { 67 string typeName = me; 68 if (y(typeName)) 69 { 70 throw tTypeNameForParam(me, terNameFixed); 71 } 72 (ndQuoteIdentifier(typeName, false)); 73 (" READONLY"); 74 } 75 else 76 { 77 internalMetaType = teTypeLengths(isYukonOrNewer); 78 (me); 79 } 80 flag = true; 81 if (ype == l) 82 { 83 byte actualPrecision = ualPrecision(); 84 byte actualScale = ualScale(); 85 ('('); 86 if (actualPrecision == 0) 87 { 88 if (oh) 89 { 90 actualPrecision = 0x1d; 91 } 92 else 93 { 94 actualPrecision = 0x1c; 95 } 96 } 97 (actualPrecision); 98 (','); 99 (actualScale);100 (')');101 }102 else if (ime)103 {104 byte num6 = ualScale();105 ('(');106 (num6);107 (')');108 }109 else if (((!d && !) && ((ype != amp) && (ype != ))) && (ured != internalMetaType.S110 {111 int size = ;112 ('(');113 if (Type)114 {115 object coercedValue = rcedValue();116 string str = null;117 if ((coercedValue != null) && ( != coercedValue))118 {119 str = coercedValue as string;120 if (str == null)121 {122 SqlString str4 = (coercedValue is SqlString) ? ((SqlString) coercedValue) : ;123 if (!)124 {125 str = ;126 }127 }128 }129 if (str != null)130 {131 int num4 = odingCharLength(str, ualSize(), , null);132 if (num4 > size)133 {134 size = num4;135 }136 }137 }138 if (size == 0)139 {140 size = InCharacters ? 0xfa0 : 0x1f40;141 }142 (size);143 (')');144 }145 else if (( && (ype != )) && (ype != ))146 {147 ("(max) ");148 }149 if (ion != )150 {151 (" output");152 }153 }154 }155 return ng();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
GO2.程序sql⽂本部分做如下修改： string sqltext ="select * from HotelMedalInfo where hotelCode in
(select * from dbo.f_split(@hotelCodes,’,'))";
发布评论