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

215697 次浏览
小开

The only way I'm aware of is building CSV list and then passing it as string. Then, on SP side, just split it and do whatever you need.

小开

The typical pattern in this situation is to pass the elements in a comma delimited list, and then in SQL split that out into a table you can use. Most people usually create a specified function for doing this like:

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

And then you can use it in other queries.

小开

Yep, make Stored proc parameter as VARCHAR(...) And then pass comma separated values to a stored procedure.

If you are using Sql Server 2008 you can leverage TVP (Table Value Parameters): SQL 2008 TVP and LINQ if structure of QueryTable more complex than array of strings otherwise it would be an overkill because requires table type to be created within SQl Server

小开

Make a datatable with one column instead of List and add strings to the table. You can pass this datatable as structured type and perform another join with title field of your table.

小开
最佳答案

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
小开

No, arrays/lists can't be passed to SQL Server directly.

The following options are available:

  1. Passing a comma-delimited list and then having a function in SQL split the list. The comma delimited list will most likely be passed as an Nvarchar()
  2. Pass xml and have a function in SQL Server parse the XML for each value in the list
  3. Use the new defined User Defined table type (SQL 2008)
  4. Dynamically build the SQL and pass in the raw list as "1,2,3,4" and build the SQL statement. This is prone to SQL injection attacks, but it will work.
小开

If you prefer splitting a CSV list in SQL, there's a different way to do it using Common Table Expressions (CTEs). See Efficient way to string split using CTE.

小开 
CREATE TYPE [dbo].[StringList1] AS TABLE(
[Item] [NVARCHAR](MAX) NULL,
[counts][nvarchar](20) NULL);

create a TYPE as table and name it as"StringList1"

create PROCEDURE [dbo].[sp_UseStringList1]
@list StringList1 READONLY
AS
BEGIN
-- Just return the items we passed in
SELECT l.item,l.counts FROM @list l;
SELECT l.item,l.counts into tempTable FROM @list l;
End

The create a procedure as above and name it as "UserStringList1" s

String strConnection = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString.ToString();
SqlConnection con = new SqlConnection(strConnection);
con.Open();
var table = new DataTable();


table.Columns.Add("Item", typeof(string));
table.Columns.Add("count", typeof(string));


for (int i = 0; i < 10; i++)
{
table.Rows.Add(i.ToString(), (i+i).ToString());


}
SqlCommand cmd = new SqlCommand("exec sp_UseStringList1 @list", con);




var pList = new SqlParameter("@list", SqlDbType.Structured);
pList.TypeName = "dbo.StringList1";
pList.Value = table;


cmd.Parameters.Add(pList);
string result = string.Empty;
string counts = string.Empty;
var dr = cmd.ExecuteReader();


while (dr.Read())
{
result += dr["Item"].ToString();
counts += dr["counts"].ToString();
}

in the c#,Try this


提交答案