How to Pass Table-Valued Parameters to Stored Procedures using ADO.NET?

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

Table-valued parameters provide a streamlined method for passing datasets to stored procedures. This guide demonstrates how to implement this functionality within ADO.NET.

1. Defining a User-Defined Table Type in SQL Server

First, create a user-defined table type within your SQL Server database to serve as the structure for your table-valued parameter:

CREATE TYPE [dbo].[MyDataType] As Table
(
    ID INT,
    Name NVARCHAR(50)
)

2. Stored Procedure Creation

Next, create a stored procedure that accepts this user-defined table type as a parameter:

CREATE PROCEDURE [dbo].[MyProcedure]
(
    @myData [dbo].[MyDataType] READONLY
)
AS
BEGIN
    SELECT * FROM @myData
END

3. DataTable Construction in C#

In your C# code, build a DataTable to mirror the structure of your SQL Server table type:

DataTable myDataTable = new DataTable("MyDataType");
myDataTable.Columns.Add("Name", typeof(string));
myDataTable.Columns.Add("Id", typeof(int));
myDataTable.Rows.Add("XYZ", 1);
myDataTable.Rows.Add("ABC", 2);

4. SqlParameter Configuration

Finally, create a SqlParameter object and configure it to represent your table-valued parameter:

SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@myData";
parameter.SqlDbType = System.Data.SqlDbType.Structured;
parameter.TypeName = "[dbo].[MyDataType]"; // Specify the type name
parameter.Value = myDataTable;
command.Parameters.Add(parameter);

By following these steps, you can efficiently pass table-valued parameters to your stored procedures using ADO.NET. Remember to replace [dbo].[MyDataType] with the actual name of your user-defined table type.

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