C# SQL Server – Passing a list to a stored procedure

by

If you’re using SQL Server 2008, there’s a new featured called a User Defined Table Type. Here is an example of how to use it:

Create your User Defined Table Type:

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

Next you need to use it properly in your stored procedure:

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

Finally here’s some sql to use it in 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());
          }
         }
    }
}

To execute this from SSMS

DECLARE @list AS StringList

INSERT INTO @list VALUES ('Apple')
INSERT INTO @list VALUES ('Banana')
INSERT INTO @list VALUES ('Orange')

-- Alternatively, you can populate @list with an INSERT-SELECT
INSERT INTO @list
   SELECT Name FROM Fruits

EXEC sp_UseStringList @list

More Related Contents:

  1. What are the pros and cons to keeping SQL in Stored Procs versus Code [closed]
  2. Difference between Parameters.Add(string, object) and Parameters.AddWithValue
  3. Passing List to SQL Stored Procedure
  4. Entity Framework – stored procedure return value
  5. What is passing parameters to SQL and why do I need it?
  6. Add WHERE clauses to SQL dynamically / programmatically
  7. How to pass a null variable to a SQL Stored Procedure from C#.net code
  8. Store value in Yes/No field in Access using C#
  9. How to save leap date ’02/29/2006′ to sql server database? [duplicate]
  10. how to get date difference between two dates using DateDiff Excluding the weekends [duplicate]
  11. How to pass an array into a SQL Server stored procedure
  12. C# Data Connections Best Practice?
  13. Call a stored procedure with parameter in c#
  14. Calling stored procedure with return value
  15. Is there a good LINQ way to do a cartesian product?
  16. Read SQL Table into C# DataTable
  17. linq case statement
  18. How do I connect to an MDF database file?
  19. SQL injection on INSERT
  20. SQL WHERE clause matching values with trailing spaces
  21. The C# using statement, SQL, and SqlConnection
  22. C# constructing parameter query SQL – LIKE %
  23. Generate class from database table
  24. How do I translate a List into a SqlParameter for a Sql In statement? [duplicate]
  25. Calling a SQL User-defined function in a LINQ query
  26. Enabling Foreign key constraints in SQLite
  27. Passing parameter to stored procedure in C#
  28. no overload for method “ToString” takes 1 arguments
  29. Mapping columns in a DataTable to a SQL table with SqlBulkCopy
  30. SQL connection string for microsoft access 2010 .accdb

Leave a Comment