Passing array to a SQL Server Stored Procedure

by

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

More Related Contents:

  1. Use of SqlParameter in SQL LIKE clause not working
  2. WHERE IN (array of IDs)
  3. SQL: Update a row and returning a column value with 1 query
  4. ExecuteReader requires an open and available Connection. The connection’s current state is Connecting
  5. How to pass an array into a SQL Server stored procedure
  6. Call a stored procedure with parameter in c#
  7. Insert 2 million rows into SQL Server quickly
  8. Calling stored procedure with return value
  9. SQL command INSERT is working but the data not appear in table
  10. Return value from SQL Server Insert command using c#
  11. How to pass sqlparameter to IN()? [duplicate]
  12. How do I return multiple result sets with SqlCommand?
  13. SQL error: Incorrect syntax near the keyword ‘User’
  14. What represents a double in sql server?
  15. SqlConnection SqlCommand SqlDataReader IDisposable
  16. SqlBulkCopy from a List
  17. How to run multiple SQL commands in a single SQL connection?
  18. Connection timeout for SQL server
  19. MultipleActiveResultSets=True or multiple connections?
  20. How can I retrieve a list of parameters from a stored procedure in SQL Server
  21. Creating a SQL Server table from a C# datatable
  22. Generate class from database table
  23. Using SqlDataAdapter to insert a row
  24. What size do you use for varchar(MAX) in your parameter declaration?
  25. Fastest method for SQL Server inserts, updates, selects
  26. How do I calculate the equivalent to SQL Server (hashbytes(‘SHA1’,[ColumnName])) in C#?
  27. How to get efficient Sql Server deadlock handling in C# with ADO?
  28. Generating sql code programmatically
  29. How to give ADO.NET Parameters
  30. What does a timestamp in T-Sql mean in C#?

Leave a Comment