How to determine size property for stored procedure output parameters in C# data access layer

Question

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.

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:

 [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?

Answer 1

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

Edit: there are techniques highlighted here

• Auto-Generating Wrapper Classes for Stored Procedures Part 1, part 2
• Dynamically Bind Your Data Layer to Stored Procedures and SQL Commands Using .NET Metadata and Reflection

Answer 2

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

If your SP is like:

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"];

}

Answer 3

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:

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)

This gives you the SP name, type name, parameter name, length, and input/output direction (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.