Return multiple SQL query column and convert into tuple

Question

I have the following code:

public async Task<(int purchaseOrderId, string PONum)> AddPurchaseOrder(PurchaseOrderMaster purchaseOrderMaster)
{
    try
    {
        var sqlQuery = new StringBuilder();
        var paramSearch = new DynamicParameters();

        _ = sqlQuery.Append($@"INSERT INTO PurchaseOrder (PONum, VendorID, WHId, PODate, ReferenceNum, EstArrivalDate, POType, SubTotal, TAX, ShippingHandling, Total, EnteredBy, EnteredOn, IsDelete)
                OUTPUT inserted.PurchaseOrderId, inserted.PONum
                VALUES (FLOOR(RAND() * 900000) + 100000, @VendorID, @WHId, @PODate, @ReferenceNum, @EstArrivalDate, @POType, @SubTotal, @TAX, @ShippingHandling, @Total, @EnteredBy, GETUTCDATE(), 'N');
        ");

        paramSearch.Add("@VendorID", purchaseOrderMaster.VendorID);
        paramSearch.Add("@WHId", purchaseOrderMaster.WHId);
        paramSearch.Add("@PODate", purchaseOrderMaster.PODate);
        paramSearch.Add("@ReferenceNum", purchaseOrderMaster.ReferenceNum);
        paramSearch.Add("@EstArrivalDate", purchaseOrderMaster.EstArrivalDate);
        paramSearch.Add("@POType", purchaseOrderMaster.POType);
        paramSearch.Add("@SubTotal", purchaseOrderMaster.SubTotal);
        paramSearch.Add("@TAX", purchaseOrderMaster.TAX);
        paramSearch.Add("@ShippingHandling", purchaseOrderMaster.ShippingHandling);
        paramSearch.Add("@Total", purchaseOrderMaster.Total);
        paramSearch.Add("@EnteredBy", purchaseOrderMaster.EnteredBy);

        return await dbHelper.ExecuteScalarAsync<(int, string)>(sqlQuery.ToString(), paramSearch).ConfigureAwait(false);
    }
    catch (Exception ex)
    {
        throw new BadRequestException(ex.Message);
    }
}

I am getting PurchaseOrderId and PONum from the SQL query result and want to return as a tuple (int purchaseOrderId, string PONum) in the C# code.

At query execution statement, I am getting an error as below:

{"Invalid cast from 'System.Int32' to 'System.ValueTuple`2[[System.Int32, System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.String, System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]]'."}

How can the result be converted to a tuple?

ExecuteScalar returns the first column of the first row in the result set returned by the query. It cannot return 2 values so you cannot combine them into a tuple. — Bart McEndree
– Bart McEndree, Commented Dec 6, 2024 at 13:35
Many weird things in this code; why are you returning a tuple? Why are you using a StringBuilder to store a constant string? Why are you using an interpolated string and not interpolating anything? Why are you using dynamic parameters for column values which presumably have a type? Why do you throw away the exception? — Dour High Arch
– Dour High Arch, Commented Dec 7, 2024 at 1:45

Bart McEndree · Accepted Answer · 2024-12-06 14:07:55Z

1

Change your ExecuteScalarAsync into ExecuteReaderAsync. Create your tuple by reading the 2 separate values.

var myTuple = Tuple.Create(
                           (int)Reader["PurchaseOrderId"], 
                           (string)Reader["PONum"]
                          );

edited Dec 6, 2024 at 14:07

answered Dec 6, 2024 at 13:41

Bart McEndree

3,6041 gold badge12 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marc Gravell · Accepted Answer · 2024-12-06 14:42:01Z

ExecuteScalar, by definition, returns only a single cell (one row, one column, one grid) - so you cannot use ExecuteScalar to read two columns - you'd need to use a reader. I don't know what dbHelper is, but it should have an API that returns a DbDataReader, allowing something like:

// something like...
using var reader = await dbHelper.ExecuteReaderAsync(sqlQuery.ToString(), paramSearch).ConfigureAwait(false);

if (!reader.Read())
{
    throw new InvalidOperationException("row not found"); // or something else suitable
}
var purchaseOrderId = reader.ReadInt32(0);
var PONum = reader.ReadString(1);
return (purchaseOrderId, PONum);

However, you might also want to consider tools that do this for you, for example, with Dapper (presumably instead of dbHelper):

return await connection.QuerySingleAsync<(int, string)>("""
    INSERT INTO PurchaseOrder (PONum, VendorID, WHId, PODate, ReferenceNum, EstArrivalDate, POType, SubTotal, TAX, ShippingHandling, Total, EnteredBy, EnteredOn, IsDelete)
    OUTPUT inserted.PurchaseOrderId, inserted.PONum
    VALUES (FLOOR(RAND() * 900000) + 100000, @VendorID, @WHId, @PODate, @ReferenceNum, @EstArrivalDate, @POType, @SubTotal, @TAX, @ShippingHandling, @Total, @EnteredBy, GETUTCDATE(), 'N');
    """, purchaseOrderMaster).ConfigureAwait(false);

(Dapper has multiple ways of passing the parameters, but in this case it looks like it should be able to just pull the values from the DTO directly)

Chris Jensen · Accepted Answer · 2024-12-09 12:26:37Z

0

all I have to use is "SelectData" instead of "ExecuteScalarAsync" that is returning multiple values as a tuple post SQL insert query.

return await dbHelper.SelectData<(int, string)>(sqlQuery.ToString(), paramSearch).ConfigureAwait(false);

Thanks

edited Dec 9, 2024 at 12:26

Chris Jensen

5053 silver badges19 bronze badges

answered Dec 9, 2024 at 6:26

krunal

195 bronze badges

Collectives™ on Stack Overflow

Return multiple SQL query column and convert into tuple

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related