DataProjectors and SP Return Values

Posts   
 
    
Marcus avatar
Marcus
User
Posts: 747
Joined: 23-Apr-2004
# Posted on: 03-Aug-2006 19:40:59   

Frans,

When I call stored procedures using the RetrievalProcedures.Get_MyQuery_AsQuery method, I can't seem to find where I can access the query's return values... disappointed

Can you point me in the right direction?

[edit]

Althought the following works... its not very integrated with LLBLGen...

SqlParameter folderItemCountParameter = query.Parameters[5] as SqlParameter;
if(folderItemCountParameter.Value!=System.DBNull.Value)
{
    itemCount = (System.Int32)folderItemCountParameter.Value;
} else {
    itemCount = 0;
}

You might consider generating some extra methods in RetrievalProcedures to match up with the Get_MyQuery_AsQuery method. For example:


public void Get_MyQuery_ReturnValues(IRetrievalQuery query, out int myReturnValue)
{
    SqlParameter myReturnValueParam = query.Parameters[5] as SqlParameter;
    if (myReturnValueParam.Value != System.DBNull.Value)
    {
        myReturnValue= (System.Int32)myReturnValueParam.Value;
    }
    else
    {
        myReturnValue= 0;
    }
}

[/edit]

Marcus

Posts: 1251
Joined: 10-Mar-2006
# Posted on: 03-Aug-2006 20:51:12   

So, you have retrieval procedures that return result sets and have output parameters?

<edit>

Marcus, I will be in Dublin, Ireland 14 days from now...and counting.....going golfing!

Marcus avatar
Marcus
User
Posts: 747
Joined: 23-Apr-2004
# Posted on: 03-Aug-2006 21:22:31   

WayneBrantley wrote:

So, you have retrieval procedures that return result sets and have output parameters?

Absolutely... these SPs are there to provide maximum efficiency for retreiving data. I could of course return an additional resultset, but the overhead of a resultset is is unnecessary when an OUTPUT parameter will suffice.

WayneBrantley wrote:

I will be in Dublin, Ireland 14 days from now...and counting.....going golfing!

smile I hope the weather lasts for you! It's been one of best summers we've had in years so far!

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 04-Aug-2006 08:28:02   

Copied from the manual:

Output parameters are also supported. When a stored procedure has an output parameter, a parameter representing the output parameter in the stored procedure is added to the method heading and is defined as 'ref' (C#) or 'ByRef' (VB.NET). Illustrated below is the call to an imaginary stored procedure which returns a datatable, takes 4 input parameters and returns a value in an output parameter: // [C#] int outputValue; DataTable resultSet = RetrievalProcedures.MyStoredProcedure(1, 2, 3, 4, ref outputValue);

Marcus avatar
Marcus
User
Posts: 747
Joined: 23-Apr-2004
# Posted on: 04-Aug-2006 08:56:02   

Walaa wrote:

Copied from the manual:

Output parameters are also supported. When a stored procedure has an output parameter, a parameter representing the output parameter in the stored procedure is added to the method heading and is defined as 'ref' (C#) or 'ByRef' (VB.NET). Illustrated below is the call to an imaginary stored procedure which returns a datatable, takes 4 input parameters and returns a value in an output parameter: // [C#] int outputValue; DataTable resultSet = RetrievalProcedures.MyStoredProcedure(1, 2, 3, 4, ref outputValue);

Yes this is true... but the parameters are missing on the Get...AsQuery methods which are used for data projections. simple_smile

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 04-Aug-2006 11:04:19   

Indeed they're missing, because where do they have to be set? The routine getas... query creates an IRetrievalQuery object and that's executed. It doesn't contain output parameters because there's no system to set them, as it would be illogical...

Frans Bouma | Lead developer LLBLGen Pro
Marcus avatar
Marcus
User
Posts: 747
Joined: 23-Apr-2004
# Posted on: 04-Aug-2006 11:49:21   

Otis wrote:

Indeed they're missing, because where do they have to be set? The routine getas... query creates an IRetrievalQuery object and that's executed. It doesn't contain output parameters because there's no system to set them, as it would be illogical...

Which is why I recommended generating an additional method for each query in RetrievalProcedures which can be subsequently called to retreive the values...

The problem with doing this manually is that the index of the output parameters in the parameters collection in IRetrievalQuery may change when the code is regenerated.

If there was a generated method to pull the values from IRetrievalQuery's parameter collection code would not break when a re-generation occurs.

for each generated Get...AsQuery method you would have a corresponding


public void GetQuery...ReturnValues(IRetrievalQuery query, out int returnValue1, out int returnValue2, ...)

This method knows where the output paramters live (their index) within the parameters collections and can pass them out. See my example at the top of this thread. simple_smile

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 04-Aug-2006 12:15:45   

I must say I don't really like the necessity of the method, as it is really a 'call it in this order or else everything will fall apart'- kind of scenario... OTOH, reading the parameters directly is also not likely something you want. Though I've to think about adding this, as it's also easily added by a custom template which generates a util class for this for the few people who need this (i.e. AND using procs, AND using output parameters on retrieval procs AND using these procs in projection scenarios)

Frans Bouma | Lead developer LLBLGen Pro
Marcus avatar
Marcus
User
Posts: 747
Joined: 23-Apr-2004
# Posted on: 05-Aug-2006 09:54:09   

Otis wrote:

I must say I don't really like the necessity of the method, as it is really a 'call it in this order or else everything will fall apart'- kind of scenario... OTOH, reading the parameters directly is also not likely something you want. Though I've to think about adding this, as it's also easily added by a custom template which generates a util class for this for the few people who need this (i.e. AND using procs, AND using output parameters on retrieval procs AND using these procs in projection scenarios)

Ahhhh its not as bad as all that! simple_smile

The thing is that these methods will ONLY be generated for procs that DO have output parameters.

In theory there is no point in generating the Get...AsQuery method without the GetQuery...ReturnValues for this kind of proc, since if the proc has output paramters it will be necessary to be able to read them.

Regading the order of calls, I agree and understand that you have always been carefull to ensure the API didn't introduce cryptic "call this first, this second" behaviour. But I think its fair to say that 1) you need an IRetrievalQuery object before you can call the method and 2) if you call the method before executing the adapter.FetchDataReader then this is just dumb simple_smile

simple_smile