使用 SQL 2005 确定存储过程参数

引言

你是否尝试过在 .NET 1.1 应用程序中使用 SQL Server 2005？ 如果你的应用程序使用 Microsoft 数据访问应用程序块 或调用 SqlCommandBuilder.DeriveParameters()，你可能会遇到奇怪的错误。

问题在于 SqlCommandBuilder.DeriveParameters() 方法不会将模式名称参数 (@procedure_schema) 传递给 sp_procedure_params_rowset。 在 SQL Server 2000 中，模式名称几乎总是 “dbo”，因此仅传递存储过程名称就足够了。

尝试使用 Visual Studio 2003 服务器资源管理器访问数据库时，也会看到相同的问题。 服务器资源管理器将连接到数据库，但不会正确显示存储过程参数。

SQL Server 2005 支持新的 ‘XML’ 数据类型这一事实使问题更加复杂——这种类型在 1.1 中的 SqlDbType 枚举中不存在。

方法

将模式名称编码为存储过程名称的一部分。 我使用将模式作为前缀附加到 SP 名称的简单方法。 如果 SP 名称是 “OM_Get_Order_XML”，模式是 “OM_Order”，则编码后的 SP 名称将是 “OM_Order.OM_Get_Order_XML”。

你可以使用以下代码片段来解码存储过程名称

string schemaName = "dbo";
int firstDot = spName.IndexOf('.');
if (firstDot > 0) 
{
    schemaName = spName.Substring(0, firstDot);
    spName = spName.Substring(firstDot+1);
}

请注意，上述调用如果未指定模式名称，将回退到旧的 “dbo” 模式。

现在我们有了模式名称和存储过程名称，就可以手动确定存储过程参数了。

确定存储过程参数

我的代码在混合 SQL Server 2000 和 2005 环境中运行。 因此，我选择编写代码以实现最大的兼容性。 我首先尝试运行 SqlCommandBuilder.DeriveParameters() 方法，如果该方法失败，则尝试手动方法。
代码是：

private static SqlParameter[] DiscoverSpParameterSet
	(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

            // Original cmd object
SqlCommand cmd = new SqlCommand(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;

// Hack to check for schema name in the spName
string schemaName = "dbo";
int firstDot = spName.IndexOf('.');
if (firstDot > 0) 
{
    schemaName = spName.Substring(0, firstDot);
    spName = spName.Substring(firstDot+1);
}

connection.Open();            

// Now this is not neat - I am trying the SQL2000 version and if it fails
// I go to the manual SQL2005 version
try 
{
    // First, attempt the SQL2000 version (no schema)
    SqlCommandBuilder.DeriveParameters(cmd);
}
catch 
{
    // If we are here, SQL2000 call failed
    // Manually run the 'derive params' SP
    // this time with the schema name parameter
    SqlCommand getParams = new SqlCommand("sp_procedure_params_rowset", connection);
    getParams.CommandType = CommandType.StoredProcedure;
    getParams.Parameters.Add("@procedure_name", spName);
    getParams.Parameters.Add("@procedure_schema", schemaName);
    SqlDataReader sdr = getParams.ExecuteReader();
            
    // Do we have any rows?
    if (sdr.HasRows) 
    {
        using (sdr) 
        {
            // Read the parameter information
            int ParamNameCol = sdr.GetOrdinal("PARAMETER_NAME");
            int ParamSizeCol = sdr.GetOrdinal("CHARACTER_MAXIMUM_LENGTH");
            int ParamTypeCol = sdr.GetOrdinal("TYPE_NAME");
            int ParamNullCol = sdr.GetOrdinal("IS_NULLABLE");
            int ParamPrecCol = sdr.GetOrdinal("NUMERIC_PRECISION");
            int ParamDirCol = sdr.GetOrdinal("PARAMETER_TYPE");
            int ParamScaleCol = sdr.GetOrdinal("NUMERIC_SCALE");

            // Loop through and read the rows
            while (sdr.Read()) 
            {
                string name = sdr.GetString(ParamNameCol);
                string datatype = sdr.GetString(ParamTypeCol);
                // Is this xml?
                // ADO.NET 1.1 does not support XML, replace with text
                if (0 == String.Compare("xml", datatype, true)) 
                {
                    datatype = "Text";
                }
                object parsedType = Enum.Parse(typeof(SqlDbType), datatype, true);
                SqlDbType type = (SqlDbType)parsedType;
                bool Nullable = sdr.GetBoolean(ParamNullCol);
                SqlParameter param = new SqlParameter(name,type);
                // Determine parameter direction
                int dir = sdr.GetInt16(ParamDirCol);
                switch (dir)
                {
                    case 1:
                        param.Direction = ParameterDirection.Input;
                        break;
                    case 2:
                        param.Direction = ParameterDirection.Output;
                        break;
                    case 3:
                        param.Direction = ParameterDirection.InputOutput;
                        break;
                    case 4:
                        param.Direction = ParameterDirection.ReturnValue;
                        break;
                }
                param.IsNullable = Nullable;
                if (!sdr.IsDBNull(ParamPrecCol))
                {
                    param.Precision = (Byte)sdr.GetInt16(ParamPrecCol);
                }
                if (!sdr.IsDBNull(ParamSizeCol)) 
                {
                    param.Size = sdr.GetInt32(ParamSizeCol);
                }
                if (!sdr.IsDBNull(ParamScaleCol))
                {
                    param.Scale = (Byte)sdr.GetInt16(ParamScaleCol);
                }
                    cmd.Parameters.Add(param);
            }
        }
    }
}
finally
{
        connection.Close();
}

if (!includeReturnValueParameter) 
{
    cmd.Parameters.RemoveAt(0);
}
                
SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

cmd.Parameters.CopyTo(discoveredParameters, 0);

// WORKAROUND begin
foreach (SqlParameter sqlParam in discoveredParameters)
{
    if ((sqlParam.SqlDbType == SqlDbType.VarChar) &&
        (sqlParam.Size == Int32.MaxValue))
    {
        sqlParam.SqlDbType = SqlDbType.Text;
    }
}
// WORKAROUND end

// Init the parameters with a DBNull value
foreach (SqlParameter discoveredParameter in discoveredParameters)
{
    discoveredParameter.Value = DBNull.Value;
}
return discoveredParameters;
}

给数据访问应用程序块用户的说明

上面的代码是 SqlHelperParameterCache 类中 DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter) 方法的完整替代。

历史

• 版本 1.0 – 发布于 2006年6月10日