注意 请查看更新6。它有一个简单的应用程序,演示如何重现问题。
我正在经历DTC噩梦...我们的设置是有两个数据库;一个SQL Server 2008数据库和一个Oracle数据库(我相信是11g)。我已经安装了oracle MTS。我已经配置了DTC以允许分布式事务。所有对Oracle表的访问都通过SQL Server数据库中针对链接服务器中的Oracle表的视图进行。
(关于DTC配置:已选->网络DTC访问、允许远程客户端、允许入站、允许出站、互相认证(尝试了所有3个选项)、启用XA事务和启用SNA LU 6.2事务。DTC登录为NT AUTHORITY\NetworkService
)
我们的应用程序是一个ASP.NET MVC 4.0应用程序,调用多个WCF服务来执行数据库工作。目前Web应用程序和WCF服务共享同一个应用程序池(不确定是否相关,但以防万一...)
我们的一些服务是事务性的,而其他一些则不是。
每个事务性的WCF服务都在其接口上具有以下属性:
[ServiceContract(SessionMode=SessionMode.Required)]
并在接口中的方法签名上使用以下属性:
[TransactionFlow(TransactionFlowOption.Allowed)]
并且在每个方法实现中都需要添加以下属性:
[OperationBehavior(TransactionScopeRequired = true, TransactionAutoComplete = true)]
在我的数据访问层中,所有事务方法都设置如下:
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 ...
}
}
服务是事务性的,调用事务性数据访问层代码。这样做的想法是将需要进行事务处理的内容(少数情况)与不需要进行事务处理的内容(约95%的情况)分开。
不应该出现在事务中调用既有事务性又有非事务性WCF方法的情况(虽然我还没有验证过,这可能是我的问题所在。我不确定,这也是我在这里提问的原因之一)。
正如我之前提到的,在大多数情况下,这一切都很好地运行着。
定期地,我无法确定是什么触发了它,就会开始出现错误。一旦出错,几乎所有的东西都会在一段时间内失败。最终,事情又开始正常工作了。不知道为什么...... 这都是在单用户测试环境中完成的。
有时候错误信息是:
Unable to start a nested transaction for OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLSERVERNAME". A nested transaction was required because the XACT_ABORT option was set to OFF.
我猜测这个错误发生在我的事务中有非事务性的代码时,因为我没有在非事务性代码中设置XACT_ABORT
(如果这样做可以解决我的问题,那是完全可行的)。
然而,最常见的错误是:
System.Data.SqlClient.SqlException (0x80131904): 由于OLE DB提供程序“OraOLEDB.Oracle”无法开始分布式事务,因此无法执行操作。
最初,我们只对SQL Server表使用了事务,一切工作正常。直到我们为某些Oracle表添加了事务支持后,事情才开始出现问题。我知道Oracle事务是有效的。正如我所说,大多数时候,一切都很顺利,但有时会开始失败,并继续失败一段时间,直到它决定停止失败,然后一切又正常了。
但愿我能找到如何将其作为“功能”销售给我的用户,但我不太乐观,所以我需要帮助来尝试追踪它。如果我遗漏了任何重要信息,请告诉我。
更新1:我注意到我们的交易似乎没有设置DistributedIdentifier,所以我添加了这篇博客文章中的EnsureDistributed()
方法:http://www.make-awesome.com/2010/04/forcibly-creating-a-distributed-net-transaction/
我不再使用硬编码的Guid(这似乎引起了很多问题),而是为每个事务生成一个新的Guid,这似乎有效,但它并没有解决我的问题。我想知道缺少DistributedIdentifier是否表明其他潜在问题。我以前从未处理过这样的环境,所以不确定什么是“正常”的。
更新2:我注意到DistributedIdentifier没有传递给WCF。从客户端来看,在Transaction.Current.TransactionInformation中有DistributedIdentifier和LocalIdentifier。然而,在WCF服务器上,只设置了LocalIdentifier,并且它与客户端侧的不同Guid(这是有道理的,但我希望DistributedIdentifier能够跨越)。
更新3: 看起来,即使在关闭IIS之后,当我处于交易失败的状态中,我也无法关闭和重启DTC服务。如果我进入组件服务并更改安全设置,例如点击应用或确定后,经过一段时间,我会得到一个对话框,显示“无法重新启动MS DTC服务,请检查事件日志以获取详细信息。”在事件日志中,我会得到一系列事件:
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
这让我想知道是否有什么地方可能正在保持事务处于打开状态?我感觉存在某种未提交或回滚的“悬挂事务”。在我使用TransactionScope的每个案例中,它都发生在“using”语句中,因此一切都应该回滚或提交。但我真的开始认为,某种东西正在泄漏...
更新4:与更新3相关。我正在执行手动登记。我们的连接字符串为“Enlist=false”。DBTools.GetConnection()需要一个布尔参数,指定是否将当前事务登记到连接中。我发布此更新是因为根据更新3的内容,我想知道,也许不应该登记事务的连接是如何以某种方式登记它们的。
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;
}
更新5:我已经找到了一组单元测试,如果我运行这个组,它总是在同一个测试的同一个地方失败(但如果我只运行那个测试,一遍又一遍地运行,它就不会失败。这与在它之前运行的测试有关)。我设法获得了一些DTC跟踪日志。这里是一个显示最初失败事务的日志。我还展示了一些先前的交易,以防看到一些成功的交易有所帮助。失败的交易从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"
我不确定,但值得注意的是,在上一次交易完成和这次交易开始之间有14.5秒的延迟。单元测试似乎卡在那里,我还没有弄清楚原因。
这可能不重要,但失败发生在下面的ExecuteNonQuery中:
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;
}
}
}
这段代码在成功执行多次后最终会失败。
我想知道的一件事是,Oracle 是否可能没有正确清除事务。如果我理解正确,Oracle 有一个 10 分布式事务限制。是否可能它认为之前的分布式事务仍然处于打开状态(我看不到任何迹象表明存在此问题。所有证据似乎表明所有先前的事务都运行良好,并且 DTC 日志显示它们已提交)。
更新6:我已成功地在一小段代码中产生了问题。 在下面的代码中,除了更改DB和clientCode的名称之外,这是用于重现问题的确切代码。 在GetClients()
中的cmd.ExecuteReader()
调用上我得到一个SqlException
错误 The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE" was unable to begin a distributed transaction.
我可以反复调用GetEmployeeBadges()
而且它会正常工作。我可以反复调用GetClients()
而且它也会正常工作。但是,如果我在事务内先调用GetEmployeeBadges()然后再调用GetClients()
,则失败。看起来是第二个调用被纳入事务的结果。
GetEmployeeBadges()
似乎有一些奇怪的问题。这是我们环境中另一个奇怪的地方,V_EMPLOYEE 和 V_PAEMPLOYEE 实际上是另一个 Oracle 数据库上的视图。因此,我正在访问的 Oracle 服务器上有另一个 Oracle 服务器上的视图。所以这是一个 Oracle 表的 Oracle 视图的 SQL Server 视图。我知道,这有点疯狂。真是太神奇了,这里还能正常运行。如果我尝试在事务中运行 GetEmployeeBadges()
并将其注册到事务中,它实际上会失败并显示:Cannot execute the query "<<query text here>>" against OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE".
我无法想象如何将其变得更加简洁。
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的东西是:
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;
}
}
距离悬赏结束只剩23小时。我非常希望将这150分发给某个人!!!