Comment passer les paramètres de valeur de la table à la procédure stockée from.net code

j'ai une base de données MS SQL Server 2005. Dans quelques procédures j'ai des paramètres de table que je passe à un proc stocké comme nvarchar (séparé par des virgules) et divise en interne en valeurs simples. Je l'ajoute à la liste des paramètres de la commande SQL comme ceci:

cmd.Parameters.Add("@Logins", SqlDbType.NVarchar).Value = "jim18,jenny1975,cosmo";

je dois migrer la base de données vers SQL Server 2008. Je sais qu'il y a des paramètres de valeur de table, et je sais comment les utiliser dans les procédures stockées. Mais je ne sais pas comment en passer un au liste de paramètres dans une commande SQL. Est-ce que quelqu'un connaît la syntaxe correcte de la procédure Parameters.Add ? Ou est-il un autre moyen de passer ce paramètre?

133
demandé sur Solomon Rutzky 2011-04-08 16:56:20

4 réponses

DataTable , DbDataReader , ou IEnumerable<SqlDataRecord> objets peuvent être utilisés pour peupler une table-valeur paramètre selon L'article MSDN table-valeur Paramètres dans SQL Server 2008 (ADO.NET) .

l'exemple suivant illustre l'utilisation d'un DataTable ou d'un IEnumerable<SqlDataRecord> :

code SQL :

CREATE TABLE dbo.PageView
(
    PageViewID BIGINT NOT NULL CONSTRAINT pkPageView PRIMARY KEY CLUSTERED,
    PageViewCount BIGINT NOT NULL
);
CREATE TYPE dbo.PageViewTableType AS TABLE
(
    PageViewID BIGINT NOT NULL
);
CREATE PROCEDURE dbo.procMergePageView
    @Display dbo.PageViewTableType READONLY
AS
BEGIN
    MERGE INTO dbo.PageView AS T
    USING @Display AS S
    ON T.PageViewID = S.PageViewID
    WHEN MATCHED THEN UPDATE SET T.PageViewCount = T.PageViewCount + 1
    WHEN NOT MATCHED THEN INSERT VALUES(S.PageViewID, 1);
END

Code C# :

private static void ExecuteProcedure(bool useDataTable, 
                                     string connectionString, 
                                     IEnumerable<long> ids) 
{
    using (SqlConnection connection = new SqlConnection(connectionString)) 
    {
        connection.Open();
        using (SqlCommand command = connection.CreateCommand()) 
        {
            command.CommandText = "dbo.procMergePageView";
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter parameter;
            if (useDataTable) {
                parameter = command.Parameters
                              .AddWithValue("@Display", CreateDataTable(ids));
            }
            else 
            {
                parameter = command.Parameters
                              .AddWithValue("@Display", CreateSqlDataRecords(ids));
            }
            parameter.SqlDbType = SqlDbType.Structured;
            parameter.TypeName = "dbo.PageViewTableType";

            command.ExecuteNonQuery();
        }
    }
}

private static DataTable CreateDataTable(IEnumerable<long> ids) 
{
    DataTable table = new DataTable();
    table.Columns.Add("ID", typeof(long));
    foreach (long id in ids) 
    {
        table.Rows.Add(id);
    }
    return table;
}

private static IEnumerable<SqlDataRecord> CreateSqlDataRecords(IEnumerable<long> ids) 
{
    SqlMetaData[] metaData = new SqlMetaData[1];
    metaData[0] = new SqlMetaData("ID", SqlDbType.BigInt);
    SqlDataRecord record = new SqlDataRecord(metaData);
    foreach (long id in ids) 
    {
        record.SetInt64(0, id);
        yield return record;
    }
}
228
répondu Ryan Prechel 2017-04-11 06:41:01

suite à la réponse de Ryan, vous aurez également besoin de définir le DataColumn 's Ordinal propriété si vous avez affaire à un table-valued parameter avec multiple colonnes dont les ordinaires sont et non dans l'ordre alphabétique.

comme exemple, si vous avez la valeur suivante de table qui est utilisée comme paramètre dans SQL:

CREATE TYPE NodeFilter AS TABLE (
  ID int not null
  Code nvarchar(10) not null,
);

vous devriez commander vos colonnes comme telles dans C#:

table.Columns["ID"].SetOrdinal(0);
// this also bumps Code to ordinal of 1
// if you have more than 2 cols then you would need to set more ordinals

si vous ne réussissez pas à faire cela, vous obtiendrez une erreur d'analyse, vous n'avez pas réussi à convertir nvarchar en int.

26
répondu Scotty.NET 2017-02-22 16:05:23

Générique

   public static DataTable ToTableValuedParameter<T, TProperty>(this IEnumerable<T> list, Func<T, TProperty> selector)
    {
        var tbl = new DataTable();
        tbl.Columns.Add("Id", typeof(T));

        foreach (var item in list)
        {
            tbl.Rows.Add(selector.Invoke(item));

        }

        return tbl;

    }
13
répondu Martea 2014-02-07 08:47:48

La façon la plus propre de travailler avec elle. Supposons que votre table est une liste d'entiers appelée " dbo.tvp_Int "(personnaliser pour votre propre type de table)

créer cette méthode d'extension...

public static void AddWithValue_Tvp_Int(this SqlParameterCollection paramCollection, string parameterName, List<int> data)
{
   if(paramCollection != null)
   {
       var p = paramCollection.Add(parameterName, SqlDbType.Structured);
       p.TypeName = "dbo.tvp_Int";
       DataTable _dt = new DataTable() {Columns = {"Value"}};
       data.ForEach(value => _dt.Rows.Add(value));
       p.Value = _dt;
   }
}

maintenant vous pouvez ajouter un paramètre de valeur de table dans une ligne n'importe où simplement en faisant ceci:

cmd.Parameters.AddWithValueFor_Tvp_Int("@IDValues", listOfIds);
5
répondu Shahzad Qureshi 2017-03-25 02:53:08