存储过程使用数组参数csharp到mssql

This is my manual query work in SQL:

这是我在SQL中的手动查询工作:

SELECT * FROM Accounts where Phone in ('05763671278','05763271578','04763125578')

how can I get parameter like this to stored procedure from csharp?

如何从csharp获取这样的参数到存储过程?

I have a phones array in C#. I get this array from parameters from a view (multi check box select). This is my view:

我在C#中有一个手机阵列。我从视图中的参数中获取此数组(多选复选框)。这是我的看法:

<td><input type="checkbox" class="CheckboxClass" value="'@item.Phones'"/></td>

This is my controller action:

这是我的控制器动作:

public ActionResult SendSMSOrMail(string[] values){

    // this give "'05763671278','05763271578','04763125578'"
    string numbers = string.Join(",", values);
    // ...
    utility.cmd.Parameters.Add("@numbers", numbers);
    // ...
}

But the result is null. What is wrong? I want to get result of all records which contain these phones.

但结果是空的。哪里不对?我想获得包含这些手机的所有记录的结果。

2 个解决方案

#1

There are several ways to do this. The first is to create your array as a string of text, separated by commas or semicolons or some other separator, then in the SQL you would parse that string. That's pretty simple and straight forward, but does not scale very well and there is a limit to the max character length of a parameter.

有几种方法可以做到这一点。第一种是将数组创建为一个文本字符串,用逗号或分号或其他分隔符分隔,然后在SQL中解析该字符串。这非常简单直接,但不能很好地扩展,并且参数的最大字符长度有限制。

A second choice is to use an XML parameter. Example here:

第二种选择是使用XML参数。这里的例子:

https://*.com/a/1069388/61164

A third choice is to use a Table parameter, which be passed as a .NET collection.

第三种选择是使用Table参数,该参数作为.NET集合传递。

I don't have an example of that handy. I've done it before, but that should give you enough info to search for yourself.

我没有那个方便的例子。我以前做过,但这应该给你足够的信息来搜索自己。

#2

I suggest you use a table valued parameter

我建议你使用表值参数

CREATE TYPE PhonesTableType AS TABLE   
( 
    Phone VARCHAR(12)
)
GO

Then you should declare (at the creation script) that you stored procedure expects a parameter of this type:

然后,您应该声明(在创建脚本中)存储过程需要此类型的参数:

CREATE PROCEDURE dbo.your_stored_procedure_name
(
    @PhonesTableType PhonesTableType READONLY
) 
....
SELECT A.* 
FROM Accounts AS A
INNER JOIN @PhonesTableType AS P
ON  A.Phone = P.Phone

Then at the C# code you should create a DataTable with one column and pass there the values you have mentioned. Last you should pass this a parameter to your stored procedure.

然后在C#代码中,您应该创建一个包含一列的DataTable,并在那里传递您提到的值。最后,您应该将此参数传递给存储过程。

var phonesDataTable = new DataTable("Phones");
phonesDataTable.Columns.Add("Phone", typeof(string));
foreach(var phone in phones) // phones is the values
{
    phonesDataTable.Rows.Add(phone);
}

Then if we suppose that you have named command the command that you would ask to be executed, before executing it you should add the above as a parameter:

然后,如果我们假设您已命名命令,您将要求执行它,在执行它之前,您应该将上面的参数添加为:

var sqlParameter = new SqlParameter
{
    ParameterName = "@PhonesTableType",
    SqlDbType = SqlDbType.Structured,
    Value = phonesDataTable
}; 
command.Parameters.Add(sqlParameter);

#1