Jul 24, 2012 at 4:23 PM
Edited Jul 24, 2012 at 4:30 PM
You can avoid transaction escalation to a distributed transaction if:
- The database is SQL Server 2008
- .NET Framework 3.5 or above is being used
- Connection pooling is enabled (it is by default)
- The connection strings are identical
- Only one connection is open at once
http://blogs.msdn.com/b/adonet/archive/2008/03/26/extending-lightweight-transactions-in-sqlclient.aspx for more information about the changes made to ADO.NET to support the above.
DbCommand implements IDisposable. In general, if an object implements IDisposable then Dispose should be called when finished with the object. That said, not calling Dispose on DbCommand is usually not catastrophic since DbCommand.Dispose() only
frees up some cached parameter information and does not affect the connection.
When you call ExecuteNonQuery() Enterprise Library will open a connection, execute the query and then call close on the connection. So, the pattern is OK.
When viewing connections in SQL Profiler remember that connections are (usually) pooled so you will see the same connection reused for many calls even though in code the connection has been "opened" and then "closed".
Enterprise Library support engineer