利用ExecuteMultipleRequest来批量导入数据,成功的成功失败的失败,并生成导入结果文件-综合-其它-我要学365体育投注人人小站_365体育投注打不开_皇冠365体育投注网址_我要学365体育投注人人小站_365体育投注打不开_皇冠365体育投注网址是一个面向开发者的知识分享网站 365体育投注人人小站_365体育投注打不开_皇冠365体育投注网址
我的位置:首页 > 综合>其它

利用ExecuteMultipleRequest来批量导入数据,成功的成功失败的失败,并生成导入结果文件

时间:2019-11-09 13:38:00 来源:互联网 作者: 神秘的大神 字体:

我是微软Dynamics 365 & Power Platform方面的工程师罗勇,也是2015年7月到2018年6月连续三年Dynamics CRM/Business Solutions方面的微软最有价值专家(Microsoft MVP),欢迎关注我的微信公众号 MSFTDynamics365erLuoYong ,回复377或者20191109可方便获取本文,同时可以在第一间得到我发布的最新博文信息,follow me!

我前面的博文 Dynamics 365 Customer Engagement的标准导入不支持并行导入了吗? 提供了多进程使用ExecuteMultipleRequest导入数据的程序,速度虽然挺快的,但是有一个问题,导入失败的并不知道是哪些,没有办法统计,这里我改善一下。

关于ExecuteMultipleRequest,请参考官方文档,Use ExecuteMultiple to improve performance for bulk data load ,官方代码示例请参考 ExecuteMultipleRequest Class ,本博文参考了Dynamics CRM ExecuteMultipleResponse – Analysing the Results

要做到成功的成功,失败的失败,记得要将 ExecuteMultipleSettings 的 ContinueOnError 设置为 true,要记录失败的原因记得要将 ExecuteMultipleSettings 的 ReturnResponses 设置为 true。

直接上代码:

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Messages;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.ServiceModel;
using System.Threading;

namespace BulkImportRecords
{
    class Program
    {
        public static IServiceManagement sm;
        public static AuthenticationCredentials authCredentials;
        static int importsequencenumberstartat = Convert.ToInt32(ConfigurationManager.AppSettings["importsequencenumberstartat"]);
        static int threadcount = Convert.ToInt32(ConfigurationManager.AppSettings["threadcount"]);
        static string resultFile = ConfigurationManager.AppSettings["resultFile"];
        static void Main(string[] args)
        {
            sm = ServiceConfigurationFactory.CreateManagement(new Uri(ConfigurationManager.AppSettings["orgUrl"]));
            authCredentials = new AuthenticationCredentials();
            authCredentials.ClientCredentials.UserName.UserName = ConfigurationManager.AppSettings["userName"];
            authCredentials.ClientCredentials.UserName.Password = ConfigurationManager.AppSettings["passWord"];
            authCredentials = sm.Authenticate(authCredentials);
            try
            {
                for (var i = 0; i < threadcount; i++)
                {
                    Thread newThread = new Thread(new ParameterizedThreadStart(Work));
                    newThread.Start(i);
                }
                Console.ReadKey();
            }
            catch (FaultException ex)
            {
                Console.WriteLine("程序出现异常:ex.Message=" + ex.Message);
                Console.ReadKey();
            }
        }

        static void Work(object data)
        {
            ExecuteMultipleResponse multiRep;
            List<string> content = new List<string>();
            try
            {
                Console.WriteLine("线程开始" + DateTime.Now.ToLongTimeString() + ";线程ID:" + Thread.CurrentThread.ManagedThreadId + ";接收的参数值为:" + data.ToString());
                int importsequencenumber = importsequencenumberstartat + Convert.ToInt32(data);
                OrganizationServiceProxy orgSvc = new OrganizationServiceProxy(sm, authCredentials.ClientCredentials);
                string strReadFilePath = ConfigurationManager.AppSettings["filename"];
                int i = 0;
                int j = 1;
                int z = 0;
                ExecuteMultipleRequest multiReqs = new ExecuteMultipleRequest()
                {
                    Settings = new ExecuteMultipleSettings()
                    {
                        ContinueOnError = true,
                        ReturnResponses = true
                    },
                    Requests = new OrganizationRequestCollection()
                };
                using (StreamReader srReadFile = new StreamReader(string.Format(strReadFilePath, (Convert.ToInt32(data) + 1).ToString("00"))))
                {
                    while (!srReadFile.EndOfStream)
                    {
                        string strReadLine = srReadFile.ReadLine(); //读取每行数据
                        if (i != 0)//如果第一行包括标题的话要过滤掉
                        {
                            content.Add(strReadLine);
                            var arrLine = strReadLine.Split(',');
                            CreateRequest req = new CreateRequest();
                            var createEntity = new Entity("ly_test");
                            createEntity["ly_name"] = arrLine[0];
                            createEntity["ly_singletext1"] = arrLine[1];
                            createEntity["ly_singletext2"] = arrLine[2];
                            createEntity["ly_singletext3"] = arrLine[3];
                            createEntity["importsequencenumber"] = Convert.ToInt32(importsequencenumber);
                            req.Target = createEntity;
                            if (j <= 1000)
                            {
                                multiReqs.Requests.Add(req);
                            }
                            else
                            {
                                multiReqs.Requests = new OrganizationRequestCollection();
                                multiReqs.Requests.Add(req);
                                j = 1;
                            }
                            if (j == 1000)
                            {
                                multiRep = (ExecuteMultipleResponse)orgSvc.Execute(multiReqs);
                                foreach(var Rep in multiRep.Responses)
                                {
                                    if(Rep.Fault != null)
                                    {
                                        File.AppendAllText(string.Format(resultFile, (Convert.ToInt32(data) + 1).ToString("00")), $"{content[z*1000+Rep.RequestIndex+1]},FAIL,{Rep.Fault.Message}" + Environment.NewLine);
                                    }
                                    else
                                    {
                                        File.AppendAllText(string.Format(resultFile, (Convert.ToInt32(data) + 1).ToString("00")), $"{content[z * 1000 + Rep.RequestIndex+1]},OK,{((CreateResponse)Rep.Response).id}" + Environment.NewLine);
                                    }
                                }
                                z++;
                                Console.WriteLine("线程:" + Thread.CurrentThread.ManagedThreadId + "-导入完毕" + z*1000 + "" + DateTime.Now.ToString());
                            }
                            j++;
                        }
                        else
                        {
                            content.Add($"{strReadLine},结果,消息");
                            File.AppendAllText(string.Format(resultFile, (Convert.ToInt32(data) + 1).ToString("00")), content[0] + Environment.NewLine);
                        }
                        i++;
                    }
                }
                multiRep = (ExecuteMultipleResponse)orgSvc.Execute(multiReqs);
                foreach (var Rep in multiRep.Responses)
                {
                    if (Rep.Fault != null)
                    {
                        File.AppendAllText(string.Format(resultFile, (Convert.ToInt32(data) + 1).ToString("00")), $"{content[z * 1000 + Rep.RequestIndex+1]},FAIL,{Rep.Fault.Message}" + Environment.NewLine);
                    }
                    else
                    {
                        File.AppendAllText(string.Format(resultFile, (Convert.ToInt32(data) + 1).ToString("00")), $"{content[z * 1000 + Rep.RequestIndex+1]},OK,{((CreateResponse)Rep.Response).id}" + Environment.NewLine);
                    }
                }
                Console.WriteLine("线程结束" + DateTime.Now.ToLongTimeString() + ";线程ID:" + Thread.CurrentThread.ManagedThreadId);
            }
            catch(FaultException ex)
            {
                Console.WriteLine("执行遇到异常:" + ex.Detail.ErrorCode + ex.Message + ex.StackTrace);
            }
            catch (Exception e)
            {
                Console.WriteLine("执行遇到异常:" + e.Message + e.StackTrace);
            }
        }
    }
}

 

配套的app.config内容如下:

xml version="1.0" encoding="utf-8"?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.2"/>
  startup>
  <appSettings>
    <add key="userName" value="crmadmin@luoyong.me"/>
    <add key="passWord" value="Password"/>
    <add key="orgUrl" value="https://demo.luoyong.me/XRMServices/2011/Organization.svc"/>
    <add key="filename" value="D:\dataimport\data{0}.csv"/>
    <add key="importsequencenumberstartat" value="1000000"/>
    <add key="threadcount" value="2"/>
    <add key="resultFile" value="D:\ImportResult{0}.csv"/>
  appSettings>
configuration>

 

为了看到测试效果,我对要导入的实体新建了一个实时工作流如下,如果测试实体的名称字段包括5或者6就以【已取消】的状态停止工作流,并设置好错误信息。

 

 

然后我就执行后看到生成的执行结果CSV文件如下,如果CSV文件用Excel打开中文是乱码,就将CSV文件以 UTF-8 with BOM 编码格式另存为一下就可以了。

可以看到有成功,有失败的,失败的告知的原因也是正确的,插入记录成功的记录了插入记录后该记录的主键ID。