Software
I have heard that when ever a database user executes a query it opens a new connection for that.
It means that as many no of users are, that many connections will be.
can any body help me control/Manage all the users through single connection.
depends. open a connection, make it persistent and then pass all db action s through it and it won't.
each application will have it's own connection, or many if you wish.
The way to do what you want it to write a service that sits in betweeen sql server and your clients. Clients connect to your service, its connects once to sql server.
Are you up on threading and TCP/IP services ?
You'll learn a lot doing this , presumably you are not talking VB6 as it's support is threading is well awful.
by silly
can u help me handle it through stored procedure
or any thing in vb6?
I had one very quick go at threading in VB, all articles I found recomended against it and they were right.
Stored proces won't help you, you need a connection to run them.
Important question why do you want to limit the number of connections ? What problem are you trying to solve?
by tjc_tek
But
This assumes while there are many users there is only one role.
Or
the web service closes the connection and opens it with a different role user.
Or
you are going to implement user management through the web service and then map to one or more db users.
Or
you are going to deal with 'permissions' through application logic and remove a very useful devloper crutch that DBs implement which would preclude certain classes of coding error.
If the first is viable, go for it, it works beautifully.
by silly
1) i want to create a DCOM in VB that will handle
multiple users
For Example:
a connection will open only when the very first user connects to a database and close only when the last user disconnects of it.
2) To reduce connection Overheads from my server.
is opening it, after that it's fairly minimal. Unless you are trying to get round a per seat limit on the DBMS.
Depends on what your clients are doing. But this is a lot of wheel re-inventing.
I would not even attempt this in VB, you are just asking for it. Without threading your solution would be more expensive than the problem and as I've stated threading support in VB is effectively non existant. This would be a real kick ass project in C#, Delphi or C++.
VB, non starter. Totally, look up threading in VB6 on google.
by admin
A lot (not all, but a lot) of the overhead of creating connections occurs on the server-side. Look up connection pooling, as it might eliminate some of the overhead. Also you might look at maintaining a set number of persistent connections and reusing them client-side. The client-server communication/handshaking and memory allocation/deallocation operations in opening and closing connections account for a lot of the connection overhead on the client side. I.e., this pattern is fairly slow (pseudo-code algorithm for demo purposes):
Loop 10 Times
::Open Connection to SQL Server
::::Insert a row in a table
::Close Connection to SQL Server
End Loop
The following is much faster than the above example, because the connection overhead is only encountered once, instead of ten times:
Open Connection to SQL Server
::Loop 10 Times
::::Insert a row in a table
::End Loop
Close Connection to SQL Server
You could extrapolate this pattern to a web service, DCOM object or whatever else that requires connection to a SQL database; i.e.:
User connects to web service
::Open connection to SQL Server
::::Insert a row in a table
::Close connection to SQL Server
User disconnects from web service
The above might run a lot more slowly than opening a connection to the SQL Server one time and using that same connection for all separate web service requests.
This discussion is closed to new replies.
Sort by Trending