retrieve single value from dataset

[Peter]

Gday,

I have a dataset with multiple tables, from which I want to access a
single value in one of those tables.

I know I can do something like:

Decimal myVar =
(Decimal)dsMyDataSet.Tables["MyTable"].Rows[intRowPos]["MyColumn"];

But what if I do not know the rowposition? Can I perform some sort of
sql operation on my dataset to retrieve just a single value?

From what I've read it looks like you have to somehow create a datarow

that matches the row you're interested in, then get the column from
there? Surely there is a simpler way?

Thanks,

Peter

[Steven Nagy]

Are you saying that you want to get a single value that is an agregate
or something similar?
You want to perform some function on your table to get a single result
(sometimes called a 'scalar') ?

Because a table is multiple rows and multiple columns, naturally you
have many values, and you need to pick which ones you want to use via
the code you entered in your post. You can look through tables and
columns to get values as well.
For example, imagine a table in a dataset that has 3 rows and 4
columns, and in each cell there is a decimal number. You could add them
all up like this:

decimal d = 0;
for (int i=0;i<3;i++) {
for (int j=0;j<4;j++) {
d += (decimal)dsMyDataSet.Tables["MyTable"].Rows[i][j];
}
}

Sometimes people want to query their database and get only 1 value
back. Rather than using a dataset, an easier way is to use the
"ExecuteScalar" method of a DataReader. This lets you pass your exact
SQL and returns a single value for you to assign to a variable.

Usually datasets and datatables are used for multiple rows of data
being returned.

[Peter]

Thanks for the reply. Actually Im not after an aggregate, I just want
to execute something like:

myVal = select <value> from <table> where <criteria>;

but do this on my existing dataset, rather than creating a datareader
object if I dont have to, or some other method...

I *think* it should be possible but doing searches on MSDN has so far
not yielded anything to what I want. However if it is as you say, that
datasets and datatables can only be used for multiple rows then I guess
I will have to.

Thanks for the comment about the 'ExecuteScalar' method for a
datareader though. I assume you would use this if you knew you were
only ever getting a single value from your query, and would be used in
place of 'ExecuteReader'?

Thanks,

Peter

[Steven Nagy]

Yes, ExecuteScalar returns 1 value, ExecuteReader returns a dataReader
for multiple rows, although its theoretically possible that only one 1
row with 1 column is returned.
If you KNOW that your dataset has only 1 row with 1 column in it, then
you can reference that value as:
double d = (decimal)dsMyDataSet.Tables[0].Rows[0][0];

In your scenario, would it be possible to further filter the data
BEFORE it gets delivered into the dataset?
For example, you could use a stored procedure to refine what's being
returned, or even a View, instead of selecting directly from the table.
I guess without really knowing what you're trying to achieve, its hard
to suggest an 'ideal' solution.
What kinds of data to you expect to be in the dataset?

[Scott C]

Steven Nagy wrote:

Yes, ExecuteScalar returns 1 value, ExecuteReader returns a dataReader
for multiple rows, although its theoretically possible that only one 1
row with 1 column is returned.
If you KNOW that your dataset has only 1 row with 1 column in it, then
you can reference that value as:
double d = (decimal)dsMyDataSet.Tables[0].Rows[0][0];

In your scenario, would it be possible to further filter the data
BEFORE it gets delivered into the dataset?
For example, you could use a stored procedure to refine what's being
returned, or even a View, instead of selecting directly from the table.
I guess without really knowing what you're trying to achieve, its hard
to suggest an 'ideal' solution.
What kinds of data to you expect to be in the dataset?

I think what you might be looking for is in DataTable, not DataSet. The
following is from MSDN:

<quote>
private void GetRowsByFilter()
{
DataTable table = DataSet1.Tables["Orders"];
// Presuming the DataTable has a column named Date.
string expression;
expression = "Date > '1/1/00'";
DataRow[] foundRows;

// Use the Select method to find all rows matching the filter.
foundRows = table.Select(expression);

// Print column 0 of each returned row.
for(int i = 0; i < foundRows.Length; i ++)
{
Console.WriteLine(foundRows[i][0]);
}
}
</quote>
hth,
scott

[Biren Prajapati]

Just for retrieving specific single value from your dataset's
datatable, I think the following will help you.

Decimal myVar =
(Decimal)dsMyDataSet.Tables["MyTable"].Select(expression)[0]["fieldName"];

[Peter]

Hey thanks - that's what I was after.

Regards,

Peter

[Peter]

Steven Nagy wrote:

Yes, ExecuteScalar returns 1 value, ExecuteReader returns a dataReader
for multiple rows, although its theoretically possible that only one 1
row with 1 column is returned.
If you KNOW that your dataset has only 1 row with 1 column in it, then
you can reference that value as:
double d = (decimal)dsMyDataSet.Tables[0].Rows[0][0];

This is what I eventually used, however I wanted a single value from a
table with multiple rows, so I also had to use the select method on
Tables, and use a rowfilter...
In your scenario, would it be possible to further filter the data
BEFORE it gets delivered into the dataset?
For example, you could use a stored procedure to refine what's being
returned, or even a View, instead of selecting directly from the table.

What I have is a dataset that has multiple tables in it. One of these
tables has just one row in it that used for editing and this data is
being displayed on a form. The other tables are used for lookupvalues
in combo boxes. The edit table is joined to lookup_table1 (that
provides values for combo box1) and that is joined to lookup_table2
(that provides values for combo box 2). Combo box 2 is initially set
from combo box 1 (whatever is loaded) then after that, modifying combo
box 2 alters the set of selectable items in combo box 1...

Does that make *some* sort of sense?

If anyone would like some code, or further explanation of what it is I
am doing am happy to provide it.

Thanks,

Peter