Configuring a TableAdapter

Our next task is to step through the process of modifying and configuring the TableAdapter. Generally, you're going to want to make changes to the generated code in almost every case. Let's look at several typical issues:

• The DSCW does not permit you to create a T-SQL query of any kind when it's first constructed. For many situations, I prefer to JOIN two or more Tables or Views to return an SQL product. In this case, you'll need to use the Query Designer to build the SELECT statement.
  
  
• Remember, the SELECT query generated returns only the specified columns (as shown in Figure 6.12), but the generated T-SQL does not include any means of limiting the number of rows returned. Unless you're creating a TableAdapter for a lookup table where you need all of the rows in a Table, you'll want to modify the WHERE clause or at least set a TOP clause to return N rows or modify the WHERE clause to include parameters to focus the query on a specific subset of rows.
  
  
• In some cases, you need to execute a Function within the T-SQL used to return the rowset. I use functions (as described in Chapter 13, "Managing SQL Server CLR Executables") to manage "multi-select" pick lists.
  
  
• You might also need to alter the order in which rows are returned by SQL Server. In this case, you'll need to add an ORDER BY clause.
  
  
• In some cases, you might want to change (alias) the column names returned or reorder the columns so that they appear in complex bound controls (like a DataGridView) in a more readable fashion.
  
  
• You might want to add T-SQL to the SELECT to add "All" or "None" selectors to lookup table queries. This can be done with a UNION query, as I'll illustrate later.
  
  

Yes, there are probably other reasons to reopen the Data Source and make modifications, so let's walk through that process.

1.	Start by opening the Data Sources window. I have ours docked to the Solution Explorer (as shown in Figure 6.14).
2.	Right-click on the Data Source icon of choiceyes, there might be several Data Sources to choose from. At this point, you're confronted with a set of options, as shown in Figure 6.15. Figure 6.15. The Data Source options menu. Depending on what you want to do, click on one of the option icons: Add New Data Source... to reopen the DSCW from the beginning. This new Data Source can use an entirely different data source (database, Web Service, or business object) or use the same ConnectionString as your existing Data Source. Edit DataSet with Designer to open the GUI Data Source editor. This is one way to modify or extend an existing Data Source. This option is used to make changes to the SQL queries, add parameters, fine-tune the columns, or simply add objects to the DataSet. Configure DataSet with wizard... to reopen the DSCW from the object-selection dialog (as shown in Figure 6.12). Use this option to simply add another Table, View, stored procedure, or Function to an existing Data Source. Refresh to repopulate the Data Source window from the stored XSD. Preview Data... to open a dialog to run the Fill query associated with a selected DataSet. I'll walk through this a bit later.
3.	For this exercise, click on "Edit DataSet with Designer"it affords the most options and can address all of the issues I raised earlier.
4.	This opens the GUI DataSet designer. This tool is basically an XML editor designed to modify the DataSet's XSD file. If you've been following along, you should see something like Figure 6.16. Figure 6.16. The GUI DataSet designer showing the "dsAuthors" DataSet. Upon closer inspection, you can see that the DataSet includes a primary key (the Au_ID column), it returns three columns (Au_ID, Author, and Year_Born), the TableAdapter is named "AuthorsTableAdapter", and both a Fill and a GetData fetch method have been created (by default) in the TableAdapter. No, you did not do anything when executing the DSCW to affect these settings other than choosing the Authors Table and selecting the columns to return. All of the rest were generated by default. Let's explore a bit deeper into the generated TableAdapter.
5.	Right-click on the Authors table in the DataSet designer (not on the Fill SQL). This opens yet another menu, as shown in Figure 6.17. Figure 6.17. Exploring the DataSet designer options. These options permit you to perform a number of operations on the targeted Data Source DataSet "table". Click on: The "Add" submenu to open another menu that permits you to: Add a new Query to the DataSet Add a Relation to another table in the DataSet Add a Key to the DataSet Add a new Column to the DataSet I'll discuss all of these options later in this section. Configure... to open the TableAdapter Configuration wizard (TACW). I spend quite a bit of time here, as the TACW permits you to make quite a few changes to the query used to return rows to the TableAdapter. That's where I'm going next. The Cut, Copy, Paste, or Delete items, to manage the "Authors" table object in the designer. These options let you clone an existing table in the designer. Rename to rename the table. AutoSize to automatically resize the graphical objects in the designer. The designer surface is intended to permit you to manage several tables at once. It also shows the relationships between those tables. Preview Data... to open a dialog (as shown in Figure 6.18) to capture any input parameters, execute the SELECT query, and display any returned rows. This is a way to test any of the Fill or GetData queries defined for the DataSet. Note that once you press the Preview button, the dialog returns the total number of rows. Figure 6.18. Previewing the data returned by the generated SELECT query. Since you want to tune the query generated by the DSCW, click "Configure" from the right-click menu to open the TACW. This opens the wizard (as shown in Figure 6.19). Initially, the SELECT query shown is the T-SQL generated by the DSCW for your DataSet (and TableAdapter SelectCommand). Notice that it's missing the WHERE and ORDER BY clauses. I'm about to fix that (and a few other things along the way). Figure 6.19. The initial TACW dialog. Note that the TACW dialog permits you to change the query manually (just type in the changes) or via the "Query Builder...". This launches a SQL query editing tool virtually identical to the Query Designer I've discussed in earlier chapters (See Chapters 4, "Getting Started with Visual Basic," and 5, "Managing Executables with the Server Explorer") and used in SQL Server Management Studio. The Advanced Options... button opens a dialog that lets you choose how the TACW (and the DACW) generates the concurrency code for your query. The DACW assumes that you want to build an updateable DataSet, even in cases where you know the table is not updateable. In this case, I don't have update rights on the Authors table, so I'll have to disable attempts to build update, delete, or insert routines.
6.	Click on the Advanced Options... button to open the Advanced Options dialog (shown in Figure 6.20). Figure 6.20. The Advanced Options dialog. By default, the TACW generates (or attempts to generate) Insert, Update, and Delete statements based on the SELECT. I want to disable this feature, as I don't have rights to alter this table in any way. Once I uncheck the first check box, the others become disabled. Disabling the generation of the DML action commands reduces the size of the dsAuthors.Designer.vb to 725 (from 853) lines of generated code (once these changes are committed). If you are working with an updateable table, your only option when it comes to concurrency management is to either accept what the TACW generates or remove all concurrency checks from the action commands used to prevent collisions. I'll discuss the implications of each of these strategies in Chapter 12 (Updating). If you're using a database-generated primary key (like an Identity column), default values, or computed expressions, you'll need to fetch the database-generated values after a new row is added or a row is changed. To enable this feature for RW tables, be sure to check this box.
7.	Click OK when you're ready to go on to the next step. This returns you to the initial TACW dialog (see Figure 6.19). I'm now ready to tune up the SELECT query. Note that no changes are made to either the XSD file or the generated code until you finish the TACW.
8.	Click on the "Query Builder..." button to open the Query Builder. You should see a dialog as shown in Figure 6.21. I'm going to use this tool to alter the SQL used to fetch our authors. Figure 6.21. The TACW Query Builder. One of the easiest things to change is the order of columns returned. You want the query sorted by Year_Born, so you'll need to display this column first. There are a several ways to do this: Don't do anything here in the query window but edit the column layout later when you build the UI. If you take this approach, you'll have to repeat the reconfigure process each time you work with the Data Source. Drag the Year_Born column to the top of the list of columns in the second (criteria) pane. Uncheck all of the columns from the top (column selection) pane and re-check them in the order in which they are to appear in the SELECT. Feel free to choose any of these methods to reorder the columns.
9.	Next, I want to hide the Au_ID column. Again, there are a several approaches to take: Again, you can choose to not do anything here in the query window and edit the column layout later when you build the UI. If you take this approach, you'll have to repeat the reconfigure process each time you work with the Data Source. Uncheck the column in the top (column selection) pane. This removes the column from the SELECT, which is fine unless you're building an updateable query. In this case, leaving out the PK will hopelessly confuse the wizards, and it won't be able to generate the DML queries. Uncheck the column's "Output" check box. This also hides the column and removes it from the SELECTwith the same caveats. Go all the way back to the DACW column selection dialog and uncheck any column you don't want included in the SELECT. Again, leaving out the PK cripples the ability to generate an updateable DataSet/TableAdapter. Use one of these techniques to hide the TimeStamp column. You don't need it.
10.	Next, you want to order the SELECT by Year_Born. Yep, there are a couple of options here as well: As with some of the other changes I'm making, you can choose to defer the sort choice to the user at runtime or programmatically reorder the data using the Sort property of the DataView class. In addition, the DataGridView permits users to sort (ascending or descending) at runtime. You might find this approach yields better speed than having the rowset presorted on the server. Add an ORDER BY clause to the Query pane. In this case, you simply add "ORDER BY Year_Born" or "ORDER BY Year_Born DESC" to the T-SQL in the query pane, depending on how you want the data sorted. Click in the Sort Order column of the criteria pane and set Year_Born to "1". The Sort Type is set to "Ascending" automatically. Change this to "Descending", if desired.
11.	Next, I want to include a computed column "Age" in the query. This column returns a value generated by a T-SQL expression. Yes, this expression can call a SQL Server Function to perform the calculation if your login has rights to the function. However, in this case, the math is very simple, so I'll just hard-code it here (and I don't have rights to any functions). Again, you have a couple of options to set up this new column: Add the "Age" expression to the SELECT. In this case, simply add "DATEPART(YYYY, GETDATE()) - Year_Born AS Age," to the SELECT. Add the "Age" expression in a new row in the criteria pane. In this case, the DATEPART expression is entered in the Column column and "Age" is entered in the Alias column. The result of the changes I've made so far look like Figure 6.22yes, I tested the query, so the "Results" pane shows the rows returned from the query. Figure 6.22. The Query Builder is used to tune the SELECT query.

Adding Parameters to a Query

You still have a problemas you can see, the query is still returning 6263 rows. You really need to focus this query on a selected set of authors to get the number of rows returned to 50 or so. There are several approaches to this problem that can be solved right here in the Query Builder:

• Add a TOP clause to the SELECT. This would limit the number of rows by number (TOP N) or by a percent (TOP PERCENT) of the rows that result from the other criteria. This approach might (would, in this case) hide important data, so its use should be governed by a thorough understanding of how TOP expressions work.
  
  
• Add a WHERE clause that focuses the query on a name match (using an equality (=<parameter>) or a SOUNDEX or LIKE (%<parameter>) expression. You might also use an IN clause here, but note that you can't pass a parameter to an IN clause^[5].
  
  

  ^[5] There are ways to pass a delimited sting via parameter to a SQL Function that generates a Table-type variable that can be few into an IN clauseI discuss this in Chapter 13.

• Add a WHERE clause that focuses the query on a range of acceptable ages or Year_Born value(s) passed in as parameters.
  
  
• Add a SET ROWCOUNT N clause to limit the number of rows returned. This works like the TOP clause, but even more arbitrarily. Unfortunately, this approach won't work with the Query Builderit works just fine if you're executing a stored procedure.
  
  

Whatever option you choose, you'll want to consider using an approach that permits you to bypass or combine the criteria tests so you can query for multiple matches. In other words, you might want to find authors who are 25 years of age whose name starts with "William" in one query and only 60 year-old authors in another querywithout changing the T-SQL. Implementing this option is simply a matter of "OR"ing and "AND"ing the logic together and including tests for NULL (or special value) parameters.

Tip

As you use criteria to limit the scope of your queries, be sure to deal with columns with NULL values.

Should all of this be done in a stored procedure? Sure, that makes a lot of sense. But when I'm prototyping, I often start from simple SELECT statements and work up from there to more complex ad hoc queries and from there to stored procedures.

I'm ready to add a few parameters to the queryand, yes, this can be done with the Query Builder, so let's step through the process of adding parameters. In case you didn't notice, as you make changes to the Query pane, the changes are reflected in the Criteria and Column Selection panes as welljust as changes to one pane affect all the others. You might find it (far) easier to make a minor change in one panebe assured that these changes are applied universally (assuming the syntax is correct and not too complex).

1.	I'll start with a set of query criteria that focuses the rowset returned from the query on a range of Year_Born values. Add the following WHERE clause to the SQL pane: WHERE (Year_Born BETWEEN @YearLow AND @YearHigh). The two parameters are recognized by the Query Builder, as they begin with the provider's (SQL Server's) parameter lead-in character ("@"). Note that this change is reflected in the other panes.
2.	Next, I want to (alternatively) be able to limit the rowset based on the author's name. To implement this criteria check, add the following LIKE expression to the Or... column in the Criteria pane in the Author row, as shown in Figure 6.23. Again, this change in the Criteria pane is reflected in the SQL in the Query pane and in the column selector pane. Figure 6.23. Setting a parameter check in the Criteria pane. At this point, the Query Builder (as configured) should look something like Figure 6.24. There's only one last change I want to make before I start testing the query. Since our parameters force SQL Server to return a reasonable set of rows, perhaps it now makes sense to set an upper limit on the number of rows returned. To implement this criteria, I added a TOP(100) clause to the beginning of the SELECT statement. Figure 6.24. The tuned SELECT query. I'm ready to test the new query (if you haven't done so already).
3.	To test the altered query (a really good idea), click the "Execute Query" button. This exposes a Query Builder dialog (shown in Figure 6.25) that's designed to collect the query parameters. Figure 6.25. Capturing query parameters. Notice that the parameters all default to NULL. Once you set a value, subsequent executions of the query using the Execute Query button default to the settings you supply on the previous run. Try various values in your querybe sure to enter values that don't make sense and see what happens. Make sure the rows returned make sense. Your UI code that captures parameters from the user (I'll get to that step later) must ensure that any parameters' values fall within acceptable range and conform to the correct typethe strongly typed code can go only so far. When using a LIKE expression, be sure to put the parameter on the left of the "%" wildcard character. This permits the SQL Server query optimizer from using the column's index (if present) to perform the query. You're finally done with the Query Builder, so click "OK" to commit the changes to the TACW. This should return you to the dialog shown in Figure 6.19. Your altered query should appear in the dialog. You're ready to click "Next".

Choosing Query Methods to Generate

When you create a new TableAdapter or when working with a pre-configured TableAdapter, you often need to revisit the methods the TableAdapter exposes to open the connection, run the query, and return the rowset. By default, the TACW generates two methods to perform (all of) these operations:

• The Fill method, which executes the SQL you defined or a specified stored procedure and populates a DataTable or DataSet passed to the method. Sure, you'll get the chance to name this anything you like. Please don't. Give everyone a break and don't change the name of this method.

• The GetData method, which executes the SQL you defined or a specified stored procedure and returns a new (system) DataTable. This method saves you the trouble of creating a new instance of the strongly typed DataTable and passing it to the GetData method.

The TACW also permits you to specify whether or not to generate the action commands (DML) to add, change, and delete rows. When you choose a stored procedure to execute, the option is grayed out, but you're still given an opportunity to specify stored procedures to perform these operations on the rowset returned by the "select" command (the Fill or GetTable methods) used to populate the DataTable. Let's walk through the query method configuration.

1.

Right-click on the TableAdapter in the Data Source designer (.XSD for the Authors table), as shown in Figure 6.20. You should now see the "Choose Methods to Generate" dialog, shown in Figure 6.26. Figure 6.26. Choosing the rowset population and whether DML commands are generated. As promised, this dialog lets you enable the (renameable) Fill and GetData methods, as well as decide whether or not to get the TACW to generate DML commands. Behind the scenes in the generated code, the GetData method simply calls the Fill method, as shown in Figure 6.27, so it does not make much difference which method is calledGetData returns the strongly typed DataTable, and Fill accepts the strongly typed DataTable as a parameter. While the dialog says that you can pass a DataTable or DataSet to the Fill method, the generated code accepts only a strongly typed DataTable as a parameter. Figure 6.27. The generated Fill and GetData methods.

Leveraging Stored Procedures

If you're working with a stored procedure instead of an ad hoc query, you'll first see the following dialog (see Figure 6.28) which lets you specify the stored procedures used to perform the action commands. If you want the TACW to generate ADO.NET Command objects to call these stored procedures when it comes time to update your data, then go ahead and fill in the "Bind Commands" dialog.

Figure 6.28. Binding Commands to existing stored procedures.

Note that I've built a TableAdapter that uses four different stored procedures to "select" (fetch) the initial rowset, and three others specifically designed to add new rows, change existing rows, and delete existing rows.

It's bad if you mix up these stored procedure assignments.

As you select each stored procedure, you'll be given an opportunity to choose how the stored procedure action command parameters are to be populated (from the initial "Select" query. As shown in Figure 6.29, the TACW exposes a drop-down list to permit you to select any one of the input source columns. It's not usually necessary to choose an alternate valueother than disable passing any value at all to permit the stored procedure to use the default value.

Figure 6.29. Selecting the parameter mapping for the action Commands.

When you click Next, (from the dialog in Figure 6.28), you'll be taken to the dialog shown in Figure 6.26, but the "Create Methods" check box is disabled.

When you click "Finish" in the dialog shown in Figure 6.28, the TACW generates (or rebuilds) the language-specific generated code that executes the selected stored procedures when the ADO.NET Update method is called on the TableAdapter. This generated code is written to a "designer" file named after the TableAdapter. In a Visual Basic .NET project, it would be called: dsAuthorsByYearBorn.Designer.vb. Figure 6.30 shows a snapshot of part of this (now 1311 line file). I'll discuss how ADO.NET manages the DataAdapter in Chapter 8.

Figure 6.30. The UpdateCommand generated by the TACW.