Selecting from DataSet (C#)

[=?Utf-8?B?anAybXNmdA==?=]

In Visual Studio 2005, I have my DataSet filled with several static tables
(they might get changed weekly, but that's about it).

With one of these tables in the DataSet, is it possible to select distinct
items?

Ex:
I want to fill a tree with a list of job titles, and the Employee table
isalready filled with all of our employee records. Is there a way I could do
something like this below?

DataRow[] drTitles = DataSet1.Tables["Employees"].Select("Distin ct JobTitle");

(the line above will fail with "Syntax error: Missing operand after JobTitle
operator.")

I'm double posting this question. I think it is more relivant in the SQL
boards, but they do not see much activity; hence, the question is being
posted here too.

[Nicholas Paldino [.NET/C# MVP]]

Re: Selecting from DataSet (C#)

You would have to do it yourself. I would do something like this:

public IEnumerable<str ingGetDistinctJ obTitles(DataTa ble table)
{
// The dictionary that indicates if you got the distinct job title or
not.
Dictionary<stri ng, boolreturnedJob Titles = new
Dictionary(tabl e.Rows.Count);

// Cycle through the rows. If the job title has been returned, then
skip, otherwise,
// add to the dictionary and get out.
foreach (DataRow row in table.Rows)
{
// The job title.
string jobTitle = (string) row["jobTitle"];

// Check the dictionary.
if (!returnedJobTi tles.ContainsKe y(jobTitle))
{
// Add to the dictionary.
returnedJobTitl es.Add(jobTitle , true);

// Return.
yield return jobTitle;
}
}
}

From there, you could easily populate an array, or cycle through the
distinct job titles as needed. Using LINQ, it would be even easier, but
this should work fine for VS.NET 2005.


--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard. caspershouse.co m



"jp2msft" <jp2msft@discus sions.microsoft .comwrote in message
news:01EF7488-7457-4209-A0FD-D8D5060DEEC8@mi crosoft.com...

In Visual Studio 2005, I have my DataSet filled with several static tables
(they might get changed weekly, but that's about it).
>
With one of these tables in the DataSet, is it possible to select distinct
items?
>
Ex:
I want to fill a tree with a list of job titles, and the Employee table
isalready filled with all of our employee records. Is there a way I could
do
something like this below?
>
DataRow[] drTitles = DataSet1.Tables["Employees"].Select("Distin ct
JobTitle");
>
(the line above will fail with "Syntax error: Missing operand after
JobTitle
operator.")
>
I'm double posting this question. I think it is more relivant in the SQL
boards, but they do not see much activity; hence, the question is being
posted here too.

[=?Utf-8?B?anAybXNmdA==?=]

Re: Selecting from DataSet (C#)

Thanks for the clarification.

Also, double thanks for the slick code example! I have often wondered what I
could use IEnumerables for or put a Dictionary object to use. I need more C#
theory before I can really understand what these are doing, I guess.

As for "yield return jobTitle": Haha. I have no idea what this is doing, and
I've only seen it once before.

My hope is that by including this snippet of yours in my code, I can revisit
it often and understand it soon.

Thanks for the help!

"Nicholas Paldino [.NET/C# MVP]" wrote:

You would have to do it yourself. I would do something like this:
>
public IEnumerable<str ingGetDistinctJ obTitles(DataTa ble table)
{
// The dictionary that indicates if you got the distinct job title or
not.
Dictionary<stri ng, boolreturnedJob Titles = new
Dictionary(tabl e.Rows.Count);
>
// Cycle through the rows. If the job title has been returned, then
skip, otherwise,
// add to the dictionary and get out.
foreach (DataRow row in table.Rows)
{
// The job title.
string jobTitle = (string) row["jobTitle"];
>
// Check the dictionary.
if (!returnedJobTi tles.ContainsKe y(jobTitle))
{
// Add to the dictionary.
returnedJobTitl es.Add(jobTitle , true);
>
// Return.
yield return jobTitle;
}
}
}
>
From there, you could easily populate an array, or cycle through the
distinct job titles as needed. Using LINQ, it would be even easier, but
this should work fine for VS.NET 2005.
>
>
--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard. caspershouse.co m
>
>
>
"jp2msft" <jp2msft@discus sions.microsoft .comwrote in message
news:01EF7488-7457-4209-A0FD-D8D5060DEEC8@mi crosoft.com...

In Visual Studio 2005, I have my DataSet filled with several static tables
(they might get changed weekly, but that's about it).

With one of these tables in the DataSet, is it possible to select distinct
items?

Ex:
I want to fill a tree with a list of job titles, and the Employee table
isalready filled with all of our employee records. Is there a way I could
do
something like this below?

DataRow[] drTitles = DataSet1.Tables["Employees"].Select("Distin ct
JobTitle");

(the line above will fail with "Syntax error: Missing operand after
JobTitle
operator.")

I'm double posting this question. I think it is more relivant in the SQL
boards, but they do not see much activity; hence, the question is being
posted here too.

>
>
>

[Nicholas Paldino [.NET/C# MVP]]

Re: Selecting from DataSet (C#)

Here is a little more information.

This method could be made static, btw, since it doesn't rely on the
state of the object.

This method will return an implementation of IEnumerable<str ingwhich
when iterated through (when you use it in a foreach statement), will return
the distinct job titles.

It does this through iterators in C# (introduced in C# 2.0). Basically,
instead of gathering all the data together at once, it returns an object
which will perform the iteration as needed. This is referred to as deferred
execution. The IEnumerable<str ingthat is returned doesn't have any
knowledge of the items in it, it just knows which items to return when
queried.

There are some subtleties that can arise from using this. For example,
if you call this, then insert a new job title into the dataset before you
call foreach on the IEnumerable<str ingreturned, then that new job title
will be returned in the result set when you iterate over the
IEnumerable<str ing>:

// Get the IEnumerable<str ing>
IEnumerable<str ingjobTitles = GetDistinctJobT itles(...);

// Add a new distinct job title here to the data set.

// Iterate here, the new job title will appear.
foreach (string jobTitle in jobTitles)
...

The code in GetDistinctJobT itles is used by the compiler to create the
implementation of IEnumerable<str ingthat is returned to you. If you look
at your code in Reflector, you will see it.

For the most part, "yield return" tells the compiler when to stop when
MoveNext is called on IEnumerable<str ing>, as well as which item to return
through the Current property.

If you need the semantics of the entire set in memory, then you can pass
the return value of GetDistinctJobT itles to the constructor of a
List<stringclas s and pass that around.

--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard. caspershouse.co m

"jp2msft" <jp2msft@discus sions.microsoft .comwrote in message
news:37CAA257-C6B2-42ED-A18D-394042D70B41@mi crosoft.com...

Thanks for the clarification.
>
Also, double thanks for the slick code example! I have often wondered what
I
could use IEnumerables for or put a Dictionary object to use. I need more
C#
theory before I can really understand what these are doing, I guess.
>
As for "yield return jobTitle": Haha. I have no idea what this is doing,
and
I've only seen it once before.
>
My hope is that by including this snippet of yours in my code, I can
revisit
it often and understand it soon.
>
Thanks for the help!
>
"Nicholas Paldino [.NET/C# MVP]" wrote:
>

> You would have to do it yourself. I would do something like this:
>>
>public IEnumerable<str ingGetDistinctJ obTitles(DataTa ble table)
>{
> // The dictionary that indicates if you got the distinct job title or
>not.
> Dictionary<stri ng, boolreturnedJob Titles = new
>Dictionary(tab le.Rows.Count);
>>
> // Cycle through the rows. If the job title has been returned, then
>skip, otherwise,
> // add to the dictionary and get out.
> foreach (DataRow row in table.Rows)
> {
> // The job title.
> string jobTitle = (string) row["jobTitle"];
>>
> // Check the dictionary.
> if (!returnedJobTi tles.ContainsKe y(jobTitle))
> {
> // Add to the dictionary.
> returnedJobTitl es.Add(jobTitle , true);
>>
> // Return.
> yield return jobTitle;
> }
> }
>}
>>
> From there, you could easily populate an array, or cycle through the
>distinct job titles as needed. Using LINQ, it would be even easier, but
>this should work fine for VS.NET 2005.
>>
>>
>--
> - Nicholas Paldino [.NET/C# MVP]
> - mvp@spam.guard. caspershouse.co m
>>
>>
>>
>"jp2msft" <jp2msft@discus sions.microsoft .comwrote in message
>news:01EF748 8-7457-4209-A0FD-D8D5060DEEC8@mi crosoft.com...

In Visual Studio 2005, I have my DataSet filled with several static
tables
(they might get changed weekly, but that's about it).
>
With one of these tables in the DataSet, is it possible to select
distinct
items?
>
Ex:
I want to fill a tree with a list of job titles, and the Employee table
isalready filled with all of our employee records. Is there a way I
could
do
something like this below?
>
DataRow[] drTitles = DataSet1.Tables["Employees"].Select("Distin ct
JobTitle");
>
(the line above will fail with "Syntax error: Missing operand after
JobTitle
operator.")
>
I'm double posting this question. I think it is more relivant in the
SQL
boards, but they do not see much activity; hence, the question is being
posted here too.

>>
>>
>>