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) { Listce = 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; } } ListTETS() { 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; } }
上一篇: win10点设置没反应怎么办?
下一篇: JSP中隐式对象是什么?隐式对象的类型