How can I get the single val from a SQL Server CE query that returns one column from one row?

Question

I need to retrieve the val from one column of a table, any record (the val should be the same at any given time in all records; that is to say, if it's "3", it will be "3" in all of them; if it's "17", it will be "17" in all of them (or "42" or whatever)).

So I saw legacy code like this:

DataSet ds = dbconn.getDataSet("SELECT siteNo FROM workTables");

foreach (DataRow row in ds.Tables[0].Rows)
{
    siteNum = row["siteNo"].ToString();
}

...which works, but seems kludgy and wasteful, as a gazillion records could be looped through, and only the value of the last loop is used.

So I tried to "cut to the chase" with this code:

String siteNum = ds.Tables[0].Rows.ToString();

...but got an exception because siteNum is System.Data.DataRowCollection

I then tried this:

String siteNum = ds.Tables[0].Rows[0].ToString();

...but got an exception because siteNum is System.Data.DataRow

So I finally reverted to the legacy kludgy code, pretty much, modifying it like so:

DataSet ds = dbconn.getDataSet("SELECT siteNo FROM workTables");

foreach (DataRow row in ds.Tables[0].Rows)
{
    siteNum = row["siteNo"].ToString();
    // just need the first one
    break;
}

This works, but I'm sure it's not "the preferred method." Who knows a "more better" way?

Answer 1

Just change your query to SELECT TOP 1 siteNo FROM workTables This way you minimize processing on the server and network traffic.

Answer 2

Use ExecuteScalar to return the first column of the first row in a result set:

var result = connection.ExecuteScalar("SELECT siteNo FROM workTables");
if((result != null) && (result != DBNull.Value))
{
    site = Convert.ToInt32(result);
}