2

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();
Improve this question
6
  • btw: you should pass SID in as a parameter rather than using concatenation - safer and more efficient (query plan reuse, etc) - i.e. cmd.Parameters.AddWithValue("@SID", SupplierId); (goes back to trying to understand what the code is doing) Commented Jun 3, 2020 at 13:02
  • also: pro tip - use a verbatim string literal for SQL, then you don't need to keep doing newlines with quotes and + just @" ... " with the ... including newlines Commented Jun 3, 2020 at 13:04
  • NOTED thanks I'll mess around with that. see what I can do to add that in. Commented Jun 3, 2020 at 13:04
  • Why are you using ExecuteScalar which only returns one column when you are selecting multiple columns? See : learn.microsoft.com/en-us/dotnet/api/… Commented Jun 3, 2020 at 13:05
  • well the reasoning for that is i was having a hard time figuring it out how to get info any other way that would actually work I'll check to link to see if I can get more info I failed to find when creaating Commented Jun 3, 2020 at 13:07

1 Answer 1

Reset to default
3

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());
    }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for the help I will try these out along with your other advice and see what happens!
@RodeoEmpire note: if you use Dapper (the second example), the parameterization is even easier - see example
@RodeoEmpire I also added an example using raw ADO.NET - frankly, my advice here is "just use Dapper" (although I'm perhaps biased, but after 44 million downloads it can't be entirely wrong)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.