CODEWITHSUNDEEP
asp.netsql-serversqlconnectionsqlcommand
Saurabh
Saurabh

Reputation: 5727

Inserting multiple rows in single command using asp.net

I have educational qualification block in my asp.net form and one set of qulification will be inserted as row in the table. so i want to insert these sets, suppose 3 sets of qualification in a single shot, like 3 rows insertion in a shot in the table. I dont want to create multiple insert queires as i am using stored procedures. I heard xml can do this job but not sure.

Your help will be appreciated.

Upvotes: 0

Views: 3949

Answers (3)

Mayur Desai
Mayur Desai

Reputation: 713

You can user the SqlBulkCopy in your code, as per below code

public void InsertDataTable(DataTable dt)
{
    try
    {

        if (dt.Rows.Count > 0)
        {
            SqlBulkCopy bulk = new SqlBulkCopy(ConnectionString);
            bulk.DestinationTableName = "YourTableName";
            bulk.WriteToServer(dt);
        }

    }
    catch (Exception)
    {
        throw;
    }
}

Upvotes: 0

praveen
praveen

Reputation: 12271

If you are using SQL Server 2008 then use Table-Valued Parameters to do the insert operation in one shot

Creating TVP

CREATE TYPE tvp_Insert AS TABLE
    (
     [ID] [int] NOT NULL,
     [Name] [nvarchar](50) NULL
    )

Create a Procedure to insert the values from TVP to the destination table

CREATE PROCEDURE usp_tvp_Insert  @ItemTVP tvp_Insert READONLY
AS
BEGIN
INSERT INTO YourTable (ID, Name)
SELECT ID, Name
FROM @ItemTVP
END

C# code

 SqlCommand sqlCmd = new SqlCommand("usp_tvp_Insert", con);
 sqlCmd.CommandType = CommandType.StoredProcedure;
 SqlParameter tvpParam = sqlCmd.Parameters.AddWithValue("@ItemTVP", datatable);
 //Pass the datatable above 
 tvpParam.SqlDbType = SqlDbType.Structured; //passing TVP
 sqlCmd.ExecuteNonQuery();

Upvotes: 1

Devart
Devart

Reputation: 121902

You can generate XML from client and send it into SP -

SP:

CREATE PROCEDURE dbo.sp_test

    @XML XML

AS BEGIN

    SET NOCOUNT ON

    --INSERT INTO dbo.ScheduleDetail (ScheduleID, DateOut, WorkShiftCD, AbsenceCode)
    SELECT 
          ScheduleID = t.p.value('@ScheduleID', 'INT') 
        , DateOut = t.p.value('@DateOut', 'DATETIME') 
        , WorkShiftCD = t.p.value('@WorkShiftCD', 'VARCHAR(50)') 
        , AbsenceCode = t.p.value('@AbsenceCode', 'VARCHAR(50)') 
    FROM @XML.nodes('root/sd') t(p)

    RETURN 0

END

Exec:

DECLARE @XML XML
SELECT @XML = '
<root>
  <sd ScheduleID="11324" DateOut="2009-01-01T00:00:00" AbsenceCode="offdays" />
  <sd ScheduleID="11324" DateOut="2009-01-02T00:00:00" WorkShiftCD="1/10" />
  <sd ScheduleID="11324" DateOut="2009-01-03T00:00:00" WorkShiftCD="1/11,5" />
  <sd ScheduleID="11324" DateOut="2009-01-04T00:00:00" WorkShiftCD="1/7" />
  <sd ScheduleID="11324" DateOut="2009-01-05T00:00:00" AbsenceCode="business_trip" />
  <sd ScheduleID="11324" DateOut="2009-01-06T00:00:00" AbsenceCode="offdays" />
</root>'

EXEC dbo.sp_test @XML = @XML

Upvotes: 2

Related Questions