Solved

SqlClient Multiple Connections with SQL Server 2000

Posted on 2006-11-09
8
343 Views
Last Modified: 2008-02-01
I have a report engine that monitors a Queue table.  It runs a Select TOP 1 Query every 2-3 seconds.  What I have done in the past with ADODB was open a global connection for the program and leave it open for the duration of the .EXE lifetime (it never caused resource issues in the past).  I'm wanting to migrate this to use SqlClient through the .NET Framework 2 and I've found that with SQL Server 2000 it doesn't support multiple connections if the connection has one open reader without opening more connection objects?  

Is there a way to get around this with 2000 as the DB Server.  I could open a new connection and close it every 2-3 seconds but wouldn't that be less efficient than opening a single connection that's just reused over and over again?
0
Comment
Question by:IUFITS
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 16

Expert Comment

by:muzzy2003
ID: 17908012
Don't use a DataReader. This holds the connection open until it is closed and disposed. Instead, use the methods to populate a DataSet (you'll need a SqlDataAdapter, and its .Fill method). This will allow each call to reuse a connection.
0
 
LVL 28

Assisted Solution

by:strickdd
strickdd earned 200 total points
ID: 17908017
You can dispose of the reader object and create a new one each time.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17908139
Use connection pooling.

Bob
0
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 28

Assisted Solution

by:strickdd
strickdd earned 200 total points
ID: 17908182
Connection pooling will establish multiple SQL connections each time you connect though.
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 300 total points
ID: 17908215
Here is some reading about connection pooling:

SQL Server Connection Pooling Myths
http://www.sql-server-performance.com/sk_connection_pooling_myths.asp

Connection Pooling with ASP
http://www.15seconds.com/issue/970531.htm

<Quote> Why Use Connection Pooling?

Connection pooling is used to enhance the performance of executing commands on a database. Before executing a command a connection to that database needs to be established. Sometimes creating and tearing down the connection is more costly then executing the command. For this reason connection pools are created to keep connections alive. After a connection is created it is placed in the connection pool. Connections are then used from the pool so that a new connection does not need to be established. If all the connections in the pool are being used, new connections are created and made available through the pool.

Connection pooling is extremely useful when used with applications that do not have a state.  State is a presence between instances. Active Server pages are consider stateless since they do not share data between themselves. Stateless applications benefit from connection pooling since they can not hold a connection open by themselves.

Connection pooling is also useful for sharing resources. An application can keep a connection alive, can reusing the connection multiple times to execute commands without connection pooling. But, if that application has many instances, the available resources on the machine to handle the connections will be reduced. If that application uses connection pooling, the connection resources are shared between instances of the application. With connection pooling, less connection resources are needed.</Quote>

Bob
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17908227
In order to reuse a DataReader, the connection needs to be opened and closed anyway, so I fail to see the benefit of using one.

Bob
0
 
LVL 3

Author Comment

by:IUFITS
ID: 17908298
If it helps, for my context my report engine is a VB.NET application that's running on an application server 24/7.
0
 
LVL 3

Author Comment

by:IUFITS
ID: 17915128
Here's what I did.  I moved the database to a SQL 2005 server and added the "MultipleActiveResultSets=True" which allowed the connection to have a data reader open and then also execute other data readers (or in my case, non query commands associated with the data processing I was doing on the original record set).  I kept running out of memory and realized that I wasn't disposing of my command objects, once I handled that properly it appears to be working great.  I'm running a load test now both monitoring the local memory/cpu usage as well as the load it places on the server.

I'm going to accept the answers that helped get me thinking in the right direction, thanks for all who helped!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question