DÓNDE EN (conjunto de ID)

Tengo un servicio web que se pasa una serie de entradas. Me gustaría hacer la statement de selección de la siguiente manera, pero sigo recibiendo errores. ¿Debo cambiar la matriz por una cadena?

[WebMethod] public MiniEvent[] getAdminEvents(int buildingID, DateTime startDate) { command.CommandText = @"SELECT id, startDateTime, endDateTime From tb_bookings WHERE buildingID IN (@buildingIDs) AND startDateTime <= @fromDate"; SqlParameter buildID = new SqlParameter("@buildingIDs", buildingIDs); } 

No puedes (desafortunadamente) hacer eso. Un parámetro Sql solo puede ser un valor único, por lo que tendrías que hacer:

 WHERE buildingID IN (@buildingID1, @buildingID2, @buildingID3...) 

Lo cual, por supuesto, requiere que sepa cuántos identificadores de construcción hay, o para construir dinámicamente la consulta.

Como una solución *, hice lo siguiente:

 WHERE buildingID IN (@buildingID) command.CommandText = command.CommandText.Replace( "@buildingID", string.Join(buildingIDs.Select(b => b.ToString()), ",") ); 

que reemplazará el texto de la statement con los números, terminando así:

 WHERE buildingID IN (1,2,3,4) 
  • Tenga en cuenta que esto se está acercando a una vulnerabilidad de inyección Sql, pero dado que es una matriz int es segura. Las cadenas arbitrarias no son seguras, pero no hay manera de incrustar sentencias Sql en un entero (o datetime, boolean, etc.).

Primero vas a necesitar una función y un sproc. La función dividirá sus datos y devolverá una tabla:

 CREATE function IntegerCommaSplit(@ListofIds nvarchar(1000)) returns @rtn table (IntegerValue int) AS begin While (Charindex(',',@ListofIds)>0) Begin Insert Into @Rtn Select ltrim(rtrim(Substring(@ListofIds,1,Charindex(',',@ListofIds)-1))) Set @ListofIds = Substring(@ListofIds,Charindex(',',@ListofIds)+len(','),len(@ListofIds)) end Insert Into @Rtn Select ltrim(rtrim(@ListofIds)) return end 

Luego necesitas un sproc para usar eso:

 create procedure GetAdminEvents @buildingids nvarchar(1000), @startdate datetime as SELECT id,startDateTime, endDateTime From tb_bookings t INNER JOIN dbo.IntegerCommaSplit(@buildingids) i on i.IntegerValue = t.id WHERE startDateTime <= @fromDate 

Finalmente, tu código:

 [WebMethod] public MiniEvent[] getAdminEvents(int[] buildingIDs, DateTime startDate) command.CommandText = @"exec GetAdminEvents"; SqlParameter buildID= new SqlParameter("@buildingIDs", buildingIDs); 

Eso va mucho más allá de lo que hizo su pregunta, pero hará lo que necesita.

Nota: si pasa algo que no sea int, la función de base de datos completa fallará. Dejo el manejo de errores para eso como un ejercicio para el usuario final.

NOTA: generalmente no soy para usar consultas sin parámetros. EN ESTA INSTANCIA, sin embargo, dado que se trata de una matriz de enteros, podría hacer tal cosa y sería más eficiente. Sin embargo, dado que todo el mundo parece querer degradar la respuesta porque no cumple con sus criterios de asesoramiento válido, presentaré otra respuesta que funciona de manera horrible, pero probablemente se ejecutará en LINK2SQL.

Asumiendo, como dice su pregunta, que tiene una matriz de entradas, puede usar el siguiente código para devolver una cadena que contendría una lista delimitada por comas que SQL aceptaría:

 private string SQLArrayToInString(Array a) { StringBuilder sb = new StringBuilder(); for (int i = 0; i < a.GetUpperBound(0); i++) sb.AppendFormat("{0},", a.GetValue(i)); string retVal = sb.ToString(); return retVal.Substring(0, retVal.Length - 1); } 

Entonces, le recomendaría que omita intentar parametrizar el comando dado que esto es una matriz de entradas y solo use:

 command.CommandText = @"SELECT id, startDateTime, endDateTime From tb_bookings WHERE buildingID IN (" + SQLArrayToInString(buildingIDs) + ") AND startDateTime <= @fromDate"; 

Un método XML superrápido que no requiere código inseguro o funciones definidas por el usuario:

Puede usar un procedimiento almacenado y pasar la lista de identificadores de edificio separados por comas:

 Declare @XMLList xml SET @XMLList=cast(''+replace(@buildingIDs,',','')+'' as xml) SELECT xivalue('.','varchar(5)') from @XMLList.nodes('i') x(i)) 

Todo el crédito va al blog de Guru Brad Schulz

Visite el procedimiento almacenado de T-SQL que acepta múltiples valores de Id para obtener ideas sobre cómo hacer esto.

Yo uso ese enfoque y funciona para mí.

Mi acto variable = mi lista de identificaciones en cadena.

act = “1, 2, 3, 4”

  command = new SqlCommand("SELECT x FROM y WHERE x.id IN (@actions)", conn); command.Parameters.AddWithValue("@actions", act); command.CommandText = command.CommandText.Replace("@actions", act); 

[WebMethod]

public MiniEvent [] getAdminEvents (int buildingID , DateTime startDate)

SqlParameter buildID = new SqlParameter (“@ buildingIDs”, buildingID );

Tal vez estoy siendo demasiado detallado, pero este método acepta una sola int, no una matriz de enter. Si espera pasar una matriz, necesitará actualizar su definición de método para tener una matriz int. Una vez que obtenga esa matriz, necesitará convertir la matriz a una cadena si planea usarla en una consulta SQL.

Puedes usar esto Ejecute en SQLServer para crear una función en su base de datos (solo una vez):

 IF EXISTS( SELECT * FROM sysobjects WHERE name = 'FN_RETORNA_ID_FROM_VARCHAR_TO_TABLE_INT') BEGIN DROP FUNCTION FN_RETORNA_ID_FROM_VARCHAR_TO_TABLE_INT END GO CREATE FUNCTION [dbo].FN_RETORNA_ID_FROM_VARCHAR_TO_TABLE_INT (@IDList VARCHAR(8000)) RETURNS @IDListTable TABLE (ID INT) AS BEGIN DECLARE --@IDList VARCHAR(100), @LastCommaPosition INT, @NextCommaPosition INT, @EndOfStringPosition INT, @StartOfStringPosition INT, @LengthOfString INT, @IDString VARCHAR(100), @IDValue INT --SET @IDList = '11,12,113' SET @LastCommaPosition = 0 SET @NextCommaPosition = -1 IF LTRIM(RTRIM(@IDList)) <> '' BEGIN WHILE(@NextCommaPosition <> 0) BEGIN SET @NextCommaPosition = CHARINDEX(',',@IDList,@LastCommaPosition + 1) IF @NextCommaPosition = 0 SET @EndOfStringPosition = LEN(@IDList) ELSE SET @EndOfStringPosition = @NextCommaPosition - 1 SET @StartOfStringPosition = @LastCommaPosition + 1 SET @LengthOfString = (@EndOfStringPosition + 1) - @StartOfStringPosition SET @IDString = SUBSTRING(@IDList,@StartOfStringPosition,@LengthOfString) IF @IDString <> '' INSERT @IDListTable VALUES(@IDString) SET @LastCommaPosition = @NextCommaPosition END --WHILE(@NextCommaPosition <> 0) END --IF LTRIM(RTRIM(@IDList)) <> '' RETURN ErrorBlock: RETURN END --FUNCTION 

Después de crear la función, debe llamar esto a su código:

 command.CommandText = @"SELECT id, startDateTime, endDateTime From tb_bookings WHERE buildingID IN (SELECT ID FROM FN_RETORNA_ID_FROM_VARCHAR_TO_TABLE_INT(@buildingIDs))) AND startDateTime <= @fromDate"; command.Parameters.Add(new SqlParameter(){ DbType = DbType.String, ParameterName = "@buildingIDs", Value = "1,2,3,4,5" //Enter the parameters here separated with commas }); 

Esta función obtiene las comas internas de texto en "matriz" y crea una tabla con estos valores como int, llamada ID. Cuando esta función está en su base de datos, puede usarla en cualquier proyecto.


Gracias a Microsoft MSDN.

Igo S Ventura

Microsoft MVA

Sistema Ari de Sá

igo1-2@hotmail.com

PD: soy de Brasil. Disculpa mi inglés ... XD

Aquí hay una solución de Linq que pensé. Insertará automáticamente todos los elementos en la lista como parámetros @ item0, @ item1, @ item2, @ item3, etc.

 [WebMethod] public MiniEvent[] getAdminEvents(Int32[] buildingIDs, DateTime startDate) { // Gets a list with numbers from 0 to the max index in buildingIDs, // then transforms it into a list of strings using those numbers. String idParamString = String.Join(", ", (Enumerable.Range(0, buildingIDs.Length).Select(i => "@item" + i)).ToArray()); command.CommandText = @"SELECT id, startDateTime, endDateTime From tb_bookings WHERE buildingID IN (" + idParamString + @") AND startDateTime <= @fromDate"; // Reproduce the same parameters in idParamString for (Int32 i = 0; i < buildingIDs.Length; i++) command.Parameters.Add(new SqlParameter ("@item" + i, buildingIDs[i])); command.Parameters.Add(new SqlParameter("@fromDate", startDate); // the rest of your code... }