CODEWITHSUNDEEP
c#.netsql-servertuples
krunal
krunal

Reputation: 19

Return multiple SQL query column and convert into tuple

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?

Upvotes: -1

Views: 99

Answers (3)

krunal
krunal

Reputation: 19

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

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1063433

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)

Upvotes: 1

Bart McEndree
Bart McEndree

Reputation: 3273

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

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

Upvotes: 1

Related Questions