예제 #1
0
        /// <summary>
        /// Stored Procedure 與 EF Context 在相同的 Transaction
        /// 利用 EF 建立的 context 物件, 去產生 connection / transaction
        /// </summary>
        /// <remarks>
        /// [探索2] 試一下 MSDN 的解法, EF 6.X 以後, 可自行控制 TRANSACTION, 可以解決
        /// https://msdn.microsoft.com/en-us/library/dn456843(v=vs.113).aspx
        /// 注意: using (MyDBEntities ctx = new MyDBEntities()) 還沒有 open connection
        /// 注意: using (var tx = ctx.Database.BeginTransaction()) 會先 open connection 至 DB, 再 BEGIN TX
        /// </remarks>
        public void CallSpWithSameTxInContext()
        {
            using (EFTestDBEntities ctx = new EFTestDBEntities())
            {
                using (var tx = ctx.Database.BeginTransaction())
                {
                    ObjectParameter orderno = new ObjectParameter("po_order_no", typeof(String));
                    ctx.usp_get_order_no(orderno);
                    Console.WriteLine(orderno.Value);

                    ctx.MyOrders.Add(new MyOrder()
                    {
                        OrderNo     = orderno.Value.ToString(),
                        ShipName    = "jasper",
                        ShipAddress = "taipei",
                        TotalAmt    = 1000
                    });
                    //試一下 SaveChanges(), 再 RollbackTransaction(), 看一下結果
                    try
                    {
                        ctx.SaveChanges();
                        //tx.Commit();
                        tx.Rollback();
                    }
                    catch (Exception ex)
                    {
                        tx.Rollback();
                        Console.WriteLine(ex.ToString());
                    }
                }
            }
        }
예제 #2
0
        // --------------------------------------------------
        // 如何在 SQL Server Profiler 觀察 TRANSACTION
        // https://weblogs.asp.net/dixin/where-is-transaction-events-in-sql-server-profiler
        // --------------------------------------------------

        /// <summary>
        /// Stored Procedure 在單獨的 Transaction
        /// </summary>
        /// <remarks>
        /// [問題] Stored Procedure 預設會單獨 1 個 Transaction, 即使 EF 沒有 SaveChanges(), 仍會 Commit !
        /// </remarks>
        public void CallSpWithExplicitTx()
        {
            using (EFTestDBEntities ctx = new EFTestDBEntities())
            {
                ObjectParameter orderno = new ObjectParameter("po_order_no", typeof(String));
                ctx.usp_get_order_no(orderno);
                Console.WriteLine(orderno.Value);

                ctx.MyOrders.Add(new MyOrder()
                {
                    OrderNo     = orderno.Value.ToString(),
                    ShipName    = "jasper",
                    ShipAddress = "taipei",
                    TotalAmt    = 1000
                });
                //故意不作 SaveChange(), 查結果, 可以發現 OrderNoGenerators 這個 table 的資料有異動
                //ctx.SaveChanges();
            }
        }