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

 

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

 

  

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<lcw_test> 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<lcw_test> TETS()
        {
            List<lcw_test> ce = new List<lcw_test>();
            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; }
    }