Async SPROCs with output parameters

[ymerej]

Feature request:

• Add a boolean option that when it's enabled then SPROCs with output parameters would generate an async version. Enabling the option would work on the assumption that the project has added the System.ValueTuple nuget package.

I am submitting this as an issue/feature request partly to see if there is any interest in this approach. Secondarily if there is interest I am asking for feedback on whether this approach is sound, or if there is an issue to this approach that I haven't discovered.

This is a sample input and sample output:

CREATE PROCEDURE [dbo].[InsertRecord]
    @Data         VARCHAR(256)
  , @InsertedId   INT OUT
AS
    BEGIN
        INSERT INTO DataTable
        VALUES (@Data)

        SET @InsertedId = SCOPE_IDENTITY();
    END;

public async Task<(int procResult, int? insertedId)> InsertRecordAsync(string data)
{
    var dataParam = new System.Data.SqlClient.SqlParameter { ParameterName = "@Data", SqlDbType = System.Data.SqlDbType.VarChar, Direction = System.Data.ParameterDirection.Input, Value = data, Size = 256 };
    if (dataParam.Value == null)
        dataParam.Value = System.DBNull.Value;

    var insertedIdParam = new System.Data.SqlClient.SqlParameter { ParameterName = "@InsertedId", SqlDbType = System.Data.SqlDbType.Int, Direction = System.Data.ParameterDirection.Output, Precision = 10, Scale = 0 };
    var procResultParam = new System.Data.SqlClient.SqlParameter { ParameterName = "@procResult", SqlDbType = System.Data.SqlDbType.Int, Direction = System.Data.ParameterDirection.Output };

    await Database.ExecuteSqlCommandAsync(System.Data.Entity.TransactionalBehavior.DoNotEnsureTransaction, "EXEC @procResult = [dbo].[InsertRecord] @Data, @InsertedId OUTPUT", dataParam, insertedIdParam, procResultParam);
    int? insertedId;
    if (IsSqlParameterNull(insertedIdParam))
        insertedId = null;
    else
        insertedId = (int)insertedIdParam.Value;

    return ((int)procResultParam.Value, insertedId);
}

[DickBaker]

May I suggest that

1. the input parameters to the InsertRecordAsync method should be a params array (that would be decomposed into individual SqlParameter items to the sproc as iteration by method)
2. the sproc should return the complete row inserted [or at least those subset DTO fields in C# model not necessarily full SQL data model] rather than just the IDENTITY field (i.e. would return the complete entity c/w default complex and computed fields) so that EF can merge into its DbContext coffers.
3. prefix appropriate using statements to avoid code clutter

thanks