問題描述

我有一個父對象(DAL 的一部分)，其中包含子對象的集合(List).

I have a parent object (part of a DAL) that contains, amongst other things, a collection (List<t>) of child objects.

當(dāng)我將對象保存回數(shù)據(jù)庫時，我輸入/更新父對象，然后循環(huán)遍歷每個子對象.為了可維護(hù)性，我已經(jīng)把孩子的所有代碼放到一個單獨(dú)的私有方法中.

When I'm saving the object back to the DB, I enter/update the parent, and then loop through each child. For maintainability, I've put all the code for the child into a separate private method.

我打算使用標(biāo)準(zhǔn)的 ADO 事務(wù)，但在我的旅行中，我偶然發(fā)現(xiàn)了 TransactionScope 對象，我相信這將使我能夠?qū)⑺袛?shù)據(jù)庫交互包裝在父方法中(以及子方法中的所有交互)一筆交易.

I was going to use standard ADO Transactions, but on my travels, I stumbled across the TransactionScope object, which I believe will enable me to wrap all DB interaction in the parent method (along with all interaction in the child method) in one transaction.

到目前為止還好嗎..?

So far so good..?

那么下一個問題是如何在這個 TransactionScope 內(nèi)創(chuàng)建和使用連接.我聽說使用多個連接，即使它們是同一個 DB 也會迫使 TransactionScope 認(rèn)為它是一個分布式事務(wù)(涉及一些昂貴的 DTC 工作).

So the next question is how to create and use connections within this TransactionScope. I have heard that using multiple connections, even if they are to the same DB can force TransactionScope into thinking that it is a distributed transaction (involving some expensive DTC work).

是這樣嗎?或者，正如我似乎在別處閱讀的那樣，使用相同的連接字符串(這將有助于連接池)的情況會好嗎?

Is the case? Or is it, as I seem to be reading elsewhere, a case that using the same connection string (which will lend itself to connection pooling) will be fine?

更實(shí)際地說，我是不是……

More practically speaking, do I...

1. 在父級中創(chuàng)建單獨(dú)的連接 &child(盡管具有相同的連接字符串)
2. 在父級中創(chuàng)建一個連接并將其作為參數(shù)傳遞(對我來說似乎很笨拙)
3. 做點(diǎn)別的……?

更新:

雖然看起來我可以使用我常用的 .NET3.5+ 和 SQL Server 2008+，但該項(xiàng)目的另一部分將使用 Oracle (10g)，所以我不妨練習(xí)一種技術(shù)跨項(xiàng)目一致使用.

所以我只是將連接傳遞給子方法.

選項(xiàng) 1 代碼示例:

using (TransactionScope ts = new TransactionScope())
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = conn;
                        cmd.Connection.Open();
                        cmd.CommandType = CommandType.StoredProcedure;

                        try
                        {
                            //create & add parameters to command

                            //save parent object to DB
                            cmd.ExecuteNonQuery();

                            if ((int)cmd.Parameters["@Result"].Value != 0)
                            {
                                //not ok
                                //rollback transaction
                                ts.Dispose();
                                return false;
                            }
                            else //enquiry saved OK
                            {
                                if (update)
                                {
                                    enquiryID = (int)cmd.Parameters["@EnquiryID"].Value;
                                }

                                //Save Vehicles (child objects)
                                if (SaveVehiclesToEPE())
                                {
                                    ts.Complete();
                                    return true;
                                }
                                else
                                {
                                    ts.Dispose();
                                    return false;
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            //log error
                            ts.Dispose();
                            throw;
                        }
                    }
                }
            }

推薦答案

當(dāng)您使用 TransactionScope 跨多個連接進(jìn)行事務(wù)時，許多數(shù)據(jù)庫 ADO 提供程序(例如 Oracle ODP.NET)確實(shí)會開始分布式事務(wù)- 即使它們共享相同的連接字符串.

Many database ADO providers (such as Oracle ODP.NET) do indeed begin distributed transactions when you use TransactionScope to transact across multiple connections - even when they share the same connection string.

某些提供程序(如 .NET 3.5+ 中的 SQL2008)識別何時在引用相同連接字符串的事務(wù)范圍內(nèi)創(chuàng)建新連接，并且不會導(dǎo)致 DTC 工作.但是連接字符串中的任何變化(例如調(diào)整參數(shù))可能會阻止這種情況發(fā)生 - 并且行為將恢復(fù)為使用分布式事務(wù).

Some providers, (like SQL2008 in .NET 3.5+) recognizes when a new connection is created in a transaction scope that refers to the same connection string, and will not result in DTC work. But any variance in the connection string (such as tuning parameters) may preclude this from occuring - and the behavior will revert to using a distributed transaction.

不幸的是，在不創(chuàng)建分布式事務(wù)的情況下確保您的事務(wù)協(xié)同工作的唯一可靠方法是將連接對象(或 IDbTransaction)傳遞給需要在同一對象上繼續(xù)"的方法.交易.

Unfortunately, the only reliable means of ensuring your transactions will work together without creating a distributed transaction is to pass the connection object (or the IDbTransaction) to methods that need to "continue" on the same transaction.

有時將連接提升到您在其中進(jìn)行工作的類的成員會有所幫助，但這會造成尷尬的情況 - 并使控制連接對象的生命周期和處置變得復(fù)雜(因?yàn)樗ǔＥ懦褂?code>using 語句).

Sometimes it helps to elevate the connection to a member of the class in which you're doing the work, but this can create awkward situations - and complicates controlling the lifetime and disposal of the connection object (since it generally precludes use of the using statement).

這篇關(guān)于TransactionScope:避免分布式事務(wù)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！