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