Simple SQL select in C#?

Question

On my current project, to get a single value (select column from table where id=val), the previous programmer goes through using a datarow, datatable and an sqldatadapter (and of course sqlconnection) just to get that one value.

Is there an easier way to make a simple select query? In php, I can just use mysql_query and then mysql_result and I'm done.

It would be nice if I could just do:

SqlConnection conSql = new SqlConnection(ConnStr);
SomeSqlClass obj = new SomeSqlClass(sql_string, conSql);
conSql.Close();
return obj[0];

Thanks for any tips.

Answer 1

You can skip the DataReader and the DataAdapter and just call ExecuteScalar() on the sql command.

using (SqlConnection conn = new SqlConnection(connString))
{
      SqlCommand cmd = new SqlCommand("SELECT * FROM whatever 
                                       WHERE id = 5", conn);
        try
        {
            conn.Open();
            newID = (int)cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
 }

Answer 2

Actually, there is a method SqlCommand.ExecuteScalar() that will simply return the first field from the first row of the returned results. Just for you.

.NET Framework Class Library SqlCommand..::.ExecuteScalar Method

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.

Answer 3

you can use SqlCommands executeScalar function. Please look at the following link

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx

Answer 4

You can do something very similar:

using (SqlConnection conn = new SqlConnection(ConnStr))
using (SqlCommand cmd = new SqlCommand(sql_string, conn))
{
    conn.Open();
    return cmd.ExecuteScalar();
}

Answer 5

You are probably looking for SqlCommand and SqlDataReader

Dictionary<int, string> users = new Dictionary<int, string>();
using(SqlConnection connection = new SqlConnection("Your connection string"))
{
    string query = "SELECT UserId, UserName FROM Users";
    SqlCommand command = new SqlCommand(query, connection);
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
            users.Add(reader.GetInt32(0), reader.GetString(1));
        reader.Close();
    }
    connection.Close();
}