Passing SqlConnection to class loses its connectionstring after dispose

I have following simple class definition (a windows console application):

SAMPLE as Expected

  • SQL to find timespan between rows based on ID
  • What are the non-dev cases of SQL Server User Instances (vs CE and other embedded databases)?
  • How can I pull a list of ID's from a SQL table as a comma-separated values string?
  • Export query result to csv in UTF-8
  • Convert tables with “id,attribute,value” columns to “id,attribute1,attribute2,…”
  • Set zero default value for null rows in SQL Server
  • 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

  • SQL Server database on Azure UNIQUE KEY constraint crash
  • Tools for Building an OCA (Occasionally Connected Application)
  • xsd schema file must be annotated in SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class?
  • Visual Studio 2008 express not recognizing my Sql Server 2008 express
  • GO statements blowing up sql execution in .NET
  • Retrieve record counts from multiple statements
  • 2 Solutions collect form web for “Passing SqlConnection to class loses its connectionstring after dispose”

    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.

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.