Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Exploring Secrets of BindingSource Filters : Page 2

Learn how to give users fast DataGridView filtering power so they can hone in on the data they're truly interested in.

Adding a Table to the DataSet
Switch back to the Solution Explorer pane in Visual Studio and double-click the AdventureWorksDataSet.xsd file to open the dataset designer (see Figure 7).

Figure 7. Empty DataSet Designer: The empty dataset designer appears when you double-click the AdventureWorkdDataSet.xsd file in Solution Explorer.
Figure 8. TableAdapter Configuration Wizard: The first screen of the TableAdapter Configuration Wizard prompts you to select a data connection.
Right-click the designer surface to open a context menu, and then choose "Add --> Table Adapter." This opens the TableAdapter Configuration Wizard (see Figure 8).

Figure 9. Command Type Selection: For this example, leave the first choice (Use SQL statements) selected as the command type.
You've already established a connection to the AdventureWorks database; just make sure it is selected in the drop-down box, and then select Next to get to the command type selection screen (see Figure 9).

Select Next from the Command Type Selection screen, and you will be prompted to enter a query. Copy this SQL text into the wizard then select Finish.

SELECT i.[CustomerID] ,c.[Title] ,c.[FirstName] ,c.[MiddleName] ,c.[LastName] ,c.[Suffix] ,c.[Phone] ,c.[EmailAddress] ,c.[EmailPromotion] ,a.[AddressLine1] ,a.[AddressLine2] ,a.[City] ,[StateProvinceName] = sp.[Name] ,a.[PostalCode] FROM [Sales].[Individual] i INNER JOIN [Person].[Contact] c ON c.[ContactID] = i.[ContactID] INNER JOIN [Sales].[CustomerAddress] ca ON ca.[CustomerID] = i.[CustomerID] INNER JOIN [Person].[Address] a ON a.[AddressID] = ca.[AddressID] INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID] WHERE i.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID] FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'I');

Author's Note: The preceding code is a slightly pared down version of the query used to generate the Sales.vIndividualCustomer view in the AdventureWorks DB. You could generate the query yourself in SqlServer 2005 Management Studio Express by opening the context menu for the view and selecting Script View As --> Create To --> New Query Editor Window.)

The wizard will close and you'll see a table on the dataset designer surface. Go to the Properties pane and change the name from the default DataTable1 to CustomerTable. Your screen should resemble Figure 10.

Figure 10. Adding a TableAdapter: The figure shows the Dataset Designer with the successfully created CustomerTable table adapter.
VS has taken the liberty of defining the first field (CustomerID) as the primary key, designated with a small key icon in the table. For this query, though, that is not sufficient to uniquely define rows in the data; if you used the default, you'd get a key violation at runtime. To avoid that, select both the CustomerID and the AddressLine1 fields, right-click to open the context menu, and choose "Set Primary Key." You should see key icons next to both fields.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.