Transforming and Filtering Data

You will typically want to present fewer than the total number of records available to your users in order to make your application more user friendly. If you have a table named Invoices that has a few hundred thousand entries, and the user only needs a listing of open invoices for a particular customer, it would not be good design planning to require the user to find the few necessary records. Restricting data returned to the user interface is a process known as filtering . Your database might also record the ClientID field for each invoice, and you may want to provide that data to your users with a more useful name value. Manipulating output data is a process known as transformation .

The Server Explorer

Before we address filtering and data transformation, you should be familiar with the Server Explorer, which is available within the Visual Studio .NET IDE. The Server Explorer provides ready access to available data connections and other services, such as the Crystal Services reporting engine, Event Logs, performance counters, and message queues.

When creating data-bound applications, you can add a data connection by following these steps:

1. Open a Visual Studio .NET project and expand the Server Explorer.

2. Right-click the Data Connections node and select Add Connection from the options provided. This will open the Data Link Properties dialog box.

3. Provide the connection information necessary for your data source. The dialog box will let you select from any of the OLE DB providers on your system to supply this information. The default is the Microsoft OLE DB Provider for SQL Server, but others are available, including Oracle and Jet (Access) database support.

4. Click OK to create the new data connection. If you've supplied information for a SQL Server database, the connection will be created using the SQL Server provider within the .NET Framework; otherwise , the connection will be created using the OLE DB provider. This new connection will remain across all your projects unless you right-click the connection and select Delete.

5. You may then left-click an element within the Data Connections node and drag it to a form within your project to automatically create the necessary code for the relevant data source to be used within your application.

Dragging and dropping a SQL Server database onto a form creates a new SqlConnection object. Tables, views, or columns will create a new SqlDataAdapter object. Stored procedures and table-valued functions will create a new SqlCommand object. All these are members of the System.Data.SqlClient namespace and will be covered in more detail in Chapter 6.

Filtering and Transforming Data

Filtering data involves selecting a subset of available data, and it may be performed within the application client using a DataView object or on the server using a view. A DataView object represents a bindable, filterable view of a DataTable object. A DataSet object contains a collection of DataTable objects as the Tables collection, and the Relations collection that is made up of DataRelation objects.

The following example assumes that you have the default Northwind database available on a SQL Server. It is also possible to create a data connection to the Jet (Access) version of the Northwind database.

You can create a DataSet and filter the resulting data using a DataGrid control by following these steps:

1. Open a Visual Studio .NET Windows Application project and then expand the Server Explorer.

2. Add a form to the application and add a DataGrid control named dgCustomers to the form.

3. Expand the Data Connections node to show a SQL Server connection for the Northwind database.

4. Expand the Tables node of this database and then drag the Customers table and drop it on your form, creating two new data objects ( SqlConnection1 and SqlDataAdapter1 ).

5. Select the SqlDataAdapter1 object and click the Generate Dataset link below the Properties window. Select the option to create a new DataSet object named dsCustomers and click OK.

6. Open the form's code module and add the following:

    Private Sub MyForm_Load(ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles MyBase.Load     SqlDataAdapter1.Fill(DsCustomers1, "Customers")   Dim dvCustomers As DataView = _      New DataView(DsCustomers1.Tables("Customers"))     dvCustomers.Sort = "ContactName"     dvCustomers.RowFilter = "Country = 'France'"     dgCustomers.DataSource = dvCustomers End Sub 

7. Set the form as the startup object for the project and then run the project. The DataGrid will display the filtered data, showing only customers from France, sorted by the ContactName field.

Client-side filtering using the DataView is useful, but it can be very inefficient when dealing with large sets of data. Rather than sending everything over the network and then selecting the needed data, it is more efficient to create a view on the server, which is a derived set of data that acts much like a table. The view can be configured so as to present only the desired subset of data, thus minimizing the amount of data that must be transferred over the network to your client application.

Views may also be used to transform data into a more user-friendly format, such as translating the CustomerID field for a series of invoices into a human-readable CustomerName value before presenting the information to the user interface for display. This type of view is sometimes called a lookup .

You'll learn more about the T-SQL language, which you can use to create views, in Chapter 6.