Evjen c07.tex V2 - 01/28/2008 2:01pm Page 315
Chapter 7: Data Binding in ASP.NET 3.5
SqlCacheDependency
property to create SQL dependencies. You can learn more about ASP.NET
caching features in Chapter 23.
Storing Connection Information
In ASP.NET 1.0/1.1, Microsoft introduced the
web.config
file as a way of storing application configura-
tion data in a readable and portable format. Many people quickly decided that the
web.config
file was a
great place to store things like the database connection information their applications use. It was easy to
access from within the application, created a single central location for the configuration data, and it was
a cinch to change just by editing the XML.
Although all those advantages were great, several drawbacks existed. First, none of the information
in the
web.config
file can be strongly typed. It was, therefore, difficult to find data type problems within
the application until a runtime error occurred. It also meant that developers w ere unable to use the power
of IntelliSense to facilitate development. A second problem was that although the
web.config
file was
secured from access by browsers (it cannot be served up by Internet Information Server), the data within
the file was clearly visible to anyone who had file access to the Web server.
Microsoft has addressed these shortcomings of the
web.config
file by adding features specifically
designed to make it easier to work with and secure connection string information in the
web.config
file. Because database connection information is so frequently stored in the

web.config
file, starting with
ASP.NET 2.0, a new configuration section was added in that file. The
<
connectionStrings
>section is
designed specifically for storing the connection string information.
If you examine your
web.config
file, you should see at least one connection string already in the
<
connectionStrings
> section because our example told the Data Connection Wizard to store connec-
tions in the
web.config
file. Listing 7-19 shows how ASP.NET stores a connection string.
Listing 7-19: A typical connection string saved in the web.config file
<
connectionStrings
>
<
add name="AppConnectionString1" connectionString="Server=localhost;
User ID=sa;Password=password;Database=Northwind;
Persist Security Info=True" providerName="System.Data.SqlClient" /
>
<
/connectionStrings
>
Using a separate configuration section has several advantages. First, .NET exposes the
ConnectionString

section using the
ConnectionStringSettings
class. This class contains a collection of all the connection
strings entered in your
web.config
file and allows you to add, modify, or remove connection strings at
runtime. Listing 7-20 shows how you can access and modify connection strings at runtime.
Listing 7-20: Modifying connection string properties at runtime
VB
<
%@ Page Language="VB" %
>
<
script runat="server"
>
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
Continued
315
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 316
Chapter 7: Data Binding in ASP.NET 3.5
If (Not Page.IsPostBack) Then
’ Create a new ConnectionStringSettings object and populate it
Dim conn As New ConnectionStringSettings()
conn.ConnectionString = _
"Server=localhost;User ID=sa;Password=password" & _
"Database=Northwind;Persist Security Info=True"
conn.Name = "AppConnectionString1"
conn.ProviderName = "System.Data.SqlClient"
’ Add the new connection string to the web.config
ConfigurationManager.ConnectionStrings.Add(conn)

End If
End Sub
<
/script
>
<
html xmlns=" />>
<
head runat="server"
>
<
title
>
Modifying the Connection String
<
/title
>
<
/head
>
<
body
>
<
form id="form1" runat="server"
>
<
div
>
<

asp:SqlDataSource ID="SqlDataSource1" Runat="server"
>
<
/asp:SqlDataSource
>
<
/div
>
<
/form
>
<
/body
>
<
/html
>
C#
<
%@ Page Language="C#" %
>
<
script runat="server"
>
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
// Create a new ConnectionStringSettings object and populate it
ConnectionStringSettings conn = new ConnectionStringSettings();

conn.ConnectionString =
"Server=localhost;User ID=sa;Password=password; " +
"Database=Northwind;Persist Security Info=True";
conn.Name = "AppConnectionString1";
conn.ProviderName = "System.Data.SqlClient";
// Add the new connection string to the web.config
ConfigurationManager.ConnectionStrings.Add(conn);
}
}
<
/script
>
316
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 317
Chapter 7: Data Binding in ASP.NET 3.5
As you can see, the
ConfigurationManager
class now has a
ConnectionStrings
collection property
in addition to the
AppSettings
collection used in ASP.NET 1.0. This new collection contains all the
connection strings for your application.
Additionally, ASP.NET makes it much easier to build connection strings using strongly typed properties
at runtime, and easier to add them to the
web.config
file. Using the new
SqlConnectionStringBuilder
class, you can build connection strings and then add them to your

ConnectionStringSettings
collec-
tion. Listing 7-21 shows how you can use the
ConnectionStringBuilder
class to dynamically assemble
connection strings at runtime and save them to your
web.config
file.
Listing 7-21: Building connection str ings using ConnectionStringBuilder
VB
’ Retrieve an existing connection string into a Connection String Builder
Dim builder As New System.Data.SqlClient.SqlConnectionStringBuilder()
’ Change the connection string properties
builder.DataSource = "localhost"
builder.InitialCatalog = "Northwind1"
builder.UserID = "sa"
builder.Password = "password"
builder.PersistSecurityInfo = true
’ Save the connection string back to the web.config
ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString = _
builder.ConnectionString
C#
// Retrieve an existing connection string into a Connection String Builder
System.Data.SqlClient.SqlConnectionStringBuilder builder = new
System.Data.SqlClient.SqlConnectionStringBuilder();
// Change the connection string properties
builder.DataSource = "localhost";
builder.InitialCatalog = "Northwind1";
builder.UserID = "sa";
builder.Password = "password";

builder.PersistSecurityInfo = true;
// Save the connection string back to the web.config
ConfigurationManager.ConnectionStrings["AppConnectionString1"].ConnectionString =
builder.ConnectionString;
Using Bound List Controls
with Data Source Controls
The data source controls included in ASP.NET really shine when you combine them with the Bound List
controls also included in ASP.NET. This combination allows you to declaratively bind your data source
to a bound control without ever writing a single line of C# or VB c ode.
317
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 318
Chapter 7: Data Binding in ASP.NET 3.5
Fear not, those of you who like to write code. You can always use the familiar
DataBind()
method to
bind data to the list controls. In fact, that method has even been enhanced to include a Boolean overload
that allows you to turn the data-binding events on or off. Thi s enables you improve the performance of
your application if you are not using any of the binding events.
GridView
With ASP.NET 1.0/1.1, Microsoft introduced a new set of server controls designed to make developers
more productive. One of the most popular controls was the DataGrid. With this one control, you could
display an entire collection of data, add sorting and paging, and perform inline editing. Although this
new functionality was great, many tasks still required that the developer write a significant amount of
code to take advantage of this advanced functionality.
Beginning with ASP.NET 2.0, Microsoft took the basic DataGrid and enhanced it, creating a new server
control called the GridView. This control makes it even easier to use those advanced DataGrid features,
usually without having to write any code, and it also adds a number of new features.
Displaying Data with the GridView
Start using the GridView by dragging the control onto the designer surface of an ASP.NET Web page.
You are prompted to select a data source control to bind to the grid. In this sample, you use the SqlData-

Source control created earlier in the chapter.
After you assign the GridView a data source, notice a number of changes. First, the GridView changes
its design-time display to reflect the data exposed by the data source control assigned to it. Should
the schema of the data behind the data source control e ver change, you can use the GridView’s Refresh
Schema option to force the grid to redraw itself based on the new data schema. Second, the GridView’s
smart tag has additional options for formatting, paging, sorting, and selection.
Switch the page to Source view in Visual Studio to examine GridView’s code. Listing 7-22 shows the code
generated by Visual Studio.
Listing 7-22: Using the GridView control in an ASP.NET Web page
<
html
>
<
head runat="server"
>
<
title
>
Using the GridView server control
<
/title
>
<
/head
>
<
body
>
<
form id="form1" runat="server"

>
<
div
>
<
asp:GridView ID="GridView1" Runat="server" DataSourceID="SqlDataSource1"
DataKeyNames="CustomerID" AutoGenerateColumns="False"
>
<
Columns
>
<
asp:BoundField ReadOnly="True" HeaderText="CustomerID"
DataField="CustomerID"
SortExpression="CustomerID"
><
/asp:BoundField
>
<
asp:BoundField HeaderText="CompanyName" DataField="CompanyName"
SortExpression="CompanyName"
><
/asp:BoundField
>
318
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 319
Chapter 7: Data Binding in ASP.NET 3.5
<
asp:BoundField HeaderText="ContactName" DataField="ContactName"
SortExpression="ContactName"

><
/asp:BoundField
>
<
asp:BoundField HeaderText="ContactTitle" DataField="ContactTitle"
SortExpression="ContactTitle"
><
/asp:BoundField
>
<
asp:BoundField HeaderText="Address" DataField="Address"
SortExpression="Address"
><
/asp:BoundField
>
<
asp:BoundField HeaderText="City" DataField="City"
SortExpression="City"
><
/asp:BoundField
>
<
asp:BoundField HeaderText="Region" DataField="Region"
SortExpression="Region"
><
/asp:BoundField
>
<
asp:BoundField HeaderText="PostalCode" DataField="PostalCode"
SortExpression="PostalCode"

><
/asp:BoundField
>
<
asp:BoundField HeaderText="Country" DataField="Country"
SortExpression="Country"
><
/asp:BoundField
>
<
asp:BoundField HeaderText="Phone" DataField="Phone"
SortExpression="Phone"
><
/asp:BoundField
>
<
asp:BoundField HeaderText="Fax" DataField="Fax"
SortExpression="Fax"
><
/asp:BoundField
>
<
/Columns
>
<
/asp:GridView
>
<
asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="SELECT * FROM [Customers]"

ConnectionString="
<
%$ ConnectionStrings:AppConnectionString1 %
>
"
DataSourceMode="DataSet"
ConflictDetection="CompareAllValues" EnableCaching="True"
CacheKeyDependency="MyKey" CacheDuration="Infinite"
>
<
/asp:SqlDataSource
>
<
/div
>
<
/form
>
<
/body
>
<
/html
>
Figure 7-13 shows what your Web page looks like when you execute the code in the browser.
The GridView includes several events that are raised when the data binding occurs. These are described
in the following table.
Event Name Description
DataBinding
Raised as the GridViews data-binding expressions are about to be evaluated

RowCreated
Raised each time a new row is created in the grid. Before the grid can be
rendered, a
GridViewRow
object must be created for each row in the control.
The
RowCreated
event allows you to insert custom content into the row as it is
being created.
RowDataBound
Raised as each
GridViewRow
is bound to the corresponding data in the data
source. This event allows you to evaluate the data being bound to the current
row and to affect the output if you need to.
DataBound
Raised after the binding is completed and the GridView is ready to be
rendered.
319
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 320
Chapter 7: Data Binding in ASP.NET 3.5
Figure 7-13
The
RowDataBound
event is especially useful, enabling you to inject logic into the binding process for
each row being bound. Listing 7-23 shows how you can use the
RowDataBound
to examine the data being
bound to the current row and to insert your own logic. The code checks to see if the databases Region
column is

null
; if it is, the code changes the
ForeColor
and
BackColor
properties of the GridView rows.
Listing 7-23: Using the RowDataBound to examine the data being bound to the current
row and to insert your own l ogic
VB
<
script runat="server"
>
Protected Sub GridView1_RowDataBound(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
’Test for null since gridview rows like the
’ Header and Footer will have a null DataItem
If (e.Row.DataItem IsNot Nothing) Then
’Used to verify the DataItem object type
System.Diagnostics.Debug.WriteLine(e.Row.DataItem.ToString())
320
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 321
Chapter 7: Data Binding in ASP.NET 3.5
’When bound to a SqlDataSource, the DataItem
’ is generally returned as a DataRowView object
Dim drv As System.Data.DataRowView = _
CType(e.Row.DataItem, System.Data.DataRowView)
If (drv("Region") Is System.DBNull.Value) Then
e.Row.BackColor = System.Drawing.Color.Red
e.Row.ForeColor = System.Drawing.Color.White
End If

End If
End Sub
<
/script
>
C#
<
script runat="server"
>
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
//Test for null since gridview rows like the
// Header and Footer will have a null DataItem
if (e.Row.DataItem != null)
{
//Used to verify the DataItem object type
System.Diagnostics.Debug.WriteLine(e.Row.DataItem.ToString());
//When bound to a SqlDataSource, the DataItem
// is generally returned as a DataRowView object
System.Data.DataRowView drv = (System.Data.DataRowView)e.Row.DataItem;
if (drv["Region"] == System.DBNull.Value)
{
e.Row.BackColor = System.Drawing.Color.Red;
e.Row.ForeColor = System.Drawing.Color.White;
}
}
}
<
/script
>

The GridView also includes e vents that correspond to selecting, inserting, updating, and deleting data.
You learn more about these events later in the chapter.
Using the EmptyDataText and EmptyDataTemplate Properties
In some cases, the data source bound to the GridView may not contain any data for the control to bind
to. Even in these cases, you may want to provide the end user with some feedback, informing him that
no data is present for the control to bind to. The GridView offers you two techniques to do this.
You first option is to use the
EmptyDataText
property. The property allows you t o specify a string of text
that is displayed to the user when no data is present for the GridView to bind to. When the ASP.NET
page loads and the GridView determines there was no data available in its bound data source, it creates
a special DataRow containing the
EmptyDataText
value and displays that to the user. Listing 7-24 shows
how you can add this property to the GridView.
321
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 322
Chapter 7: Data Binding in ASP.NET 3.5
Listing 7-24: Adding EmptyDataText to the GridView
<
asp:GridView ID="GridView1" Runat="server" DataSourceID="SqlDataSource1"
DataKeyNames="CustomerID" AutoGenerateColumns="False"
EmptyDataText="No data was found using your query"
>
The other option is to use an EmptyDataTemplate template to completely customize the special row the
user sees when no data exists for the control to bind to.
A control template is simply a container that gives y ou the capability to add other content such as
text, HTML controls, or even ASP.NET controls. The GridView control provides you with a variety
of templates for various situations, including the EmptyDataTemplate template. You examine these
templates throughout the rest of the GridView control section of this chapter.

You can access the template from the Visual Studio 2008 design surface in two ways. The first option is
to right-click the GridView control, expand the Edit Template option in the context menu, and select the
EmptyDataTemplate item from the menu. This procedure is shown in Figure 7-14.
Figure 7-14
The o ther option for opening the template is to select the Edit Templates option f rom the GridView smart
tag. Selecting this option puts the GridView into template editing mode and presents you with a
dialog from which you can choose the specific template you wish to edit. Simply select EmptyData-
Template from the drop-down list, as shown in Figure 7-15.
After you have entered template editing mode, you can simply add your custom text and/or controls
to the template editor on the design surface. When you have finished editing the template, simply
right-click, or open the GridViews smart tag and select the End Template Editing option.
322
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 323
Chapter 7: Data Binding in ASP.NET 3.5
Figure 7-15
Switching to Source view, you see that an
<
EmptyDataTemplate
> element has been added to the Grid-
View control. The element should contain all the controls and HTML that you added while editing the
template. Listing 7-25 shows an e xample of an
EmptyDataTemplate
.
Listing 7-25: Using EmptyDataTemplate
<
EmptyDataTemplate
>
<
table style="width: 225px"
>

<
tr
>
<
td colspan="2"
>
No data could be found based on your query parameters.
Please enter a new query.
<
/td
>
<
/tr
>
<
tr
>
<
td style="width: 162px"
>
<
asp:TextBox ID="TextBox1" runat="server"
><
/asp:TextBox
><
/td
>
<
td style="width: 102px"
>

<
asp:Button ID="Button1" runat="server" Text="Search" /
><
/td
>
<
/tr
>
<
/table
>
<
/EmptyDataTemplate
>
You could, of course, have also added the template and its contents while in Source view.
Enabling GridView Column Sorting
The capability to sort data is one of the most basic tools users have to navigate through a significant
amount of data. The DataGrid control made sorting columns in a grid a relatively easy task, but the
GridView control takes it one step further. Unlike using the DataGrid, where you are responsible for
coding the sort routine, to enable column sorting in this grid, you just set the
AllowSorting
attribute to
True
. The control takes care of all the sorting logic for you internally. Listing 7-26 shows how to add this
attribute to your grid.
Listing 7-26: Adding sorting to the GridView control
<
asp:GridView ID="GridView1" Runat="server" DataSourceID="SqlDataSource1"
DataKeyNames="CustomerID" AutoGenerateColumns="False"
AllowSorting="True"

>
323
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 324
Chapter 7: Data Binding in ASP.NET 3.5
After enabling sorting, you see that all grid columns have now become hyperlinks. Clicking a column
header sorts that specific column. Figure 7-16 sho ws your grid after the data has been sorted by country.
Figure 7-16
GridView sorting has also b een enhanced in a number of other ways. The grid can handle both ascending
and descending sorting. If you repeatedly click on a column header, you cause the sort order to switch
back and forth between ascending and descending. The GridView’s
Sort
method can also accept multiple
SortExpressions
to enable multicolumn sorting. Listing 7-27 shows how you can use the GridView’s
sorting event to implement a multicolumn sort.
Listing 7-27: Adding multicolumn sorting to the GridView
VB
<
script runat="server"
>
Protected Sub GridView1_Sorting(ByVal sender As Object, _
ByVal e As GridViewSortEventArgs)
Dim oldExpression As String = GridView1.SortExpression
Dim newExpression As String = e.SortExpression
324