To practice using the SqlDataSource control, we’ll need an ASP.NET website with a database. To save time in creating a new website and database, let’s use the website and database from Hour 13.

Start by creating a new ASP.NET page named AccessingData.aspx. Next, drag a SqlDataSource control from the Toolbox onto the page. Each data source control is rendered in the Design view as a gray box with the data source control type followed by its ID value. As Figure 14.2 shows, after we add a SqlDataSource control to the page, the gray box reads SqlDataSource – SqlDataSource1. Here, SqlDataSource1 is the ID of the control.

Figure 14.2. A SqlDataSource control has been added to the ASP.NET page.

In addition to this gray box, there’s also a smart tag. A smart tag is a list of common tasks that can be performed from the Web control. For the data source controls, the smart tag contains a single option, at first: Configure Data Source. Clicking this link starts the Configure Data Source Wizard, from which we’ll specify what data we want to retrieve from the database.

To be able to work with a database’s data, the SqlDataSource control needs to know two bits of information:

The SqlDataSource control’s wizard prompts you to provide these two vital pieces of information and does so in a very intuitive and developer-friendly manner.

Let’s start examining the SqlDataSource control’s wizard. Go ahead and click the Configure Data Source link.

Step 1: Choose Your Data Connection

The first step of the Configure Data Source Wizard prompts you to select the database to work with. A drop-down list contains those databases listed in your Database Explorer and should include the MyFirstDatabase.mdf database we created in the preceding hour. Go ahead and select this database from the drop-down list (see Figure 14.3).

Figure 14.3. Select the MyFirstDatabase.mdf database from the drop-down list.

Beneath the drop-down list of available databases is a Connection string label with a plus next to it. If you click this plus, it displays the connection string used to access the database data. A connection string is low-level information required by ASP.NET that provides the specific details for connecting to the database.

By the Way

If you need to connect to a database that’s not located in the App_Data folder, click the New Connection button to bring up a dialog box that prompts you for the connection information.

If this is the first time you’ve used a data source control to connect to this database, you’ll be prompted to save the connection information in the web application’s configuration file (see Figure 14.4). If you choose to save the connection string information in the web application’s configuration file, which I heartily recommend, a new setting will automatically be inserted into the web.config file that associates the name provided in Figure 14.4 with the connection string.

Figure 14.4. You can save the connection string in your website’s configuration file.

The benefit of saving the connection string information in the web application’s configuration file is that it adds a level of indirection in your application, which makes responding to changes easier. For example, imagine that you are using the MyFirstDatabase.mdf file and have created a dozen ASP.NET pages that work with this database’s data. If you do not save the connection string in web.config, the database connection string will appear in each of these 12 ASP.NET pages. Now the gotcha: Imagine that your database’s connection string changes. Perhaps you’ve renamed the database file, or you have deployed your website to a web hosting company that has moved your database from the App_Data folder to their database server. With the connection string hard-coded into each of the dozen ASP.NET pages, you’ll have to go into each of those 12 pages and update the connection information. Had you stored the connection string information in web.config, however, you would have had to modify the connection string in only one place—the web.config file.

Because the data source control’s wizard will handle adding the connection string setting in web.config automatically, there’s no reason not to store the connection string there. Therefore, leave the check box in Figure 14.4 checked and click Next to proceed to the next step.

Step 2: Configure the Select Statement

After choosing the database to use, the next step is to specify what data you want to retrieve from the database. With the SqlDataSource Wizard, you can select data in one of two ways:

• By specifying a database table, along with the columns to return

• By providing a SQL SELECT query

With the first option, you can pick the table and specify the columns to retrieve through the wizard. With the second option, you’ll need to spell out the precise SQL query to use. Regardless of what approach you use, the result is the same: The SqlDataSource control concocts some SQL statement that is sent to the database to retrieve the data. We’ll examine the basics of SQL later in this hour in the “A Look at SQL, the Language of Databases” section. For now, let’s practice with using the first option.

In the Configure the Select Statement step of the wizard, you choose whether to select a database table or specify your own SQL query by selecting the appropriate radio button at the top of the dialog box. To pick a table, select the Specify Columns from a Table or View radio button (see Figure 14.5).

Figure 14.5. Pick the table whose data you want to retrieve.

The tables in the database are listed in the drop-down list, with the selected table’s columns listed underneath. Because only one database table is in our database—Books—it’s the only option in the drop-down list. The area beneath lists the columns—BookID, Title, Author, YearPublished, Price, LastReadOn, and PageCount. There’s also a * option list. This represents all columns.

We need to check those columns that we want returned from the Books table. If you want to retrieve all column values, select the * option, or check each of the individual columns. For this example, return all columns by checking the * check box. After you check this, a SELECT statement appears at the bottom of this dialog box. Specifically, you’ll see the query syntax:

SELECT * FROM [Books]

SELECT queries are used to retrieve information from a database. There are many parts to the SELECT statement, some of which we’ll examine further in this hour. For now, don’t worry about the intricacies of the SQL syntax. Instead, focus on how the table data is selected through the SqlDataSource control’s wizard.

Now that we’ve specified the data to return from the Books table, click the Next button to proceed to the final wizard step.

Step 3: Test the Query

The final step in the SqlDataSource control’s wizard is the Test Query screen. The Test Query screen allows you to run the query to see what data, exactly, is returned. As Figure 14.6 shows, the SELECT * FROM [Books] SQL query returns all records from the Books table, with each record containing all the table’s columns.

Figure 14.6. The query returns all columns and all rows in the Books table.

If you received the database results you expected, click the Finish button to complete the wizard. If something is awry, you can click the Previous button and adjust the query as needed.

By the Way

Notice that when you select a table to display, all records from the table are returned. Furthermore, the results of the Books table are ordered by the BookID values. The SQL SELECT statement makes it easy to limit the records returned—such as retrieving only those books with a price less than $20.00—and to order the results by some column. We’ll examine the SQL syntax for filtering and sorting results in the “A Look at SQL, the Language of Databases” section and see how to apply these settings through the SqlDataSource’s wizard later in this hour.

The SqlDataSource control’s Configure Data Source wizard simply sets a number of the control’s properties. Let’s take a moment to examine the markup generated in the Source view by the SqlDataSource control’s wizard. After clicking the Finish button in the wizard you should find a SqlDataSource control declaration in the Source view like so:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
   ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
   SelectCommand="SELECT * FROM [Books]">
</asp:SqlDataSource>

As you can see, the SqlDataSource control has three property values at this point:

At this point, the SqlDataSource’s declarative markup is pretty simple. As we get into more involved examples that include interactive user filtering and updating, inserting, and deleting database data, the declarative markup generated by the SqlDataSource will quickly balloon. The lesson to take away from this discussion is that the SqlDataSource control’s wizard is helpful in two ways: First, it helps us construct the appropriate SQL statements, rather than having to enter them ourselves by hand; and second, it saves a lot of tedious and cryptic typing of markup in the Source view.

When you’re learning SQL, it helps to run queries against a database so that you can see the specific results returned by the SQL query. Fortunately, Visual Web Developer makes this task quite simple.

As we saw in the preceding hour, the Database Explorer window lists the databases used in the current ASP.NET website. Go to the Database Explorer, right-click the database name (MyFirstDatabase.mdf), and choose the New Query option from the context menu. This displays the query window, which first prompts you to select what tables to query. Because our database has only one table—Books—this is the only table listed (see Figure 14.7).

Figure 14.7. Select what table(s) you want to query.

Select the Books table and click the Add button; this adds the table to the query window. After adding the table, click the Close button in the dialog box. At this point your screen should look similar to Figure 14.8.

Figure 14.8. Create and run a query from the query window.

By default, the query window contains four regions:

The first three panes work together, in a sense. For example, if you check a subset of columns in the Books table representation in the diagram pane and then execute the query, the criteria pane and SQL pane will be updated accordingly. Likewise, if you write a SQL query by hand in the SQL pane and then execute the query, the diagram pane and criteria pane will be updated. In short, the diagram, criteria, and SQL panes provide alternative ways to specify a SQL query. The results pane shows the results of the executed query, regardless of what pane was used to express the query.

Along the top of the query window are a series of icons that are especially useful (see Figure 14.9). Starting from the left, the first four icons toggle what panes are displayed. You can show or hide the panes you don’t use. (Personally, I like to enter my SQL queries by hand, so I turn off the diagram and criteria panes.) Next is the Change Type icon, from where you can specify what type of SQL query you are interested in running (SELECT, INSERT, UPDATE, DELETE, and so forth). Next to that is the red exclamation point icon. This icon, when clicked, executes the query and displays the results in the results pane. The next icon, when clicked, validates the SQL query syntax, informing you of any syntax errors in your SQL query. The remaining icons are for functionality that’s beyond the scope of this book.

Figure 14.9. The Toolbar icons can be used to customize the query window.

Let’s practice using the query window. Go ahead and type into the SQL pane the following query:

SELECT * FROM Books

Next, click the red exclamation point icon in the Toolbar to execute the query. At this point the diagram and criteria panes should update to reflect the SQL query entered in the SQL pane, and the results should be shown in the results pane. Figure 14.10 shows the query window after this SELECT statement is executed.

Figure 14.10. The results of the SQL query are displayed in the results pane.

One thing to note is that at times the query engine will rewrite your SQL queries. I typed in SELECT * FROM Books as my query, but the query was rewritten to

SELECT     BookID, Title, Author, YearPublished, Price, LastReadOn, PageCount
FROM        Books

Of course, this query and my query are identical in their results. Also, note that the results in the results pane are identical to the results we saw in the Test Query step of the SqlDataSource control’s wizard in Figure 14.6.

Let’s try executing another SELECT statement. Change the SQL query in the SQL pane from SELECT * FROM Books to

SELECT Title, Author
FROM Books

This SQL query returns all of the rows from the Books table, displaying the values for just the Title and Author columns. After you have entered this query into the text box, execute the query; the results are shown in Figure 14.11. Notice how the diagram and criteria panes have been updated to reflect the new SQL query and how the results pane has only two columns returned instead of seven.

Figure 14.11. The SQL query returns the values for two columns.

The SELECT statement, when composed of just the SELECT and FROM clauses, returns all rows of the specified database table. For example, the SQL query results shown in Figures 14.10 and 14.11 display all the rows in the Books table; the only difference between the two results is the columns whose values are returned.

Often, when querying database data, we are not interested in all the data in a table, but only a subset. For example, when you are searching Amazon.com for books on ASP.NET, the search results page lists only those books that match your search criteria rather than all the books in Amazon.com’s database.

To limit the rows returned by the SELECT statement, we use the WHERE clause. The WHERE clause specifies conditions that a row must match to be returned by the SELECT statement. For example, the following SQL SELECT statement returns only those rows in which the Title column’s value equals The Number:

SELECT *
FROM Books
WHERE Title = 'The Number'

Go ahead and enter this query into the SQL pane and click the Execute SQL Toolbar icon. The results should show all the columns of the Books table, but only one row—the book The Number.

As you can see, the WHERE clause has a Boolean condition preceding it: Title = 'The Number'. The = operator here is synonymous with the = operator in Visual Basic, which we examined in Hour 5, “Understanding Visual Basic’s Variables and Operators.” In addition to the = operator, other comparison operators, such as <, <=, >, >=, and <>, can be used as well. Table 14.1 summarizes these other comparison operators.

In addition to the comparison operators, the logical operators AND and OR may be used to string together multiple Boolean expressions. The AND and OR keywords in a WHERE clause and are synonymous with the Visual Basic And and Or keywords. For example, the following SQL SELECT statement returns the Title, Author, and Price columns of books whose YearPublished equals 2006 or whose Price is less than or equal to 18.00:

SELECT Title, Author, Price
FROM Books
WHERE YearPublished = 2006 OR Price <= 18.00

This query returns the Title, Author, and Price columns for three books: Create Your Own Website, The Catcher in the Rye, and Fight Club.

SELECT Title, Author
FROM Books
WHERE Title <> 'The Number' AND BookID <= 3

You may have noticed that the results returned by the SQL queries we have examined so far have all been ordered by the BookID value. To see this point illustrated, refer to Figure 14.10, which shows the results of the query SELECT * FROM Books. What if we want the results ordered by some other column value, though?

The SELECT statement can include an optional ORDER BY clause, which specifies the column to sort the results by. For example, to retrieve books sorted alphabetically by title, use the following SELECT query:

SELECT *
FROM Books
ORDER BY Title

Figure 14.12 shows the query window when this SQL query is used. Note that the books are ordered by the values in the Title column, instead of by the values in the BookID column.

Figure 14.12. The books are ordered alphabetically by their Title column value.

SELECT *
FROM Books
WHERE Title <> 'Fight Club'
ORDER BY Author

SELECT *
FROM Books
ORDER BY Author
WHERE Name <> 'Fight Club'

ORDER BY ColumnName DESC

As things stand now, all records from the Books table will be returned. Imagine, however, that we want to return only those records that have a BookID value of 3 or less and cost more than $20.00. We can add these WHERE clause filters via the SqlDataSource’s wizard by clicking the WHERE button. This will display the Add WHERE Clause dialog box, shown in Figure 14.13.

Figure 14.13. Filter the results using the Add WHERE Clause dialog box.

Adding a filter through the Add WHERE Clause dialog box involves a number of steps, such as choosing what column to filter on, the operator to filter with, and what value to use in filtering. To add the filter expression on the BookID column, perform the following steps:

Congratulations, you have added your first WHERE clause expression using the SqlDataSource wizard!

Because we want our query to have two WHERE clause expressions, our work is only halfway done. Repeat the preceding steps, this time adding an expression that filters on the Price column for results with values strictly greater than $20.00. After adding both filter expressions, click the OK button to return to the Configure the Select Statement screen. At the bottom of this screen, you see the SQL query the wizard has constructed thus far:

SELECT * FROM [Books] WHERE (([BookID] <= @BookID) AND ([Price] > @Price))

At this point we have added two filter expressions to the WHERE clause. However, the results returned will still be ordered by BookID. Let’s instead have the books ordered by Price in descending order (from most expensive to least). If any ties occur in Price, let’s break them by alphabetically sorting on the Title.

To accomplish this, click the ORDER BY button, which is beneath the WHERE button in the Configure Select Statement screen. This brings up the Add ORDER BY Clause dialog box, from which you can specify up to three columns to order the results.

The results are sorted by the column specified in the first drop-down list. If any ties occur in the results, the second column is consulted; finally, if any ties occur there, the third column specified is used to break those ties.

Figure 14.15 shows the Add ORDER BY Clause dialog box after it has been configured to sort first by Price in descending order, with ties being broken based on the alphabetical ordering of the Title column.

Figure 14.15. The results will be ordered by Price in descending order, with ties being broken by Title.

As Figure 14.15 shows, after we add the ORDER BY clause, the final SQL statement for our SqlDataSource control is

SELECT * FROM [Books] WHERE (([BookID] <= @BookID) AND ([Price] > @Price)) ORDER BY [Price] DESC, [Title]

After you’ve entered the ORDER BY clause, click the OK button to return to the Configure Select Statement screen. At this point, our SQL query is complete; it returns all columns of the Books table records that have a BookID less than or equal to 3 and a price exceeding $20.00. These results are sorted by Price in descending order, breaking ties by sorting on Title alphabetically. Click the Next button to advance to the Test Query screen.

When you have a query that involves a WHERE clause, clicking the Test Query button prompts you to supply values for the WHERE clause filter expressions. For this example, we are prompted to enter values for BookID and Price (see Figure 14.16). You can either leave in the default values—3 and 20.00, respectively—or enter different numbers.

Figure 14.16. Specify the values for the WHERE clause filter expressions.

If you leave in the values of 3 and 20.00 for the BookID and Price filter expressions, you should see two books in the results—The Number and Visual Studio Hacks (see Figure 14.17). These are the only books that have BookID values less than or equal to 3 and prices greater than $20.00. The Number and Visual Studio Hacks have the same price—$24.95. The Number is listed first because its title comes before Visual Studio Hacks when sorted alphabetically.

Figure 14.17. The query returns two records from the Books table.

To complete the SqlDataSource control’s wizard, click the Finish button. For more practice with the Add WHERE Clause and Add ORDER BY Clause dialog boxes, click the Previous button to return to the Configure Select Statement screen.

After you have configured the SqlDataSource control to include both WHERE and ORDER BY clauses, take a moment to inspect the control’s declarative markup in the Source view:

<asp:SqlDataSource ID="SqlDataSource2" runat="server"
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    SelectCommand="SELECT * FROM [Books] WHERE (([BookID] &lt;= @BookID) AND ([Price] &gt; @Price)) ORDER BY [Price] DESC, [Title]">
    <SelectParameters>
        <asp:Parameter DefaultValue="3" Name="BookID" Type="Int32" />
        <asp:Parameter DefaultValue="20.00" Name="Price" Type="Decimal" />
    </SelectParameters>
</asp:SqlDataSource>

The ID and ConnectionString properties are nothing new; we examined them back in the “Examining the Data Source Controls” section at the start of this hour. The SelectCommand is more intricate, though, now including both a WHERE clause and an ORDER BY clause. Notice that no values are supplied for the filter expression values, even though we provided hard-coded values in the Add WHERE Clause dialog box. Instead, a parameterized query is used.

A parameter is a placeholder in a SQL statement that has the form @ParameterName. It serves as a location where a value will be inserted right before the actual SQL statement is sent off to the database. The SqlDataSource control lists the parameters for the SelectCommand in the <SelectParameters> element. There are two <asp:Parameter> elements within <SelectParameters>: one for the @BookID parameter and one for the @Price parameter. Here, their hard-coded values are specified in the DefaultValue properties.

When a user visits the ASP.NET page, the SqlDataSource control takes those parameter values and injects them into the appropriate places within the SELECT query before sending the query to the database. Although parameterized queries seem like overkill when filtering on hard-coded values, their utility will become more apparent when we start filtering based on values specified by the user visiting the page or from other external sources. We’ll see how to base parameter values on user input in Hour 17, “Working with Data-Bound DropDownLists, RadioButtons, and CheckBoxes.”