tabla - C#SQL Server-Pasar una lista a un procedimiento almacenado

pasar una tabla como parametro sql server

Question

Estoy llamando a un procedimiento almacenado de SQL Server desde mi código 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();

Mi proceso almacenado es bastante estándar, pero se une con QueryTable (de ahí la necesidad de utilizar un proceso almacenado).

Ahora: quiero agregar una lista de cadenas, List<string> , al conjunto de parámetros. Por ejemplo, mi consulta de proceso almacenada es la siguiente:

SELECT feature 
FROM table1 t1 
INNER JOIN @QueryTable t2 ON t1.fid = t2.fid 
WHERE title IN <LIST_OF_STRINGS_GOES_HERE>

Sin embargo, la lista de cadenas es dinámica y de unos pocos cientos de largo.

¿Hay alguna forma de pasar una lista de cadenas List<string> al proceso almacenado ??? ¿O hay una mejor manera de hacer esto?

Muchas gracias, Brett

Answer 1

El patrón típico en esta situación es pasar los elementos en una lista delimitada por comas, y luego dividirlos en SQL en una tabla que puede usar. La mayoría de las personas suele crear una función específica para hacer esto, como:

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

Y luego puedes usarlo en otras consultas.

Answer 2

Haga una tabla de datos con una columna en lugar de Lista y agregue cadenas a la tabla. Puede pasar esta tabla de datos como tipo estructurado y realizar otra unión con el campo de título de su tabla.

Answer 3

La única forma que conozco es compilar la lista CSV y luego pasarla como una cadena. Luego, en el lado SP, solo divídelo y haz lo que necesites.

Answer 4

No, las matrices / listas no se pueden pasar directamente a SQL Server.

Las siguientes opciones están disponibles:

1. Pasar una lista delimitada por comas y luego tener una función en SQL divide la lista. La lista delimitada por comas probablemente pase como un Nvarchar ()
2. Pasar xml y tener una función en SQL Server analizar el XML para cada valor en la lista
3. Utilice el nuevo tipo de tabla definido por el usuario definido (SQL 2008)
4. Construya dinámicamente el SQL y pase en la lista sin procesar como "1,2,3,4" y construya la instrucción SQL. Esto es propenso a ataques de inyección SQL, pero funcionará.

Answer 5

Sí, haga que el parámetro de proceso almacenado sea VARCHAR(...) Y luego pase los valores separados por comas a un procedimiento almacenado.

Si está utilizando Sql Server 2008 puede aprovechar TVP ( Table Value Parameters ): SQL 2008 TVP y LINQ si la estructura de QueryTable es más compleja que una matriz de cadenas, de lo contrario sería una exageración porque requiere que se cree el tipo de tabla dentro del servidor SQl.

Answer 6

Si está utilizando SQL Server 2008, hay una nueva característica denominada Tipo de tabla definida por el usuario. Aquí hay un ejemplo de cómo usarlo:

Crea tu tipo de tabla definida por el usuario:

CREATE TYPE [dbo].[StringList] AS TABLE(
    [Item] [NVARCHAR](MAX) NULL
);

A continuación, debe usarlo correctamente en su procedimiento almacenado:

CREATE PROCEDURE [dbo].[sp_UseStringList]
    @list StringList READONLY
AS
BEGIN
    -- Just return the items we passed in
    SELECT l.Item FROM @list l;
END

Finalmente, aquí hay algunos sql para usarlo en c #:

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

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

          cmd.Parameters.Add(pList);

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

Answer 7

Si prefiere dividir una lista CSV en SQL, hay una forma diferente de hacerlo utilizando Expresiones de tabla comunes (CTE). Consulte la forma eficiente de dividir cadenas usando CTE .