如何确定c#数据访问层中存储过程输出参数的大小属性?

I've made a data access layer modeled shamelessly off of Castle Project's ActiveRecord implementation. In order for it to gain acceptance, it must support the extensive library of stored procedures in use at my organization, which have been written to use every input/output structure imaginable, including return values and output parameters of every conceivable datatype.

我已经在Castle Project的ActiveRecord实现中对数据访问层进行了无耻的建模。为了让它获得认可，它必须支持在我的组织中使用的大量存储过程库，这些存储过程库被编写为使用所有可能的输入/输出结构，包括所有可能的数据类型的返回值和输出参数。

My problem is in developing the code that adds the output parameter to the parameters collection of the Command object used to execute the stored procedure. Right now I'm just using a large default value, hoping it's enough to catch all the cases, but this feels shoddy.

我的问题是开发将输出参数添加到用于执行存储过程的命令对象的参数集合的代码。现在我只是使用了一个大的默认值，希望它足够捕获所有的情况，但这感觉很糟糕。

How can I know the length of the output parameter in advance?

如何提前知道输出参数的长度?

Here's my DAL class, using attributes to denote the output parameter:

这是我的DAL类，使用属性表示输出参数:

 [StoredProcedure("usp_PostARTransaction", OperationType.Insert)]
    public class ARTranPost : DataObjectBase<ARTranPost>
    {
        [StoredProcedureParameter("sARTranID",OperationType.Insert,ParameterDirection.Output)]
        public string ARTranID {get;set;}
        [StoredProcedureParameter("sDueDate", OperationType.Insert, ParameterDirection.Input)]
        public string DueDate { get; set; }
        [StoredProcedureParameter("sPostDate", OperationType.Insert, ParameterDirection.Input)]
        public string PostDate { get; set; }

    }

Do I need to use SMO or some other library to get the length from the database?

是否需要使用SMO或其他库从数据库中获取长度?

3 个解决方案

#1

If you work on SQL Server (not specified in OP), you can use the sysobjects and syscolumns catalog views to retrieve stored procedures and their parameters:

如果您使用SQL Server(在OP中没有指定)，您可以使用sysobjects和syscolumns编目视图来检索存储过程及其参数:

SELECT p.name, t.name, c.*
FROM sysobjects p
INNER JOIN syscolumns c ON p.id = c.id
INNER JOIN systypes t on c.xusertype = t.xusertype
WHERE p.type = 'P'
AND p.name NOT LIKE 'dt[_]%'
ORDER BY p.name, c.colid

(in SQL2005+ use sys.objects, sys.types and sys.columns, but the older views are still there)

(在SQL2005 +使用系统。对象,系统。类型和系统。列，但是旧的视图仍然在那里)

This gives you the SP name, type name, parameter name, length, and input/output direction (c.isoutparam).

这将提供SP名称、类型名称、参数名称、长度和输入/输出方向(c.isoutparam)。

I wrote on my blog on generating SP access for C#. I did not cover OUT parameters there, but adjusting the code to output parameters should be straightforward.

我在博客上写了关于为c#生成SP访问的文章。我在这里没有覆盖参数，但是将代码调整为输出参数应该很简单。

#2

The output parameter in your stored procedure has a data type / size. Use that.

存储过程中的输出参数具有数据类型/大小。使用它。

If your SP is like:

如果你的SP是:

create procedure DoThis
    @parm1 int
    , @parm2 varchar(50) output
as

select @parm2 = (
select breed from dogs
where dogid = @parm1
)

You know what the output parm is. Call it

你知道输出参数是什么。叫它

public string DoThis(int dogid)
{
    SqlCommand cmd = new SqlCommand("DoThis");
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = theConnection;

    cmd.Parameters.Add(new SqlParameter("@parm1", dogid);
    cmd.Parameters["@parm1"].DbType = DbType.Int32;

    cmd.Parameters.Add(new SqlParameter("@parm2", DbType.String, 50));
    cmd.Parameters["@parm2"].Direction = ParameterDirection.Output;

    db.ExecuteNonQuery(cmd);

    return (string) cmd.Parameters["@parm2"];

}

#3

You have to look at the SQL code. Or reflect (and cache)

您必须查看SQL代码。或反映(和缓存)

Edit: there are techniques highlighted here

编辑:这里突出显示了一些技术

Auto-Generating Wrapper Classes for Stored Procedures Part 1, part 2
为存储过程自动生成包装类第1部分，第2部分
Dynamically Bind Your Data Layer to Stored Procedures and SQL Commands Using .NET Metadata and Reflection
使用.NET元数据和反射动态地将数据层绑定到存储过程和SQL命令

#1