Cleaning up an ADO connection.

Ron

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.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs16y ago
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?
0
Andrew Fenster16y ago
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;
    }
}
0
Sam Hobbs16y ago
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.
0
Andrew Fenster16y ago
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.
0