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?
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)