SQLConnection  
Author Message
LotusGuru





PostPosted: .NET Framework Data Access and Storage, SQLConnection Top

Hi there,

I use Visual Studio 2005, .net Framework 2.0.50727 and Visual C# 2005

In my application, I’ve about six objects form six different classes and a connection object that connect to SQL server 2005. Those six objects use the single connection object. Out of the six obj, some uses Adaptor.Fill method and others use ExecuteNonQuery method. For ExecuteNonQuery, I manually open and then close connection object. The connection is used by the objects at least once in every two seconds. When I run the application, some time its throws an error, "connection is being used by a data reader which must be closed."

Here I'm not sure what the best practice in using the connection is

1. Do I've to use different connection for each object

2. Do I've to manually open and close the connection for Adapotor.Fill Method

Please let me know what's the best way of using the connection for this situation

Thanks,

Antony



.NET Development10  
 
 
Thilak Nathen





PostPosted: .NET Framework Data Access and Storage, SQLConnection Top

1. Not necessarily, just make sure you open, use it, then close it for each connection.

2. Yes, I believe, you have to


 
 
Bill Lin - MSFT





PostPosted: .NET Framework Data Access and Storage, SQLConnection Top

1. It's recommend to do so. Connection pooling will handle the overhead.

2. No. You don't need to open a conneciton to do the Adapter.Fill().