How to pass datatable as parameter to Stored Procedure in ADO.Net C# ?

Step by Step Explanation:-

In this Article, i have explained step by step about passing datatable as parameter to stored procedure in ADO.Net C#.By using this sample ,you can pass bulk datatable directly to sql server database.

Step 1:- Create User defined Table Type in SQL Server DAtabase

                     
GO
CREATE TYPE [dbo].[TestActivityType] AS TABLE(
	[activityname] [nvarchar](max) NULL,
	[rating] [varchar](max) NULL
)
GO
                    

Step 2:- Create Stored Procedure which uses the above Table Type

                    GO
                   
                    SET ANSI_NULLS ON
                    GO
                    SET QUOTED_IDENTIFIER ON
                    GO
                    ALTER procedure [dbo].[sp_TestActivities]
                    (
                     @TestActivityType dbo.TestActivityType readonly
                    )
                    as
                    begin
                    insert into TestActivities(ActivityName,Rating)
                    select ActivityName,Rating from @testactivitytype
                    end

Step 3:- Now Go to Dotnet Side, Go to SampleActivityPage and pass the datatable directly to your DALSample class.For eg:- You can write the below code on button click and check.

                         DataTable dtActivity = new DataTable();
                        DataColumn dcActivityName = new DataColumn("ActivityName");
                                               dtActivity.Columns.Add(dcActivityName);

                        DataColumn dcRrating = new DataColumn("Rating");
                                               dtActivity.Columns.Add(dcrating);
                        DataRow drowActivity = dtActivity.NewRow();
                                               drowActivity["activityname"] = "Running";
                                               drowActivity["rating"] = "5";
                                               dtActivity.Rows.Add(drowActivity);
                        bool reesult = DALSample.TestActivity(dtActivity);

                

Step 4:

Step 5:

Step 6:

Step 7:

Step 8:

Step 9:

Step 10:

HAVE FUN AND ENJOY CODING !!! HAPPY CODING !!!