c# - Getting Result From Select Command SQL Server

Question

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

 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...

Any advice?

Answer 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.

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

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