CODEWITHSUNDEEP
c#t-sqlstored-proceduressql-server-2014executescalar
John Straka
John Straka

Reputation: 1914

Access column name with ExecuteScalar in C#?

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#?

Upvotes: 1

Views: 361

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460158

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)
        }
    }
}

Upvotes: 5

Dmitri Trofimov
Dmitri Trofimov

Reputation: 763

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.

Upvotes: 4

Related Questions