Solved

SqlClient Multiple Connections with SQL Server 2000

Posted on 2006-11-09
8
342 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

813 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

10 Experts available now in Live!

Get 1:1 Help Now