再程序的过程中遇到“与基础事务管理器的通信失败的问题
过程如下:
1.做一个项目的业务逻辑层利用事务进行多个表的操作,出现了上述问题。问题出现了,有些矛盾。因为在做上一个项目的时候也是这样做的,可是此项目中却出现了问题。一时得不到答案。于是在网上找答案,
首先网上查找资料参考了下面的网址,
http://www.cnblogs.com/enquan/archive/2008/07/31/1257622.html
主要是在服务组件里面进行了MS DTC的配置 ,可是问题还是没有解决。之前的项目也没有对这个进行配置,也是可以进行类似事务处理的。所以一直很纠结,因为这个事情半天没干成事,把后面的事情也耽误了。
其次,由于项目的时间进度问题,这个问题请项目经理去解决了。到了下午,告诉我是因为应用程序和服务器不在一个地址段,等放在了正式平台上就可以了。虽然说正式平台没问题了,但是还是不太明白到底是怎么回事。第二天项目稍微缓和了下,想了想。(由于做上一个项目后,服务器和员工用的机器不在一个IP段)。既然现在不在一个IP段,那我就用现在的IP地址去执行先前的项目,如果出现类似的错误,那就是如项目经理所说。如果没有出现错误,那就是另外一回事了。果然,执行了旧的项目 后发现没有出现这样的问题。那就很奇怪了,是项目经理错了,还是有其他的原因。
再次,又在网上找了一篇博客http://blogs.msdn.com/b/dataaccess/archive/2006/02/14/532026.aspx
看了这个里面的内容,才渐渐的找到了原因。在一个事务里面进行多此的new SqlConnection()的操作就会出现上面的问题。超过了SQLSERVER预期的链接次数。那篇文章里就是用 一个类,将此事务里面的数据库连接,都用一个来操作。就没有这个问题。
既然是按照上面的说法,是所有的表操作都共用了一个数据库连接的原因。我 就拿之前的旧项目来验证下,发现的确之前的那个项目的确是这样做的。而现在的新项目在进行数据库操作时,每次都是新建了一个连接。现在才有些恍然的感觉。
最后,我又找项目经理过来看了,也用比较的方法进行了操作。验证了我所说的。项目经理说Ip段的问题,有些他们说的东西太简单了。不太好理解。最后,最后,为什么数据库会这样排斥多个连接?,为什么在一个IP段就不排斥多次连接呢?我还要想想。下回见!!!
http://blogs.msdn.com/b/dataaccess/archive/2006/02/14/532026.aspx
A ConnectionScope class. [Alazel Acheson]
14 Feb 2006 12:48 PM
I’ve heard a few comments from people who would like an easier way to manage connection lifetime & use across multiple methods. Most often, the problem is due to using a TransactionScope at an higher level, but opening and closing connections inside the methods – generally resulting in a distributed transaction unless you manually move a single connection around. For example:
void OuterMethod() {
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.RequiresNew, opts)) {
InnerMethod("select * from testtable");
InnerMethod("update testtable set col1 = N'new value'");
tx.Complete();
}
}
static void InnerMethod(string sqlText) {
using (SqlConnection conn = SqlConnection(connStr)) {
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.ExecuteNonQuery();
}
}
To avoid the distributed transaction, you would need to create the connection in the OuterMethod and pass it in as a parameter (somewhat tedious) or assign it to a member variable (somewhat risky, as you are then probably maintaining a reference to the connection beyond it’s intended lifetime).
I've implemented a simple scope class for db connections that can simplify the process (see the attached file). Feel free to use this class directly or modify it as needed.
To use it, simply create a new DbConnectionScope in the OuterMethod and follow one of the two patterns for getting your connection to the inner scope:
- Create, open and place your connection into the scope prior to use with AddConnection (generally the OuterMethod), assigning it a key for identification. In the InnerMethod, pull it out using GetConnection and assign it to your command before executing.
- Use GetOpenConnection() in the InnerMethod and the scope will construct & open your connection as needed, using the connection string as the key.
The example, re-written using the second pattern, looks like this:
void OuterMethod() {
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.RequiresNew, opts)) {
using (DbConnectionScope db = new DbConnectionScope()) {
InnerMethod("select * from testtable");
InnerMethod("update testtable set col1 = N'new value'");
tx.Complete();
}
}
}
static void InnerMethod(string sqlText) {
SqlCommand cmd = new SqlCommand();
cmd.Connection = (SqlConnection) DbConnectionScope.Current.GetOpenConnection(SqlClientFactory.Instance, connStr);
cmd.ExecuteNonQuery();
}
This class is only something you’d want to use only if you specifically want to re-use the same open connection – the connection pool does a much better job of handling connection re-use when your logic allows for the connection being reset, and you don't need any particular state associated with it. You also need to keep in mind the problems that using the same connection can cause – for example, only one command executing at a time if MARS is not on.
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights
Updated: Fixed a glaringly simple bug in the Dispose() method.
Updated: Missed one other bug, now fixed.
ADO.Net
以下是DbConnectionScope.cs这个类的代码
// Copyright (c) 2006, Microsoft Corporation
//
// Author: Alazel Acheson
namespace WebApplication3
{
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
// Allows almost-automated re-use of connections across multiple call levels
// while still controlling connection lifetimes. Multiple connections are supported within a single scope.
// To use:
// Create a new connection scope object in a using statement at the level within which you
// want to scope connections.
// Use Current.AddConnection() and Current.GetConnection() to store/retrieve specific connections based on your
// own keys.
// Simpler alternative: Use Current.GetOpenConnection(factory, connection string) where you need to use the connection
//
// Example of simple case:
// void TopLevel() {
// using (DbConnectionScope scope = new DbConnectionScope()) {
// // Code that eventually calls LowerLevel a couple of times.
// // The first time LowerLevel is called, it will allocate and open the connection
// // Subsequent calls will use the already-opened connection, INCLUDING running in the same
// // System.Transactions transaction without using DTC (assuming only one connection string)!
// }
// }
//
// void LowerLevel() {
// string connectionString = <...get connection string from config or somewhere...>;
// SqlCommand cmd = new SqlCommand("Some TSQL code");
// cmd.Connection = (SqlConnection) DbConnectionScope.Current.GetOpenConnection(SqlClientFactory.Instance, connectionString);
// ... finish setting up command and execute it
// }
/// <summary>
/// Class to assist in managing connection lifetimes inside scopes on a particular thread.
/// </summary>
sealed public class DbConnectionScope : IDisposable
{
#region class fields
[ThreadStatic()]
private static DbConnectionScope __currentScope = null; // Scope that is currently active on this thread
private static Object __nullKey = new Object(); // used to allow null as a key
#endregion
#region instance fields
private DbConnectionScope _priorScope; // previous scope in stack of scopes on this thread
private Dictionary<object, DbConnection> _connections; // set of connections contained by this scope.
#endregion
#region public class methods and properties
/// <summary>
/// Obtain the currently active connection scope
/// </summary>
public static DbConnectionScope Current
{
get
{
return __currentScope;
}
}
#endregion
#region public instance methods and properties
/// <summary>
/// Constructor
/// </summary>
public DbConnectionScope()
{
// Devnote: Order of initial assignment is important in cases of failure!
// _priorScope first makes sure we know who we need to restore
// _connections second, to make sure we no-op dispose until we're as close to
// correct setup as possible
// __currentScope last, to make sure the thread static only holds validly set up objects
_priorScope = __currentScope;
_connections = new Dictionary<object, DbConnection>();
__currentScope = this;
}
/// <summary>
/// Convenience constructor to add an initial connection
/// </summary>
/// <param name="key">Key to associate with connection</param>
/// <param name="connection">Connection to add</param>
public DbConnectionScope(object key, DbConnection connection)
: this()
{
AddConnection(key, connection);
}
/// <summary>
/// Add a connection and associate it with the given key
/// </summary>
/// <param name="key">Key to associate with the connection</param>
/// <param name="connection">Connection to add</param>
public void AddConnection(object key, DbConnection connection)
{
CheckDisposed();
if (null == key)
{
key = __nullKey;
}
_connections[key] = connection;
}
/// <summary>
/// Check to see if there is a connection associated with this key
/// </summary>
/// <param name="key">Key to use for lookup</param>
/// <returns>true if there is a connection, false otherwise</returns>
public bool ContainsKey(object key)
{
CheckDisposed();
return _connections.ContainsKey(key);
}
/// <summary>
/// Shut down this instance. Disposes all connections it holds and restores the prior scope.
/// </summary>
public void Dispose()
{
if (!IsDisposed)
{
// Firstly, remove ourselves from the stack (but, only if we are the one on the stack)
// Note: Thread-local _currentScope, and requirement that scopes not be disposed on other threads
// means we can get away with not locking.
if (__currentScope == this)
{
// In case the user called dispose out of order, skip up the chain until we find
// an undisposed scope.
DbConnectionScope prior = _priorScope;
while (null != prior && prior.IsDisposed)
{
prior = prior._priorScope;
}
__currentScope = prior;
}
// secondly, make sure our internal state is set to "Disposed"
IDictionary<object, DbConnection> connections = _connections;
_connections = null;
// Lastly, clean up the connections we own
foreach (DbConnection connection in connections.Values)
{
connection.Dispose();
}
}
}
/// <summary>
/// Get the connection associated with this key. Throws if there is no entry for the key.
/// </summary>
/// <param name="key">Key to use for lookup</param>
/// <returns>Associated connection</returns>
public DbConnection GetConnection(object key)
{
CheckDisposed();
// allow null-ref as key
if (null == key)
{
key = __nullKey;
}
return _connections[key];
}
/// <summary>
/// This method gets the connection using the connection string as a key. If no connection is
/// associated with the string, the connection factory is used to create the connection.
/// Finally, if the resulting connection is in the closed state, it is opened.
/// </summary>
/// <param name="factory">Factory to use to create connection if it is not already present</param>
/// <param name="connectionString">Connection string to use</param>
/// <returns>Connection in open state</returns>
public DbConnection GetOpenConnection(DbProviderFactory factory, string connectionString)
{
CheckDisposed();
object key;
// allow null-ref as key
if (null == connectionString)
{
key = __nullKey;
}
else
{
key = connectionString;
}
// go get the connection
DbConnection result;
if (!_connections.TryGetValue(key, out result))
{
// didn't find it, so create it.
result = factory.CreateConnection();
result.ConnectionString = connectionString;
_connections[key] = result;
}
// however we got it, open it if it's closed.
// note: don't open unless state is unambiguous that it's ok to open
if (ConnectionState.Closed == result.State)
{
result.Open();
}
return result;
}
#endregion
#region private methods and properties
/// <summary>
/// Was this instance previously disposed?
/// </summary>
private bool IsDisposed
{
get
{
return null == _connections;
}
}
/// <summary>
/// Handle calling API function after instance has been disposed
/// </summary>
private void CheckDisposed()
{
if (IsDisposed)
{
throw new ObjectDisposedException("DbConnectionScope");
}
}
#endregion
}
}