CODEWITHSUNDEEP
c#parameterized-query
quirky purple
quirky purple

Reputation: 2219

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

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.

Upvotes: 1

Views: 1372

Answers (2)

user6291531
user6291531

Reputation: 26

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.

Upvotes: 1

Amir
Amir

Reputation: 2098

I did not considered that you are using the Datasource Object:

Add a ASP parameter to the SQL data source:

<asp:Parameter Name="LoanType" Type="String" DefaultValue="default" />

Change the select command to:

"Select CustomerName, LoanNumber, LoanDate FROM Loan WHERE IsActive = 1 AND (@LoanType="default" or LoanType =@LoanType)"

Then in your code behind do this:

if (filterRecord != "All") {  
    sds.SelectParameters("LoanType").DefaultValue="Mortgage"
}

Description: You need to define the sql parameter in the ASP webform before editing in the code behind. I defined a parameter with a dummy value "default". So if in the you do not change the parameter in code behind the parameter @LoanType is equal to "default" string and the @LoanType="default" will returns true for all rows and it will not consider the LoanType =@LoanType. However, If you change the parameter in code behind the @LoanType="default" returns false and it will check LoanType=@LoanType for each row which will result in what you want!

My OLD Answer: This is good when you do not use the sql data source and you are using your sql command 

if (filterRecord != "All") {  
    SqlParameter Parameter = new SqlParameter();
    Parameter.ParameterName = "@LoanType";
    Parameter.SourceColumn = "LoanType";
    Parameter.SqlDbType = SqlDbType.NVarChar;
    Parameter.Value = "Mortgage";

    sds.SelectCommand += "AND LoanType = @LoanType";
    sds.SelectParameters.Add(Parameter);
}

Upvotes: 0

Related Questions