Note See Update 6. It has a simple application that demonstrates how to recreate the problem.
I live a DTC nightmare ... Our setup is that we have two databases; SQL Server 2008 database and Oracle database (I believe 11 g). I have installed MTS oracle material. I have DTC configured to allow distributed transactions. All access to Oracle tables occurs through views in the SQL Server database that go against Oracle tables on the linked server.
(Regarding DTC config: Checked-> Network DTC Access, Allow Remote Clients, Allow Inbound, Allow Outbound, Mutual Authentication (tried all 3 options), Enable XA transactions and Enable SNA LU 6.2 transactions. NT AUTHORITY\NetworkService )
Our application is an ASP.NET MVC 4.0 application that calls a number of WCF services to perform database operations. Currently, the web application and the WCF service use the same application pool (not sure if it matters, but just in case ...)
Some of our services are transactional, others are not.
Each WCF service that is transactional has the following attribute on its interface:
[ServiceContract(SessionMode=SessionMode.Required)]
and the following attribute in the method signatures in the interface:
[TransactionFlow(TransactionFlowOption.Allowed)]
and the following attribute for each method implementation:
[OperationBehavior(TransactionScopeRequired = true, TransactionAutoComplete = true)]
At my data access level, all transactional methods are configured as follows:
using (IDbConnection conn = DbTools.GetConnection(_configStr, _connStr, true)) { using (IDbCommand cmd = DbTools.GetCommand(conn, "SET XACT_ABORT ON")) { cmd.ExecuteNonQuery(); } using (IDbCommand cmd = DbTools.GetCommand(conn, sql)) { ... Perform actual database work ... } }
Services that are a transactional DAL code for a transactional call. The idea was to keep the material, which should be transactional (in several cases), separate from the material, which should not be transactional (~ 95% of cases).
There should be no cases where transactional and non-transactional WCF methods are called from a transaction (although I have not verified this yet, and this may be the cause of my problems. I'm not sure if this is part of why I ask here.)
As I mentioned earlier, in most cases all this works fine.
Periodically, and I canāt determine what initiates it, I begin to receive errors. And as soon as they begin, almost everything starts to fail for a while. In the end, everything starts working again. Not sure why ... It's all in a single-user test environment.
Sometimes an error occurs:
Unable to start nested transaction for OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLSERVERNAME". A nested transaction was required because the XACT_ABORT parameter was set to OFF.
This message, I suppose, occurs when I have transactional material in transactions, since I do not set XACT_ABORT in non-transactional code (this is fully doable if this fixes my problem).
Most often, however, the error is this:
System.Data.SqlClient.SqlException (0x80131904): The operation could not be completed because the OLE DB provider "OraOLEDB.Oracle" for the linked server "ORACLSERVERNAME" could not start the distributed transaction.
Now, initially, we only had transactions on SQL Server tables and everything worked fine. This only happened after we added transaction support for some Oracle tables that started to crash. I know that Oracle transactions work. And, as I said, most of the time everything is just a gloomy dory, and then sometimes it starts to crash and doesn't work for a while until it decides to stop the failure, and then it all works again.
Wish that I could understand how to sell this as a āfeatureā for my users, but I'm not an optimist, so I would appreciate help in trying to track it. Please let me know if I have omitted any important information.
Update 1:. I noticed that our transactions did not have a DistributedIdentifier installed, so I added the EnsureDistributed() method from this blog post: http://www.make-awesome.com/2010/04/forcibly-creating-a-distributed-net -transaction /
Instead of hardcoded Guid (which seems to be causing a lot of problems), I have generating a new Guid for each transaction and it seems to work, but that did not fix my problem. I am wondering if a lack of DistribuedIdentifier indicates some other problem. I have never encountered such a situation as before, so I'm not sure what is ānormalā.
Update 2: I noticed that DistributedIdentifier is not passed to WCF. From the client, I have DistributedIdentifier and LocalIdentifier in Transaction.Current.TransactionInformation. On the WCF server, however, there is only a LocalIdentifier set, and it differs from Guid on the client side (which makes sense, but I expected DistributedIdentifier to go over).
Update 3:. It seems that when I am in the middle of a transaction fails, even after I disabled IIS, I cannot get the DTC to shut down and restart. If I go to Component Services and, for example, change the security settings and click "Apply" or "OK", after a short wait I will get dialogs saying: "Failed to restart the MS DTC service. View the event log for more information. "
In the event log, I get a series of events:
1 (from MSDTC): "The MS DTC service is stopping" 2 (From MSSQL$SQLEXPRESS): "The connection has been lost with Microsoft Distributed Transaction Coordinator (MS DTC). Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) will begin once the connection is re-established. This is an informational message only. No user action is required." -- Folowed by these 3 identical messages 3 (from MSDTC Client 2): 'MSDTC encountered an error (HR=0x80000171) while attempting to establish a secure connection with system GCOVA38.' 4 (from MSDTC Client 2): 'MSDTC encountered an error (HR=0x80000171) while attempting to establish a secure connection with system GCOVA38.' 5 (from MSDTC Client 2): 'MSDTC encountered an error (HR=0x80000171) while attempting to establish a secure connection with system GCOVA38.' 6 (From MSDTC 2): MSDTC started with the following settings: Security Configuration (OFF = 0 and ON = 1): Allow Remote Administrator = 0, Network Clients = 1, Trasaction Manager Communication: Allow Inbound Transactions = 1, Allow Outbound Transactions = 1, Transaction Internet Protocol (TIP) = 0, Enable XA Transactions = 1, Enable SNA LU 6.2 Transactions = 1, MSDTC Communications Security = Mutual Authentication Required, Account = NT AUTHORITY\NetworkService, Firewall Exclusion Detected = 0 Transaction Bridge Installed = 0 Filtering Duplicate Events = 1
This makes me wonder if there is something, perhaps an open transaction somewhere? I understand that there is something like, due to the lack of a better term, "dangling transactions" that are not committed or rolled back. In each case, when I use TransactionScope, this happens in the "using" statement, so everything should be either a rollback or a transfer. But I'm really starting to think that somehow, something is leaking ...
Update 4: related to update 3. I am doing manual dialing. Our connection string has "Enlist = false". DBTools.GetConnection() takes a boolean parameter that indicates whether to reinsert the current transaction into the connection. I am posting this update because, based on the materials from Update 3, I am wondering if, apparently, it is possible for a connection that should not involve transactions to borrow them somehow.
public static IDbConnection GetConnection(string configString, string connectionString, bool enlistTransaction) { SqlConnection conn = new SqlConnection(connectionString); conn.Open(); if (enlistTransaction && Transaction.Current != null) { conn.EnlistTransaction(Transaction.Current); } return conn; } public static IDbCommand GetCommand(IDbConnection conn, string command) { IDbCommand cmd = conn.CreateCommand(); cmd.CommandText = command; return cmd; }
Update 5: I was able to find a set of unit tests that, if I run a group, always fail in the same place in the same test (but if I just ran this test on my own, again and again, it doesnāt fails. It has something to do with testing done before it.) I managed to get some DTC trace logs. Here is a log that shows the initial transaction with an error. I also show some previous transactions, in case some helpers help. A transaction failure starts with seq=1846 .
pid=1244 ;tid=6284 ;time=10/15/2013-10:00:45.064 ;seq=1822 ;eventid=TRANSACTION_BEGUN ;tx_guid=49a79b73-66c0-48cb-abb1-8b657a2e3e4d ;"TM Identifier='(null) '" ;"transaction has begun, description :'<NULL>'" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:45.064 ;seq=1823 ;eventid=RM_ENLISTED_IN_TRANSACTION ;tx_guid=49a79b73-66c0-48cb-abb1-8b657a2e3e4d ;"TM Identifier='(null) '" ;"resource manager #1004 enlisted as transaction enlistment #1. RM guid = '344d3060-811c-4fc6-bab6-0eea76e3af3a'" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:45.064 ;seq=1824 ;eventid=RM_ENLISTED_IN_TRANSACTION ;tx_guid=49a79b73-66c0-48cb-abb1-8b657a2e3e4d ;"TM Identifier='(null) '" ;"resource manager #1004 enlisted as transaction enlistment #2. RM guid = '7b16851c-00a5-41dd-b59c-b003dcae08ec'" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:45.064 ;seq=1825 ;eventid=RM_ENLISTED_IN_TRANSACTION ;tx_guid=49a79b73-66c0-48cb-abb1-8b657a2e3e4d ;"TM Identifier='(null) '" ;"resource manager #1005 enlisted as transaction enlistment #3. RM guid = '72efe9cc-80f2-4a5b-9659-28b07987b600'" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:45.079 ;seq=1826 ;eventid=RECEIVED_COMMIT_REQUEST_FROM_BEGINNER ;tx_guid=49a79b73-66c0-48cb-abb1-8b657a2e3e4d ;"TM Identifier='(null) '" ;"received request to commit the transaction from beginner" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:45.079 ;seq=1827 ;eventid=RM_ISSUED_PREPARE ;tx_guid=49a79b73-66c0-48cb-abb1-8b657a2e3e4d ;"TM Identifier='(null) '" ;"prepare request issued to resource manager #1004 for transaction enlistment #1" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:45.079 ;seq=1828 ;eventid=RM_ISSUED_PREPARE ;tx_guid=49a79b73-66c0-48cb-abb1-8b657a2e3e4d ;"TM Identifier='(null) '" ;"prepare request issued to resource manager #1004 for transaction enlistment #2" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:45.079 ;seq=1829 ;eventid=RM_ISSUED_PREPARE ;tx_guid=49a79b73-66c0-48cb-abb1-8b657a2e3e4d ;"TM Identifier='(null) '" ;"prepare request issued to resource manager #1005 for transaction enlistment #3" pid=1244 ;tid=8488 ;time=10/15/2013-10:00:45.079 ;seq=1830 ;eventid=RM_VOTED_COMMIT ;tx_guid=49a79b73-66c0-48cb-abb1-8b657a2e3e4d ;"TM Identifier='(null) '" ;"resource manager #1004 voted commit for transaction enlistment #2" pid=1244 ;tid=8488 ;time=10/15/2013-10:00:45.079 ;seq=1831 ;eventid=RM_VOTED_COMMIT ;tx_guid=49a79b73-66c0-48cb-abb1-8b657a2e3e4d ;"TM Identifier='(null) '" ;"resource manager #1004 voted commit for transaction enlistment #1" pid=1244 ;tid=8488 ;time=10/15/2013-10:00:45.079 ;seq=1832 ;eventid=RM_VOTED_READ_ONLY ;tx_guid=49a79b73-66c0-48cb-abb1-8b657a2e3e4d ;"TM Identifier='(null) '" ;"resource manager #1005 voted read-only for transaction enlistment #3" pid=1244 ;tid=8488 ;time=10/15/2013-10:00:45.079 ;seq=1833 ;eventid=TRANSACTION_COMMITTED ;tx_guid=49a79b73-66c0-48cb-abb1-8b657a2e3e4d ;"TM Identifier='(null) '" ;"transaction has got committed" pid=1244 ;tid=8488 ;time=10/15/2013-10:00:45.079 ;seq=1834 ;eventid=RM_ISSUED_COMMIT ;tx_guid=49a79b73-66c0-48cb-abb1-8b657a2e3e4d ;"TM Identifier='(null) '" ;"commit request issued to resource manager #1004 for transaction enlistment #1" pid=1244 ;tid=8488 ;time=10/15/2013-10:00:45.079 ;seq=1835 ;eventid=RM_ISSUED_COMMIT ;tx_guid=49a79b73-66c0-48cb-abb1-8b657a2e3e4d ;"TM Identifier='(null) '" ;"commit request issued to resource manager #1004 for transaction enlistment #2" pid=1244 ;tid=8488 ;time=10/15/2013-10:00:45.079 ;seq=1836 ;eventid=RM_ACKNOWLEDGED_COMMIT ;tx_guid=49a79b73-66c0-48cb-abb1-8b657a2e3e4d ;"TM Identifier='(null) '" ;"received acknowledgement of commit request from the resource manager #1004 for transaction enlistment #1" pid=1244 ;tid=8488 ;time=10/15/2013-10:00:45.079 ;seq=1837 ;eventid=RM_ACKNOWLEDGED_COMMIT ;tx_guid=49a79b73-66c0-48cb-abb1-8b657a2e3e4d ;"TM Identifier='(null) '" ;"received acknowledgement of commit request from the resource manager #1004 for transaction enlistment #2" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:45.002 ;seq=1838 ;eventid=TRANSACTION_BEGUN ;tx_guid=55dd8607-c01e-4135-a247-7ef435c70bc6 ;"TM Identifier='(null) '" ;"transaction has begun, description :'<NULL>'" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:45.018 ;seq=1839 ;eventid=RM_ENLISTED_IN_TRANSACTION ;tx_guid=55dd8607-c01e-4135-a247-7ef435c70bc6 ;"TM Identifier='(null) '" ;"resource manager #1004 enlisted as transaction enlistment #1. RM guid = '7b16851c-00a5-41dd-b59c-b003dcae08ec'" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:45.018 ;seq=1840 ;eventid=RECEIVED_COMMIT_REQUEST_FROM_BEGINNER ;tx_guid=55dd8607-c01e-4135-a247-7ef435c70bc6 ;"TM Identifier='(null) '" ;"received request to commit the transaction from beginner" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:45.018 ;seq=1841 ;eventid=TRANSACTION_COMMITTED ;tx_guid=55dd8607-c01e-4135-a247-7ef435c70bc6 ;"TM Identifier='(null) '" ;"transaction has got committed" pid=1244 ;tid=8488 ;time=10/15/2013-10:00:45.106 ;seq=1842 ;eventid=TRANSACTION_BEGUN ;tx_guid=cc4a8215-3475-4c14-b40d-d150fc79f8f7 ;"TM Identifier='(null) '" ;"transaction has begun, description :'<NULL>'" pid=1244 ;tid=8488 ;time=10/15/2013-10:00:45.121 ;seq=1843 ;eventid=RM_ENLISTED_IN_TRANSACTION ;tx_guid=cc4a8215-3475-4c14-b40d-d150fc79f8f7 ;"TM Identifier='(null) '" ;"resource manager #1004 enlisted as transaction enlistment #1. RM guid = '7b16851c-00a5-41dd-b59c-b003dcae08ec'" pid=1244 ;tid=8488 ;time=10/15/2013-10:00:45.121 ;seq=1844 ;eventid=RECEIVED_COMMIT_REQUEST_FROM_BEGINNER ;tx_guid=cc4a8215-3475-4c14-b40d-d150fc79f8f7 ;"TM Identifier='(null) '" ;"received request to commit the transaction from beginner" pid=1244 ;tid=8488 ;time=10/15/2013-10:00:45.121 ;seq=1845 ;eventid=TRANSACTION_COMMITTED ;tx_guid=cc4a8215-3475-4c14-b40d-d150fc79f8f7 ;"TM Identifier='(null) '" ;"transaction has got committed" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:59.657 ;seq=1846 ;eventid=TRANSACTION_BEGUN ;tx_guid=d07cc436-033c-450b-a36c-7e2fe79cdb81 ;"TM Identifier='(null) '" ;"transaction has begun, description :'<NULL>'" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:59.657 ;seq=1847 ;eventid=RM_ENLISTED_IN_TRANSACTION ;tx_guid=d07cc436-033c-450b-a36c-7e2fe79cdb81 ;"TM Identifier='(null) '" ;"resource manager #1004 enlisted as transaction enlistment #1. RM guid = '344d3060-811c-4fc6-bab6-0eea76e3af3a'" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:59.672 ;seq=1848 ;eventid=RM_ENLISTED_IN_TRANSACTION ;tx_guid=d07cc436-033c-450b-a36c-7e2fe79cdb81 ;"TM Identifier='(null) '" ;"resource manager #1004 enlisted as transaction enlistment #2. RM guid = '7b16851c-00a5-41dd-b59c-b003dcae08ec'" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:59.672 ;seq=1849 ;eventid=RM_ENLISTED_IN_TRANSACTION ;tx_guid=d07cc436-033c-450b-a36c-7e2fe79cdb81 ;"TM Identifier='(null) '" ;"resource manager #1005 enlisted as transaction enlistment #3. RM guid = '72efe9cc-80f2-4a5b-9659-28b07987b600'" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:59.672 ;seq=1850 ;eventid=RECEIVED_ABORT_REQUEST_FROM_NON_BEGINNER ;tx_guid=d07cc436-033c-450b-a36c-7e2fe79cdb81 ;"TM Identifier='(null) '" ;"received request to abort the transaction from non beginner" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:59.672 ;seq=1851 ;eventid=TRANSACTION_ABORTING ;tx_guid=d07cc436-033c-450b-a36c-7e2fe79cdb81 ;"TM Identifier='(null) '" ;"transaction is aborting" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:59.672 ;seq=1852 ;eventid=RM_ISSUED_ABORT ;tx_guid=d07cc436-033c-450b-a36c-7e2fe79cdb81 ;"TM Identifier='(null) '" ;"abort request issued to resource manager #1004 for transaction enlistment #1" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:59.672 ;seq=1853 ;eventid=RM_ISSUED_ABORT ;tx_guid=d07cc436-033c-450b-a36c-7e2fe79cdb81 ;"TM Identifier='(null) '" ;"abort request issued to resource manager #1004 for transaction enlistment #2" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:59.672 ;seq=1854 ;eventid=RM_ISSUED_ABORT ;tx_guid=d07cc436-033c-450b-a36c-7e2fe79cdb81 ;"TM Identifier='(null) '" ;"abort request issued to resource manager #1005 for transaction enlistment #3" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:59.672 ;seq=1855 ;eventid=RM_ACKNOWLEDGED_ABORT ;tx_guid=d07cc436-033c-450b-a36c-7e2fe79cdb81 ;"TM Identifier='(null) '" ;"received acknowledgement of abort request from the resource manager #1005 for transaction enlistment #3" pid=1244 ;tid=6284 ;time=10/15/2013-10:00:59.672 ;seq=1856 ;eventid=RM_ACKNOWLEDGED_ABORT ;tx_guid=d07cc436-033c-450b-a36c-7e2fe79cdb81 ;"TM Identifier='(null) '" ;"received acknowledgement of abort request from the resource manager #1004 for transaction enlistment #2" pid=1244 ;tid=8488 ;time=10/15/2013-10:00:59.672 ;seq=1857 ;eventid=RM_ACKNOWLEDGED_ABORT ;tx_guid=d07cc436-033c-450b-a36c-7e2fe79cdb81 ;"TM Identifier='(null) '" ;"received acknowledgement of abort request from the resource manager #1004 for transaction enlistment #1" pid=1244 ;tid=8488 ;time=10/15/2013-10:00:59.672 ;seq=1858 ;eventid=TRANSACTION_ABORTED ;tx_guid=d07cc436-033c-450b-a36c-7e2fe79cdb81 ;"TM Identifier='(null) '" ;"transaction has been aborted"
I'm not sure, but it might be worth noting that there was a 14.5-second delay between the completion of the previous transaction and the start of this. Unit tests seem to be everywhere there, and I still don't understand why.
This probably doesn't matter, but the error is in ExecuteNonQuery below:
public IClientInternal GetClient(string clientCode) { string sql = "SELECT [CLIENT_CODE], [COMPANY], [EMPLOYEE] << more fields here >> FROM OPENQUERY("+ _settings.LinkedOracleServer + ", 'SELECT * FROM CLIENT WHERE "+ "CLIENT_CODE = ''" + clientCode + "'' "+ "')"; using (IDbConnection conn = DbTools.GetConnection(_configStr, _connStr, true)) { using (IDbCommand cmd = DbTools.GetCommand(conn, "SET XACT_ABORT ON")) { cmd.ExecuteNonQuery(); } using (IDbCommand cmd = DbTools.GetCommand(conn, sql)) { DbTools.AddParameter(cmd, "@CLIENT_CODE", DbType.String, clientCode); IDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { return ClientInternal.FromReaderRecord(reader); } return null; } } }
This code is called several times successfully before it ultimately fails.
In that, I wonder if it is possible that transactions will not be properly cleared on the Oracle side. If I understand correctly, Oracle has 10 distributed transaction restrictions. Is it possible that he believes that previous distributed transactions are still open (I see no signs of this. All the evidence seems to indicate that all previous transactions are working fine, and the DTC log shows that they were committed).
Update 6: I managed to create a problem in a rather small piece of code. Below, with the exception of changing the DB and clientCode names, is the exact code to reproduce the problem. I get a SqlException in a cmd.ExecuteReader() call in GetClients() The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE" was unable to begin a distributed transaction. I can call GetEmployeeBadges() again and again and it will work. I can call GetClients() again and again and it will work. But if I call GetEmployeeBadges () and then call GetClients() in a transaction, it fails. Apparently, this is the result of the second call being credited to the transaction.
As an additional note, something strange appears with GetEmployeeBadges() . This is another oddity in our environment, but V_EMPLOYEE and V_PAEMPLOYEE are actually representations of yet another Oracle database. So the Oracle server I am facing has views on another Oracle server. So this is a SQL Server view of the representation of the oracle in the oracle table. I know these are some nuts. It's amazing that everything is functioning here. If I try to run GetEmployeeBadges() in a transaction and enlist it in a transaction, it really fails: Cannot execute the query "<<query text here>>" against OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE".
I canāt imagine that to be much more minimal.
class Program { private static string connStr = @"Server=localhost\SQLEXPRESS;Database=MYDB;Trusted_Connection=True;Enlist=false"; static void Main(string[] args) { GetEmployeeBadges(); using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew, new TimeSpan(0, 15, 0))) { GetClients(); ts.Complete(); } } private static void GetEmployeeBadges() { string sql = @"select * from OPENQUERY(ORACLE, 'select a.security_nbr, a.employee, b.last_name, b.first_name, b.department from V_PAEMPLOYEE a, V_EMPLOYEE b where LENGTH(TRIM(a.SECURITY_NBR)) > 0 and a.EMPLOYEE = b.EMPLOYEE and a.COMPANY = 3')"; using (IDbConnection conn = DbTools.GetConnection(connStr)) { using (IDbCommand cmd = DbTools.GetCommand(conn, "SET XACT_ABORT ON")) { cmd.ExecuteNonQuery(); } using (IDbCommand cmd = DbTools.GetCommand(conn, sql)) { cmd.CommandTimeout = 240; IDataReader reader = cmd.ExecuteReader(); } } } public static void GetClients() { string clientCode = "clientCode"; string sql = @"SELECT [CLIENT_CODE], [COMPANY], [EMPLOYEE], [MENU_TOKEN_CODE], [ADMINISTRATOR_FLAG], [SUPERVISOR_FLAG], [CLIENT_DESCR], [DEFAULT_QUEUE_CODE], [FG_WHSE_CODE], [FRT_WHSE_CODE], [BILL_COMP_CODE], [FI_COMP_CODE], [DEFAULT_ROLE], [DEFAULT_PRINTER_CODE], [SHIP_PRINTER_CODE], [DEFAULT_DISPLAY] FROM OPENQUERY( ORACLE, 'SELECT * FROM CLIENT WHERE CLIENT_CODE = ''clientCode''')"; using (IDbConnection conn = DbTools.GetConnection(connStr, true)) { using (IDbCommand cmd = DbTools.GetCommand(conn, "SET XACT_ABORT ON")) { cmd.ExecuteNonQuery(); } using (IDbCommand cmd = DbTools.GetCommand(conn, sql)) { DbTools.AddParameter(cmd, "@CLIENT_CODE", DbType.String, clientCode); IDataReader reader = cmd.ExecuteReader(); reader.Read(); } } } }
DbTools stuff, if helpful:
public static class DbTools { public static IDbConnection GetConnection(string connectionString) { return GetConnection(connectionString, false); } public static IDbConnection GetConnection(string connectionString, bool enlistTransaction) { SqlConnection conn = new SqlConnection(connectionString); conn.Open(); if (enlistTransaction && Transaction.Current != null) { conn.EnlistTransaction(Transaction.Current); } return conn; } public static IDbCommand GetCommand(IDbConnection conn, string command) { IDbCommand cmd = conn.CreateCommand(); cmd.CommandText = command; return cmd; } public static IDbDataParameter AddParameter(IDbCommand cmd, string name, DbType type, object value) { IDbDataParameter param = cmd.CreateParameter(); param.ParameterName = name; param.DbType = type; param.Value = value != null ? value : DBNull.Value; cmd.Parameters.Add(param); return param; } }
Only 23 hours left for generosity. I LOVE to give this 150 points to someone !!!