Beginning Visual Basic 2005 Express Edition - From Novice To Professional (2006)
.pdf430 C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G
in order to establish database connections, fire commands at the database, and of course retrieve and work with data. The beauty of the typed DataSets that the DataSet designer generates is that you don’t have to worry about any of that stuff. Let’s take a look.
The first thing you need to do is create a data adapter. Data adapters provide a conduit between the database and the DataSets and tables in your code. A data adapter is able to fire commands at the database to select data, and also handle any updates, inserts, and deletes for you. When you added the data source, the IDE actually generated all these SQL statements for you and created a customized data adapter for you to use:
Sub Main()
Dim da As New _
AdventureWorks_DataDataSetTableAdapters.EmployeeTableAdapter()
End Sub
Creating your data adapter then involves nothing more than creating an instance of a data adapter that the DataSet designer made for you. You’ll find all the data adapters for this database living inside the
AdventureWorks_DataDataSetTableAdapters namespace.
So, now you have a data adapter created, all you need to do now is grab data from the database. The generated code takes care of reading the app.config file to grab the connection string and connect to the database, and also takes care of issuing Select commands (the SQL commands that grab data) against the database. All you need to do is call one of the generated methods (Fill() or GetData()) to actually talk to the database and grab the data you want:
Sub Main()
Dim da As New _
AdventureWorks_DataDataSetTableAdapters.EmployeeTableAdapter()
Dim table As New _
AdventureWorks_DataDataSet.EmployeeDataTable()
da.Fill(table)
End Sub
C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G |
431 |
The first thing that happens is to create a new instance of the data table you want to work with, in this case the EmployeeDataTable(). In this database, all the tables live in the AdventureWorks_DataDataSet namespace.
After the table instance has been created, all that remains is to call the Fill() method on the data adapter. That fires off all the work of connecting to the database, issuing a query, grabbing the results, and then populating the data table itself.
The next step is to walk through the rows in the table:
Sub Main()
Dim da As New _
AdventureWorks_DataDataSetTableAdapters.EmployeeTableAdapter()
Dim table As New _
AdventureWorks_DataDataSet.EmployeeDataTable()
da.Fill(table)
For Each row As AdventureWorks_DataDataSet.EmployeeRow _ In table.Rows
Console.WriteLine("ID:{0}, {1}", row.EmployeeID, _ row.LoginID)
Next
Console.ReadLine()
End Sub
That’s all there is to it. Run the program now and you’ll see the information in the table printed out to the console, just as in Figure 16-19.
432 C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G
Figure 16-19. Run the application and you’ll see data from all the rows written out.
The table has a Rows collection, with each element in that collection being a row in the database. Because you are using a typed DataSet generated by the designer, you can walk through the rows and then use properties on each row to get at information inside it, like the EmployeeID and LoginID in this example.
You could, if you wanted, do away with the need for the DataSet designer completely and manually write code to do everything by hand, but why would you want to? The DataSet designer makes life so simple, and typed DataSets as you’ve just seen bring with them the added benefit of IntelliSense for column names and compile-time checking. If you are going to do database programming, always use a typed DataSet.
Save the application; we’ll be coming back to it in a second.
You may have noticed when you built the DataSet in the designer that each data adapter gets two methods: GetData() and Fill(). In the example you just worked through, you used the Fill() method to populate a data table. So what exactly does GetData() do? The short answer is “exactly the same thing.” Calling Fill() on a data
C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G |
433 |
adapter is the traditional way of doing things in .NET. The DataAdapter class itself, which the designer subclasses, has a Fill() method that when called invokes the Select statement embedded in the data adapter to grab data and put it somewhere (usually in a DataSet or table). GetData() on the other hand is a new method that will return a new DataTable to you. Which you use is really a matter of choice; obviously, though, GetData() cuts down on the need for you to create a new DataTable object first.
In the example you just worked through, you walked over the Rows collection to grab information from each row in a table. The humble DataRow (which EmployeeRow is a subclass of) is a very powerful object in its own right. You see, not only does it provide you with access to the data in a specific row, but it also tracks changes. You can find out from a DataRow if the row has been changed at all since it was last loaded, how it was changed, and even request the original data from the row in case you want to do a comparison.
That all sounds powerful enough, but terribly complex. It is, however, how the mighty DataAdapter works its magic. You see, you can call a method on a DataAdapter called Update() and pass in a data table with changes. The DataAdapter then walks through every row in the table looking for rows that have been changed. If the change is the result of an edit, the adapter can call an update command to update the original record in the database. If the change is because of a completely new row being added, the DataAdapter can spot that too, firing off an insert command to the database. Similarly, the DataAdapter can also spot rows that have been flagged as being deleted and call a delete command.
Let’s take a brief look at all this in action and see how to update data in a table and get it put back into the database.
Try It Out: Updating Data
Load up the project you worked on in the preceding “Try It Out.” You’ll get rid of the code you wrote and replace it with some new code to demonstrate how to update data, but the data source you added for the Adventure Works database is going to come in handy once again.
Double-click on Module1.vb to bring up the code in the code editor, if it’s not already on display. Delete all the code in the Main() function except the line that creates the data adapter. When you’re finished, the function will look like this:
Sub Main()
Dim da As New _
AdventureWorks_DataDataSetTableAdapters.EmployeeTableAdapter()
End Sub
434 C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G
Let’s use the GetData() method to see how that works, to grab a list of employees from the database:
Sub Main()
Dim da As New _
AdventureWorks_DataDataSetTableAdapters.EmployeeTableAdapter()
Dim employees As AdventureWorks_DataDataSet.EmployeeDataTable = _ da.GetData()
End Sub
As you can see here, you don’t need to create an instance of the data table if you use the GetData() method; it returns a new fully populated table to you.
Now that you have your data, let’s grab the first row from the database so that you can change it, and I’ll show you how to use the row to find out its state:
Sub Main()
Dim da As New _
AdventureWorks_DataDataSetTableAdapters.EmployeeTableAdapter()
Dim employees As AdventureWorks_DataDataSet.EmployeeDataTable = _ da.GetData()
Dim row As AdventureWorks_DataDataSet.EmployeeRow = employees(0)
End Sub
Something quite subtle is going on here. You could have used the Rows collection of the employees object to get at the row you want, but that would return to you an object of type DataRow. If you did that, you’d have to write code to cast that object to an EmployeeRow. The table classes generated by the DataSet designer have a default property that you can use as you have here. Just by treating the employees object as if it were an array, you get back the data row you want. After you get used to working this way, it’s far more intuitive than diving down through collections and then subcollections and so on.
C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G |
435 |
Now that you have the row you are going to update, let’s find out what state it’s in. Rows have a special property called RowState that returns an enum. This enum could be unchanged for a newly loaded record, modified for a record that was just changed, deleted if it’s been deleted, or created for a new record:
Sub Main()
Dim da As New _
AdventureWorks_DataDataSetTableAdapters.EmployeeTableAdapter()
Dim employees As AdventureWorks_DataDataSet.EmployeeDataTable = _ da.GetData()
Dim row As AdventureWorks_DataDataSet.EmployeeRow = employees(0)
Console.WriteLine("Current row state: {0}", row.RowState)
End Sub
Before anything happens, you print out the row state just to see what happens.
To edit a row, you can’t unfortunately just go changing property values. You need to tell the row what you are going to do to it. If you wanted to delete the row, for example, you’d just call its Delete() method. To make changes to the values in a row, you need to call BeginEdit(). When you’re finished, either call EndEdit() to accept the changes, or CancelEdit() to throw them out. Incidentally, when we look at control binding in a little while, you’ll be pleased to learn that bound controls do all these nasty method calls for you.
Sub Main()
Dim da As New _
AdventureWorks_DataDataSetTableAdapters.EmployeeTableAdapter()
Dim employees As AdventureWorks_DataDataSet.EmployeeDataTable = _ da.GetData()
Dim row As AdventureWorks_DataDataSet.EmployeeRow = employees(0) Console.WriteLine("Current row state: {0}", row.RowState) row.BeginEdit()
row.EndEdit()
End Sub
436 C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G
Between the BeginEdit() and EndEdit() calls, you can make your changes to the row:
Sub Main()
Dim da As New _
AdventureWorks_DataDataSetTableAdapters.EmployeeTableAdapter()
Dim employees As AdventureWorks_DataDataSet.EmployeeDataTable = _ da.GetData()
Dim row As AdventureWorks_DataDataSet.EmployeeRow = employees(0) Console.WriteLine("Current row state: {0}", row.RowState) row.BeginEdit()
row.LoginID = "Test" row.EndEdit()
End Sub
With the change to the row made, let’s print out its new state. When a row changes (either being deleted, or edited, or even added), the table becomes aware of the change. You can call GetChanges() on the table itself to get an array of rows with changes back. Let’s do just that:
Sub Main()
Dim da As New _
AdventureWorks_DataDataSetTableAdapters.EmployeeTableAdapter()
Dim employees As AdventureWorks_DataDataSet.EmployeeDataTable = _ da.GetData()
Dim row As AdventureWorks_DataDataSet.EmployeeRow = employees(0) Console.WriteLine("Current row state: {0}", row.RowState) row.BeginEdit()
row.LoginID = "Test" row.EndEdit()
Console.WriteLine( _
"Current row state: {0}" + vbCrLf + _ "No. of rows changed:{1}", _
row.RowState, employees.GetChanges().Rows.Count)
End Sub
The final step is to commit the changes to the database by using the data adapter you created at the top of the code. There are two stages to this. The first is to call Update() on the adapter. This will kick the adapter into life, looking through the rows for changes before calling the update command it generated silently when you built the DataSet. The second is to call AcceptChanges() on the table itself. The reason for this is that after
C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G |
437 |
the changes are written to the database, the rows that changed are still in your table and flagged as changed. Calling AcceptChanges() clears out all the changes, leaving you with just the current rows in the table and no more:
Sub Main()
Dim da As New _
AdventureWorks_DataDataSetTableAdapters.EmployeeTableAdapter()
Dim employees As AdventureWorks_DataDataSet.EmployeeDataTable = _ da.GetData()
Dim row As AdventureWorks_DataDataSet.EmployeeRow = employees(0) Console.WriteLine("Current row state: {0}", row.RowState) row.BeginEdit()
row.LoginID = "Test" row.EndEdit()
Console.WriteLine( _
"Current row state: {0}" + vbCrLf + _ "No. of rows changed:{1}", _
row.RowState, employees.GetChanges().Rows.Count)
da.Update(employees)
employees.AcceptChanges()
Console.ReadLine()
End Sub
Run the program now to see the output in Figure 16-20.
Figure 16-20. Run the program to see the row state change and to update the database.
438 C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G
If you use the Database Explorer now to show you the data in the Employees table, in the database you’ll see your changed record right at the top.
So, to summarize, if you want to change a record, all you need to do is call BeginEdit() on the row in question, make your changes, and then call EndEdit(), Update() on the adapter and AcceptChanges() on the table. It seems convoluted I know, but you get used to it.
Alternately, to delete a row, call Delete() on the row in question and then Update() and AcceptChanges() just as if you were making an edit.
Adding a row is slightly different. Every table class created by the designer gets a New...Row() method, where the ellipsis (…) is replaced with the name of the table. For example, your employees object has a method called NewEmployeeRow(). This will return a new blank row for you to use. Simply set the properties on the row that you need and then call AddEmployeeRow() (or whatever your table is called) on the table object to store the row into the Rows collection.
There’s a lot more you can do, including drilling down into the row itself to find out what changed, as well as validating the row, that unfortunately I don’t have time to cover here. Feel free to explore the ADO.NET section in the online help for more information. For most cases, what I’ve covered here will be all you need to use. In fact, for most cases, it’s more than you need to know because data binding can take care of much of the detail work for you.
So far we’ve used the DataSet pretty much out of the box. As you’ve seen, the DataSet designer automatically generates two methods to get data from the database, and implicitly produces the SQL code to update the database, delete records, and create new ones. It would be easy to presume that the DataSet designer is somewhat limiting, that it forces you down a path that’s hard to deviate from. Nothing could be further from the truth. It’s incredibly easy to change the default behavior of the produced DataSet, and almost trivial to extend it with even more functionality.
After the data connection wizard has produced the DataSet, you can configure the tables within it just by right-clicking in the designer view and selecting Configure from the pop-up menu. This will start the TableAdapter Configuration Wizard, as shown in Figure 16-21.
As you can see, this instantly displays the SQL code that’s going to be used to grab data from the database. You can easily modify this either by typing new SQL code into the mini editor, or by clicking the Query Builder button to run the graphical query builder shown in Figure 16-22.
C H A P T E R 1 6 ■ D A T A B A S E P R O G R A M M I N G |
439 |
Figure 16-21. You can easily access the TableAdapter Configuration Wizard by right-clicking on any table in the designer and selecting Configure.
Figure 16-22. The graphical query designer makes it a relatively painless task to develop quite
complex queries.