SQL Bulk Copy

CREATE TYPE EmployeeType AS TABLE

(

    Id INT,

    Name VARCHAR(50),

    Age INT

);

GO

CREATE PROCEDURE InsertEmployees

    @EmployeeData EmployeeType READONLY

AS

BEGIN

    INSERT INTO Employees (Id, Name, Age)

    SELECT Id, Name, Age FROM @EmployeeData;

END;

C#--code to call Stored Procedure

using System;

using System.Data;

using System.Data.SqlClient;

class Program

{

    static void Main()

    {

        string connectionString = "your_connection_string_here";

        using (SqlConnection conn = new SqlConnection(connectionString))

        {

            conn.Open();

            using (SqlCommand cmd = new SqlCommand("InsertEmployees", conn))

            {

                cmd.CommandType = CommandType.StoredProcedure;

                // Create DataTable matching EmployeeType structure

                DataTable employeeTable = new DataTable();

                employeeTable.Columns.Add("Id", typeof(int));

                employeeTable.Columns.Add("Name", typeof(string));

                employeeTable.Columns.Add("Age", typeof(int));

                // Add sample data

                employeeTable.Rows.Add(1, "Alice", 25);

                employeeTable.Rows.Add(2, "Bob", 30);

                // Add parameter

                SqlParameter param = cmd.Parameters.AddWithValue("@EmployeeData", employeeTable);

                param.SqlDbType = SqlDbType.Structured;

                cmd.ExecuteNonQuery();

            }

        }

        Console.WriteLine("Bulk insert completed!");

    }

}