利用ExecuteMultipleRequest来批量导入数据,成功的成功失败的失败,并生成导入结果文件
我是微软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 ,本博文参考了Magnetism 的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<IOrganizationService> 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<IOrganizationService>(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<Microsoft.Xrm.Sdk.OrganizationServiceFault> 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。