3

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

tvanfosson
  • 524,688
  • 99
  • 697
  • 795
Ace Grace
  • 631
  • 1
  • 7
  • 21

5 Answers5

5

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.

tvanfosson
  • 524,688
  • 99
  • 697
  • 795
  • Would it reuse a connection via connection pooling if within a certain time period? – Jason Down Jun 05 '09 at 15:30
  • This is orthogonal to how the connection manager handles connection pooling. Since it uses the same connection string (unless you do something different) each time, they would be drawn from the same pool. The simple fact that it's a table adapter doesn't change the way the pools are handled. If you are interested there's an article at MSDN that covers pooling, but it's not specific to table adapters (nor does it need to be): http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx – tvanfosson Jun 05 '09 at 15:35
  • Is it a value-based comparison of the connection string, or a reference comparison? – JamieS Sep 02 '16 at 14:49
  • @JamieS it uses string equality which handles both reference and value equality. – tvanfosson Sep 02 '16 at 16:58
1

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();
            }
        }
maxbeaudoin
  • 6,546
  • 5
  • 38
  • 53
0

What happens when 

Dim Dt As dataset1.UsersDataTable
With New dataset1TableAdapters.UsersTableAdapter
   Dt = .GetData()
End With

Did the connection leave open? or it closed?

Amin
  • 1,242
  • 2
  • 12
  • 25
0

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

Arsen Mkrtchyan
  • 49,896
  • 32
  • 148
  • 184
0

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.

JasonRShaver
  • 4,344
  • 3
  • 32
  • 39