我是微軟Dynamics 365 & Power Platform方面的工程師/顧問羅勇，也是2015年7月到2018年6月連續三年Dynamics CRM/Business Solutions方面的微軟最有價值專家(Microsoft MVP)，歡迎關注我的微信公衆号 MSFTDynamics365erLuoYong ，回複459或者20211112可友善擷取本文，同時可以在第一間得到我釋出的最新博文資訊，follow me!
這個是個簡單的控制台程式讀取類似如下内容的Excel内容，對使用者執行增加/移除角色操作并生成結果檔案。
直接上代碼如下，添加/移除使用者或者團隊角色的方法可以參考我的博文：常見的通過Web API執行associate和disassociate消息示例 和 Dynamics 365中的常用Associate和Disassociate消息彙總 。
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Tooling.Connector;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
namespace ModifyUserRoles
{
class Program
{
static void Main(string[] args)
{
Console.OutputEncoding = Encoding.GetEncoding("gb2312");
Console.WriteLine("本程式用于增加/移除使用者角色，開始連接配接Dynamics 365!");
try
{
var sourceFile = ConfigurationManager.AppSettings["sourceFile"];
var crmClientUrl = ConfigurationManager.AppSettings["CRMClientUrl"];
int colCount = Convert.ToInt32(ConfigurationManager.AppSettings["colCount"]);
int rowCount = Convert.ToInt32(ConfigurationManager.AppSettings["rowCount"]);
CrmServiceClient crmServiceClient = new CrmServiceClient(ConfigurationManager.AppSettings["connectStr"]);
if (crmServiceClient.IsReady)
{
Console.WriteLine($"連接配接到Dynamics 365環境 ({crmServiceClient.ConnectedOrgFriendlyName}) 成功，若要開始執行程式，請輸入Y并回車确認!");
var inputValue = Console.ReadLine();
if (inputValue.Equals("Y", StringComparison.OrdinalIgnoreCase))
{
List<UserModel> lsUsers = new List<UserModel>();
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(sourceFile);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
xlRange.Cells[1, colCount + 1] = "狀态";
xlRange.Cells[1, colCount + 2] = "處理消息";
xlRange.Cells[1, colCount + 3] = "檢視使用者詳情";
UserModel user;
UserModel tempUserModel;
try
{
for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++)
{
Console.WriteLine($"讀取第{rowIndex}行記錄!");
user = new UserModel();
user.errorMessages = new List<string>();
user.warnMessages = new List<string>();
user.userId = Guid.Empty;
for (int colIndex = colCount + 2; colIndex >= 1; colIndex--)
{
user.rowIndex = rowIndex;
if (colIndex == colCount + 1)
{
user.status = GetCellValue(xlRange, rowIndex, colIndex);
}
if (user.status != Enums.HandleStatus.Handled_Successfully.ToString().Replace('_', ' '))
{
switch (colIndex)
{
case 1:
user.userName = GetCellValue(xlRange, rowIndex, colIndex);
if (string.IsNullOrEmpty(user.userName))
{
user.errorMessages.Add("使用者名或者使用者主要郵箱不能為空!");
}
else
{
tempUserModel = GetUser(crmServiceClient, user.userName);
if (tempUserModel == null || tempUserModel.userId == Guid.Empty)
{
user.errorMessages.Add("找不到有效的使用者!");
}
else
{
user.businessUnitId = tempUserModel.businessUnitId;
user.userId = tempUserModel.userId;
}
}
break;
case 2:
user.toAssociateRoles = GetCellValue(xlRange, rowIndex, colIndex);
break;
case 3:
user.toDisassociateRoles = GetCellValue(xlRange, rowIndex, colIndex);
break;
}
}
}
lsUsers.Add(user);
}
Console.WriteLine("開始處理使用者的角色添加/移除!");
var lsRoles = GetRoles(crmServiceClient);
foreach (var userInstance in lsUsers.Where(t => !t.errorMessages.Any() && t.status != Enums.HandleStatus.Handled_Successfully.ToString().Replace('_', ' ')))
{
try
{
var userRoles = GetDirectUserRoles(crmServiceClient,userInstance.userId);
if (userInstance.toAssociateRoles != null)
{
foreach (var roleName in userInstance.toAssociateRoles.Split(';'))
{
if (!userRoles.Contains(roleName))
{
var lsR = lsRoles.Where(t => t.roleName.Equals(roleName) && t.businessUnitId == userInstance.businessUnitId).ToList();
if (lsR != null && lsR.Any())
{
crmServiceClient.Associate("systemuser", userInstance.userId, new Relationship("systemuserroles_association"), new EntityReferenceCollection() { new EntityReference("role", lsR.FirstOrDefault().roleId) });
}
else
{
userInstance.warnMessages.Add($"角色{roleName}不存在,是以未授予!");
}
}
}
}
if (userInstance.toDisassociateRoles != null)
{
foreach (var roleName in userInstance.toDisassociateRoles.Split(';'))
{
if (userRoles.Contains(roleName))
{
var lsR = lsRoles.Where(t => t.roleName.Equals(roleName) && t.businessUnitId == userInstance.businessUnitId).ToList();
if (lsR != null && lsR.Any())
{
crmServiceClient.Disassociate("systemuser", userInstance.userId, new Relationship("systemuserroles_association"), new EntityReferenceCollection() { new EntityReference("role", lsR.FirstOrDefault().roleId) });
}
else
{
userInstance.warnMessages.Add($"角色{roleName}不存在,是以未移除!");
}
}
else
{
userInstance.warnMessages.Add($"使用者沒有角色{roleName},是以不需要移除!");
}
}
}
if (userInstance.userId != Guid.Empty)
{
xlWorksheet.Hyperlinks.Add(xlWorksheet.Cells[userInstance.rowIndex, colCount + 3], $"{crmClientUrl}&pagetype=entityrecord&etn=systemuser&id={userInstance.userId}", "", "", "看看使用者詳情");
}
}
catch (Exception userEx)
{
userInstance.errorMessages.Add($"異常錯誤!{userEx.Message}");
}
}
Console.WriteLine("開始将結果寫入Excel檔案!");
foreach (var userInstance in lsUsers.Where(t => t.errorMessages.Any() || t.warnMessages.Any()))
{
var messages = string.Empty;
if (userInstance.errorMessages.Any())
{
xlRange.Cells[userInstance.rowIndex, colCount + 1] = Enums.HandleStatus.Failed_to_Handle.ToString().Replace('_', ' ');
messages = string.Join(";", userInstance.errorMessages);
}
else
{
xlRange.Cells[userInstance.rowIndex, colCount + 1] = Enums.HandleStatus.Handled_Partially.ToString().Replace('_', ' ');
}
if (userInstance.warnMessages.Any())
{
if (string.IsNullOrEmpty(messages))
{
messages += string.Join(";", userInstance.warnMessages);
}
else
{
messages = $"{messages};{string.Join(";", userInstance.warnMessages)}";
}
}
xlRange.Cells[userInstance.rowIndex, colCount + 2] = messages;
if (userInstance.userId != Guid.Empty)
{
xlWorksheet.Hyperlinks.Add(xlWorksheet.Cells[userInstance.rowIndex, colCount + 3], $"{crmClientUrl}&pagetype=entityrecord&etn=systemuser&id={userInstance.userId}", "", "", "看看使用者詳情");
}
}
foreach (var userInstance in lsUsers.Where(t => !t.errorMessages.Any() && !t.warnMessages.Any()))
{
xlRange.Cells[userInstance.rowIndex, colCount + 1] = Enums.HandleStatus.Handled_Successfully.ToString().Replace('_', ' ');
}
xlWorkbook.SaveAs(sourceFile.Replace(".xlsx", "_Result.xlsx"));
}
catch (Exception exelEx)
{
Console.WriteLine($"處理Excel資料報錯.{exelEx.Message}");
}
finally
{
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
}
else
{
Console.WriteLine("你選擇了取消程式運作!");
}
}
else
{
throw new Exception($"連接配接Dynamics 365報錯.{crmServiceClient.LastCrmError}");
}
}
catch (Exception ex)
{
Console.WriteLine($"程式運作出錯：{ex.Message};{ex.StackTrace}");
}
Console.WriteLine("程式運作結束，按任意鍵退出!");
Console.ReadKey();
}
public static string GetCellValue(Excel.Range xlRange, int rowIndex, int colIndex)
{
string returnVal = string.Empty;
if (xlRange.Cells[rowIndex, colIndex] != null && xlRange.Cells[rowIndex, colIndex].Value2 != null)
{
returnVal = Convert.ToString(xlRange.Cells[rowIndex, colIndex].Value2);
returnVal = returnVal.Trim();
}
return returnVal;
}
static IEnumerable<string> GetDirectUserRoles(CrmServiceClient crmServiceClient, Guid userId)
{
string fetchXml = string.Format(@"<fetch version='1.0' mapping='logical' distinct='true' no-lock='true'>
<entity name='role'>
<attribute name='name' />
<link-entity name='systemuserroles' from='roleid' to='roleid' visible='false' intersect='true'>
<link-entity name='systemuser' from='systemuserid' to='systemuserid' alias='ad'>
<filter type='and'>
<condition attribute='systemuserid' operator='eq' value='{0}' />
</filter>
</link-entity>
</link-entity>
</entity>
</fetch>", userId);
return crmServiceClient.RetrieveMultiple(new FetchExpression(fetchXml)).Entities.Select(x => x.GetAttributeValue<string>("name"));
}
public static UserModel GetUser(CrmServiceClient crmServiceClient, string userName)
{
UserModel returnVal = new UserModel();
var fetchXml = $@"<fetch mapping='logical' distinct='false' no-lock='true'>
<entity name='systemuser'>
<attribute name='fullname' />
<attribute name='businessunitid' />
<filter type='and'>
<filter type='or'>
<condition attribute='domainname' operator='eq' value='{userName}' />
<condition attribute='internalemailaddress' operator='eq' value='{userName}' />
</filter>
<condition attribute='isdisabled' operator='eq' value='0' />
</filter>
</entity>
</fetch>";
var userEC = crmServiceClient.RetrieveMultiple(new FetchExpression(fetchXml));
if (userEC.Entities.Any())
{
returnVal.userId = userEC.Entities[0].Id;
returnVal.businessUnitId = userEC.Entities[0].GetAttributeValue<EntityReference>("businessunitid").Id;
}
return returnVal;
}
public static List<RoleModel> GetRoles(CrmServiceClient crmServiceClient)
{
List<RoleModel> returnVal = new List<RoleModel>();
string fetchXml = @"<fetch no-lock='true' mapping='logical' distinct='false'>
<entity name='role'>
<attribute name='name' />
<attribute name='businessunitid' />
<attribute name='roleid' />
<order attribute='name' descending='false' />
</entity>
</fetch>";
var roleEC = RetrieveAllRecordsByFetchXml(fetchXml, crmServiceClient);
foreach(var item in roleEC.Entities)
{
returnVal.Add(new RoleModel()
{
businessUnitId = item.GetAttributeValue<EntityReference>("businessunitid").Id,
roleId = item.Id,
roleName = item.GetAttributeValue<string>("name")
}); ;
}
return returnVal;
}
public static string CreateXml(string xml, string cookie, int page, int count)
{
XDocument doc = XDocument.Parse(xml);
if (!string.IsNullOrEmpty(cookie))
{
doc.Root.Add(new XAttribute("paging-cookie", cookie));
}
doc.Root.Add(new XAttribute("page", page));
doc.Root.Add(new XAttribute("count", count));
return doc.ToString(SaveOptions.DisableFormatting);
}
public static EntityCollection RetrieveAllRecordsByFetchXml(string fetchXml, CrmServiceClient crmServiceClient, int recordsPerPage = 5000)
{
EntityCollection EC = new EntityCollection();
int pageNumber = 1;
string pagingCookie = null;
while (true)
{
string xml = CreateXml(fetchXml, pagingCookie, pageNumber, recordsPerPage);
RetrieveMultipleRequest fetchRequest1 = new RetrieveMultipleRequest
{
Query = new FetchExpression(xml)
};
EntityCollection returnCollection = ((RetrieveMultipleResponse)crmServiceClient.Execute(fetchRequest1)).EntityCollection;
if (returnCollection.Entities != null && returnCollection.Entities.Any())
{
EC.Entities.AddRange(returnCollection.Entities);
}
if (returnCollection.MoreRecords)
{
pageNumber++;
pagingCookie = returnCollection.PagingCookie;
}
else
{
break;
}
}
return EC;
}
}
public class UserModel
{
public int rowIndex { get; set; }
public Guid userId { get; set; }
public Guid businessUnitId { get; set; }
public string userName { get; set; }
public string status { get; set; }
public string toAssociateRoles { get; set; }
public string toDisassociateRoles { get; set; }
public List<string> errorMessages { get; set; }
public List<string> warnMessages { get; set; }
}
public class RoleModel
{
public Guid roleId { get; set; }
public Guid businessUnitId { get; set; }
public string roleName { get; set; }
}
public static class Enums
{
public enum HandleStatus
{
Validation_Failure,
Handled_Successfully,
Handled_Partially,
Failed_to_Handle
}
}
} <appSettings>
<add key="sourceFile" value="C:\ModifyUserRoles.xlsx" />
<add key="rowCount" value="10" />
<add key="colCount" value="3" />
<add key="connectStr" value="AuthType=ClientSecret;url=https://thomasluodemo.crm5.dynamics.com/;ClientId=************;ClientSecret=*******" />
<add key="CRMClientUrl" value="https://thomasluodemo.crm5.dynamics.com/main.aspx?appid=******" />
</appSettings> ![更改Dynamics 365 Online登入頁面的風格提示背景等資訊[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)