Inserting multiple rows in single command using asp.net

Question

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.

in which from in form of table or in form of indidviduals parameters ? — Amit Singh, May 08 '13 at 05:45
http://stackoverflow.com/questions/2624713/how-do-i-insert-multiple-rows-without-repeating-the-insert-into-dbo-blah-part ? — gsharp, May 08 '13 at 06:04

score 2 · Accepted Answer · answered May 08 '13 at 05:54

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

How multiple insert query work on this. and where is insert statement in the SP. Please advice. — Saurabh, May 08 '13 at 06:03
You generate XML (by StringBuilder, etc.) in your asp.net form and after it executing SP with XML parameter. — Devart, May 08 '13 at 06:07
Please see row: INSERT INTO dbo.ScheduleDetail (ScheduleID, DateOut, WorkShiftCD, AbsenceCode) — Devart, May 08 '13 at 06:08
Try this: http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/ee16f840-c983-40f0-a0b6-e664bf4b38fe — Devart, May 08 '13 at 06:25

score 1 · Answer 2 · answered May 08 '13 at 05:58

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();

score 0 · Answer 3 · answered May 08 '13 at 07:37

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;
    }
}

Inserting multiple rows in single command using asp.net

3 Answers3