SQL connections

[surfuric]

I would like to know what is best (in terms of performance and memory, and programming practice) for opening connections to a SQL database.

I have an activity which accesses a SQL database, so in Process_Globals I have:
Dim SQL1 as SQL
From what I understand, everthing in my app now has access to that connection.

I also have a service which will attach to the same database at intervals. Plus I have a class which I use within other Activities and it has its own connection to the same database.

All could potentially be accessing the database at the same time.

So my question is:
Should all be referring to SQL1 which was defined in MAIN and leave the SQL connection open (never close it) basically just having 1 shared connection to the database?
Or should I use a seperate Initialize and Close for each activity/class, effectively having multiple, seperate connections to the database.

 

[mc73]

I used to have a connection for every single activity and service. lately, I am using a single one for all of them. I've noticed better performance and at the same time I don't have to worry about initializations and closing processes. I think since sql is globally declared, we can use it exactly as its declaration suggests, ie globally:

 

[Erel]

I agree. It is better to share a single SQL object. You might want to put it in a code module with an Init method:

Public Sub Init
 If SQL1.IsInitialized = False Then SQL1.Initialize(...)
End Sub

This way you can call it from each service or activity when they are created. In some cases your application may start from a different activity or service (for example if you use any intent filter).

 

[surfuric]

Excellent. Using 1 connection will greatly simplify my code anyway!

 

[Ricky D]

It certainly does. That's what I do for my apps and they all use SQL in them.

I put them into a db code module with any database specific code.

regads, Ricky