Passing SqlConnection to a class loses its connection string after deletion

Question

I have the following class definition (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 the above example, I pass the string the IDisposable test class. As soon as the code goes out of scope, it is deleted. As expected, the local TeXT variable TeXT not affected after deleting the test for the first time and is available to the second instance of the test class in the same way as it is.

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(); } } } 

As before, I pass the SqlConnection to the first instance of the test class. After removing the first instance, SqlConnection loses the connection string. making it inaccessible to the next instance of the class.

Question Why in the second case I lose ConnectionString? Is this a mistake or intentional? Is there a solution to avoid this, with the exception of a few connections to the database server? should I stop using the instance of the internal connection?

Note SqlCommand-SqlConnection Using the delete problem does not answer my question

Answer 1

You delete SqlConection when disposing of Test , but then try to use it again.

The best approach is to almost always create a new SqlConnection every time you need it, and get rid of it as soon as you are done with it. You are already doing the second part, but you must create a new SqlConnection instance for the second operation.

Please note that this does not mean creating multiple connections to the server, it is mandatory - the .NET connection pool will handle the number of actual network connections.

Answer 2

Why in the second case do I lose ConnectionString?

Because your two examples have very little in common.

In your first Dispose() class, you set Text = ""; . This will change the link that your class uses, but not the link from Main() , and its meaning . That way, TeXT from Main() will still contain your connection string.

In your second example, you call Dispose() on the same instance , namely sqlConn , which you pass from Main() .

When you Dispose() an object, this does not mean that you cannot call methods or access properties. What exactly a class does when you dispose of it varies in type. For SqlConnection it seems to set its ConnectionString property to an empty string, among other things, as returning the connection (if any) to the connection pool.