469,890 Members | 1,543 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

Selecting from DataSet (C#)

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("Distinct 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.
Nov 11 '08 #1
3 6643
You would have to do it yourself. I would do something like this:

public IEnumerable<stringGetDistinctJobTitles(DataTable table)
{
// The dictionary that indicates if you got the distinct job title or
not.
Dictionary<string, boolreturnedJobTitles = new
Dictionary(table.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 (!returnedJobTitles.ContainsKey(jobTitle))
{
// Add to the dictionary.
returnedJobTitles.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]
- mv*@spam.guard.caspershouse.com

"jp2msft" <jp*****@discussions.microsoft.comwrote in message
news:01**********************************@microsof t.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("Distinct
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.

Nov 11 '08 #2
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<stringGetDistinctJobTitles(DataTable table)
{
// The dictionary that indicates if you got the distinct job title or
not.
Dictionary<string, boolreturnedJobTitles = new
Dictionary(table.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 (!returnedJobTitles.ContainsKey(jobTitle))
{
// Add to the dictionary.
returnedJobTitles.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]
- mv*@spam.guard.caspershouse.com

"jp2msft" <jp*****@discussions.microsoft.comwrote in message
news:01**********************************@microsof t.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("Distinct
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.


Nov 11 '08 #3
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<stringwhich
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<stringthat 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<stringreturned, then that new job title
will be returned in the result set when you iterate over the
IEnumerable<string>:

// Get the IEnumerable<string>
IEnumerable<stringjobTitles = GetDistinctJobTitles(...);

// 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 GetDistinctJobTitles is used by the compiler to create the
implementation of IEnumerable<stringthat 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<string>, 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 GetDistinctJobTitles to the constructor of a
List<stringclass and pass that around.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"jp2msft" <jp*****@discussions.microsoft.comwrote in message
news:37**********************************@microsof t.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<stringGetDistinctJobTitles(DataTable table)
{
// The dictionary that indicates if you got the distinct job title or
not.
Dictionary<string, boolreturnedJobTitles = new
Dictionary(table.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 (!returnedJobTitles.ContainsKey(jobTitle))
{
// Add to the dictionary.
returnedJobTitles.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]
- mv*@spam.guard.caspershouse.com

"jp2msft" <jp*****@discussions.microsoft.comwrote in message
news:01**********************************@microso ft.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("Distinct
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.



Nov 11 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Tavish Muldoon | last post: by
6 posts views Thread by aaj | last post: by
4 posts views Thread by rodchar | last post: by
14 posts views Thread by Sueffel | last post: by
3 posts views Thread by mark.norgate | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.