Best Practices for using unique connection

Topics: Data Access Application Block
Jan 31, 2012 at 3:23 PM

Hi,

I use Ent.Library Data 5.0.

I want use only unique connection for execute several commands (using the "same" connection).

Any best practices about it ? any good full sample with source code ?

Regards, thx

 

Editor
Feb 1, 2012 at 12:45 AM

Could you be more specific about what you are trying to accomplish?  Are you worried about transactions?

Since Enterprise Library 3 there has been support for reusing the same connection within a single transaction.  http://www.davidhayden.com/blog/dave/archive/2007/01/27/TransactionScopeDataAccessApplicationBlock.aspx

You can also get access to the database connection directly but this is not typically done when using Enterprise Library Data Access Application Block:

Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
using (IDbConnection conn = db.CreateConnection())
{
    conn.Open();
    // ....
} 

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

Feb 1, 2012 at 9:08 AM
Edited Feb 1, 2012 at 12:02 PM

 Thanks, I want reuse connection, without transaction context (NO transaction) for  execute multiple DbCommands with one connection. I use ODP.Net and Oracle.


My sample Code, really I use
Command = Command.CreateCommand(Database, storedProcName);
rowsAffected = db.ExecuteNonQuery(cm);

My question is really:

How can you execute multiple DbCommands with one connection?

http://stackoverflow.com/questions/2468891/executing-multiple-dbcommands-in-an-open-connection-with-enterprise-library
And more questions:

"Each call to ExecuteNonQuery(DbCommand) will potentially use a new connection. If you wanted to ensure that you were always using the same connection you could use the overload ExecuteNonQuery(DbCommand, DbTransaction). If you pass in a transaction Enterprise Library will use the connection associated with the transaction. Of course, you may not want to use a transaction if it is not required." Tuzo

Lieven's issue (NOT MINE):  "I need to insert 750 records at once. I used open close connection for all 750 but it takes a long time. A batch insert also takes a long time. There are 350 concurrent users that will need to insert 750 records at the same time. So I need the fastest way"
"How long did it take for 750 inserts? Was it for one user performing the 750 inserts or 350 concurrent? Do you have indexes on the tables? By default ADO.NET uses connection pooling [ msdn.microsoft.com/en-us/library/8xx3tyca ] so even though you are doing an open and close you should not be actually physically opening a connection to the database every time and is probably not the source of your performance issue (unless maxpoolsize is being reached but then I would expect to see timeout expired errors)." Tuzo


thereall, anyways, any best practices for using Enterprise Library for  execute multiple DbCommands with one connection.

        [TestMethod]
        public void Conectar_con_EntLib_test()
        {

            Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
            using (IDbConnection conn = db.CreateConnection())
            {
                conn.Open();

                TestContext.WriteLine("Conexión: " + conn.State);

                using (var reader = db.ExecuteReader(CommandType.Text, "select * from xxx t order by fecha desc"))
                {
                    while (reader.Read())
                    {
                        TestContext.WriteLine("Reading  {0}: {1}", reader[2], reader[3]);
                    }
                }

                TestContext.WriteLine("Conexión: " + conn.State);
                               using (var reader = db.ExecuteReader(CommandType.Text, "select * from xxx t order by fecha desc"))
                {
                    while (reader.Read())
                    {
                        TestContext.WriteLine("Reading  {0}: {1}", reader[2], reader[3]);
                    }
                }

                
                conn.Close();
            }

        }

 

Editor
Feb 3, 2012 at 3:22 AM

Connection management is done deep within the Database class so it's hard to modify the behavior without creating a new Database implementation and overriding the CreateConnection method (you may also need a custom DbProvider as well).  This answer shows how.

Another option would be to work with the connection directly using ADO but it seems like you want to test using the Enterprise Library code.

Your best bet might be to enable connection pooling and, since this appears to be for a test, set the min and max pool size to 1 which would force the same connection to be used and also set Connection Reset to false. 

I'm just curious what you are trying to accomplish with only having one connection?  

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

Oct 30, 2013 at 10:33 AM
For example, ExecuteNonQuery command opens and closes the connection.

The individual commands typically handle the opening and closing of the connection (e.g. ExecuteNonQuery).

Any example replicating the exact ADO logic using the Enterprise Library database methods?

I use Oracle, using Enterprise Library and ODP.NET. I use out parameters like XMLType or BLOB types. It required opened connection, but ExecuteNonQuery opens and closes connection.

My sample Code, really I use

rowsAffected = db.ExecuteNonQuery(cm);

db = Database Enterprise Library
cm = DbCommand

References: https://entlib.codeplex.com/discussions/454750
Editor
Oct 31, 2013 at 5:47 AM
You would have to recreate the code from the Enterprise Library (and perhaps also EntLibContrib) source code. Or just directly code in ADO.NET the functionality you need.

For example, you can get a connection, manage its lifetime and then create a command to do whatever you want:
Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
using (IDbConnection conn = db.CreateConnection())
{
    conn.Open();
    DbCommand cmd = conn.CreateCommand();
    // ....
} 
~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to