Brett Brett - 1 month ago 14
SQL Question

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

I am calling a SQL Server stored procedure from my C# code:

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


My stored proc is fairly standard but does a join with
QueryTable
(hence the need for for using a stored proc).

Now: I want to add a list of strings,
List<string>
, to the parameter set. For example, my stored proc query goes like this:

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


However, the list of strings is dynamic and a few hundred long.

Is there a way to pass a list of strings
List<string>
to the stored proc???
Or is there a better way to do this?

Many thanks,
Brett

Answer

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());
          }
         }
    }
}
Comments