Manege large dataset in sql

Pass XML to the database using ADO

Table

CREATE TABLE [dbo].[Employee](
[ID] [int] NOT NULL,
[Name] [varchar](50) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SP

CREATE PROCEDURE MY_XML
 @xml xml
AS
BEGIN
 INSERT INTO [dbo].[Employee]
      SELECT
      Customer.value('(ID/text())[1]','Int') AS Id, --TAG
      Customer.value('(Name/text())[1]','VARCHAR(100)') AS Name --TAG
      FROM
      @xml.nodes('/Customers/Customer')AS TEMPTABLE(Customer)
END
GO

xml File

<?xml version="1.0" standalone="yes"?>
 <Customers>
 <Customer>
    <ID>1</ID>
    <Name>Chamith</Name>
 </Customer>
   <Customer>
     <ID>4</ID>
     <Name>Saranga</Name>
 </Customer>
   <Customer>
     <ID>3</ID>
     <Name>Janson</Name>
   </Customer>
   <Customer>
     <ID>2</ID>
     <Name>Sajeeka</Name>
 </Customer>
</Customers>

C# Codes

static void UploadXML()
        {
            
            string xml = File.ReadAllText(@"E:\WB\back\2\WebPortals\ABCD\Customer.xml");
            string constr = @"Data Source=JET-DEV03\TOWNSUITE;Initial Catalog=MySP;Integrated Security=True";
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("MY_XML"))
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@xml", xml);
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }

exact execution quay on db 

            exec MY_XML @xml=N'<?xml version="1.0" standalone="yes"?>
 <Customers>
 <Customer>
    <ID>1</ID>
    <Name>Chamith</Name>
 </Customer>
   <Customer>
     <ID>4</ID>
     <Name>Saranga</Name>
 </Customer>
   <Customer>
     <ID>3</ID>
     <Name>Janson</Name>
   </Customer>
   <Customer>
     <ID>2</ID>
     <Name>Sajeeka</Name>
 </Customer>
</Customers>'

=============================

Execute Table structure on database 

Table

CREATE TABLE [dbo].[Employee](
[ID] [int] NOT NULL,
[Name] [varchar](50) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

create customer type

-- Create the data type
CREATE TYPE CustomerType AS TABLE 
(
Id int,
Name varchar(50)
);
GO

SP

CREATE PROCEDURE sp_passtable
@CustomerT  as CustomerType   READONLY
AS
BEGIN
 
insert into [dbo].[Employee]
select Id,Name from @CustomerT;

END

GO

C# Code 

 static void UpdateDataTable() { 

        

            DataTable dt = new DataTable();

            dt.Columns.Add("Id");

            dt.Columns.Add("Name");

            for (int i = 10; i < 15; i++)

            {

                dt.Rows.Add(i,$"name {i}");

  

            }

            string constr = @"Data Source=JET-DEV03\TOWNSUITE;Initial Catalog=MySP;Integrated Security=True";

            using (SqlConnection con = new SqlConnection(constr))

            {

                using (SqlCommand cmd = new SqlCommand("sp_passtable"))

                {

                    cmd.Connection = con;

                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter parameter = new SqlParameter();

 

                    parameter.ParameterName = "@CustomerT";

                    parameter.SqlDbType = System.Data.SqlDbType.Structured;

                    parameter.Value = dt;

                    cmd.Parameters.Add(parameter);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();

                }

            }

        }

exact execution quay on db 

     

            declare @p1 dbo.CustomerType

                insert into @p1 values(N'0',N'name 0')

                insert into @p1 values(N'1',N'name 1')

                insert into @p1 values(N'2',N'name 2')

                insert into @p1 values(N'3',N'name 3')

                insert into @p1 values(N'4',N'name 4')

                insert into @p1 values(N'5',N'name 5')

                insert into @p1 values(N'6',N'name 6')

                insert into @p1 values(N'7',N'name 7')

                insert into @p1 values(N'8',N'name 8')

                insert into @p1 values(N'9',N'name 9')

                exec sp_passtable @CustomerT=@p1