How to Pass Table-Valued Parameters from ADO.NET to SQL Server Stored Procedures?

Using ADO.NET to Pass Table-Valued Parameters to SQL Server Stored Procedures

This guide demonstrates how to transmit structured data, like tables or arrays, to SQL Server stored procedures using ADO.NET's table-valued parameters. Follow these steps:

1. Define a SQL Server User-Defined Table Type: Use the CREATE TYPE statement to structure your table-valued data. For example:

<code class="language-sql">CREATE TYPE [dbo].[MyDataType] AS TABLE
(
    ID INT,
    Name NVARCHAR(50)
)</code>

2. Create the Stored Procedure: Declare the table-valued parameter as a read-only input parameter within your stored procedure. Example:

<code class="language-sql">CREATE PROCEDURE [dbo].[MyProcedure]
(
    @myData [dbo].[MyDataType] READONLY
)
AS
BEGIN
    SELECT * FROM @myData
END</code>

3. Populate a DataTable in C#: Create a DataTable in your C# code and populate it with the data you intend to pass.

<code class="language-csharp">DataTable myDataTable = new DataTable("MyDataType");
myDataTable.Columns.Add("Name", typeof(string));
myDataTable.Columns.Add("Id", typeof(Int32));
myDataTable.Rows.Add("XYZ", 1);
myDataTable.Rows.Add("ABC", 2);</code>

4. Create and Add the SQL Parameter: Create a SqlParameter object and set its properties:

• ParameterName: Must match the stored procedure's parameter name.
• SqlDbType: Set to Structured.
• Value: Assign the DataTable as the value.

Add this parameter to your command object:

<code class="language-csharp">SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@myData";
parameter.SqlDbType = System.Data.SqlDbType.Structured;
parameter.Value = myDataTable;
command.Parameters.Add(parameter);</code>

By following these steps, you can efficiently pass table-valued parameters from your ADO.NET application to SQL Server stored procedures.

The above is the detailed content of How to Pass Table-Valued Parameters from ADO.NET to SQL Server Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!