Selecting from DataSet (C#) - CSharp

This is a discussion on Selecting from DataSet (C#) - CSharp ; 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 ...

+ Reply to Thread
Results 1 to 4 of 4

Selecting from DataSet (C#)

  1. Default 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.

  2. Default Re: Selecting from DataSet (C#)

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

    public IEnumerable<string> GetDistinctJobTitles(DataTable table)
    {
    // The dictionary that indicates if you got the distinct job title or
    not.
    Dictionary<string, bool> returnedJobTitles = 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]
    - mvp@spam.guard.caspershouse.com



    "jp2msft" <jp2msft@discussions.microsoft.com> wrote in message
    news:01EF7488-7457-4209-A0FD-D8D5060DEEC8@microsoft.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.




  3. Default 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<string> GetDistinctJobTitles(DataTable table)
    > {
    > // The dictionary that indicates if you got the distinct job title or
    > not.
    > Dictionary<string, bool> returnedJobTitles = 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]
    > - mvp@spam.guard.caspershouse.com
    >
    >
    >
    > "jp2msft" <jp2msft@discussions.microsoft.com> wrote in message
    > news:01EF7488-7457-4209-A0FD-D8D5060DEEC8@microsoft.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.

    >
    >
    >


  4. Default 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<string> which
    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<string> that 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<string> returned, then that new job title
    will be returned in the result set when you iterate over the
    IEnumerable<string>:

    // Get the IEnumerable<string>
    IEnumerable<string> jobTitles = 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<string> that 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<string> class and pass that around.

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

    "jp2msft" <jp2msft@discussions.microsoft.com> wrote in message
    news:37CAA257-C6B2-42ED-A18D-394042D70B41@microsoft.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<string> GetDistinctJobTitles(DataTable table)
    >> {
    >> // The dictionary that indicates if you got the distinct job title or
    >> not.
    >> Dictionary<string, bool> returnedJobTitles = 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]
    >> - mvp@spam.guard.caspershouse.com
    >>
    >>
    >>
    >> "jp2msft" <jp2msft@discussions.microsoft.com> wrote in message
    >> news:01EF7488-7457-4209-A0FD-D8D5060DEEC8@microsoft.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.

    >>
    >>
    >>




+ Reply to Thread