存储过程,将数组参数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屋!