Updating a DataView
Typically when people hear the word view , they think of a read-only set of data derived from multiple tables. As we discussed in Chapter 11, the DataView represents a database view in .NET. Whereas you more than likely cannot update a view defined in your database, you are able to update a view in .NET by using the DataView class.
In case you have skipped around in this book, I'll take a moment to review. A DataSet is roughly akin to a subset of a database ”although it is important to remember that data does not have to come from a database in ADO.NET. Within a DataSet you can have one or more DataTable s and zero or more DataRelation s. Each DataTable has what is referred to as its default data view . The default data view is usually analogous to the table if you were to display it in a grid. This default data view is actually represented by a DataView object.
If one or more of a DataView object's AllowNew , AllowEdit , or AllowDelete properties is set to True , you can modify a table by performing insert, edit, and delete operations on a DataView object. One version of the help documentation suggests that DataView objects are read only by default; however, the AllowNew , AllowEdit , and AllowDelete properties are set to True by default, suggesting that DataView objects are editable by default.
I implemented a moderately sized ”257 lines for a book listing seems long ”sample application that demonstrates simple inheritance for DataTable and DataSet objects. The sample EditDataViewDemo.sln contains code that will let you experiment with updating DataView objects. The basic idea is that you set AllowNew , AllowEdit , or AllowDelete to True for each kind of operation you want a specific DataView object to support. When you invoke AddNew on the DataView object, a DataRowView object is returned. You can modify the DataRowView object and send the changes back to the data provider.
When you invoke AddNew , a DataRowView object is created. The row is not added to the underlying table until EndEdit is called. If you call AddNew a second time, EndEdit is implicitly called on the first row because you may edit only one row at a time. When you are finished editing a new row, call EndEdit on the DataRowView object returned by AddNew or call CancelEdit to discard your changes. Listing 12.1 shows how to edit a DataView object. The DataView code is surprisingly easy, so I introduced some additional code that hints at inheritance from the DataSet and DataTable classes.
Listing 12.1 Updating a DataView Object
1: Imports System.Data.OleDb 2: Imports System.Configuration 3: 4: Public Class Form1 5: Inherits System.Windows.Forms.Form 6: 7: [ Windows Form Designer generated code ] 8: 9: Private OrderDetails As OrderDetails 10: Private OrderDetailsView As DataView 11: Private Row As DataRowView = Nothing 12: 13: Private Sub Form1_Load(ByVal sender As System.Object, _ 14: ByVal e As System.EventArgs) Handles MyBase.Load 15: 16: OrderDetails = New OrderDetails() 17: OrderDetailsView = OrderDetails.Orders.DefaultView 18: DataGrid1.DataSource = OrderDetailsView 19: 20: End Sub 21: 22: Private Sub ButtonInsert_Click(ByVal sender As System.Object, _ 23: ByVal e As System.EventArgs) Handles ButtonInsert.Click 24: 25: Row = OrderDetailsView.AddNew() 26: 27: End Sub 28: 29: Private Sub ButtonUpdate_Click(ByVal sender As System.Object, _ 30: ByVal e As System.EventArgs) Handles ButtonUpdate.Click 31: 32: Row.EndEdit() 33: OrderDetails.Update() 34: Row = Nothing 35: 36: End Sub 37: 38: Private Sub ButtonCancel_Click(ByVal sender As System.Object, _ 39: ByVal e As System.EventArgs) Handles ButtonCancel.Click 40: 41: If (Row Is Nothing) Then Return 42: Row.CancelEdit() 43: Row = Nothing 44: 45: End Sub 46: 47: Private Sub ButtonDelete_Click(ByVal sender As System.Object, _ 48: ByVal e As System.EventArgs) Handles ButtonDelete.Click 49: 50: If (Row Is Nothing) Then Return 51: Row.Delete() 52: Row = Nothing 53: 54: End Sub 55: End Class 56: 57: Public Class Database 58: Private Shared FConnection As OleDbConnection 59: 60: Private Shared ReadOnly Property ConnectionString() 61: Get 62: Return ConfigurationSettings.AppSettings("ConnectionString") 63: End Get 64: End Property 65: 66: Public Shared ReadOnly Property Connection() _ 67: As OleDbConnection 68: Get 69: If (FConnection Is Nothing) Then 70: FConnection = New OleDbConnection(ConnectionString) 71: End If 72: Return FConnection 73: End Get 74: End Property 75: 76: Public Shared Function GetAdapter( _ 77: ByVal SQL As String) As OleDbDataAdapter 78: 79: Return New OleDbDataAdapter(SQL, Connection) 80: End Function 81: End Class 82: 83: Public Class Details 84: Inherits DataTable 85: 86: Private FAdapter As OleDbDataAdapter 87: Private FBuilder As OleDbCommandBuilder 88: 89: Public Sub New() 90: MyBase.New("Details") 91: FAdapter = Database.GetAdapter( _ 92: "SELECT * FROM [Order Details]") 93: FAdapter.Fill(Me) 94: 95: FBuilder = New OleDbCommandBuilder(FAdapter) 96: End Sub 97: 98: Public ReadOnly Property Key() As DataColumn 99: Get 100: Return Columns("OrderID") 101: End Get 102: End Property 103: 104: Public Sub Update() 105: FAdapter.Update(Me) 106: AcceptChanges() 107: End Sub 108: 109: End Class 110: 111: Public Class Orders 112: Inherits DataTable 113: 114: Private FAdapter As OleDbDataAdapter 115: Private FBuilder As OleDbCommandBuilder 116: 117: Public Sub New() 118: MyBase.New("Orders") 119: FAdapter = Database.GetAdapter( _ 120: "SELECT * FROM [Orders]") 121: 122: FAdapter.Fill(Me) 123: FBuilder = New OleDbCommandBuilder(FAdapter) 124: End Sub 125: 126: Public ReadOnly Property Key() As DataColumn 127: Get 128: Return Columns("OrderID") 129: End Get 130: End Property 131: 132: Public Sub Update() 133: FAdapter.Update(Me) 134: AcceptChanges() 135: End Sub 136: 137: End Class 138: 139: Public Class OrderDetails 140: Inherits DataSet 141: 142: Public Sub New() 143: MyBase.New("OrderDetails") 144: Tables.Add(New Orders()) 145: Tables.Add(New Details()) 146: Relations.Add("Order Details", Orders.Key, Details.Key) 147: End Sub 148: 149: Public ReadOnly Property Orders() As Orders 150: Get 151: Return Tables("Orders") 152: End Get 153: End Property 154: 155: Public ReadOnly Property Details() As Details 156: Get 157: Return Tables("Details") 158: End Get 159: End Property 160: 161: Public Sub Update() 162: Orders.Update() 163: Details.Update() 164: End Sub 165: 166: End Class
I created a Database class in lines 57 through 81 that makes it convenient to get a connection to the database. I used an App.config file and the appSettings element to externalize the connection string, as introduced in Chapter 11.
Lines 83 through 166 demonstrate that we can inherit from the basic ADO.NET DataTable and DataSet classes and extend them. I created a custom Details class (lines 83 through 109) and a custom Orders class (lines 111 through 137) by generalizing DataTable and a custom OrderDetails class (lines 139 through 166) by generalizing DataSet . As a result, extra information can be added to the classes. In the example, the custom classes are used to perform initialization and define DataRelations. A benefit of this strategy is that we can reuse these classes instead of writing initialization code all over the place. Actually Visual Studio .NET will create advanced, generalized ADO.NET classes for you. I created the examples here to introduce the notion of inheriting from ADO.NET classes. Refer to the Creating a Typed DataSet section later in this chapter for more information on defining XML Schemas and inheriting the DataSet and DataTable classes.
The code for updating the DataView object is very simple, as revealed by the relatively few lines required (see lines 1 through 55). Lines 9 through 11 define an instance of the typed DataSet class mentioned above, a DataView object, and a DataRowView object. The first class is my Frankenstein; the latter two classes are part of ADO.NET.
In the Form1_Load event (lines 13 through 20) I initialized the DataSet object, requested the Orders object's default DataView object (line 17), and associated DataGrid1 's DataSource property with the default DataView object. Normally we have been conditioned to think that a browse view has been created. With ADO.NET we can actually perform CRUD (create, read, update, and delete) operations. For now you can ignore the custom classes; simply focus on the fact that we have a DataView object.
The event handler in lines 22 through 27 demonstrates how to create a new row by calling the DataView.AddNew method. The return value for AddNew is assigned to the DataRowView field introduced in line 11. With the row we can perform other operations, including deleting, canceling, and updating. Lines 29 through 36 demonstrate how to update OrderDetails . I called DataRowView.EndEdit and updated the DataSet object. Chapter 11 showed that this can be accomplished by generating UPDATE SQL and calling the OleDbDataAdapter.Update method and the DataTable or DataSet objects' AcceptChanges methods. My custom Update method (invoked in line 33) conceals these steps, but they are performed. (You will have to download or visually trace the code to see them.) To cancel changes, call DataRowView.CancelEdit (line 42), and DataRowView.Delete will delete an added DataRowView object (line 51).
As you can see, the operations for managing a DataView object are easy to use. This is another benefit of ADO.NET. As the database subframework developers understand more about how programmers need to work with database code, the developers can converge and consolidate steps into higher, abstract steps. More happens with less effort.