Stored Procedure With Array parameters csharp to mssql

Question

This is my manual query work in SQL:

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

how can I get parameter like this to stored procedure from 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:

<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.

The parameter are added in sequential order and don't need a name. So use : utility.cmd.Parameters.Add("05763671278"); — jdweng, Jun 21 '17 at 17:33
You cannot parameterize an IN list, use a Table Valued Parameter instead, E.g. https://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code / https://stackoverflow.com/questions/2377506/pass-array-parameter-in-sqlcommand — Alex K., Jun 21 '17 at 17:33
Use [Table-Valued Parameters](https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine) — Ilyes, Jun 21 '17 at 17:34

Christos · Answer 1 · 2017-06-21T17:42:54.260

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.

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

The DataTable method is messy. You can pass a collection directly, although I don't have an example of this handy. — Erik Funkenbusch, Jun 21 '17 at 17:37
@ErikFunkenbusch I wasn't aware of this. Thanks for the info. I will check it out. — Christos, Jun 21 '17 at 17:43

score 1 · Answer 2 · answered Jun 21 '17 at 17:36

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.

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

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

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

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.

Stored Procedure With Array parameters csharp to mssql

2 Answers2

Linked