Best way to work with DataTables and TableAdapters

Question

Best way to work with DataTables and TableAdapters

James Buss 136

Over five years, my application has been slowing down because one of the main tables has a lot of rows. It's still workable, but I'd like to rewrite how I handle data to make it more efficient before it becomes unworkable. I was new to vb.net five years ago, so I probably didn't do things in the best way.

For example, on a form that displays data from the large data table, I used the default TableAdapter. That of course loads the whole table into memory and then I filter to show the records that I want on the form. I know now that I could actually customize the query in the TableAdapter so that it only pulls the records that I want. I suspect that change alone would speed things up.

I'm also toying with the idea of writing the query myself and loading it into memory using the DataTable class and connecting it to the BindingSource.

Pros and cons of each?

The bigger issue is adding new records. There is a form in the program that allows the user to add data to the table. It doesn't display any data. It's only purpose is to allow data entry and save it to the database. Since the fields are connected to the database through a BindingSource and TableAdapter, the TableAdapter is loading all records from the table before going to the new record to allow data entry. This of course slows down the whole process.

It seemed fairly simple to just use an unbound form, validate the data that is entered, and take the data from the various fields and construct an INSERT query to add it to the database. But then the question became how to handle the data in the DataGridView, especially with a parent/child relationship. The functionality built into the TableAdapter to manage all of this works well, but if I'm using an unbound form then I have to do all of that.

What do people suggest?

Xingyu Zhao-MSFT 5,381 Reputation points

2022-01-27T05:36:44.603+00:00

Hi @James Buss ,

I suspect that change alone would speed things up.

Yes, it will help speed up the data display.

The bigger issue is adding new records.

Could you provide some related code here? It will help us analyze the question.
Karen Payne MVP 35,586 Reputation points Volunteer Moderator

2022-01-27T18:06:33+00:00

Conceptually speaking, using TableAdapter, DataAdapter, untracked DataTable (and the same for Entity Framework) the key is to never load more data than needed at any one given time. Some developers will use pagination which brings a whole other set of complexities to the table when considering filters and searching.

The functionality built into the TableAdapter

Personally, always considered TableAdapter approach to be constraining, but that is just me.

Going back to DataTable containers, there is always an option to work with events and GetChanges method. The point is, more code but more control over data operations.

I would drop using TableAdapter if the project was in it's infancy stage but now that would be too much work.

James Buss 136

Hi @Karen Payne MVP

The application is still functional; I don't need an immediate fix. I can take the time to redesign it to be more efficient and then replace it. I'm thinking something like this (ignore syntax errors):

Public Class Form1  
    Dim MyAdapter As New OleDBDataAdapter()  
    Dim MyTable As New DataTable  

    Private Sub Form1_Load()  
        MyAdapter.SelectCommand = "SELECT ID, Field1, Field2, Field 3 FROM DatabaseTable WHERE ID = " & CStr(TargetID)  
        Dim MyCommandBuilder As New OleDBCommandBuilder(MyAdapter)  
    End Sub  

    Private Sub Form1_Shown()  
        LoadData()  
    End Sub  

    Private Sub LoadData()  
        MyAdapter.Fill(MyData)  
        FormBindingSource.DataSource = MyData  
    End Sub  

    Private Sub BtnSave_Click()  
        SaveData()  
    End Sub  

    Private Sub SaveData()  
        Dim ChangedData As DataTable = MyData.GetChanges()  
        If ChangedData.HasErrors Then  
            resolve errors  
        End If  
        MyAdapter.Update(ChangedData)  
    End Sub  
End Class

Seems like this might be what I'm looking for for reading and editing the data, but I don't think this would work on the data entry forms (forms that only collect new data and adds it to the database.

I see that Update() has an overload that takes an array of DataRows. I'm thinking that on the data entry form that I could use a SaveData() routine that constructs a DataRow or an array of DataRows.

Am I on the right track here?

Accepted answer

0 additional answers

Your answer

Xingyu Zhao-MSFT 5,381 Reputation points

2022-01-27T05:36:44.603+00:00

Hi @James Buss ,

I suspect that change alone would speed things up.

Yes, it will help speed up the data display.

The bigger issue is adding new records.

Could you provide some related code here? It will help us analyze the question.
Karen Payne MVP 35,586 Reputation points Volunteer Moderator

2022-01-27T18:06:33+00:00

Conceptually speaking, using TableAdapter, DataAdapter, untracked DataTable (and the same for Entity Framework) the key is to never load more data than needed at any one given time. Some developers will use pagination which brings a whole other set of complexities to the table when considering filters and searching.

The functionality built into the TableAdapter

Personally, always considered TableAdapter approach to be constraining, but that is just me.

Going back to DataTable containers, there is always an option to work with events and GetChanges method. The point is, more code but more control over data operations.

I would drop using TableAdapter if the project was in it's infancy stage but now that would be too much work.
James Buss 136 Reputation points

2022-01-28T03:44:18.743+00:00

Hi @Karen Payne MVP

The application is still functional; I don't need an immediate fix. I can take the time to redesign it to be more efficient and then replace it. I'm thinking something like this (ignore syntax errors):

Public Class Form1 Dim MyAdapter As New OleDBDataAdapter() Dim MyTable As New DataTable Private Sub Form1_Load() MyAdapter.SelectCommand = "SELECT ID, Field1, Field2, Field 3 FROM DatabaseTable WHERE ID = " & CStr(TargetID) Dim MyCommandBuilder As New OleDBCommandBuilder(MyAdapter) End Sub Private Sub Form1_Shown() LoadData() End Sub Private Sub LoadData() MyAdapter.Fill(MyData) FormBindingSource.DataSource = MyData End Sub Private Sub BtnSave_Click() SaveData() End Sub Private Sub SaveData() Dim ChangedData As DataTable = MyData.GetChanges() If ChangedData.HasErrors Then resolve errors End If MyAdapter.Update(ChangedData) End Sub End Class

Seems like this might be what I'm looking for for reading and editing the data, but I don't think this would work on the data entry forms (forms that only collect new data and adds it to the database.

I see that Update() has an overload that takes an array of DataRows. I'm thinking that on the data entry form that I could use a SaveData() routine that constructs a DataRow or an array of DataRows.

Am I on the right track here?

Answer 1

Karen Payne MVP 35,586 Volunteer Moderator

Take a look at the following code sample which I did several years ago. It's using a small set of data to keep things simple, easy for novice developers to learn from.

Add form
Add code in main form.
Sections of code use Task.Run to keep the form responsive for larger data sets

There is other code of interest that might helpful to check out too.

Have no code samples with a TableAdapter for master/details, only the following code sample.

James Buss 136 Reputation points

2022-01-28T18:00:37.48+00:00

Thanks for this. I think you're pointing me in the right direction.

I wish I could pick your brain endlessly or sit in a class that you teach. Looking at your code, I think there's much I could learn from you.
Karen Payne MVP 35,586 Reputation points Volunteer Moderator

2022-01-28T18:08:20.797+00:00

Note that I've been doing this since version 1 of the .NET Framework and can say the best way to learn is to try things out, outside of a normal project.

Go to my GitHub repositories, in the search, enter vb to filter for VB.NET code.

Share via

Best way to work with DataTables and TableAdapters

0 additional answers

Your answer