MiniProfiler 是一個追蹤套件,會在網頁秀出追蹤頁籤,方便開發人員觀察,有興趣的可以去看 Kevin 大寫的文章
http://kevintsengtw.blogspot.tw/2013/01/aspnet-mvc-4-miniprofiler.html
若你的專案不是 Web ,但又想要紀錄 SQL Command,可以使用 MiniProfiler.Integrations...
開發環境:
- 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