杀虫纪录:一个OleDb/SqlDb Mapping中SqlDbType.Text引起的问题
来源:互联网 发布:网络模特王若雪 编辑:程序博客网 时间:2024/04/27 13:45
杀虫纪录:一个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)
{
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_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但是,System.Data.SqlClient.MetaType.GetSqlDbTypeFromOleDbType(Int16, String) : SqlDbType有了点小小的问题:mapping之后,注意定义为Text类型的参数DISEASE_NAMES,Debug的结果是:
NameValueType-sqlPrmt{System.Data.SqlClient.SqlParameter}System.Data.SqlClient.SqlParameterDbTypeAnsiStringSystem.Data.DbTypeSqlDbTypeVarCharSystem.Data.SqlDbTypeParameterName@DISEASE_NAMESStringSize2147483647Integer注意到,它的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
- 杀虫纪录:一个OleDb/SqlDb Mapping中SqlDbType.Text引起的问题
- 杀虫纪录:一个OleDb/SqlDb Mapping中SqlDbType.Text引起的问题
- SqlDbType.VarChar的问题
- url中jsessionid引起的一个问题
- 纪录一个Spinner赋值的问题
- 一个ORACLE本身OLEDB驱动的问题
- 通过OLEDB访问informix的一个问题
- 一个字串中结束符'/0'引起的问题
- IE6中javascript数组声明语法错误引起的一个问题
- 一个问题引起的思考
- 一个笔误引起的问题
- NHibernate Mapping中 对应MSSQL中Text字段的类型
- OLEDB TEXT
- 设置text-overflow: ellipsis后引起的文本对齐问题
- Mac 上装 Python的SQLdb
- C#一个OleDb获取FoxPro关键字的问题。
- win7 oledb的问题
- .Net的Oledb问题
- 一些算法题
- 使用SharePoint 2010的母版页
- Linux中搜索大于200M的文件
- 十七大以来8名省部级干部因特大事故受处分-省部级干部因-事故-处分
- S3C2440 UART串口驱动
- 杀虫纪录:一个OleDb/SqlDb Mapping中SqlDbType.Text引起的问题
- PS DEC
- linux运维常用命令
- Liferay UI <liferay:ui discussion/> working with Spring
- 《Flash Player 多元件性能测试报告》作者:寂寞火山
- linux下异步IO的简单例子
- hadoop 命令手册
- 2012-CVPR\ECCV感兴趣文章列表
- 如何获得当前所在的DLL模块名称