C# 从 sql ExecuteScalar() 中检索值

Retrieving value from sql ExecuteScalar()

提问by haysam

I have the following:

我有以下几点：

String sql = "SELECT * FROM Temp WHERE Temp.collection  = '" + Program.collection + "'";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
Program.defaultCollection = (String)cmd.ExecuteScalar();

And I want to get the second column after executing the statement. I know it will return only one row with two columns

我想在执行语句后获取第二列。我知道它只会返回一行两列

I have read online that I will have to read each row of the result, is there any other way?

我在网上读到我必须阅读结果的每一行，还有其他方法吗？

采纳答案by Mike Perrenoud

ExecuteScalargets the first column from the first row of the result set. If you need access to more than that you'll need to take a different approach. Like this:

ExecuteScalar从结果集的第一行获取第一列。如果您需要访问更多内容，则需要采取不同的方法。像这样：

DataTable dt = new DataTable();
SqlDataAdapater sda = new SqlDataAdapter(sql, conn);
sda.Fill(dt);

Program.defaultCollection = dt.Rows[0]["defaultCollection"];

Now, I realize that the field name may not be defaultCollection, but you can fill that in.

现在，我意识到字段名称可能不是defaultCollection，但您可以填写它。

From the MSDN documentationfor ExecuteScalar:

从MSDN文档为ExecuteScalar：

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

执行查询，返回查询返回的结果集中第一行的第一列。其他列或行将被忽略。

Now, as a final bit of advice, please wrap all ADO.NET objects in a usingstatement. Like this:

现在，作为最后一点建议，请将所有 ADO.NET 对象包装在一个using语句中。像这样：

using (SqlConnection conn = new SqlConnection(connString))
using (SqlDataAdapter sda = new SqlDataAdapter(sql, conn))
{
   DataTable dt = new DataTable();
   sda.Fill(dt);

   // do something with `dt`
}

this will ensure they are properly disposed.

这将确保它们得到妥善处置。

回答by Ehsan

And I want to get the second column after executing the statement

我想在执行语句后得到第二列

It is not possible with execute scalar.

执行标量是不可能的。

is there any other way

有没有其他办法

You have 2 options here either to use SqlDataAdapter or SqlDataReader. For you using DataReader is a recommendedapproach as you don't need offline data or do other worh

您在这里有 2 个选项可以使用 SqlDataAdapter 或 SqlDataReader。 For you using DataReader is a recommended方法，因为您不需要离线数据或做其他事情

by using SqlDataAdapter

通过使用 SqlDataAdapter

using (SqlConnection c = new SqlConnection(
    youconnectionstring))
    {
    c.Open();
    /
    using (SqlDataAdapter a = new SqlDataAdapter(sql, c))
    {

        DataTable t = new DataTable();
        a.Fill(t);
        if(t.Rows.Count > 0)
         {
             string text  = t.Rows[0]["yourColumn"].ToString();
         }    
    }
    }

by using DataREader

通过使用 DataReader

 using (SqlConnection connection =
               new SqlConnection(connectionString))
    {
        SqlCommand command =
            new SqlCommand(sql, connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();


        while (reader.Read())
        {
            //read data here
            string text = reader.GetString(1) 
        }


        reader.Close();
    }

回答by BendEg

You can use a DataReader and read only the first column like:

您可以使用 DataReader 并仅读取第一列，例如：

IDataReader cReader = cmd.ExecuteReader();

if(cReader.Read())
{
    string cText = cReader.GetString(1); // Second Column
}

回答by Nir Kornfeld

SqlCommand.ExecuteScalar()can be used onlywhen the result have just onerow and onecolumn.

SqlCommand.ExecuteScalar()可用于只有当结果正好有一个行和一个列。

If you need more than one column to be returned, you should use something like this:

如果您需要返回多于一列，则应使用以下内容：

String sql = "SELECT * FROM Temp WHERE Temp.collection  = '" + Program.collection + "'";
SqlConnection conn = new SqlConnection(connString);
using(SqlCommand cmd = new SqlCommand(sql, conn))
{
    using(SqlDataReader rdr = cmd.ExecuteReader())
    {
        if(rdr.Read()) 
        {
            Program.defaultCollection = (String)rdr["Column1"];
            Program.someOtherVar = (String)rdr["Column2"];
        }
    }
    rdr.Close();
}

That will be the fastest way.

那将是最快的方式。

回答by the_lotus

ExecuteScalar only returns one value. You have to make sure your query only returns that value.

ExecuteScalar 只返回一个值。您必须确保您的查询仅返回该值。

String sql = "SELECT temp.defaultCollection FROM Temp WHERE Temp.collection  = '" + Program.collection + "'";

On a side note, read on SqlParameter. You don't want to concatenate values like that, you'll have a problem when the collection property contains a quote.

附带说明，请阅读SqlParameter。您不想连接这样的值，当集合属性包含引号时会出现问题。