欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-04-16 15:45:48
...

首先在 数据 库里新建存储过程: 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 
public class lcw_test
    {
        public Guid ID { get; set; }
        public string Name { get; set; }
        public string Msg { get; set; }
        public DateTime  Dt { get; set; }
    }