SubSonic: retrieving value of stored procedure OUT parameters

Question

I love your tool. I have been using it a lot, but just today I ran into a problem...

I wrote a stored procedure that returns some values via OUT parameters, but SubSonic does not seem to generate the out parametes of the stored procedure method. For example, for SPI like this:

CREATE PROC dbo.MyProc @param1 int, @param2 int out, @param3 varchar(150) out

It generates signature

SPs.MyProc(int? param1, int? param2, string param3

I would expect it to generate this

SPs.MyProc(int? param1, out int? param2, out string param3) Well, considering that the method actually just configures the SP and does not actually execute it, I woiuld expect Subsonic to generate this

SPs.MyProc(int? param1, ref int? param2, ref string param3)

How do you guys solve this problem? Is there something like that in Subsonic already and I just missed it?

Answer 1

SOURCE You can acess OuPut parametes of SP in SubSonic using StoredProcedure.OutputValues;

Folowing is the chunk of code to access OutPut parameters of SP, here i have sued SP “UspTestOutPut”:

StoredProcedure s = SPs.UspTestOutPut(”10″, “15″);
s.Execute();
s.OutputValues.ForEach(delegate(object objOutput)
{
    Response.Write(”OutPutValues=”+objOutput.ToString());
});

Answer 2

You have to instantiate the SP as an object then run it - it will then hand you the OUTPUT param as a property.

Answer 3

It seems that the usage of OutputParameters doesn't work in combination with .GetReader(), but only with .Execute().

I'm unable to retrieve records from the database in combination with the usage of OUTPUT parameters.

For example, I have a storedprocedure that returns a list of newsletters from the database, but I want to use the paging functionality of SQL Server 2005. So I provide the pageSize and actual page I want to see. In the same procedure I want to count the amount of pages that are available and return that using an output variable.

I don't get it to work. The list with OutputParameters has 1 item in it, but the value is always NULL.

Answer 4

I'm using subsonic and Sqlserver 2005 server. for the paging functionality, i pass the currentpage and pagesize as input parameters. To get the total number of records, i use @TotalCount int OUTPUT parameter variable.

in the StoresPrecedure, i use like this:

Select * from TestTable where PageSize=@PageSize and CurrentPage = @CurrentPage -- it gets the current page records.

Set @Query ="Insert ....."

EXEC sp_executesql @Query

SET @TotalCount = @@Rowcount -- total number of records affected by insert statement

in my C#.net - subsonic class file, i use, GetDataSet() to get the result DataSet and OutputValues[0] to get the value from the output parameter.

      DataSet results = sp.GetDataSet(); // current page records

      int totalCnt = Convert.ToInt32(sp.OutputValues[0].ToString()); // total number of
                                                             //records from outputparameter