c# - stored - Cómo pasar los parámetros de valor de la tabla al procedimiento almacenado desde el código.net

table type sql server

Question

Tengo una base de datos MS SQL Server 2005. En algunos procedimientos, tengo parámetros de tabla que paso a un proceso almacenado como un nvarchar (separados por comas) y los divido internamente en valores únicos. Lo agrego a la lista de parámetros del comando SQL de esta manera:

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

Tengo que migrar la base de datos a SQL Server 2008. Sé que hay parámetros de valores de tabla, y sé cómo usarlos en procedimientos almacenados. Pero no sé cómo pasar uno a la lista de parámetros en un comando SQL. ¿Alguien sabe la sintaxis correcta del procedimiento Parameters.Add ? ¿O hay otra forma de pasar este parámetro?

Answer 1

Además de la respuesta de Ryan, también necesitará establecer la DataColumn de DataColumn si está tratando con un table-valued parameter con table-valued parameter con varias columnas cuyos ordinales no están en orden alfabético.

Como ejemplo, si tiene el siguiente valor de tabla que se usa como parámetro en SQL:

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

Debería pedir sus columnas como tales 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 no lo haces, obtendrás un error de análisis, no se pudo convertir nvarchar a int.

Answer 2

Genérico

   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 3

La forma más limpia de trabajar con eso. Suponiendo que su tabla es una lista de enteros llamada "dbo.tvp_Int" (Personalizar para su propio tipo de tabla)

Crea este método de extensión ...

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

Ahora puede agregar un parámetro valuado de tabla en una línea en cualquier lugar simplemente haciendo esto:

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

Answer 4

DataTable DbDataReader DataTable , DbDataReader o IEnumerable<SqlDataRecord> se pueden usar para rellenar un parámetro con valores de tabla según el artículo de MSDN Parámetros con valores de tabla en SQL Server 2008 (ADO.NET) .

El siguiente ejemplo ilustra el uso de DataTable o IEnumerable<SqlDataRecord> :

Código 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

C # Code :

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