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
– jdweng, Commented Jun 21, 2017 at 17:33
You cannot parameterize an IN list, use a Table Valued Parameter instead, E.g. stackoverflow.com/questions/5595353/… / stackoverflow.com/questions/2377506/… — Alex K.
– Alex K., Commented Jun 21, 2017 at 17:33

Erik Funkenbusch · Accepted Answer · 2017-06-21 17:36:34Z

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.

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.

answered Jun 21, 2017 at 17:36

Erik Funkenbusch

93.7k29 gold badges201 silver badges292 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Christos · Accepted Answer · 2017-06-21 17:42:54Z

1

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

edited Jun 21, 2017 at 17:42

answered Jun 21, 2017 at 17:34

Christos

54.1k9 gold badges82 silver badges114 bronze badges

2 Comments

Erik Funkenbusch Over a year ago

The DataTable method is messy. You can pass a collection directly, although I don't have an example of this handy.

Christos Over a year ago

@ErikFunkenbusch I wasn't aware of this. Thanks for the info. I will check it out.

Collectives™ on Stack Overflow

Stored Procedure With Array parameters csharp to mssql

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related