Comment passer des paramètres de valeur de table à une procédure stockée depuis un code .net ?

Question

J'ai une base de données SQL Server 2005. Dans quelques procédures, j'ai des paramètres de table que je transmets à une procédure stockée en tant qu'élément d'information. nvarchar (séparés par des virgules) et les diviser en valeurs individuelles. 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 faire migrer la base de données vers SQL Server 2008. Je sais qu'il existe 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 à la liste des paramètres dans une commande SQL.

Est-ce que quelqu'un connaît la syntaxe correcte de la Parameters.Add procédure ? Ou existe-t-il un autre moyen de transmettre ce paramètre ?

Answer 1

DataTable , DbDataReader ou IEnumerable<SqlDataRecord> peuvent être utilisés pour remplir un paramètre à valeur de table, conformément à l'article MSDN Paramètres évalués par table dans SQL Server 2008 (ADO.NET) .

L'exemple suivant illustre l'utilisation d'un DataTable ou 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;
    }
}

Answer 2

En complément de la réponse de Ryan, vous devrez également définir l'option DataColumn 's Ordinal si vous avez affaire à un table-valued parameter con multiple colonnes dont les ordinaux sont no par ordre alphabétique.

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

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

Vous devrez ordonner vos colonnes de la sorte en 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 le faites pas, vous obtiendrez une erreur d'analyse, failed to convert nvarchar to int.

Answer 3

On dirait que le Conseils MSSQL article - Paramètres de valeur de table dans SQL Server 2008 et .NET (C#) montre comment y parvenir.

Le lien ci-dessus semble être mort, mais il est disponible sur la Wayback Machine .

Answer 4

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;

    }

Answer 5

La façon la plus propre de travailler avec elle. En supposant que votre table est une liste d'entiers appelée "dbo.tvp_Int" (Personnalisez 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 tableau dans une ligne n'importe où en faisant simplement ceci :

cmd.Parameters.AddWithValueFor_Tvp_Int("@IDValues", listOfIds);