Global ADO connection to SQL 1

[DebbieCoates]

I am new to using visual basic with SQL, previously I have used Access.
I was wondering if anyone could answer these few questions for me.
If I was to create a login form asking for username and password, and then once that
Was supplied I then connect to my SQL Server using an ado connection, providing I didn’t close the form down (hid it instead), would my connection stay open all the time whilst my user is in my application?
And if I declared the adodb connection as a global variable, whatever form I am in, could I then just use this to call the connection

Ie
Global cnn As New ADODB.Connection

Public Function Connect() As Integer

cnn.Open "Provider=sqloledb;" & _
"Data Source=SERVERNAME;" & _
"Initial Catalog=DATABASENAME;" & _
"User Id=USERID;" & _
"Password=PASSWORD"
Connect = cnn.State

This function would open my connection when my users first enter my application
And then
Private Sub Command6_Click()
If Connect = 1 Then
Dim rs As New ADODB.Recordset
Dim strsql As String
strsql = "Select * from Projects"
rs.Open strsql, cnn
With rs
Me.projectid = rs.Fields("ProjectId")
Me.txtname = rs.Fields("name")
Me.organisation = rs.Fields("organisation")
End With

rs.Close
Set rs = Nothing
Else
'Unable to establish a connection
End If
End Sub

Private Sub Form_Close()
cnn.Close
Set cnn = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
Call Connect
End Sub
End Function


Is this a good way of doing this rather than opening a new connection everytime I wanted to do something with SQL?

 

[Andrzejek]

That's the way I do it. I have connection open while user is in the app. We have over 500 users accessing ORACLE and the app is working fine for over 7 years.

Every night all users are kicked out - usually they are not logged in at night anyway - to run some stored procedures.

You just need to make sure that your time out at your DB is set right. You don't want your users to idle and be kicked out before they have the chance to log out.

Also, consider:

Private Sub Command6_Click()
If Connect = 1 Then
Dim rs As New ADODB.Recordset
Dim strsql As String
strsql = "Select * from Projects"
[blue]
rs.CursorType = adOpenForwardOnly
rs.LockType = adLockReadOnly
rs.CursorLocation = adUseClient
[/blue]
rs.Open strsql, cnn

Have fun.

---- Andy

 

[BobRodes]

<Global cnn As New ADODB.Connection
Debbie, I would not do this, especially in this context. I would put the declaration on one line and the instantiation on another. For reasons why, see faq222-6008. Furthermore, I would keep in mind that connections are expensive, and I would find ways to close them when they aren't being used. My feeling is that you're kind of grasping at the concept of encapsulation, which is very important. Consider learning how to encapsulate your connection functionality in an object dedicated to that purpose, and then exposing that functionality to the other entities in your application. If you wish to take a look at a demonstration of how to create DLLs in VB, you'll find one at faq708-5940.

HTH

Bob

 

[BobRodes]

Andy, you may wish to check this code, which suggests a superfluity in the code you've posted:

With rs
    .CursorType = adOpenForwardOnly  'Omitting this line gives the same result
    .Open "select * from customers", cn
    Debug.Print .CursorType
    .Close
    .CursorLocation = adUseClient
    .Open "select * from customers", cn
    Debug.Print .CursorType
End With

You'll find that Cursortype is 0 the first time, and 3 the second time. Now why would that be?

Bob

 

[Andrzejek]

Bob,

Pretty much all what I know about VB is what I have learned by myself - from groups like this one. And I do not know the answer to your question: "Now why would that be?" Sorry.

All what I know is what works for me. And if it works - I do not question 'why'.

I wish I would know more abour the VB's 'inside' how stuff works and why.......


Have fun.

---- Andy

 

[BobRodes]

Well, then. You could remove two of your three lines of code, and it would work exactly as it does now. This isn't "inside" stuff, either: suppose you read the "ADO Cursor Settings" table here

http://msdn2.microsoft.com/en-us/library/aa905893(SQL.80).aspx.

You may find the information useful (and it is definitely "outside" information) sometime down the line, in a context where not knowing it causes you to create code that doesn't work, rather than being merely superfluous. So might the OP.

HTH

Bob

 

[dilettante]

Using a single connection per client is generally a good thing.

Holding a connection open for the life of the program... not aways so good. Some users never seem to know when to close an application, leaving it open all day or even going home with it running! Timing out idle sessions somehow is probably a good idea, but I wouldn't rely on the server to handle it.


Lots of scary code in this thread. Oof!

The [tt]Global[/tt] keyword is so old and deprecated it isn't even listed in the 1998 VB6 documentation. Where'd you get this? It dates back to way before VB5... I think after VB3 it was replaced by [tt]Public[/tt]. I suspect people think it means something special - it doesn't.

This is kind of painful:

Private Sub Command6_Click()
If Connect = 1 Then
Dim rs As New ADODB.Recordset
Dim strsql As String
strsql = "Select * from Projects"
rs.Open strsql, cnn
    With rs
        Me.projectid = rs.Fields("ProjectId")
        Me.txtname = rs.Fields("name")
        Me.organisation = rs.Fields("organisation")
    End With
    
rs.Close
Set rs = Nothing
Else
    'Unable to establish a connection
End If
End Sub

Yipes! Object qualification goofs: Redundant "Me" here, With block but not using it, relying on default properties of controls. Extra variables not needed. Magic numbers (use the named constants). Bulky collection access syntax - the bang operator (!) exists for a reason. Setting object references to [tt]Nothing[/tt] before exiting their scope is pointless.

Maybe something like:

Private Sub Command6_Click()
    If Connect = adStateOpen Then
        Dim rs As New ADODB.Recordset
        With rs
            .Open "Select * from Projects", cnn
            projectid.Text = !ProjectId
            txtname.Text = !name
            organisation.Text = !organisation
            .Close
        End With
    Else
        'Unable to establish a connection
    End If
End Sub

I hope [tt]Projects[/tt] only contains those 3 fields and one row because you've asked for every field of every row in the table with [tt]Select *[/tt] (ouch). And give that command button a real name. Command6? Come on! ;-)

 

[strongm]

>The bang operator exists for a reason

I'm sure dilettante already knows this, but for others who may visit this thread see thread222-717031 for a little further discussion of the bang operator

 

[JoeAtWork]

Wow, thanks to that last thread reference I finally understand the use of ! (and why I will continue not to use it).

As for your original question on global connections - I used to do that until I found out about about connection pooling. Basically, if your connection string is always the same, you can open, close, and reopen your connection without a performance penalty because SQL Server keeps recently used connections open for a while. If an application asks for a connection with an identical connection string that is already in the pool, SQL Server will simply use the one already in the pool, therefore by passing the slow process of establishing a new connection.

 

[dilettante]

I think you may have misunderstood.

The "bang" syntax is slower, but only in the sense that accessing most types of collection objects is slower by symbolic key value than by item ordinal.

Which of course implies that you can use ! with Collection objects as well (which you can).

I'd hate to work on a large program heavily using databases that is filled with numeric Field-ordinal uses.


I think you misunderstand connection pooling too. It hasn't a thing to do with anything happening on the database server, and for a typical VB client application you don't get connection pooling. Of course MTS/COM+ can offer object pooling if you're using that, and there is a quirky kind of ODBC connection pooling that can be turned on... if you're using ODBC and not other OLE DB providers than OLE DB provider for ODBC.

INFO: Frequently Asked Questions About ODBC Connection Pooling

INFO: How to Enable Connection Pooling in an OLE DB Application as well, but:

An OLE DB application can enable ODBC connection pooling if the OLE DB application is using the OLE DB provider for ODBC. The OLE DB application that uses the ODBC provider is like an ODBC application because the ODBC provider internally calls the ODBC API to perform data manipulation.

However none of that is any use for a typical VB client. The pooling would happen where the Connection objects live, i.e. at the client machine.

Connection pooling is almost only useful for mid-tier applications and Web applications. It doesn't help in two-tiered (or Jet MDB, i.e. monolithic) applications...

... unless your programs open several simultaneous connections. But we know not to do that except in rare cases, right?


The "open late and close as soon as possible" rule for database connections is intended to improve throughput in things like ASP pages. Where one not only does have connection pooling services available (from IIS) but you're also servicing multiple simultaneous users there in the Web-tier machine. The other case would be mid-tier COM+ applications.

One can make a case for doing this on a client with no connection pooling, but it isn't about performance and will kill performance when using a client/server DBMS like SQL Server. Connection setup is expensive there.

The main reason you'd do it is actually to improve long-term integrity of a Jet MDB! It avoids having connections open that are holding locks or dirty data pages when a client machine dies or loses network connectivity.

 

[BobRodes]

<I think after VB3 it was replaced by Public.
It was. I started with VB4, and Global was deprecated at that time.

<Holding a connection open for the life of the program... not aways so good.
Disconnected recordsets are the default in .Net, probably primarily for this reason. Connections are expensive.

By the way, bang notation is roughly equivalent in performance to string notation, e. g. [tt]myRecordset("myField")[/tt] is roughly equivalent to [tt]myRecordset!myField[/tt]. Ordinal notation is perhaps 30 to 40 percent faster. Maintaining ordinal notation is perhaps 200 to 300 percent slower.

<and why I will continue not to use it
Assuming you're in the habit of using ordinal notation, remind me to charge extra when maintaining your code.

For completeness's sake, if you set the .CursorLocation property to adUseClient, you will, upon opening the recordset, also set the .CursorType property to adOpenStatic, rendering previous CursorType and LockType (with the exception of adLockBatchOptimistic) settings superfluous.

Bob

 

[gmmastros]

Maintaining ordinal notation is perhaps 200 to 300 percent slower. ... Assuming you're in the habit of using ordinal notation, remind me to charge extra when maintaining your code.

Bob,

Have you ever tried using the ADO field object to get the data out of a recordset? Execution time is similar to ordinals, but the code is a lot more readable. Unfortunately, there's more code to read.

I won't bore you with all of the details. Assume that there is a recordset object named RS.

Dim fLabel As ADODB.Field
Dim fStartLongitude As ADODB.Field
Dim fStartLatitude As ADODB.Field
Dim fEndLongitude As ADODB.Field
Dim fEndLatitude As ADODB.Field
Dim fFeatureId As ADODB.Field

Set fStartLongitude = RS.Fields.Item("StartLongitude")
Set fStartLatitude = RS.Fields.Item("StartLatitude")
Set fEndLongitude = RS.Fields.Item("EndLongitude")
Set fEndLatitude = RS.Fields.Item("EndLatitude")
Set fLabel = RS.Fields.Item("Label")
Set fFeatureId = RS.Fields.Item("FeatureId")
    
With LineGrid(LineGridIndex)
    While Not RS.EOF
        
        .CacheCount = .CacheCount + 1
        With .StreetLines(.CacheCount)
            .FeatureId = fFeatureId.Value
            .StartLongitude = fStartLongitude.Value
            .StartLatitude = fStartLatitude.Value
            .EndLongitude = fEndLongitude.Value
            .EndLatitude = fEndLatitude.Value
            .Label = fLabel.Value
        End With
    
        RS.MoveNext
    Wend
End With

Again, the performance is comparable to using ordinal values, but it's a lot easier to read and therefore easier to maintain.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BobRodes]

No, I haven't, George. It makes complete sense that it would perform better, too, because it would parse the string-based field reference once outside of the loop and then use the resulting pointer inside the loop. Great tip!

I might add that these three lines are interchangeable:

Set fStartLongitude = RS.Fields.Item("StartLongitude")
Set fStartLongitude = RS("StartLongitude")
Set fStartLongitude = RS!StartLongitude

Bob

 

[JoeAtWork]

OK, I got confused with connection pooling because of all the ADO.NET stuff I've been reading lately. In ADO.NET opening and closing connections is the standard, and ADO.NET provides the connection pooling.

So, my apologies for misstating that.

And no, I never use ordinals because that would make my code unreadable, and I don't want Bob to gouge my clients if he has to take over my code

And the real reason I don't like bang notation: I just never really understood it. I was never sure when I could use it - none of the documentation I ever read on it explained it as clearly as in the thread StrongM referenced. Now that I finally get it, I still won't use it because dot notation feels comfortable, and quite frankly I never did like the look of exclamation points in my code!

 

[BobRodes]

I like George's code. I would hold that in reserve as a quick performance enhancer, which I could sell for lots and lots of money for very little work. Maybe to some of Joe's clients.

By the way, another performance enhancer is dynamic indexing of recordsets, as in [tt]myRs.Fields!myField.Properties("Optimize") = True[/tt]. This will create a RAM-based index on that field. I had a tough time finding that, but it made a huge difference in a lookup-intensive application I did once.