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

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;
    }
}

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.

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;

    }

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