Hey All, 

I have built a Strongly Typed DAL for a system I am working on and I am trying to add filtered searching to it.

Example: 

 
Before I put the Strongly Typed dataset in place I had a class file that made all the calls to the database/stored procedures. To accomplish this I simply added to the SQL String:

WHERE " & FieldToSearch & " LIKE '%' + @SearchString + '%'"

 
This worked exactly how I needed it to. But now that I have a strongly typed datatable, I am finding the only way to do this with that table is by DataView.RowFiltering, which seems taxing on the database considering I have to pull back 10+ rows, populate a dataset and only present 3 or 4 to the user from that dataset.

 
Any ideas on how to best handle these kinds of searches?

Thanks,

-Matthew
 

If your database had 10,000,000 would you pull them all back and then filter it down to only 3?
No - of course not

You need to look at allowing your DAL to pull back only the needed data from the database.

Filtering a datatable can be appropriate in some situations but not in all.

My DAL uses dynamic/parameterized sql to make the calls to the database.  This provides me with unlimited filtering options when pulling data.

I don't really want to use Dynamic SQL for the fear of Injection attacks.

I only SHOW three rows, but I have to pull all rows back to be able to search them, otherwise the DataSet has null values in them. So say I wanted to search "MAC" well, since I don't display MAC I can't search for values that arn't there.

 I'm guessing there is no real way to get around this problem without the use of Dynamic SQL?

 

when you perform simple string concatenations to construct your sql statements, then you are open to injection attacks

    "select * from blah where something='" & var1 & "'"      <--  very bad indeed

To avoid injection attacks, use parameterized sql statements.

    "select * from blah where something=@param1"

So basically I will have to write a case for each field I'm going to want to search since I can't figure out a way to use a parameter like so: WHERE @Field LIKE @Value?

 
Maybe it won't be so bad writing those statements. Guess I should use a Stored Procedure then for ease of updating.