C# 选择语句的返回值

Return value of a select statement

提问by mac007

I have this little problem. I want to retrieve the resulting value of a select statement into a string variable. Like this

我有这个小问题。我想将 select 语句的结果值检索到字符串变量中。像这样

OleDbCommand cmd1 = new OleDbCommand();
cmd1.Connection = GetConnection();
cmd1.CommandText = "SELECT treatment FROM appointment WHERE patientid = " + text;
cmd1.ExecuteNonQuery();

I want the to place the selected treatment value into a string variable. How can i do this. Thanks

我希望将选定的处理值放入字符串变量中。我怎样才能做到这一点。谢谢

采纳答案by Soner G?nül

Use ExecuteReader()and not ExecuteNonQuery(). ExecuteNonQuery()returns only the number of rows affected.

使用ExecuteReader()而不是ExecuteNonQuery()。ExecuteNonQuery()仅返回受影响的行数。

try
{
    SqlDataReader dr = cmd1.ExecuteReader();
}
catch (SqlException oError)
{

}
while(dr.Read())
{
    string treatment = dr[0].ToString();
}

Or better, use a usingstatementfor it.

或者更好的是，使用一个using声明。

using(SqlDataReader dr = cmd1.ExecuteReader())
{
    while(dr.Read())
    {
        string treatment = dr[0].ToString();
    }
}

But if your SqlCommandreturns only 1column, you can use the ExecuteScalar()method. It returns first column of the first row as follows:-

但是如果您SqlCommand只返回1列，则可以使用该ExecuteScalar()方法。它返回第一行的第一列，如下所示：-

cmd.CommandText = "SELECT treatment FROM appointment WHERE patientid = " + text;
string str = Convert.ToString(cmd.ExecuteScalar());

Also you can open your code to SQL Injection. Always use parameterized queries. Jeff has a cool blog article called Give me parameterized SQL, or give me death. Please read it carefully. Also read DotNetPerl SqlParameterarticle. SQL Injection very important when you are working queries.

您也可以将代码打开到SQL Injection。始终使用参数化查询。Jeff 有一篇很酷的博客文章，名为“给我参数化 SQL，或者给我死”。请仔细阅读。另请阅读DotNetPerl SqlParameter文章。SQL 注入在您处理查询时非常重要。

回答by Rich Andrews

You just need to use the ExecuteScalar method of the command - this will give you the value at the first row and column of the result set.

您只需要使用命令的 ExecuteScalar 方法 - 这将为您提供结果集第一行和第一列的值。

OleDbCommand cmd1 = new OleDbCommand();
cmd1.Connection = GetConnection();
cmd1.CommandText = "SELECT treatment FROM appointment WHERE patientid = " + text;
var result = cmd1.ExecuteScalar();

If your SQL statement returns more than one row/column then you can use ExecuteReader().

如果您的 SQL 语句返回不止一行/列，那么您可以使用 ExecuteReader()。

回答by tschmit007

the answer:

答案：

String res = cmd1.ExecuteScalar();

the remark: use parametrized query to prevent sql injection

备注：使用参数化查询防止sql注入

回答by Oleg Kovalov

You need to use OleDbAdapter.

您需要使用 OleDbAdapter。

string connection = "your connection";
string query = "SELECT treatment FROM appointment WHERE patientid = " + text;
OleDbConnection conn = new OleDbConnection(connection);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(query, conn);
adapter.Fill(dataset);

回答by Vishal Suthar

Execute Scalar: Getting Single Value from the Databasemethod to retrieve a single value (for example, an aggregate value) from a database.

Execute Scalar: Getting Single Value from the Database方法以从数据库中检索单个值（例如，聚合值）。

cmd1.Connection = GetConnection();
cmd1.CommandText = "SELECT treatment FROM appointment WHERE patientid = " + text;
if(cmd.ExecuteScalar()==null)
{
    var treatment = cmd.ExecuteScalar();
}

Other Way: ExecuteReader()

其他方式：ExecuteReader()

try
{
    cmd1.CommandText ="SELECT treatment FROM appointment WHERE patientid=@patientID";
    cmd1.Parameters.AddWithValue("@patientID", this.DropDownList1.SelectedValue);

    conn.Open();
    SqlDataReader dr = cmd1.ExecuteReader();
    while (dr.Read())
    {
        int PatientID = int.Parse(dr["treatment"]);
    }
    reader.Close();
    ((IDisposable)reader).Dispose();//always good idea to do proper cleanup
}
catch (Exception exc)
{
    Response.Write(exc.ToString());
}

回答by Clarence Klopfstein

There is a lot wrong with your example code.

您的示例代码有很多错误。

1. You have inline sql, which opens you up to sql injection in a major way.

2. You are using ExecuteNonQuery() which means you get no data back.

    string sSQL = "SELECT treatment FROM appointment WHERE patientid = @patientId";
    OleDbCommand cmd1 = new OleDbCommand(sSQL, GetConnection()); // This may be slight different based on what `GetConnectionReturns`, just put the connection string in the second parameter.
   
   
       cmd1.Parameters.AddWithValue("@patientId", text);
       SqlDataReader reader = cmd1.ExecuteReader();
       string returnValue;
       while(reader.Read())
       {
          returnValue = reader[0].ToString();
       }
   

1. 你有内联 sql，它以一种主要的方式打开了你的 sql 注入。

2. 您正在使用 ExecuteNonQuery() 这意味着您没有返回任何数据。

    string sSQL = "SELECT treatment FROM appointment WHERE patientid = @patientId";
    OleDbCommand cmd1 = new OleDbCommand(sSQL, GetConnection()); // This may be slight different based on what `GetConnectionReturns`, just put the connection string in the second parameter.
   
   
       cmd1.Parameters.AddWithValue("@patientId", text);
       SqlDataReader reader = cmd1.ExecuteReader();
       string returnValue;
       while(reader.Read())
       {
          returnValue = reader[0].ToString();
       }
   

回答by Siriojka

SqlConnection dbConnect = new SqlConnection("your SQL connection string");    
string name = " 'ProjectName' ";
string strPrj = "Select e.type, (e.surname +' '+ e.name) as fulln from dbo.tblEmployees e where id_prj = " + name;
        SqlCommand sqlcmd = new SqlCommand(strPrj, dbConnect);
        SqlDataAdapter sda = new SqlDataAdapter(strPrj, dbConnect);
        ds = new DataSet();
        sda.Fill(ds);
        dbConnect.Open();
        sqlcmd.ExecuteNonQuery();
        dbConnect.Close();