c# - salida - pasar array a procedimiento almacenado sql server

Cómo pasar una matriz a un procedimiento almacenado de SQL Server

Question

¿Cómo pasar una matriz a un procedimiento almacenado de SQL Server?

Por ejemplo, tengo una lista de empleados. Quiero usar esta lista como una tabla y unirla a otra tabla. Pero la lista de empleados debe pasarse como parámetro desde C #.

Answer 1

SQL Server 2008 (o más nuevo)

Primero, en su base de datos, cree los siguientes dos objetos:

CREATE TYPE dbo.IDList
AS TABLE
(
  ID INT
);
GO

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List AS dbo.IDList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ID FROM @List; 
END
GO

Ahora en tu código C #:

// Obtain your list of ids to send, this is just an example call to a helper utility function
int[] employeeIds = GetEmployeeIds();

DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("ID", typeof(int)));

// populate DataTable from your List here
foreach(var id in employeeIds)
    tvp.Rows.Add(id);

using (conn)
{
    SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
    // these next lines are important to map the C# DataTable object to the correct SQL User Defined Type
    tvparam.SqlDbType = SqlDbType.Structured;
    tvparam.TypeName = "dbo.IDList";
    // execute query, consume results, etc. here
}

SQL Server 2005

Si está utilizando SQL Server 2005, todavía recomendaría una función de división sobre XML. Primero, crea una función:

CREATE FUNCTION dbo.SplitInts
(
   @List      VARCHAR(MAX),
   @Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
  RETURN ( SELECT Item = CONVERT(INT, Item) FROM
      ( SELECT Item = x.i.value(''(./text())[1]'', ''varchar(max)'')
        FROM ( SELECT [XML] = CONVERT(XML, ''<i>''
        + REPLACE(@List, @Delimiter, ''</i><i>'') + ''</i>'').query(''.'')
          ) AS a CROSS APPLY [XML].nodes(''i'') AS x(i) ) AS y
      WHERE Item IS NOT NULL
  );
GO

Ahora su procedimiento almacenado puede ser:

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List VARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT EmployeeID = Item FROM dbo.SplitInts(@List, '',''); 
END
GO

Y en su código C # solo tiene que pasar la lista como ''1,2,3,12'' ...

Encuentro que el método para pasar a través de parámetros con valores de tabla simplifica la capacidad de mantenimiento de una solución que la usa y, a menudo, tiene un mayor rendimiento en comparación con otras implementaciones que incluyen XML y división de cadenas.

Las entradas están claramente definidas (nadie tiene que adivinar si el delimitador es una coma o un punto y coma) y no tenemos dependencias en otras funciones de procesamiento que no son obvias sin inspeccionar el código del procedimiento almacenado.

En comparación con las soluciones que incluyen el esquema XML definido por el usuario en lugar de los UDT, esto implica una cantidad similar de pasos, pero en mi experiencia es un código mucho más sencillo de administrar, mantener y leer.

En muchas soluciones, es posible que solo necesite uno o algunos de estos UDT (tipos definidos por el usuario) que reutilice para muchos procedimientos almacenados. Al igual que con este ejemplo, el requisito común es pasar a través de una lista de punteros de ID, el nombre de la función describe qué contexto deben representar esos Ids, el nombre de tipo debe ser genérico.

Answer 2

El contexto siempre es importante, como el tamaño y la complejidad de la matriz. Para listas pequeñas y medianas, varias de las respuestas publicadas aquí están bien, aunque se deben hacer algunas aclaraciones:

• Para dividir una lista delimitada, un divisor basado en SQLCLR es el más rápido. Hay numerosos ejemplos en torno a si desea escribir el suyo propio, o simplemente puede descargar la biblioteca gratuita de SQL # de funciones CLR (que escribí, pero la función String_Split, y muchas otras, son completamente gratuitas).
• La división de arrays basados ​​en XML puede ser rápida, pero necesita usar XML basado en atributos, no XML basado en elementos (que es el único tipo que se muestra en las respuestas aquí, aunque el ejemplo XML de @ AaronBertrand es el mejor ya que su código usa el Función de text() XML. Para obtener más información (es decir, análisis de rendimiento) sobre el uso de XML para dividir listas, consulte "Usar XML para pasar listas como parámetros en SQL Server" por Phil Factor.
• El uso de TVP es excelente (suponiendo que esté utilizando al menos SQL Server 2008, o más reciente), ya que los datos se transmiten al proceso y se muestran analizados y tipificados como una variable de tabla. SIN EMBARGO, en la mayoría de los casos, almacenar todos los datos en DataTable significa duplicar los datos en la memoria a medida que se copian de la colección original. Por lo tanto, el uso del método DataTable para pasar TVP no funciona bien para conjuntos de datos más grandes (es decir, no se escala bien).
• XML, a diferencia de las listas delimitadas simples de Ints o Strings, puede manejar más de matrices unidimensionales, al igual que los TVP. Pero también al igual que el método TVT de DataTable , XML no se escala bien, ya que duplica el tamaño de la memoria en la memoria, ya que además tiene en cuenta la sobrecarga del documento XML.

Dicho todo esto, SI los datos que está utilizando son grandes o no son muy grandes todavía pero crecen constantemente, entonces el método IEnumerable TVP es la mejor opción, ya que transmite los datos a SQL Server (como el método DataTable ), BUT no No requiere ninguna duplicación de la colección en la memoria (a diferencia de cualquiera de los otros métodos). Publiqué un ejemplo del código SQL y C # en esta respuesta:

Pase el diccionario al procedimiento almacenado T-SQL

Answer 3

Esto te ayudara. :) Sigue los siguientes pasos,

1. Abra el Diseñador de consultas

2. Copie Pegue el siguiente código como está, creará la función que convierte la cadena a Int.

   CREATE FUNCTION dbo.SplitInts
   (
      @List      VARCHAR(MAX),
      @Delimiter VARCHAR(255)
   )
   RETURNS TABLE
   AS
     RETURN ( SELECT Item = CONVERT(INT, Item) FROM
         ( SELECT Item = x.i.value(''(./text())[1]'', ''varchar(max)'')
           FROM ( SELECT [XML] = CONVERT(XML, ''<i>''
           + REPLACE(@List, @Delimiter, ''</i><i>'') + ''</i>'').query(''.'')
             ) AS a CROSS APPLY [XML].nodes(''i'') AS x(i) ) AS y
         WHERE Item IS NOT NULL
     );
   GO

3. Crear el siguiente procedimiento almacenado

    CREATE PROCEDURE dbo.sp_DeleteMultipleId
    @List VARCHAR(MAX)
    AS
    BEGIN
         SET NOCOUNT ON;
         DELETE FROM TableName WHERE Id IN( SELECT Id = Item FROM dbo.SplitInts(@List, '','')); 
    END
    GO

4. Ejecute este SP usando exec sp_DeleteId ''1,2,3,12'' esta es una cadena de ID que desea eliminar,

5. Convierte su matriz en cadena en C # y la pasa como un parámetro de Procedimiento almacenado

   int[] intarray = { 1, 2, 3, 4, 5 };  
   string[] result = intarray.Select(x=>x.ToString()).ToArray();

   SqlCommand command = new SqlCommand();
   command.Connection = connection;
   command.CommandText = "sp_DeleteMultipleId";
   command.CommandType = CommandType.StoredProcedure;
   command.Parameters.Add("@Id",SqlDbType.VARCHAR).Value=result ;

Esto eliminará varias filas, todo lo mejor

Answer 4

He estado buscando a través de todos los ejemplos y respuestas de cómo pasar cualquier matriz a un servidor SQL sin la molestia de crear un nuevo tipo de tabla, hasta que encontré esta linK , a continuación es cómo lo apliqué a mi proyecto:

--El siguiente código obtendrá una matriz como parámetro e insertará los valores de esa matriz en otra tabla

Create Procedure Proc1 


@UserId int, //just an Id param
@s nvarchar(max)  //this is the array your going to pass from C# code to your Sproc

AS

    declare @xml xml

    set @xml = N''<root><r>'' + replace(@s,'','',''</r><r>'') + ''</r></root>''

    Insert into UserRole (UserID,RoleID)
    select 
       @UserId [UserId], t.value(''.'',''varchar(max)'') as [RoleId]


    from @xml.nodes(''//root/r'') as a(t)
END 

Espero que lo disfrutes

Answer 5

Me tomó mucho tiempo resolver esto, así que en caso de que alguien lo necesite ...

Esto se basa en el método SQL 2005 en la respuesta de Aaron y en el uso de su función SplitInts (acabo de eliminar el parámetro delim ya que siempre usaré comas). Estoy usando SQL 2008 pero quería algo que funcionara con conjuntos de datos escritos (XSD, TableAdapters) y sé que los parámetros de cadenas funcionan con esos.

Estaba intentando que su función funcionara en una cláusula de tipo "dónde en (1,2,3)", y no tuve suerte de manera directa. Así que primero creé una tabla temporal, y luego hice una combinación interna en lugar de "where in". Aquí está mi ejemplo de uso, en mi caso, quería obtener una lista de recetas que no contienen ciertos ingredientes:

CREATE PROCEDURE dbo.SOExample1
    (
    @excludeIngredientsString varchar(MAX) = ''''
    )
AS
    /* Convert string to table of ints */
    DECLARE @excludeIngredients TABLE (ID int)
    insert into @excludeIngredients
    select ID = Item from dbo.SplitInts(@excludeIngredientsString)

    /* Select recipies that don''t contain any ingredients in our excluded table */
   SELECT        r.Name, r.Slug
FROM            Recipes AS r LEFT OUTER JOIN
                         RecipeIngredients as ri inner join
                         @excludeIngredients as ei on ri.IngredientID = ei.ID
                         ON r.ID = ri.RecipeID
WHERE        (ri.RecipeID IS NULL)

Answer 6

Necesitas pasarlo como un parámetro XML.

Edición: código rápido de mi proyecto para darle una idea:

CREATE PROCEDURE [dbo].[GetArrivalsReport]
    @DateTimeFrom AS DATETIME,
    @DateTimeTo AS DATETIME,
    @HostIds AS XML(xsdArrayOfULong)
AS
BEGIN
    DECLARE @hosts TABLE (HostId BIGINT)

    INSERT INTO @hosts
        SELECT arrayOfUlong.HostId.value(''.'',''bigint'') data
        FROM @HostIds.nodes(''/arrayOfUlong/u'') as arrayOfUlong(HostId)

Luego puedes usar la tabla temporal para unirte a tus tablas. Definimos arrayOfUlong como un esquema XML incorporado para mantener la integridad de los datos, pero no tienes que hacerlo. Recomiendo usarlo, así que aquí hay un código rápido para asegurarte de que siempre obtengas un XML con largos.

IF NOT EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = ''xsdArrayOfULong'')
BEGIN
    CREATE XML SCHEMA COLLECTION [dbo].[xsdArrayOfULong]
    AS N''<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="arrayOfUlong">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded"
                            name="u"
                            type="xs:unsignedLong" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>'';
END
GO

Answer 7

No hay soporte para la matriz en el servidor SQL, pero hay varias formas en que puede pasar la recopilación a un proceso almacenado.

1. Mediante el uso de datos
2. Al utilizar XML, intente convertir su colección en un formato xml y luego pásela como entrada a un procedimiento almacenado

El siguiente enlace puede ayudarte

pasar la colección a un procedimiento almacenado

Answer 8

Según mi experiencia, al crear una expresión delimitada a partir de los ID de los empleados, existe una solución delicada y agradable para este problema. Solo debe crear una expresión de cadena como '';123;434;365;'' en el cual 123 , 434 y 365 son algunos ID de empleado. Al llamar al siguiente procedimiento y pasarle esta expresión, puede obtener los registros que desee. Fácilmente puede unirse a la "otra tabla" en esta consulta. Esta solución es adecuada en todas las versiones del servidor SQL. Además, en comparación con el uso de la variable de tabla o la tabla temporal, es una solución muy rápida y optimizada.

CREATE PROCEDURE dbo.DoSomethingOnSomeEmployees  @List AS varchar(max)
AS
BEGIN
  SELECT EmployeeID 
  FROM EmployeesTable
  -- inner join AnotherTable on ...
  where @List like ''%;''+cast(employeeID as varchar(20))+'';%''
END
GO

Answer 9

Utilice un parámetro con valores de tabla para su procedimiento almacenado.

Cuando lo pase desde C #, agregará el parámetro con el tipo de datos de SqlDb.Structured.

Consulte aquí: http://msdn.microsoft.com/en-us/library/bb675163.aspx

Ejemplo:

// Assumes connection is an open SqlConnection object.
using (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories =
  CategoriesDataTable.GetChanges(DataRowState.Added);

// Configure the SqlCommand and SqlParameter.
SqlCommand insertCommand = new SqlCommand(
    "usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;

// Execute the command.
insertCommand.ExecuteNonQuery();
}