writing dynamic sql query based on user input coditions in C#

Question

CREATE TABLE [DBO].[TBL_PROFILE] 
(
    PROFILE_ID BIGINT IDENTITY(1, 1) NOT NULL
    ,NAME NVARCHAR(200) NOT NULL
    ,GENDER TINYINT NOT NULL
    ,CASTE NVARCHAR(15)
    ,QUALIFICATION NVARCHAR(50)
    ,COMPLEXION NVARCHAR(50)
    ,FEEDING_HBTS INT 
    ,CONSTRAINT PK__PROFILE PRIMARY KEY (PROFILE_ID)
)

INSERT INTO [DBO].[TBL_PROFILE] 
VALUES ('AJAY', 1, 'BRAHMAN', 'MASTER', 'FAIR', 1),
       ('JIRAM', 1, 'CHETTRI', 'BACHELOR', 'BLACK', 1),
       ('SUMAN', 1, 'NEWAR', '+2', 'BLACK', 1),
       ('HIRA', 1, 'MAGAR', 'BACHELOR', 'FAIR', 1),
       ('JANNY', 1, 'MAGAR', 'BACHELOR', 'MEDIUM', 1),
       ('RANVI', 1, 'NEWAR', 'BACHELOR', 'BLACK', 1),
       ('SURAJ', 1, 'BRAHMAN', 'BACHELOR', 'FAIR', 1);

Above is the SQL Server table and some sample data for testing purpose.

In the front end, the user has the option to select the caste, qualification, complexion and based on these conditions I've to design the query. The user can select more than one value for any of these attributes. Now based on these user conditions, I've to design the sql query in C#.

Suppose, the user selects NEWAR, MAGAR as caste and others are NULL, then the query would be:

SELECT * 
FROM [DBO].[TBL_PROFILE] 
WHERE GENDER = 1
  AND CASTE IN ('NEWAR', 'MAGAR')

Suppose the user selects qualification as BACHELOR and COMPLEXION as FAIR, BLACK then the query would be:

SELECT * 
FROM [DBO].[TBL_PROFILE] 
WHERE GENDER = 1
  AND COMPLEXION IN ('FAIR', 'BLACK') 
  AND QUALIFICATION = 'BACHELOR';

So the query is dynamic, based on the three attributes. How can I write dynamic query in C# for this scenario?

halfer · Accepted Answer · 2020-04-09 16:10:29Z

4

There are literally 100s of ways to do this.

In the old days we would just use if statements and a StringBuilder. These days ORMs Like Entity Framework are commonly used, and a very successful at solving problems like this.

However, if you don't want to spin up your own query, or use an ORM like Entity Framework, I'll suggest a query builder. Once again these are dime-a-dozen, and there are hundreds of them. A quick web search found https://sqlkata.com/docs/

Install-Package SqlKata
Install-Package SqlKata.Execution

Code

// these would come from the ui
int? gender = 1;
var complexion = new[]
                     {
                        "FAIR",
                        "BLACK"
                     };
var qualification = "BACHELOR";

// setup
var connection = new SqlConnection("...");
var compiler = new SqlServerCompiler();
var db = new QueryFactory(connection, compiler);

//query
var query = db.Query("TBL_PROFILE");

if (gender != null)
   query.Where("GENDER", gender);

if (complexion != null)
   query.WhereIn("COMPLEXION ", complexion);

if (qualification != null)
   query.Where("QUALIFICATION ", qualification);

...

var results = query.Get();

foreach (var profile in results)
{
   Console.WriteLine($"{profile.Id}: {profile.Name}");
}

Disclaimer: I'm not endorsing this query builder and I have never used it. You need to do your own due diligence and use at your own risk.

Personally I'd recommend just jumping in the deep-end and use Entity Framework and be done with it. However, this example might get your pointed in the right direction.

edited Apr 9, 2020 at 16:10

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Sep 8, 2018 at 5:12

TheGeneral

82k9 gold badges110 silver badges151 bronze badges

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

2 Comments

clarkitect Over a year ago

Thank you for directing the OP towards a real ORM. Might want to add some of the reasons, too (indexes, parameterized queries)

amd Over a year ago

No need to reassign the query since it's a mutable object

Collectives™ on Stack Overflow

writing dynamic sql query based on user input coditions in C#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related