So far, you've seen how to make good-looking web pages with clever and useful controls. You know how to change the appearance of the page in response to user selections, and how to use AJAX to enhance the performance of your application. But the applications you've made so far have been limited in what they can actually do. In this chapter we add the most frequently sought after functionality: the ability to retrieve, change and store data.
Think about the web sites you visit most often, and you'll find that almost all of them have one thing in common—they interact with persistent data. Persistent data is data that survives a single session; data that you expect will be there the next time you visit. In fact, it may even be data that can have significant financial consequences.
Shopping sites have databases to track their inventories and customer transactions. News sites keep databases with articles and photos in them, perhaps referenced by topic and date. Search engines use unimaginably large (and wicked-fast) databases.
Nearly every real-world commercial web application must be able to perform the four essential "CRUD" interactions with a database: Create, Read, Update, and Delete.
Fortunately, ASP.NET provides controls that make it easy and fast to perform these essential activities. We will demonstrate these interactions with SQL Server Express (or its big brother, SQL Server) but they work equally well—or nearly so—with Microsoft Access and most commercial databases. In theory, you can interact with virtually any collection of data or with XML files, but that is an advanced topic we won't go into here.
Along the way we'll show you enough about database interactions that even if you've never used a relational database, such as SQL Express, you'll have little trouble working with one through your web application.
To see how to interact with a database, you'll begin by creating a web application that can be used to display information about the AdventureWorks database. You'll start out by simply retrieving and displaying a subset of data. These exercises will teach you how to connect your controls to a database to retrieve, filter, and sort the data and then use the myriad options for presenting it attractively.
As you may remember, AdventureWorks is a free database from Microsoft that represents a fictional company that sells outdoor and extreme sports gear. The database tracks products, inventory, customers, transactions, and suppliers.
See Chapter 1 for instructions on installing this sample database if you have not already done so.
ASP.NET includes a number of controls specifically designed for displaying data.
We'll focus on the
GridView control, but other
data controls include the
GridView control displays columns and rows
of data and allows sorting and paging. It is by far the most popular data display
control and is ideal for understanding how data display controls interact with
data-retrieval controls and code. The
control allows the user to click on a column header to sort the data.
GridViews also let you present just a small subset of
the data at one time, called a page, with links for easy access to other pages—this
process is called "paging" through data. You can do these, and for numerous other
data manipulations, with very little programming. A
GridView with data from the AdventureWorks database is shown in Figure 4-1.
You could write code to pick out each piece of data you want and write it into the appropriate row or column of the data control, but that's time-consuming and error-prone. It is more efficient and safer to bind the control directly to the underlying data.
In the early days of Graphical User Interface (GUI) programming, binding was a bit of a "trick"—great for simple programs, but useless for commercial applications because the minute you wanted to do anything out of the ordinary, the binding would become a strait-jacket. Microsoft has solved that with ASP.NET by exposing events on the Data Control that allow you to insert custom code at every stage of the retrieval and binding of the data to the control.
Figure 4-1. This GridView control displays data from the AdventureWorks database in a table format that makes it easier to read, and allows users to click the column headings to sort the data.
Binding is most often used with the larger data controls such as
GridView, but you can also bind many other controls, such as
RadioButtonList. All of these controls
DataSource property that identifies
the source to which the control is bound. For example, you might keep a list of
all your customers' names in a database. Binding that data to a
ListBox can be a convenient way to allow a
customer service representative to quickly pick a customer rather than typing in
a name that might otherwise be difficult to spell.
To see how all this works, you'll build the
GridView from Figure 4-1. Once you have it up
and running, you'll add some features to it, including the ability to use the
grid to update the database with new data!
The IDE automatically places the all-important
ScriptManager control onto your page. Open your toolbox and click
the Data tab. You'll find two types of objects: display controls, which are designed to present data, and
DataSource controls, which are designed to help
you manage interacting with data sources, as shown in Figure 4-2.
By default, the Data controls are arranged so the display controls are on top,
and the DataSource controls are below (You can drag them into any order you
like or arrange them alphabetically by right-clicking on any control and
selecting Sort Items Alphabetically.) There is a
DataSource control for use with Microsoft SQL Server or SQL
Server Express, one for Microsoft Access, one for any type of Object, one for
use with SiteMaps (for binding to menu controls—more on this in Chapter 6), and one for XML
documents as a data source.
Since the AdventureWorks database is a SQL Server database, you'll use the
SqlDataSource control whether you are
using SQL Server or SQL Server Express. This control will allow you to access
the AdventureWorks database, but first you need to direct the control where to
Switch to Design view and drag the
SqlDataSource control from the Toolbox directly onto the design
surface. A Smart Tag will open, as seen in Figure 4-3.
Figure 4-3. A Smart Tag opens when you drag the SqlDataSource control onto your page allowing you to configure the data source.
The first step is to create (or choose) a data connection as seen in Figure 4-4.
Figure 4-4. To configure your DataSource control, you need to provide it with a data connection. You can choose a preexisting connection from the list (if you have previously created any for this web site), or create a new data connection by clicking the New Connection button.
Previous data connections in this web site will be listed in the drop-down menu. To make a new connection, click the New Connection… button to get the Add Connection dialog shown in Figure 4-5.
Figure 4-5. The Add Connection dialog is where you specify a new connection for your data source. Select the server, the logon credentials, and finally the database you want to use.
Following the steps in Figure 4-5, prepare your connection to the database:
Select your server from the Server Name drop-down menu. If it is not there, type the name of the server. Typically, if you are using SQLExpress, the name will be ".\SqlExpress" (dot-slash then SqlExpress) and if you are using SQL Server it will be the name of your computer, or it will be (local)—including the parentheses.
Leave the radio button set to "Use Windows Authentication."
Click OK to complete the string and return to the Configure Data Source Wizard. Click the plus mark next to
"Connection string" to see the connection string you've just created, as shown
in Figure 4-6. The segment
IntegratedSecurity=True was created when
you chose Windows Authentication rather than SQL Server Authentication.
In Figure 4-6, the Wizard
displays an expanded data connection in the drop-down menu, consisting of
the name of the server (in this case the local machine,
virtdell380, concatenated with
sqlexpress, followed by
the name of the database and database owner). You don't need to enter this
Figure 4-6. Click the plus sign to view the connection string you just created. This is what gives your control access to the database.
When you click Next, the Wizard will ask if you'd like to save this Connection string in the "application configuration file." In an ASP.NET program, the application configuration file is web.config, and saving the connection string there is an excellent idea, so be sure to check the checkbox and give the string a name you can easily remember. The Wizard will make a suggestion for the name of the connection string, as shown in Figure 4-7.
Figure 4-7. It's a good idea to save the connection string in the application's web.config file, so you can use it again with other controls.
<connectionStrings> <add name="AdventureWorksConnectionString" connectionString="Data Source=.\SqlExpress; Initial Catalog=AdventureWorks;Integrated Security=True" providerName="System.Data.SqlClient"/> </connectionStrings>
The Wizard next prompts you to configure the
SELECT statement. The
statement is the SQL code the control uses to retrieve the exact subset of data
you are looking for from the database. Fortunately, if you are not fluent in SQL
(most often pronounced "see-quill"), the Wizard will help you build the
Starting with the radio buttons at the top of the dialog box, select "Specify columns from a table or view." (You would select the other button if you had a custom SQL statement prepared, as you'll see shortly.)
Selecting the button, displays the table drop-down menu. Here, you are
presented with the various table options that represent the different sets of
data in the database. For this exercise, choose the Product table. The various
columns from the Product table will be displayed, as shown in Figure 4-8. Simply check the
columns you want retrieved, and they'll be added to the
SELECT statement. The choices you make will be displayed in the
text box at the bottom of the dialog. For this exercise, select the ProductID,
Name, ProductNumber, MakeFlag, SafetyStockLevel, and ReorderPoint columns. You
could narrow the set of data with the WHERE button, or specify the order in
which to retrieve the data with the ORDER BY button. For the moment, you can
ignore them both.
When you've completed the table setup, click Next, to move to the last page of the Wizard, and then click the Test Query button. The test fails, as shown in Figure 4-9.
Figure 4-9. The Wizard let you down; the Query Test failed and you're looking at this error message because this database requires a schema name in front of the table names.
In this instance, the Wizard falls on its face. It turns out that the
AdventureWorks database prefixes a schema name in front of
each table name and the Wizard is unprepared for that. It generates a
SELECT statement without schema names, as you saw back in Figure 4-8.
Schema in this context refers to an optional name used
for organizing the tables in a large database. For example, in the
AdventureWorks database, all the tables relating to the HR department have the
schema name HumanResources prefixed to every table name, separated by a period,
schemas in the AdventureWorks database include
As mentioned, a schema name is optional in SQL. In fact, in our experience, they are rarely used, and the Wizard is unaware of them. However, since the Adventure-Works database (which ships as part of Microsoft SQL Server) does use them, the Wizard becomes confused and flies off to Kansas leaving you on your own.
The square brackets surrounding each field and table name in the generated
SELECT statement are not required,
but are used to guarantee that there will be no problems if the name
includes any space characters (usually a very bad idea in any case). We
often remove them from the finished statement to enhance readability.
Think of this as proof that people are not yet entirely replaceable by
automation. Hit the Previous button to go back one step and fix the
SELECT statement manually. Click the radio button
captioned "Specify a custom SQL statement or stored procedure," and then click
Next. In the SQL Statement box, shown in Figure 4-10, type in:
SELECT ProductID, Name, ProductNumber, MakeFlag, SafetyStockLevel, ReorderPoint FROM Production.Product
Figure 4-10. The SQL statement editing dialog, after adding the schema name to the table name, and removing all the extraneous square brackets.
As you can see, this is nearly the same
SELECT statement that you built with the Wizard in Figure 4-8, except the Product
table now has the required schema (Production) in front of it. We've also left out the square
brackets on the columns, as mentioned in the note above.
Click Next to proceed to the next page of the Wizard, and then click Test Query. This time, you should get the results shown in Figure 4-11.
Figure 4-11. When you test the SELECT statement this time, you'll see the results you were looking for.
Click Finish to save your work. It may not look like much, but you've just enabled your application to access the AdventureWorks database, meaning all that data is now at your control.
Now that the DataSource control is providing the data you want, you need a way
to display it. From the Data section of the Toolbox, drag a
GridView control onto the page. The
GridView control recognizes that a
SqlDataSource is on the page and does not create
If you had dragged the
the page first, it would have given you the opportunity to create a
SqlDataSource rather than assuming you'd like
to use one already in existence. It pretty much amounts to the same
Click on the
GridView's Smart Tag (if it is
not already open). Click the drop-down menu next to "Choose Data Source" and select the
DataSource control you just created, as shown in Figure 4-12.
Once the data source is set, the data grid is redrawn, with a column for each field returned by the data source. The column headers are filled in for you based on the column names in the table that the data source represents.
Open the Smart Tag again and check "Enable Paging," which allows the grid to show a limited number of entries on each page and provide links to the other pages providing access to all the data. Also check "Enable Sorting," which allows the user to sort the grid by clicking on a column header.
Set the page to be the start page for the application (right-click the page in the Solution Explorer and select "Set As Start Page") and then run the application. Figure 4-13 demonstrates how the screen should appear.
Notice that the MakeFlag column (which is a Boolean value of some obscure use to the AdventureWorks business model) is shown as a checkbox. Also note that each of the column headers are shown as links. Click on one of them now—you see that the grid is sorted by that column. Also notice that at the bottom of the grid are links to page through more data, 10 rows at a time. Click on some of those too, to see the various pages.
Figure 4-13. With the GridView in place and connected to the data source, you can see the data you asked for. Notice the clickable headings and the paging links.
Close the browser and return to Design view. Drag an
UpdatePanel control onto the page from the AJAX Extensions
section of the Toolbox. Drag both the
controls already on the page into the
Run the application again. Notice there are no visible postbacks when you page or sort, and consequently, no flicker.
Switch to Source view and look at the markup code that was generated for the
GridView. It should appear as highlighted
in Example 4-1.
Example 4-1. GridView auto-generated control source code
<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Default.aspx.vb" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <asp:ScriptManager ID="ScriptManager1" runat="server" /> <div> </div> <asp:UpdatePanel ID="UpdatePanel1" runat="server"> <ContentTemplate> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString= "<%$ ConnectionStrings:AdventureWorksConnectionString %>" SelectCommand="SELECT [ProductID], [Name], [ProductNumber], [MakeFlag], [SafetyStockLevel], [ReorderPoint] FROM [Production].[Product]" > </asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server"
<asp:BoundField DataField="Name" HeaderText="Name"
</asp:GridView></ContentTemplate> </asp:UpdatePanel> </form> </body> </html>
The IDE has done a lot of work for you. It has examined the data source and
BoundField for each column in the
data. Further, it has set the
the name of the column in the database, represented by the
DataField attribute. It has set the
AllowSorting properties to
true. In addition, it has also set the
SortExpression to the name of the field. Finally, you'll notice
on the declaration of the
GridView that it
If you were creating the
GridView by hand,
and if you want to let the grid create all the columns directly from the
retrieved data, you could simplify the code by setting
is set to True, and you also include explicitly bound columns, then you will
display duplicate data.) To see this at work, create a second
GridView by dragging another
control from the Toolbox inside the
UpdatePanel, below the first.
In the Smart Tag, set the Data Source to the same source as that of the first,
SqlDataSource1. Click on the "Enable
Paging" and "Enable Sorting" checkboxes.
Now go to Source view. If necessary, delete the
<columns> collection from the new grid,
AutoGenerateColumns to the default value:
True. The declaration for this second
GridView should look something like the
<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True" DataSourceID="SqlDataSource1" > </asp:GridView>
Run the page. Both grids behave identically and are visually
indistinguishable. So why does the IDE create the more complex version? By
AutoGenerateColumns, the IDE
gives you much greater control over the presentation of your data. For example,
you can set the headings on the columns (such as changing
No.). You can change the order of the columns or remove columns
you don't need, and you can add new columns with controls for manipulating the rows.
You can make these changes by manually coding the HTML in the Source view, or
by switching to Design View and clicking the Smart Tag for the
GridView and choosing Edit Columns. Do that now
for GridView1 and you'll see the Fields dialog box, as shown in Figure 4-14.
This dialog box is divided into three main areas: the list of available
fields, the list of selected fields (with buttons to remove fields
or reorder the list), and the BoundField properties window on the right. When
you click on a selected field (such as
ProductID), you can set the way that field will be displayed in
the data grid (such as changing the header to
While you're examining what you can do with the
GridView, let's make it look a little nicer. First, delete or
comment out the second (simpler) grid (
GridView2) you just created a few moments ago. Second, open the
Smart Tag on the original grid. Click AutoFormat and choose one of the
formatting options. Of course, you can format it by hand, but why work so hard
for a simple example? We'll choose "Brown Sugar" because it shows up well in the
printed book. Run the application. The output should appear as in Figure 4-15.
SelectCommand="SELECT ProductID, Name, ProductNumber, MakeFlag, SafetyStockLevel, ReorderPoint FROM Production.Product" >
That's fine, if all you want to do is display the data in the database. For a
functional site, though, you probably want to be able to add new data, edit
existing data, and even delete data. You can do all that just as easily as you did the
SELECT statement, by asking your data
source control to generate the remaining Create, Retrieve, Update, and Delete statements (fondly known as CRUD statements), using a wizard to make your work easier. To see
this in action, switch to Design view, click on the
SqlDataSource's Smart Tag, and choose Configure Data Source. The
Configure Data Source Wizard opens, displaying your current connection string.
Click Next and the Configure Select Statement dialog box is displayed, as shown
earlier in Figure 4-8.
Figure 4-15. The AutoFormat option in the GridView's Smart Tag lets you choose the formatting option that best fits your site, and applies it automatically.
Recall the previous the Configure Data Source Wizard—it did not correctly identify the table in the autogenerated SELECT statement, omitting the schema name. You worked around that by specifying your own SQL statement. Since the SELECT statement you needed was relatively simple to type in, that was not a problem.
However, there is a lot of typing involved for all the CRUD statements. So for the rest of these statements, you will use the Wizard to generate the SQL code, and then just fix the table names.
Make sure the "Specify columns from a table or view" radio button is selected,
and the Product table is selected. Check the columns you want returned by the
SELECT statement (
ReorderPoint). This will create a new
Click the Advanced button to open the Advanced SQL Generation Options dialog box. Select the "Generate INSERT, UPDATE, and DELETE statements" checkbox, as shown in Figure 4-16.
Figure 4-16. You'll use the Advanced SQL Options dialog box to automatically create the SQL statements to add, edit, and delete data from your data source.
Clicking this checkbox instructs the Wizard to create the remaining CRUD statements, and also enables the second checkbox, Use optimistic concurrency. This is a feature that safeguards your data in case another user makes a change to the database at the same time you do. Select this option as well, and Click OK. When you return to the Wizard, click Next then Finish. You may be asked to update your grid, which unfortunately will wipe out all your customization, but the good news is that you are now bound to a data source control that provides all four CRUD methods.
This Wizard breaks down if any of the fields in the grid can have null
values. When a database table is created, you must specify if a column must
have data or if null values (no data) are allowed. If you include fields in
GridView which are allowed to be
null, then you must handcode the
SqlDataSource declaration in Source view.
Open the Smart Tag on the
again, and reapply the look and feel you want. Also—and this is important—select
the checkboxes "Enable Editing" and "Enable Deleting."
Switch to Source view. The
markup will appear similar to Example 4-2, except the new SQL commands have been added. You still need to modify the
table names, or else you'll get the error you saw earlier (see Figure 4-9). Add the schema name
Production] to each of the four
statements highlighted in Example 4-2.
Example 4-2. SqlDataSource with CRUD statements
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>" SelectCommand=" SELECT [ProductID], [Name], [ProductNumber], [MakeFlag], [SafetyStockLevel], [ReorderPoint] FROM
[Production].[Product]" DeleteCommand="DELETE FROM
[Production].[Product] WHERE [ProductID] = @ProductID" InsertCommand="INSERT INTO
[Production].[Product] ([Name], [ProductNumber], [MakeFlag], [SafetyStockLevel], [ReorderPoint]) VALUES (@Name, @ProductNumber, @MakeFlag, @SafetyStockLevel, @ReorderPoint)" UpdateCommand="UPDATE
[Production].[Product] SET [Name] = @Name, [ProductNumber] = @ProductNumber, [MakeFlag] = @MakeFlag, [SafetyStockLevel] = @SafetyStockLevel, [ReorderPoint] = @ReorderPoint WHERE [ProductID] = @ProductID" > <DeleteParameters> <asp:Parameter Name="ProductID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="ProductNumber" Type="String" /> <asp:Parameter Name="MakeFlag" Type="Boolean" /> <asp:Parameter Name="SafetyStockLevel" Type="Int16" /> <asp:Parameter Name="ReorderPoint" Type="Int16" /> <asp:Parameter Name="ProductID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="ProductNumber" Type="String" /> <asp:Parameter Name="MakeFlag" Type="Boolean" /> <asp:Parameter Name="SafetyStockLevel" Type="Int16" /> <asp:Parameter Name="ReorderPoint" Type="Int16" /> </InsertParameters> </asp:SqlDataSource>
Taking apart the code in Example 4-2,
on the first line is the declaration for the
SqlDataSource (and its corresponding closing tag at the bottom).
ID, the obligatory
runat="server", and the
ConnectionString attribute, you see four attributes: the
SelectCommand (which was there previously) and the
<DeleteParameters> <asp:Parameter Name="ProductID" Type="Int32" /> </DeleteParameters>
UpdateCommand control requires more
parameters, one for each column you'll be updating, as well as a parameter for
ProductID (to make sure the correct record is updated). Similarly, the
InsertCommand takes parameters for each column for the new
record. All of these parameters are within the definition of the