开发者

How do you go about writing "dynamic sql" filters using querystring values in ASP.NET MVC?

I just can't seem to wrap my mind around this concept... I want to allow users to apply a number of "filters" to a dataset (preferably in the querystring to allow bookmarking the filtered results), retrieved using Rob Conery's Massive dynamic data access "tool".

I could simply write a whole bunch of if's, then write a dir开发者_运维百科ect query by each "rule" I catch... but that seems to defeat the purpose of "dynamic sql".

Is there a general pattern/best practice for this in C#/ASP.NET MVC?


I think the concept is a bit broad to say there's a pattern/best practice for doing something like this. That said, I think using something like LINQ to SQL or Entity Framework would make a good dynamic query engine because you can do stuff like this:

var query = DBContext.Items.Select(x => x.Name);

switch(QueryString["Type"])
{
    case "Dog":
        query = query.Where(x => x.Type == "Dog";
        break;
    case "Cat":
        query = query.Where(x => x.Type == "Cat";
    etc....
}

query = query.Where(x => x.Owner.Name == QueryString["Owner"]);

ResultsDataGrid.Datasource = query;

Obviously some psuedo-code there, but the good thing about LINQ to SQL in this case is the deferred execution. query doesn't get run until the databinding, and includes whatever filters you add dynamically. It also gives you type-safety and will prevent SQL Injection. You can do something similar by actually building up the SQL query that will be run at the end, but it's going to be error-prone and require a lot of checks and balances.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜