Managing queries with dynamic parameters in a MVC application

Question

I'm developing a web application in which users can save queries, conecction strings and the query parameters as a row of a table in the app database. Later, the application will offer a dropdown with all the queries saved in the database, and the user could select a desired query to run.

My issue is that I'll have the queries with the following format:

select * from table where field = {0} and field = {1}

I will have a column in the database with the exact amount and type of every parameter, that would be something like this: "D|N|S", indicating date, number or string, accordingly.

In the application, I currently have a controller action that retrieves the query in one string and the parameters in another one, so in the controller I know how many and which type of parameters the query has, but I can't come with a good solution of how to create html controls in the view for the user to enter values for such parameters. Since I don't know how many parameters the query has until the controller fetchs it from the database, I'm unsure how to proceed.

Thanks in advance.

Answer 1

I would have a class for the query and parameter, with the query having a collection of parameters.

public class SomeQueryClassName
{
    public int QueryId { get; set; }
    public string ConnectionString { get; set; }
    public string CommandText { get; set; }
    public List<SomeParameterClassName> Parameters { get; set; }

    public SomeQueryClassName()
    {
        ....
    }
}

public class SomeParameterClassName
{
    public string Name { get; set; }
    public string Description { get; set; }
    public string DataType { get; set; }
    public object Value { get; set; }

    public SomeParameterClassName()
    {
        ....
    }
}

Then in your controller get the query and return the list of parameters as your model.

public ActionResult GetQuery(int queryId)
{
    var query = 
    return View(SomeFunctionToGetTheQuery(queryId).Select(x => x.Parameters).ToList());
}

public ActionResult RunQuery(List<SomeParameterClassName> parameters)
{
    //some server side validation

    //GetQueryResults will add the parameters and execute the query
    return View(GetQueryResults(parameters);
}

And then in the view

using (Html.BeginForm())
{
    for (int i = 0; i < Model.Count; i++))
    {
        var parameter = Model   [i];     

        <div class="formGroup">
            <div class="labelDivClass">
                <label class="labelClass>@parameter.Description</label>
            </div>
            <div class="inputDivClass">
                <input type="text" name="[i].Value" class="someClassUsedForClientSideValidationBasedOnParameterDataType" />
                <input type="hidden" name="[i].Name" value="@parameter.Name"/>
                <input type="hidden" name="[I].DataType" value="@parameter.DataType"/>
            </div>

        </div>
    }
}

Make sure when you're saving/executing your queries that you're using parameterized queries and adding parameters rather than concatenating values into the query.

using (var connection = new SqlConnection("some connection string"))
{
    connection.Open();

    string cmd = "select * from testable where testcolumn = @testvalue";

    using (var command = new SqlCommand(cmd,connection))
    {
        command.Parameters.AddWithValue("testvalue",somevalue);

        //execute query and return data in a datatable or yield return objects
    }

    connection.Close();
}

Answer 2

This will help you get started:

It will add dynamic input elements inside a form. The form directs to a controller post action which will receive list of SqlParams , there you can do whatever you want.

Of course you must add your validations, and adjust it to your needs, it looks something like this:

Server:

public class HomeController : Controller
    {

        public class SqlParams 
        {
            public string Name { get; set; }
            public string Value { get; set; }
            public string ParamType { get; set; }

        }

        public ActionResult SQL() 
        {
            return View();
        }

        [HttpPost]
        public ActionResult SQL(List<SqlParams> sqlParams)
        {

            foreach (var item in sqlParams)
            {
                //Do whatever 
                string query = string.Format("{0} = {1}, {2}",item.Name,item.Value, item.ParamType);                
            }

            return View();
        }
}

The view:

<button type="button" onclick="Add();">Click to add param</button>

<form action="@Url.Action("SQL")" method="post">


    <div id="controls">

    </div>

    <button type="submit">Save query</button>
</form>

@section scripts {

    <script>
        var counter = 0;

        function Add() {
            var html = "<label>Parameter Name: </label> <input name='sqlParams[" + counter + "].Name'/> <br/>" +
                       "<label>Parameter Value: </label> <input name='sqlParams[" + counter + "].Value'/> <br/>" +
                       "<label>Parameter Type: </label> <input name='sqlParams[" + counter + "].Type'/> <br/>";
            counter++;

            $("#controls").append(html);
        }

    </script>
    }