C# SQL Server - Passage d'une liste à une procédure stockée

Je suis de l'appel d'une procédure stockée SQL Server à 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();

De ma procédure stockée est assez standard, mais ne une jointure avec QueryTable (d'où la nécessité pour les pour les à l'aide d'une procédure stockée).

Maintenant: je veux ajouter une liste de chaînes de caractères, List<string>, pour le jeu de paramètres. Par exemple, ma procédure stockée requête qui 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>

Toutefois, la liste des chaînes est dynamique et à une centaine de long.

Est-il un moyen pour passer d'une liste de chaînes de List<string> à la procédure stockée??? Ou est-il une meilleure façon de le faire?

Merci beaucoup,
Brett

Éventuellement, double de stackoverflow.com/questions/209686/...
Ce version de SQL Server?? 2005?? 2008 ?? 2008 R2 ?? SQL Server 2008 et plus récent a le concept de "table des paramètres" (voir Redth la réponse pour plus de détails)
Sans rapport avec astuce: le SqlDataReader est également IDisposable devrait donc être dans un using bloc.

InformationsquelleAutor Brett | 2011-08-17

c#sql stored-procedures

162

Si vous utilisez SQL Server 2008, il y a une nouvelle vedette appelé un Type de Table Défini par l'Utilisateur. Voici un exemple de comment l'utiliser:

Créer votre Utilisateur Type de Table Défini par:
```
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 quelques sql à utiliser en 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());
          }
         }
    }
}
```
Pour exécuter cette de SSMS
```
DECLARE @list AS StringList

INSERT INTO @list VALUES ('Apple')
INSERT INTO @list VALUES ('Banana')
INSERT INTO @list VALUES ('Orange')

-- Alternatively, you can populate @list with an INSERT-SELECT
INSERT INTO @list
   SELECT Name FROM Fruits

EXEC sp_UseStringList @list
```
- A-t-elle à définir un datatable pour définir la valeur du paramètre? Toute autre approche?
- Nous avons essayé, mais nous avons trouvé son inconvénient étant pas pris en charge par Enitiy cadre
- Je suis en train de l'utiliser avec l' IN de la clause de la sorte: SELECT [columnA] FROM [MyTable] WHERE [Id] IN (@List) mais lorsque j'essaie de mettre à jour ma Procédure Stockée pour ce faire, SQL Server 2012 me dit que je dois Déclarer la variable scalaire @Liste. Je l'utilise dans la section paramètres, comme vous êtes donc, lorsque j'essaie de mettre DÉCLARER en face d'elle je reçois beaucoup d'erreurs de syntaxe... toutes les idées @Redth ?
- soyez PRUDENT AVEC CETTE SOLUTION SI VOUS ÊTES USIN LINQ2SQL, CAR IL NE prend PAS en charge DÉFINIS par l'UTILISATEUR TABLE des TYPES de PARAMÈTRES!!! Une solution de contournement peut être trouvé dans Jon Raynor, en réponse, à l'aide de listes séparées par des virgules et un analyseur de fonction, cependant, cela a aussi des inconvénients....
- dans ce cas, ne pas utiliser de Linq2SQL. Il est préférable de concaténation de chaîne et l'analyse en T-SQL
- Ouais, alors, comment avez-vous réellement l'exécuter à partir de SSMS?
- Elle n'accepte pas la liste des<> des cordes, au lieu de DataTable?
- J'ai édité la réponse d'ajouter un exemple de la façon d'exécuter à partir de SSMS
InformationsquelleAutor Redth
20

Le modèle typique de cette situation est de passer les éléments dans une liste délimitée par des virgules, puis dans SQL répartis dans un tableau, vous pouvez utiliser. La plupart des gens créent habituellement une fonction spécifiée pour ce faire, comme:
```
 INSERT INTO <SomeTempTable>
 SELECT item FROM dbo.SplitCommaString(@myParameter)
```
Et puis vous pouvez l'utiliser dans d'autres requêtes.
- permettez-moi de jeter un lien pour une dbo.SplitCommaString mise en oeuvre complet: goo.gl/P9ROs
- Et ce qui se passe quand il y a une virgule dans un de vos champs de données?
- De les délimiter avec des tubes à la place.
- Que faire si il y a un tube dans l'un de vos champs de données?
- Puis utiliser quelque chose qui n'est pas dans votre champs de données. Nettoyer vos données, si nécessaire, mais pas beaucoup de données que j'ai jamais vu a jamais utilisé les tuyaux. Si elles sont absolument nécessaire de trouver un autre caractère, comme ¤§.
InformationsquelleAutor Tejs
7

Non, les tableaux/listes ne peuvent pas être transmis à SQL Server directement.

Les options suivantes sont disponibles:
1. Passage d'une liste délimitée par des virgules et puis d'avoir une fonction en SQL scinder la liste. La liste délimitée par des virgules sera très probablement être passé comme un type Nvarchar()
2. Passer xml et ont une fonction dans SQL Server parse le XML pour chaque valeur de la liste
3. Utiliser la nouvelle définition de l'Utilisateur type de table Défini par SQL (2008)
4. Générer dynamiquement le SQL et le passer dans les matières liste "1,2,3,4" et créer de l'instruction SQL. C'est sujette à des attaques par injection SQL, mais il faudra travailler.
InformationsquelleAutor Jon Raynor
2

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

Si vous utilisez Sql Server 2008, vous pouvez tirer parti de la TVP (Le Tableau De La Valeur Des Paramètres): SQL 2008 TVP et LINQ si la structure de la table de requête plus complexe que le tableau de chaînes de caractères sinon il serait inutile parce que requiert type de table dans SQl Server

InformationsquelleAutor sll
1

Faire une table avec une colonne au lieu de la Liste et ajouter des chaînes à la table. Vous pouvez passer cette datatable comme type structuré et effectuer une autre jointure avec le champ titre de votre table.
- c'est le chemin à parcourir. J'ai effectivement créé une table dans la db côté et chargé avec bcp d'écrire sur le serveur.
InformationsquelleAutor hungryMind
1

Si vous préférez le fractionnement d'un fichier CSV liste en SQL, il y a une manière différente de le faire à l'aide de Les Expressions De Table Communes (expressions de table communes). Voir Moyen efficace de la chaîne de split à l'aide de la CTE.
- Le lien semble mort
- Merci. Changé à un débordement de pile question de la place.
InformationsquelleAutor Larry Silverman
0

La seule façon dont je suis conscient, c'est la construction CSV liste, puis en le passant comme une chaîne de caractères. Ensuite, sur le SP côté, juste de le diviser et de faire tout ce dont vous avez besoin.

InformationsquelleAutor Andrey Agibalov

Vous devez vous connecter pour publier un commentaire.