Managing db connections on the device

[Jeff]

I have a singleton class in my data layer that opens a SQL CE db connection
and assigns the connection to a member variable. This way I can open the
connection only once in the application and avoid having to close and
re-open it.

In my data layer I have a Customers class that is derived from a BaseTable
class. The BaseTable class has a DBConn property that calls a GetInstance of
the connection singleton class and returns the reference to the open
connection.

The question I have is: have I created any conflicts with the garbage
collector or am I mismanaging connection resources? In short, is my approach
reasonable here, any gotchas I haven't seen?

My code in Customers class looks like this:

public DataTable TableAll()
{
try
{
// here's the DBConn property that gets the db connection
SqlCeCommand cmd = DBConn.CreateCommand();
cmd.CommandText = "Customers";
cmd.CommandType = CommandType.TableDirect;

SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
DataTable table = new DataTable();
da.Fill(table);
da.Dispose();
cmd.Dispose();
return table;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}

here's DBConn in the singleton Connection class:

private SqlCeConnection m_conn;
public SqlCeConnection DBConn
{
get
{
if (m_conn != null && m_conn.State == ConnectionState.Open)
return m_conn;
else
return OpenConnection();
}
}


Thanks,
Jeff

 

[Darren Shaffer]

Jeff,

I use a similar approach on all of my projects and have benchmarked this
technique and find that not opening and closing the connection to SQL CE is
considerable faster. You have to ensure that the connection was not closed
as
a result of some previous operation failing or a transaction committing, but
I see
you doing that in your code below.

I have had no issue with this approach and have developed it into a
DatabaseManager
singleton that abstracts not only getting the DB connection but executes
queries,
non-queries (transactional and without), gets representations of SQL CE
tables as datasets,
etc. The reuse possible here saves a lot of time project to project.

Until the next version of SQL CE where more than one connection to the DB is
supported, this is a great way to make sure you never attempt to open a
redundant
connection.

-Darren

 

[Ginny Caughey [MVP]]

Darren,

Even after the next version of SqlCe which supports multiple connections, I
think your approach will be a good one when only one connection is needed or
intended.