I have following simple class definition (a windows console application):
SAMPLE as Expected
using System;
namespace ConsoleApplication1 {
class Test: IDisposable {
string Text;
public Test(string str) {
Text = str;
}
public void print() {
Console.WriteLine("Text: " + Text);
}
public void Dispose() {
Text = "";
}
}
class Program {
static void Main(string[] args) {
string TeXT = "Data Source=localhost;Initial Catalog=master;";
using (Test test = new Test(TeXT)) {
test.print();
}
using (Test test = new Test(TeXT)) {
test.print();
}
Console.ReadKey();
}
}
}
in above example I pass a string
to a IDisposable
class test
. As soon as code goes out of scope it is disposed. As expected the local variable TeXT
is not touched after disposing the test for the first time and it is accessible for second instance of test class the same way it was.
Now here is the real scenario that made me scratch my head for hours.
Actual Result
using System;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication1 {
class Test: IDisposable{
SqlConnection dbConn;
public Test(SqlConnection connection){
dbConn = connection;
}
public void print(){
Console.WriteLine("Connection" + dbConn.ConnectionString);
}
public void Dispose() {
dbConn.Dispose();
}
}
class Program {
static void Main(string[] args) {
SqlConnection sqlConn = new SqlConnection("Data Source=localhost;Initial Catalog=master;");
using(Test test = new Test(sqlConn)){
test.print();
}
using(Test test = new Test(sqlConn)){
test.print();
}
Console.ReadKey();
}
}
}
Same as before I pass a SqlConnection
to first instance of test
class. After disposing the first instance, SqlConnection
looses the connection string. which makes it unavailable for next instance of class.
Question Why in second case I lose ConnectionString? Is this a bug or intentional? Is there any solution to avoid this except making multiple connections to DB Server? should I stop disposing the internal connection instance?
Note SqlCommand-SqlConnection Using Disposing issue does not answer my question
You're disposing of the SqlConection
when you dispose of the Test
- but you're then trying to use it again.
The best approach is almost always to create a new SqlConnection
each time you need one - and dispose of it as soon as you're finished with it. You're already doing the second part, but you should create a new SqlConnection
instance for the second operation.
Note that that doesn't mean making multiple connections to the server, necessarily - the .NET connection pool will handle how many actual network connections are needed.
See more on this question at Stackoverflow