Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
For some reason the Sqlparameter for my IN() clause is not working. The code compiles fine, and the query works if I substitute the parameter with the actual values
StringBuilder sb = new StringBuilder();
foreach (User user in UserList)
sb.Append(user.UserId + ",");
string userIds = sb.ToString();
userIds = userIds.TrimEnd(new char[] { ',' });
SELECT userId, username
FROM Users
WHERE userId IN (@UserIds)
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);
–
–
–
If you are using SQL 2008, you can create a stored procedure which accepts a Table Valued Parameter (TVP) and use ADO.net to execute the stored procedure and pass a datatable to it:
First, you need to create the Type in SQL server:
CREATE TYPE [dbo].[udt_UserId] AS TABLE(
[UserId] [int] NULL
Then, you need to write a stored procedure which accepts this type as a parameter:
CREATE PROCEDURE [dbo].[usp_DoSomethingWithTableTypedParameter]
@UserIdList udt_UserId READONLY
BEGIN
SELECT userId, username
FROM Users
WHERE userId IN (SELECT UserId FROM @UserIDList)
Now from .net, you cannot use LINQ since it does not support Table Valued Parameters yet; so you have to write a function which does plain old ADO.net, takes a DataTable, and passes it to the stored procedure: I've written a generic function I use which can do this for any stored procedure as long as it takes just the one table-typed parameter, regardless of what it is;
public static int ExecStoredProcWithTVP(DbConnection connection, string storedProcedureName, string tableName, string tableTypeName, DataTable dt)
using (SqlConnection conn = new SqlConnection(connection.ConnectionString))
SqlCommand cmd = new SqlCommand(storedProcedureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter p = cmd.Parameters.AddWithValue(tableName, dt);
p.SqlDbType = SqlDbType.Structured;
p.TypeName = tableTypeName;
conn.Open();
int rowsAffected = cmd.ExecuteNonQuery(); // or could execute reader and pass a Func<T> to perform action on the datareader;
conn.Close();
return rowsAffected;
Then you can write DAL functions which use this utility function with actual names of stored procedures; to build on the example in your question, here is what the code would look like:
public int usp_DoSomethingWithTableTypedParameter(List<UserID> userIdList)
DataTable dt = new DataTable();
dt.Columns.Add("UserId", typeof(int));
foreach (var userId in updateList)
dt.Rows.Add(new object[] { userId });
int rowsAffected = ExecStoredProcWithTVP(Connection, "usp_DoSomethingWithTableTypedParameter", "@UserIdList", "udt_UserId", dt);
return rowsAffected;
Note the "connection" parameter above - I actually use this type of function in a partial DataContext class to extend LINQ DataContext with my TVP functionality, and still use the (using var context = new MyDataContext()) syntax with these methods.
This will only work if you are using SQL Server 2008 - hopefully you are and if not, this could be a great reason to upgrade! Of course in most cases and large production environments this is not that easy, but FWIW I think this is the best way of doing this if you have the technology available.
–
Possible "cleaner" version:
StringBuilder B = new StringBuilder();
for (int i = 0; i < UserList.Count; i++)
YourCommand.Parameters.AddWithValue($"@UserId{i}", UserList[i].UserId);
B.Append(String.Join(",", YourCommand.Parameters.Select(x => x.Name)));
–
–
If the user id's are in the DB, then the IN clause should be changed to a subquery, like so:
IN (SELECT UserID FROM someTable WHERE someConditions)
This is a hack -- it doesn't work well with indexes, and you have to be careful it works right with your data, but I've used it successfully in the past:
@UserIDs LIKE '%,' + UserID + ',%' -- also requires @UserID to begin and end with a comma
–
–
–
–
–
