Cleaning up an ADO connection.

Question

I have written a class that accesses data from an SQL database. Here is the class:

public DataSet GetData(int nCol)
string cSQLSelect = @"SELECT * FROM table";
SqlConnection conDB = new SqlConnection(GetConnString());
SqlDataAdapter da = new SqlDataAdapter(cSQLSelect, conDB);
DataSet ds = new DataSet();
da.Fill(ds, "table");
return ds;

In my windows form I'm calling this class and binding the first column to a textbox. Here is the code:

private void button1_Click(object sender, EventArgs e)
{
DataSet ds = class.GetData(11007);
textBox1.DataBindings.Add("text", ds, "table.col1");
}

This code works, the data appears in the textbox just as I expected. But here is my concern: Am I leaving the connection open by using this methodology? Apparently the data adapter does not require a call to the open method. So does the connection close automatically?

I'm new to C# programming, and I don't know the best strategy to handle this situation.

Sam Hobbs · Answer

In SqlDataAdapter Constructor (String SqlConnection) it says  This implementation of the SqlDataAdapter opens and closes a SqlConnection if it is not already open. .... If the SqlConnection is already open you must explicitly call Close or Dispose to close it.  Where does it say that SqlDataAdapter calls Dispose for the connection?

Andrew Fenster · Answer

Hi Sam You can use using to clean up your database connection as in the following code. In Ron's case however he doesn't have a database connection to clean up. The DataAdapter is doing it for him. So he doesn't need to do anything more. Database db = dm.CreateDatabase(); DbTransaction dbTran = null; using (DbConnection dbConnection = db.CreateConnection()) { try { dbConnection.Open(); dbTran = dbConnection.BeginTransaction(); // Update something here! dbTran.Commit(); } catch { if (dbTran != null) try {dbTran.Rollback();} catch {} throw; } }

Sam Hobbs · Answer

I think the using statement is designed to ensure clean-up but I am not sure. You should at least use try/catch to catch errors such as a problem with the database. Using will provide a default try/catch. I don't use the using statement much but I learned recently that it is designed to ensure proper clean-up.

Andrew Fenster · Answer

What you have looks fine. There's nothing more to do. The DataAdapter handles the connection for you. It opens it. It closes it. It cleans up after itself. The DataAdapters are designed for this very purpose and they work very well.