Passing array to a SQL Server Stored Procedure

ado.net, arrays, c#, sql-server, t-sql

Solution

In SQL Server 2008 and later

Create a type in SQL Server like so:

CREATE TYPE dbo.ProductArray 
AS TABLE
(
  ID INT,
  Product NVARCHAR(50),
  Description NVARCHAR(255)
);

Alter your procedure in SQL Server:

ALTER PROC INSERT_SP
@INFO_ARRAY AS dbo.ProductArray READONLY
AS
BEGIN
    INSERT INTO Products SELECT * FROM @INFO_ARRAY
END

Then you'll need to create a `DataTable` object with values to pass in C#:

DataTable dt = new DataTable();
//Add Columns
dt.Columns.Add("ID");
dt.Columns.Add("Product");
dt.Columns.Add("Description");
//Add rows
dt.Rows.Add("7J9P", "Soda", "2000ml bottle");

using (conn)
{
    SqlCommand cmd = new SqlCommand("dbo.INSERT_SP", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter dtparam = cmd.Parameters.AddWithValue("@INFO_ARRAY", dt);
    dtparam.SqlDbType = SqlDbType.Structured;
}

Problem

How can I pass an array variable to a SQL Server stored procedure using C# and insert array values into a whole row? Thanks in advance. SQL Server table: ``` ID | Product | Description ------------------------------- 8A3H | Soda | 600ml bottle ``` C# array: ``` string[] info = new string[] {"7J9P", "Soda", "2000ml bottle"}; ``` SQL Server stored procedure: ``` ALTER PROC INSERT (@INFO_ARRAY ARRAY) AS BEGIN INSERT INTO Products VALUES (@INFO_ARRAY) END ```

Original source

Related problems