SqlCommand.ExecuteScalar - specify a particular data item

Question

I have a Stored Procedure which returns 10 columns of data. Using cmd.ExecuteScalar() returns the value of the 1st column from the 1st record.

How can I change this so that I can return different columns, by specifying their alias/dataitem name?

I want to be able to do something like:

Dim FirstName as String = cmd.ExecuteScalar("FirstName")

score 2 · Accepted Answer · answered Jun 01 '11 at 16:31

2

You could create a method that calls ExecuteReader, and then uses GetOrdinal with your column name to then call GetString.

My VB is non-existent, but this is the C# for an extension method.

public static class SqlCommandExt
{
    public static string ExecuteScalar(this SqlCommand cmd, string columnName)
    {
        using (var reader = cmd.ExecuteReader())
        {
            if (!reader.Read())
                return null;

            var index = reader.GetOrdinal(columnName);
            return reader.GetString(index);
        }
    }
}

answered Jun 01 '11 at 16:31

Ray

45,695
27
126
169

Thanks Ray, I was considering building a function which uses SqlDataReader, but I didn't want to create a workaround if there was something already built into the framework which supports this, but it seems there isnt. 1 question, as a fail-safe, if columnName doesn't match any of the columns in the SqlCommand, will "return reader.GetString(index)" return an error or just NULL? Cheers – Curtis Jun 02 '11 at 08:11
1

GetOrdinal will throw a IndexOutOfRangeException – Ray Jun 02 '11 at 08:29
@Gens: You'd rather I just didn't answer? – Ray Jun 04 '11 at 17:11
@Ray: I will return the apple if my original order is orange. – Predator Jun 04 '11 at 17:14
Order? What are you talking about? It's not like anyone is paying anything. – Ray Jun 06 '11 at 08:27
This is a .NET question... and this answer was adequate in conveying the _.NET_ solution which works in either C# or VB.NET. Gens has posted at least twice in this thread and neither comment was useful. – canon Jun 06 '11 at 13:16

score 1 · Answer 2 · answered Jun 01 '11 at 16:24

1

You can not. Command.ExecuteScalar take no parameters ..

What you can do is to use a text command and modify its CommandText property value to include the column you need to get:

command.CommandText = "SELECT " + columnName + " FROM Table WHERE Key = " + ...

answered Jun 01 '11 at 16:24

Akram Shahda

14,655
4
45
65

Is there something similar though? cmd.ExecuteScalar("FirstName") was just an example of what I'm after – Curtis Jun 01 '11 at 16:26

score 1 · Answer 3 · answered Jun 01 '11 at 16:38

You could create an extension method to do this for you. C# equiv (which I imagine you could translate to a VB.NET extension rather easily):

public static T ExecuteScalar<T>(this SqlCommand cmd, String columnName)
{
    using(var reader = cmd.ExecuteReader())
    {
        var item = default(T);
        if(reader.Read())
        {
            item = (T)dataReader.GetValue(dataReader.GetOrdinal(columnName))
        }
        return item;
    }
}

... and invoke it like so:

var firstName = cmd.ExecuteScalar<String>("FirstName");

It's .NET, buddy... the two are mostly interchangeable. You'll note that the accepted answer was also C#. Thanks for trawling old answers doling out down-votes, though. — canon, Jun 06 '11 at 13:10

score 0 · Answer 4 · answered Jun 02 '11 at 08:18

You should try the below code also.

Private Sub YourFunctionName()
        Using con As System.Data.SqlClient.SqlConnection = New SqlConnection("YourConnection string")
            con.Open()
            Using cmd As SqlCommand = New SqlCommand
                Dim expression As String = "Parameter value"
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "Your Stored Procedure"
                cmd.Parameters.Add("Your Parameter Name", SqlDbType.VarChar).Value = expression
                cmd.Connection = con
                Using dr As IDataReader = cmd.ExecuteReader()
                    If dr.Read() Then
                        Dim str As String = dr("YourColumnName").ToString()
                    End If
                End Using
            End Using
        End Using
    End Sub

SqlCommand.ExecuteScalar - specify a particular data item

4 Answers4