As you saw in Chapter 2, ADO.NET provides components to view data in data-bound controls. You just drag and drop controls to a form, set their properties, and bind them to data-bound controls. Then you're all set to run your programs. The DataView and DataViewManager classes fall into this category. You can use these classes to represent different views of a DataTable based on different filter and sort criterion. You can use these classes either at design-time or at runtime.
A DataSet is a key component in the ADO.NET data model. It's an in-memory representation of one or multiple DataTables, relations, and constraints. A DataSet stores data from a data source and provides data to the client applications (Web Forms or Windows Forms). A DataSet's contents can be bound to data-bound controls. (We discuss the DataSet class in more detail in "The DataSet: The Heart of ADO.NET."
The DataView is useful when you need to bind a DataTable or multiple DataTable objects with data-bound controls. You can also represent multiple views of the same data by applying a filter and sort on a DataTable, and you can bind these multiple views to different data-bound controls such as DataGrid, DataList, ComboBox, and ListBox controls.
Figure 3-15 shows the relationship between a DataSet, DataTable, DataView, and Windows and Web Forms controls. As you can see, a DataSet contains three DataTable objects, which are represented by three different views. You can bind the three different DataView objects to different data-bound controls to represent data in different ways.
Figure 3-15: Relationship between the DataSet, DataTable, and DataView
A DataSet object plays a vital role in the ADO.NET component model. A DataSet represents a disconnected cache of data in the form of tables, rows, columns, or XML schemas. If you've ever programmed a database application in previous versions of VS, you're probably familiar with recordsets. A recordset object was a way to represent data in your programs. Similar to a recordset, a DataSet represents data in your applications. Once you've constructed a DataSet, you can get or set data in your application or your data source. As mentioned earlier, the DataSet works in both connected and disconnected environments. A DataSet communicates with a DataAdapter and calls the DataAdapter's Fill method to fill data from a DataAdapter. You can fill a DataSet object with multiple tables or stored procedures with the aid of the DataAdapters. You'll see how a DataSet works in a connected environment to access data from multiple data sources in the next chapter. In this section, you'll see how it works with DataTable and DataView objects. (We discuss DataAdapter in more detail in Chapter 4.) Once data is filled to a DataSet from a DataAdapter, you can view it in Windows or Web applications by binding data to data-bound controls through a DataView. You can generate one or multiple views for a DataTables based on the filter and sort criteria.
Figure 3-16 shows the relationship between DataSet, DataAdapter, and DataView objects.
Figure 3-16: The relationship between DataSet, DataAdapter, and DataView objects
As you saw earlier, a DataSet can have multiple DataTable objects. The DataTableCollection object represents all DataSet objects related to a DataSet. The Tables property of the DataSet represents the collection of DataTable objects. The tables relate to each other with DataRelation objects, discussed earlier. DataRelationCollection represents all the available relations in the DataSet. The Relations property represents the DataRelationCollection of a DataSet. A DataSet object stores data in XML format. An XML schema represents a DataSet. A DataSet also defines methods to read and write XML documents. The ReadXml method reads an XML document and fills the DataSet with the XML data, and the WriteXML method writes DataSet data to an XML document. This class also defines the methods ReadXmlSchema and WriteXmlSchema to read and write XML schema.
The DataSet class provides three overloaded constructors to create a DataSet object. You create a DataSet with or without passing a DataSet name in the DataSet contructor. The following code creates two DataSet objects in different ways:
Dim dtSet1 As DataSet dtSet1 = New DataSet("DataSet1") Dim dtSet2 As DataSet = New DataSet("DataSet2")
You just learned how to create a DataSet object. In most cases, you'll probably create a DataSet and use the DataAdapter's Fill and Update methods to fill and save data from a data source. But the DataSet class provides properties that you can get and set as needed.
The CaseSensitive property indicates whether the string comparison within DataTable objects is case sensitive. This property affects how sorting, searching, and filtering operations are performed on DataTable objects of a DataSet, when |you use the Select method of DataTable to filter and sort data. By default the |CaseSensitive property is False. Use the following to set it to True:
DtSet1.CaseSensitive = True
The DataSetName property represents the name of a DataSet. The following code first sets a DataSet name and then displays it in a message box:
Dim dtSet1 As DataSet dtSet1 = New DataSet("DataSet1") dtSet1.DataSetName = "CustomDataSet" MessageBox.Show(dtSet1.DataSetName.ToString())
The DefaultViewManager (read only) property returns a custom view of the data of a DataSet. (We discuss this in more details in "The DefaultViewManager.")
The EnforceConstraints property is a Boolean type property, which makes sure constraint rules are followed when data of a DataSet is being updated. The default value of the EnforceConstraints property is True. If you don't want the Update method to check the constaints, just set this property as False.
Do you remember the ExtendedProperties property of the DataTable? The ExtendedProperties property attaches some additional custom information about a DataTable. Similar to the DataTable, the DataSet also provides the ExtendedProperties property. Listing 3-28 sets and reads the custom properties of a DataSet.
Listing 3-28: Setting and Reading Custom Properties of a DataSet
Dim dtSet1 As DataSet dtSet1 = New DataSet("DataSet1") dtSet1.DataSetName = "CustomDataSet" dtSet1.ExtendedProperties.Add("Description", "The Name Column") dtSet1.ExtendedProperties.Add("Author", "Mahesh Chand") dtSet1.ExtendedProperties.Add("UserId", "MCB") dtSet1.ExtendedProperties.Add("PWD", "Password") ' Remove Author property dtSet1.ExtendedProperties.Remove("Author") ' Read custom properties Dim str As String Dim i As Integer str = dtSet1.ExtendedProperties("Description").ToString() str = str + ", " + dtSet1.ExtendedProperties("UserId").ToString() str = str + ", " + dtSet1.ExtendedProperties("PWD").ToString() MessageBox.Show(str)
The HasErrors property finds out if any of the rows of a DataSet has any errors. The DataTable also provides this property to check if a particular row has any errors. (We show you how to use this property in a moment.)
A CultureInfo object contains the data about a machine locale. The Locale property gets and sets this data. The following code uses this property to read the information:
Dim cInfo As System.Globalization.CultureInfo cInfo = dtSet1.Locale Console.WriteLine(cInfo.DisplayName, cInfo.EnglishName)
As mentioned earlier (and in more detail in Chapter 6), a DataSet is represented by an XML schema, which has a namespace and prefix. The Namespace and Prefix properties represent the namespace and prefix of the schema, respectively. These properties are both readable and writeable:
dtSet1.Namespace = "CustDSNamespace" dtSet1.Prefix = "CustPrefix"
The Relations property returns a collection of relations that a DataSet has. (We discuss this property in later sections and in Chapter 4.)
As discussed earlier, the Tables property of a DataSet represents a collection of DataTables attached to a DataSet. The Tables property is of type DataTableCollection, which was discussed earlier. The DataTableCollection class provides methods to add and remove DataTable objects to a collection.
You've already seen how to create a DataTable and add columns and rows to a DataTable by using the DataRow and DataColumn. As mentioned earlier, a DataSet is a collection of DataTable objects. The Tables property of DataSet gets all the attached DataTables of a DataSet. The Tables property, which is a type of DataTableCollection, also adds DataTable objects to a DataSet and removes them. Listing 3-29 creates four DataTable objects and adds them to a DataSet using the DataSet.Tables.Add method. The DataSet is bound to a DataGrid to view the contents in a DataGrid.
Listing 3-29: Adding DataTable Objects to a DataSet
Dim tbl1 As DataTable = New DataTable("One") Dim tbl2 As DataTable = New DataTable("Two") Dim tbl3 As DataTable = New DataTable("Three") Dim dtSet1 As DataSet = New DataSet("CustDtSet") dtSet1.Tables.Add(custTable) dtSet1.Tables.Add(tbl1) dtSet1.Tables.Add(tbl2) dtSet1.Tables.Add(tbl3) DataGrid1.DataSource = dtSet1.DefaultViewManager
In this code, custTable is a DataTable created using the methods discussed.
The output of Listing 3-29 looks like Figure 3-17. If you click a DataTable link, you can see the data of the DataTable (if it has any).
Figure 3-17: Attaching DataTables to a DataSet
The Clear method removes all the rows from a DataSet; however, it does not destroy the structure of a DataSet. The Copy method copies the contents with schema of a DataSet to another DataSet. The Clone method only copies the structure of a DataSet to another. Listing 3-30 uses all three members.
Listing 3-30: Using Copy, Clone, and Clear Methods of DataSet
Dim dtSet1 As DataSet = New DataSet("CustDtSet") dtSet1.Tables.Add(custTable) Dim dtSet2 As DataSet = New DataSet() dtSet2 = dtSet1.Clone() dtSet2 = dtSet1.Copy() dtSet1.Clear()
So far, we've talked about filling data from a DataAdapter to a DataSet and saving data back to a data source using the Update method of DataSet. The Update method takes a DataSet and saves its results in a data source. A DataAdapter belongs to a data provider, so we discuss it in Chapter 4.
The GetChanges method helps you find the changed data of a DataSet so you can send only changed data back to the data source instead of sending an entire DataSet. It helps to reduce the network traffic and make the save process faster.
The GetChanges method returns a DataSet, which contains all the changes made to a DataSet since it was last loaded or the AcceptChanges method was called.
The GetChanges method has two overloaded forms. The first form takes no parameters and returns all the results. The second form lets you filter the results based on the row states, and it takes a parameter of type DataRowState.
The HasChanges method determines if a DataSet has any changes since it was last saved or loaded. The following snippet shows how to use the HasChanges and GetChanges methods (you'll see these methods in more detail in Chapter 4).
If Not dtSet1.HasChanges(DataRowState.Modified) Then Exit Sub Dim dtSet2 As DataSet dtSet2 = dtSet1.GetChanges(DataRowState.Modified) SqlDbDataAdapter1.Update(dtSet2)
The Merge method merges two DataSet objects and saves the results in one DataSet. The Merge method has seven overloaded forms. These forms allow you to merge a DataRow, a DataTable, and a DataSet with an existing DataSet. The first parameter of the Merge method is a DataRow, DataTable, or DataSet. The second optional parameter is a Boolean type—a True value indicates that the changes should be preserved and False indicates that changes won't be preserved. The third optional parameter is a type of MissingSchemaAction, which indicates what action should be taken when the schema of added DataRow, DataTable, or DataSet does not match the schema of the current DataSet. The MissingSchemaAction enumeration has four values: Add, AddWithKey, Error, and Ignore. The Add value adds necessary columns to complete the schema. The AddWithKey value adds the necessary information with a primary key. The Error value generates an error. The Ignore value ignores the extra columns.
Listing 3-31 merges two DataSet objects. The custTable and ordersTables are two DataSet objects created by the CreateCustomersTable() and CreateOrdersTable() methods, as discussed in "The DataSet: The Heart of ADO.NET" section.
Listing 3-31: Merging Two DataSetse
Dim dtSet1 As DataSet = New DataSet("EmpDataSet") Dim dtSet2 As DataSet = New DataSet("OrdersDataSet") dtSet1.Tables.Add(custTable) dtSet2.Tables.Add(ordersTable) dtSet1.Merge(dtSet2, False, MissingSchemaAction.Add) DataGrid1.DataSource = dtSet1.DefaultViewManager
As mentioned, the Merge method also lets you merge an array of a DataRow or a DataTable object. Listing 3-32 shows you how to merge rows and a DataTable with a DataSet.
Listing 3-32: Merging DataRow and DataTable Objects
Dim str As String = "OrderId >= 2 " Dim rows() As DataRow = ordersTable.Select(str) dtSet1.Tables.Add(custTable) dtSet2.Tables.Add(ordersTable) dtSet1.Merge(rows) dtSet1.Merge(ordersTable, True, MissingSchemaAction.Ignore)
There are a few more DataSet methods left to discuss. The AcceptChanges method saves all the changes made to a DataSet since it was loaded or the last time AcceptChanges was called. The RejectChanges method rolls back all the changes made to a DataSet since it was created or the last time AcceptChanges was called. These methods are discussed in more details in next chapter.
The ReadXml and ReadXmlSchema methods read an XML document and XML schema document, respectively. The WriteXml and WriteXmlSchema methods save DataSet data to XML document and XML schema document, respectively. (We discuss these methods in Chapter 6 in more detail.)
As you may recall from earlier discussions, there are two kinds of a DataSet: typed or untyped. A typed DataSet first comes from the DataSet class and then uses an XML schema (.xsd file) to generate a new class. You saw how to create a typed DataSet using VS .NET in Chapter 2. An untyped DataSet has no built-in schema. You create an instance of a DataSet class and call its methods and properties to work with the data sources. All elements of an untyped DataSet are collections. In this chapter, you'll work with untyped DataSets.
Both kinds of DataSet have their own advantages and disadvantages. Typed DataSets take less time to write applications but offer no flexibility. They're useful when you already know the schema of a database. The biggest advantage of typed DataSets is the VS .NET IDE support. As you saw in Chapter 2, you can drag a database table, its columns, or stored procedures to a form in your application, and the IDE generates typed DataSet for you. After that, you can bind these DataSets to the controls. However, there are many occasions when you don't know the schema of a database. In those cases, the untyped DataSets are useful. The untyped DataSets also provide the flexibility of connecting with multiple data sources. And you can use them without the VS .NET IDE.
Another powerful feature of ADO.NET is the ability to create several different views of the same data. You can sort these views differently and filter them on different criteria. They can contain different row state information.
A DataView represents a customized view of DataTable that you can bind to Windows Forms and Web Forms controls. Using DataView sort and filter features, you can also have multiple views of a single DataTable. Using RowFilter and Sort properties, you can apply a filter on a DataView and sort its contents before binding it to a data-bound control. The AddNew method adds a new row to a DataView, and the Delete method deletes a row from a DataView. You can use the Find and FindRows methods to search for rows based on the defined criteria.
Table 3-9 describes some of the DataView properties, and Table 3-10 describes some of its methods.
Indicates whether deletes are allowed.
Indicates whether edits are allowed.
Indicates whether new rows can be added.
Indicates whether to use default sort. True means use the default sort and False means no.
Represents the number of records in a DataView after RowFilter and RowStateFilter have been applied.
DataViewManager associated with this view.
Represents an item of a row.
Represents the expression used to filter rows to view in the DataView.
You can use a row state filter using this property.
Represents the sort column and sort order.
DataTable attached with this view.
Adds a new row to the DataView
Begins the intialization if a data view was previously used
Ends the initialization started by BeginInit
Deletes a row
Finds a row in the DataView based on the specified criteria
Returns an array of rows based on the specified criteria
The DataView constructor allows you to create a DataView object from a DataTable, which you can use with or without a DataSet. In Listing 3-33, the custTable and ordersTable are two DataTable objects. As you can see, the code creates a DataView directly from a DataTable and creates a second DataView through a DataSet.
Listing 3-33: Creating DataView Objects from a DataTable
' Creating a DataView from DataTable Dim dtView1 As DataView = New DataView(custTable) ' Creating a DataView through DataSet Dim dtSet1 As DataSet = New DataSet() dtSet1.Tables.Add(ordersTable) Dim dtView2 As DataView = New DataView(dtSet1.Tables(0))
Binding a DataView with a data-bound control is pretty easy. You set the DataSource and DisplayMember properties of a data-bound control. For example, the following code binds two DataView objects with a DataGrid and a ListBox control.
DataGrid1.DataSource = dtView1 ListBox1.DataSource = dtView2 ListBox1.DisplayMember = "Name"
Chapter 7 discusses data-bound controls in more detail.
The AddNew method of a DataView, which returns a DataRowView, adds a new row to the DataView. The AddNew method is followed by a call of EndEdit. The BeginEdit and EndEdit pair edits the values of a row. The AllowDelete, AllowNew, and AllowEdit properties allow deleting, adding, and updating in a DataView if they're set to True.
The Delete method of a DataView deletes a row specified by the index of the row. The Count property returns the total number of rows in a DataView.
Listing 3-34 uses the previously discussed methods and properties to add, delete, and edit rows of a DataView.
Listing 3-34: Adding, Updating, and Deleting Rows of a DataView
' Creating a DataView from DataTable Dim dtView1 As DataView = New DataView(custTable) ' Creating a DataView through DataSet Dim dtSet1 As DataSet = New DataSet() dtSet1.Tables.Add(ordersTable) Dim dtView2 As DataView = New DataView(dtSet1.Tables(0)) ' Set AllowDelete, AllowNew and AllowEdit to true dtView1.AllowDelete = True dtView1.AllowNew = True dtView1.AllowEdit = True ' Edit the data of first row dtView1(0).BeginEdit() dtView1(0)("Name") = "Edited Name" dtView1(0)("Address") = "Edited Address" dtView1(0).EndEdit() ' Delete the first row from DataView ' the Delete method takes an index of row starting ' at 0 If dtView1.AllowDelete Then dtView1.Delete(0) End If ' Add a new row Dim drv As DataRowView = dtView1.AddNew ' Change values in the DataRow. drv("id") = 1010 drv("Name") = "New Name" drv("Address") = "New Address" drv.EndEdit() ' Count number of rows MessageBox.Show(dtView1.Count.ToString())
Searching and sorting are two major operations that a DataView does to filter data. The Sort property of DataView sorts rows of a DataView. The Sort property takes a string value, which contains the sort criteria with one or more than one DataTable columns separated by a comma (,) and followed by ASC or DESC, where ASC is for sorting rows in ascending order and DESC for sorting rows in descending order. The following code shows how to use one or more than one column with the ASC and DESC options:
dtView1.Sort = "Name, Address ASC" dtView1.Sort = "Name DESC"
The RowFilter property of a DataView filters the rows depending on the given criteria. The RowFilter takes a criteria, which contains columns and their values. The following code returns all rows that contain Name column values of Miranda:
dtView1.RowFilter = "Name = 'Miranda'"
Besides the Sort and RowFilter properties, the DataView class provides Find and FindRow methods to find rows. The Find method requires a Sort field and takes a parameter of type Object or an array of Object. The FindRow method returns an array of DataRowView, whose column matches the specified sort key value.
Listing 3-35 uses the Find method to find the value "Data Quest" in the column Name, which returns an index of the row.
Listing 3-35: Finding Rows in a DataView Using the Find Method
' Creating a DataView from DataTable Dim dtView1 As DataView = New DataView(custTable) ' Creating a DataView through DataSet Dim dtSet1 As DataSet = New DataSet() dtSet1.Tables.Add(ordersTable) Dim dtView2 As DataView = New DataView(dtSet1.Tables(0)) dtView2.Sort = "Name" Dim val As Object = "Data Quest" Dim pos As Integer = dtView2.Find(Val) MessageBox.Show(pos.ToString())
A DataViewManager contains a collection of views of a DataSet—one view for each DataTable in the DataSet. The DataViewManager has a DataViewSettings property that enables the user to construct a different view for each DataTable in the DataSet. If you want to create two views on the same DataTable, you need to create another instance of the DataViewManager and construct the DataViewSettings for that particular view. Then you construct a DataView using the DataViewManager. For example, in the Orders DataSet, you may want to filter out the orders with an EmployeeID of 4 and sort the orders by the date they were shipped. You can retrieve records for EmployeedId = 4 sorted on the date they were shipped using the sort and filter properties of the DataSet and attaching the filtered and sorted data to a DataView or a DataViewManager.
To construct a DataViewManager, you can either use the Default constructor or pass in a DataSet object. For example:
view = new DataViewManager ()
or, for example:
view = new DataSetView(myDataSet)
The DataViewManager has a few properties you need to know about to utilize it effectively. Table 3-11 shows the main properties.
The DataSet being viewed of type DataSet.
Contains the collection of TableSetting objects for each table in the DataSet. The TableSetting object contains sorting and filtering criteria for a particular table.
DataViewManager contains the CreateDataView method, which allows you to create a DataView object for a particular table in your DataSet. You can construct the DataView for the table with the DataViewManager's DataViewSettings for the particular DataTable of the DataSet. You can also adjust settings for the DataView by assigning filter and sort properties directly in the DataView.
You'll see DataView and DataViewManager objects in the sample applications in Chapter 4.