How to use parameterized queries in SqlDataSource.SelectCommand in code behind?

Question

I have a code behind method that uses SqlDataSource object to retrieve data from database, then is used as a data source for my grid view to display the records. I need to conditionally add another condition to my WHERE clause of my SelectCommand query, and need to pass the data to the query through parameters. How do I do this?

SqlDataSource sds = new SqlDataSource();

sds.SelectCommand = "Select CustomerName, LoanNumber, LoanDate FROM Loan WHERE IsActive = 1 ";

if (filterRecord != "All") {   // DDL filter on page, all records by default
    sds.SelectCommand += "AND LoanType = ??";   //this is where I need to parameterize my query
}

I thought something like

if (filterRecord != "All") {  
    sds.SelectCommand += "AND LoanType = @LoanType";
    sds.SelectParameters.Add("@LoanType", "Mortgage");
}

But that does not appear to work.

Answer 1

I would advise switching to an SqlCommand object To define your query and reading the records with an SqlDataReader object. You are able to add parameters to your query using something similar to following:

SqlCommand cmd = new SqlCommand(queryString, connection);
cmd.Parameters.AddWithValue("@paramName", value);

Afterwards, you can bind the SqlDataReader to the grid view as you normally would, by declaring it as the data source.