Solved

SqlClient Multiple Connections with SQL Server 2000

Posted on 2006-11-09
8
340 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now