存储过程，将数组参数csharp转换为mssql [英] Stored Procedure With Array parameters csharp to mssql

问题描述

这是我在SQL中手动进行的查询工作：

This is my manual query work in SQL:

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

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

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

我在C＃中有一个电话阵列。我从视图的参数中获取此数组（选中多个复选框）。这是我的观点：

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

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

这是我的控制器操作：

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.

推荐答案

我建议您使用表值参数

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

然后，在C＃代码处，您应该创建一个带有一列的DataTable，然后将您提到的值传递给该表。最后，您应该将此参数传递给存储过程。

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.

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

然后，如果我们假设您已命名 command 您将要求执行的命令，在执行该命令之前，应将上面的内容添加为参数：

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

这篇关于存储过程，将数组参数csharp转换为mssql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！