余小章 @ 大內殿堂

開發環境：

• Windows 10 Enterprise x64 CHT
• VS2015 Update2 Eng
• Dapper 1.42
• MiniProfiler 3.2.0.157
• MiniProfiler.Integrations 2.0.1

實作步驟：

實作 MiniProfiler.Integrations.IDbConnectionFactory

System.Data.Entity.Infrastructure 裡也有一個 IDbConnectionFactory 接口，別弄錯了。

這個接口只需要實作 CreateConnection 方法，這個方法是用 DbProviderFactories 根據組態設定的連線字串名稱建立 DbConnect 物件，程式碼如下：
 

internal class DbConnectionFactory : IDbConnectionFactory
{
    public string _connectName;

    public DbConnectionFactory(string connectName)
    {
        this._connectName = connectName;
    }

    public DbConnection CreateConnection()
    {
        var connectObject = ConfigurationManager.ConnectionStrings[this._connectName];
        var factory = DbProviderFactories.GetFactory(connectObject.ProviderName);
        var connection = factory.CreateConnection();
        connection.ConnectionString = connectObject.ConnectionString;

        return connection;
    }
}

 

調用 DbConnectionFactoryHelper：

接下來在 Client 端調用 MiniProfiler.Integrations.DbConnectionFactoryHelper，DbConnectionFactoryHelper uing 區段裡面寫下 Sql Command，

最後再調用 MiniProfiler.Integrations.CustomDbProfiler.Current.ProfilerContext.GetCommands() 列出所有執行過的 Sql Command，片段程式碼如下：
 

DbConnectionFactory factory = new DbConnectionFactory("Local_HrDbContext");
using (var connection = DbConnectionFactoryHelper.New(factory, CustomDbProfiler.Current))
{
    //TODO:working
}

Console.WriteLine(MiniProfiler.Integrations.CustomDbProfiler.Current.ProfilerContext.GetCommands());

 

執行結果如下：

Test Name:    Dapper_SQL_Transaction_Log
Test Outcome:    Passed
Result StandardOutput:    
ExecutedCommands: CommandType: Text, CommandText: DELETE FROM dbo.Department
WHERE
    Remark = @Remark

Parameters:
Name: Remark, Value: 整合測試案例

----
CommandType: Text, CommandText: INSERT INTO dbo.Department
(
    Id
    ,ChangeCode
    ,SentDatetime
    ,DepartmentId
    ,DepartmentName
    ,CreatedAccountId
    ,CreatedDate
    ,CreatedTime
    ,ModifiedAccountId
    ,ModifiedDate
    ,ModifiedTime
    ,Remark
)
VALUES
(
    DEFAULT
    ,@ChangeCode
    ,@SentDatetime
    ,@DepartmentId
    ,@DepartmentName
    ,@CreatedAccountId
    ,@CreatedDate
    ,@CreatedTime
    ,@ModifiedAccountId
    ,@ModifiedDate
    ,@ModifiedTime
    ,@Remark
)

Parameters:
Name: ChangeCode, Value: A
Name: SentDatetime, Value: 0
Name: DepartmentId, Value: TEST
Name: DepartmentName, Value: TEST
Name: CreatedAccountId, Value: TEST_USER
Name: CreatedDate, Value: 0
Name: CreatedTime, Value: 0
Name: ModifiedAccountId, Value: TEST_USER
Name: ModifiedDate, Value: 0
Name: ModifiedTime, Value: 0
Name: Remark, Value: 整合測試案例

----
CommandType: Text, CommandText: UPDATE dbo.Department
SET
    ChangeCode = @ChangeCode
WHERE
    Remark = @Remark
Parameters:
Name: ChangeCode, Value: C
Name: Remark, Value: 整合測試案例
--------------
FailedCommands:

 

完整程式碼如下：
 

[TestMethod]
public void Dapper_SQL_Transaction_Log()
{
    var deleteText = @"DELETE FROM dbo.Department
WHERE
Remark = @Remark
";

    var insertText = @"INSERT INTO dbo.Department
(
Id
,ChangeCode
,SentDatetime
,DepartmentId
,DepartmentName
,CreatedAccountId
,CreatedDate
,CreatedTime
,ModifiedAccountId
,ModifiedDate
,ModifiedTime
,Remark
)
VALUES
(
DEFAULT
,@ChangeCode
,@SentDatetime
,@DepartmentId
,@DepartmentName
,@CreatedAccountId
,@CreatedDate
,@CreatedTime
,@ModifiedAccountId
,@ModifiedDate
,@ModifiedTime
,@Remark
)
";

    var updateText = @"UPDATE dbo.Department
SET
ChangeCode = @ChangeCode
WHERE
Remark = @Remark";

    StackExchange.Profiling.MiniProfiler.Start();
    ConsoleProfiling.Start();

    //arrange
    DbConnectionFactory factory = new DbConnectionFactory("Local_HrDbContext");

    using (var connection = DbConnectionFactoryHelper.New(factory, CustomDbProfiler.Current))
    {
        connection.Execute(deleteText, new { Remark = Utility.TEST_REMARK });
        connection.Execute(insertText, new
        {
            ChangeCode = "A",
            SentDatetime = 0,
            DepartmentId = "TEST",
            DepartmentName = "TEST",
            CreatedAccountId = "TEST_USER",
            CreatedDate = 0,
            CreatedTime = 0,
            ModifiedAccountId = "TEST_USER",
            ModifiedDate = 0,
            ModifiedTime = 0,
            Remark = Utility.TEST_REMARK
        });
    }

    using (var connection = DbConnectionFactoryHelper.New(factory, CustomDbProfiler.Current))
    using (var transaction = connection.BeginTransaction())
    {
        try
        {
            connection.Execute(updateText, new { ChangeCode = "C", Remark = Utility.TEST_REMARK },
                transaction);
            throw new Exception();
            //transaction.Rollback();
        }
        catch (Exception)
        {
            transaction.Rollback();
        }
    }

    Console.WriteLine(CustomDbProfiler.Current.ProfilerContext.GetCommands());
}

 

心得：

這個套件很輕易的就幫我們把執行過的 SQL 命令記錄下來，不過我試了好久試不出交易命令的紀錄，希望有人可以告訴我怎麼做

若有謬誤，煩請告知，新手發帖請多包涵

Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET