Cómo pasar sqlparameter a IN ()?

Por algún motivo, el parámetro Sql para mi cláusula IN () no funciona. El código comstack bien y la consulta funciona si sustituyo el parámetro con los valores reales

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) 

Debe crear un parámetro para cada valor que desee en la cláusula IN .

El SQL debe verse así:

 SELECT userId, username FROM Users WHERE userId IN (@UserId1, @UserId2, @UserId3, ...) 

Por lo tanto, debe crear los parámetros y la cláusula IN en el ciclo foreach .
Algo así (fuera de mi cabeza, no probado):

 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++; } 

Si está utilizando SQL 2008, puede crear un procedimiento almacenado que acepte un parámetro de valor de tabla (TVP) y use ADO.net para ejecutar el procedimiento almacenado y pasarle una tabla de datos:

En primer lugar, debe crear el tipo de servidor SQL:

 CREATE TYPE [dbo].[udt_UserId] AS TABLE( [UserId] [int] NULL ) 

Luego, debe escribir un procedimiento almacenado que acepte este tipo como un parámetro:

 CREATE PROCEDURE [dbo].[usp_DoSomethingWithTableTypedParameter] ( @UserIdList udt_UserId READONLY ) AS BEGIN SELECT userId, username FROM Users WHERE userId IN (SELECT UserId FROM @UserIDList) END 

Ahora desde .net, no puede usar LINQ porque aún no es compatible con los Parámetros de valores de tabla; entonces tienes que escribir una función que hace simple ADO.net, toma una DataTable y la pasa al procedimiento almacenado: he escrito una función genérica que uso que puede hacer esto para cualquier procedimiento almacenado, siempre y cuando solo tome el parámetro de una tabla, independientemente de lo que sea;

  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 to perform action on the datareader; conn.Close(); return rowsAffected; } } 

Luego puede escribir funciones DAL que usan esta función de utilidad con nombres reales de procedimientos almacenados; Para construir sobre el ejemplo en su pregunta, aquí es cómo se vería el código:

  public int usp_DoSomethingWithTableTypedParameter(List 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; } 

Observe el parámetro “conexión” anterior: de hecho, uso este tipo de función en una clase DataContext parcial para extender el LINQ DataContext con mi funcionalidad TVP, y todavía uso la syntax (usando var context = new MyDataContext ()) con estos métodos.

Esto solo funcionará si está usando SQL Server 2008, con suerte lo es y si no, ¡esta podría ser una buena razón para actualizar! Por supuesto, en la mayoría de los casos y en entornos de producción grandes esto no es tan fácil, pero FWIW creo que esta es la mejor manera de hacerlo si tiene la tecnología disponible.

Posible versión “limpia”:

 StringBuilder B = new StringBuilder(); for (int i = 0; i < UserList.Count; i++) YourCommand.Parameters.AddWithValue("@UserId" + i.ToString(), UserList[i].UserId); B.Append(String.Join(",", YourCommand.Parameters.Select(x => x.Name))); 

SQL Server ve su cláusula IN como:

 IN ('a,b,c') 

Lo que necesita ser es:

 IN ('a','b','c') 

Hay una mejor manera de hacer lo que intentas hacer.

  • Si los identificadores de usuario están en el DB, entonces la cláusula IN debe cambiarse a una subconsulta, así:

    IN (SELECT UserID FROM someTable WHERE someConditions)

  • Esto es un truco: no funciona bien con los índices, y debe tener cuidado de que funcione correctamente con sus datos, pero lo he utilizado con éxito en el pasado:

    @UserIDs LIKE '%,' + UserID + ',%' -- also requires @UserID to begin and end with a comma