杀虫纪录：一个OleDb/SqlDb Mapping中SqlDbType.Text引起的问题

最新推荐文章于 2019-12-20 20:36:21 发布

ghlfllz

最新推荐文章于 2019-12-20 20:36:21 发布

阅读量2.4k

点赞数

分类专栏： C# SQL Server 文章标签： null character schema table command integer

SQL Server 同时被 2 个专栏收录

36 篇文章 0 订阅

订阅专栏

29 篇文章 0 订阅

订阅专栏

杀虫纪录：一个OleDb/SqlDb Mapping中SqlDbType.Text引起的问题　

前天用户报告了一个bug，一分析十分奇怪。

　　首先，IIS+Sql Database上有问题，但IDE+Sql Database就没有问题。

　　其次，原始的Error Message是

System.Data.SqlClient.SqlException: A severe error occurred on the current command. The results, if any, should be discarded

，这个，根据Microsoft Knowledge Base Article - 827366，应该是第二个原因

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 successful.

，但原来的程序是用

Void System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(SqlCommand)

配置的参数，应该没有错呀？

　　上边的MS KB827366中特别举出了Text类型作为例子，果然，程序调用的Stored Procedure中有一个Text类型的参数，将其去掉，果然可以了。

　　知其然还要知其所以然，调出Reflector，分析一下，原来是这样的……

　　首先，程序是这样分析SqlCommand的参数的：

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)

对System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(SqlCommand) : Void，reflector给的结果是：

public static void DeriveParameters(SqlCommand command)
{

SqlConnection.SqlClientPermission.Demand();
if (command == null)
{

throw ADP.ArgumentNull("command");

}
command.DeriveParameters();

}

好，追下去，System.Data.SqlClient.SqlCommand.DeriveParameters() : Void是：

internal void DeriveParameters()
{

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 ADP.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 = array1[3];
SqlDataReader 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 ADP.NoStoredProcedureExists(this.CommandText);

}
this.Parameters.Clear();
foreach (object obj2 in list1)
{

this._parameters.Add(obj2);

}

原来是——先用SQL Database里的系统存储过程sp_procedure_params_rowset获得stored procedure的参数（居然Google到的网站上给出了两个实现），

实现一：

/* 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.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.name = @procedure_name
and (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 ((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 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),o.name +';'+ 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.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))),
  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, 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.name = @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.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),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_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(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

实现二：

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_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.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),o.name +';'+ 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   = 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, 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),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_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

——然后用System.Data.SqlClient.MetaType.GetSqlDbTypeFromOleDbType(Int16, String) : SqlDbType来进行OleDbType到SqlDbType的一个mapping：

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;
return 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;
return type1;

}

}
if (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;
return type1;

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没有问题。对于存储过程：

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
...

sp_procedure_params_rowset的返回结果是：

PARAMETER_NAME	DATA_TYPE	CHAR_MAX_LEN	CHAR_OCTET_LEN	TYPE_NAME	LOCAL_TYPE_NAME
@RETURN_VALUE	3	NULL	NULL	int	int
@PAT_ID	3	NULL	NULL	int	int
@REL_CODE	129	15	15	varchar	varchar
@EGO	3	NULL	NULL	int	int
@FAMILIAL_CASE	129	1	1	char	char
@AGE_DIAG	3	NULL	NULL	int	int
@DISEASE_NAMES	129	2147483647	2147483647	text	text
@OUTCM_CODE	129	15	15	varchar	varchar
@CREATE_BY	3	NULL	NULL	int	int
@RETURNVALUE	3	NULL	NULL	int	int

但是，System.Data.SqlClient.MetaType.GetSqlDbTypeFromOleDbType(Int16, String) : SqlDbType有了点小小的问题：mapping之后，注意定义为Text类型的参数DISEASE_NAMES，Debug的结果是：

	Name	Value	Type
-	sqlPrmt	{System.Data.SqlClient.SqlParameter}	System.Data.SqlClient.SqlParameter
	DbType	AnsiString	System.Data.DbType
	SqlDbType	VarChar	System.Data.SqlDbType
	ParameterName	@DISEASE_NAMES	String
	Size	2147483647	Integer

注意到，它的SqlDbType属性并不是SqlDbType.Text，而是SqlDbType.VarChar，这，是mapping函数中的疏忽。当然，它的Size属性还是2147483647，即2^31-1，仍然保留了SqlDbType.Text类型的属性。

这就是错误的根源。当SqlCommandBuilder构造参数列表时，SqlDbType.Text类型的参数被标志成了SqlDbType.VarChar，程序后来对参数进行赋值：

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)
    Next

End Sub 'AssignParameterValues

传入的String类型的值，也顺理成章，被自动转化成了VarChar。然后，在执行的时候，发现参数定义的Text类型和传入的VarChar类型的mismatch，报错了。

至于为什么IDE中可以，我想IDE有类似Late Binding的功能，类似情况以前也遇到过。

解决方法是，改写了上面的定义参数的函数：

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

置亮的部分是加的内容，即根据SqlParameter的Size属性进行了判断，filter了SqlDbType.Text这一情况。

本文引自：http://blog.csdn.net/athossmth/article/details/344865

ghlfllz

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
杀虫纪录：一个OleDb/SqlDb Mapping中SqlDbType.Text引起的问题

杀虫纪录：一个OleDb/SqlDb Mapping中SqlDbType.Text引起的问题　前天用户报告了一个bug，一分析十分奇怪。　　首先，IIS+Sql Database上有问题，但IDE+Sql Database就没有问题。　　其次，原始的Error Message是System.Data.SqlClient.SqlException: A se
复制链接

扫一扫