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
