Combinations of Where Criteria - Still parameterized query - Dapper

c#-4.0 dapper tsql

Question

I have a Dapper query as follows

Public void GetAllCusomers(string CustmoerId, StringFirstName, String LastName, String Gender)
    {
        TblCustomer tblCustomer  = new TblCustomer();

        using (var sqlConnection = new SqlConnection(“DatabaseConncetionString"))
        {
            sqlConnection.Open();
            //tblCustomer = sqlConnection.Query<TblCustomer >("SELECT *  FROM tblCustomer WHERE CustomerId = @CustomerID" AND FirstName = @FirstName……………, new { CustomerID = CustomerId,……………. }).ToList(); 
            tblCustomer = sqlConnection.Query<TblCustomer >("SELECT *  FROM tblCustomer WHERE CustomerId = @CustomerID", new { CustomerID = CustomerId }).ToList();
            sqlConnection.Close();
        }
}

The question is how to build the query? In the above method user can provide value to any parameters that he wishes to query. If the parameter value is blank that will not be used in the WHERE criteria. I will be using all the supplied parameters in the where criteria with AND operations.

Without Dapper it is easy to build the dynamic query by concatenating the SQL statement depending upon the supplied parameters. How to build these queries in Dapper without compromising the parameterized feature.

Thank you, Ganesh

Popular Answer

You would do it similar to how you build a dynamic query. Build your string dynamically (based on user input), only including filters in the Where clause as needed. Exmpale:

var query = new StringBuilder("select * from users where ");

if(!string.IsNullOrEmpty(firstname)) query.Append("FirstName = @FirstName ");

As far as passing in the parameters, you can either construct an object that includes all of your possible parameters with values to pass in:

new {FirstName = "John", LastName = "Doe"}

or, if you only want to pass in parameters that will actually be used, you can build a Dictionary<string,object> that contains only those parameters you need to pass in:

new Dictionary<string,object> { {"FirstName", "John" } }



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why