C#+SqlServer2008使用XML大批量数据入库

首先在 数据 库里新建存储过程： create Procedure lcw_test--Author:lcw--Description:--DateTime:2012-10-12@xml nvarchar(max)ASbegin declare @idHandle int EXEC sp_xml_preparedocument @idHandle OUTPUT, @xml --创建xml的句柄 --print @idHandle INS

首先在数据库里新建存储过程：

create Procedure lcw_test
--Author:lcw
--Description:
--DateTime:2012-10-12
@xml nvarchar(max)
AS
begin
 declare @idHandle int
 EXEC sp_xml_preparedocument @idHandle OUTPUT, @xml --创建xml的句柄
 --print @idHandle 
 
 INSERT INTO t_test (ID,name,Msg,SaveTime)
 SELECT ID,name,Msg,SaveTime FROM OPENXML(@idHandle,N'/root/t_test') with t_test
 
 IF @@ERROR=0
 BEGIN
 SELECT 1
 END
 ELSE
 BEGIN
 SELECT 0
 END
 
 EXEC sp_xml_removedocument @idHandle --xml文档会存储在sqlserver的缓存中，为了避免内存不足，执行该语句 以释放内存。
 END

然后是C#代码：

 private void button14_Click(object sender, EventArgs e)
 {
 List ce = TETS();
 XmlDocument document = new XmlDocument();
 XmlElement root = document.CreateElement("root");
 document.AppendChild(root);
 foreach (lcw_test personEntity in ce)
 {
 XmlElement xmlPerson = document.CreateElement("t_test");
 xmlPerson.SetAttribute("ID",personEntity.ID.ToString());
 xmlPerson.SetAttribute("name", personEntity.Name);
 xmlPerson.SetAttribute("Msg", personEntity.Msg);
 xmlPerson.SetAttribute("SaveTime", personEntity.Dt.ToString()); 
 root.AppendChild(xmlPerson);
 }
 SqlParameter[] parameters = null;
 parameters = new SqlParameter[] { new SqlParameter("@xml", document.InnerXml) };
 try
 {
 using (SqlConnection conn = new SqlConnection("server=.;database=test;user=sa;pwd=lcw;"))
 {
 conn.Open();
 using (SqlCommand cmd = conn.CreateCommand())
 {
 cmd.CommandText = "lcw_test";
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.AddRange(parameters);
 int cccc = Convert.ToInt32(cmd.ExecuteNonQuery());

 }
 }
 
 }
 catch (Exception ex)
 {
 throw ex;
 }

 }
 List TETS()
 {
 List ce = new List();
 lcw_test c = null;
 for (int i = 0; i < 5000; i++)
 {
 ce.Add(c = new lcw_test { ID = Guid.NewGuid(), Name = "Name" + i, Msg = "Msg" + i, Dt = DateTime.Now });
 }
 return ce;
 }
public class lcw_test
 {
 public Guid ID { get; set; }
 public string Name { get; set; }
 public string Msg { get; set; }
 public DateTime Dt { get; set; }
 }