question

b.korporaal avatar image
b.korporaal asked

What is the most optimal use of the IN-clause from C# code

Our application uses SQL server 2016 and our Data Access layer is based on C#, .NET 4.7.2 framework.

There are some options to construct a SQL query with a IN-clause from code.

One is simple string replacement, which is not secure because of possible threats of SQL-Injection.

Second option is to use a SQLParameter of type string which contains comma separated values which are converted to a table on the DB server. But this cost expensive CPU usages on the DB server.
We use in some cases the custom CsvToInt Function.

The third option is to include in where clause OR statements instead of the IN clause. We will loop through list and create dynamically SQL parameters in the SQL statement.
But we think there will be multiple Query Execution plans created by the DB server. This because the amount of OR clauses/SQL parameters will vary per call.

Are there other options or data types which are available? Which efficient for code and more important efficient to the DB server.

Thank you in advance.

parametersc#sql server 2016
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

Why not use a table-valued parameter to a stored procedure?

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you for introducing me a extra option. I will investigate if it fits in our application.
What do I have in mind when I use this kind of parameter on the SQL server side? Is there something that I have to optimize?

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.