Null許容のbit型のカラムが存在しない場合のデータ競合エラーの原因と解消方法
Accessの単票画面＋AccessからSQL Serverへのリンクテーブルの構成で、
マスタメンテナンス画面を作成していたのですが、とあるテーブルの単票画面から更新しようとすると、以下のデータ競合エラーがでて全くデータ更新できない状態となりました。
エラーメッセージから検索すると、以下のURLがヒットするのですが、
いずれのURLにもテーブルのカラムにNull許容のbit型が存在することをが原因と記載されています。
ところが、このエラーがでるテーブルにNull許容のbit型のカラムが存在しないため、
もう少し調べてみると、以下のURLがヒットし、こちらが原因でした。
よくテーブルのレコード作成日時やレコード更新日時のカラムでミリ秒まで保持しているケースがありますが、datetime型がミリ秒まで保持しているカラムが存在する場合でも、必ずこのデータ競合エラーが発生します。
以下のSQLでミリ秒を切捨てするデータ更新することで、このエラーは解消されました。
update [dbo].[M_Table] set [CreateDatetime] = CONVERT(DATETIME,CONVERT(VARCHAR(24),[CreateDatetime],20)), [UpdateDatetime] = CONVERT(DATETIME,CONVERT(VARCHAR(24),[UpdateDatetime],20))
Accessの日付/時刻型は、秒単位までしか扱えず、ミリ秒まで保持できないようです。
ConnnectionStringをSQL Server認証からWindows認証に変更する方法
ASP.NET Core 3.0 + EF Core 3.0 + IIS + SQL Server 2017の環境でWebアプリケーションを作っているのですが、そこでDB接続に使用しているConnnectionStringのPasswordを暗号化、秘匿化する方法として以下の3つが考えられます。
① Secret ManagerやAzure Key Vaultを使う。
② 暗号化、複合化クラスを作成して、ConnnectionStringを暗号化する。
③ SQL Server認証からWindows認証に変更する。（そもそもConnectionStringにPasswordを書かない）
今回、対応方法として1番手っ取り早い③について検証しました。
ただし、ConnnectionStringをSQL Server認証（saによる認証）から、Windows認証に変更しただけだと、以下のエラーがでる場合があります。
このエラーは、Windows認証の中でもNTLM認証の場合、つまりKerberos認証のように「ダブルホップ」を使用せず、IISのアプリケーションプールに指定したアカウントでDB接続する場合に発生します。
このエラーが出る場合、以下の対応によりエラーが解消され、Windows認証によるDB接続が可能となります。
① IISのアプリケーションプールのIDを既定のビルトインアカウント「ApplicationPoolIdentity」から、ビルトインアカウント「NetworkService」へ変更する。
② SSMS＞セキュリティ＞ログインから「NT AUTHORITY\NETWORK SERVICE」を追加して、サーバーロール「sysadmin」か、データベースロール「db_owner」を付ける。
もしくは「NetworkService」でなく、任意の管理用ドメインユーザーでも良いです。
① IISのアプリケーションプールのIDを既定のビルトインアカウント「ApplicationPoolIdentity」から、カスタムアカウント「管理用ドメインユーザー」へ変更する。
② SSMS＞セキュリティ ＞ログインから「管理用ドメインユーザー」を追加して、サーバーロール「sysadmin」かデータベースロール「db_owner」を付ける。
これにより以下のようにWindows認証により、Passwordを書かずにConnnectionStringを記述できるようになります。
Data Source=172.31.42.229;Initial Catalog=DB名;User ID=sa;Password=saのパスワード;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False
↓
Windows認証
Data Source=172.31.42.229;Initial Catalog=DB名;Integrated Security=True
参考サイト
idmlab.eidentity.jp
Pesterの結果からFailureのみを抽出する
PowerShellには、Pesterというユニットテスト用のフレームワークが標準で用意されています。
Pesterのテスト結果はXMLファイルで出力されるのですが、テストケースが多い場合、また多数のサーバーでテストをする場合、テスト結果の中からエラーになった結果（Failure）を捕捉しにくいという欠点がありました。
そこで、複数のテスト結果のXMLファイルの中から、エラーになった結果（Failure）のみを抽出して、Excelファイルに一覧化して出力する方法を記載します。
using ClosedXML.Excel; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Xml.Linq; using System.Xml.XPath; namespace FailureExtractFromXML { class Program { static void Main(string[] args) { string stCurrentDir = Directory.GetCurrentDirectory(); IEnumerable<string> xmlFiles = Directory.EnumerateDirectories( stCurrentDir, "*", SearchOption.AllDirectories) .SelectMany(d => Directory.EnumerateFiles(d)) .Where(f => new[] { ".xml" }.Contains(Path.GetExtension(f))); var exportFile = Path.Combine(stCurrentDir,$"UTFailureResult_{DateTime.Now:yyyyMMddHHmmss}.xlsx"); using(var workbook = new XLWorkbook()) { var worksheet = workbook.Worksheets.Add("UTFailureResult"); worksheet.Style.Font.FontName = "Meiryo UI"; worksheet.Cell("A1").Value = "nodename"; worksheet.Cell("B1").Value = "name"; worksheet.Cell("C1").Value = "description"; worksheet.Cell("D1").Value = "message"; worksheet.Cell("E1").Value = "stacktrace"; var i = 2; foreach (var xmlFile in xmlFiles) { var nodeName = Path.GetFileNameWithoutExtension(xmlFile); var xdocText = XDocument.Load(xmlFile).ToString(); var xfailures = XDocument.Parse(xdocText).XPathSelectElements($"/test-results/test-suite/results").Descendants("failure"); foreach (var xfailure in xfailures) { worksheet.Cell(i,1).Value = nodeName; worksheet.Cell(i,2).Value = xfailure.Ancestors().First().Attribute("name").Value; worksheet.Cell(i,3).Value = xfailure.Ancestors().First().Attribute("description").Value; worksheet.Cell(i,4).Value = xfailure.Element("message").Value.Replace("\n"," "); worksheet.Cell(i,5).Value = xfailure.Element("stack-trace").Value.Replace("\n"," "); i++; } } workbook.SaveAs(exportFile); } } } }
参考サイト
qiita.com
コレクションを分割してマルチスレッド処理を実行する
foreachのループ処理でコレクション内データを取得し、ループ内でシングルスレッド処理を行うことがあるかと思います。
foreach内でや大量データのバッチ処理や重い処理（APIによるCRUD処理など）を行う場合に、
予めコレクションを分割し、マルチスレッド処理にする方法を記載します。
using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace CollectionSplit { class Program { static void Main(string[] args) { //0～11000までの連番をListに追加 var allList = Enumerable.Range(0, 11000).ToList(); var splitLists = GetSplitList(allList).Select(i => i.ToList()).ToList(); List<Task> taskList = new List<Task>(); //分割したコレクションをループし、マルチスレッド処理を実行 foreach (var splitList in splitLists.Select((item, index) => new { item, index })) { taskList.Add(Task.Factory.StartNew(() => { foreach (var splitItem in splitList.item) { //実際はここで重い処理（APIによるCRUD処理など）を実行 Console.WriteLine($"index: {splitList.index}, value: {splitItem}"); } }, TaskCreationOptions.LongRunning)); } Task.WaitAll(taskList.ToArray()); Console.ReadLine(); } /// <summary> /// コレクション分割メソッド /// </summary> /// <typeparam name="T"></typeparam> /// <param name="allList"></param> /// <returns></returns> private static IEnumerable<IEnumerable<T>> GetSplitList<T>(List<T> allList) { //コレクションを分割する単位を指定（この例では16分割する） int parallelCount = 16; var cntPerTask = allList.Count < parallelCount ? allList.Count : allList.Count / parallelCount; var cntMod = allList.Count % parallelCount; int count = 0; for (int i = 0; i < parallelCount; i++) { if (i != parallelCount - 1) { yield return allList.Skip(count).Take(cntPerTask); count += cntPerTask; } else { //余りは全て最後のコレクションに追加 if (cntMod != 0) cntPerTask += cntMod; yield return allList.Skip(count).Take(cntPerTask); } } } } }
参考サイト
ufcpp.net
実行結果
マッピングクラスを使わないCsvHelperによるCSV取り込み方法
以下のサイトにあるように
CsvHelperによるCSV取り込みには、マッピングクラスを使わない方法もありますが、
CsvHelperのクラス構成がv7.0.0以降大きく変わったのに伴い、変わっていたのでメモしておきます。
参考サイト
github.com
ClosedXMLを使ってExcelファイルからデータを読み取る方法
CSVファイルやテキストファイルがデータを読み取る場合は、File.ReadAllLinesメソッドなどを使用して簡単に読み取りができます。
CSVファイルやテキストファイルからだけでなく、Excelファイルからデータを読み取り、
Dynamics CRMに何らかのデータ処理を行いたい要件も比較的多いと思いますが、
ExcelファイルについてもClosedXMLライブラリを使うことで簡単に読み取りができるようになります。
他にもNPOI やEPPlusといったオープンソースライブラリがよく使用されています。
読み取りしたExcelファイルのサンプルデータ
この例ではエンティティの作成をしていますが、もちろんデータ処理を行うこともできます。
読み取ったデータごとにエンティティの作成、更新、削除をそれぞれ分けたい場合は、フラグ用のカラム（Create,Update,Delete）を追加して処理を分けるしかないかと思います。
また組織サービス（OrganizationService）によるDynamics CRMへの接続は、CrmConnectionクラスを使用するより、以下のようにapp.configに設定したconnectionStringをパラメーターとしてそのまま渡すほうがより簡単に接続ができます。
参考サイト
qiita.com
Dynamics CRMグローバルオプションセット一覧がほしい時に実行するSQL
Dynamics CRMのカスタムグローバルオプションセットの一覧がほしい時に実行するSQLです。
SELECT PicklistValue.Label, PicklistValue.Name, --PicklistValue.OptionSetId, LLLV2.Label, PicklistValue.Value FROM LocalizedLabelLogicalView AS LLLV2 INNER JOIN ( -- カスタムグローバルオプションセットのPickListIDを表示 SELECT OSIdLabel.OptionSetId, OSIdLabel.Label, OSIdLabel.Name, APVLV.Value, APVLV.AttributePicklistValueId FROM AttributePicklistValueLogicalView AS APVLV INNER JOIN ( -- カスタムグローバルオプションセットの表示名と名前を表示 SELECT OSLV.OptionSetId, LLLV.Label, OSLV.Name FROM OptionSetLogicalView AS OSLV INNER JOIN LocalizedLabelLogicalView AS LLLV ON OSLV.OptionSetId = LLLV.ObjectId WHERE LLLV.ObjectColumnName = 'DisplayName' AND OSLV.IsGlobal = 1 AND OSLV.IsCustomOptionSet = 1 ) AS OSIdLabel ON OSIdLabel.OptionSetId = APVLV.OptionSetId ) AS PicklistValue ON LLLV2.ObjectId = PicklistValue.AttributePicklistValueId WHERE LLLV2.ObjectColumnName = 'DisplayName'
LocalizedLabelLogicalViewやMetadataSchema.LocalizedLabelには、
オプションセットのプルダウン内の各ラベルや値はもちろんですが、オプションセット自体の表示名と名前もデータが入っているんですね。
LabelTypeCode列の値が1の場合、プルダウン内の各ラベルの表示で、
LabelTypeCode列の値が10の場合、オプションセット自体の表示名を表します。
参考サイト
How to query CRM Global OptionSet Values in SQL? | Arun Potti's MS CRM blog
