OracleConnection life cycle

Question

I'm using ODP on my DB application. I created wrapper classes over ODP for my application. It's a desktop applicaton it runs in a linear way(user can not do any other operation during a running operation. GUI is locked during operations). Considering this i created a single OracleConnection object as a member and using it for all queries. But as i see best practice is using sth. like:

using (SqlConnection connection = new SqlConnection(connectionString))  
{  
    SqlCommand command = connection.CreateCommand();  

    command.CommandText = "mysp_GetValue";  
    command.CommandType = CommandType.StoredProcedure;  

    connection.Open();  
    object ret = command.ExecuteScalar();  
}

in all cases(even in linear execution).

Do i have to use like this or is single OracleConnection enough?

Now i'm calling the connect of my wrapper object and it calls m_OracleConnection.open(connectionString) while application is starting. But if i create a separate connection for every query how will i hold the state of the connection? Will a boolean value like bool m_IsConnected be enough?

And how can i warn the user if connection is lost soft or hard way?

I don't understand your question clearly but be aware of `OracleConnection` API is [obsolete now](http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracleconnection.aspx) — Soner Gönül, Jun 17 '13 at 10:44
It's not `System.Data.OracleClient.OracleConnection` under `ADO.NET`, it's `Oracle.DataAccess.Client.OracleConnection` under `ODP`. — gesus, Jun 17 '13 at 10:52

score 1 · Accepted Answer · answered Jun 17 '13 at 11:18

1

The important question under this is: does the ODP OracleConnection implement connection-pooling? If it does (and many ADO.NET providers do), then the "best practice" code is absolutely fine; it might look like you are opening a connection, but with connection pooling that Open() is actually "get an underlying connection from the pool, or connect if there isn't one available". The Dispose() (at the end of using) releases the underlying connection back to the pool. With this approach, then, there is no need to track the state of the connection - you let the pool worry about that.

It looks like connection pooling is enabled in ODP by default, with connection-string parameters to tweak it (source)

answered Jun 17 '13 at 11:18

Marc Gravell

1,026,079
266
2,566
2,900

I understand. And i guess i only have to worry about the state if there is a hard or soft problem with conenction. In case of a connection i can show a message box to user indicating the problem? And registering to `StateChange` event of `OracleConnection` will be useless because there will be a separate `OracleConnection` for each operation? – gesus Jun 17 '13 at 12:14
@gesus on the message box: whatever you choose, I guess. On the `StateChange` - yes, for *that* to work you'd need to keep the connection instance open and available – Marc Gravell Jun 17 '13 at 14:48

OracleConnection life cycle

1 Answers1