The <asp:ObjectDataSource> control can be employed along with DataSets to provide database maintenance functions. Recall that DataSets are in-memory tables that are filled with data from database tables. Manipulation of this information takes place in memory, with changes written back to the database through the DataSet's Update() method. When using DataSets, it is not necessary, even, to supply SQL INSERT, UPDATE, or DELETE statements to rewrite changes to the database. These statements are composed and issued for you.
An example of using DataSet methods is shown in Figure 11-14. This is a default DetailsView whose record display is chosen from a DropDownList. Automatic "Edit," "Delete," and "Insert" buttons call class methods to carry out processing through a DataSet.
Note that making actual changes to the BooksDB.mdb database is not permitted in these tutorials.
Populating the DropDownList
A DropDownList permits selection of records from the Books table. This list is filled with all (unique) BookIDs drawn from the table.
<asp:ObjectDataSource id="BookIDSource" Runat="Server" TypeName="DSClass" SelectMethod="Select_BookIDs"/> <asp:DropDownList id="DropList" DataSourceID="BookIDSource" Runat="Server" DataTextField="BookID" DataValueField="BookID" AutoPostBack="True"/>
The DropDownList is associated with an ObjectDataSource that supplies BookID values. The data source calls the Select_BookIDs method from DSClass (the data access class) to return these values. Code for this class and method are given below. The class appears in file DSClass.vb in the standard app_Code directory.
Imports System.Data.OleDb Imports System.Data Public Class DSClass Function Select_BookIDs() Dim DBConnection As OleDbConnection Dim DBCommand As OleDbCommand Dim DBReader As OleDbDataReader Dim SQLString As String DBConnection = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\eCommerce\Databases\BooksDB.mdb") DBConnection.Open() SQLString = "SELECT DISTINCT BookID FROM Books ORDER BY BookID" DBCommand = New OleDbCommand(SQLString, DBConnection) DBReader = DBCommand.ExecuteReader() Return DBReader DBReader.Close() DBConnection.Close() End Function ... End Class
Two namespaces are imported to the class: System.Data.OleDb and System.Data. The former is necessary for accessing the BooksDB.mdb database, the later for working with DataSets as described below. The Select_BookIDs method simply returns a DataReader through the ObjectDataSource associated with the DropDownList.
Selecting a Record for Display
The DropDownList has an AutoPostBack="True" property such that selection from the list automatically reloads the page and triggers reloading of the DetailsView with the selected record. The DetailsView is also populated by an ObjectDataSource which, in this case, uses the selected BookID as its selection parameter.
<asp:ObjectDataSource id="BookSource" Runat="Server" TypeName="DSClass" SelectMethod="Select_Book" UpdateMethod="Update_Book" InsertMethod="Insert_Book" DeleteMethod="Delete_Book"> <SelectParameters> <asp:ControlParameter Name="BookID" ControlID="DropList" PropertyName="SelectedValue"/> </SelectParameters> </asp:ObjectDataSource> <asp:DetailsView id="BookView" DataSourceID="BookSource" Runat="Server" AutoGenerateRows="True" AutoGenerateEditButton="True" AutoGenerateDeleteButton="True" AutoGenerateInsertButton="True" DataKeyNames="BookID" RowStyle-VerticalAlign="Top" RowStyle-Font-Size="10pt"/>
The ObjectDataSource includes a SelectParameter identifying the selected value from the DropDownList as a selection parameter named BookID. This value is passed to the Select_Book method of DSClass for choosing which particular book to display in the DetailsView. Code for the Select_Book method is shown below in the continuing DSClass script.
Imports System.Data.OleDb Imports System.Data Public Class DSClass Function Select_BookIDs() ... End Function Function Select_Book (BookID As String) Dim DBConnection As OleDbConnection Dim DBAdapter As OleDbDataAdapter Dim DBDataSet As DataSet Dim SQLString As String DBConnection = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\eCommerce\Databases\BooksDB.mdb") SQLString = "SELECT * FROM Books WHERE BookID = '" & BookID & "'" DBAdapter = New OleDbDataAdapter(SQLString, DBConnection) DBDataSet = New DataSet DBAdapter.Fill(DBDataSet) Return DBDataSet End Function ... End Class
Filling a DataSet
Rather than issuing a SELECT statement through a Command object and returning a DataReader to the ObjectDataSource, a DataSet is used. In this case, the value from the DropDownList is received by the Select_Book method as argument BookID, which is plugged into a SELECT statement that is issued through a DataAdapter. The adapter fills a DataSet (DBDataSet in this example) with this table of information. The DataSet is returned to the ObjectDataSource, which populates the DetailsView with this record.
Keep in mind that the DataSet table is a single record from the BooksDB.mdb database. It is the first, or only, table in the DataSet's Tables collection (identified as Tables(0)) and comprises a single row in the table's Rows collection (identified as Tables(0).Rows(0)). These references become important later when updating the database.
Editing with a DataSet
When the DetailsView's "Edit" button is clicked, the display is placed in edit mode, supplying textboxes for changing values in all fields except the BookID field, which is the record key. Since the DetailsView is associated with an ObjectDataSource, clicking its "Update" button activates the ObjectDataSource's UpdateMethod which, in turn, calls the Update_Book method of the DSClass. This method is shown below in the continuing script.
Imports System.Data.OleDb Imports System.Data Public Class DSClass Function Select_BookIDs() ... End Function Function Select_Book (BookID As String) ... End Function Function Update_Book (BookID As String, _ BookType As String, _ BookTitle As String, _ BookAuthor As String, _ BookDescription As String, _ BookPrice As Decimal, _ BookQty As Integer, _ BookSale As Boolean) Dim DBConnection As OleDbConnection Dim DBAdapter As OleDbDataAdapter Dim DBDataSet As DataSet Dim SQLString As String Dim DBCommandBuilder As OleDbCommandBuilder DBConnection = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\eCommerce\Databases\BooksDB.mdb") SQLString = "SELECT * FROM Books WHERE BookID = '" & BookID & "'" DBAdapter = New OleDbDataAdapter(SQLString, DBConnection) DBDataSet = New DataSet DBAdapter.Fill(DBDataSet) DBDataSet.Tables(0).Rows(0).Item("BookType") = BookType DBDataSet.Tables(0).Rows(0).Item("BookTitle") = BookTitle DBDataSet.Tables(0).Rows(0).Item("BookAuthor") = BookAuthor DBDataSet.Tables(0).Rows(0).Item("BookDescription") = BookDescription DBDataSet.Tables(0).Rows(0).Item("BookPrice") = BookPrice DBDataSet.Tables(0).Rows(0).Item("BookQty") = BookQty DBDataSet.Tables(0).Rows(0).Item("BookSale") = BookSale DBCommandBuilder = New OleDbCommandBuilder(DBAdapter) DBAdapter.Update(DBDataSet) End Function ... End Class
Once again a DataSet is filled with a table containing a record from the BooksDB.mdb database. This time the value used to select the record comes from the BookID argument passed from the ObjectDataSource along with the other values from the DetailsView's edit fields.
Now it is a matter of replacing the current values in the fields with the changed values passed through the argument list. Note that a reference to a current field is in the format
Updating pertains to a particular table in the DataSet's Tables collection (Tables(0) for this first, or only, table), to a particular row in the table's Rows collection (Rows(0) for this first, or only, row), and to a particular cell along this row (Item("field") for a named field). Each field is assigned its corresponding passed value, using the names in the method's argument list.
Using a CommandBuilder
Once these changes are made, the database is updated with the DataSet values. One of the ways to update the database is by composing an SQL UPDATE statement and issuing it through the adapter. An easier way, however, is to make use of a DataSet's CommandBuilder, which composes the statement for you. By creating an OleDbCommandBuilder object, appropriate SQL commands are built "behind the scenes" to permit changes in a DataSet to be rewritten to a database. The general formats for creating a CommandBuilder are shown below.
Dim CommandBuilder As OleDbCommandBuilder CommandBuilder = New OleDbCommandBuilder(adapter) or Dim CommandBuilder = New OleDbCommandBuilder(adapter)
In the current example, DBCommandBuilder is created for the adapter DBAdapter. Then the adapter executes its Update() method, applying the changed DataSet to the database from which it was orginally filled.
DBCommandBuilder = New OleDbCommandBuilder(DBAdapter) DBAdapter.Update(DBDataSet)
Inserting with a DataSet
A similar technique is used when adding a new record to a database. The DetailsView's "Insert" button activates the ObjectDataSource's InsertMethod, calling the Insert_Book method of DSClass and passing along new data values.
Imports System.Data.OleDb Imports System.Data Public Class DSClass Function Select_BookIDs() ... End Function Function Select_Book (BookID As String) ... End Function Function Update_Book (BookID As String, _ ... ... End Function Function Insert_Book (BookID As String, _ BookType As String, _ BookTitle As String, _ BookAuthor As String, _ BookDescription As String, _ BookPrice As Decimal, _ BookQty As Integer, _ BookSale As Boolean) Dim DBConnection As OleDbConnection Dim DBAdapter As OleDbDataAdapter Dim DBDataSet As DataSet Dim SQLString As String Dim DBCommandBuilder As OleDbCommandBuilder DBConnection = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\eCommerce\Databases\BooksDB.mdb") SQLString = "SELECT * FROM Books WHERE BookID = ''" DBAdapter = New OleDbDataAdapter(SQLString, DBConnection) DBDataSet = New DataSet DBAdapter.Fill(DBDataSet) Dim AddedRow as DataRow = DBDataSet.Tables(0).NewRow() AddedRow("BookID") = BookID AddedRow("BookType") = BookType AddedRow("BookTitle") = BookTitle AddedRow("BookAuthor") = BookAuthor AddedRow("BookDescription") = BookDescription AddedRow("BookPrice") = BookPrice AddedRow("BookQty") = BookQty AddedRow("BookSale") = BookSale DBDataSet.Tables(0).Rows.Add(AddedRow) DBCommandBuilder = New OleDbCommandBuilder(DBAdapter) DBAdapter.Update(DBDataSet) End Function ... End Class
Again, a DataSet is created. This time, however, it is not filled with a record from the database; a current record is not being affect. Instead, a null record fills the DataSet to ensure only that the structure of the Books table is represented in the DataSet. A new, empty row (a DataRow object) is created using the Tables Collection's NewRow() method.
Dim AddedRow as DataRow = DBDataSet.Tables(0).NewRow()
Once this empty row is filled with data passed from the DetailsView's insert template, the row is added to the null table and the database is updated with this added information, using the CommandBuilder to create an appropriate INSERT statement.
DBDataSet.Tables(0).Rows.Add(AddedRow) DBCommandBuilder = New OleDbCommandBuilder(DBAdapter) DBAdapter.Update(DBDataSet)
Deleting with a DataSet
The Delete_Book method of DSClass removes an identified record from the database. It receives a BookID key from the ObjectDataSource through its DeleteMethod, the value coming from the displayed record in the DetailsView.
Imports System.Data.OleDb Imports System.Data Public Class DSClass Function Select_BookIDs() ... End Function Function Select_Book (BookID As String) ... End Function Function Update_Book (BookID As String, _ ... ... End Function Function Insert_Book (BookID As String, _ ... ... End Function Function Delete_Book (BookID As String) Dim DBConnection As OleDbConnection Dim DBAdapter As OleDbDataAdapter Dim DBDataSet As DataSet Dim SQLString As String Dim DBCommandBuilder As OleDbCommandBuilder DBConnection = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\eCommerce\Databases\BooksDB.mdb") SQLString = "SELECT * FROM Books WHERE BookID = '" & BookID & "'" DBAdapter = New OleDbDataAdapter(SQLString, DBConnection) DBDataSet = New DataSet DBAdapter.Fill(DBDataSet) DBDataSet.Tables(0).Rows(0).Delete() DBCommandBuilder = New OleDbCommandBuilder(DBAdapter) DBAdapter.Update(DBDataSet) End Function End Class
Once again the corresponding record from the database fills a DataSet. The Rows Collection's Delete() method removes the record from the DataSet, and the CommandBuilder creates an appropriate DELETE statement for updating the database to reflect the deleted record.