ADODB Connections

  • Thread starter Thread starter Brian P. Hammer
  • Start date Start date
B

Brian P. Hammer

All,

I have a MDI application with 15 or so different forms. Each form connects
to the same SQL database via adodb. My question is, is it better to:

1. Just open the connection upon startup, then just reference the connection
throughout each form opening
2. Open and close each connection with the opening and closing of each MDI
form
3. Open and close the connection after each operation use such as load data,
save record and filtering
3. Create and new connection name for each MDI form

The problem as I see it, 1 could have many open connections to the SQL even
when the user doesn't need to be. 2. it gets very difficult to open and
close the connections going from form to form and threads.

I am not in the position to use the built in .Net data connections and
adapters, although I would love to in this app.

Any guidance would be great with this.
Thanks,
Brian




Public Const conData As String = "driver={SQL Server} and so on...."
Public cnnData As New Connection

cnnData.ConnectionTimeout = 10
cnnData.Open(conData)
 
I'll take a stab at this Couple questions first:

1. Is this program going to be used by multiple people in a networked
environment or is it solely for the use of one?
2. Since you don't have the liberty of using built in dataconnections or
adapters, can you still use datasets & dataviews?

I was thinking, that depending on how big this database is, you could
connect to the database upon program load, and load all of the data in a
dataset at runtime. Then you can pass the dataset between forms, and set it
to update the dataset at certain intervals that would then update the
database. This way, you can have the data readily available but not have to
be constantly taxing the database server. But, obviously this gets hairy in
a multi-user scenario.

hope this helps.
 
The program could be used by multiple people at the same time but looking at
it, no more then 15 people in 5 years for growth.

I'll have to do some trial runs with a dataset, should be able to. I am
using the Flex Grid control from Component One. About half of the rows and
columns are calculated values and not stored in SQL. I have to be able to
specify the datasource for each row (maybe skip 15 rows) and column so
that's why I am using a recordset.


Thanks for the great feedback.
--
Brian P. Hammer


Jim said:
I'll take a stab at this Couple questions first:

1. Is this program going to be used by multiple people in a networked
environment or is it solely for the use of one?
2. Since you don't have the liberty of using built in dataconnections or
adapters, can you still use datasets & dataviews?

I was thinking, that depending on how big this database is, you could
connect to the database upon program load, and load all of the data in a
dataset at runtime. Then you can pass the dataset between forms, and set it
to update the dataset at certain intervals that would then update the
database. This way, you can have the data readily available but not have to
be constantly taxing the database server. But, obviously this gets hairy in
a multi-user scenario.

hope this helps.
Click to expand...
 
Back
Top