The <asp:ObjectDataSource> control contains, besides the SelectMethod to retrieve information from a database, three additional methods to perform the full range of database operations. The UpdateMethod changes information in a database; the InsertMethod adds a new record to a database; and the DeleteMethod deletes a record from a Database. These methods are applied through bindings to server controls that permit these operations.
Master/Detail Editing with an ObjectDataSource
Recall that a master/detail setup with a GridView and associated DetailsView permits additions, changes, and deletions for a database. These operations can be performed through an ObjectDataSource as demonstrated in Figure 11-13.
Note that making actual changes to the BooksDB.mdb database is not permitted in these tutorials.
Coding the GridView
The above GridView presents the collection of BookID values for all records in the database. Clicking on a BookID displays the associated record in the accompanying DetailsView. Code for the GridView is shown below.
<asp:AccessDataSource id="BookSource1" Runat="Server" DataFile="~/Databases/BooksDB.mdb" SelectCommand="SELECT BookID FROM Books ORDER BY BookID"/> <asp:GridView id="BookGrid" DataSourceID="BookSource1" Runat="Server" DataKeyNames="BookID" AutoGenerateColumns="False" AllowPaging="True" PageSize="5" HeaderStyle-BackColor="#E0E0E0" SelectedIndex="0" SelectedRowStyle-BackColor="#F0F0F0" Style="float:left; margin-bottom:200px"> <Columns> <asp:TemplateField HeaderText="ID" HeaderStyle-Font-Size="10pt" ItemStyle-Font-Size="10pt"> <ItemTemplate> <asp:LinkButton CommandName="Select" Runat="Server" Text='<%# Eval("BookID") %>'/> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView>
You should recognize this coding from previous examples. The BookID values from the database are formatted as LinkButtons which, when clicked, supply the SelectedValue for retrieving the full record for display in the DetailsView. Note that the GridView is populated through an AccessDataSource. An ObjectDataSource could have been used, however, its paging method is a little too complex to be worth the trouble.
Coding the DetailsView
Code for the DetailsView is given in Listing 11-18. Again, its layout should be familiar from previous examples. All fields from the database are rendered in BoundField controls with the BookID field identified as the record key in the DataKeyNames property.
<asp:ObjectDataSource id="BookSource2" Runat="Server" TypeName="BookUpdate" SelectMethod="SelectBook" InsertMethod="InsertBook" UpdateMethod="UpdateBook" DeleteMethod="DeleteBook"> <SelectParameters> <asp:ControlParameter Name="BookID" ControlID="BookGrid" PropertyName="SelectedValue"/> </SelectParameters> </asp:ObjectDataSource> <asp:DetailsView id="BookDetails" Runat="Server" DataSourceID="BookSource2" AutoGenerateRows="False" AutoGenerateEditButton="True" AutoGenerateInsertButton="True" AutoGenerateDeleteButton="True" DataKeyNames="BookID" SelectedIndex="0" ShowHeader="True" RowStyle-Font-Size="10pt" RowStyle-VerticalAlign="Top" OnItemInserted="Rebind_GridView_Add" OnItemDeleted="Rebind_GridView_Delete"> <Fields> <asp:BoundField HeaderText="ID" HeaderStyle-BackColor="#E0E0E0" HeaderStyle-Font-Bold="True" DataField="BookID" ReadOnly="True"/> <asp:BoundField HeaderText="Type" HeaderStyle-BackColor="#F0F0F0" HeaderStyle-Font-Bold="True" DataField="BookType"/> <asp:BoundField HeaderText="Title" HeaderStyle-BackColor="#F0F0F0" HeaderStyle-Font-Bold="True" DataField="BookTitle"/> <asp:BoundField HeaderText="Author" HeaderStyle-BackColor="#F0F0F0" HeaderStyle-Font-Bold="True" DataField="BookAuthor"/> <asp:BoundField HeaderText="Description" HeaderStyle-BackColor="#F0F0F0" HeaderStyle-Font-Bold="True" DataField="BookDescription"/> <asp:BoundField HeaderText="Price" HeaderStyle-BackColor="#F0F0F0" HeaderStyle-Font-Bold="True" DataField="BookPrice"/> <asp:BoundField HeaderText="Qty" HeaderStyle-BackColor="#F0F0F0" HeaderStyle-Font-Bold="True" DataField="BookQty"/> <asp:BoundField HeaderText="Sale" HeaderStyle-BackColor="#F0F0F0" HeaderStyle-Font-Bold="True" DataField="BookSale"/> </Fields> </asp:DetailsView>
The connection between the BookID selected in the GridView and this record's display in the DetailsView is given in the SelectParameters of the ObjectDataSource that populates the DetailsView. As shown in the listing below, the SelectedValue from the GridView supplies the parameter passed to the SelectBook method of the BookUpdate class that selects and returns this record.
<asp:ObjectDataSource id="BookSource2" Runat="Server" TypeName="BookUpdate" SelectMethod="SelectBook" InsertMethod="InsertBook" UpdateMethod="UpdateBook" DeleteMethod="DeleteBook"> <SelectParameters> <asp:ControlParameter Name="BookID" ControlID="BookGrid" PropertyName="SelectedValue"/> </SelectParameters> </asp:ObjectDataSource>
A partial listing of the BookUpdate.vb file containing the SelectBook method is shown below. Remember that this file must be placed in the app_Code directory to be accessible.
Imports System.Data.OleDb Public Class BookUpdate Function SelectBook (BookID as String) 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 * FROM Books WHERE BookID = @BookID" DBCommand = New OleDbCommand(SQLString, DBConnection) DBCommand.Parameters.AddWithValue("@BookID", BookID) DBReader = DBCommand.ExecuteReader() Return DBReader DBReader.Close() DBConnection.Close() End Function ... End Class
The BookID identified in the ObjectDataSource's ControlParameter is passed to this function and becomes the parameter value for selecting this book for display in the DetailsView.
Inserting Records with an ObjectDataSource
The ObjectDataSource for the DetailsView includes an InsertMethod="InsertBook" property identifying InsertBook as the method called when the DetailView's "Insert" button is clicked. This method appears in the BookUpdate class in the continuing script coded below.
Imports System.Data.OleDb Public Class BookUpdate Function SelectBook (BookID as String) ... End Function Function InsertBook (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 DBCommand As OleDbCommand Dim SQLString As String DBConnection = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\eCommerce\Databases\BooksDB.mdb") DBConnection.Open() SQLString = "INSERT INTO Books " & _ "(BookID, BookType, BookTitle, BookAuthor, " & _ "BookDescription, BookPrice, BookQty, BookSale) " & _ "VALUES " & _ "(@BookID, @BookType, @BookTitle, @BookAuthor, " & _ "@BookDescription, @BookPrice, @BookQty, @BookSale)" DBCommand = New OleDbCommand(SQLString, DBConnection) DBCommand.Parameters.AddWithValue("@BookID", BookID) DBCommand.Parameters.AddWithValue("@BookType", BookType) DBCommand.Parameters.AddWithValue("@BookTitle", BookTitle) DBCommand.Parameters.AddWithValue("@BookAuthor", BookAuthor) DBCommand.Parameters.AddWithValue("@BookDescription", BookDescription) DBCommand.Parameters.AddWithValue("@BookPrice", BookPrice) DBCommand.Parameters.AddWithValue("@BookQty", BookQty) DBCommand.Parameters.AddWithValue("@BookSale", BookSale) DBCommand.ExecuteNonQuery() DBConnection.Close() End Function ... End Class
The InsertBook function receives the field names and data types from the DetailsView in its argument list. You must make sure that the names match those in the DetailsView; the order in which the fields are listed is not important. A set of Command Parameters are identified, associating the passed field names with the parameter names in the INSERT statement. Then the record is inserted with the Command object's ExecuteNonQuery() method.
Updating Records with an ObjectDataSource
The ObjectDataSource also includes an UpdateMethod property naming the "UpdateBook" method to call when the DetailView's "Update" button is clicked. This method appears in the BookUpdate class in the continuing script coded below.
Imports System.Data.OleDb Public Class BookUpdate Function SelectBook (BookID as String) ... End Function Function InsertBook (BookID as String, ... ... End Function Function UpdateBook (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 DBCommand As OleDbCommand Dim SQLString As String DBConnection = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\eCommerce\Databases\BooksDB.mdb") DBConnection.Open() SQLString = "UPDATE Books SET " & _ "BookType=@BookType, " & _ "BookTitle=@BookTitle, " & _ "BookAuthor=@BookAuthor, " & _ "BookDescription=@BookDescription, " & _ "BookPrice=@BookPrice, " & _ "BookQty=@BookQty, " & _ "BookSale=@BookSale " & _ "WHERE BookID = @BookID" DBCommand = New OleDbCommand(SQLString, DBConnection) DBCommand.Parameters.AddWithValue("@BookType", BookType) DBCommand.Parameters.AddWithValue("@BookTitle", BookTitle) DBCommand.Parameters.AddWithValue("@BookAuthor", BookAuthor) DBCommand.Parameters.AddWithValue("@BookDescription", BookDescription) DBCommand.Parameters.AddWithValue("@BookPrice", BookPrice) DBCommand.Parameters.AddWithValue("@BookQty", BookQty) DBCommand.Parameters.AddWithValue("@BookSale", BookSale) DBCommand.Parameters.AddWithValue("@BookID", BookID) DBCommand.ExecuteNonQuery() DBConnection.Close() End Function ... End Class
In a similar fashion to the InsertBook function, the UpdateBook function receives a list of field names and data types passed by the ObjectDataSource. Also, a similar set of Parameters are added to the Command object associating the passed fields with parameter values in the UPDATE statement. Note that the order in which these Parameters are identified match the order in which the appear in the UPDATE statement. The Command object then issues its ExecuteNonQuery() method to update the database with the passed values.
Deleting Records with an ObjectDataSource
The ObjectDataSource includes a DeleteMethod property naming the "DeleteBook" method to call when the DetailView's "Delete" button is clicked. This method appears in the BookUpdate class in the continuing script coded below.
Imports System.Data.OleDb Public Class BookUpdate Function SelectBook (BookID as String) ... End Function Function InsertBook (BookID as String,... ... End Function Function UpdateBook (BookID as String, ... ... End Function Function DeleteBook (BookID as String) Dim DBConnection As OleDbConnection Dim DBCommand As OleDbCommand Dim SQLString As String DBConnection = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\eCommerce\Databases\BooksDB.mdb") DBConnection.Open() SQLString = "DELETE FROM Books WHERE BookID = @BookID" DBCommand = New OleDbCommand(SQLString, DBConnection) DBCommand.Parameters.AddWithValue("@BookID", BookID) DBCommand.ExecuteNonQuery() DBConnection.Close() End Function End Class
In this case the only argument passed to the function is the key of the record to be deleted—the BookID value identified by the DataKeyNames property of the DetailsView.
Rebinding the GridView
One other small housekeeping chore is coded in this application. When the ObjectDataSource calls the InsertBook and DeleteBook methods, the database reflects these additions and deletions. However, The GridView is not automatically bound to the changed database. An added record does not appear in the list of BookIDs, nor does the list reflect a deleted BookID. These changes only appear after another record is selected for display. The application would be a bit more elegant if additions and deletions were immediately reflected in the GridView listing. To this purpose, a pair of subprogram calls are added to the DetailsView.
<asp:DetailsView id="BookDetails" Runat="Server" DataSourceID="BookSource2" AutoGenerateRows="False" AutoGenerateEditButton="True" AutoGenerateInsertButton="True" AutoGenerateDeleteButton="True" DataKeyNames="BookID" SelectedIndex="0" ShowHeader="True" RowStyle-Font-Size="10pt" RowStyle-VerticalAlign="Top" OnItemInserted="Rebind_GridView_Add" OnItemDeleted="Rebind_GridView_Delete"> ... </asp:DetailsView>
After a new record is added and after an existing record is deleted through the DetailsView, subprograms are called to update the GridView to reflect these operations. These two on-page subprograms are shown below.
<SCRIPT Runat="Server"> Sub Rebind_GridView_Add (Src As Object, Args As DetailsViewInsertedEventArgs) BookGrid.DataBind() End Sub Sub Rebind_GridView_Delete (Src As Object, Args As DetailsViewDeletedEventArgs) BookGrid.DataBind() End Sub </SCRIPT>
In both cases, the GridView's DataBind() method is called. This action causes the ObjectDataSource associated with the GridView to repopulate it with a new set of BookID values from the database. The GridView is now up-to-date in its display.