CODEWITHSUNDEEP
c#.netsqlconnection
Chris Pfohl
Chris Pfohl

Reputation: 19064

Why does SqlConnection forget the database type

I recently posted (and promptly deleted, when I decided the question was irrelevant to the actual problem) a question about SqlConnection losing its Database information when the scope of "ChangeDatabase" ends. Example:

    //Other code...
    dbConn = new SqlConnection(dbConnBuilder.ConnectionString);
    dbConn.Open();
    dbConn.ChangeDatabase(currentDatabase);
    dbConn.Close();
}

My questions:

  1. Is it considered bad practice to hold onto a SqlConnection object and open and close it whenever you need it when you'll only ever have ONE connection of a given type?
  2. Why does dbConn.Database not remember currentDatabase after ChangeDatabase (a method not a variable) 'Goes out of scope'? (Heck, I didn't know methods like ChangeDatabase could know about scope).

My connection string was:

Data Source=server.name.com;Persist Security Info=True;User ID=username;Password=password

Thanks guys, let me know if I can give you more information, still learning to use S.O.

Upvotes: 0

Views: 361

Answers (2)

Steven Sudit
Steven Sudit

Reputation: 19620

Calling Close() completely destroys the object, so you should not be reading any of its properties after.

In fact, there should even be an "after" because you shouldn't be calling Close(). Instead, instantiate the connection in a using block, so that it'll call Dispose(), which does the same thing as Close(), but is guaranteed to do so no matter how you leave the block.

Upvotes: 5

Joel Martinez
Joel Martinez

Reputation: 47789

So just make sure to call changedatabase every time you need to execute a statement :-)

Upvotes: 0

Related Questions