从Select Command SQL Server获取结果

I am trying to get the result from a select command:

我试图从select命令获取结果:

 string strName = dtTable.Rows[i][myName].ToString();
 string selectBrand = "SELECT [brand] FROM [myTable] WHERE [myName] = '" + strName + "'";

 SqlCommand sqlCmdSelectBrand = new SqlCommand(selectBrand , sqlConn);
 sqlCmdSelectBrand .Connection.Open();
 sqlCmdSelectBrand .ExecuteNonQuery();                         

 string newBrand = Convert.ToString(sqlCmdSelectBrand .ExecuteScalar());                          
 sqlCmdSelectBrand .Connection.Close();

The select works, I have executed it in SQL Studio, but it does not assign to my variable on the second to last line. Nothing gets assigned to that variable when I debug it...

选择工作,我已经在SQL Studio中执行了它,但它没有在第二行到最后一行分配给我的变量。调试时没有任何内容被分配给该变量...

Any advice?

1 个解决方案

#1

Your approach to read data returned from a SELECT query is (in this particular context) a bit wrong. Usually you call ExecuteReader of the SqlCommand instance to get back your data.

从SELECT查询返回的读取数据的方法是(在此特定上下文中)有点错误。通常,您调用SqlCommand实例的ExecuteReader来获取数据。

string strName = dtTable.Rows[i][myName].ToString();
string selectBrand = "SELECT [brand] FROM [myTable] WHERE [myName] = @name";

using(SqlCommand sqlCmdSelectBrand = new SqlCommand(selectBrand , sqlConn))
{
    sqlCmdSelectBrand.Parameters.Add(
             new SqlParameter("@name", SqlDbType.NVarChar)).Value = strName;
    sqlCmdSelectBrand .Connection.Open();
    using(SqlDataReader reader = sqlCmdSelectBrand.ExecuteReader())
    {
        if(reader.HasRows)
        {
           reader.Read();
           string newBrand = reader.GetString(reader.GetOrdinal("Brand"));
           ..... work with the string newBrand....
        }
        else
            // Message for data not found...

        sqlCmdSelectBrand .Connection.Close();
    }
}

In your context, the call to ExecuteNonQuery is not required because it doesn't return anything from a SELECT query. The call to ExecuteScalar should work if you have at least one record that match to the WHERE condition

在您的上下文中,不需要调用ExecuteNonQuery,因为它不会从SELECT查询返回任何内容。如果您至少有一条记录与WHERE条件匹配,则对ExecuteScalar的调用应该有效

Notice also that you should always use a parameterized query when building an sql command text. Also if you think to have full control of the inputs, concatenating string is the open door to Sql Injection

另请注意,在构建sql命令文本时,应始终使用参数化查询。此外,如果您认为可以完全控制输入,连接字符串是Sql Injection的大门

#1