Null許容のbit型のカラムが存在しない場合のデータ競合エラーの原因と解消方法

Accessの単票画面+AccessからSQL Serverへのリンクテーブルの構成で、
マスタメンテナンス画面を作成していたのですが、とあるテーブルの単票画面から更新しようとすると、以下のデータ競合エラーがでて全くデータ更新できない状態となりました。


f:id:katharsis1721:20200417004850p:plain


エラーメッセージから検索すると、以下のURLがヒットするのですが、
いずれのURLにもテーブルのカラムにNull許容のbit型が存在することをが原因と記載されています。

asbepartners.com

social.msdn.microsoft.com

ところが、このエラーがでるテーブルにNull許容のbit型のカラムが存在しないため、
もう少し調べてみると、以下のURLがヒットし、こちらが原因でした。

osada113.blog94.fc2.com

よくテーブルのレコード作成日時やレコード更新日時のカラムでミリ秒まで保持しているケースがありますが、datetime型がミリ秒まで保持しているカラムが存在する場合でも、必ずこのデータ競合エラーが発生します。
以下のSQLでミリ秒を切捨てするデータ更新することで、このエラーは解消されました。

update [dbo].[M_Table]
set [CreateDatetime] = CONVERT(DATETIME,CONVERT(VARCHAR(24),[CreateDatetime],20)),
[UpdateDatetime] = CONVERT(DATETIME,CONVERT(VARCHAR(24),[UpdateDatetime],20))


Accessの日付/時刻型は、秒単位までしか扱えず、ミリ秒まで保持できないようです。

hatenachips.blog.fc2.com

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認証に変更しただけだと、以下のエラーがでる場合があります。

stackoverflow.com

このエラーが出る場合、以下の対応によりエラーが解消され、Windows認証によるDB接続が可能となります。

IISのアプリケーションプールのIDを既定のビルトインアカウント「ApplicationPoolIdentity」から、ビルトインアカウント「NetworkService」へ変更する。
② SSMS>セキュリティ>ログインから「NT AUTHORITY\NETWORK SERVICE」を追加して、サーバーロール「sysadmin」か、データベースロール「db_owner」を付ける。


もしくは「NetworkService」でなく、任意の管理用ドメインユーザーでも良いです。
IISのアプリケーションプールのIDを既定のビルトインアカウント「ApplicationPoolIdentity」から、カスタムアカウント「管理用ドメインユーザー」へ変更する。
② SSMS>セキュリティ >ログインから「管理用ドメインユーザー」を追加して、サーバーロール「sysadmin」かデータベースロール「db_owner」を付ける。


f:id:katharsis1721:20200308195027p:plain

f:id:katharsis1721:20200308195525p:plain

これにより以下のようにWindows認証により、Passwordを書かずにConnnectionStringを記述できるようになります。

SQL Server認証

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

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

実行結果

f:id:katharsis1721:20190117233530p:plain

マッピングクラスを使わないCsvHelperによるCSV取り込み方法

以下のサイトにあるように
CsvHelperによるCSV取り込みには、マッピングクラスを使わない方法もありますが、
CsvHelperのクラス構成がv7.0.0以降大きく変わったのに伴い、変わっていたのでメモしておきます。

kageura.hatenadiary.jp

参考サイト
github.com

ClosedXMLを使ってExcelファイルからデータを読み取る方法

CSVファイルやテキストファイルがデータを読み取る場合は、File.ReadAllLinesメソッドなどを使用して簡単に読み取りができます。

CSVファイルやテキストファイルからだけでなく、Excelファイルからデータを読み取り、
Dynamics CRMに何らかのデータ処理を行いたい要件も比較的多いと思いますが、
ExcelファイルについてもClosedXMLライブラリを使うことで簡単に読み取りができるようになります。
他にもNPOI やEPPlusといったオープンソースライブラリがよく使用されています。

読み取りしたExcelファイルのサンプルデータ
f:id:katharsis1721:20180409225846p:plain

この例ではエンティティの作成をしていますが、もちろんデータ処理を行うこともできます。
読み取ったデータごとにエンティティの作成、更新、削除をそれぞれ分けたい場合は、フラグ用のカラム(Create,Update,Delete)を追加して処理を分けるしかないかと思います。

また組織サービス(OrganizationService)によるDynamics CRMへの接続は、CrmConnectionクラスを使用するより、以下のようにapp.configに設定したconnectionStringをパラメーターとしてそのまま渡すほうがより簡単に接続ができます。

参考サイト
qiita.com

Microsoft Dynamics CRM 2015 へのより簡単な接続

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