Microsoft Visual C# 2005 Express Edition: Build a Program Now! (37 page)

When you look in the Properties window, you’ll see that four types of queries were automatically generated

Figure 8-23

by Visual Studio: SELECT, INSERT, DELETE, and UPDATE. They are the queries that helped you have a fully
Adding new queries to a table adapter

workable application without writing a single line of code. When you read about table adapters earlier, you learned that you can have multiple queries with a table adapter because it is the central point of data access. You will thus add search capabilities to your application by adding queries to the table adapters and by using parameters from the UI. First, you will add the ability to search for listings that have a certain color.

12 Right-click the
ListingTableAdapter
section and select
Add Query. . .
as shown in Figure 8-23. This will bring you to the TableAdapter Query Configuration Wizard. This wizard will help you add another SELECT query that will use parameters to refine your search. You can also create a SELECT query and turn it into a stored procedure or use an existing stored procedure. As its name implies, a stored procedure is one that is stored in SQL Server and contains SQL statements, along with other programming constructs, that use a language called T-SQL or Transact-SQL. A new feature to SQL Server 2005 Express Edition is that stored procedures can also be coded in managed languages, such as C# and Visual Basic. Stored procedures are executed on the server. Since you’re using SQL Server 2005 Express Edition, this will be of no concern because the SQL Server and the application are executed on the same machine. 13 Select
Use SQL statements
and click
Next
. When asked which type of SQL query you want to use, choose
SELECT which

return rows
and then click
Next
. Note that you could have added any SQL query type you wanted.

14 You are now presented with an edit window in which to add

your SQL statement that will perform a search for all of the listings that contain a particular color. Refer to Figure 8-24 to see the SQL command edit window. Click on the
Query Builder. . .
button to get a visual view of the query.

15 You will now add the Color table to the diagram so that you’ll

be able to base your search on a particular color. To add the

Color table, simply right-click in the diagram area and select
Add

Table. . .
The Add Table dialog box appears as shown in Figure 8-25. Select the
Color
table and click the
Add
button. When the Color table has been added to the diagram, click the
Close
button.

Figure 8-24

SQL command edit window ready to

customize the user’s search

Chapter 8: Managing the Data

159

C08622299.indd 159

C08622299.indd 159

10/24/05 4:02:50 PM

10/24/05 4:02:50 PM

Figure 8-25

The Add Table dialog box

The ‘%’ symbol is the wildcard in

16 In the SQL code pane of Query Builder, append the following SQL code that will help in the filtering process:

SQL and it can mean anything. For

example, in the previous WHERE

clause, it means return something

WHERE (Color.ColorName LIKE ‘%’ + @colorname + ‘%’)

that has a color similar to the col-

orname parameter.

17 Before you proceed with your new query, make sure it will

give you the results you’re expecting. Click the
Execute

Query
button to display the Query Parameters dialog box, as

shown in Figure 8-26.

18 Try replacing the word
NULL
with
blue
and then click
OK
. The Results pane of Query Builder should display only one

row. Using the word
black
should return the black car row.

Simply enter the letter
b
, and you should get both the blue and the black rows. Once you’re satisfied with your query, click
OK
in Query Builder.

Figure 8-26

Query Parameters dialog box with

prompt to enter a color name value

160

Microsoft Visual C# 2005 Express Edition: Build a Program Now!

C08622299.indd 160

C08622299.indd 160

10/24/05 4:02:51 PM

10/24/05 4:02:51 PM

19 On the Specify a SQL SELECT Statement screen of the wizard,

click
Next
. It’s time to add your query to the application.

20 A screen appears that will prompt you to name the methods

that your query will generate. Those methods will be available

after this creation from the Listing table adapter. Refer to Figure 8-27

to view this screen containing the two new method names. For both

names you basically need to add what your filter is. In your case, you can add ColorName since you filtered by that in your WHERE clause.

When done, click
Next
.

21 After processing for a few seconds, the computer should come

back with a results screen informing you that your Select statement and your new Fill and Get methods are ready to use. Click the
Finish
button.

Look at the table adapter section of the Listing data table. Your new methods will be added there.

22 Repeat steps starting at step 12 and add another query to the

Figure 8-27

ListingTableAdapter for the CarType table. Use the following WHERE clause:
Dialog box with prompt to rename

the methods used to increase

WHERE (CarType.CarTypeName LIKE ‘%’ + @cartypename + ‘%’)

search capabilities

Name the fill and get data methods
FillByCarTypeName
and
GetDataByCarTypeName
. 23 Repeat steps starting at step 12 and add another query to the ListingTableAdapter for the Make table. Use the following WHERE clause:

WHERE (Make.MakeName LIKE ‘%’ + @makename + ‘%’)

Name the fill and get data methods
FillByMakeName
and
GetDataByMakeName
. You’re almost done now, but you still have to bind those new queries to controls on your form. On any data-bound control on your form, you can select
Add Query…
from the smart tag menu. In your case, you want to add search capabilities to the whole form and not simply to a particular control. 24 Go to the component tray, click the
ListingTableAdapter
smart tag, and select
Add Query. . .
You’ll see a Search Criteria Builder dialog box that will prompt you to create a new query or pick an existing one. Since you just built three new sets of methods, you merely need to select one. Select the
Existing
Query Name
option and then select
FillByColorName
as shown in Figure 8-28.
Chapter 8: Managing the Data

161

C08622299.indd 161

C08622299.indd 161

10/24/05 4:02:52 PM

10/24/05 4:02:52 PM

25 Click the
OK
button. You’ll see that a tool strip has been placed at the top of the form with a search button that will call your method when you click it, thereby giving you a way of searching by certain criteria. This was accomplished by only typing in the three WHERE clauses for your specific queries.

26 Repeat step 24 and add the
FillByCarTypeName
and
FillByMakeName
queries, which will add two more tool strips.

27 Now add a tool strip container to the form like you did in Chapter 6. Set the Dock property to fill the form. In the smart tag menu, select
Re-Parent
Controls
to place all of your tool strips on the top panel and all of your other controls in your content panel. If necessary, use the Document Outline window to view and adjust the hierarchy of objects on the form.

28 When you’re through moving controls, extend the top panel by clicking the grip and pulling it down so that it becomes two tool strips wide.

29 Make sure your application looks like the one shown in Figure 8-29. Press
F5

to see the results of your work. Type “blue” in the colorname tool strip and

Figure 8-28

click
FillByColorName
to see if it returns blue color car listings. Experiment with the
Search Criteria Builder with the

other features of the application.

FillByColorName method selected

Figure 8-29

Final Car Tracker application screen

162

Microsoft Visual C# 2005 Express Edition: Build a Program Now!

C08622299.indd 162

C08622299.indd 162

10/24/05 4:02:53 PM

10/24/05 4:02:53 PM

This was a simple application that you can probably modify to handle more information, such as car pictures. But there is nothing that you can’t add by yourself now! Here’s a list of other things you can do if you want to continue to work on this application: