How do designer-generated table adapters handle connections

Question

Hi,

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

yea, tableadapter is leave the connection open if you opened it before and pass to adapter, and open and closa adapter if you pass closed connection, or leave the default conenction

Answer 2

You can do it like this:

using (var MyConnection = new SqlConnection("Connection String Here"))
{
   var MyDataAdapter = new SqlDataAdapter("Select * from [stuff]", MyConnection);
   MyDataAdapter.Fill(MyDataSet);
}

or

using (var MyConnection = new SqlConnection("Connection String Here"))
{
   var MyDataAdapter = new SqlDataAdapter();
   var SelectCommand = MyConnection.CreateCommand();
   SelectCommand.CommandText = "select * from [stuff]";
   MyDataAdapter.SelectCommand = SelectCommand;
   MyDataAdapter.Fill(MyDataSet);
}

As for the connection lifespan, if it is not opened, it will open and close it for you.

Answer 3

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 4

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();
            }
        }