Persistent Connection

[Nandov]

Hi
I have a dll installed at COM+. I exported it to an application proxy and after installed in Windows 98 clients.
In this dll there is a Connection Class that connect the SQL Server, and return a Connection Object.
I need to maintain a persistent Connection in this Component, for the other classes use, without to connect again.
I try to use a Connection Global Variable in a Module, but it didn't work.
How can I make it?

Thank you, Nandov

 

[SemperFiDownUnda]

Don't!
Unless you need to maintain 1 transaction across all the calls then you should have the other objects create and destroy their connections.

This might sound bad but Component services doesn't actually disconnect the connection object but holds it for about 3 minutes incase another object asks for a connection of the same type (server,database, userid, password,...)
Global variables in a library are only global to that instance...sounds weird but in multi user setting there are seperate global variables. (you can use tricks to get around this)

But basically without getting into a deep enterprise development discussion create and destroy your connections as needed. Connection Pooling will work so that only the first call will wear the time to create the connection. This is assuming that you make at least 1 call every 3 minutes. This also lets multiple users use the same connection if needed. If you have only 1 licence for SQL then there is a possiblity that a client may block for a small time waiting for the connection to free up but you'd get that with the method you where talking about anyway.

let me know if you need me to explain it more.

 

[Nandov]

Hi
The problem is that the application is running in an Intranet and each user has a Sql user. So the database is the same, but the user is not.
If I'll have a Modelue inserted in a dll with a Global Connection Variable declared, and if in the first time I connect the Database, I keep the Connection in this variable. Will I have an Connection for each user?
I tried it, but there was an error.
Is there a way of to use a connection pooling in this case?
To use the connection pooling, I need to wirte a special code in dll?

Thank you, nandov

 

[SemperFiDownUnda]

Your application is a 3 tiered app?
In a three tier application your UI (a VB program or your ASP pages) should call your COM objects. Security can be done at this level. If you need auditing then that should be built into your table design anyway. The COM object then should all use the same loggin to the SQL server.

Client 
Computer     COM Server                    Database Server
Client A ---->Object B----connection C----> SQL
                        | 
Client B ---->Object B--|

In the above situation you would creat and destroy "connection C" as needed. Without connection pooling what you would see is a nice lag on every call while "Object B" established a connection to SQL. But with connection sharing (MTS/Component Services does this for you) "connection C" is not really closed even after an instance of "Object B" is done. "COM Server" holds the connection open waiting for another open to ask for it then passes the already open connection over to it. If "connection C" is in use by another object somewhere then "COM Server" will attempt to create another connection with the same information then there will be 2 "connection C" in the pool. After about 3 minutes of not being called by an object "COM Server" will release that instance.

In a 3 tiered system there is often little to no reason for the end user to have their own login to the SQL Server.

 

[Nandov]

The application is 3 tired
As I can see I should connect and disconnect the COM Object of Sql Server.
But I think that the time of 3 minutes to free the connection is insuficient.
There are only 30 users, that don't use the app in the same time.
Would I set this time?

Thank you, nandov

 

[SemperFiDownUnda]

Hmmm ok ... so you don't have a high hit rate ..... The you are not going to benifit from connection pooling much...

Anyway connection pooling is controlled by ODBC Data Source Administrator (probably under CONTROL PANEL->ADMINISTRATIVE TOOLS->Data Sources (ODBC).
Select the Connection Pooling TAB and then find your driver and double click on it. That is where the timeout is located.

 

[Nandov]

Ok, I'll try to set this property.
What I need to know the time that is good for this.
I have another problem. I hope your help...
When dll is running a long process at COM+, and I click on the screen, the program client show a message:
"this application is busy ... click retry or Switch ..."
Is There a way to this message does not appear?

Thank you, Nandov

 

[SemperFiDownUnda]

Yup there is ... the problem is that VB uses synchronous(spelling) calls. You could use some tricks to get your call to be asynchronous. But this is a change on how you deal with stuff. For example since control comes back right away but the task isn't finished you have to either assume that it works or you have to build in a notification system that it finished with the status. You'd have to stop them from doing anything to that data (like viewing) becuase it may not be there yet.

MSMQ is one easy way to impliment asynchronous calls. Another way is you could create a service that is also a DCOM object(s). You make calls to a proxy method that stores the parameters and returns right away. The server has a timer that then kicks off, grabs the variables, and does the processing possibly notifing the client when it is done. Its a big shift on the client and server side doing this though.

With the connection time out remember 1 thing. If you don't make a call atleast once every 3 minutes then delaying the client by 2 or 3 seconds for a call isn't that big of a deal. 2 or 3 seconds every 180 seconds (or more) isn't much. Connection pooling has a benifit when you make a call often. Lets look at a call every second. That is 180 calls every 3 minutes, saving 1-2 second per call you save 3-6 minutes. Which is huge....1/180 compaired to 180/180 or .00555 compaired to 1.

Maybe just leave it at 3 and let the connection be established each time.