VS 2015 DataTable filtering-VBForums
Results 1 to 11 of 11

Thread: DataTable filtering

  1. #1

    Thread Starter
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,666

    DataTable filtering

    Hi all, I got a rather general question about DataTables, just to see if I'm missing anything.

    I have two DataTables. The first is something like this:
    Table: Master_list_of_parts
    Columns:
    PART (string)
    REVISION (string)
    DATA1
    DATA2
    .
    .
    DATAETC

    The second:
    Table: Parts_completed
    Columns:
    PART (string)
    REVISION (string)

    In each table, the combination of "PART" and "REVISION" is a primary, unique key.

    Each DataTable comes from a completely different database (the first is from MSSQL, the second from MySQL) so I can't just do this with a line of SQL.
    What I'm trying to do is "DataTable1 - DataTable2 = DataTable3 (where PART = PART and REVISION = REVISION); i.e. filter out all the rows from the first data table that have a match in the second.

    I can do this with loops, and .Search() and other methods, but I'm curious what the most "elegant" way to do this is? (least code, fastest, least computations)

    Thanks in advance for any suggestions!
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    30,836

    Re: DataTable filtering

    I almost hate to suggest it, since I'm no big fan of the technology, but what you are talking about is a JOIN....and LINQ allows you to do joins between datatables, so it is likely that you could do this in one line with LINQ. The reason I don't much like LINQ is two things. The first is that, while it is compact, it isn't particularly efficient. I have yet to see some LINQ that performs better than writing a loop, so the whole reason for using it is that you can type less, and the difference in speed almost never matters. It's not a HUGE difference, after all. The other thing I don't really like about LINQ is that it can be harder to parse. You can write such convoluted stuff into a single line of LINQ that it's hard for a good coder to parse it fully, so it doesn't teach well.

    Having said that, this is a case where both of those objections are probably invalid. Writing a JOIN using loops would be sufficiently tedious that LINQ may perform better than the loops, or at least as well. Second, writing a join using loops is going to take enough code that it will be harder for somebody to understand at a glance than just writing what amounts to SQL.

    There are numerous examples, but this one looks pretty suited to your question (once you get past a page or two of setting up the problem):

    https://docs.microsoft.com/en-us/dot...by-using-joins
    My usual boring signature: Nothing

  3. #3

    Thread Starter
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,666

    Re: DataTable filtering

    Yea, I don't care for LINQ for the same reasons. That's one of the ways I set it up to do it.
    If it was as easy as SQL to a single database, I'd do something like SELECT * FROM Table1 WHERE Part + Rev NOT IN (SELECT Part + Rev FROM Table2)

    I'm messing around with .Select and DataSet relations now. There's no way I'm happy with doing this. I mostly asked the question to see if I'm missing some stupid, obvious answer and to ask other programmers "how would you do this"?
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  4. #4
    Junior Member
    Join Date
    May 2012
    Posts
    26

    Re: DataTable filtering

    Just because the data sources are from different databases doesn't mean you can't use SQL. Have you tried creating a linked server table from SQL Server to MySQL and then use a SELECT statement with OPENQUERY? The below links should help:

    http://www.c-sharpcorner.com/article...to-sql-server/
    https://stackoverflow.com/questions/...t-in-statement
    Paul ~~~~ Microsoft MVP (Visual Basic)

  5. #5
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    733

    Re: DataTable filtering

    Could you perhaps create a link from one db to the other so you can just do it in SQL? One such example

    Edit: I see pclement beat me to the punch.

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,111

    Re: DataTable filtering

    Quote Originally Posted by Shaggy Hiker View Post
    I almost hate to suggest it, since I'm no big fan of the technology, but what you are talking about is a JOIN....and LINQ allows you to do joins between datatables, so it is likely that you could do this in one line with LINQ. The reason I don't much like LINQ is two things. The first is that, while it is compact, it isn't particularly efficient. I have yet to see some LINQ that performs better than writing a loop, so the whole reason for using it is that you can type less, and the difference in speed almost never matters. It's not a HUGE difference, after all. The other thing I don't really like about LINQ is that it can be harder to parse. You can write such convoluted stuff into a single line of LINQ that it's hard for a good coder to parse it fully, so it doesn't teach well.

    Having said that, this is a case where both of those objections are probably invalid. Writing a JOIN using loops would be sufficiently tedious that LINQ may perform better than the loops, or at least as well. Second, writing a join using loops is going to take enough code that it will be harder for somebody to understand at a glance than just writing what amounts to SQL.

    There are numerous examples, but this one looks pretty suited to your question (once you get past a page or two of setting up the problem):

    https://docs.microsoft.com/en-us/dot...by-using-joins
    If I was still at my previous job, I could actually supply you with some code where LINQ/Lambda setup was faster than a filter/loop... by many orders... I couldn't explain it then, I still can't explain it now. But we had several bottlenecks that were performing pretty badly until they were replaced by a combination of LINQ & Lambdas, even though convetional wisdom would suggest the opposite should be true. Needless to say, it baffled a lot of us. But the evidence at the time was hard to ignore. That said, I don't remember much about the circumstances, and there may have been joins involved, and that's why there was an improvement.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,598

    Re: DataTable filtering

    I think that there's also some confusion about what LINQ actually is. For instance, you can't use Entity Framework without using LINQ. It's good to understand the difference between LINQ the technology and individual LINQ providers. When Shaggy says that he doesn't like LINQ, I think that he's really talking about LINQ to Objects, which is one specific provider for generic collections. LINQ to DataSet is another provider, as is LINQ to SQL and LINQ to Entities is the one for EF. There are various others from Microsoft, e.g. LINQ to XML, and third parties have provided their own implementations too.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  8. #8

    Thread Starter
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,666

    Re: DataTable filtering

    Can't do that. The environment has the MSSQL server isolated. I'm using essentially an edge server to send requests to it and it sends back DataTable objects.

    I need to read the MSSQL datatable into a set of data objects anyways. The solution I'm thinking I'm settling on is:

    Load the key data from the MySQL datatable into a HashSet(Of String)
    Enumerate the MSSQL datatable, check if it's key column information is contained in the HashSet and if it is, skip it.
    Otherwise, load the data Row into a new data object and append that object to a collection.

    I'm enumerating the table regardless, and the lookup to the HashSet is pretty minimal.
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  9. #9
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,111

    Re: DataTable filtering

    Quote Originally Posted by jmcilhinney View Post
    I think that there's also some confusion about what LINQ actually is. For instance, you can't use Entity Framework without using LINQ. It's good to understand the difference between LINQ the technology and individual LINQ providers. When Shaggy says that he doesn't like LINQ, I think that he's really talking about LINQ to Objects, which is one specific provider for generic collections. LINQ to DataSet is another provider, as is LINQ to SQL and LINQ to Entities is the one for EF. There are various others from Microsoft, e.g. LINQ to XML, and third parties have provided their own implementations too.
    True, in our case it would have been LINQ to (typed) DataSets that we were using. It probably also doesn't help that people also confuse the use of type extensions and Lambdas with LINQ... you can use extensions and Lambdas w/o LINQ but you can't really use LINQ w/o the extensions (in fact, if I remember right, they were added to support LINQ).

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,666

    Re: DataTable filtering

    My issue with LINQ is the readability. I've certainly used it, and in many cases, rather extensively to sort and filter things like data objects, but it seems when I go back years later and look at that code, while very tight and orderly, I always scratch my head and wonder exactly how many quad machiattos I had the morning I wrote it.
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  11. #11
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    30,836

    Re: DataTable filtering

    Quote Originally Posted by jmcilhinney View Post
    I think that there's also some confusion about what LINQ actually is. For instance, you can't use Entity Framework without using LINQ. It's good to understand the difference between LINQ the technology and individual LINQ providers. When Shaggy says that he doesn't like LINQ, I think that he's really talking about LINQ to Objects, which is one specific provider for generic collections. LINQ to DataSet is another provider, as is LINQ to SQL and LINQ to Entities is the one for EF. There are various others from Microsoft, e.g. LINQ to XML, and third parties have provided their own implementations too.
    That's true. My objections are both LINQ to Object and LINQ to Datasets. I haven't really used any other.
    My usual boring signature: Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.