none
multiple selection criteria (where clause) in a single dataset RRS feed

  • Question

  • In my Windows Forms application, there is a strongly typed dataset which consists of a datatable with 15 columns. I would like to give user the freedom of specifying selection criteria for any columns. But there is no way to specify different WHERE clause for the specified SELECT command.

    The criteria should be applied to SELECT statement, to save network resources, not by filtering on the client side.

    A clumsy solution is to specify parameters for all columns in the dataset designer, and then in the application fill those parameters with max/min values for columns that do not participate in selection criteria.

    Another solution is to just manually fill the dataset with a SELECT statementcontaining all necessary selection criteria, different than the one that is specified in the dataset definition.

    I just would like to know what there is "The Right" recommended way to do it ??
    Monday, January 21, 2008 7:35 AM

All replies

  • You should probably use a parameterized query with a WHERE clause like this:

     

    (@Col1MinValue IS NULL OR Col1 >= @Col1MinValue) AND

    (@Col1MaxValue IS NULL OR Col1 <= @Col1MaxValue) AND

    (@Col2MinValue IS NULL OR Col2 >= @Col2MinValue) AND

    (@Col2MaxValue IS NULL OR Col2 <= @Col2MaxValue) AND...

     

    This is less clumsy than the approach you suggested, and works regardless of how few or how many values the user provides.

    Friday, January 25, 2008 12:38 AM