none
How to filter rows in table? RRS feed

  • Question

  • This is probably a noob ??? and for that I apologize, however, I've also not been able to find a satisfactory answer.

     

    Here's the situation...

     

    We are populating (.Net, VS2005 Designer-generated, strongly-typed) tables from two DB's.

     

    What we want to do:

    Take information (tables) from one DB, call it DB_A

    Build Select clauses based on DB_A table content

    Get tables back from the other, call it DB_B

    Filter the rows in the DB_B tables so only those rows in the DB_B tables that match the DB_A-based select criteria remain

     

    We can't construct parameterized queries for the DB_B tables because apparently SQL doesn't allow WHERE clauses to be parameterized, and the strongly-typed datasets don't allow direct access to the underlying queries.


    To make matters worse, the underlying implementation does not allow one to do something like this (in C# pseudo-code):

    DataRow[] drs = SomeDS.SomeTableOfInterest.Select(myClause...)

    SomeDS.TableOfInterest.Clear() // <== Wrong! The rows in drs just went away!

    foreach (DataRow dr in drs) // See above...

       SomeDS.TableOfInterest.Rows.Add(dr) // See above...

    endforeach

    SomeDS.TableOfInterest.Rows.Count = ??? // It's zero, because we cleared the table...

     

    There has to be a way to do this, but we just can't seem to locate it.

     

    Any help?

     

    Wednesday, October 31, 2007 12:29 AM

Answers

  • I wasn't being clear, they are different DBs and may or may not be on the same server. We can't assume they are because customers can configure our product so that the DBs are not on the same server.

     

    The fundamental issue is that you can't change the underlying query and what Select() does is return an array of Rows without changing the underlying Table.

     

    We got around this by writing our own function that:

    1) Takes the WHERE clause intended for the .Select() method call

    2) Wraps it in a "NOT" construct: "NOT (" + sTheOriginalSelectClause + ")"

    3) Uses that as a Select() clause

    4) Captures the returned array of rows

    5) Removes the rows in the array (selected by the negation of the original clause) from the original Table

     

    Thursday, November 1, 2007 7:34 PM

All replies

  • In case it may help with suggesting solutions etc, the context is that we're developing reports for our app using the MS .Net ReportViewer control in LocalMode.

     

    The VS2005 Designers etc require .Net based, strongly-typed DS's for Designer support and proper codegen behind the scenes etc. So we are kind of roped into using the strong-typed DS's.

     

    Wednesday, October 31, 2007 12:40 AM
  • Are those two databases located on a same server? If yes, you could avoid roundtrips to the database server and join results from different databases in one SQL statement using fully qualified name for objects, like ServerName.OwnerName.TableName.

    In addition, I do not know what exactly you do, but you can pass parameters to WHERE conditions.

     

    Thursday, November 1, 2007 10:27 AM
    Moderator
  • I wasn't being clear, they are different DBs and may or may not be on the same server. We can't assume they are because customers can configure our product so that the DBs are not on the same server.

     

    The fundamental issue is that you can't change the underlying query and what Select() does is return an array of Rows without changing the underlying Table.

     

    We got around this by writing our own function that:

    1) Takes the WHERE clause intended for the .Select() method call

    2) Wraps it in a "NOT" construct: "NOT (" + sTheOriginalSelectClause + ")"

    3) Uses that as a Select() clause

    4) Captures the returned array of rows

    5) Removes the rows in the array (selected by the negation of the original clause) from the original Table

     

    Thursday, November 1, 2007 7:34 PM