CODEWITHSUNDEEP
c#.netsql-server
Bistro
Bistro

Reputation: 2025

Passing SqlConnection to class loses its connectionstring after dispose

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

Upvotes: 2

Views: 2884

Answers (2)

CodeCaster
CodeCaster

Reputation: 151584

Why in second case I lose ConnectionString?

Because your two examples have very little in common.

In your first class's Dispose() method, you set Text = "";. This will alter the reference that your class holds, but not the reference from Main(), nor its value. So TeXT from Main() will still hold your connection string.

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

When you Dispose() an object, this doesn't mean you can't call methods or access properties. What exactly a class does when you dispose it differs per type. For SqlConnection, it apparently sets its ConnectionString property to an empty string, among other things like returning the connection (if any) to the connection pool.

Upvotes: 2

Jon Skeet
Jon Skeet

Reputation: 1500385

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.

Upvotes: 10

Related Questions