當心SqlParameter(LINQ to SQL及EF)的小數無條件捨去行為

2014-07-25 07:20 AM

踩到一顆地雷!! 某支 LINQ to SQL 程式遭投訴: 使用者輸入4位小數，寫入資料庫 DECIMAL(3,2) 欄位時，最後兩位小數被無條件捨去而非四捨五入。

使用以下範例重現問題。在SQL建立資料表MathRound，分別有欄位PK VARCHAR(8)及N DECIMAL(3,2)兩個欄位，分別採用SqlCommand、LINQ to SQL及Entity Framework三種方法寫入三筆資料，PK分別為'ADO.NET'、'LINQ2SQL'以及'EF'，而數值則一律為2.2459(decimal)。SqlCommand執行時，以SqlParameter傳入2.2459，但執行兩次，第一次不指定Scale，第二次指定Scale=2。

        static void TestCommand()

var cnStr = "Data Source=(local);Initial Catalog=Lab;Integrated Security=SSPI";

            using (var cn = new SqlConnection(cnStr))

                cn.Open();

                var cmd = cn.CreateCommand();

                cmd.CommandText = "INSERT INTO MathRound VALUES(@pk,@n)";

                var pPK = new SqlParameter("@pk", SqlDbType.VarChar);

                pPK.Value = "ADO.NET";

                cmd.Parameters.Add(pPK);

                var pN = new SqlParameter("@n", SqlDbType.Decimal);

                pN.Value = 2.2459M;

                cmd.Parameters.Add(pN);

                cmd.ExecuteNonQuery();

                pPK.Value = "ADO.NET2";

                pN.Scale = 2;

                cmd.ExecuteNonQuery();

        static void TestEF()

            using (var ctx = new MyEntities())

                var ent = new MathRound();

                ent.PK = "EF";

                ent.N = 2.2459M;

                ctx.MathRounds.Add(ent);

                ctx.SaveChanges();

        static void TestLinqToSql()

            using (var ctx = new MyDataClassesDataContext())

                var ent = new MathRound();

                ent.PK = "LINQ2SQL";

                ent.N = 2.2459M;

                ctx.MathRounds.InsertOnSubmit(ent);

                ctx.SubmitChanges();

由執行結果發現一項事實: 當SqlParameter未指Sacle時，寫入數值為2.25，即2.2459四捨五入的結果；但若指定Scale=2，後兩位小數則被捨去而寫入2.24。而LINQ to SQL及EF等同指定Scale的SqlParameter，也會寫入2.24。

在stackoverflow找到相關討論，指出這是SqlParamter特性始然，而在SqlParamter.Scale文件可找到進一步說明:

Use of this property to coerce data passed to the database is not supported. To round, truncate, or otherwise coerce data before passing it to the database, use the Math class that is part of the System namespace prior to assigning a value to the parameter's Value property. (Scale屬性不支援資料精準度轉換，開發者應自行使用Math.Round或Math.Truncate方式處理後再指定給Value屬性)

【結論】

使用指定Scale的SqlParameter、LINQ to SQL或Entity Framework更新數字到DB DECIMAL欄位，若來源數值之小數位數高於DECIMAL欄位規格，多出的小數部分將會被"無條件捨去"而非一般預期的"四捨五入"! 建議做法是自行使用Math.Round(另外需小心銀行家四捨五入法陷阱)或Math.Truncate將數字轉換成要求的小數精準度，以避免誤差。

當心SqlParameter(LINQ to SQL及EF)的小數無條件捨去行為

Comments

Post a comment