Re: ADO data control, proper use
From: Mark J. McGinty (mmcginty_at_spamfromyou.com)
Date: 08/09/04
- Next message: Doug Stoltz: "Re: Running Query in VB-6 using MultiThreading ?"
- Previous message: Brian: "Multiple-step operstion generated erros - Datagrid as the Source"
- In reply to: Val Mazur: "Re: ADO data control, proper use"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 9 Aug 2004 13:32:40 -0700
"Val Mazur" <group51a@hotmail.com> wrote in message
news:%23aw2hmXfEHA.3612@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> 1,2. It is not a good idea to open multiple connections from the same
> application and keep them alive. First of all, you usually need just one
> connection, which could be used to get data. Second, opening of the
multiple
> connections is expensive and requires additional resources from the server
> to maintain them. Third, in a multi-user environment you could face
> situation, when other users will experience problem to connect to the
server
> if you reached limit for the number of opened connection. Fourth, you
would
> experience performance impact because of it. My personal opinion is to
avoid
> using ANY bound controls and use ADO library directly. In this case you
> could open connection, get data and close it. It would give you much
> flexibility, then using data controls. Since ADO supports pooling of the
> connections, you should not experience any issues opening and closing
> connections several times and it would work fast. In a case of bound
> controls, you cannot control opening of the connections and each control
> will open its own connection
There's actually some middle ground here (not that I hope to change your
mind, Val, you never seem to miss an opportunity to run-down data-bound
controls) but just so all views are heard...
I agree that the ADODC sucks, and imho, the data environment equally sucks
(if not more so -- it's more flexible but that just leads you to waste more
time with it before writing it off.). Neither is really geared to allow
dynamically determined data sources (at least, not in a way that isn't
excruciating for both the developer and user every single execution.) And
the lack of a way to manage open connections as has been noted, is a fatal
flaw.
So what's the middle ground? Custom data classes and data source user
controls. They can be coded to share a global connection, they can be coded
to take a clone from existing recordsets -- they can be coded to do just
about anything.
If you're binding grids, or maybe a small number of listboxes/comboboxes, I
like using a class, less overhead... But a class object cannot be contained
within a form, and as such gives you no graphical design-time binding (you
must do it yourself in code.) So if you have a form full of inputs, the
bindings for which would be tedious to code, to say the least, you'll need a
user control that's flagged and coded to be a data source.
Now some of the more experienced of you are saying, "but Mark, what about
that ugly little problem of the data control needing to be intialized at
design time, and when compiling, even though the app's globals upon which
the control depends may not have been initialized it at that time?" And
then before I can answer, you interrupt to say, "oh and Mark, don't even
suggest the AmbientProperties object, which is very nearly worthless because
it's unavailable during the Form_Initialize event."
Well, those are very astute observations, but there are ways... if anyone's
interested I'll explain more, and may even be coaxed into a bit of sample
source just for GP. :-)
-Mark
> 3. In a case if you use ADO library directly, then you would need to close
> connection and set connection variable to Nothing. Also never declare and
> instantiate ADO variable in a declaration part, because it could lead to
the
> memory leaks. Your code should look like
>
> Dim loConnection as ADODB.Connection
> .....
> Set loConnection = New ADODB.Connection
> loConnection.Open .........
> .......
>
> loConnection.Close
> Set loConnection=Nothing
>
> --
> Val Mazur
> Microsoft MVP
>
>
> "Zap" <Zappa@141.com.removethis> wrote in message
> news:H4adnS87wOQe7IvcRVn-hA@buckeye-express.com...
> >I am in the process of converting a VB program to backend MS SQL from
> > Access. The program had used the DAO data control to communicate with
> > Access. This program has many data controls (8-10) incorporated into
it.
> > Switching over to the ADO control is moving along but I have encountered
> > several questions along the way.
> > 1. When opening the program there is currently five connections to
SQL
> > Server that remain open and sleeping. There will probably be another
5-8
> > more connections by the time the program is complete. Is this alot of
> > connections for a single program to have open with SQL Server. There
will
> > probably be a max of 6 users connected at any one time which would yield
> > about 60 connections to SQL Server, any problems with this?
> > 2. Is there a way to close the ado data control connection after a
> > recordset is retreived from the server and then open it up when needed
> > again.
> > 3. Is there a proper way to close the connection and set the
> > connection to nothing upon exiting the program?
> >
> > I'll think of some more questions, I'm sure but answers to these will
help
> > for now. And yes I know that I should hard code the connections to SQL
> > Server and not use the data control but those were in place and I'm not
> > advanced enough to tackle someone elses project that way. I think I can
> > handle the minor syntax changes between ADO and SQL Server.
> >
> > VB 6 on Win XP
> > SQL Server 2000 on Windows 2000 Server, Database size about 1gig.
Adding
> > records for a radiolog, contacts, and reports in a police department.
> >
> > Thanks,
> > Zap
> >
> >
>
>
- Next message: Doug Stoltz: "Re: Running Query in VB-6 using MultiThreading ?"
- Previous message: Brian: "Multiple-step operstion generated erros - Datagrid as the Source"
- In reply to: Val Mazur: "Re: ADO data control, proper use"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
