Chapter 4. Saving and Retrieving Data
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.
Getting Data from a Database
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.
Tip
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 DataList,
Repeater, DetailsView, and FormView.
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 GridView
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.
Binding Data Controls
Database information is stored in memory as tables (just as it is retrieved
from a relational database). Tables consist of rows and columns that match
nicely to the GridView control.
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.
Tip
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.
Binding is most often used with the larger data controls such as GridView, but you can also bind many other controls, such as
DropDownList, ListBox, CheckBoxList, and
RadioButtonList. All of these controls
have a 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!
Create a Sample Web Page
To begin, create a new ASP.NET AJAX-enabled web site named AWProductData.
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.
Using a DataSource Control
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
find it.
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.
When you click on Configure Data Source, you invoke a wizard that will walk you through the steps of configuring your data source—hooking up the control to the underlying data table(s).
The first step is to create (or choose) a data connection as seen in Figure 4-4.
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.
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.”
Choose the AdventureWorks database in the database name drop-down.
Click the Test Connection button to verify that it all works.
This dialog box constructs a connection string, which provides the information necessary to connect to a database on a server.
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.
Tip
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
information yourself.
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.
This will cause the following lines to be written to web.config:
<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 SELECT
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
statement.
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.
“Pay No Attention to That Man Behind the Curtain”
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.
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,
such as HumanResources.EmployeeAddress. Other
schemas in the AdventureWorks database include Person, Production, Purchasing, and Sales.
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.
Tip
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
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.
Behold—the triumph of 3 billion years of random mutation and natural selection over 50 years of automation!
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.
Using the GridView 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
its own.
Tip
If you had dragged the GridView onto
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
thing.
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.
Each time you click on one of the columns or one of the page numbers the entire page is posted back to the server, and you’ll encounter a noticeable delay and flicker. You know how to fix that!
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 SqlDataSource and GridView
controls already on the page into the UpdatePanel.
Run the application again. Notice there are no visible postbacks when you page or sort, and consequently, no flicker.
Auto-Generated Code
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.
<%@ 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"
AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False"
DataKeyNames="ProductID" DataSourceID="SqlDataSource1">
<Columns>
lt;asp:BoundField DataField="ProductID"
HeaderText="ProductID" InsertVisible="False"
ReadOnly="True"
SortExpression="ProductID" />
<asp:BoundField DataField="Name" HeaderText="Name"
SortExpression="Name" />
<asp:BoundField DataField="ProductNumber"
HeaderText="ProductNumber"
SortExpression="ProductNumber" />
<asp:CheckBoxField DataField="MakeFlag"
HeaderText="MakeFlag"
SortExpression="MakeFlag" />
<asp:BoundField DataField="SafetyStockLevel"
HeaderText="SafetyStockLevel"
SortExpression="SafetyStockLevel" />
<asp:BoundField DataField="ReorderPoint"
HeaderText="ReorderPoint"
SortExpression="ReorderPoint" />
</Columns>
</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
created a BoundField for each column in the
data. Further, it has set the HeaderText to
the name of the column in the database, represented by the DataField attribute. It has set the AllowPaging and 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
has set AutoGenerateColumns to False.
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 AutoGenerateColumns to True. (If AutoGenerateColumns
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 GridView
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, GridView2. Change AutoGenerateColumns to the default value: True. The declaration for this second GridView should look something like the
following:
<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
turning off 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 ProductNumber to Product
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 ID).
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.
Adding Insert, Update, and Delete Statements
At this point, the SqlDataSource you’ve
created has only a SELECT statement to
extract data from the database:
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.
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 (ProductID, Name, ProductNumber, MakeFlag,
SafetyStockLevel, ReorderPoint). This will create a new SELECT statement.
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.
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.
Warning
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
the GridView which are allowed to be
null, then you must handcode the SqlDataSource declaration in Source view.
Open the Smart Tag on the GridView control
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 SqlDataSource
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.
<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>
Switch back to Design view and notice the Edit and Delete buttons on each row. They are the result of checking the Enable Editing and Enable Deleting checkboxes.
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).
After the ID, the obligatory runat="server", and the ConnectionString attribute, you see four attributes: the SelectCommand (which was there previously) and the
new DeleteCommand, InsertCommand, and UpdateCommand.
The DeleteCommand takes a single parameter
(@ProductID), which is specified in the
DeleteParameters element:
<DeleteParameters> <asp:Parameter Name="ProductID" Type="Int32" /> </DeleteParameters>
The 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 SqlDataSource.
Displaying and Updating the Data
Now that your SqlDataSource object is ready to
go, you only have to set up your GridView
control. In Design view, click on the GridView
Smart Tag and choose “Edit Columns.” Verify that the checkboxes to enable editing
and deleting are selected, as shown in Figure 4-17.
If you prefer to have buttons for Edit and Delete, rather than links, click on the
Smart Tag and select “Edit Columns…” When the Fields dialog box opens, click the
Command Field entry in the Selected Fields area (lower-left corner). This brings up
the Command Field Properties in the right-hand window. In the Appearance section of
the Fields editor, choose ButtonType and then
change Link to Button in the drop-down menu next to ButtonType, as shown in Figure 4-18.
The result is that the commands (Edit and Delete) are shown as buttons, as shown in Figure 4-19.
Take It for a Spin
Start the application. The product database information is loaded into your
GridView. When you click the Edit button,
the data grid automatically enters edit mode. You’ll notice that the editable
text fields change to text boxes and checkboxes, as appropriate, and the command
buttons change from Edit and Delete to Update and Cancel. Make a small change to
one field, as shown in Figure 4-20.
When you click the Update button for that row, the grid and the database are both updated, which you can confirm by opening the table in the database, as shown in Figure 4-21.
To open the database table, stop the application first. Then on the right side
of the IDE, click the Database Explorer tab (in VWD; it is called Server
Explorer in VS2005). Expand the AdventureWorks folder, and then expand the
Tables folder. Scroll down until you find the Product
(Production) table (in the IDE, the schema name is displayed in
parenthesis after the table name—go figure), then
right-click it, and select “Show Table Data.” This will show you the contents of
the table from within the IDE.
Modifying the Grid Based on Events
Suppose you would like you to modify the grid so the contents of the Name
column are red when the MakeFlag column is checked, that is, when its value is True. In
addition, you want all the ProductNumbers that begin with the letters CA to
display in green. You can do this by handling the RowDataBound event. As the GridView is populated with data, each row of data is bound to the
GridView individually, and the RowDataBound event is fired once for each
row.
To modify the GridView, switch to Design
view, click the GridView, click the lightning
bolt in the Properties window, and double-click in the method name column
(currently blank) to the right of the RowDataBound event. The IDE will create an event handler named
GridView1_RowDataBound() and then place
you in the code-behind file within the skeleton of that method, ready for you to
start typing code.
The second argument to this method is of type GridViewRowEventArgs. This object has useful information about
the row that is databound, which is accessible through the Row property of the event argument.
Enter the code shown in Example 4-3.
Protected Sub GridView1_RowDataBound(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
Dim cellProductNumber As TableCell = e.Row.Cells(3) ' ProductNumber column
If cellProductNumber.Text.Substring(0, 2) = "CA" Then
cellProductNumber.ForeColor = Drawing.Color Green
End If
Dim cellMakeFlag As TableCell = e.Row.Cells(4) ' MakeFlag column
Dim cb As CheckBox = CType(cellMakeFlag.Controls(0), CheckBox)
If cb.Checked Then
e.Row.Cells(2).ForeColor = Drawing.Color.Red
End If
End If
End SubThe first If statement (highlighted in
Example 4-3) tests if the type of
Row passed in as a parameter—in other
words, the row that was bound and triggered this event—is a DataRow (rather than a header, footer, or
something else).
Once you know you are dealing with a DataRow, you can extract the cell(s) you want to examine from
that row. Here, we will look at two cells: the ProductNumber cell is the fourth cell in the row, at offset
(index) 3, and the MakeFlag cell is the fifth
cell in, at offset 4. (Remember, all indices are zero-based.)
To access the ProductNumber cell, you
define a new variable, cellProductNumber,
defined as a TableCell with the As keyword,
and set it equal to the cell at offset 3 in the row, like this:
Dim cellProductNumber As TableCell = e.Row.Cells(3)
Once you have the cell as a variable, you want to get the text contained in
the cell to compare to your known value. You do that by accessing the Text
property of cellProductNumber, and then using
the Substring() function.
The Substring() function, as you might
guess from its name, extracts a smaller string from a larger one. This is a
pretty simple function to work with. First, you call the function on a string,
and you give it two numbers as parameters: the index of the start of the
substring, and the length of the substring. As with all other indices, the first
character in the string is position zero. You want the first two characters from
the Text string, so the starting index is 0, and the length of the substring is
2. Therefore, to get the first two characters from your string, you use the
function Substring(0,2). Once you have that
substring, you can use a simple If statement
to compare it to the string you want to match, “CA”:
If cellProductNumber.Text.Substring(0, 2) = "CA" Then
It there is a match, you want to set the ForeColor property of the cell to green, which you can do using
the Drawing.Color.Green property:
cellProductNumber.ForeColor = Drawing.Color.Green
In the case of the MakeFlag, it is somewhat more complicated. It’s easy enough
to isolate the cell that contains the checkbox—it’s at index 4—and then assign that value to a new
variable called cellMakeFlag:
Dim cellMakeFlag As TableCell = e.Row.Cells(4)
This is the same technique you used to isolate the ProductNumber cell. In this
case, though, the Text property of this cell will always be empty. However, it
does contain a CheckBox control, which is the
only control in the cell. Instead of reading the text in
the cell, you want to read the value of the Checked property of that CheckBox control. Each cell has a collection of all the controls
contained in the cell, called Controls, which
has a zero-based index. Since the checkbox you want is the only control in the
collection, you know it’s at cellMakeFlag.Controls(0). Next you define a new variable,
cb, which you define as a CheckBox. Then you use the CType function on the control you just isolated,
to convert the control to a CheckBox. This
works because we know it is a CheckBox:
Dim cb As CheckBox = CType(cellMakeFlag.Controls(0), CheckBox)
Then you test the Checked property of the
CheckBox:
If cb.Checked Then
If the box is checked, cb.Checked will
evaluate to true. If it is checked, you want
to set the ForeColor property of the third
cell in the row (offset 2), the ProductName
column:
e.Row.Cells(2).ForeColor = Drawing.Color.Red
You set the color of the cell the same way you did for ProductNumber, but notice this time you’re not
changing the color of the checkbox cell itself—you’re changing a different cell
in the table.
Run the web site. It will look identical to Figure 4-19, except the product
names for which the MakeFlag field is checked
will display in red, and some of the product numbers will display in green.
(Neither of these changes will be obvious in the printed book, so we will forego
a figure showing the color changes.)
Selecting Data from the GridView
Often you need to select a row from the grid and extract data from that row. This is easy to do using the SelectedIndexChanged event of the GridView.
To see how this works, drag a Label control
from the Standard section of the Tool-box onto the Design view, below the grid
but within the UpdatePanel control. Change
the Text property of this Label to Name. Then drag a TextBox control next to the Label. Change its ID property
to txtName and set its ReadOnly property to True. You now have a place to display the name of the selected
item from the grid.
Click on the Smart Tag of the GridView and
check the “Enable Selection” checkbox. This will cause a Select button to
display in the first column of the grid, next to the Edit and Delete buttons
already there, as shown in Figure 4-22.
Now all you need to do is set up the event handler to respond to the Select
buttons. Double-click on the Select button in the first row of the grid. This
will open up the code-behind file with the skeleton of the SelectedIndexChanged already created for you,
ready to accept your custom code. Enter the highlighted code from the following
snippet:
Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, _ ByVal e As System.EventArgs)If GridView1.SelectedRow.RowType = DataControlRowType.DataRow ThenDim cellName As TableCell = GridView1.SelectedRow.Cells(2) ' Name columntxtName.Text = cellName.TextEnd IfEnd Sub
This code first tests to determine if the selected row is a DataRow (as opposed to a HeaderRow or a FooterRow). If
it is a DataRow, it creates a variable of
type TableCell, which is assigned to the
third cell in the selected row (because of zero-based indexing, the third item
will have an index value of 2). Then the Text
property of the Text-Box is set equal to the Text property of that cell.
Run the app and click on one of the Select buttons. The name from the selected
row appears in the TextBox.
Passing Parameters to the SELECT Query
Sometimes you do not want to display all the records in a table. For example,
you might want to have users select a product from your grid and display the
order details for it in a second grid on the current page. To do this, you’ll
need a way to select a product as well as a way to pass the ID of the selected product to the second grid. The
Select buttons are already in place from the previous example, so all you need
to do now is pass the ID of the selected
product to the second grid.
To keep the downloadable source code clear, copy the previous example, AWProductData to a new web site, AWProductDataOrderDetails.
Tip
See Appendix A for details about how to copy a web site.
You need to create a second GridView, which
will be used to display the order details. From the Toolbox, drag the second
GridView onto the page below the first,
and then drag the Label and TextBox inside the UpdatePanel. Open the Smart Tag for the UpdatePanel. As you did earlier in the chapter, create a new data
source (name it AdventureWorksOrderDetails),
but use the existing connection string. Choose the SalesOrderDetail table, select the desired columns (for this
example, SalesOrderID, CarrierTrackingNumber, OrderQty, UnitPrice, UnitPriceDiscount, and LineTotal), and then click the Where button, as shown in Figure 4-23.
A WHERE clause is a SQL language keyword used to narrow the set of data returned by the SELECT statement. In other words, you’re saying, “Get me all the records from this table, where this condition is true.” The condition could be defined any number of ways—where the amount in inventory is less than 10, where the customer name is “Smith,” or where the copyright date is after 1985. It all depends on the types of information you have stored in your columns.
When you click the WHERE button, the Add WHERE Clause dialog opens, which you can see in Figure 4-24. First, you pick the
column you want to match on, in this case ProductID. Next, pick the appropriate operator for your condition
statement. Your choices include among others, equal to, less than/greater than,
like, and contains. For this exercise, use the default (=).
The third drop-down lets you pick the source for the ProductID—that is, where you will get the term you want to match
on. You can pick from any one of several objects in the menu or choose None if
you’ll be providing a source manually. In this case, you’ll obtain the source of
the ProductID from the first GridView, so choose Control.
When you choose Control, the Parameter
properties panel of the dialog wakes up. You are asked to provide the ID of the Control containing the target parameter. Select GridView1. Once you’ve made all your choices, the
screen will resemble Figure 4-24.
Click Add. When you do, the upper portion of the dialog returns to its initial
(blank) state and the WHERE clause is added
to the WHERE Clause window. You could add
additional WHERE clauses at this point, to
further restrict the data, but we won’t for this example.
Click OK to return to the Configure Select Statement dialog box. While you are at it, sort the
results by the SalesOrderID column by
clicking on the Order By button. The Add ORDER BY
Clause dialog with the SalesOrderID column
selected is shown in Figure 4-25.
The ORDER BY clause is another SQL keyword,
and this one does just what its name implies—it sorts the results using the
selected field for sort order.
Click OK until the Configure Data Source Wizard is finished.
Switch to Source view and again fix the name of the tables in the SQL
statements that were auto-generated. The markup for the second GridView and its associated SqlDataSource is shown in Example 4-4, with the corrected table
names highlighted.
<asp:GridView ID="GridView2" runat="server"
DataSourceID="AdventureWorksOrderDetails">
</asp:GridView>
<asp:SqlDataSource ID="AdventureWorksOrderDetails" runat="server"
ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>"
SelectCommand="SELECT [SalesOrderID], [CarrierTrackingNumber],
[OrderQty], [UnitPrice], [UnitPriceDiscount], [LineTotal]
FROM [Sales]. [SalesOrderDetail]
WHERE ([ProductID] = @ProductID)
ORDER BY [SalesOrderID]">
<SelectParameters>
<asp:ControlParameter
ControlID="GridView1"
Name="ProductID"
PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>Also highlighted in Example 4-4 are
the results of the WHERE and ORDER BY buttons from the Configure Select Statement Wizard.
The SELECT statement now has a WHERE clause that includes a parameterized value
(@ProductID). In addition, within the
definition of the SqlDataSource control is a
definition of the SelectParameters. This
includes one parameter of type asp:
ControlParameter, which is a parameter that knows how to get its
value from a control (in our example, GridView1). In addition, a second property, PropertyName, tells it which property in the
GridView to check. A third property,
Type, tells it that the type of the value
it is getting is of type Int32, so it can
properly pass that parameter to the SELECT
statement.
You may now reformat your grid and edit the columns as you did for the first grid, and then try out your new page, which should look something like Figure 4-26.
Warning
The AdventureWorks database has no order details for any of the entries with ProductIDs below 707. The first entry with details is on Code-Behind Files of the grid, so be sure to move to Code-Behind Files (or later) to see product details. If you select a product that does not have any order details, the second grid will not appear.
Source Code Listings
The complete markup for the Default.aspx file in the AWProductData site is shown in Example 4-5, with the code-behind shown directly after in Example 4-6.
<%@ 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" />
<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]"
DeleteCommand="DELETE FROM [Production].[Product]
WHERE [ProductID] = @original_ProductID AND [Name] = @original_Name
AND [ProductNumber] = @original_ProductNumber
AND [MakeFlag] = @original_MakeFlag
AND [SafetyStockLevel] = @original_SafetyStockLevel
AND [ReorderPoint] = @original_ReorderPoint"
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] = @original_ProductID
AND [Name] = @original_Name
AND [ProductNumber] = @original_ProductNumber
AND [MakeFlag] = @original_MakeFlag
AND [SafetyStockLevel] = @original_SafetyStockLevel
AND [ReorderPoint] = @original_ReorderPoint"
ConflictDetection="CompareAllValues"
OldValuesParameterFormatString="original_{0}" >
<DeleteParameters>
<asp:Parameter Name="original_ProductID" Type="Int32" />
<asp:Parameter Name="original_Name" Type="String" />
<asp:Parameter Name="original_ProductNumber" Type="String" />
<asp:Parameter Name="original_MakeFlag" Type="Boolean" />
<asp:Parameter Name="original_SafetyStockLevel" Type="Int16" />
<asp:Parameter Name="original_ReorderPoint" Type="Int16" />
</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="original_ProductID" Type="Int32" />
<asp:Parameter Name="original_Name" Type="String" />
<asp:Parameter Name="original_ProductNumber" Type="String" />
<asp:Parameter Name="original_MakeFlag" Type="Boolean" />
<asp:Parameter Name="original_SafetyStockLevel" Type="Int16" />
<asp:Parameter Name="original_ReorderPoint" Type="Int16" />
</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>
<asp:GridView ID="GridView1" runat="server"
AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False"
DataKeyNames="ProductID" DataSourceID="SqlDataSource1"
BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None"
BorderWidth="1px" CellPadding="3" CellSpacing="2"
OnRowDataBound="GridView1_RowDataBound"
OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
<Columns>
<asp:CommandField ButtonType="Button"
ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="ProductID" HeaderText="ID"
InsertVisible="False"
ReadOnly="True" SortExpression="ProductID" />
<asp:BoundField DataField="Name" HeaderText="Name"
SortExpression="Name" />
<asp:BoundField DataField="ProductNumber"
HeaderText="ProductNumber"
SortExpression="ProductNumber" />
<asp:CheckBoxField DataField="MakeFlag"
HeaderText="MakeFlag" SortExpression="MakeFlag" />
<asp:BoundField DataField="SafetyStockLevel"
HeaderText="SafetyStockLevel"
SortExpression="SafetyStockLevel" />
<asp:BoundField DataField="ReorderPoint"
HeaderText="ReorderPoint"
SortExpression="ReorderPoint" />
</Columns>
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True"
ForeColor="White" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<HeaderStyle BackColor="#A55129" Font-Bold="True"
ForeColor="White" />
</asp:GridView>
<asp:Label ID="Label1" runat="server" Text="Name"></asp:Label>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</ContentTemplate>
</asp:UpdatePanel>
</form>
</body>
</html>Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub GridView1_RowDataBound(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
Dim cellProductNumber As TableCell = e.Row.Cells(3) ' ProductNumber
If cellProductNumber.Text.Substring(0, 2) = "CA" Then
cellProductNumber.ForeColor = Drawing.Color.Green
End If
Dim cellMakeFlag As TableCell = e.Row.Cells(4) ' MakeFlag column
Dim cb As CheckBox = CType(cellMakeFlag.Controls(0), CheckBox)
If cb.Checked Then
e.Row.Cells(2).ForeColor = Drawing.Color.Red
End If
End If
End Sub
Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, _
ByVal e As System.EventArgs)
If GridView1.SelectedRow.RowType = DataControlRowType.DataRow Then
Dim cellName As TableCell = GridView1.SelectedRow.Cells(2) ' Name
txtName.Text = cellName.Text
End If
End Sub
End ClassThe complete markup for the Default.aspx file in the AWProductDataOrderDetails site is shown in Example 4-7, and the code-behind is shown in Example 4-8.
<%@ 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" />
<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]"
DeleteCommand="DELETE FROM [Production].[Product]
WHERE [ProductID] = @original_ProductID
AND [Name] = @original_Name
AND [ProductNumber] = @original_ProductNumber
AND [MakeFlag] = @original_MakeFlag
AND [SafetyStockLevel] = @original_SafetyStockLevel
AND [ReorderPoint] = @original_ReorderPoint"
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] = @original_ProductID
AND [Name] = @original_Name
AND [ProductNumber] = @original_ProductNumber
AND [MakeFlag] = @original_MakeFlag
AND [SafetyStockLevel] = @original_SafetyStockLevel
AND [ReorderPoint] = @original_ReorderPoint"
ConflictDetection="CompareAllValues"
OldValuesParameterFormatString="original_{0}" >
<DeleteParameters>
<asp:Parameter Name="original_ProductID" Type="Int32" />
<asp:Parameter Name="original_Name" Type="String" />
<asp:Parameter Name="original_ProductNumber" Type="String" />
<asp:Parameter Name="original_MakeFlag" Type="Boolean" />
<asp:Parameter Name="original_SafetyStockLevel" Type="Int16" />
<asp:Parameter Name="original_ReorderPoint" Type="Int16" />
</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="original_ProductID" Type="Int32" />
<asp:Parameter Name="original_Name" Type="String" />
<asp:Parameter Name="original_ProductNumber" Type="String" />
<asp:Parameter Name="original_MakeFlag" Type="Boolean" />
<asp:Parameter Name="original_SafetyStockLevel" Type="Int16" />
<asp:Parameter Name="original_ReorderPoint" Type="Int16" />
</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>
<asp:GridView ID="GridView1" runat="server"
AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False"
DataKeyNames="ProductID" DataSourceID="SqlDataSource1"
BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None"
BorderWidth="1px" CellPadding="3" CellSpacing="2"
OnRowDataBound="GridView1_RowDataBound"
OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
<Columns>
<asp:CommandField ButtonType="Button"
ShowDeleteButton="True"
ShowEditButton="True"
ShowSelectButton="True" />
<asp:BoundField DataField="ProductID" HeaderText="ID"
InsertVisible="False"
ReadOnly="True"
SortExpression="ProductID" />
<asp:BoundField DataField="Name" HeaderText="Name"
SortExpression="Name" />
<asp:BoundField DataField="ProductNumber"
HeaderText="ProductNumber"
SortExpression="ProductNumber" />
<asp:CheckBoxField DataField="MakeFlag"
HeaderText="MakeFlag" SortExpression="MakeFlag" />
<asp:BoundField DataField="SafetyStockLevel"
HeaderText="SafetyStockLevel"
SortExpression="SafetyStockLevel" />
<asp:BoundField DataField="ReorderPoint"
HeaderText="ReorderPoint"
SortExpression="ReorderPoint" />
</Columns>
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True"
ForeColor="White" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<HeaderStyle BackColor="#A55129" Font-Bold="True"
ForeColor="White" />
</asp:GridView>
<asp:Label ID="Label1" runat="server" Text="Name"></asp:Label>
<asp:TextBox ID="txtName" runat="server" ReadOnly="True">
</asp:TextBox>
<br />
<asp:GridView ID="GridView2" runat="server"
DataSourceID="AdventureWorksOrderDetails"
BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None"
BorderWidth="1px"
CellPadding="3" CellSpacing="2">
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True"
ForeColor="White" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<HeaderStyle BackColor="#A55129" Font-Bold="True"
ForeColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="AdventureWorksOrderDetails" runat="server"
ConnectionString=
"<%$ ConnectionStrings:AdventureWorksConnectionString %>"
SelectCommand="SELECT [SalesOrderID], [CarrierTrackingNumber],
[OrderQty], [UnitPrice], [UnitPriceDiscount], [LineTotal]
FROM [Sales].[SalesOrderDetail]
WHERE ([ProductID] = @ProductID)
ORDER BY [SalesOrderID]">
<SelectParameters>
<asp:ControlParameter
ControlID="GridView1"
Name="ProductID"
PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</ContentTemplate>
</asp:UpdatePanel>
</form>
</body>
</html>Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub GridView1_RowDataBound(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
Dim cellProductNumber As TableCell = e.Row.Cells(3) ' ProductNumber
If cellProductNumber.Text.Substring(0, 2) = "CA" Then
cellProductNumber.ForeColor = Drawing.Color.Green
End If
Dim cellMakeFlag As TableCell = e.Row.Cells(4) ' MakeFlag column
Dim cb As CheckBox = CType(cellMakeFlag.Controls(0), CheckBox)
If cb.Checked Then
e.Row.Cells(2).ForeColor = Drawing.Color.Red
End If
End If
End Sub
Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, _
ByVal e As System.EventArgs)
If GridView1.SelectedRow.RowType = DataControlRowType.DataRow Then
Dim cellName As TableCell = GridView1.SelectedRow.Cells(2) ' Name
txtName.Text = cellName.Text
End If
End Sub
End ClassSummary
Most useful web sites make use of a database. ASP.NET provides controls that make it easy to connect to a database, and retrieve and edit data.
The
GridViewis the most commonly used control for displaying data, although there are others. TheGridViewcan sort data, and present it in pages, for easy reading.Data controls need to be bound to a data source to display data. To do that, you provide a
DataSourcecontrol, which connects to the database and retrieves the data.You configure a
DataSourcecontrol with a wizard that allows you to set a connection string, and then helps you construct a SQL query for retrieving data, or you can enter your own custom query.You create a new connection with the Add Connection dialog, and then you can save it in your web.config file for future use.
The SQL
SELECTstatement allows you to specify which columns of data you want to retrieve, and from which table. The Wizard can configure this statement for you automatically.The SQL
INSERT,UPDATE, andDELETEstatements allow you to add, edit, and remove data, respectively. The Wizard can also generate these statements for you automatically, and you can easily add buttons to perform these functions in yourGridView.Optimistic concurrency is a technique that protects your data by only changing the database if no one else has changed it since you read the data. Again, the Wizard can enable optimistic concurrency for you.
The
WHERESQL clause filters the data you retrieve by specifying a condition for the data. A row will only be retrieved if that condition is true.You can create event handlers for the
GridView, which enables you to take action on rows as they’re bound, and also allows you to take action on rows as they’re selected.You can provide parameters to the
SELECTquery, which enables you to display data in aGridViewbased on the value of another control, even anotherGridView.
Adding the ability to access a database is arguably the most powerful improvement you can make to your site. It’s easy see how accessing a database would make the Order Form site from previous chapters that much more useful. Even the best order form, though, can’t retrieve the right data if users don’t give it valid input—if they enter a four-digit zip code, for example, or an improperly formatted credit card number. The whole thing would work much more smoothly if there was a way to check that the user’s responses are valid before you spend the time to access the database. The good news is that ASP.NET provides such a way, called validation, and that’s what you’ll learn about in the next chapter.
BRAIN BUILDER
Quiz
What type of control do you need to retrieve data from the database?
What is the name of the process for allowing a control, such as a
GridView,to extract data from the retrieved tables and format it properly?What is a connection string?
What are the four elements of CRUD?
How do you attach a data source to a
GridView?If your table has many rows, what should you do in the
GridViewto make it easier to read?What does optimistic concurrency do?
How can you enable users to change the contents of the database from your
GridView?How can you take an action based on the data in a row, as the table is loaded?
How do you filter the amount of data returned from a SELECT query?
Exercises
Exercise4-1. We’ll start out easy, letting
you create your own GridView. Create a new
web site called Exercise 4-1. Add to it a GridView control that shows records from the Product table with a Weight greater than 100. The GridView should list the Product ID, Product Name, Product
Number, Color, and List Price. The user should be able to update and delete
records, sort by rows, and page through the content. Use the Professional
formatting scheme to give it some style. The result should look like Figure 4-27.
Exercise4-2. This one is a little trickier,
but it lets you see how users could interact with the data in a GridView. Copy the web site from Exercise 4-1 to a
new web site, called Exercise 4-2. Add the ability to select rows in your
GridView. Add two labels and two
read-only textboxes below the GridView to
show the selected item’s Product Name and color. The result should look like
Figure 4-28.
Exercise4-3. Now it’s time to combine what
you’ve learned from previous chapters with the new stuff, and throw a little
AJAX into the mix as well. Create a new AJAX-enabled web site called Exercise
4-3. This site should have a radio button that gives readers the opportunity to
select whether they want to see data from the Employee table, or the Customer
table. The Employee panel should have a GridView showing the EmployeeID, ManagerID, and Title. The
Customer panel should have a GridView showing
the Customer ID, Account Number, and Customer Type. The table that the reader
chooses should appear dynamically in a new panel; the other one should be
invisible. The result should look like Figure 4-29.
Exercise 4-4. Ready for a bit of a challenge? Sure you are. You’re going to see how to retrieve data based on multiple customer selections—like you would in a shopping site. Create a new web site called Exercise 4-4. This site should have three dropdown menus:
A Category menu that lists the product categories from the
ProductCategorytableA Subcategory menu that lists the subcategories of the Category listed in the first drop-down, by using the
ProductSubcategorytableA Color menu that lists the available product colors from the
ProductmenuIn addition, there should be a Submit button that users click. Below all of this is a
GridViewthat displays the Products (from the Product table) that match the chosen subcategory and color. (You don’t need to match the category—all that control does is dictate the contents of the Subcategory table.) TheGridViewshould display the ProductID, Name, Product number, and the color, just so you can tell it’s working. (Hint: You can use theDISTINCTSQL statement to avoid duplication in your table.) It should look like Figure 4-30.
Get Learning ASP.NET 2.0 with AJAX now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
