Insert DataTable in to SQL Server Table using C# and VB.Net

Step 1 : Create User-Defined TableType.

CREATE TYPE [dbo].[Employee] AS TABLE(

      [Id] [int] NULL,

      [Name] [varchar](100) NULL,

      [Country] [varchar](50) NULL

)

GO

when user define type is created it will display in Object Explorer
Programability > Types > User-Defined TableTypes

or

User-Defined TableType is also created from Object Explorer
Right click on folder Programability > Types > User-Defined TableTypes
and create new  type

Step 2 : Create Stored Procedure which accept DataTable as  parameter

CREATE PROCEDURE [dbo].[Insert_Employee]

      @tblEmployee CustomerType READONLY

AS

BEGIN

      SET NOCOUNT ON;

     

      INSERT INTO Customers(CustomerId, Name, Country)

      SELECT Id, Name, Country FROM @tblEmployee

END

Step 3 : Call Stored Procedure From C# and VB

C# code

SqlCommand cmd = new SqlCommand("Insert_Employee")

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = con;

cmd.Parameters.AddWithValue("@tblEmployee", DatatableObject);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

VB code

cmd.CommandType = CommandType.StoredProcedure

cmd.Connection = con

cmd.Parameters.AddWithValue("@tblEmployee", DatatableObject)

con.Open()

cmd.ExecuteNonQuery()

con.Close()