Access column name with ExecuteScalar in C#?

Question

I have a stored procedure that returns 0 or 1 depending on certain outcomes. I often execute this procedure manually, so to have a description of the success/failure that's easily viewed in SSMS but still readable as 0/1 in code, I select the 0 or 1 as a different column name, i.e. SELECT 0 AS ThisReason or SELECT 0 AS ThatReason.

There is almost certainly a better way to handle this, but it got me curious - is it possible to read the name of the column you've selected when using ExecuteScalar in C#?

score 5 · Accepted Answer · answered Apr 15 '16 at 12:21

Not with ExecuteScalar but with ExecuteReader and SqlDataReader.GetName:

using (var con = new SqlConnection("connection-string"))
using (var cmd = new SqlCommand("storedprocedurename", con))
{
    cmd.CommandType = CommandType.StoredProcedure;
    // parameters here...
    con.Open();
    using (var rd = cmd.ExecuteReader())
    {
        if (rd.Read())
        {
            string column = rd.GetName(0); // first column
            int value = rd.GetInt16(0);    // or rd.GetInt32(0)
        }
    }
}

score 4 · Answer 2 · answered Apr 15 '16 at 12:20

What you want to do is to get two bits of information as a result of a query. To use ExecuteScalar you will need to first "pack" those two bits into one. For example you could return a string starting with "+" or "-" indicating the success/failure, and the rest of the string could be a "reason".

There is no other way to do this with ExecuteScalar.

Access column name with ExecuteScalar in C#?

2 Answers2