How do designer-generated table adapters handle connections

Question

How do table adapters make use of connections?

To explain that a bit, do they automatically open and shut connections or if I already have the connection open before calling a tableadapter method, do they use it and leave it open?

Regards

Answer 1

If you look at the designer-generated code, you'll see that if there is a connection, the adapter reuses it, otherwise it creates a new one. When executing a query method, if the connection isn't open, the method opens it. If the method opened it, it closes it when it is done. By default you get a new connection for every table adapter.

Answer 2

Here is the code of a typical designer-generated Table Adapter's function:

[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
    [global::System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")]
    [global::System.ComponentModel.DataObjectMethodAttribute(global::System.ComponentModel.DataObjectMethodType.Select, true)]
    public virtual Styles.OrdersDataTable GetOrders() {
        this.Adapter.SelectCommand = this.CommandCollection[0];
        Styles.OrdersDataTable dataTable = new Styles.OrdersDataTable();
        this.Adapter.Fill(dataTable);
        return dataTable;
    }

According to MSDN, DbDataAdapter.Fill behave like this:

The Fill method retrieves rows from the data source using the SELECT statement specified by an associated SelectCommand property. The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open.

Ref: Fill Method (DataTable)

However, in a designer-generated Insert/Delete/Update, the code would look like this:

global::System.Data.ConnectionState previousConnectionState = this.Adapter.InsertCommand.Connection.State;
        if (((this.Adapter.InsertCommand.Connection.State & global::System.Data.ConnectionState.Open) 
                    != global::System.Data.ConnectionState.Open)) {
            this.Adapter.InsertCommand.Connection.Open();
        }
        try {
            int returnValue = this.Adapter.InsertCommand.ExecuteNonQuery();
            return returnValue;
        }
        finally {
            if ((previousConnectionState == global::System.Data.ConnectionState.Closed)) {
                this.Adapter.InsertCommand.Connection.Close();
            }
        }