Unable to pass 2 parameter in ASP.NET Razor - WebMatrix Database Query() method

Question

I am unable to use WebMatrix database method Query() with 2 interpolate string.

Actual Code: (Not working)

@{
    var db = Database.Open("WebPagesMovies") ;
    var selectCommand = "SELECT * FROM Movies";        
    var item = "";
    var search = "";

    if(!Request.QueryString["searchTitle"].IsEmpty() ) {
        selectCommand = "SELECT * FROM Movies WHERE @0 LIKE @1";
        item = Request.QueryString["item"];
        search = "%" + Request.QueryString["search"] + "%";
    }

    var selectedData = db.Query(selectCommand, item, search); // **NOT WORKING**
    var grid = new WebGrid(source: selectedData, defaultSort: "Genre", rowsPerPage:3);
}

Working

@{
    var db = Database.Open("WebPagesMovies") ;
    var selectCommand = "SELECT * FROM Movies";   
    var search = "";

    if(!Request.QueryString["searchTitle"].IsEmpty() ) {
        selectCommand = "SELECT * FROM Movies WHERE Title LIKE @0";
        item = Request.QueryString["item"];
        search = "%" + Request.QueryString["search"] + "%";
    }

    var selectedData = db.Query(selectCommand, item, search);
    var grid = new WebGrid(source: selectedData, defaultSort: "Genre", rowsPerPage:3);
}

Why am I unable to interpolate 2 string like item and search in Query() method? Does it not supported in ASP.NET?

On debugging I can see that values are assigned to item and search. But values are not passed to Query() method.

Official Doc:

Knox · Accepted Answer · 2016-12-14 01:47:15Z

1

That's a very interesting approach. What your code is doing is using parameterized SQL. The parameters are @0, @1 and so. The db.Query statement passes the parameters into SQL. I believe there is a significant restriction on where these parameters can be used. They can only be used where data can go, not where SQL commands or table names or fields can go. So because Title is a field name, it can't be parameterized.

So guessing that sometimes you want to search on the title, sometimes on the release date, and so on, you have several choices.

One is to examine the incoming item variable, maybe in a switch statement, and then have several sql statements each which handles just one item. So you code would look like this psuedo code:

switch (item)
{
   case 'title':
       selectCommand = "SELECT * FROM Movies WHERE Title LIKE @0";
       break;
   case 'releaseDate':
        selectCommand = "SELECT * FROM Movies WHERE ReleaseDate = @0";
        break;
    ...
  }

That leads to one of the reasons that a parameterized field name doesn't make sense. Because 'Title' is string, it can use the LIKE command, but a date couldn't.

edited Dec 14, 2016 at 1:47

answered Dec 10, 2016 at 17:35

Knox

2,92912 gold badges38 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aravin Over a year ago

your ideas & suggestion are perfect!! Is there any official statement that C# does not suuport parameter for SQL query?

Knox Over a year ago

I did some Googling before giving the answer and did not find a definite good source for exactly how to best use parameters. One of the ways to address the issue is to use dynamic SQL but that's more complicated than the approach i suggested. Please mark the question as solved. Good luck!

Collectives™ on Stack Overflow

Unable to pass 2 parameter in ASP.NET Razor - WebMatrix Database Query() method

Actual Code: (Not working)

Working

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Actual Code: (Not working)

Working

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related