.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Should I ever keep a connection open for more than 1 call if I'm not using transactions?

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server


I'm designing a Data Access layer for our team.  I have some questions.

1) If I'm going to use a connection to call multiple commands, should I keep the connection open between calls? Or open/use/close, open#2/use#2/close#2?  The "use" part will typically be using a SqlDataReader to stream the results and turn each row into a business object.

2) Is it really worth it to delay opening my connection until after I create my command, set my parameters, etc. ? 

Obviously a developer needs to remember not to do anything "slow" while reading rows or setting up parameters, but do I need to be superparanoid and truly open at the last possible second and close immediately?  If it's truly cheap to do that I can design my layer to do that.  But I've always done like this:

using (SqlConnection connection = new SqlConnection("connectionString"))
  using (SqlCommand command = new SqlCommand("text", connection))
    // call a function that uses a reader and processes the results
    // it will be building a list of business objects, and developers COULD in theory
    // forget and do something "slow" while reading the records

  using (SqlCommand command2 = new SqlCommand("text2", connection))
    // do something e

View Complete Post

More Related Resource Links

Open SQL Server Connection Troubleshooting

Hello, This is regarding ASP.Net (VB) 2.0 and SQL Server 2008.  My project's (there is only one in my solution) has a "Start Action" set to a "Specific Page" (..\Forms\frmMain.aspx).  Occasionally (though not always) a large number of connections (30+) will be opened prior to the frmMain.PreInit event.  These connections stay open for quite some time.  I am closing all of my connections with extreme prejudice.  In my "finally" clause of my try...catch I have myconnection.close.  Immediately after the "End Try" I have myconnection.dispose (redundant, I know).  I have accounted for all connections.  I placed a breakpoint on frmMain.PreInit and then (sometimes) a large number of connections (used SSMS to run sp_who and sp_who2) will have just opened.  What would open a large number of connections prior to the starting page's preinit event?  I am closing (and then disposing) all of my connections.  I thought that I must be missing a myconnection.close in the "finally" clause, but I have looked repeatedly and cannot find anywhere that a connection is open and then not closed.  Also, I do not believe that I have even opened a connection prior to the frmMain's preinit event.        

how many open connection is recommended to leave - in sql server 2008 Enterprise Edition

hi i have my C# program that work with sql-server 2008 Enterprise Edition i have 40 users that connect to this database. i dont close the connection. how many open connection is recommended to leave ? or is it better to close connection ? thank's in advance

how to open ssh / secure shell connection using vb and run commands

Hi I need to open connection for Secure shell and run commands from a remote site i am using Vb.net   i have seen many component that can use but i would like to code by my own if i could please give me your brilliant ideas

claims authenticated (FBA) user cannot open a connection to our custom application database

We have designed a custom sharepoint site. This site uses a membership provider that I implemented, and the users are stored in our custom application database. These are the steps I follow to reproduce the error: 1. Browse to our site. Select Forms Authentication from the default.aspx selection. 2. Enter in a username from our DB table. This user I have configured as a site collection administrator. 3. Login to the site. The user is authenticated by our membership provider and allowed access (ValidateUser returns true) 4. Browse to a page that attempts to connect to the custom app DB. 5. I get the following error when the page attempts to open a connection to our DB: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server NOTES: The code that opens the DB connection is running with SPSecurity.RunWithElevatedPrivileges. Our application pool account has access to the database. My user can connect to the database fine as well as the user that our  application pool is running as. I have validated the connection string that is being used in the debugger, and that is correct.

DBCC OPENTRAN does not report known explicitly open transactions

Using query analyzer, I can do simple select after BEGIN TRANSACTION, and in a separate or same session execute DBCC OPENTRAN and I get reported " no active transactions."  Tried on SS_2000 as well as SS_2008. Not sure what I am missing !   Any ideas ?      Thanks ! 

Send/open timeouts on WAS-hosted WCF service with named pipe orTCP binding, per-call instancing

We have a client that sends requests to a per-call WCF service (WAS hosted, NP binding, same machine) in a loop. The WCF service calls an external EXE to process requests. The operations on the service can take a few seconds, or a few hours. To combat this, we have sendTimeout=00:05:00, receiveTimeout=00:05:00 and a built-in circuit-breaker on the service that kills the external process after 00:05:00. Any service-side errors are returned to the client as FaultExceptions.  On the client, FaultExceptions are logged and the client proxy is aborted (proxy.Abort()). The client has configurable multithreading. In this example, it is running 3 threads, each of which creates a proxy, makes a call, and closes the proxy (or aborts it if an exception was received). Since the service is instanced as per-call, and it never receives more than 3 simultaneous calls (each of which being followed by a .Close() or .Abort() on the proxy), I would not expect the client to be getting timeouts during the send, but I am. In fact, the send timeouts I am receiving would seem to imply that the WCF service is hitting the default session limit, even though the class is explicitly marked with [ServiceBehavior(InstanceContextMode = InstanceContextMode.PerCall)]. I put some trace logging on the client and I can see that the timeout is occurring when the proxy is being created. sendTimeout seems to

Using SIP make a connection and call in Asterisk Server using c#

Hi all,

       I am new to Telecommunication but now my situation is want to use the SIP(Session Initiation Protocol) with Asterisk Server. So some one pls help me that is how i connect with Asterisk server and make a call using the SIP and c#.

SQL 2005 open connection timeout error


We have Microsoft SQL Server 2005 - 9.00.4207.00 (X64)  Enterprise Edition (64-bit) on Windows server 2003 (Build 3790: Service Pack 2) .

Periodically (~ once per 15 min) some db-clients have error messages when open connection (over SQL Native Client ) like:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

All timeouts more or equal 15 sec. Less then 15 sec is not acceptable by business rules. Performance counters (cpu,memory,disk etc.) on server does not show bottleneck. Only one sql counter - logins/sec show trouble. When problem exists then this counter = 0. Also dramatically decrease many other counters - sql batch requests/sec, transactions/sec, cpu load and some ones. SQL Server "sleep" few seconds (4 - 5) and then accessible again.




Named Pipes Provider, error: 40 - Could not open a connection to SQL Server


This was originally posted in the SSIS Forum, but a member of the IS team suggested it be moved here.  "Most recently I got this error (Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) so does this mean that SQL Server is still trying to use named pipes even if I only have TCP/IP enabled in SQL Server Configuration Manager?"

I'm having the same issue, and here's our scenario:

  1. Installed SQL Server 2005 Developer Edition on a machine with WinXP SP2 and enabled remote connections over TCP/IP
  2. Installed SQL Server 2005 Standard Edition on a machine with Win2003 SP1 (remote connections over TCP/IP enabled by default)
  3. Attempted to 'Copy Database' from Developer Edition TO Standard Edition using 'Detach and Attach' method and recieved the error on the Win2003 machine: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server
  4. A

ExecuteReader requires an open and available Connection. The connection's current state is closed.


I am accessing SQL2005 with C# code using  OleDbConnection.


A try and catch block catches the following error once a while between the Open() and Close() of the connection:

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.


I do not even have any idea where to start to debug this.  The ExecuteNonQuery() runs a delete SQL query. It works 99.9% of the time. I do not see anything wrong when this error happens. 


Any hint would be greatly appreciated.



provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

after lots  of goes and back, I have the same problem but it is related to my Visual studio too. Because I can access to the same database  for new projects but when I use an existing project with a new default page I get the same error.
I am using visual studio 2005 connecting to MSSQL 2008 in server that has an instance of SQLServer express 2005 too
the code for boht new and existing project is bellow

I appricate any help


protected void Page_Load(object sender, EventArgs e)



SqlConnection cn =

ORA-06413: Connection not open.


Hi everyone,

I have Oracle client 10g 32 bit installed on my Windows 7 64 bit machine. I am using System.Data.Oracleclient to talk to Oracle databases running on 8i and 10g server. I have my website setup on localhost as well. When I run my website in debug in visual studio 2010, it fails to open a connection and returns an error code ORA-06413: Connection not open. When I run it under localhost, it opens a connection fine and executes a query alright. But becaue I need to debug the code as it do not generate expected results I need to debug it, but am stuck due to this Visual Studio not talking to Oracle. Any ideas on how to get around this problem.


Exception thrown on client proxy call to open

Exception thrown System.InvalidOperationException: The communication object cannot be modified while it is in the Opened state.

The following code throws this exception:

                if (dataAcesssProxy == null)
                    dataAcesssProxy = new CompetellaDataAccessService.CompetellaDataAccessClient();
                    dataAcesssProxy.ClientCredentials.Windows.ClientCredential = new System.Net.NetworkCredential("someuser", "password", "domain");

                    dataAcesssProxy.InnerChannel.Faulted += new EventHandler(InnerChannel_Faulted);

implications of not closing an open connection declared locally


 Hello All.


I was wondering about something.  I'm supporting a .NET C# application which has a number of database connections declared locally as IDbConnection with MS SQL Server and NHibernate.  These connections are declared locally and not explicitly closed after a specific operation is performed.  Are these connections automatically closed after the local method is executed, or could these connections be floating around somewhere?  We're having semaphore max out problems, and I'm trying to see if this may be the culprit.  I'm doubtful, as I believe these connections would automatically close, but thought I'd throw out hte possibility.






create and open global database connection object


Hi to everyone:

I`m trying to install the allinta cms software www.allinta.com, I have already create a MySQL Data Base, but when I try to launch my cmsadmin I t appears an error

Microsoft VBScript runtime error '800a01f4'

Variable is undefined: 'dbConnectionStr'

/cmsadmin/inc/db_connection.asp, line 75

IT  seems to be the Object Connection, could somebody help me to give the right code instruction please, I don´t have an idea on how to make it.

At this moment I opened in dreamweaver and it´s

Set objConn = Server.CreateObject("ADODB.Connection")

Trying to enumerate the contents of folder ''. Can't open data connection (425).


Hello Guys,

I know there were posts before about this issue but contacting the web hoster did not help me.
The oddity is, that the same website files, copied and pasted, connection string altered to point to the
production/live db, published to the httpdocs folder works fine BUT the same web files, copied and
pasted, connection string altered to poin to the dev db, published to the subdomains/dev fodler causes the error below:

 ------ Build started: Project: AHN, Configuration: Debug Any CPU ------
   AHN -> P:\Web_Development\xxx\DEV\bin\xxx.dll
------ Publish started: Project: xxx, Configuration: Debug Any CPU ------
Connecting to ftp://xxx.co.uk/subdomains/dev/httpdocs...
Transformed Web.config using Web.Debug.config into
Copying all files to temporary location below for package/publish:
Deleting existing files...
An error occured trying to enumerate the contents of folder ''. Can't
open data connection (425).
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Publish: 0 succeeded, 1 failed, 0 skipped ==========

When I asked the hoster whats going on, he obsiously that its not his fold because the publishing is

provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server


On windows 2008 using sql 2k8 standard edition. I have a couple of apps that run on the server. One works perfectly and attaches to sql with no problem. The second app that uses THE EXACT SAME CONNECTION STRING and gives me the following error,

provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

How can one app work and the other not? What can I check for?


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend