locked
How to avoid open/close connections between tableadapter fills? RRS feed

  • Question

  • I have a form with several combo boxes that show items from their respective lookup tables. To load the tables in the first place, I do this.

    private void LoadLookupTables()
    {
    	neededByTableAdapter.Fill(dsRequests.NeededBy);
    	tasksTableAdapter.Fill(dsRequests.Tasks);
    	requestStatusTableAdapter.Fill(dsRequests.RequestStatus);
    	assigningFulfillersTableAdapter.Fill(dsRequests.AssigningFulfillers);
    	enteringFulfillersTableAdapter.Fill(dsRequests.EnteringFulfillers);
    	fulfillersTableAdapter.Fill(dsRequests.Fulfillers);
    	workUnitsTableAdapter.Fill(dsRequests.WorkUnits);
    }

    It seems to me that the process would be a little faster if the database were not, as I imagine it is, opened and closed with every .Fill call. But I don't know how to do that. Any ideas?

    Or do I have to fill the DataTables with DataReaders instead?

    Thursday, December 19, 2019 3:43 AM

Answers

  • It depends on what you're doing. In most cases SqlDataReader is the better choice.

    With a SQLDataReader, you are "streaming" data from the database. With a SQLDataAdapter, you are extracting the data from the database into an object that can itself be queried further, as well as performing CRUD operations on.

    Streaming is MUCH faster, but you can only process one record at a time. With a SQLDataAdapter, you have a complete collection of the matching rows to your query from the database to work with/pass through your code.

    If you use a SqlDataReader you should properly dispose the connection.

    example

    using(SqlConnection connection = new SqlConnection("connection string"))
    {
        connection.Open();
    
        using(SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection))
        {
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        //do something
                    }
                }
            } // reader closed and disposed up here
    
        } // command disposed here
    
    } //connection closed and disposed here



    william xifaras

    • Marked as answer by Robert in SF Thursday, December 19, 2019 5:56 PM
    Thursday, December 19, 2019 11:04 AM

All replies

  • Hi Robert in SF,
    As VirtualSurfer's test shows, it has impact on performance if the connections are opened and closed every time.
    So you can try to open and close the connections manually by using the

    TableAdapter.Connection.Open();
    TableAdapter.Connection.Close();

    More details you can refer to this document
    [TableAdapters: Opening and closing connections, performance hit?]
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, December 19, 2019 6:04 AM
  • It depends on what you're doing. In most cases SqlDataReader is the better choice.

    With a SQLDataReader, you are "streaming" data from the database. With a SQLDataAdapter, you are extracting the data from the database into an object that can itself be queried further, as well as performing CRUD operations on.

    Streaming is MUCH faster, but you can only process one record at a time. With a SQLDataAdapter, you have a complete collection of the matching rows to your query from the database to work with/pass through your code.

    If you use a SqlDataReader you should properly dispose the connection.

    example

    using(SqlConnection connection = new SqlConnection("connection string"))
    {
        connection.Open();
    
        using(SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection))
        {
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        //do something
                    }
                }
            } // reader closed and disposed up here
    
        } // command disposed here
    
    } //connection closed and disposed here



    william xifaras

    • Marked as answer by Robert in SF Thursday, December 19, 2019 5:56 PM
    Thursday, December 19, 2019 11:04 AM
  • Thanks, but that works if you are using just one table adapter with multiple fill methods. For example,

    myTableAdapter.Connection.Open();
    myTableAdapter.FillFoo(dataset, table);
    myTableAdapter.FillBar(dataset, table);
    myTableAdapter.FillGoo(dataset, table);
    myTableAdapter.FillBuzz(dataset, table);
    myTableAdapter.Connection.Close()

    In that case, it works great because the connection is only opened and closed once. But in the case of multiple table adapters, it doesn't save any time.

    Thursday, December 19, 2019 5:55 PM
  • Sounds like a much better approach. These are lookup tables that don't change once loaded, so they just need to be read in. I had not gotten involved with connections and commands because data sets and table adapters conveniently hide all that, but I'll give it a try. Thanks!

    Update: Wow, a huge improvement. Went from 630 milliseconds to only 95.

    var conn = new OleDbConnection(Properties.Settings.Default.LSOfficeConnectionString);
    conn.Open();
    var cmd = new OleDbCommand();
    cmd.Connection = conn;
    cmd.CommandText = "SELECT ID, NName FROM NeededBy";
    DBLookupTables.dsRequests.NeededBy.Clear();
    DBLookupTables.dsRequests.NeededBy.Load(cmd.ExecuteReader());
    cmd.CommandText = "SELECT ID, TName FROM Tasks";
    DBLookupTables.dsRequests.Tasks.Clear();
    DBLookupTables.dsRequests.Tasks.Load(cmd.ExecuteReader());
    cmd.CommandText = "SELECT ID, SName FROM RequestStatus";
    DBLookupTables.dsRequests.RequestStatus.Clear();
    DBLookupTables.dsRequests.RequestStatus.Load(cmd.ExecuteReader());
    cmd.CommandText = "SELECT ID, FName FROM Fulfillers";
    DBLookupTables.dsRequests.AssigningFulfillers.Clear();
    DBLookupTables.dsRequests.AssigningFulfillers.Load(cmd.ExecuteReader());
    cmd.CommandText = "SELECT ID, FName FROM Fulfillers";
    DBLookupTables.dsRequests.EnteringFulfillers.Clear();
    DBLookupTables.dsRequests.EnteringFulfillers.Load(cmd.ExecuteReader());
    cmd.CommandText = "SELECT ID, FName FROM Fulfillers";
    DBLookupTables.dsRequests.Fulfillers.Clear();
    DBLookupTables.dsRequests.Fulfillers.Load(cmd.ExecuteReader());
    cmd.CommandText = "SELECT ID, UName FROM WorkUnits";
    DBLookupTables.dsRequests.WorkUnits.Clear();
    DBLookupTables.dsRequests.WorkUnits.Load(cmd.ExecuteReader());
    cmd.Dispose();
    conn.Close();
    conn.Dispose();
    

    • Edited by Robert in SF Thursday, December 19, 2019 11:55 PM
    Thursday, December 19, 2019 6:00 PM
  • An addition for anybody else reading this. It turns out that retrieving data is not always faster with a DataReader. Here, I create an instance of an object that contains table adapters, and binding sources for a particular table. The table in the object usually contains many rows, the results of a query, but in this case, it contains a single row.

    Using DataReader --

    var conn = new OleDbConnection(Properties.Settings.Default.LSOfficeConnectionString);
    conn.Open();
    var cmd = new OleDbCommand();
    cmd.Connection = conn;
    var bs = db.requestsBindingSource as BindingSource;
    var drv = bs.Current as DataRowView;
    cmd.CommandText = "SELECT * FROM Requests WHERE ID = " + drv.Row.Field<int>("ID").ToString();
    var singleRequest = new DBRequests();
    singleRequest.dsRequests.Requests.Load(cmd.ExecuteReader(CommandBehavior.SingleRow));
    conn.Close();
    conn.Dispose();
    cmd.Dispose();

    and here is using TableAdapter.Fill

    var bs = db.requestsBindingSource as BindingSource;
    var drv = bs.Current as DataRowView;
    var singleRequest = new DBRequests();
    singleRequest.requestsTableAdapter.FillByID(singleRequest.dsRequests.Requests, drv.Row.Field<int>("ID"));
    
     

    They both take the same time to execute (~90 ms on my machine), plus the TableAdapter.Fill uses less code in this case.

    It would seem the performance penalty is mostly in the opening and closing of the database. If you're filling multiple tables from the same database, then DataReader lets you open the connection just once. But if you're filling a single table, you're going to open the connection just once anyway, so you might as well use a Table Adapter.

    Friday, December 20, 2019 4:39 PM