How to pass sqlparameter to IN()?
You have to create one parameter for each value that you want in the IN
clause.
The SQL needs to look like this:
SELECT userId, username
FROM Users
WHERE userId IN (@UserId1, @UserId2, @UserId3, ...)
So you need to create the parameters and the IN
clause in the foreach
loop.
Something like this (out of my head, untested):
StringBuilder sb = new StringBuilder();
int i = 1;
foreach (User user in UserList)
{
// IN clause
sb.Append("@UserId" + i.ToString() + ",");
// parameter
YourCommand.Parameters.AddWithValue("@UserId" + i.ToString(), user.UserId);
i++;
}
SqlParameter and IN statement
I use a helper method like this
/// <summary>
/// Adds a sequence of parameters to an existing parameter collection
/// </summary>
/// <typeparam name="T">Type of parameter values</typeparam>
/// <param name="parameters">Existing parameter collection</param>
/// <param name="pattern">Name pattern of parameters. Must be a valid <see langword="int"/> format string</param>
/// <param name="parameterType">Database type of parameters</param>
/// <param name="length">Length of parameter. 0 for numeric parameters</param>
/// <param name="values">Sequence of values</param>
/// <returns>Comma separated string of parameter names</returns>
public static string AddParameters<T>(SqlParameterCollection parameters,
string pattern,
SqlDbType parameterType,
int length,
IEnumerable<T> values) {
if (parameters == null)
throw new ArgumentNullException("parameters");
if (pattern == null)
throw new ArgumentNullException("pattern");
if (values == null)
throw new ArgumentNullException("values");
if (!pattern.StartsWith("@", StringComparison.CurrentCultureIgnoreCase))
throw new ArgumentException("Pattern must start with '@'");
var parameterNames = new List<string>();
foreach (var item in values) {
var parameterName = parameterNames.Count.ToString(pattern, CultureInfo.InvariantCulture);
parameterNames.Add(parameterName);
parameters.Add(parameterName, parameterType, length).Value = item;
}
return string.Join(",", parameterNames.ToArray());
}
It is used like this
string sql = "SELECT col1 " +
"FROM Table " +
"WHERE col2 IN ({@Values}) ";
var paramNames = SqlHelper.AddParameters(command.Parameters,
"@Value0",
SqlDbType.Int,
0,
listOfInts);
command.CommandText = sql.Replace("{@Values}", paramNames);
...
(Result is a query like SELECT ... IN (@Value0, @Value1, @Value2)
)
Passing a dynamic array of SqlParameters into a SQL string IN clause
Let SQL Server do all dirty work. Something like this.
var SQL0 = "SELECT * FROM [database] WHERE material_id IN (select value from string_split('{0}',','))";
var idList = new List<int> { 11, 53, 125};
int[] idListArray = idList.ToArray();
sqlCommand.CommandText = String.Format(SQL0, string.Join(",", idListArray));
// now execute the command
EDIT
More secure and performat way.
var SQL0 = "SELECT * FROM [database] WHERE material_id IN (select value from string_split(@ids,','))";
var idList = new List<int> { 11, 53, 125};
int[] idListArray = idList.ToArray();
sqlCommand.CommandText = SQL0;
sqlCommand.Parameters.Add("@ids", SqlDbTypes.VarChar, -1).Value = string.Join(",", idListArray);
// now execute the command
Pass list of values in sqlparameter, structured perhaps
You need to add TypeName in sqlParameter , the same name with you created your table type in DB.
sqlParameters.Add(new SqlParameter()
{
ParameterName = $"@paramname",
SqlDbType = SqlDbType.Structured,
Value = table,
TypeName = "dbo.MyType";
});
If you do not have table type in database then first you need to create it in SQL
CREATE TYPE [dbo].[IntegerList] AS TABLE(
[Data] [int] NOT NULL,
)
GO
And then give that name in your code. That will work and you do need to create table in DB for that.
Pass (1,2,3,4) as a SqlParameter
You have to either generate many parameters:
string[] paramValues = parameterString.Split(',');
var paramNames = Enumerable
.Range(0, paramValues.Length)
.Select(index => $"@prm{index}")
.ToArray();
string query = $"SELECT * from test WHERE testId IN ({string.Join(",", paramNames)})";
...
for (int i = 0; i < paramNames.Length; ++i)
command.Parameters.Add(new SqlParameter(paramNames[i], paramValues[i]));
Or do not use parameters at all and add the string as is which easier to implement but prone to SQL injection and that's why can't be recommended:
string query = $"SELECT * from test WHERE testId IN ({ParameterString})";
how to write sql query text for IN clause in C#
While you can solve this with a string .join
or just iterating your loop to build the stuff between the parentheses in your IN
clause, it will leave you wide open for SQL injection.
To avoid that you MUST parameterize your inputs to the SQL string. We do this by using the sql command .Parameters
class.
It will look something like:
var query = "SELECT OM.ORDERCONFIRMATIONID AS OCN FROM [DGS].[DGSCRM].[LEAD] SESSIONID in ({0})";
var idParameterList = new List<string>();
var index = 0;
foreach (var sessionid in ListSessionId)
{
var paramName = "@idParam" + index;
sqlCommand.Parameters.AddWithValue(paramName, sessionid);
idParameterList.Add(paramName);
index++;
}
sqlCommand.CommandText = String.Format(query, string.Join(",", idParameterList));
Note that I'm totally guessing at your sqlCommand
variable name, but you can swap that out as needed.
With this we are splitting our SQL string/command up into two chunks
The sql itself. This ends up being submitted to the database looking like
SELECT OM.ORDERCONFIRMATIONID AS OCN FROM [DGS].[DGSCRM].[LEAD] SESSIONID in (@idParam0, @idParam1, @idParam3, ... @idParamN)
. The database gets this sql string and compiles it as if it had values in it. It determines the execution path and just sits on it waiting for the values to come through in the second partThe parameter's values come in. This is the list we are building in the loop. Each value in your
ListSessionId
gets paired up with a newsqlCommand.Parameters
which takes a name@idParam0
or@idParam1
, etc.
Because your SQL is compiled without any of the actual stuff in your ListSessionID
there is no way for bad SQL to show up and get executed in your database. It's compiled and ready to execute. So when the parameters hit, it runs the plan for the sql statement and barfs back results. Nice and clean.
Related Topics
What Does {0} Mean When Found in a String in C#
How to Determine a Mapped Drive's Actual Path
Web API Put Request Generates an Http 405 Method Not Allowed Error
Task Parallel Library Replacement for Backgroundworker
Escape Invalid Xml Characters in C#
Detect Webbrowser Complete Page Loading
Pass Connection String to Code-First Dbcontext
How to Detect a Usb Drive Has Been Plugged In
How to Get Intptr from Byte[] in C#
Editing Dictionary Values in a Foreach Loop
Foreach VS Somelist.Foreach(){}
What Is the Use of Enumerable.Zip Extension Method in Linq
How to Deploy Application with SQL Server Database on Clients
Display Image from Database in Asp MVC
How to Get an Oauth 2.0 Authentication Token in C#