Dataadapters versus Tableadapters

Hello being rather new to VB.net and using VS2008 to design a database (SQL Server 2005 db), I have a few questions and hope I am heading on the right path..

I have been trying to design programatically so have been using a dataadapter.

Am I correct in thinking that a dataadapter is used when designing "programatically" as I am doing already? And that a tableadapter is used with Visual Studio design-time tools such as the Dataset Designer and Data Source Configuration Wizard which then places the binding source, binding navigator and compiles and configures the queries etc?

Which method would be the most ideal? Is there any reason to choose one over the other?

I wanted to learn to design programatically (dataadapter) so I got an idea of how things worked and linked together, I have my created own stored procedures in sql server and call them from VS2008. I dont feel I would gain that experience if I used design-time (tableadapter) tools and wizards. But it would save time?

I found these links which did help, but was just wondering what the general consensus was..use design-time (tableadapter) or use progamatic (dataadaptor) approach?

http://msdn.microsoft.com/en-us/library/ms171919.aspx
http://keithdevens.com/weblog/archiv...3/TableAdapter

You're pretty much correct. I don't believe you ever use a "TableAdapter" unless you're linking datasources via the GUI at design-time. At least I never have.

Personally, I never touch the design-time junk. It's literally like a different world. I've tried about four or five times to use the design-time system but have always ended up falling flat on my face at some of the simplest, stupidest things that would take one line to do programmatically. I've run into so many problems with doing DataBinding via the design-time GUI; especially when using a DataGrid, that at this point, I don't touch them anymore.

A lot of the programmers on here feel the same way unfortunately from what I gather. A simply look at the amount of unanswered, no-reply posts about DataGrids and working with TableAdapters on these forums is an easy indication of which method people seem to prefer on here. I honestly don't think there's more than one or two "pros" on here that even know how the GUI methods work well enough to give solid advice. Sadly, the design-time GUI tools which are supposed to "make things easier" usually end up making things way more harder than they need to be unless you're doing all but the simplest task.

Thus, I do my best to steer new users away from the things and point them to simple, five-line examples of how to use the "hard" programmatic method. Sometimes, my posts showing my disdain for the use of those GUI methods get quite colorful with words such as "abysmal" and "dismal". :)

The programmatic methods... Connection, Command, DataAdapter and DataSet objects are so simple to use regardless that I don't see why anyone shouldn't learn them and use them exclusively and as such, my conclusion is: "Good for you for learning the easier of the two methods of data manipulation. Trust me, it's all you'll ever need."

Think of tableadapters as wrappers around dataadapters. They're just visual components to help making database oriented applications easier to build. Each new release of Visual Studio gives the developer more and more ways to drag and drop their components onto a form, shielding them from learning how the way things work. I keep saying, "No, I don't want it to be easier for me. Stop trying to give me these pointless controls." (I say this out loud when nobody is nearby or they would think that I am talking to myself).

Your best bet is to stick with the method you're currently using right now. You will realize how things work 'behind the scenes' (from another perspective) and it'll give you more control (pun unintended) in the long run. The only downside is that you will not be able to understand immediately how the tableadapter works. To be honest, I wish I knew more about it and how to use it, but the aversion towards dragging and dropping things without retaining control over how things are done is far too strong to allow the motivation to learn about the tableadapter to kick in.

You are, however, correct that it would save time - for a simple application. You wouldn't use it for enterprise level applications.

A TableAdapter is to a typed DataSet as a DataAdapter is to an untyped DataSet. A typed DataSet is a class that the IDE generates for you based on the database schema that you provide. Generally the schema is provided by way of a connection to the database, where the IDE reads the schema itself and generates DataSet for you, including all the DataTables and TableAdapters. You can also build it yourself in the designer.

The advantage of a typed DataSet is that every table and every column is available via a typed property rather than via a string name. In the untyped world you might do like this:

vb.net Code:

Dim row As DataRow = myDataSet.Tables("MyTable").Rows(0)
Dim name As String = CStr(row("Name"))

You need to cast the values you retrieve from the DataSet and you get no help from Intellisense with regards to table or column names. In the typed world that becomes:

vb.net Code:

Dim row As MyTableDataRow = myDataSet.MyTable(0)
Dim name As String = row.Name

No casting required and Intellisense all the way.

As you can see, there's more to typed DataSets than the ability to drag and drop at design time. I dispute mendhak's assertion that they are fine for simple apps but you wouldn't use then in enterprise level applications. Anywhere you are not using a generic DAL you should use typed DataSets for the Intellisense and strong-typing benefits alone. There's no requirement to add them to your forms in the designer. I never do.

As to the TableAdapters, a wrapper for a DataAdapter is exactly what they are. The IDE generates a TableAdapter for each DataTable in your typed DataSet. Within the TableAdpter the code is written for you to create a DataAdapter and set up all four of its commands, including setting there Connection properties. I also dispute the claim that TableAdapters reduce the amount of control you have. You can edit all aspects of the TableAdapter in the designer, which is equivalent to writing code to configure a DataAdapter yourself. You can also use the Query Builder to write your SQL statements, which is far better than writing inline SQL by hand.

If you want to use stored procedures then you can choose to use an existing procedure or the IDE will generate one for you. Again, you have complete control over the entire process if you want, or you can let the IDE handle the mundane details for you. The difference is that it's done visually instead of everything being hand-coded.

I don't know if DataBinding has improved any since 2K5 but after the 20th DataTable for a 11 table database, doing it this method started to become ridiculous. Sure, I could join DataTables via references in memory, but the speed difference between doing this and just calling the data you want over the network with a SQL JOIN operation was well over 20 to 1. Network tests showed the method to be a bandwidth hog on our server as well.

I'll bite though and be the first to admit that it's been years since I last attempted to play around with any form of DataBinding. The IT director at the time and I had such a miserable experience with it that we shelved the concept and wrote it off as some half-baked "database control for dummies" system Microsoft tried to cook into VB.NET.

I knew you could do typed DataSets with them, but that benefit seemed so minuscule considering getting the datatype of a field took seconds and the performance boost negligible (especially in light of the increased server load and spaghetti mess of DataSet objects my program turned into), I never re-examined their use.

Jmcilhinney, you got an excellent Data Access Example tutorial in your sig, one that I've frequently sent other users to. Do you know of or have any links to a good tutorial on the proper ways to do DataBinding? Maybe the times I attempted it, I was doing something wrong.

Jenner, you seem to be under the impression that a typed DataSet is limited to returning the contents of single tables. Like I said, a TableAdapter simply wraps a DataAdapter, so it can return the result of any query you like. You simply open the DataSet designer, add a new TableAdapter and build a new query, which can be as complex as any stored procedure you can imagine.

I was playing around with the things yesterday afternoon with VB2k8 and was able to attach pretty much my whole database to a single strong typed dataset. When I attempted it years ago, I couldn't seem to attach more than one table per dataset. I do have a question for you though if you don't mind because try as I might, I couldn't get it to work.

I know the ConnectionString is stored in the Settings (i.e. the .config file). Suppose I had two databases with identical schemas but different data. I can change the ConnectionString, but how do I re-initialize the connection without closing and reopening my program? i.e. where's the connection object?

I've also been trying to wrap my brains around DataBinding more and though weird, I may be getting the hang of it. I still haven't been able to reproduce a style of form that I typically create for my applications (a form with a split; listview on left side, text fields on right. When item on left is selected, data fills in on right. The catch: When data on right is changed, list on left becomes disabled and save/cancel buttons become visible. There are also "Add and Delete" buttons above the listview which perform their respected functions. I'm having a hard time with the cancel/save buttons when the data is bound.)

Quote:

---

Originally Posted by Jenner

I was playing around with the things yesterday afternoon with VB2k8 and was able to attach pretty much my whole database to a single strong typed dataset. When I attempted it years ago, I couldn't seem to attach more than one table per dataset. I do have a question for you though if you don't mind because try as I might, I couldn't get it to work.

I know the ConnectionString is stored in the Settings (i.e. the .config file). Suppose I had two databases with identical schemas but different data. I can change the ConnectionString, but how do I re-initialize the connection without closing and reopening my program? i.e. where's the connection object?

I've also been trying to wrap my brains around DataBinding more and though weird, I may be getting the hang of it. I still haven't been able to reproduce a style of form that I typically create for my applications (a form with a split; listview on left side, text fields on right. When item on left is selected, data fills in on right. The catch: When data on right is changed, list on left becomes disabled and save/cancel buttons become visible. There are also "Add and Delete" buttons above the listview which perform their respected functions. I'm having a hard time with the cancel/save buttons when the data is bound.)

---

The connection is not exposed by the TableAdapter by default. If you need to access it you must open the DataSet designer, select a TableAdapter and then set the ConnectionModifier property to Public. This will then regenerate the code for ALL your TableAdapters and add a Connection property, which will return a reference to the internal SqlConnection object, or whatever type is appropriate for your data source.

If you want to set up such a form then I STRONGLY recommend NOT using a ListView. It doesn't support data-binding and therefore makes your life more difficult. You should use a DataGridView and the code to bind a DataTable to a grid and multiple individual controls is almost laughably trivial. Let's assume that your table has ID, FirstName and LastName columns. You would have a grid and two TextBoxes for FirstName and LastName. You'd also add a BindingSource and then the code would look like this:

vb.net Code:

Me.BindingSource1.DataSource = myDataTable
Me.DataGridView1.DataSource = Me.BindingSource1
Me.firstNameText.DataBindings.Add("Text", Me.BindingSource1, "FirstName")
Me.lastNameText.DataBindings.Add("Text", Me.BindingSource1, "LastName")

That's it. The grid will be populated with data, as will the TextBoxes. As the user selects a row in the grid the TextBoxes will update automatically. Any changes you make to the data in the TextBoxes will automatically be propagated to the grid WHEN the user navigates to another record, thus committing the change.

If you need more control you can get it. You can make the grid read-only, which you probably would if you're using TextBoxes to edit. You can hide certain columns in the grid if desired. You can add a BindingNavigator to provide the user an easy way to navigate the data, or you can provide your own interface and access the members of the BindingSource in code.

You should also note that, if you've added a typed DataSet to your form, all the data-binding can be set up in the designer and you can get away without writing any code at all.

Interesting. Didn't know ListViews couldn't bind. As long as a user can sort a column by clicking on the columnheader (or I can set it up to do so as I have with ListViews) I got no problem with a read-only DataGrid. I don't care what control is on the left as long as it has multiple columns, can sort by column, and can't be edited directly (i.e. serve for viewing of data only).

My problem is users are too stupid to click a new record to commit changes. They need buttons laid out specifically with "Save" and "Cancel", and prevention from selecting any other record until they commit the change or dump it. Our first versions of some of these programs were written in VB6 and apparently behaved where you change records to commit changes or go one-past the last record to add a new record (ala Access). Our techs got flooded with so many calls of corrupted data, because someone changed something and closed the form because they were done; that the system was quickly changed.

In my test code, I'm just using a bound Listbox. Guess I would have discovered the ListView problem at some point. Still can't seem to get my form to cancel an update and roll it back though.

If you bind your data using a BindingSource then you can explicitly call EndEdit to commit changes or CancelEdit to roll back. When I say "commit" I mean commit to the underlying list. Committing to the database is another step.

Yes, when "Save" is clicked, it would commit to the list and database via an update.

The problem I'm having now is I can't figure out how to tell if the BindingSource is in "Edit" mode. There's no "IsDirty" or "State.Editing" or something similar that I can find. If I call "CancelEdit" without an edit taking place, I get an exception (granted, I could use that to figure it out, but I prefer not to use exceptions as logic conditions).

Quote:

---

Originally Posted by Jenner

Yes, when "Save" is clicked, it would commit to the list and database via an update.

The problem I'm having now is I can't figure out how to tell if the BindingSource is in "Edit" mode. There's no "IsDirty" or "State.Editing" or something similar that I can find. If I call "CancelEdit" without an edit taking place, I get an exception (granted, I could use that to figure it out, but I prefer not to use exceptions as logic conditions).

---

Are you sure? What are the details of the exception?

Heh... "StackOverflow":

Attachment 64428

Have you checked the stack trace? I don't know but that sounds to me like your own code has somehow created endless recursion.

Hey, this is a great thread! I've been trying to use the designer/wizard for my connections, and this thread explains how to do many of the things I've been having problems with. Cheers!

Quote:

---

Originally Posted by jmcilhinney

Have you checked the stack trace? I don't know but that sounds to me like your own code has somehow created endless recursion.

---

I'm betting it is because it's on the ListBox_SelectedIndexChanged event, but I don't know how else to achieve the effect I want, and that is NOT to auto-update the local copy of the dataset when the index changes.

If they make any changes to the bound TextBoxes, and then switch to a different record, I want those changes forgotten and scrapped; back to what the record was before the editing. The only time I want to commit changes is if the "Save" button is pressed.

Granted, I can easily get this effect if I DON'T databind the controls and manually fill them on selection, but I've been doing that. I'd love it if I could databind controls to a list/grid perhaps as "read only", or update the dataset/database only when I want it to. it would certainly make for less code.

OK, I should have realised this earlier but wasn't really thinking. The reason that you are able to call CancelEdit at all when a DataTable is data-bound is that the DataRowView class implements the IEditableObject interface. When you bind a DataTable it's treated as an IListSource object. When you bind an IListSource object its GetList method is called to get an IList object containing the data to display. The GetList method of the DataTable class returns its DefaultView, which is a DataView object. A DataView is populated with DataRowView objects, each of which corresponds to a DataRow in the DataTable. When you bind a DataTable to a DataGridView, what you actually see is the data from the DataRowViews in its DefaultView.

Now, the DataRowView class implements the IEditableObject interface, which specifically supports transactional editing. That means that the BeginEdit, EndEdit and CancelEdit members of the BindingSource are simply passed through to the same members of the DataRowView object currently exposed via its Current property. The IEditableObject interface also has an IsEdit property that tells you whether the object is currently in edit mode.

The short of it is your code could look like this:

vb.net Code:

If DirectCast(Me.BindingSource1.Current, DataRowView).IsEdit Then
    Me.BindingSource1.CancelEdit()
End If

or, more generally:

vb.net Code:

Dim currentRow As IEditableObject = TryCast(Me.BindingSource1.Current, IEditableObject)
 
If currentRow IsNot Nothing AndAlso currentRow.IsEdit Then
    Me.BindingSource1.CancelEdit()
End If

Ah! Ok, that makes more sense. Cool, I'll have to give that a try when I get back from vacation on the 6th. Thanks for the clarification jmcilhinney!