Passing a DataTable into a Stored Procedure. Is there a better way?

Question

Can A datatable somehow be passed into SQL Server 2005 or 2008 ?

I know the standard way seesm to be passing XML to a SP. And a datatable can easily be converted to XML somehow to do that.

What about passing a .NET object into a SP ? Is that possible ?

I remember hearing about SQL and CLR working together in 2008 somehow but I never understood.. Maybe that means you can refer to .NET objects within a Stored Procedure ?

Answer 1

You can create a User-defined table type in SQL. Then, in the stored procedure, accept a parameter of type (your user-defined table type) and pass in a datatable as it's value to a stored procedure.

Here's some examples from http://msdn.microsoft.com/en-us/library/bb675163.aspx:

In SQL:

CREATE TYPE dbo.CategoryTableType AS TABLE
    ( CategoryID int, CategoryName nvarchar(50) )

Then:

// Assumes connection is an open SqlConnection object.
using (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories =
  CategoriesDataTable.GetChanges(DataRowState.Added);

// Configure the SqlCommand and SqlParameter.
SqlCommand insertCommand = new SqlCommand(
    "usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;

SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;

// Execute the command.
insertCommand.ExecuteNonQuery();
}