Passing array to a SQL Server Stored Procedure

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 READONLYASBEGIN    INSERT INTO Products SELECT * FROM @INFO_ARRAYEND

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

DataTable dt = new DataTable();//Add Columnsdt.Columns.Add("ID");dt.Columns.Add("Product");dt.Columns.Add("Description");//Add rowsdt.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;}

here is a way simpler example:

I've been searching through all the examples and answers of how to pass any array to sql server,till i found this linK, below is how I applied it to my project:

--The following code is going to get an Array as Parameter and insert the values of that--array into another table

Create Procedure Proc1 @INFO_ARRAY ARRAY nvarchar(max)  //this is the array your going to pass from C# codeAS    declare @xml xml    set @xml = N'<root><r>' + replace(@INFO_ARRAY,',','</r><r>') + '</r></root>'    Insert into Products     select      t.value('.','varchar(max)')    from @xml.nodes('//root/r') as a(t)END 

Hope you enjoy it