How to define a size property for the out parameters of a stored procedure at the C # data access layer
I created a data access layer that is shamelessly modeled from the Castle Project's ActiveRecord implementation. To gain acceptance, it must maintain an extensive library of stored procedures used by my organization that have been written to use every possible I / O structure, including return values and output parameters of every conceivable data type.
My problem is in developing code that adds an output parameter to the parameter set of the Command object used to execute the stored procedure. Right now I just use a large default, hoping that this is enough to catch all the cases, but that seems cheesy.
How do I know the length of the output parameter in advance?
Here's my DAL class using attributes to denote an out 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?
source to share
If you are on SQL Server (not listed in the 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 old views still exist)
This gives you the SP name, type name, parameter name, length and I / O direction (c.isoutparam).
I wrote on my blog Create SP Access for C # . I didn't look at the OUT parameters there, but setting up the code for the output parameters should be simple.
source to share
The output parameter in the stored procedure is of data type / size. Use this.
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 a day off is. Name 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"];
}
source to share
You should look at the SQL code. Or flip (and cache)
Edit: The methods are highlighted here.
- Automatically 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 reflections
source to share