杀虫纪录:一个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)
} |
好,追下去,System.Data.SqlClient.SqlCommand.DeriveParameters() : Void是:
原来是——先用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) {
|
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这一情况。