Transforming and Filtering Data

When you're dealing with data from a database, you might find that the data is not in the exact form that you'd like to display to the user . Perhaps 5,000 rows are in the Customer table and you want to pull out the single row that interests your users. Or perhaps you want to show customer names with orders, but the Orders table stores only the CustomerID. In this section, you'll look at a few tools the .NET Framework offers for manipulating database data.

Using Server Explorer

You can invoke the Server Explorer window in Visual Studio .NET by selecting View, Server Explorer.

To work with bound data, you use the Data Connections node in Server Explorer and its children.

You've already seen that you can add a data connection to your project from within the Data Form Wizard. Those data connections are automatically available in Server Explorer as well. You can also add a data connection directly from Server Explorer by right-clicking the Data Connections node and then selecting Add Connection. This opens the Data Link Properties dialog box where you can fill in the connection information for your data source (refer to Figure 6.1).

Server Explorer can act as a source for drag-and-drop operations. Different visual data objects can be created, depending on which type of object you drag from Server Explorer:

• Dragging and dropping a database creates a SqlConnection object.

• Dragging and dropping a table, view, table column, or view column creates a SqlDataAdapter object (as well as a SqlConnection object if one does not exist already).

• Dragging and dropping a stored procedure or table-valued function creates a SqlCommand object (as well as a SqlConnection object if one does not exist already).

These three objects are members of the System.Data.SqlClient namespace; you'll learn more about this namespace in Chapter 7. This chapter concentrates more on what you can do with the objects than on the code that creates and supports them.

The following example demonstrates how to implement complex binding between a DataGrid control and a SQL Server table visually without writing much code:

1. Add a new Visual C# ASP.NET Web Application project named Example6_5 to the current solution.

2. Place a DataGrid control ( dgCustomers ) on the Web form.

3. Open Server Explorer. Expand the tree under Data Connections to show a SQL Server data connection that points to the Northwind sample database, then to the Tables node of the SQL Server, and then to individual tables.

4. Drag the Customers table from Server Explorer and drop it on the form. This creates two visual data objects: sqlConnecton1 and sqlDataAdapter1 .

5. Select the sqlDataAdapter1 object. Then click the Generate Dataset link under the Properties window. In the Generate Dataset window, choose to create a new dataset named dsCustomers .

6. Set the DataSource property of the DataGrid control to dsCustomers1 . Then set the DataMember property of the DataGrid control to Customers .

7. Switch to Code view and add the following code in the Page_Load() event handler:

    private void Page_Load(object sender, System.EventArgs e) {     // Move the data from the database to the DataGrid     sqlDataAdapter1.Fill(dsCustomers1, "Customers");     dgCustomers.DataBind(); } 

8. Set the project as the startup project and run the project. The DataGrid control displays all the data from the Customers table, and the code uses the visual data objects you created on the form to make the connection between the DataGrid control and the table.

Filtering Data

Filtering data refers to the process of selecting only some data from a larger body of data to appear on the user interface of a form. This can be a critical part of avoiding information overload for end users of an application. In most cases, users will need only a small subset of the larger data body. This section examines two ways to filter data in your applications.

Filtering with a DataView Object

To understand the DataView object, you need to know a little bit about the internal structure of the DataSet object. A DataSet object contains two collections. The Tables collection is made up of DataTable objects, each of which represents data from a single table in the data source. The Relations collection, on the other hand, is made up of DataRelation objects, each of which represents the relationship between two DataTable objects.

The DataView object supplies one more piece of this puzzle: It represents a bindable, customized view of a DataTable object. You can sort and filter the records from a DataTable object to build a DataView object.

If you want to modify Example6_5 to display a sorted list of all customers from France, you need to modify the Page_Load() method, like so:

 private void Page_Load(object sender, System.EventArgs e) {     // Move the data from the database to the DataSet     sqlDataAdapter1.Fill(dsCustomers1, "Customers");     // Create a dataview to filter the Customers table     DataView dvCustomers = new DataView(dsCustomers1.Tables["Customers"]);     // Apply a sort to the dataview     dvCustomers.Sort = "ContactName";     // Apply a filter to the dataview     dvCustomers.RowFilter = "Country = 'France'";     // and bind the results to the grid     dgCustomers.DataSource = dvCustomers;     dgCustomers.DataBind(); } 

Filtering at the Database Server

The DataView class provides a useful way to filter data, but it's inefficient if you're working with large amounts of data. That's because all the data is first retrieved from the database server and stored on the Web server. Ideally, you should filter the data on the database server itself. One way to do this is by basing a SqlDataAdapter object on a view instead of a table, as described in the following example:

1. Add a new Visual C# ASP.NET Web Application project named Example6_6 to the current solution. Place a DataGrid control ( dgCustomers ) on the Web form.

2. Open Server Explorer and expand the tree under Data Connections to show a SQL Server data connection that points to the Northwind sample database; then expand the Views node of the SQL Server.

3. Right-click the Views node and select New View. In the Add Table dialog box, select the Customers table, click Add, and then click Close. This puts you in the view designer in Visual Studio .NET.

4. Click the check boxes for All Columns , ContactName, and Country in the column listing. Then fill in the details of the view as shown in Figure 6.4.

   Figure 6.4. You can create a new SQL Server view by using the design tools in Visual Studio .NET.

   

5. Click the Save button and save the view as vwFranceCustomers . Close the design window for the view.

6. Drag the vwFranceCustomers view from Server Explorer and drop it on the form. You get a configuration error because the view is read-only, but that's not a problem because you're not writing any data back to the database. Click OK to create objects. This creates two visual data objects: sqlConnection1 and sqlDataAdapter1 .

7. Select the sqlDataAdapter1 object, and click the Generate Dataset link below the Properties window. In the Generate Dataset window, choose to create a new dataset named dsCustomers . Click OK.

8. Set the DataSource property of the DataGrid control to dsCustomers1 . Set the DataMember property of the DataGrid control to vwFranceCustomers .

9. Switch to Code view and add the following code in the Page_Load() event handler:

    private void Page_Load(object sender, System.EventArgs e) {     // Move the data from the database to the DataGrid     sqlDataAdapter1.Fill(dsCustomers1, "vwFranceCustomers");     dgCustomers.DataBind(); } 

10. Set the project as the startup project and run the project. The DataGrid control displays only the data from customers in France, sorted by the ContactName column. This time, the filtering and sorting is all done by the view you created on the SQL Server.

If you're operating over a slow network or Internet connection, this type of server-side filtering in the database server can save a lot of time.

Transforming Data

This section takes a brief look at transforming data by applying lookups. A lookup is a technique for replacing one column of data with another column from the same table. For example, given an order ID value, you could look up the corresponding company name that placed the order. The following example demonstrates how to accomplish this using server-side views:

1. Add a new Visual C# ASP.NET Web Application project named Example6_7 to the current solution. Place a DataGrid control ( dgOrders ) on the Web form.

2. Open Server Explorer and create a new view in the Northwind database. In the Add Table dialog box, add the Customers table and the Orders table.

3. In the View designer, click the check boxes for CompanyName in the Customers table and all columns except for CustomerID in the Orders table. Figure 6.5 shows the completed view.

   Figure 6.5. You can create a new SQL Server view that joins data from two tables together.

   

4. Save the view as vwCustOrders . Drag the vwCustOrders view from Server Explorer and drop it on the form. Ignore the configuration error and click OK to create objects. This creates two visual data objects, sqlConnection1 and sqlDataAdapter1 .

5. Select the sqlDataAdapter1 object and click the Generate Dataset link below the Properties window. In the Generate Dataset window, create a new DataSet object named dsOrders . Click OK.

6. Set the DataSource property of the DataGrid control to dsOrders1 . Set the DataMember property of the DataGrid control to vwCustOrders .

7. Switch to Code view and add the following code to the Page_Load() event handler:

    private void Page_Load(object sender, System.EventArgs e) {     sqlDataAdapter1.Fill(dsOrders1, "vwCustOrders");     dgOrders.DataBind(); } 

8. Set the project as the startup project for the solution and run the project. The DataGrid control displays order data with full company names. The lookup operation is performed by the SQL Server view before the data is even sent to the application.