Dynamic parameters for where clause in a typed dataset possible?

views:

224

answers:

Dynamic parameters for where clause in a typed dataset possible?

The normal ASP.NET TableAdapters are good for simple where clauses, for example - "where city = @city and state = @state and zip = @zip"

But how do I design a DAL that allows me to use any combination of the parameters - "search only by city" or "search by zip and state" or "search by city and state".....or even more complex "search by zip but if @zip is null then search by city"....

How can I design a DAL that supports such dynamic where conditions? Are typed datasets with table adapters the best approach? Currently, I'm just using dynamic SQL queries with normal datasets and I'm trying to convert it into some sort of strongly typed DAL.

It sounds to me like you might want to review Dynamic LINQ. It can be helpful when such dynamic conditions exist. I have found the Code Samples here to be very helpful.

HTH, Sid

SidC 2010-02-16 17:16:48

Thanks Sid, I'm somewhat apprehensive about doing all the DAL stuff with LINQ in case it turns out MS decides to kill (fully/partially) LINQ later on. But whether LINQ stays or goes, is there some other approach to designing DAL that can solve this "dynamic where clause" problem?

achilles19282 2010-02-16 19:46:49

Achilles, two possible ideas come to mind. First, and not so clean, is to create sprocs to select data from your table using zip, city or state, or multiple parms. Then, in your presentation layer, create a control that uses a dropdownlist and a textbox where the dropdownlist allows the user to select which field is searched - city, state or zip.Editorial starts here: I don't feel that LINQ to ADO.NET entities is going anywhere anytime soon. I use L2E in current projects, but am steering clear of using LINQ to SQL as I do feel that it will be phased out.

SidC 2010-02-16 22:02:59

ansaurus

tags:

views:

answers:

Dynamic parameters for where clause in a typed dataset possible?

related questions