How do get a simple string from a database

Question

The following code does not work. There is only 1 row in this table. How do I just get the statement that the sql would return?:

SqlConnection conn = new SqlConnection(connectionStringArg);
SqlCommand command = new SqlCommand("select applicationname from tbl_settings");
command.Connection = conn;
conn.Open();
string simpleValue = command.ExecuteReader()[0].ToString();
conn.Close();
return simpleValue;

Okay any help on how to achieve this relatively simple task would be great.

Answer 1

Since there's only a single value returned, you could do this:

 string value = (string)command.ExecuteScalar();

If you need more than the first column from the first row returned, you'll need to use ExecuteReader(). See driis' answer.

Answer 2

The DataReader can't be indexed like that. You need something like:

using(conn)
using(var reader = command.ExecuteReader()) 
{
    reader.Read();
    string simpleValue = reader.GetString(0);
}

The general idea is to advance the reader for each record (using Read), then you can read values from the row. As pointed out in another answer, if you know there is only one single value in the result set, command.ExecuteScalar() gives you just that.

Answer 3

You have to call the Read method on a DataReader returned from ExecuteReader to get to the first row. Something like this:

        SqlDataReader rdr = command.ExecuteReader();
        if (rdr.Read())
             ...

Answer 4

You'd do e.g.

    using(SqlConnection conn = new SqlConnection(connectionStringArg))
    using(SqlCommand command = new SqlCommand("select applicationname from tbl_settings")) {

        command.Connection = conn;
        conn.Open();
        SqlDataReader reader = command.ExecuteReader();
        if(reader.read()) {
            return reader.GetString(0);
        }
        return null;
    }

Answer 5

For a single string you want to use ExecuteScalar to get a single value and then attempt to cast it to string. From doc: 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.

Example:
string result = command.ExecuteScalar() as string;

For small stuff I often find LINQ2SQL the easiest to set up and use. This tutorial should get you up and running in a few minutes. For bigger projects LINQ2SQL is considered obsolete, see this question for discussion and alternatives.

Answer 6

string applicationname = "";
using(var reader = command.ExecuteReader()) 
 {
    reader.Read();
    applicationname = reader.GetString(reader.GetOrdinal("applicationname"));
 }

Answer 7

var query_result = com.ExecuteScalar() as string;
if (!string.IsNullOrEmpty(query_result )) // do your thing here...

Answer 8

Check DataReader.Read() you need to call it.