Reputation: 339
After opening a connection, Do I have to explicitly close it? or does .net close it automatically? What about if an exception is thrown?
using (DataContext pContext = new DataContext())
{
pContext.Connection.Open()
using (var trx = pContext.Connection.BeginTransaction())
{
//make changes to objects
//Make calls to ExecuteStoreCommand("Update table SET pee=1 AND poop=2 WHERE ETC");
pContext.SaveChanges();
trx.Commit();
}
}
Does pContext.Connection.Close()` get called in all instances by the framework? I know that in a normal using statement it does, but what about when I manually open it?
I am using the Connection.BeginTransaction because In my tests, (using SaveChanges() alone without transactions) if this code executes and fails for some reason, the commands sent during ExecuteStoreCommand() are saved even though the changes to my objects aren't.
I have to call Connection.Open() before calling pContext.Connection.BeginTransaction() otherwise I get the error:
Inner Exception:
The connection is not open.
Any help would be appreciated.
Upvotes: 1
Views: 4236
Reputation: 131413
There is no need to call DataContext.Connection.Open(). In fact, it's bad practice to do so.
The context opens a connection only when it needs to. By calling Connection.Open, you open the connection for far longer that is needed, increasing the chance that locks will accumulate and lead to blocking. It is also a good way to exhaust the connection pool in high traffic systems.
SaveChanges opens a connection and a transaction itself, so there is no need to manually open the connection or start the transaction. From the documentation:
SaveChanges operates within a transaction. SaveChanges will roll back that transaction and throw an exception if any of the dirty ObjectStateEntry objects cannot be persisted
Explicitly using connections and transactions makes sense only if you want to mix raw SQL commands inside the EF session. By itself this is not a good practice, but if you do, you don't need to close the connection because the DataContext will close the connection when it is disposed. There is a sample in the documentation, at How to: Manually Open the Connection from the Object Context
You can also handle a transaction using a TransactionScope, as described in How to: Manage Transactions in the Entity Framework. In this case, you don't need to open the connection as all changes are automatically enlisted in the current transaction. This is described in http://msdn.microsoft.com/en-us/library/bb738523(v=vs.100).aspx and an example would be :
using (DataContext pContext = new DataContext())
{
using (TransactionScope transaction = new TransactionScope())
{
//execute commands
pContext.SaveChanges();
transaction.Complete()
}
}
In any case, manually handling connections and transactions is not trivial. You should read Managing Connections and Transactions for some of the things you need to be aware
Upvotes: 9
Reputation: 23087
This means
using (DataContext pContext = new DataContext())
that pContext is disposed after leaving using
it's equivalent to:
DataContext pContext = new DataContext()
try
{
//do stuff here
}
finally
{
if(pContext!=null)
((IDisposable)pContext).Dispose();
}
so answer is -> you don't need to call close after your code because
Connections are released back into the pool when you call Close or Dispose on the Connection..."
from here
Upvotes: 1