Come passare sqlparameter a IN ()?

Per qualche motivo, lo Sqlparameter per la mia clausola IN () non funziona. Il codice viene compilato correttamente e la query funziona se si sostituisce il parametro con i valori effettivi

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) 

Devi creare un parametro per ogni valore che desideri nella clausola IN .

L’SQL deve apparire così:

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

Quindi è necessario creare i parametri e la clausola IN nel ciclo foreach .
Qualcosa di simile (fuori dalla mia testa, non testata):

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

Se si utilizza SQL 2008, è ansible creare una stored procedure che accetti un parametro con valori di tabella (TVP) e utilizzare ADO.net per eseguire la stored procedure e passare ad esso un datatable:

Innanzitutto, è necessario creare il tipo nel server SQL:

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

Quindi, è necessario scrivere una stored procedure che accetta questo tipo come parametro:

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

Ora da .net, non è ansible utilizzare LINQ poiché non supporta ancora i parametri con valori di tabella; quindi devi scrivere una funzione che fa semplicemente il vecchio ADO.net, prende un DataTable e lo passa alla stored procedure: ho scritto una funzione generica che uso, che può farlo per qualsiasi procedura memorizzata, purché ci voglia solo l’unico parametro tipizzato da tabella, indipendentemente da cosa sia;

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

Quindi è ansible scrivere le funzioni DAL che utilizzano questa funzione di utilità con i nomi effettivi delle stored procedure; per build sull’esempio nella tua domanda, ecco come dovrebbe apparire il codice:

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

Nota il parametro “connessione” sopra: in realtà utilizzo questo tipo di funzione in una class DataContext parziale per estendere LINQ DataContext con la mia funzionalità TVP, e comunque utilizzo la syntax (usando il contesto var = new MyDataContext ()) con questi metodi.

Questo funzionerà solo se stai usando SQL Server 2008 – si spera che tu sia e se no, questo potrebbe essere un ottimo motivo per aggiornare! Naturalmente nella maggior parte dei casi e in ambienti di produzione di grandi dimensioni non è così facile, ma FWIW penso che questo sia il modo migliore per farlo se hai la tecnologia disponibile.

Possibile versione “pulita”:

 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 vede la tua clausola IN come:

 IN ('a,b,c') 

Quello che deve sembrare è:

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

C’è un modo migliore per fare ciò che stai cercando di fare.

  • Se gli ID utente sono nel DB, la clausola IN deve essere modificata in una sottoquery, ad esempio:

    IN (SELECT UserID FROM someTable WHERE someConditions)

  • Questo è un hack: non funziona bene con gli indici, e devi stare attento che funzioni correttamente con i tuoi dati, ma l’ho usato con successo in passato:

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