Solved

How can I compare columns in a dataset for equal and not equal values?

Posted on 2007-11-16
4
827 Views
Last Modified: 2012-05-05
hi all, i have a dataset that contains 4 columns, employee key, employee name, manager key, manager name.  i need to be able to list a set of rows of discrepancies where emp key = mgr key and names are different, or if emp name = mgr name, list the keys that are different.

so if employee key = 123 and employee name for key 123 is kelly, and manager key = 123 and manager name for mgr key 123 is matt, then i need to list that as a row, since the names don't match the equal keys.

i have a loop (below) going through the dataset looking for the discrepancies, but it's proving too slow on larger datasets where there is 50,000 + rows.  What I need is help with possibly setting up a relationship or filtered view to the dataset to accomplish the same thing.  Performance is really what i'm looking for here.

Thanks in advanced!

DataTable _dt = _ds.Tables[0];

for (int i = 0; i < _dt.Rows.Count; i++)
{
    for (int j = 0; j < _dt.Rows.Count; j++)
    {
       if ((_dt.Rows[i][_employeeKey].ToString() == _dt.Rows[j][_managerKey].ToString() &&
         _dt.Rows[i][_employeeName].ToString() != _dt.Rows[j][_managerName].ToString()) ||

         _dt.Rows[i][_employeeName].ToString() == _dt.Rows[j][_managerName].ToString() &&
         _dt.Rows[i][_employeeKey].ToString() != _dt.Rows[j][_managerKey].ToString() )
        {
        //add row
        }

0
Comment
Question by:kellyclu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
anoyes earned 500 total points
ID: 20302405
You might have better luck offloading that to your DB server.  MS SQL?  You could create a view or stored proc that would take care of finding your discrepancies, and then the ASP only has to list what comes out of that.  Is this a viable option?  If you can give a little info on how your DB is set up I can give you the SQL to do it.
0
 

Author Comment

by:kellyclu
ID: 20302485
not an option, the dataset is generated from the user choosing a file with several columns in it.  they then select which columns they want to be the emp key/name, and mgr key/name.  so the columns are coming from 4 drop downs on a web page listing all the available columns in the file.

so the user is looking for file discrepancies between values they choose, i was trying to make the question easier by not going there.

tried:
string _filter = _employeeKey + " = " + _managerKey + " AND " + _employeeName + " <> " + _managerName;
DataRow[] _rows = _dt.Select(_filter);

no rows are returned because it's all in one table. i've been thinking about creating a relation between to dataviews, but haven't been able to make it work.
0
 
LVL 16

Expert Comment

by:anoyes
ID: 20308719
Here's a tutorial about DataViews - don't know if it'll help any: http://aspnet.4guysfromrolla.com/articles/040203-1.2.aspx

But what about trying:
Dim dv As DataView = _ds.Tables(0).DefaultView
dv.RowFilter = "_employeeKey = _managerKey AND _employeeName <> _managerName"

This would only give you one set of discrepancies, but if this works you could easily do another one for the other set.
0
 

Author Comment

by:kellyclu
ID: 20330517
the dataview provided has the same problem as my filter string posted right before your's.

the rows in the dataset are like this:

empkey  empname  mgrkey  mgrname

so what we're both doing is going through the dataset one row at a time looking for equal keys, different names.  

if data was
123 matt 234 kelly
234 linda 123 matt

you won't find any discrepancies because 123 is not = to 234, and 234 is not = 123, the check is going through the dataset one row at a time.

i need to go through the data set so the 123 for the emp key is compared to the 123 of the mgr key.  so instead of comparing each individual row, i need to take the emp key 123 for matt, and look for the 123 mgr key (next line), then mark THAT as a discrepancy because the 234 name (linda) is not the same as the previous 234 (kelly).

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question