SqlCommand.ExecuteScalar - 指定特定数据项

发布于 11-13 10:52 字数 209 浏览 10 评论 0原文

我有一个返回 10 列数据的存储过程。使用 cmd.ExecuteScalar() 会返回第一条记录中第一列的值。

如何更改此设置，以便可以通过指定别名/数据项名称来返回不同的列？

我希望能够做类似的事情：

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

枫以2024-11-20 10:52:39

您可以创建一个调用 ExecuteReader 的方法，然后使用 GetOrdinal 和您的列名来调用 GetString。

我的 VB 不存在，但这是扩展方法的 C#。

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

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

回复收藏 0 原文

指尖微凉心微凉2024-11-20 10:52:39

你不能。 Command.ExecuteScalar 不带任何参数..

您可以做的是使用文本命令并修改其 CommandText 属性值以包含您需要获取的列：

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

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 = " + ...

回复收藏 0 原文

人事已非2024-11-20 10:52:39

您可以创建一个扩展方法来为您执行此操作。 C# 等效项（我想您可以将其转换为 VB.NET 扩展相当容易）：

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

...并像这样调用它：

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

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");

回复收藏 0 原文

秋心╮凉2024-11-20 10:52:39

您也应该尝试下面的代码。

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

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

回复收藏 0 原文

~没有更多了~