How to Query and Select a Record using a Dataset TableAdapter in Visual Basic

Question

UPDATE:

So I was able to make some progress, I got this code to work....I was having trouble evaluating a FOUND condition, so wrapped it in a For Loop, what do you all think?

Dim ControlRow = NewBenefitsDataSet.Tables("FO_HealthcateHighways_Control").Select("NGID = 'HCHRXMEDTIP'")

    For Each Row As DataRow In ControlRow
        Do
            'Check if Already Processed, if so skip to next record
            If Row("NGID") = "HCHRXMEDTIP" Then
                MessageBox.Show("FOUND: " + Row("NGID"))
                Exit Do
            Else
                MessageBox.Show("GROUP NOT FOUND: " + Row("NGID"))
                Return False
            End If
        Loop
    Next

---

First of all I love this community and I've been really struggling a bit trying to get the proper syntax from my transition from Visual Foxpro to VB. No matter how much I search, I see a dozen different variations and examples, but finding it hard to find the magic combination for what I'm doing.

So very simply, my Dataset and Adapter are already setup in my Form.XSD and from within my Code.VB I want to query a record, but am not sure how to code it properly....

What am I doing wrong here? Thanks again in advance.

    Dim dtControl As NewBenefitsDataSet.FO_HealthcateHighways_ControlDataTable
    Dim drControl As NewBenefitsDataSet.FO_HealthcateHighways_ControlRow
    Dim daControlTableAdapter As New NewBenefitsDataSetTableAdapters.FO_HealthcateHighways_ControlTableAdapter

    'Fill Control Table Dataset
    daControlTableAdapter.Fill(NewBenefitsDataSet.FO_HealthcateHighways_Control)

    'Try to Query the DataTable (Intelisense tells me Variable is used before assigning a value)
    Dim cThisGroup() As DataRow = dtControl.Select("NPID = HCHRXMEDTIP")

    'So then I tried this variation (tells me Select is not a member of the Adapter)
    cThisGroup = daControlTableAdapter.Select("NPID = HCHRXMEDTIP")

So I think I'm close, just haven't been able to find a good example to code it properly.

I'm learning more each day, but still feel dumb trying to do these simple tasks.

Answer 1

Yes, you’re a little confused, but it's very easy to see why and I remember clearly when I was in the same position a few years back

A DataSet is a collection of DataTable objects. It also contains and manages DataRelations

A DataTable is a collection of DataRow objects and a collection of DataColumn objects.

A DataRow is a collection of values, strings, integers, datetimes etc. DataColumns serve to constrain a particular value on a DataRow to a type. You can have a DataRow and ask it for the data referenced by some DataColumn

———-

None of this has anything to do with a database like SQL Server or Access (or Foxpro ;) ). Databases have tables, tables have rows, rows have cells, cells have values, columns are typed etc... So it does kinda look like a DataSet and a database are the same/similar things but it's important to remember that they're just modeled on the same concepts and have similar names for the bits that make them work

So the DataSet/DataTable/DataRow/DataColumn infrastructure mimics how databases are laid out but you must be absolutely clear in your mind: they are not databases. You can create a DataSet, with DataTables, and DataRelations, and load the tables with data, and save/load from disk without ever looking at a database like SQL Server. A DataSet is a local cache of data, a suite of objects whose design intent is to store data locally in your app and allow some database like behavior, not because they’re trying to be databases but because the behaviors allow for sensible modelling of related data, and by mimicking a database it then means that they make a suitable local cache of parts of your database data - you can have both a dataset and a database, and download some portion of data from the db into the dataset, edit it, send it back, add new data to the ds, send that too..

It's a smart idea to split any app into layers, having a layer that purely deals with saving data somewhere and modelling it. DataSets/Tables/etc achieve this because you can have a set of tables that have names that don't even match with the db, but the info for how to map fro mone to the other is all there - Your Person datatable can have a FamilyName column that is mapped to the Surname column in the db. Some years later someone decides to move to a new DB server, rename the columns etc, you change the mapping in the dataset so that FamilyName now refers to a column called LastName. Your code carries on using FamilyName, and where the set once mapped that to a db column of Surname, it now uses LastName. This is why we layer things; to minimize changes needed to be just within that layer

––––

Now, there exists in the framework a device called a DataAdapter - it knows how to use a DbCommand/DbConnection to fill a DataTable from a database table. It’s an abstraction over a DataReader (which doesn’t know about DataSets/Tables).. if you accessed your db with a DataReader you’d have to poke the data into your DataTable yourself in a loop, which is very tedious. Think of DataReader as lowest of the low; a bit like wanting to write a 3D game and having to draw on the monitor yourself instead of using something like OpenGL. They have their uses but mainly it's if you want a fast, read only access to data and you're not looking to store the result. For example, imagine generating a CSV on the fly from a db table and immediately writing the CSV data to a network connection. You don't have to cache the whole 1Gb table from the DB and take a load of server memory before you make the CSV and send it; you do it line by line on the fly in a few Kb of memory

A DataReader code to pull a person out of a db and put them in a datatable might look like this:

Dim r = connection.ExecuteReader("SELECT * FROM Person")
While(r.HasRows)
    Dim dt = myDS.Tables("Person")
    Dim ro = dt.NewRow()
    ro("Name") = r.GetString(1)
    ro("NumberChildren") = r.GetInt(2)
    ro("BirthDate") = r.GetDateTime(3)
End While

Yeah. I'd prefer to stick pins in my eyes than do that for a living. I wouldn't even give it to the junior dev

---

A step up from the lowest of the low; A DataAdapter reduces the selecting and updating code to a few lines but it still operates in a very generic way:

Dim dt as New DataTable
Dim da as New DataAdapter("SELECT...","connstr")
da.Fill(dt) 'it'll autocreate columns etc

By generic I mean your data in the table is stored as a base Object; not even as sophisticated as being a String. You call this:

myDataTable.Rows(0).Item("Name")

Its an Object. It's a string inside an object but to use it as a string you have to cast it. And you need to access it by using a string "Name". Intellisense won't help you if you typo it; you'll just get a crash saying "'Naem' is not a member of this datatable". So all this is still really low level, and we’re accessing rows like they’re arrays, having to remember that the first name is at position 1, or using a string of the column name and casting:

myPerson.Name = DirectCast(myDataTable.Rows(0).Item("Name") as String)
myPerson.BirthDate = DirectCast(myDataTable.Rows(0).Item(2) as DateTime)

What a headache; this isn't really what we signed up for with a modern type safe language like VB/C# etc, the code is ugly as sin and we don't get any help with the code from VS; Intellisense is no good at suggesting column names if they’re in strings

So... Super that datasets et al are these sophisticated data caches but it kinda feels like a backward step where everything is an object and accessed by giving a string. We could write some boilerplate, by extending a DataRow and making it custom:

Class PersonDataRow Extends DataRow
  Property fullName() As String
    Get
      If this.Item("Person") IsNot Nothing Then
          Return DirectCast(this.Item("Name"), String)
      Else
          Return Nothing
      End If

    End Get

Then we could say:

myPerson.Name = myPersonDataRow.Name;

Or even better; just use the PersonDataRow as the entity in our program that stores people and ditch the other Person class all together. We could spend a few days writing all this boilerplate for each project and never have messy code elsewhere

If only there were a layer on top of this that wrote all this boring boilerplate for us..

——————

Enter the DataSet designer, what you called an XSD. This is a visual device built into visual studio that can connect to a database, and help you make custom DataTables with all this boilerplate already done and It’ll create things called TableAdapters (which are a wrapper around a basic DataAdapter) to push data back and forth between the db and the dataset.

So here's the kicker; these DataTabkes the DataSet designer makes aren’t basic low level ones like I discussed earlier; they have these extra properties and functions that do things at a higher level of data management. Every column in the table has a property that casts and returns the value from the underlying row. Instead of having a DataTable that is a collection of DataRow that has a load of objects that you have to cast back to strings and datetimes etc you’ll have a PersonDataTable that has PersonDataRows and PersonDataRows have properties that Intellisense can read, like Name and Birthdate. And it's all written with a few clicks and picking some names, datatypes etc and what happens when values are null. Then your code can go from:

Dim bd as DateTime = DirectCast(myDT.Rows(0).Item("birhdate"), DateTime) 'generic weak typing, note the typo!

To

Dim bd as DateTime = myPersonDt(0).Birthdate 'first row, get the name

With strongly typed datasets the code neatens up; there is a .Birthdate property of a PersonDataRow that pulls the underlying date out of the row, casts it for you and returns it.

If you have a strongly typed dataset try never to drop into weakly typed mode, ie don't do this:

Dim myDT = myDs.Tables("Person")

Because you'll have to cast it to a PersonDataTable (it is already a PersonDataTable but it will be boxed up as a plain DataTable) to make the most of it:

Dim myDT = DirectCast(myDs.Tables("Person"), PersonDataTable)

Starting to get needlessly messy, needlessly because... you guessed it.. there's a property of a strongly typed DataSet that will return the PersonDataTable as a full proper PersonDataTable type:

Dim myDT = myDS.Person

You don't even need to make the temp variable.. it's usually going to be cleaner and neater to just reference the DataSet property directly:

ForEach x as PersonDataRow in myDS.Person

In the same vein, don't access the .Rows property - it returns a collection of a DataRow, not a collection of PersonDataRow. Any time your Intellisense tells you the property you're accessing returns a plain DataTable, DataRow etc have a look instead for the named property that returns the strong type:

myDS.Tables(0).Columns("Name") 'no; Tables(0) returns a DataTable - we just dropped to base types
myDS.Person.Columns("Name") 'no; we started well, got a PersonDataTable type out vis the .Person property of the dataset, but then went and accessed Columns("Name") which returns a DataColumn so we're back in base type land
myDS.Person.NameColumn 'yes; we stayed with the named properties all the way

I keep coming back to the same thing here- try as hard as you can to avoid dropping out of using the strongly named properties of your strongly typed dataset. There's no benefit of going back to the low level

---

I mentioned TableAdapters earlier. They're a DataAdapter on steroids. They're designed to push data back and forth between a database and an existing or a new strongly typed DataTable. They're typed and paired with one kind of DataTable - a PersonDataTable has a matching PersonTableAdapter.

When you make a TableAdapter, you do it via a wizard on a DataSet designer, unless you dragged a representation of a database table out of a data sources window and into a DataSet (in which case it's made with some default assumptions)

In the wizard you typically type a select query that picks the columns or a subset of them from the db, and the wizard crafts a local DataTable representing what you queried. At this point no database has moved out of the database, the wizard has simply looked at your SELECT id, name, age, testpassdate FROM person WHERE id = @id query and gone "ok so that's a guid, a string, an int, a date, from a table called person, it's looking up on id which is a guid, now I have enough info to make a DataTable with 4 properties of those types, and populate a parameters collection for querying the db that uses a guid.. and because the primary key column is selected I can also generate updating and deleting queries..."

So you end up with a PersonTableAdapter that wraps around an internal DataAdapter, it takes a PersonDataTable for fill and update operations, and you can use it in your code like this:

'SELECT ... FROM Person WHERE id = @id
personTA.Fill(myDS.Person, SOME_GUID_HERE)

This is the manifestation of the query that we used to create the adapter in the first place. We finished the wizard, based on that query; it made a few queries for selecting and updating and loaded them into the tableadapter. You can see them by debugging your app and checking out the .SelectCommand, .UpdateCommand, .InsertCommand etc

So what if you wanted to query people from the db by name? This one only does by ID.

Right click the TableAdapter in the DataSet, add a query of SELECT whatever FROM person WHERE name =@name and tell the wizard you want to call it FillByName. In code do:

personTA.FillByName(myDS.Person, "John Smith")

Eventually you'll get together all the different queries in the different tableadapters to make your app work. Some of my tableadapters have 20 or more queries. They select the same data from the table, but by different criteria. Some even use joins, like FillChildrenByParentName:

SELECT child.* FROM person parent INNER JOIN person child ON child.ParentID = parent.ID WHERE parent.Name = @name

Because we select all the child records only (child,*), we still have a set of columns from Person with no extra parent info. It's stil la valid set of data that will fit in a PersonDataTable and it means the datalayer can meet the business ned of "the user shall be abl to retrieve a list of all children belonging to a person named X"

Important point: TableAdapters have Insert/Update/Delete functions (if you ticked GenerateDBDirect methods" in the advanced settings of the wizard) but these aren't really intended for direct use unless you're deleting/updating data that you never downloaded. The function that saves data to a DB with a tableadapter is called Update but it

1. Takes a DataRow, collection of DataRow, or a DataTable
2. Looks at the datarow state (Added, Modified, Deleted etc) to work out if it should run INSERT, UPDATE or DELETE
3. Runs the appropriate SQL

I wish they'd called it Save, but it's Update - just remember that you download data into a datatable with Fill. You modify the datatable, you add to it, you delete from it and when you want to persist the changes you myTableAdapter.Update(theDataTable)

---

Now, a quick critique of your code..

''Don't need this, but you'd benefit from renaming the DataTable to have a shorter name like NBDS.Control
Dim dtControl As NewBenefitsDataSet.FO_HealthcateHighways_ControlDataTable

''Don't need this either
Dim drControl As NewBenefitsDataSet.FO_HealthcateHighways_ControlRow

''Again, call your dataset Nbds perhaps, and call the TA ControlTableAdapter
Dim daControlTableAdapter As New NewBenefitsDataSetTableAdapters.FO_HealthcateHighways_ControlTableAdapter

''Caution.. this probably my fills the whole database into the dataset
''Don't do this; put parameters into the query to restrict the data that comes down from the db     
''ie add a query to the TA of SELECT * FROM control WHERE NPID = @npid daControlTableAdapter.Fill(NewBenefitsDataSet.FO_HealthcateHighways_Control)

''Then do this
daControlTableAdapter.FillByNpid(NewBenefitsDataSet.FO_HealthcateHighways_Control, "HCHRXMEDTIP" )

Then you had these attempts

'Try to Query the DataTable (Intelisense tells me Variable is used before assigning a value)

Dim cThisGroup() As DataRow = dtControl.Select("NPID = HCHRXMEDTIP")

Intelisense said this because you declared dtControl as a typed variable but you didn't give it a value. Vb isn't really a great language in this regard because it's easy to get confused between MyDatset.PersonDataTable which is a type of PersonDataTable in the MyDataSet type of object, and MyDataSet.Person which is an instance of a PersonDataTable called Person, inside an instance of a MyDataSet type called MyDataSet

Confused? It's entirely vb's fault for allowing us to create object instances where the variable has the same name as the type, and then the windows forms teams fault for making new dataset instances on a form have the same name as the type. Other languages like c# don't let you make an instance of a type with the same name as the type

Golden rule, rename all your things on your form after you add them! When you drop a dataset on your form vb does

Dim MyDataSet as New MyDataSet

Rename the name of the dataset on the form so it's called myDS etc, so that behind the scenes vb is doing

Dim myDS as new MyDataSet

This means you'll never get confused between the type of the object and the name of the instance of the object

If you had done that, and then written this:

 Dim dtControl As NewBenefitsDataSet.FO_HealthcateHighways_ControlDataTable
= myNBDS.FO_HealthcateHighways_Control

It would have worked. Actually this works:

Dim dtControl As NewBenefitsDataSet.FO_HealthcateHighways_ControlDataTable
= NewBenefitsDataSet.FO_HealthcateHighways_Control

But it's confusing as *&!# because the first NewBenefitsDataSet is a TYPE and the second NewBenefitsDataSet is an INSTANCE and he two are very different things. Always avoid naming variables the exact same name as their type

'So then I tried this variation (tells me Select is not a member of the Adapter)
cThisGroup = daControlTableAdapter.Select("NPID = HCHRXMEDTIP")

It's true, tableadapter don't have functions called Select unless you tell it you want one by deleting the suggested "FillBy" in the wizard and writing "Select". The default name for a fill operation that has parameters is fillBy. I recommend you always edit the name to include what it fills by, like when I called mine FillByName. I don't recommend you use select, because it won't help you maintain a mental distinction between a DataTable (cache of local data), database(store of remote data) and the role of a tableadpater.. and it won't help because You'll have a sub called select that fills a provided DataTable, so it's filling not selecting.. Calling thing a names that don't reflect what they do is another fast way to confusion

---

A note on select: Select is a function of a DataTable that will search the data cached in the DataTable. It doesn't hit the database. You can realistically only use it to find data in the db if you've downloaded he whole db table into the DataTable.. don't do this. Craft your tableadapter to have a new SELECT query that selects some small portion of the db Rows, call it fillByX, and fill only the rows you need

Apologies if any part of this post is hard to understand. It was written on an old iPad that has a really flaky autocorrect and massive input lag so if anything doesn't make sense let me know and I'll fix it up