CODEWITHSUNDEEP
jqueryasp.net-mvc-3sql-server-2008
Anton Lyhin
Anton Lyhin

Reputation: 1945

What are the best ways to send arrays from js (jQuery) to sql sproc? Array transmitting between layers

Array transmitting between layers.

What are the best ways to send arrays from js (jQuery) to sql sproc? Could you share your ideas?

This generalizes my previous questions:

  1. Js to MVC controller.

    https://stackoverflow.com/a/9109671/1145224

  2. Best way to send arrays from MVC to SQL. (Is still open for discussion)

  3. And this interesting SQL post for testing. What about [Table Valued Parameters]?

    http://www.adathedev.co.uk/2010/02/sql-server-2008-table-valued-parameters.html

Upvotes: 2

Views: 595

Answers (1)

Oleg Dok
Oleg Dok

Reputation: 21766

I think that best way is to send array as Table-Valued Parameter

How to pass an array to SP from MVC (any c# code). In your case it is a int[] array, right?

public static DataTable ConvertIntArrayToDataTable(IEnumerable<int> array, bool AllowNull = false)
{
    var dt = new DataTable();
    dt.Columns.Add(new DataColumn("Value", typeof(int)) { AllowDBNull = AllowNull });
    foreach(var i in array)
      dt.Rows.Add(i));
    return dt;
}

You also have to implement User Defined Table Type

CREATE TYPE IntArray AS TABLE
(
  Value int not null
)

Create procedure which accepts this parameter:

CREATE PROCEDURE YourProc
  @DataArray IntArray READONLY
AS
BEGIN
  SELECT Value FROM @DataArray
END

and finally call the proc with approptiate parameter:

void CallProc(int[] array)
{
// assuming there is exisis already created connection and command named sqlCommand

  sqlCommand.CommandText = "YourProc";
  sqlCommand.CommandType = CommandType.StoredProcedure;
  sqlCommand.Parameters.Clear();
  sqlCommand.AddWithValue("@DataArray", ConvertIntArrayToDataTable(array));
  sqlCommand.ExecuteNonQuery();
}

Upvotes: 1

Related Questions