C # SQL Server-passer une liste à une procédure stockée

j'appelle une procédure stockée sur un serveur SQL à partir de mon code C: 

using (SqlConnection conn = new SqlConnection(connstring))
{
   conn.Open();
   using (SqlCommand cmd = new SqlCommand("InsertQuerySPROC", conn))
   {
      cmd.CommandType = CommandType.StoredProcedure;

      var STableParameter = cmd.Parameters.AddWithValue("@QueryTable", QueryTable);
      var NDistanceParameter = cmd.Parameters.AddWithValue("@NDistanceThreshold", NDistanceThreshold);
      var RDistanceParameter = cmd.Parameters.AddWithValue(@"RDistanceThreshold", RDistanceThreshold);

      STableParameter .SqlDbType = SqlDbType.Structured;
      NDistanceParameter.SqlDbType = SqlDbType.Int;
      RDistanceParameter.SqlDbType = SqlDbType.Int;

      // Execute the query
      SqlDataReader QueryReader = cmd.ExecuteReader();

mon proc stocké est assez standard mais fait une jointure avec QueryTable (d'où la nécessité d'utiliser un proc stocké).

maintenant: je veux ajouter une liste de chaînes, List<string> , au paramètre set. Par exemple, ma requête stockée proc va comme ceci: 

SELECT feature 
FROM table1 t1 
INNER JOIN @QueryTable t2 ON t1.fid = t2.fid 
WHERE title IN <LIST_OF_STRINGS_GOES_HERE>

cependant, la liste des cordes est dynamique et longue de quelques centaines.

y a-t-il un moyen de passer une liste de cordes List<string> au proc stocké??? Ou est-il une meilleure façon de le faire?

Merci beaucoup, Brett

94
demandé sur marc_s 2011-08-17 21:40:34

7 réponses

si vous utilisez SQL Server 2008, il y a une nouvelle fonctionnalité appelée un type de Table défini par L'utilisateur. Voici un exemple d'utilisation:

créer le type de Table défini par L'utilisateur: 

CREATE TYPE [dbo].[StringList] AS TABLE(
    [Item] [NVARCHAR](MAX) NULL
);

ensuite, vous devez l'utiliser correctement dans votre procédure stockée: 

CREATE PROCEDURE [dbo].[sp_UseStringList]
    @list StringList READONLY
AS
BEGIN
    -- Just return the items we passed in
    SELECT l.Item FROM @list l;
END

enfin voici un peu de sql pour l'utiliser dans c#: 

using (var con = new SqlConnection(connstring))
{
    con.Open();

    using (SqlCommand cmd = new SqlCommand("exec sp_UseStringList @list", con))
    {
        using (var table = new DataTable()) {
          table.Columns.Add("Item", typeof(string));

          for (int i = 0; i < 10; i++)
            table.Rows.Add("Item " + i.ToString());

          var pList = new SqlParameter("@list", SqlDbType.Structured);
          pList.TypeName = "dbo.StringList";
          pList.Value = table;

          cmd.Parameters.Add(pList);

          using (var dr = cmd.ExecuteReader())
          {
            while (dr.Read())
                Console.WriteLine(dr["Item"].ToString());
          }
         }
    }
}
151
répondu Redth 2016-10-11 14:15:37

le modèle typique dans cette situation est de passer les éléments dans une liste délimitée par une virgule, Et puis en SQL diviser que dans une table que vous pouvez utiliser. La plupart des gens créent habituellement une fonction spécifiée pour faire ceci comme: 

 INSERT INTO <SomeTempTable>
 SELECT item FROM dbo.SplitCommaString(@myParameter)

Et ensuite vous pouvez l'utiliser dans d'autres requêtes.

19
répondu Tejs 2011-08-17 17:45:31

non, les tableaux / listes ne peuvent pas être passés directement au serveur SQL.

les options suivantes sont disponibles:

  1. passant une liste délimitée par une virgule et ayant ensuite une fonction en SQL divise la liste. La liste délimitée par une virgule sera très probablement passée en Nvarchar()
  2. passer xml et avoir une fonction dans SQL Server analyse le XML pour chaque valeur dans la liste
  3. utiliser le nouveau défini utilisateur défini table type (SQL 2008)
  4. construire dynamiquement le SQL et passer dans la liste brute comme" 1,2,3,4 " et construire la déclaration SQL. Ceci est sujet aux attaques par injection SQL, mais cela va marcher.
5
répondu Jon Raynor 2011-08-17 18:09:19

Ouais, faire Stockées proc paramètre VARCHAR(...) Et puis passer valeurs séparées par des virgules à une procédure stockée.

si vous utilisez Sql Server 2008, vous pouvez utiliser TVP ( Table Value Parameters ): SQL 2008 TVP et LINQ si la structure de QueryTable plus complexe que le tableau de chaînes de caractères sinon, ce serait une overkill parce que nécessite type de table à créer dans SQl Server

2
répondu sll 2011-08-17 17:45:33

crée un datatable avec une colonne au lieu de List et ajoute des chaînes à la table. Vous pouvez passer ce type datable comme type structuré et effectuer une autre jointure avec le champ de titre de votre table.

1
répondu hungryMind 2011-08-17 17:49:34

si vous préférez partager une liste CSV en SQL, il y a une façon différente de le faire en utilisant Expressions courantes de la Table (CTEs). Voir façon efficace de séparer les chaînes en utilisant CTE .

1
répondu Larry Silverman 2017-05-23 12:26:20

la seule façon dont je suis au courant est de construire la liste CSV et puis de la passer comme chaîne de caractères. Ensuite, du côté de SP, partagez - le et faites ce que vous voulez.

0
répondu Andrey Agibalov 2011-08-17 17:44:05