Working with Data Views

There's another handy kind of object that's much like a dataseta data view object. You normally use datasets to hold local copies of data from a data source, but data views are also available as alternative data containers. They're much like read-only datasetssnapshots of your data (as the term data view suggests)and can give you faster access to your data than datasets can. Their chief advantage is that they let you filter data from a dataset according to criteria you set, without having to retrieve that data from the data source again.

You can use SQL-like expressions in a data view's RowFilter property to filter the records you want to take a look at. For example, say that you want to look at only those authors whose last name is White from the authors table. You could loop over all the records in a dataset, choosing only those au_lname field holds " White ", or you could simply bind a data view to the dataset and set the data view's RowFilter property to au_lname = 'White' . (Note the single quotes here; C# won't accept au_lname = "White" as the value of this property, because it will surround the text with double quotation marks before passing it on to SQL Server, and the string "au_lname = "White"" isn't going to work with SQL Server.) In other words, data views let you manipulate and even sort the data from a dataset as you want. They give you the ability to execute SQL-like expressions on the data in a dataset without having to connect to and retrieve data from the data store. And, like datasets, you can also bind data views to controls.

You can see a data view in action in the ch09_04 example in the code for this book. To follow along, create a Windows application named ch09_04. Use a data adapter to connect to the authors table in the pubs database and generate a dataset, dataSet11 , to hold the authors table from that data adapter (select all fields in the authors table). Next , drag a new data view object from the Data tab in the toolbox onto the application's main form. As with other data objects, this new data view, dataView1 , will appear in the component tray at design time.

You bind a data view to a specific data table using its Table property, so set that property to the authors table from dataSet11 now (when you select the Table property in the properties window, a list will appear displaying the available tables to work with). Next, add a data grid to the main form, and set its DataSource property to dataView1 . You don't need to set the data grid's DataMember property, because we're only working with one table in the data view. By doing this, you've connected the data grid to the authors table through the data view.

This is where the data view's RowFilter property comes in. We want to look only at records in which the author's last name is White, so set the data view's RowFilter property to au_lname = 'White' . To finish the code, make sure you populate the dataset from the data adapter, as usual:

 private void Form1_Load(object sender, System.EventArgs e) {  sqlDataAdapter1.Fill(dataSet11);  } 

And that's it; you can see the results in Figure 9.24, where the single author whose last name is White is displayed. Using a data view, you've been able to create a filtered snapshot of your data and bound it to a data grid, pointing out one of the uses of data viewssearching data for a record matching a specific criterion.

Figure 9.24. Using a data view.

Besides filtering rows, you can also use a data view's RowStateFilter property to filter rows depending on their state . For example, you can examine the rows of a table that have been marked as deleted (before you've called your data adapter's Update method to actually delete them back in the database) or are new in a data view using this property. Here are the possible states you can specify, and the types of rows they match, from the DataRowState enumeration:

• DataRowState.Added Identifies added rows.

• DataRowState.CurrentRows Identifies current rows (including all unchanged, new, and modified rows).

• DataRowState.Deleted Identifies deleted rows.

• DataRowState.ModifiedCurrent Identifies current rows (even if they have been modified from the original data).

• DataRowState.ModifiedOriginal Identifies original rows before they have been modified.

• DataRowState.None Identifies no rows.

• DataRowState.OriginalRows Identifies original rows, including unchanged and deleted rows.

• DataRowState.Unchanged Identifies the unchanged rows.

For example, here's how you might take a look at the rows that have been marked as deleted in a data view:

 private void Form1_Load(object sender, System.EventArgs e) {   sqlDataAdapter1.Fill(dataSet11);  dataView1.RowStateFilter = DataViewRowState.Deleted;  } 

You can also use data views to navigate through a dataset. For example, if you store the position of the record you want in a variable named position , you can use code like this to load the corresponding record into a data view, which will also update any controls bound to the data view:

 string id = dataSet11.Tables[0].Rows[position].Item["au_id"]; dataView1.RowFilter = "au_id = '" + id + "'"; 

In fact, navigating through the data in a dataset is such a common thing to do that there's a great deal of support for it in the FCL. In particular, you can use the BindingContext property of Windows forms to set your position in datasets that your controls are bound to. And that brings up our next topicdata binding.