ASP.NET Core Web APIとストアドプロシージャで実装するシンプルで高速なDBトランザクション処理（on SQLServer）

.Net 6.0 の ASP.NET Core Web APIから、Dapper+Microsoft.Data.SqlClient NuGetパッケージを使い、SQLServerデータベースのストアドプロシージャ（Transact-SQL）を使用する場合の、DBトランザクション処理を行うサンプルを作成しました。
ソースコードはGitHubで公開しています。

Youtube動画だと、DBトランザクション継続中に SQLServer Management Studio側からの接続に対してレスポンスが返って来ないのは、SQLServer側でロックが発生している為です。

ソースコード構成

ASP.NET Core Web APIで実装するシンプルで高速なDBトランザクション処理（on SQLServer）で作成したソースコードに、下記の修正を加えました。

ソースコード変更内容を解説

/SQL/SQL_TableA.cs

C#側に定数として持っていた、Select文、Insert文をストアドプロシージャ側へ移したので、C#側のSQL文は削除し、Dapperにはストアドプロシージャ名を渡すように変更。


using System.Data;
using WebApplication1.Model;
using WebApplication1.Shared;
using Dapper;
using Microsoft.Extensions.Logging;
using Microsoft.Data.SqlClient;

namespace WebApplication1.SQL
{
    public class SQL_TableA
    {
        public static void Transaction1(TableA insertTableA)
        {
            using (var conn = new SqlConnection(SharedData.ConnectionString))
            {
                conn.Open();

                using (var tran = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    try
                    {
                        var tableAList = Select(conn, tran);

                        Insert(conn, tran, insertTableA);

                        var tableAList2 = Select(conn, tran);

                        tran.Commit();
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        throw;
                    }
                }
            }
        }

        public static IEnumerable<TableA> Select(SqlConnection conn, SqlTransaction tran)
        {
            return conn.Query<TableA>("spTableA_Select", null, tran, commandType: CommandType.StoredProcedure);
        }

        public static void Insert(SqlConnection conn, SqlTransaction tran, TableA insertTableA)
        {
            var sqlParam = new
            {
                id = insertTableA.Id,
                code = insertTableA.Code,
                valueString = insertTableA.ValueString,
                valueDate = insertTableA.ValueDate
            };

            conn.Execute("spTableA_Insert", sqlParam, tran, commandType: CommandType.StoredProcedure);
        }
    }

}

using System.Data;

using WebApplication1.Model;

using WebApplication1.Shared;

using Dapper;

using Microsoft.Extensions.Logging;

using Microsoft.Data.SqlClient;

namespace WebApplication1.SQL

{

public class SQL_TableA

{

public static void Transaction1(TableA insertTableA)

{

using (var conn = new SqlConnection(SharedData.ConnectionString))

{

conn.Open();

using (var tran = conn.BeginTransaction(IsolationLevel.ReadCommitted))

{

try

{

var tableAList = Select(conn, tran);

Insert(conn, tran, insertTableA);

var tableAList2 = Select(conn, tran);

tran.Commit();

}

catch (Exception ex)

{

tran.Rollback();

throw;

}

public static IEnumerable<TableA> Select(SqlConnection conn, SqlTransaction tran)

{

return conn.Query<TableA>("spTableA_Select", null, tran, commandType: CommandType.StoredProcedure);

}

public static void Insert(SqlConnection conn, SqlTransaction tran, TableA insertTableA)

{

var sqlParam = new

{

id = insertTableA.Id,

code = insertTableA.Code,

valueString = insertTableA.ValueString,

valueDate = insertTableA.ValueDate

};

conn.Execute("spTableA_Insert", sqlParam, tran, commandType: CommandType.StoredProcedure);

}

ストアドプロシージャ定義

C#から呼び出しているストアドプロシージャの実装内容は、C#側に定数として持っていた、Select文、Insert文をそのままストアドプロシージャ（Transact-SQL）へ移しています。
※SQLをストアドプロシージャ側に纏めると、C#側のソースコードはシンプルになり、SQL実行時にSQLServerがSQLの実行プランを毎回解析する必要はなくなり、処理コストを少なくできます。
ストアドプロシージャをの作り方は SQL Serverのストアドプロシージャを SQL Server Management Studioで作成する手順を参照下さい。


USE [TestDb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spTableA_Select]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT
		[Id]
		,[ValueString]
		,[Code]
		,[ValueDate]
	FROM
		[dbo].[TableA]

END
GO

USE [TestDb]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[spTableA_Select]

BEGIN

SET NOCOUNT ON;

SELECT

[Id]

,[ValueString]

,[Code]

,[ValueDate]

FROM

[dbo].[TableA]

END


USE [TestDb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spTableA_Insert]
	@Id				bigint,
	@ValueString	[nvarchar](50),
	@Code			[nvarchar](50),
	@ValueDate		datetime
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO [dbo].[TableA] (
		[Id],
		[ValueString],
		[Code],
		[ValueDate]
    ) VALUES (
        @Id,
        @ValueString,
        @Code,
        @ValueDate
	)

END
GO

USE [TestDb]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[spTableA_Insert]

@Id bigint,

@ValueString [nvarchar](50),

@Code [nvarchar](50),

@ValueDate datetime

BEGIN

SET NOCOUNT ON;

INSERT INTO [dbo].[TableA] (

[Id],

[ValueString],

[Code],

[ValueDate]

) VALUES (

@Id,

@ValueString,

@Code,

@ValueDate

)

END