Dapper +Oracle 之 DateTime 注意事項
0 |
同事報案,我先前寫的 Dapper 共用程式庫有 Bug,當 WHERE 條件包含日期型別時,將 DateTime 寫入 Oracle Date 欄位,接著用同 DateTime 值做 WHERE 比對,竟找不到剛才寫入的資料。
用以下範例重現問題:
using (var cn = new OracleConnection(csOra))
{
cn.Open();
cn.Execute("TRUNCATE TABLE JEFFTEST");
var idx = 1;
var dttm = DateTime.Now;
//將DateTime.Now寫入資料庫
cn.Execute("INSERT INTO JEFFTEST (IDX,DTTM) VALUES (:idx, :dttm)", new { idx, dttm });
//重新該時間當WHERE條件比對,筆數為0(登楞!)
var cnt = cn.Query("SELECT * FROM JEFFTEST WHERE DTTM = :dttm", new { dttm }).Count();
Console.WriteLine($"Count={cnt}");
}
第一時間懷疑是 Oracle 的 Date 欄位精準度只到秒造成,元件配合 SQL 已使用好一陣子沒遇過此狀況,應是 SQL DateTime 欄位精準度可到秒以下,故同樣做法在 SQL 不會出錯:
using (var cn = new SqlConnection(csSql))
{
cn.Open();
cn.Execute("TRUNCATE TABLE JEFFTEST");
var idx = 1;
var dttm = DateTime.Now;
//將DateTime.Now寫入資料庫
cn.Execute("INSERT INTO JEFFTEST (IDX,DTTM) VALUES (@idx, @dttm)", new { idx, dttm });
//重新用DateTime.Now當WHERE條件比對
var cnt = cn.Query("SELECT * FROM JEFFTEST WHERE DTTM = @dttm", new { dttm }).Count();
//筆數為1,測試無誤
Console.WriteLine($"Count={cnt}");
Console.Read();
}
有趣的是,若 Oracle Date 搭配 .NET DateTime 有此陷阱,過去使用 ODP.NET 何以相安無事多年?於是我改用 OracleCommand、OracleParameter 做測試,也不會有日期比對不符的狀況:
using (var cn = new OracleConnection(csOra))
{
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText = "TRUNCATE TABLE JEFFTEST";
cmd.ExecuteNonQuery();
var dttm = DateTime.Now;
var timeStr = dttm.ToString("yyyy/MM/dd HH:mm:ss");
cmd.CommandText = "INSERT INTO JEFFTEST (IDX,DTTM) VALUES (:idx, :dttm)";
cmd.Parameters.Add("idx", OracleDbType.Decimal).Value = 1;
cmd.Parameters.Add("dttm", OracleDbType.Date).Value = dttm;
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM JEFFTEST WHERE DTTM = :dttm";
cmd.Parameters.RemoveAt(0);
var dr = cmd.ExecuteReader();
if (dr.Read())
Console.WriteLine(dr["DTTM"]);
else
Console.WriteLine("No Data");
Console.Read();
}
嗯,很好,所以這是 Oracle + Dapper 衍生出的新問題。弄一段程式碼驗證問題及解決方法,說明寫在註解裡,直接看 Code:
using (var cn = new OracleConnection(csOra))
{
cn.Open();
cn.Execute("TRUNCATE TABLE JEFFTEST");
var idx = 1;
var dttm = DateTime.Now;
var timeStr = dttm.ToString("yyyy/MM/dd HH:mm:ss");
//將DateTime.Now寫入資料庫
cn.Execute("INSERT INTO JEFFTEST (IDX,DTTM) VALUES (:idx, :dttm)", new { idx, dttm });
//從資料庫取出剛才存入的日期備用
var dttmInDb = cn.Query<DateTime>("SELECT DTTM FROM JEFFTEST WHERE IDX=1").Single();
//重新用DateTime.Now當WHERE條件比對,筆數為0
var cnt = cn.Query("SELECT * FROM JEFFTEST WHERE DTTM = :dttm", new { dttm }).Count();
Console.WriteLine($"使用原值WHERE比對 Count={cnt}");
//原因是Oracle DATE型別不包含毫秒
Console.WriteLine($"DB {dttmInDb:HH:mm:ss.fff} vs C# {dttm:HH:mm:ss.fff}");
//把毫秒刪除試試
var dttm2 = dttm.AddMilliseconds(-dttm.Millisecond);
cnt = cn.Query("SELECT * FROM JEFFTEST WHERE DTTM = :dttm2", new { dttm2 }).Count();
//登楞!筆數還是0
Console.WriteLine($"AddMilliseconds修正後比對 Count={cnt}");
//換個方法,先轉字串再ParseExact轉回日期
var dttm3 = DateTime.ParseExact(timeStr, "yyyy/MM/dd HH:mm:ss", null);
cnt = cn.Query("SELECT * FROM JEFFTEST WHERE DTTM = :dttm3", new { dttm3 }).Count();
Console.WriteLine($"ToString+ParseExact轉換後比對 Count={cnt}");
//暗!這樣就找到得,為什麼?兩個時間看起來一樣,但CompareTo不同
Console.WriteLine($"兩種修正結果看似相同 dttm2={dttm2:HH:mm:ss.fff} vs dttm3={dttm3:HH:mm:ss.fff}");
Console.WriteLine($"dttm2.CompareTo(dttm3)={dttm2.CompareTo(dttm3)}");
//問題出在Ticks,二者Ticks不同
Console.WriteLine($"關鍵在ms以下部分:dttm2={dttm2:HH:mm:ss.fffffff} dttm3={dttm3:HH:mm:ss.fffffff}");
//REF: http://stackoverflow.com/a/153014/4335757
dttm2 = dttm.Trim(TimeSpan.TicksPerSecond);
cnt = cn.Query("SELECT * FROM JEFFTEST WHERE DTTM = :dttm2", new { dttm2 }).Count();
Console.WriteLine($"Trim()取到秒再比對 Count={cnt}");
Console.Read();
}
執行結果如下:
使用原值WHERE比對 Count=0 DB 21:22:22.000 vs C# 21:22:22.356 AddMilliseconds修正後比對 Count=0 ToString+ParseExact轉換後比對 Count=1 兩種修正結果看似相同 dttm2=21:22:22.000 vs dttm3=21:22:22.000 dttm2.CompareTo(dttm3)=1 關鍵在ms以下部分:dttm2=21:22:22.0004700 dttm3=21:22:22.0000000 Trim()取到秒再比對 Count=1
簡單來說,關鍵在 DateTime.Now 秒之下還有 Milliseconds 跟 Ticks,存入 Oracle Date 時只存到秒,而 Dapper 預設使用 Oracle Timestamp 型別(精準到 0.000001 秒)對應 C# DateTime 型別,實際的 WHERE 條是 Date 與 Timestamp 相比。一開始修正問題,以為 AddMilliseconds(-dateVar.Millisecond) 就夠,忘記更底下還有 Tick,比對還是失敗,改用 ToString 再 ParseExact 反而成功。最後我在 stackoverflow 找到漂亮的 Trim(TimeSpan.TicksPerSecond) 函式完整截去秒以下部分,才算搞定。
關於 Dapper 將 C# DateTime 對應成 Oracle Timestamp 的行為,延伸到另一個更嚴重的議題:DateTime 被轉成 Timestamp 與 Oracle Date 欄位做比較將無法藉由索引加速,導致速度慢上五倍!(參考)換句話說,即使截去小數秒結果正確,也將因 Index Scan 效能低落。網路建議解法是改寫 SQL 語法,寫成「WHERE Col = CAST (:dttm AS DATE)」,但我想到一招:其實大可不必花功夫捨去小數秒,因為在 OracleCommand 範例中,DateTime 搭配 OracleDbType.Date 就沒有小數秒差異問題。在一篇點部落文章看到 SqlMapper.AddTypeMap(typeof(DateTime), DbType.Date) 做法,要求 Dapper 將 DateTime 對應成 OracleDbType.Date,就完全不用煩惱小數秒差異囉!這應該是最簡潔有效的做法,經測試驗證,成功!
using (var cn = new OracleConnection(csOra))
{
//修改Mapping設定
SqlMapper.AddTypeMap(typeof(DateTime), System.Data.DbType.Date);
cn.Open();
cn.Execute("TRUNCATE TABLE JEFFTEST");
var idx = 1;
var dttm = DateTime.Now;
//將DateTime.Now寫入資料庫
cn.Execute("INSERT INTO JEFFTEST (IDX,DTTM) VALUES (:idx, :dttm)", new { idx, dttm });
//重新用DateTime.Now當WHERE條件比對
var cnt = cn.Query("SELECT * FROM JEFFTEST WHERE DTTM = :dttm", new { dttm }).Count();
//筆數為1,測試無誤
Console.WriteLine($"Count={cnt}");
Console.Read();
}
【結論】
- Dapper 搭配 Oracle 時,C# DateTime 預設會對應成 OracleDbType.Timestamp,當資料庫端欄位為 Date 型別,會因小數秒差造成 WHERE 比對不符,另外會因無法套用索引造成查詢效率不彰。
- 在查詢語法中使月 CAST(:dateParam AS DATE) 轉型可以克服上述問題。
- 若資料庫以 Date 為主未用到 Timestamp,透過 SqlMapper.AddTypeMap(typeof(DateTime), DbType.Date) 將 C# DateTime 改對應至 DbType.Date,可更巧妙避開問題。
Comments
Be the first to post a comment