Comment passer un tableau dans un serveur SQL, procédure stockée

Question

Comment passer un tableau dans un serveur SQL, procédure stockée ?

Par exemple, j’ai la liste des employés. Je veux utiliser cette liste comme une table et y adhérer avec une autre table. Mais la liste des employés doit être passé comme paramètre de langage c#.

Answer 1

Puisque vous avez une Liste déjà, je pense qu'il y a plus simple des façons que XML.

SQL Server 2008 (ou version plus récente)

Tout d'abord, dans votre base de données, créer les deux objets:

CREATE TYPE dbo.EmployeeList
AS TABLE
(
  EmployeeID INT
);
GO

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

  SELECT EmployeeID FROM @List; 
END
GO

Maintenant, dans votre code C#:

DataTable tvp = new DataTable();
// define / populate DataTable from your List here

using (conn)
{
    SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
    tvparam.SqlDbType = SqlDbType.Structured;
    // execute query, consume results, etc. here
}

SQL Server 2005

Si vous utilisez SQL Server 2005, je voudrais encore vous recommandons une fonction "split" sur XML. Tout d'abord, créez une fonction:

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

Maintenant, votre procédure stockée peut-être simplement:

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

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

Et dans votre code C# vous avez juste à passer la liste en tant que '1,2,3,12'...

Je vous recommande de comparer la maintenabilité et de la performance de ces options en fonction de la méthode sélectionnée.

Answer 2

Utiliser un paramètre table pour votre procédure stockée.

Lorsque vous le passez à partir de c#, vous devez ajouter le paramètre avec le type de données de SqlDb.Structured.

Voir ici : http://msdn.microsoft.com/en-us/library/bb675163.aspx

Exemple :

Answer 3

Vous avez besoin de passer comme un paramètre XML.

Edit: rapide code de mon projet pour vous donner une idée:

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)

Ensuite, vous pouvez utiliser la table temporaire pour rejoindre avec vos tables. Nous avons défini arrayOfUlong comme un construit en schéma XML pour maintenir l'intégrité des données, mais vous n'avez pas à le faire. Je vous recommande de l'utiliser alors voici un petit code pour s'assurer que vous obtenez toujours un XML avec longs.

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 4

Il n’y a pas de support pour tableau dans sql server, mais il existe plusieurs moyens par lesquels vous pouvez passer la collection à une procédure stockée.

1. À l’aide de datatable
2. En utilisant le XML. Essayez de convertir votre collection au format xml et de le passer en tant qu’entrée à une procédure stockée

Le lien ci-dessous peut vous aider

en passant la collection d’une procédure stockée

Answer 5

J'ai été à la recherche à travers tous les exemples et les réponses de comment passer un tableau à sql server sans les tracas de la création de nouveaux type de Table,jusqu'à ce que j'ai trouvé ce lien, ci-dessous comment j'ai appliqué à mon projet:

--Le code suivant va avoir un Tableau comme Paramètre, et insérer les valeurs de la --tableau dans un autre tableau

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 

J'espère que vous apprécierez