How to return multiple results from SqlConnection using C#

Question

I have working code which I have listed below here it does exactly what I want. However, I would like to minimize it down so I only call the DB once instead of twice. I currently have the query written to only bring back 1 record that has 1 column.

This column brings back a date which I use in my code. The issues is I also need to bring back the user as well. Which I made work by basically using the same query but have it bring back the user rather than the date.

THE BIG QUESTION: I have the query to use to bring back both pieces of information, however, I am not sure how to get the information back to use in my code? when there is more that one column being returned from query. side note the new query still only brings back one record but with 2 columns.

THE WORKING CODE: I would add in "UpdatedBy" to last select statement to add the 2nd column.

SqlConnection conn = new SqlConnection(@"Data Source=(localdb)\......");
                SqlCommand cmd = conn.CreateCommand();

                string updatedOn;
                conn.Open();
                cmd.CommandText = ("USE CORNERSTONE DECLARE @SID int SET @SID = " + SupplierId + "; "  +
                "with MaxDate as " +
                "(select UpdatedOn, UpdatedBy, SupplierId from CORNERSTONE.Suppliers.Supplier where SupplierId = @SID " +
                "UNION " +
                "select UpdatedOn, UpdatedBy, SupplierId from Suppliers.CompanyLocation where SupplierId = @SID " +
                "UNION " +
                "select UpdatedOn, UpdatedBy, SupplierId from Suppliers.AssignedContact where SupplierId = @SID) " +
                "select UpdatedOn " +
                "from MaxDate " +
                "where UpdatedOn = (Select MAX(UpdatedOn) from MaxDate)");

                updatedOn = cmd.ExecuteScalar().ToString();
                DateTime ParsedDateTime = DateTime.Parse(updatedOn);
                AuditUpdatedOn = ParsedDateTime;

                conn.Close();

Answer 1

I'm assuming that you want the UpdatedBy from the query; if so, this should just be:

cmd.CommandText = @"
with MaxDate as (
    select UpdatedOn, UpdatedBy from CORNERSTONE.Suppliers.Supplier where SupplierId = @SID
    union select UpdatedOn, UpdatedBy from Suppliers.CompanyLocation where SupplierId = @SID
    union select UpdatedOn, UpdatedBy from Suppliers.AssignedContact where SupplierId = @SID
)
select top 1 UpdatedOn, UpdatedBy
from MaxDate 
where UpdatedOn = (Select MAX(UpdatedOn) from MaxDate)
";

However, the problem comes from the fact that you now have two columns to read, so ExecuteScalar isn't useful. There are two options:

1. use ExecuteReader and use the read API, or
2. cheat and get a tool to do it all for you

On the second, frankly: this is a perfect scenario for "Dapper":

(var when, var who) = conn.QuerySingle<(DateTime, string)>(@"
with MaxDate as (
    select UpdatedOn, UpdatedBy from CORNERSTONE.Suppliers.Supplier where SupplierId = @SID
    union select UpdatedOn, UpdatedBy from Suppliers.CompanyLocation where SupplierId = @SID
    union select UpdatedOn, UpdatedBy from Suppliers.AssignedContact where SupplierId = @SID
)
select top 1 UpdatedOn, UpdatedBy
from MaxDate 
where UpdatedOn = (Select MAX(UpdatedOn) from MaxDate)
", new { SID = SupplierId });

and then when and who should be populated. You might want to think about whether you need QuerySingleOrDefault in case there are no rows.

Usually, when you use Query[First/Single/etc]<T> with Dapper, the T is mapped using column names (to populate an object model), but it detects value-tuples ((DateTime, string) is a value-tuple) and treats them by position instead.

---

If you have to use raw ADO.NET - something like the following:

using (var cmd = conn.CreateCommand())
{
    cmd.CommandText = @"
with MaxDate as (
select UpdatedOn, UpdatedBy from CORNERSTONE.Suppliers.Supplier where SupplierId = @SID
union select UpdatedOn, UpdatedBy from Suppliers.CompanyLocation where SupplierId = @SID
union select UpdatedOn, UpdatedBy from Suppliers.AssignedContact where SupplierId = @SID
);
select top 1 UpdatedOn, UpdatedBy
from MaxDate 
where UpdatedOn = (Select MAX(UpdatedOn) from MaxDate)
";
    cmd.CommandType = CommandType.Text;
    var p = cmd.CreateParameter();
    p.ParameterName = "@SID";
    p.Value = SupplierId;
    p.DbType = DbType.Int32;
    cmd.Parameters.Add(p);

    using (var reader = cmd.ExecuteReader())
    {
        if (reader.Read())
        {
            var who = reader.GetDateTime(0);
            var when = reader.GetString(1);
            // *** HERE *** we now know the answers
        }
        else
        {
            // what to do if we don't get anyone?
        }
        do // need to consume entire TDS to
        { // make sure we see errors
            while (reader.Read()) { }
        } while (reader.NextResult());
    }
}