Reminder: Bulk insert through a store procedure

Ok, my problem was I had a large collection of item; where each item is suppose to be inserted in a database and I didn't want to do a bulkcopy.

This is accomplished by using XML in the store procedure and sending it in through the ADO.Net

In the C# code:

//Make xml
using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["connectionString"]))
{
   SqlCommand cmd = new SqlCommand("insBulkFTSEDCFileDetail", conn);
   cmd.CommandType = CommandType.StoredProcedure;
   StringBuilder sb = new StringBuilder();
   sb.Append("\r\n");
   foreach (oEDCItem item in edc)
   {
      sb.AppendFormat("\r\n",
      item.ModuleName, item.PartNumber, item.SerialNumber, item.TestPosition, item.SupplierName, item.EDC, item.Opt);
}
   sb.Append("");

   cmd.Parameters.AddRange(new SqlParameter[] {
   new SqlParameter("@EDCFileID", EDCFileID),
   new SqlParameter("@LastUpdateUserID", UserID),
   new SqlParameter("@XMLDOC", sb.ToString())});

   conn.Open();
   cmd.ExecuteNonQuery();
   conn.Close();
}



The stored procedure:

Create Procedure insBulkFTSEDCFileDetail
{
   @EDCFileID int,
   @LastUpdateUserID int,
   @XMLDOC varchar(MAX)
}
AS

DECLARE @xml_handle int

EXEC sp_XML_preparedocument @xml_handle OUTPUT, @XMLDOC

INSERT INTO [TABLE]
(EDCFileID,
ItemType,
Model,
SerialNumber,
EDCPosition,
SupplierName,
EDC,
Options,
LastUpdateDate,
LastUpdateUserID)

SELECT @EDCFileID,
EDCXML.ItemType,
EDCXML.Model,
EDCXML.SerialNumber,
EDCXML.EDCPosition,
EDCXML.SupplierName,
EDCXML.EDC,
EDCXML.Options,
getUTCDate(),
@LastUpdateUserID
FROM OPENXML( @xml_handle, '/EDCItems/EDCItem')
WITH ( ItemType varchar(8),
Model varchar(50),
SerialNumber varchar(50),
EDCPosition varchar(50),
SupplierName varchar(50),
EDC varchar(50),
Options varchar(80)) AS EDCXML

EXEC sp_XML_removedocument @xml_handle