Multiple Active Result Sets (MARS)
Entity Framework中出现"已有打开的与此命令相关联的 DataReader，必须首先将它关闭。"的解决方案
Fixing the "There is already an open DataReader associated with this Command which must be closed first." exception in Entity Framework
Once you get your model, database and mapping metadata files configured correctly for Entity Framework, one of the first exceptions that you're likely to face is the DataReader already open exception. It will normally surface when you're iterating over a result set and then lazy loading a child collection. In the following example I have a Contact entity that has a Phone collection. If I lazy load the Phones then I'll get the DataReader exception.
var contacts = from c in db.Contact
select c;
foreach (Contact c in contacts)
{
if (c.Phones.IsLoaded == false)
c.Phones.Load();
if (c.Phones.Count > 0)
{
Console.WriteLine(c.LastName);
foreach (ContactPhone p in c.Phones)
{
Console.WriteLine("\t"+p.ContactPhoneId);
}
}
}
The reason for the exception is that there is an open data reader while reading in the contacts and then the loading of the child Phone collection is attempting to open another DataReader. By default, the SqlClient ADO.NET driver does not allow this.
There are two ways to fix this. First, if you are using SQL Server 2005, you can just enable MARS in your connection string.
add name="YourDBConnectionString" connectionString="metadata=.;
provider=System.Data.SqlClient;provider connection string="Data Source=irv-dev-vms05;Initial Catalog=YourDB;Integrated Security=True;
MultipleActiveResultSets=true"" providerName="System.Data.Mapping" /
A second option is to read all the results into memory in one shot and close the connection. Then you are free to open another connection to the database to read in the child entities. So how do you control this? The trip to the database does not happen when the LINQ statement is assigned to the var in the code above. Instead, the query to the database happens during the inplicit call to the enumerator of the contacts collection. To force all the contacts to be loaded, simply copy them to a list in memory using the ToList method and then continue the normal processing.
var contacts = from c in db.Contact
select c;
List results = contacts.ToList();
foreach (Contact c in results)
{
if (c.Phones.IsLoaded == false)
c.Phones.Load();
if (c.Phones.Count > 0)
{
Console.WriteLine(c.LastName);
foreach (ContactPhone p in c.Phones)
{
Console.WriteLine("\t"+p.ContactPhoneId);
}
}
}
This works as expected. Another important realization here is that there is an open connection to the database as long as you are reading in records through that enumerator! A novice programmer may unwittingly include a massive number of time consuming operations within the foreach loop (calling a web service, for example, comes to mind) which will keep the connection to the database open for an inordinate length of time. Of course, you won't fall into that trap.
*********分隔符*********
文中提到解决方案有两种：
1、数据库为SQL Server 2005版本时，可以在web.config数据库链接串中加入MultipleActiveResultSets=true。
2、进行重复操作之前，将数据查询结果放入内存中，再进行使用。
具体解释看上文。
Enabling Multiple Active Result Sets
- 03/30/2017
- 5 minutes to read
- Contributors
Multiple Active Result Sets (MARS) is a feature that works with SQL Server to allow the execution of multiple batches on a single connection. When MARS is enabled for use with SQL Server, each command object used adds a session to the connection.+
Note
A single MARS session opens one logical connection for MARS to use and then one logical connection for each active command.
Enabling and Disabling MARS in the Connection String
Note
The following connection strings use the sample AdventureWorks database included with SQL Server. The connection strings provided assume that the database is installed on a server named MSSQL1. Modify the connection string as necessary for your environment.
The MARS feature is disabled by default. It can be enabled by adding the "MultipleActiveResultSets=True" keyword pair to your connection string. "True" is the only valid value for enabling MARS. The following example demonstrates how to connect to an instance of SQL Server and how to specify that MARS should be enabled.
string connectionString = "Data Source=MSSQL1;" +
"Initial Catalog=AdventureWorks;Integrated Security=SSPI;" +
"MultipleActiveResultSets=True";
You can disable MARS by adding the "MultipleActiveResultSets=False" keyword pair to your connection string. "False" is the only valid value for disabling MARS. The following connection string demonstrates how to disable MARS.
string connectionString = "Data Source=MSSQL1;" +
"Initial Catalog=AdventureWorks;Integrated Security=SSPI;" +
"MultipleActiveResultSets=False";
Special Considerations When Using MARS
In general, existing applications should not need modification to use a MARS-enabled connection. However, if you wish to use MARS features in your applications, you should understand the following special considerations.
Statement Interleaving
MARS operations execute synchronously on the server. Statement interleaving of SELECT and BULK INSERT statements is allowed. However, data manipulation language (DML) and data definition language (DDL) statements execute atomically. Any statements attempting to execute while an atomic batch is executing are blocked. Parallel execution at the server is not a MARS feature.
If two batches are submitted under a MARS connection, one of them containing a SELECT statement, the other containing a DML statement, the DML can begin execution within execution of the SELECT statement. However, the DML statement must run to completion before the SELECT statement can make progress. If both statements are running under the same transaction, any changes made by a DML statement after the SELECT statement has started execution are not visible to the read operation.
A WAITFOR statement inside a SELECT statement does not yield the transaction while it is waiting, that is, until the first row is produced. This implies that no other batches can execute within the same connection while a WAITFOR statement is waiting.
MARS Session Cache
When a connection is opened with MARS enabled, a logical session is created, which adds additional overhead. To minimize overhead and enhance performance, SqlClient caches the MARS session within a connection. The cache contains at most 10 MARS sessions. This value is not user adjustable. If the session limit is reached, a new session is created—an error is not generated. The cache and sessions contained in it are per-connection; they are not shared across connections. When a session is released, it is returned to the pool unless the pool's upper limit has been reached. If the cache pool is full, the session is closed. MARS sessions do not expire. They are only cleaned up when the connection object is disposed. The MARS session cache is not preloaded. It is loaded as the application requires more sessions.
Thread Safety
MARS operations are not thread-safe.
Connection Pooling
MARS-enabled connections are pooled like any other connection. If an application opens two connections, one with MARS enabled and one with MARS disabled, the two connections are in separate pools. For more information, see SQL Server Connection Pooling (ADO.NET).
SQL Server Batch Execution Environment
When a connection is opened, a default environment is defined. This environment is then copied into a logical MARS session.
The batch execution environment includes the following components:
-
Set options (for example, ANSI_NULLS, DATE_FORMAT, LANGUAGE, TEXTSIZE)
-
Security context (user/application role)
-
Database context (current database)
-
Execution state variables (for example, @@ERROR, @@ROWCOUNT, @@FETCH_STATUS @@IDENTITY)
-
Top-level temporary tables
With MARS, a default execution environment is associated to a connection. Every new batch that starts executing under a given connection receives a copy of the default environment. Whenever code is executed under a given batch, all changes made to the environment are scoped to the specific batch. Once execution finishes, the execution settings are copied into the default environment. In the case of a single batch issuing several commands to be executed sequentially under the same transaction, semantics are the same as those exposed by connections involving earlier clients or servers.
Parallel Execution
MARS is not designed to remove all requirements for multiple connections in an application. If an application needs true parallel execution of commands against a server, multiple connections should be used.
For example, consider the following scenario. Two command objects are created, one for processing a result set and another for updating data; they share a common connection via MARS. In this scenario, the Transaction.Commit fails on the update until all the results have been read on the first command object, yielding the following exception:
Message: Transaction context in use by another session.
Source: .Net SqlClient Data Provider
Expected: (null)
Received: System.Data.SqlClient.SqlException
There are three options for handling this scenario:
-
Start the transaction after the reader is created, so that it is not part of the transaction. Every update then becomes its own transaction.
-
Commit all work after the reader is closed. This has the potential for a substantial batch of updates.
-
Don't use MARS; instead use a separate connection for each command object as you would have before MARS.
Detecting MARS Support
An application can check for MARS support by reading the SqlConnection.ServerVersion value. The major number should be 9 for SQL Server 2005 and 10 for SQL Server 2008.
See Also
Multiple Active Result Sets (MARS)
ADO.NET Managed Providers and DataSet Developer Center
Note
The feedback system for this content will be changing soon. Old comments will not be carried over. If content within a comment thread is important to you, please save a copy. For more information on the upcoming change, we invite you to read our blog post.
南来地,北往的,上班的,下岗的,走过路过不要错过!
======================个性签名=====================
之前认为Apple 的iOS 设计的要比 Android 稳定,我错了吗?
下载的许多客户端程序/游戏程序,经常会Crash,是程序写的不好(内存泄漏?刚启动也会吗?)还是iOS本身的不稳定!!!
如果在Android手机中可以简单联接到ddms,就可以查看系统log,很容易看到程序为什么出错,在iPhone中如何得知呢?试试Organizer吧,分析一下Device logs,也许有用.
