An SqlConnection can only support one DataReader at a time: when you "share" a connection you have to make sure that the reader is closed and disposed when you are finished with it at all times.
The best approach is not to "recycle" SQL connections: create it in a Using block, open it, use it, and let the system Dispose it when it goes out of scope.
I told you this three days ago!
Look through your entire code, and delete your "common" SqlConnection - replace it with individual ones created inside Using blocks when you need them.
And your loop is still weird: why do you close the reader at the end of the first iteration?
Quote:
should i put connection property inside using block or is there a way to put property in connection class and connect it with new connection inside using block ??????
Please do not use multiple question marks in communications with me again: it is "a sign of a diseased mind" according to pTerry - and he's right.
You can use a class to encapsulate a connection - if you must - and put teh construction code into a using block:
using (MyConnection con = MyConnection.GetConnection())
{
Console.WriteLine("Created");
...
}
...
internal class MyConnection : IDisposable
{
private MyConnection() { }
public static MyConnection GetConnection()
{
return new MyConnection();
}
#region IDisposable Support
private bool disposedValue = false;
protected virtual void Dispose(bool disposing)
{
Console.WriteLine("Disposing");
...
}
public void Dispose()
{
Dispose(true);
}
}And the system will automatically discard it when it goes out of scope.
Sorry, forgot you use VB.
Using con As MyConnection = MyConnection.GetConnection()
Console.WriteLine("Created")
End Using
...
Friend Class MyConnection
Inherits IDisposable
Private Sub New()
End Sub
Public Shared Function GetConnection() As MyConnection
Return New MyConnection()
End Function
Private disposedValue As Boolean = False
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
Console.WriteLine("Disposing")
...
End Sub
Public Sub Dispose()
Dispose(True)
End Sub
End Class
Submit an article or tip