读取接口XML和批量导入数据SqlBulkCopy

首先是C#处理xml文档

string urlStr = string.Format("http://……？timeBeg={0}&timeEnd={1}", timeBeg, timeEnd);
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(urlStr);//加载xml

工作中遇到的xml文档在根元素的第一个节点（state）都会返回此次接口是否返回正常，所以首先要获取xml根元素

XmlElement root = xmlDoc.DocumentElement;//获取xml根元素
int state = Convert.ToInt32(root.FirstChild.InnerText);

判断state值

获取返回xml中需要的XMLNodeList

XmlNodeList xnlchannel= xmlDoc.SelectNodes("root/data/channel");

然后准备datatable,处理datatable中字段的字段名和数据类型

DataTable dt = new DataTable();
dt.Columns.Add("DataDate", typeof(DateTime));
dt.Columns.Add("ProductType", typeof(string));

 DataRow dr = null;

由xnlchannel只是返回所有频道，每个人又有自己的信息

foreach (XmlNode node in xnlchannel)
                {
                    string date = node.SelectSingleNode("date").InnerText;
                    XmlNodeList xnlchanneldata = node.SelectNodes("channeldata");
                    for (int i = 0; i < xnlchanneldata.Count; i++)
                    {
                        string producttype = xnlchanneldata[i].SelectSingleNode("chnneltype").InnerText;
                        XmlNodeList basicnode = xnlchanneldata[i].SelectNodes("detail/nodedata");
                        foreach (XmlNode nd in basicnode)
                        {
                            dr = dt.NewRow();
                            dr[0] = Convert.ToDateTime(date);
                            dr[1] = producttype;
                            dr[2] = nd.SelectSingleNode("nodenme").InnerText;
                            ……
                            dr[10] = DateTime.Now;
                            dt.Rows.Add(dr);
                        }
                    }
                }    

View Code

数据放入datatable之后，就要批量导入了

  public static bool InsertLogUser(DataTable dt)
        {
            if (dt != null && dt.Rows.Count > 0)
            {
                dt.TableName = "Tj_PageCount";//数据库中的表名
                SQLHelper DB = new SQLHelper(Config.WebMonitorWrite);
                DB.Open();
                List<SqlBulkCopyColumnMapping> ColumnMappings = new List<SqlBulkCopyColumnMapping>() { 
                new SqlBulkCopyColumnMapping("DataDate","DataDate"),
                new SqlBulkCopyColumnMapping("ProductType","ProductType"),
               ……
                new SqlBulkCopyColumnMapping("UpdateTime","UpdateTime")
                
                };

                bool a = DB.SqlBulkCopy(dt, ColumnMappings);
                return a;
            }
            return true;
        }

View Code

 public bool SqlBulkCopy(DataTable dt, List<SqlBulkCopyColumnMapping> ColumnMappings)
        {
            SqlBulkCopy sqlbulk = new SqlBulkCopy(this.MyConnStr, SqlBulkCopyOptions.UseInternalTransaction);
            try
            {
                sqlbulk.NotifyAfter = dt.Rows.Count;
                sqlbulk.BulkCopyTimeout = 1200;
                //目标数据库表名
                sqlbulk.DestinationTableName = dt.TableName;
                //数据集字段索引与数据库字段索引映射
                
                foreach (SqlBulkCopyColumnMapping item in ColumnMappings)
                {
                    sqlbulk.ColumnMappings.Add(item);
                }

                //导入
                sqlbulk.WriteToServer(dt);
                sqlbulk.Close();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                sqlbulk.Close();
                dt.Dispose();
                this.Close();
            }
        }

View Code

源代码在百度云

 

转载于:https://www.cnblogs.com/lb12081116/p/4386318.html