Building a flexible parameterized adhoc query with C#, SQL

I'm in the process of building the capability for a user to perform ad-hoc queries on a SQL Server database. The resulting query will take on the following basic form:

SELECT <ONE TO MANY USER SELECTED FIELDS>
FROM <ONE TO MANY TABLES DETERMINED BY FIELDS SELECTED BY USER>
WHERE <ZERO TO MANY CRITERIA FOR THE SELECTED FIELDS>

It's a guarantee that the selection will most likely span more than one table. Some (not all) of the fields may have 0 or more filter criteria for a particular field.

My application is using the default EF4 classes within ASP.NET MVC 2 using C#. I am currently passing in an object called QueryItem that contains all the information for a particular criteria.

My question(s) are:

1. What is the best approach for coding this? (Code samples of what I have to date below).
2. Can this be done with Linq2SQL or should I use ADO.NET(My current approach)
3. If ADO.NET is the best way, how do you access the DBConnection within EF4?

Note: I intend to refactor this into SQLParameter objects, to protect against SQL injection. My goal right now is best practice in developing the query first.

QueryItem class:

public class QueryItem
{
    public bool IsIncluded { get; set; }
    public bool IsRequired { get; set; }

    public string LabelText { get; set; }
    开发者_如何学Pythonpublic string DatabaseLoc { get; set; }
    public List<string> SelectedValue { get; set; }

    public List<SelectListItem> SelectList { get; set; }
}

Query Parsing Code

    foreach(QueryItem qi in viewModel.StandardQueryItems)
    {
        string[] dLoc = qi.DatabaseLoc.Split(new Char[] { '.' }); //Split the table.fieldname value into a string array

        if(qi.IsIncluded == true) //Check if the field is marked for inclusion in the final query
        {
            fields.Append(qi.DatabaseLoc + ","); //Append table.fieldname to SELECT statement

            if(!tables.ToString().Contains(dLoc[0])) // Confirm that the table name has not already been added to the FROM statement
            {
                tables.Append(dLoc[0] + ","); //Append the table value to the FROM statement
            }
        }

        if(qi.SelectedValue != null)
        {
            if(qi.SelectedValue.Count == 1)
            {
                query.Append(qi.DatabaseLoc + " = '" + qi.SelectedValue[0].ToString() + "'");
            }
            else 
            {
                foreach(string s in qi.SelectedValue)
                {
                    //Needs to handle "IN" case properly
                    query.Append(qi.DatabaseLoc + " IN " + qi.SelectedValue.ToString());
                }
            }
        }
    }

I have built a similar system to what you are describing in the past by passing in a single parameter to a stored procedure of type xml. By doing so, you can actually specify(in xml), what all you would like to report off of and build the SQL necessary to return the results you want.

This also makes your C# code easier, as all you have to do is generate some xml that your procedure will read. Generating Dynamic SQL is definitely not something you should use unless you have to, but when you want to allow users to dynamically select what they want to report off of, it's pretty much the only way to go about doing it.

Another option for you might be to look into Reporting Services - that will allow the user to pick what fields they want to view and save that particular 'report' in their own section where they can then go back and run it again at any time.. You could also create the reports for them if they aren't computer savvy(which is a lot easier to do with report builder, provided that all they need is data and no special features).

Either way you go about it, their are pros and cons to both solutions.. You'll have to determine which option is best for you.

xml/dynamic sql: Hard to maintain/make changes to.(I feel sorry for anyone who has to come behind someone who is generating dynamic sql and try to understand the logic behind the mess).

reporting services: very easy to spit out reports that look good, but it's a little less flexible and it's not free.