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

Question

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

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 l'ensemble 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 caractères List<string> à la procédure stockée??? Ou est-il une meilleure façon de le faire?

Merci beaucoup, Brett

Answer 1

Si vous utilisez SQL Server 2008, une nouvelle fonctionnalité appelée type de table définie par l'utilisateur est présente. Voici un exemple d'utilisation:

Créez votre 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 quelques SQL à utiliser en c #:

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

    using (SqlCommand cmd = new SqlCommand("exec sp_UseStringList @list", con))
    {
        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;

        using (var dr = cmd.ExecuteReader())
        {
            while (dr.Read())
                Console.WriteLine(dr["Item"].ToString());
        }
    }
}
 

Answer 2

Le modèle typique dans cette situation est de passer les éléments dans une liste délimitée par des virgules, puis en SQL le scinde en une table utilisable. La plupart des gens créent généralement une fonction spécifiée comme ceci:

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

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

Answer 3

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

Les options suivantes sont disponibles:

1. En passant 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 les nouveaux Utilisateurs définis par le 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.

Answer 4

Yep, 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 tirer parti de la TVP (Tableau de Paramètres de Valeur): 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

Answer 5

Si vous préférez diviser une liste CSV en SQL, il existe une façon différente de le faire en utilisant des expressions de table communes (CTE). Voir Méthode efficace pour scinder une chaîne à l'aide de CTE .