The short answer:
We’ve updated from untyped, configured DataAdapters to Typed, Code Generated classes similar to the type DataSet for DataAdapters.
- Using the Data Sources Window, add a new Data Source then drag the results to your form
- Using the DataGridView, select Choose Data Source from the Smart tag that pops up.
With new releases of products, we work to make experiences better. In Whidbey (Visual Studio 2005), we were able to pick up where we left off in 2002 for generating typed classes to access data. In 2002, and 2003 we would generate a Typed DataSet to provide a better experience iterating through collections of data.
Me.CustomerTextBox.Text = Me.NorthwindDataSet.Customers(0).CustomerName
However, we didn’t have the time to help with the really hard part… getting the data.
Even if you used the DataAdapter wizard to create an updatable query with parameters you still needed to write ugly code.
For the common SELECT statement:
SELECT CustomerID, CompanyName, AddressLine1, AddressLine2, City, State, PostalCode
WHERE Company LIKE @company
AND State = @state
You would have to use the following code:
Me.SqlDataAdapter1.SelectCommand.Parameters(0).Value = Me.CustomerNameTextBox.Text.Trim() & “&”
If the developer just finished the wizard, they may remember the order of the parameters.
“Which parameter number represents the @companyName and which parameter represents @state”
Sure, they could write code like the following:
Me.SqlDataAdapter1.SelectCommand.Parameters(“CompanyName”).Value = Me.CustomerNameTextBox.Text.Trim() & “&”
Me.SqlDataAdapter1.SelectCommand.Parameters(“State”).Value = Me.StateComboBox.SelectedValue.Trim()
But if your like me, may quickly forget the names, order, or even what parameters even exists. You may even wish to share it with another form, or you may split who configures the DataAdapters with who consumes them.
There are a host of issues that then come up:
- If I’m on the same form, do I remember the parameter names? Was it “State”, “StateCode”, “Company” or “CompanyName”? Was it the first, second, third.. parameter? Do I call the name “@company” or “Company”? Ok, so I could use an Enum or Constant, but is that really any more intuitive when consuming the SqlDataAdapter1? I don’t get auto prompted for the list, I need to remember where the enum is. It does give me compile time verification. (Love compile time verification by the way. Who goes to the airport without first checking if the flight is on time?. Why should I have to exercise ever code path of my app just to find out I typo’d something?)
- What happens when I need to use that same CustomersDataTable on Form2? How do I get the DataAdapter from Form1 to Form2. I could copy/paste, (yeah like that’s maintainable)
- Oh, and what’s up with SqlDataAdapter1, why do I have to go back and rename it CustomerDataAdapter and CustomerSelectCommand? We already know it was configured for the Customer DataTable
In comes Whidbey…
So, now we have a recap of the story we shipped in 2002. Did we want to write more pages, sure. But we needed to ship, so that’s where Chapter 1 of Visual Studio Data left off. In Chapter 2 we now fill in more details.
Wouldn’t it be nice if I could just reduce the above fragile, unintelligible code to the following?
Me.CustomerTableAdapter.FillByCompanyName(Me.NorthwindDataSet.Customers, Me.CompanyNameTextBox.Text, Me.StateComboBox.SelectedValue)
- The DataAdapter is friendly named
- The DataSet is friendly named
- A typed, friendly named method (FillByCompanyName) exists directly on the CustomerTableAdapter
- The parameters for FillByCompanyName are specifically typed:
Public Overridable Function FillByCompanyName(ByVal dataTable As NorthwindDataSet.CustomersDataTable, ByVal companyName As String, ByVal state As String) As Integer
- The DataAdapter is a generated class/type so it can be instanced on multiple forms components, or within your own classes. Change the query or interface in one place, and it available in all your forms or objects.
- It doesn’t expose the internal commands and connections by default.
But what about Nulls? (or is it Nothing…)
Ahhh, let’s consider the following query:
SELECT OrderID, CustomerID, OrderDate, RequiredDate, ShippedDate
WHERE (ShippedDate = @shippedDate) OR
(ShippedDate IS NULL)
Wouldn’t it be nice if this could handle a Null/Nothing value for the ShippedDate parameter?
Public Overridable Function FillByShippedDate(ByVal dataTable As NorthwindDataSet.OrdersDataTable, ByVal shippedDate As Nullable(Of DateTime)) As Integer
Internally, the method would convert Null/Nothing to DBNull
Note: This isn’t available in Beta 1, this work was just completed and should be available in the next community technology preview and of course Beta 2
Don’t let your friends see your privates…
Many developers have heard this term. We’ve also heard that developers want to create these methods and expose them as public API’s, however, they don’t want someone to be able to get at or change the ConnectionString or the CommandText of the internal commands.
TableAdapter, DataAdapter, Potato Potatoe…
To solve this, we encapsulate a provider specific DataAdapter within a generated class that, by default, inherits from Component.
Partial Public Class OrdersTableAdapter
This means we, or you the developer, can control what public API exists. This also means that you can’t cast one of our generated classes to a IDBDataAdapter. This is why we came up with the name TableAdapter. Hopefully developers can recognize the name, but understand why they can’t cast it.
But I want more…
Ahh, everyone has a feature request, and many of them are really, really good. Leveraging Nullable(Of T) was one of those requests that came from one of your fellow MVP’s. (Sorry, I never got their name or I’d give them credit…)
In Whidbey we’ve introduced the concept of Partial Classes. You can essentially define two portions of the class in two separate files. It’s sort of akin to using #Includes in old asp. Although you can only use the include in one class. However, what this means is Visual Studio can generate all the code in one file, and you can contribute to the same base class, but in a different file.
File Name: NorthwindDataSet.designer.vb
This is the file we generate. Note the designer within the name.
Partial Public Class OrdersTableAdapter
File Name: NorthwindDataSet.vb
This is your file.
Partial Public Class OrdersTableAdapter
In this file you can extend the API of the class we defined in NorthwindDataSet.designer.vb.
Partial classes are powerful, but do have some limitations:
- You can reference private variables in the other partials
- You can’t override a method in other partials
If you want to make the ConnectionString public you can simply add the following to your own partial class:
Partial Public Class CustomersTableAdapter
Public Property ConnectionString() As String
Set(ByVal value As String)
Me.Connection.ConnectionString = value
Changes from Beta 1 to Beta 2
There are a few changes we’ve made that will show up in Beta 2:
The TableAdpater.Connection will no longer be exposes as friend, and the Transaction property will be removed. You can see more info at: Connection and Transaction properties exposed as Friend on TableAdapters
Is this VB Only is C# allowed to play?
These features are supported in all .NET languages. This includes Visual Basic 2005, C#, J# and C++. Other .NET languages can also integrate with these features as well.
So, why are the Data Components removed from the ToolBox?
Because so many people were using the old model that we felt we needed to force people to look for the new model. With the new model, we don’t believe developers really need the untyped components on the toolbox.
If you don’t want to create UI from your DataSets, or you want to work with the Component Designer and just want the DataAdapters, we still have a feature for you…
Once you add a Typed DataSet and you have TableAdapters, these typed TableAdapters will appear in the toolbox. Just select Rebuild All, and look in the top of the new handy, dandy toolbox. Just like user controls, you’ll see your components available.
But I don’t want my cheese moved. I like the old model…
Ok. I’d really, really,