C# (CSharp) YaminabeExtensions.Sql BulkCopyDataReader.GetPrimaryColumns示例

示例#1

        /// <summary>
        /// 宛先テーブルに対して一括でデータ更新を行います。
        /// </summary>
        /// <typeparam name="T">モデルの型。</typeparam>
        /// <param name="connection">コネクション。</param>
        /// <param name="destinationTableName">宛先テーブル名。</param>
        /// <param name="dataReader">データリーダ。</param>
        /// <param name="externalTransaction">トランザクション。</param>
        public static void BulkUpdate <T>(
            this SqlConnection connection,
            string destinationTableName,
            BulkCopyDataReader <T> dataReader,
            SqlTransaction externalTransaction
            )
        {
            // 一時テーブル作成
            var temporaryTableName = $"#_tmp{Guid.NewGuid().ToString("N")}";

            CreateTemporaryTable <T>(
                connection,
                temporaryTableName,
                externalTransaction
                );

            using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, externalTransaction))
            {
                // マッピング
                dataReader.SetColumnMappings(sqlBulkCopy.ColumnMappings);
                // 一括登録実行
                sqlBulkCopy.DestinationTableName = temporaryTableName;
                sqlBulkCopy.WriteToServer(dataReader);
            }

            // 更新コマンド生成
            // モデルに設定された主キーを元にJOINを生成
            // 生成されるSQLの例）
            //
            // UPDATE [T1]
            // SET
            //     [T1].[更新対象1] = [T2].[更新対象1] , [T1].[更新対象2] = [T2].[更新対象2] …
            // FROM [宛先テーブル名] AS [T1]
            //     INNER JOIN [一時テーブル名] AS [T2]
            //         ON [T1].[主キー1] = [T2].[主キー2] AND [T1].[主キー2] = [T2].[主キー2] …
            using (var updateCommand = new SqlCommand($@"
UPDATE [T1]
SET
    {dataReader.GetUpdateColumns().Select(column => $"[T1].[{column}] = [T2].[{column}]").Aggregate((a, b) => $"{a} , {b}")}
FROM [{destinationTableName}] AS [T1]
    INNER JOIN [{temporaryTableName}] AS [T2]
        ON {dataReader.GetPrimaryColumns().Select(column => $"[T1].[{column}] = [T2].[{column}]").Aggregate((a, b) => $"{a} AND {b}")}
", connection, externalTransaction))
            {
                updateCommand.ExecuteNonQuery();
            }

            // 一時テーブルは削除
            using (var dropCommand = new SqlCommand($"DROP TABLE [{temporaryTableName}]", connection, externalTransaction))
            {
                dropCommand.ExecuteNonQuery();
            }
        }

示例#2

        /// <summary>
        /// 宛先テーブルに対して一括でデータ登録・更新・削除を行います。
        /// </summary>
        /// <typeparam name="T">モデルの型。</typeparam>
        /// <param name="connection">コネクション。</param>
        /// <param name="destinationTableName">宛先テーブル名。</param>
        /// <param name="dataReader">データリーダ。</param>
        /// <param name="externalTransaction">トランザクション。</param>
        /// <param name="notMatchedDelete">宛先テーブルのみに存在するデータの削除要否。<c>true</c> の場合は削除を行う。 <c>false</c>の場合は削除を行わない。</param>
        public static void BulkMerge <T>(
            this SqlConnection connection,
            string destinationTableName,
            BulkCopyDataReader <T> dataReader,
            SqlTransaction externalTransaction,
            bool notMatchedDelete = true
            )
        {
            // 一時テーブル作成
            var temporaryTableName = $"#_tmp{Guid.NewGuid().ToString("N")}";

            CreateTemporaryTable <T>(
                connection,
                temporaryTableName,
                externalTransaction
                );

            using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, externalTransaction))
            {
                // マッピング
                dataReader.SetColumnMappings(sqlBulkCopy.ColumnMappings);
                // 一括登録実行
                sqlBulkCopy.DestinationTableName = temporaryTableName;
                sqlBulkCopy.WriteToServer(dataReader);
            }

            // 更新コマンド生成
            // モデルに設定された主キーを元にJOINを生成
            // 生成されるSQLの例）
            //
            // MERGE [宛先テーブル名] AS [T1]
            // USING [一時テーブル名] AS [T2]
            //     ON [T1].[主キー1] = [T2].[主キー2] AND [T1].[主キー2] = [T2].[主キー2] …
            // WHEN MATCHED
            //     THEN UPDATE
            //         SET
            //             [T1].[更新対象1] = [T2].[更新対象1] , [T1].[更新対象2] = [T2].[更新対象2] …
            // WHEN NOT MATCHED BY TARGET
            //     THEN INSERT ([項目1] , [項目2] …)
            //         VALUES ([T2].[項目1], [T2].[項目2] …)
            // WHEN NOT MATCHED BY SOURCE
            //     DELETE
            using (var mergeCommand = new SqlCommand($@"
MERGE[{ destinationTableName }] AS[T1]
USING[{ temporaryTableName}] AS[T2]
    ON { dataReader.GetPrimaryColumns().Select(column => $"[T1].[{column}] = [T2].[{column}]").Aggregate((a, b) => $"{a} AND {b}")}
            WHEN MATCHED
    THEN UPDATE
        SET
            { dataReader.GetUpdateColumns().Select(column => $"[T1].[{column}] = [T2].[{column}]").Aggregate((a, b) => $"{a} , {b}")}
            WHEN NOT MATCHED BY TARGET
                THEN INSERT({ dataReader.GetColumns().Select(column => $"[{column}]").Aggregate((a, b) => $"{a} , {b}")})
                    VALUES({ dataReader.GetColumns().Select(column => $"[T2].[{column}]").Aggregate((a, b) => $"{a} , {b}")})
{ (notMatchedDelete ? $@"
            WHEN NOT MATCHED BY SOURCE
                THEN DELETE
" : string.Empty)}
;
", connection, externalTransaction))
            {
                mergeCommand.ExecuteNonQuery();
            }

            // 一時テーブルは削除
            using (var dropCommand = new SqlCommand($"DROP TABLE [{temporaryTableName}]", connection, externalTransaction))
            {
                dropCommand.ExecuteNonQuery();
            }
        }