Output parameter value not set with async procedure call

Posts   
 
    
gvb
User
Posts: 19
Joined: 09-Nov-2014
# Posted on: 13-Apr-2021 11:18:30   

Hi support,

I have a stored procedure with a resultset and an output parameter. When I use an async method to retrieve the result, the output parameter value is not set.

I can reproduce it in northwind with this procedure:

CREATE PROCEDURE dbo.GetCustomersByCity
    @city       NVARCHAR(20),
    @counted AS INT OUTPUT
AS
    BEGIN
        SET NOCOUNT ON;

        SELECT  c.CustomerID,
                c.ContactName,
                c.CompanyName,
                c.City
          FROM  dbo.Customers AS c
         WHERE  c.City = @city;

        SELECT  @counted = @@ROWCOUNT;
    END;

and use this C# code:

        static async Task Main(string[] args){

            RuntimeConfiguration.ConfigureDQE<SQLServerDQEConfiguration>(
                c => c.SetTraceLevel(TraceLevel.Verbose)
                      .AddDbProviderFactory(typeof(Microsoft.Data.SqlClient.SqlClientFactory))
                      .SetDefaultCompatibilityLevel(SqlServerCompatibilityLevel.SqlServer2012));

            // Generate code for .netstandard2.0
            List<GetCustomersByCityResultRow> results;
            var                               counted=-1 ;
            using (var adapter = new DataAccessAdapter(@"Data Source=.\sql2016;Initial Catalog=NORTHWIND;Integrated Security=True")){
               
                results = await adapter.FetchQueryFromSourceAsync(
                            new QueryFactory().GetGetCustomersByCityResultTypedViewProjection(),
                            RetrievalProcedures.GetGetCustomersByCityCallAsQuery("London",ref counted ));

            }
            foreach(var row in results){
                Console.WriteLine($"Company: {row.CompanyName}");
            }
            Console.WriteLine($"Counted company {counted } records");
            Console.ReadKey();
        }

The result:

Company: Around the Horn
Company: B's Beverages
Company: Consolidated Holdings
Company: Eastern Connection
Company: North/South
Company: Seven Seas Imports
Counted company -1 records
  • .Net 5.0
  • LLBLGen 5.0.8 Nuget
  • Generated for .netstandard2.0

Can you help me any further?

Regards, Goos van Beek.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 14-Apr-2021 05:29:38   

Do you need output parameters in Retrieval Procedures?

In your case you can use, results.Count to get the resultsetcount.

However for Async ActionProcedures output parameters, please read this

gvb
User
Posts: 19
Joined: 09-Nov-2014
# Posted on: 15-Apr-2021 22:59:52   

Walaa wrote:

Do you need output parameters in Retrieval Procedures?

In your case you can use, results.Count to get the resultsetcount.

However for Async ActionProcedures output parameters, please read this

Thanks for your reply. This was just an example to reproduce this issue. The output parameter(s) of the original SP returns intermediate results from different actions. I'll see what I can do best now

Regards, Goos van Beek.