stored - sqlparameter[] c# ejemplos

DÓNDE EN(conjunto de ID)

Question

Tengo un servicio web que se pasa una serie de entradas. Me gustaría hacer la declaración 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);
}

Answer 1

[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.

Answer 2

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

Answer 3

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 declaración 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.).

Answer 4

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.

Answer 5

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á

[email protected]

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

Answer 6

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(''<i>''+replace(@buildingIDs,'','',''</i><i>'')+''</i>'' as xml)
SELECT x.i.value(''.'',''varchar(5)'') from @XMLList.nodes(''i'') x(i))

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

Answer 7

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

Answer 8

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

Answer 9

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