The day before yesterday reported a bug, and an analysis was very strange.
First, there is a problem on IIS SQL Database, but IDE SQL Database is no problem.
Second, the original Error Message is
System.data.sqlclient.sqlexception: a Severe Error Occurred on The Current Command. The resultS, if any, shouth be distth
, This, according to Microsoft Knowledge Base Article - 827366, it should be the second reason
You do not specify an explicit SQLDbType enumeration when you create a SqlParameter object. When you do not specify an explicit SQLDbType, the Microsoft .NET Framework Data Provider for SQL Server (SqlClient) tries to select the correct SQLDbType based on the data that is passed Sqlclient is not success.
, But the original program is used
Void system.data.sqlclient.sqlcommandbuilder.deriveParameters (SqlCommand)
The parameters configured should be not wrong?
The MS KB827366 in the upper side specially mentioned the TEXT type as an example. Sure enough, there is a TEXT type parameter in the Stored Procedure, which will be removed, and it is okay.
I know that I have to know how it is, call up the reflector, analyze it, it turns out like this ...
First, the program is to analyze the parameters of SQLCommand:
Dim cn As New SqlConnection (connectionString) Dim cmd As SqlCommand = New SqlCommand (spName, cn) Dim discoveredParameters () As SqlParameterTry cn.Open () cmd.CommandType = CommandType.StoredProcedure SqlCommandBuilder.DeriveParameters (cmd) If Not includeReturnValueParameter Then cmd. Parameters.removeat (0) end if discoveredParameters = new sqlparameter (cmd.parameters.count - 1) {} cmd.parameters.copyto (discoveredparameters, 0)
The result of the result is: VoidParameters (SQLCommand): void, and reflector: void, the result is:
Public Static Void DeriveParameters (SqlCommand Command) {
SqlConnection.sqlclientPermission.Demand (); if (Command == Null) {
Throw ADP.Argumentnull ("Command");} Command.deriveParameters ();
Ok, chase, system.data.sqlclient.sqlcommand.deriveParameters (): void is: Internal void deactParameters () {
SQLParameter Parameter1; Object Obj1; CommandType Type1 = this.commandType; IF (Type1! = CommandType.text) {
IF (Type1 == CommandType.StoredProcedure) {
Goto label_0037;} if (Type1 == CommandType.tableDirect) {
Goto label_0024;} goto label_002b;} throw adp.deriveParametersNotsupported (this); label_0024:
Throw adp.deriveParametersNotsupported (this); label_002b:
Throw ad.invalidcommandtype (this.commandtype); label_0037:
This.ValidateCommand ("DeriveParameters", False; string [] array1 = adp.parseProcedureName (this.commandtext); sqlcommand command1 = null; if (array1 [1]! = null) {
this.cmdtext = String.concat ("[", array1 [1], "] .. sp_procedure_params_rowset"); if (array1 [0]! = null) {
This.cmdtext = String.concat (Array1 [0], ".", this.cmdtext);} command1 = new sqlcommand (this.cmdtext, this.connection);} else {
command1 = new SqlCommand ( "sp_procedure_params_rowset", this.Connection);} command1.CommandType = CommandType.StoredProcedure; command1.Parameters.Add (new SqlParameter ( "@ procedure_name", SqlDbType.NVarChar, 255)); command1.Parameters [0 ] .Value = array11 [3]; sqldatarader reader1 = null; arraylist list1 = new arraylist (); try {
Reader1 = Command1.executeReader (); parameter1 = null; while (reader1.read ()) {
parameter1 = new SqlParameter (); parameter1.ParameterName = ((string) reader1 [ "PARAMETER_NAME"]); parameter1.SqlDbType = MetaType.GetSqlDbTypeFromOleDbType (((short) reader1 [ "DATA_TYPE"]), ((string) reader1 [ " TYPE_NAME "])); obj1 = reader1 [" CHARACTER_MAXIMUM_LENGTH "]; if ((obj1 as int) = 0) {parameter1.Size = ((int) obj1);}! parameter1.Direction = this.ParameterDirectionFromOleDbDirection (((short Reader1 ["parameter_type"]); if (parameter1.sqldbtype == sqldbtype.decimal) {
Parameter1.scale = ((Byte) ((Short) Reader1 ["Numeric_scale"]) & 255)); parameter1.Precision = ((Byte) ((short) Reader1 ["Numeric_Precision"] & 255)) } list1.add (parameter1);}} finally {
IF (reader1! = null) {
Reader1.close ();} Command1.connection = null;} if (list1.count == 0) {
Throw ad.nostoredProcedureexists (this.commandtext);} this.parameters.clear (); foreach (object obj2 in list1) {
THIS._PARAMETERS.ADD (OBJ2);}}
It turned out to be - first use the system stored procedure sp_procedure_params_rowset in SQL Database (two implementations on Google's website),
Implementation one:
/ * Procedure for 8.0 servers * / create procedure sp_procedure_params_rowset (@procedure_name sysname, @group_number int = 1, @procedure_schema sysname = null, @parameter_name sysname = null) as select PROCEDURE_CATALOG = db_name (), PROCEDURE_SCHEMA = user_name (o.uid) , Procedure_name = Convert (NVARCHAR (134), O.NAME ';' LTRIM (Str (C.Number, 5))), parameter_name = c.name, Ordinal_Position = Convert (Smallint, C.Colid), parameter_type = convert (smallint, 1 c.isoutparam), PARAMETER_HASDEFAULT = convert (tinyint, 0), PARAMETER_DEFAULT = convert (nvarchar (255), null), IS_NULLABLE = convert (bit, ColumnProperty (c.id, c.name, 'AllowsNull ')), DATA_TYPE = d.oledb_data_type, CHARACTER_MAXIMUM_LENGTH = convert (int, case when d.oledb_data_type = 129 / * DBTYPE_STR * / or d.oledb_data_type = 128 / * DBTYPE_BYTES * / then coalesce (d.column_size, c.length) When D.OLEDB_DATA_TYPE = 130 / * DBTYPE_WSTR * / THEN COALESCE (D.COLUMN_SIZE, C.LENGTH / 2) ELSE NULL end), CHARACTER_OCTET_LENGTH = convert (int, case when d.oledb_data_type = 129 / * DBTYPE_STR * / or d.oledb_data_type = 128 / * DBTYPE_BYTES * / then coalesce (d.column_size, c.length) when d.oledb_data_type = 130 / * DBTYPE_WSTR * / then coalesce (d.column_size * 2, c.length) else null end), NUMERIC_PRECISION = convert (smallint, case when d.oledb_data_type = 131 / * DBTYPE_NUMERIC * / then c.prec when (d.fixed_prec_scale = 1 or D.OLEDB_DATA_TYPE = 5 or D.OLDB_DATA_TYPE = 4) Then D.Data_Precision Else Null End, Numeric_scale =
convert (smallint, case when d.oledb_data_type = 131 / * DBTYPE_NUMERIC * / then c.scale else null end), DESCRIPTION = convert (nvarchar (1), null), TYPE_NAME = d.type_name, LOCAL_TYPE_NAME = d.local_type_name from sysobjects o, syscolumns c, master.dbo.spt_provider_types d, system = @procedure_name and ('p', 'tf', 'ip ") or (len (c.name)> 0 And o.type = 'fn')))) and (@procedure_schema is null or @procedure_schema = user_name (o.UID)) and o.id = C.ID and ((c.Number = @Group_number and o.type = ' P ') or (c.Number = 0 and o.type =' fn ') or (c.Number = 1 and o.TYPE IN (' TF ',' IF '))) and c.XTYPE = D.SS_DTYPE And C.LENGTH = CASE WHEN D.FIXLEN> 0 THEN D.FIXLEN ELSE C.LENGTH End and C.XUSERTYPE = T.XUSERTYPE AND (@Parameter_name = c.name) Union All Select / * Return Value Row * / procedure_catalog = db_name (), procedure_schema = user_name (o.UID), Procedure_name = Convert (NVARCHAR (134), O.NAME ';' LTRIM (Str (Str (C.Number, 5) )), PARAMETER_NAME = convert (sysname, '@ RETURN_VALUE'), ORDINAL_POSITION = convert (smallint, 0), PARAMETER_TYPE = convert (smallint, 4 / * DBPARAMTYPE_RETURNVALUE * /), PARAMETER_HASDEFAULT = convert (tinyint, 0), PARAMETER_DEFAULT = convert (nvarchar (255), null), IS_NULLABLE = convert (bit, 0), DATA_TYPE = convert (smallint, 3 / * DBTYPE_I4 * /), CHARACTER_MAXIMUM_LENGTH = convert (int, null), CHARACTER_OCTET_LENGTH = convert (int, null), Numeric_Precision = Convert (Smallint, 10), Numeric_scale =
convert (smallint, null), DESCRIPTION = convert (nvarchar (1), null), TYPE_NAME = convert (sysname, N'int '), LOCAL_TYPE_NAME = convert (sysname, N'int') from sysobjects o, syscomments c where o .name = @procedure_name and o.id = c.id and c.number = @group_number and c.colid = 1 and o.type = 'P' / * Just Procedures * / and (@procedure_schema is null or @procedure_schema = user_name (o.uid)) and (@parameter_name is null or @parameter_name = '@RETURN_VALUE') UNION ALL SELECT / * UDF return value row * / PROCEDURE_CATALOG = db_name (), PROCEDURE_SCHEMA = user_name (o.uid), PROCEDURE_NAME = Convert (NVARCHAR (134), O.NAME ';' LTRIM (STR (C.Number, 5))) = convert (smallint, 4 / * DBPARAMTYPE_RETURNVALUE * /), PARAMETER_HASDEFAULT = convert (tinyint, 0), PARAMETER_DEFAULT = convert (nvarchar (255), null), IS_NULLABLE = convert (bit, c.isnullable), DATA_TYPE = d.oledb_data_type, CHARACTER_MAXIMUM_LENGTH = convert (int, case when d.oledb_data_type = 129 / * DBTYPE_STR * / or d.oledb_data_type = 128 / * DBTYPE_BYTES * / then coalesce (d.column_size, c.length) when d.oledb_data_type = 130 / * DBTYPE_WSTR * / then coalesce (d.column_size, c.length / 2) else null end), CHARACTER_OCTET_LENGTH = convert (int, case when d.oledb_data_type = 129 / * DBTYPE_STR * / or d.oledb_data_type =
128 / * DBTYPE_BYTES * / then coalesce (d.column_size, c.length) when d.oledb_data_type = 130 / * DBTYPE_WSTR * / then coalesce (d.column_size * 2, c.length) else null end), NUMERIC_PRECISION = convert ( smallint, case when d.oledb_data_type = 131 / * DBTYPE_NUMERIC * / then c.prec when (d.fixed_prec_scale = 1 or d.oledb_data_type = 5 or d.oledb_data_type = 4) then d.data_precision else null end), NUMERIC_SCALE = convert (smallint, case when d.oledb_data_type = 131 / * DBTYPE_NUMERIC * / then c.scale else null end), DESCRIPTION = convert (nvarchar (1), null), TYPE_NAME = d.type_name, LOCAL_TYPE_NAME = d.local_type_name FROM sysobjects o , Syscolumns c, master.dbo.spt_provider_types d, system_types d, system @procedure_name and o.id = C.ID and c.Number = 0 and c.colid = 0 and o.type = 'fn' / * UDF scalar functions * / and c.XTYPE = D.ss_dtype and c.Length = Case When D.FIXLEN> 0 THEN D.FIXLEN ELSE C.LENGTH End and C.XUSERTYPE = T.XUSERTYP e and (@procedure_schema is null or @procedure_schema = user_name (o.uid)) and (@parameter_name is null or @parameter_name = '@RETURN_VALUE') UNION ALL SELECT / * UDF table value row * / PROCEDURE_CATALOG = db_name (), PROCEDURE_SCHEMA = user_name (o.uid), PROCEDURE_NAME = convert (nvarchar (134), o.name ';' ltrim (str (c.number, 5))), PARAMETER_NAME = convert (sysname, '@ TABLE_RETURN_VALUE') , Ordinal_Position = Convert (Smallint, 0), Parameter_Type = Convert (Smallint, 4 / * dbparamType_ReturnValue * /), Parameter_Hasdefault =
convert (tinyint, 0), PARAMETER_DEFAULT = convert (nvarchar (255), null), IS_NULLABLE = convert (bit, 0), DATA_TYPE = convert (smallint, 0), / * DBTYPE_EMPTY * / CHARACTER_MAXIMUM_LENGTH = convert (int, case when d.oledb_data_type = 129 / * DBTYPE_STR * / or d.oledb_data_type = 128 / * DBTYPE_BYTES * / then coalesce (d.column_size, c.length) when d.oledb_data_type = 130 / * DBTYPE_WSTR * / then coalesce (d.column_size, c.length / 2) else null end), CHARACTER_OCTET_LENGTH = convert (int, case when d.oledb_data_type = 129 / * DBTYPE_STR * / or d.oledb_data_type = 128 / * DBTYPE_BYTES * / then coalesce (d.column_size, c.length ) when d.oledb_data_type = 130 / * DBTYPE_WSTR * / then coalesce (d.column_size * 2, c.length) else null end), NUMERIC_PRECISION = convert (smallint, case when d.oledb_data_type = 131 / * DBTYPE_NUMERIC * / then c .prec when (D.fixed_prec_scale = 1 or D.OLEDB_DATA_TYPE = 5 or D.OLEDB_DATA_TYPE = 4) THEN D.DATA_P recision else null end), NUMERIC_SCALE = convert (smallint, case when d.oledb_data_type = 131 / * DBTYPE_NUMERIC * / then c.scale else null end), DESCRIPTION = convert (nvarchar (50), N'Result table returned by table valued function '), TYPE_NAME = N'table', LOCAL_TYPE_NAME = N'table 'from sysobjects o, syscolumns c, master.dbo.spt_provider_types d where o.name = @procedure_name and o.id = c.id and c.number = 0 and c.colid = 1 and o.TYPE IN ('TF', 'IF') / * udf table functions * / and c.XTYPE = D.ss_dtype and c.Length = Case When D.FIXLEN>
0 then d.fixlen else c.length end and (@procedure_schema is null or @procedure_schema = user_name (o.uid)) and (@parameter_name is null or @parameter_name = '@TABLE_RETURN_VALUE') order by 2, 3, 5 to achieve two:
create procedure sp_procedure_params_rowset (@procedure_schema sysname = null, @parameter_name sysname = null) as select PROCEDURE_CATALOG = db_name (), PROCEDURE_SCHEMA = user_name (o.uid), PROCEDURE_NAME = convert (nvarchar (134), o.name ';' Ltrim (Str (C.Number, 5)) ), PARAMETER_DEFAULT = convert (nvarchar (255), null), IS_NULLABLE = convert (bit, ColumnProperty (c.id, c.name, 'AllowsNull')), DATA_TYPE = d.oledb_data_type, CHARACTER_MAXIMUM_LENGTH = convert (int, case when d.oledb_data_type = 129 / * DBTYPE_STR * / or d.oledb_data_type = 128 / * DBTYPE_BYTES * / then coalesce (d.column_size, c.length) when d.oledb_data_type = 130 / * DBTYPE_WSTR * / then coalesce (d.column_size, C.LENGTH / 2) ELSE NULL END, CHARACTER_OCTET_LENGTH = Convert (int, Case When D.OLEDB_DATA_TYPE = 129 / * DBTYPE_STR * / or d.oledb_data_type = 128 / * DBTYPE_BYTES * / then coalesce (d.column_size, c.length) when d.oledb_data_type = 130 / * DBTYPE_WSTR * / then coalesce (d.column_size * 2, c.length ) else null end), NUMERIC_PRECISION = convert (smallint, case when d.oledb_data_type = 131 / * DBTYPE_NUMERIC * / then c.prec when (d.fixed_prec_scale = 1 or d.oledb_data_type = 5 or d.oledb_data_type = 4) then d .DATA_PRECISION ELSE NULL END, NUMERIC_SCALE = Convert (Smallint, Case When D.OLEDB_DATA_TYPE =
131 / * DBTYPE_NUMERIC * / then c.scale else null end), DESCRIPTION = convert (nvarchar (1), null), TYPE_NAME = d.type_name, LOCAL_TYPE_NAME = d.local_type_name from sysobjects o, syscolumns c, master.dbo.spt_provider_types D, SYSTYPES T where (O.Type IN ('P', 'TF', 'IF') or (Len (C.Name)> 0 and o.type = 'fn')) and (@Procedure_Schema Is Null OR) @Procedure_schema = user_name (o.UID)) and o.id = C.ID and (o.Type = 'p' or (c.Number = 0 and o.type = 'fn') or (c.Number = 1 And O.Type in ('TF', 'IF'))))) and c.XTYPE = D.SS_DTYPE AND C.LENGTH = Case When D.FIXLEN> 0 THEN D.FIXLEN ELSE C.LENGTH End and C.XUSERTYPE = t.xusertype and (@parameter_name is null or @parameter_name = c.name) UNION ALL SELECT / * return value row * / PROCEDURE_CATALOG = db_name (), PROCEDURE_SCHEMA = user_name (o.uid), PROCEDURE_NAME = convert (nvarchar (134) , oname ';' LTRIM (Str (C.Number, 5))), parameter_name = convert (sysname, '@ Return_Value'), Ordinal_Position = Convert (Smallint, 0), Parameter_Type = convert (smallint, 4 / * DBPARAMTYPE_RETURNVALUE * /), PARAMETER_HASDEFAULT = convert (tinyint, 0), PARAMETER_DEFAULT = convert (nvarchar (255), null), IS_NULLABLE = convert (bit, 0), DATA_TYPE = convert (smallint, 3 / * DBTYPE_I4 * /), CHARACTER_MAXIMUM_LENGTH = convert (int, null), CHARACTER_OCTET_LENGTH = convert (int, null), NUMERIC_PRECISION = convert (smallint, 10), NUMERIC_SCALE = convert (smallint, null), DESCRIPTION = convert (nvarchar (1) , NULL, TYPE_NAME = Convert (sysname, n'int '), local_type_name =
Convert (sysname, n'int ') from sysobjects o, syscomments c where o.type =' p '/ * just procedures * / and o.id = c.id and c.colid = 1 and (@procedure_schema is null OR) @procedure_schema = user_name (o.uid)) and (@parameter_name is null or @parameter_name = '@RETURN_VALUE') UNION ALL SELECT / * UDF return value row * / PROCEDURE_CATALOG = db_name (), PROCEDURE_SCHEMA = user_name (o.uid) , Procedure_name = Convert (NVARCHAR (134), O.NAME ';' LTRIM (Str (C.Number, 5))), parameter_name = communication (sysname, '@ Return_Value'), Ordinal_Position = Convert (Smallint, 0 ), PARAMETER_TYPE = convert (smallint, 4 / * DBPARAMTYPE_RETURNVALUE * /), PARAMETER_HASDEFAULT = convert (tinyint, 0), PARAMETER_DEFAULT = convert (nvarchar (255), null), IS_NULLABLE = convert (bit, c.isnullable), DATA_TYPE = d.oledb_data_type, CHARACTER_MAXIMUM_LENGTH = convert (int, case when d.oledb_data_type = 129 / * DBTYPE_STR * / or d.oledb_data_type = 128 / * DBTYPE_BYTES * / then coalesce (d.column_size, c. length) when d.oledb_data_type = 130 / * DBTYPE_WSTR * / then coalesce (d.column_size, c.length / 2) else null end), CHARACTER_OCTET_LENGTH = convert (int, case when d.oledb_data_type = 129 / * DBTYPE_STR * / or d.oledb_data_type = 128 / * DBTYPE_BYTES * / then coalesce (d.column_size, c.length) when d.oledb_data_type = 130 / * DBTYPE_WSTR * / then coalesce (d.column_size * 2, c.length) else null end), Numeric_Precision = Convert (Smallint, Case When D.OLEDB_DATA_TYPE =
131 / * DBTYPE_NUMERIC * / then c.prec when (d.fixed_prec_scale = 1 or d.oledb_data_type = 5 or d.oledb_data_type = 4) then d.data_precision else null end), NUMERIC_SCALE = convert (smallint, case when d.oledb_data_type = 131 / * DBTYPE_NUMERIC * / then c.scale else null end), DESCRIPTION = convert (nvarchar (1), null), TYPE_NAME = d.type_name, LOCAL_TYPE_NAME = d.local_type_name from sysobjects o, syscolumns c, master.dbo. SPT_PROVIDER_TYPES D, SYSTYPES T where o.id = C.ID and c.Number = 0 and c.colid = 0 and o.type = 'fn' / * udf scalar functions * / and c.XTYPE = D.SS_DTYPE AND C .length = case when d.fixlen> 0 then d.fixlen else c.length end and c.xusertype = t.xusertype and (@procedure_schema is null or @procedure_schema = user_name (o.uid)) and (@parameter_name is null or @parameter_name = '@RETURN_VALUE') UNION ALL SELECT / * UDF table value row * / PROCEDURE_CATALOG = db_name (), PROCEDURE_SCHEMA = user_name (o.uid), PROCEDURE_NAME = convert (nvarchar (134 ), nname ';' LTRIM (Str (C.Number, 5))), parameter_name = convert (sysname, '@ Table_Return_Value'), Ordinal_Position = Convert (Smallint, 0), Parameter_Type = Convert (Smallint, 4 / * DBPARAMTYPE_RETURNVALUE * /), PARAMETER_HASDEFAULT = convert (tinyint, 0), PARAMETER_DEFAULT = convert (nvarchar (255), null), IS_NULLABLE = convert (bit, 0), DATA_TYPE = convert (smallint, 0), / * DBTYPE_EMPTY * / Character_maximum_length = communication (int, case when d.oledb_data_type = 129 / * dbtype_str * / or D.OLEDB_DATA_TYPE =
128 / * DBTYPE_BYTES * / then coalesce (d.column_size, c.length) when d.oledb_data_type = 130 / * DBTYPE_WSTR * / then coalesce (d.column_size, c.length / 2) else null end), CHARACTER_OCTET_LENGTH = convert ( int, case when d.oledb_data_type = 129 / * DBTYPE_STR * / or d.oledb_data_type = 128 / * DBTYPE_BYTES * / then coalesce (d.column_size, c.length) when d.oledb_data_type = 130 / * DBTYPE_WSTR * / then coalesce ( d.column_size * 2, c.length) else null end), NUMERIC_PRECISION = convert (smallint, case when d.oledb_data_type = 131 / * DBTYPE_NUMERIC * / then c.prec when (d.fixed_prec_scale = 1 or d.oledb_data_type = 5 or d.oledb_data_type = 4) then d.data_precision else null end), NUMERIC_SCALE = convert (smallint, case when d.oledb_data_type = 131 / * DBTYPE_NUMERIC * / then c.scale else null end), DESCRIPTION = convert (nvarchar (1 ), null, type_name = n'table ', local_type_name = n'table' from sysObjects o, syscolumns c, master.dbo. SPT_PROVIDER_TYPES D where o.id = C.ID and c.Number = 0 and c.colid = 1 and o.TYPE IN ('TF', 'IF') / * udf Table functions * / and c.xtype = d. ss_dtype and c.length = case when d.fixlen> 0 then d.fixlen else c.length end and (@procedure_schema is null or @procedure_schema = user_name (o.uid)) and (@parameter_name is null or @parameter_name = ' @Table_Return_Value ') Order by 2, 3, 5 - then use system.data.sqlclient.mettype.getsqldbtypeFromoledbType (int16, string): sqldbtype to make OLDBTYPE to SqldbType's maping:
internal static SqlDbType GetSqlDbTypeFromOleDbType (short dbType, string typeName) {SqlDbType type1 = SqlDbType.Variant; OleDbType type2 = dbType; if (type2 <= OleDbType.Filetime) {
Switch ((Type2 - Oledbtype.smallint) {
Case 0: {
Goto label_013d;} case 1: {
TYPE1 = SqldbType.int; return type1;} case 2: {
TYPE1 = SqldbType.real; return type1;} case 3: {
TYPE1 = SqldbType.float; return Type1;} case 4: {
Goto label_00ee;} case 5: {
Goto label_0104;} case 6: {
Goto label_0161;} case 7: {
Return Type1;} case 8: {
Return Type1;} case 9: {
TYPE1 = SqldbType.bit; returniting type1;} case 10: {
TYPE1 = SqldbType.variant; return type1;} case 11: {
Return Type1;} case 12: {
Goto label_0119;} case 13: {
Return Type1;} case 14: {
Goto label_0142;} case 15: {
Goto label_0142;} case 16: {
Goto label_013d;} case 17: {
Return Type1;} case 18: {
TYPE1 = SqldbType.bigint; returniting;}}}}}}}} (type2 == OLEDBTYPE.FILETIME) {
Goto label_0104;} Return Type1;} if (Type2 == OLEDBTYPE.GUID) {
Goto label_0121;} Switch ((type2 - oledbtype.binary) {
Case 0: {
Goto label_0147;} case 1: {
Goto label_00d6;} case 2: {
Goto label_0161;} case 3: {
Goto label_0119;} case 4: {
Return Type1;} case 5: {
Goto label_0104;} case 6: {
Goto label_0104;} case 7: {
Goto label_0104;}} switch ((Type2 - Oledbtype.varchar) {
Case 0: {
Goto label_00d6;} case 1: {
TYPE1 = SqldbType.Text; Return Type1;} case 2: {
Goto label_0161;} case 3: {
TYPE1 = SqldbType.nText; return type1;} case 4: {
Goto label_0147;} case 5: {
TYPE1 = SqldbType.Image; return type1;}} return type1; label_00d6:
TYPE1 = ((TypenAme == "char")? 3: 22); Return Type1; label_00ee:
TYPE1 = ((TypenAme == "Money")? 9: 17); Return Type1; label_0104:
TYPE1 = ((TypenAme == "DATETIME")? 4: 15); return type1; label_0119: type1 = sqldbtype.decimal; return type1; label_0121:
TYPE1 = Sqldbtype.uniqueIdentifier; returnique1; label_013d:
TYPE1 = SqldbType.smallint; return type1; label_0142:
TYPE1 = SqldbType.tinyint; return type1; label_0147:
TYPE1 = ((TypenAme == "binary")? 1: 21); return type1; label_0161:
Return ((TypenAme == "nchar")? 10: 12);
Stored procedure sp_procedure_params_rowset is no problem. For stored procedures:
CREATE PROCEDURE dbo.GCCSP_ADDNEW_PATFAMHIST (@PAT_ID INTEGER, @REL_CODE Varchar (15), @EGO INTEGER = NULL, @FAMILIAL_CASE CHAR (1) = NULL, @AGE_DIAG INTEGER = NULL, @DISEASE_NAMES TEXT = NULL, @OUTCM_CODE Varchar (15) = Null, @create_by integer, @ReturnValue int = null out) AS ...
The return result of sp_procedure_params_rowset is:
PARAMETER_NAMEDATA_TYPECHAR_MAX_LENCHAR_OCTET_LENTYPE_NAMELOCAL_TYPE_NAME @ RETURN_VALUE3NULLNULLintint @ PAT_ID3NULLNULLintint @ REL_CODE1291515varcharvarchar @ EGO3NULLNULLintint @ FAMILIAL_CASE12911charchar @ AGE_DIAG3NULLNULLintint @ DISEASE_NAMES12921474836472147483647texttext @ OUTCM_CODE1291515varcharvarchar @ CREATE_BY3NULLNULLintint @ RETURNVALUE3NULLNULLintint
However, system.data.sqlclient.mettype.getsqldbtypefromoledbtype (int16, string): SqldbType has a small problem: mapping, Note that the parameter_names, which is defined as the text type, DISESE_NAMES, the result of Debug is:
Name Value Type- sqlPrmt {System.Data.SqlClient.SqlParameter} System.Data.SqlClient.SqlParameterDbType AnsiString System.Data.DbType SqlDbType VarChar System.Data.SqlDbType ParameterName @DISEASE_NAMES String Size 2147483647 Integer noted that the property is not SqlDbType Sqldbtype.text, but sqldbtype.varchar, this is the negligence in the mapping function. Of course, its size attribute is still 2147483647, ie 2 ^ 31-1, still retains the properties of the SqldbType.Text type.
This is the source of the wrong. When the SQLCommandBuilder constructs a list of parameters, the parameter of the SqldbType.Text type is marked as sqldbtype.varchar, and the program assigns the parameters:
Private Shared Sub AssignParameterValues (ByVal commandParameters () As SqlParameter, ByVal parameterValues () As Object) Dim i As Short Dim j As Short If (commandParameters Is Nothing) And (parameterValues Is Nothing) Then 'do nothing if we get no data Return End If 'we must have the same number of values as we pave parameters to put them in If commandParameters.Length <> parameterValues.Length Then Throw New ArgumentException ( "Parameter count does not match Parameter value count.") End If' value array j = CommandParameters.Length - 1 for i = 0 to j commandparameters (i) .value = parametervalues (i) Nextend Sub 'AssignParameterValues
The value of the incoming String type is also synthesized, and it is automatically converted to VARCHAR. Then, when executed, discover the text type of the parameter and the incoming VARCHAR type Mismatch, an error.
As for why Ide can, I think Ide has a function similar to late binding, and similar situation has been encountered before.
The solution is to rewrite the function of the above definition parameters:
Private Shared Function DiscoverSpParameterSet (ByVal connectionString As String, _ ByVal spName As String, _ ByVal includeReturnValueParameter As Boolean, _ ByVal ParamArray parameterValues () As Object) As SqlParameter () Dim cn As New SqlConnection (connectionString) Dim cmd As SqlCommand = New SqlCommand (spName, cn) Dim discoveredParameters () As SqlParameter Try cn.Open () cmd.CommandType = CommandType.StoredProcedure SqlCommandBuilder.DeriveParameters (cmd) If Not includeReturnValueParameter Then cmd.Parameters.RemoveAt (0) End If discoveredParameters = New SqlParameter (cmd .Parameters.Count - 1) {} cmd.Parameters.CopyTo (discoveredParameters, 0) Dim sqlPrmt As SqlParameter For Each sqlPrmt In cmd.Parameters If (sqlPrmt.SqlDbType = SqlDbType.VarChar Or sqlPrmt.SqlDbType = SqlDbType.Char) And _ SQLPRMT.SIZE> 8000 Then sqlPrmt.SqlDbType = SqlDbType.Text End If Next Finally cmd.Dispose () cn.Dispose () End Try Return discoveredParameters bright portion is opposed added content, i.e., the determination performed according to the Size property SqlParameter, filter the SqlDbType. TEXT this situation.
Note, this article was published on my blog (http://blog.cod.cn/athos) http://blog.coder.cn, but this site is now a bit problem, so moving to 9CBS Blog. At that time, I didn't stay, but fortunately, Google also had cache, thank you.
