Evjen c07.tex V2 - 01/28/2008 2:01pm Page 295
Chapter 7: Data Binding in ASP.NET 3.5
<
SelectParameters
>
<
asp:QueryStringParameter Name="CustomerID"
QueryStringField="ID" Type="String"
>
<
/asp:QueryStringParameter
>
<
/SelectParameters
>
<
/asp:SqlDataSource
>
In addition to hand-coding your
SelectParameters
collection, you can create parameters using the
Command and Parameter Editor dialog, which can be accessed by modifying the
SelectQuery
property
of the SqlDataSource control while you are viewing the Web page in design mode. Figure 7-5 shows the
Command and Parameter Editor dialog.
Figure 7-5
This dialog gives you a fast and friendly way to create
SelectParameters
foryourquery.Simplyselect
the Parameter source from the drop-down list and enter the required parameter data. Figure 7-5

295
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 296
Chapter 7: Data Binding in ASP.NET 3.5
demonstrates how to add the
QuerystringParameter
(based on the value of the
querystring
Field ID)
to your SqlDataSource control.
Conflict Detection Property
The
ConflictDetection
property allows you to tell the SqlDataSource control what style of conflict
detection to use when updating the data. This determines what action should be taken if more than one
user attempt to modify the same data. When the value is set to
OverwriteChanges
, the control uses a Last
in Wins style of updating data. In this style, the control overwrites any changes to data that have been
made between the time the data was retrieved by the control and the time the update is made.
If the value is set to
CompareAllValues
, the data source control compares the original data values (what
was retrieved) to the data values currently in the data store. If the data has not changed since it was
retrieved, the control allows the changes to be implemented. If the control detects differences between
the original data that was retrieved from the data store and what is currently in the data store, it does
not allow the update to continue. This could potentially occur when you have multiple users accessing
the data store and making changes to the data at the same time. In this case, another user could possibly
retrieve and change the data well before you send your own changes to the data store. If you don’t want
to override the previous user’s changes, you need to use the
CompareAllValues

value. Listing 7-5 shows
how to add the
ConflictDetection
property to the SqlDataSource control.
Listing 7-5: Adding the ConflictDetection property to a SqlDataSource control
<
asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="SELECT * FROM [Customers] WHERE ([CustomerID] = @CustomerID)"
ConnectionString="
<
%$ ConnectionStrings:AppConnectionString1 %
>
"
DataSourceMode="DataSet"
ConflictDetection="CompareAllValues"
>
<
SelectParameters
>
<
asp:QueryStringParameter Name="CustomerID"
QueryStringField="id" Type="String"
>
<
/asp:QueryStringParameter
>
<
/SelectParameters
>
<

/asp:SqlDataSource
>
As described earlier, you can also use the SqlDataSource Configuration Wizard to add optimistic concur-
rency to the control. Doing this causes several changes in the underlying control. First, it automatically
adds the
ConflictDetection
attribute to the control and sets it to
CompareAllValues
. Second, the wizard
modifies the
Update
and
Delete
parameter collections to include parameters for the original data values.
It also modifies the SQL statements so that they compare the original data values to the new values. You
can recognize the newly added parameters because the wizard simply prepends the prefix
original
to
each data column name. Listing 7-6 shows you what the modified
UpdateParameters
looks like.
Listing 7-6: Adding original value parameters to the UpdateParameters collection
<
UpdateParameters
>
<
asp:Parameter Name="CompanyName" Type="String" /
>
<
asp:Parameter Name="ContactName" Type="String" /

>
<
asp:Parameter Name="ContactTitle" Type="String" /
>
<
asp:Parameter Name="Address" Type="String" /
>
<
asp:Parameter Name="City" Type="String" /
>
<
asp:Parameter Name="Region" Type="String" /
>
<
asp:Parameter Name="PostalCode" Type="String" /
>
296
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 297
Chapter 7: Data Binding in ASP.NET 3.5
<
asp:Parameter Name="Country" Type="String" /
>
<
asp:Parameter Name="Phone" Type="String" /
>
<
asp:Parameter Name="Fax" Type="String" /
>
<
asp:Parameter Name="original_CustomerID" Type="String" /

>
<
asp:Parameter Name="original_CompanyName" Type="String" /
>
<
asp:Parameter Name="original_ContactName" Type="String" /
>
<
asp:Parameter Name="original_ContactTitle" Type="String" /
>
<
asp:Parameter Name="original_Address" Type="String" /
>
<
asp:Parameter Name="original_City" Type="String" /
>
<
asp:Parameter Name="original_Region" Type="String" /
>
<
asp:Parameter Name="original_PostalCode" Type="String" /
>
<
asp:Parameter Name="original_Country" Type="String" /
>
<
asp:Parameter Name="original_Phone" Type="String" /
>
<
asp:Parameter Name="original_Fax" Type="String" /

>
<
/UpdateParameters
>
Finally, the SqlDataSource Wizard sets an additional property called
OldValueParameterFormatString
.
This attribute determines the prefix for the original data values. By default, the value is
original_0
,but
you have complete control over this.
One way to determine whether your update has encountered a concurrency error is by testing the
AffectedRows
property in the SqlDataSource’s
Updated
event. Listing 7-7 shows one way to do this.
Listing 7-7: Detecting concurrency errors after updating data
VB
Protected Sub SqlDataSource1_Updated(ByVal sender as Object, _
ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
If (e.AffectedRows
>
0) Then
Message.Text = "The record has been updated"
Else
Message.Text = "Possible concurrency violation"
End If
End Sub
C#
protected void SqlDataSource1_Updated(object sender,

SqlDataSourceStatusEventArgs e)
{
if (e.AffectedRows
>
0)
Message.Text = "The record has been updated";
else
Message.Text = "Possible concurrency violation";
}
SqlDataSource Events
The SqlDataSource control provides a number of events that you can hook into to affect the behavior
of the SqlDataSource control or to react to events that occur while the SqlDataSource control is executing.
The control provides events that are raised before and after the Select, Insert, Update, and Delete com-
mands are executed. You can use these events to alter the SQL command being sent to the data source
by the control. You can cancel the operation or determine if an error has occurred while executing the
SQL command.
297
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 298
Chapter 7: Data Binding in ASP.NET 3.5
Using the Data Source Events to Handle Database Errors
The data source control events are very useful for trapping and handling errors that occur while you are
attempting to execute a SQL command against the database. For instance, Listing 7-8 demonstrates how
you can use the SqlDataSource control’s
Updated
event to handle a database error that has bubbled back
to the application as an exception.
Listing 7-8: Using the SqlDataSource control’s Updated event to handle database
errors
VB
Protected Sub SqlDataSource1_Updated(ByVal sender As Object, _

ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
If (e.Exception IsNot Nothing) Then
’An exception has occured executing the
’ SQL command and needs to be handled
lblError.Text = e.Exception.Message
’Finally, tell ASP.NET you have handled the
’ exception and it is to continue
’ executing the application code
e.ExceptionHandled = True
End If
End Sub
C#
protected void SqlDataSource1_Updated(object sender,
System.Web.UI.WebControls.SqlDataSourceStatusEventArgs e)
{
if (e.Exception != null)
{
//An exception has occured executing the
// SQL command and needs to be handled
lblError.Text = e.Exception.Message;
//Finally, tell ASP.NET you have handled the
// exception and it is to continue
// executing the application code
e.ExceptionHandled = true;
}
}
Notice that the sample tests to see if the
Exception
property is
null

; if it is not (indicating an exception
has occurred), the application attempts to handle the exception.
An extremely important part of this sample is the code that sets the
ExceptionHandled
property. By
default, this property returns
False
. Therefore, even if you detect the
Exception
property is not null and
you attempt to handle the error, the exception still bubbles out of the application. Setting the
Exception-
Handled
property to
True
tells .NET that you have successfully handled the exception and that it is safe
to continue executing. Note that the
AccessDataSource
and
ObjectDataSource
controls also function in
this manner.
298
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 299
Chapter 7: Data Binding in ASP.NET 3.5
Although the SqlDataSource control is powerful, a number of other data source controls might suit your
specific data access scenario better.
Using the SqlDataSource with Oracle
Just as you would use the SqlDataSource control to connect to Microsoft’s SQL Server, you can also use
this same control to connect to other databases that might be contained within your enterprise, such as

Oracle.
To use the SqlDataSource control with Oracle, start by dragging and dropping the SqlDataSource con-
trol onto your page’s design surface. Using the SqlDataSource control’s smart tag, you are then able to
configure your data source by clicking the Configure Data Source link.
Within the Configure Data Source wizard, you first need to create a new connection to your Oracle
database. Click on the New Connection button to open the Add Connection dialog which is shown in
Figure 7-6.
By default, the Data Source is configured to work with a SQL Server database but you can change this by
simply pressing the Change button. This will launch a new dialog that allows you to select Oracle. This
dialog is presented here in Figure 7-7.
This discussion is for the Microsoft Oracle Provider t hat comes with Visual Studio.
Oracle also has its own provider, named the Oracle Data Provider (ODP), and the
procedures to use that would be different. Consult the ODP documentation for
details.
Selecting an Oracle database will then modify the Add Connection dialog so that it is more appropriate
for the job. This is presented in Figure 7-8.
From the Add Connection dialog, you can add the name of the Oracle database that you are connecting
to in the Server name text box. The name you place here is the name of the database that is held in the
tnsnames.ora
configuration file. This file is put into place after you install the Oracle Client on the server
that will be making the connection to Oracle. You will find this file under a folder structure that is specific
to the version of the Oracle client software — f or example:
C:
\
Oracle
\
product
\
10.1.0
\

Client_1
\
NETWORK
\
ADMIN
. One particular database entry in this
tnsnames.ora
file is presented here:
MyDatabase =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
)
(CONNECT_DATA =
(SID = MyDatabase)
(SERVER = DEDICATED)
)
)
After the reference to the database, you can then use your database username and password and then
simply use the SqlDataSource control as you would if you were working with SQL Server. Once the
configuring of the SqlDataSource is complete, you will then find a new connection to Oracle in your
299
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 300
Chapter 7: Data Binding in ASP.NET 3.5
<
connectionStrings
> section of the
web.config
(if you chose to save the connection string there
through the configuration process). An example of this is presented here:

<
connectionStrings
>
<
add name="ConnectionString"
connectionString="Data Source=MyDatabase;User
ID=user1;Password=admin1pass;Unicode=True"
providerName="System.Data.OracleClient" /
>
<
/connectionStrings
>
Figure 7-6
300
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 301
Chapter 7: Data Binding in ASP.NET 3.5
Figure 7-7
Figure 7-8
301
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 302
Chapter 7: Data Binding in ASP.NET 3.5
LINQ Data Source Control
The LinqDataSource control is a new Data Source control introduced in ASP.NET 3.5 that allows you to
use the new LINQ features of .NET 3.5 to query data objects in your application.
This chapter focuses primarily on how to use the LinqDataSource control and its design-time configura-
tion options. If you want to learn more about LINQ, its syntax, and how it works with different object
types, refer to Chapter 9 in this book.
The LinqDataSource control works much the same way as any other Data Source control, converting
the properties you set on the control into queries that can be executed on the targeted data object.
Much like the SqlDataSource control, which generated SQL statements based on your property

settings, the LinqDataSource control converts the property settings into valid LINQ queries. When you
drag the control onto the Visual Studio design surface, you can use the smart tag to configure the control.
Figure 7-9 shows the initial screen of the configuration wizard.
Figure 7-9
From this screen you can choose the context object you want to use as the source of your data. The context
object is the base object that contains the data you want to query. By default, the wizard will show only
objects that are derived from the
System.Data.Linq.DataContext
base class, which are normally data
context classes created by LINQ to SQL. The wizard does give you the option of seeing all objects in your
application (even those included as references in your project) and allowing you to select one of those as
your context object.
Once you have selected your context object, the wizard allows you to select the specific table or prop-
erty within the context object that returns the data you want to bind to, as shown in Figure 7-10. If you
are binding to a class derived from
DataContext
, the table drop-down list shows all of the data tables
302
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 303
Chapter 7: Data Binding in ASP.NET 3.5
contained in the context object. If you are binding to a standard class, then the drop-down allows you to
select any enumerable property exposed by the context object.
Figure 7-10
Once you have selected the table, you can click the Finish button and complete the wizard. Listing 7-9
shows the markup that is generated by the LinqDataSource Configuration Wizard after it is configured
to use the Northwind database as its context object and the Customers table.
Listing 7-9: The basic LinqDataSource control markup
<
asp:LinqDataSource ID="LinqDataSource1" runat="server"
ContextTypeName="NorthwindDataContext" TableName="Customers"

EnableInsert="True" EnableUpdate="True" EnableDelete="True"
>
<
/asp:LinqDataSource
>
The LinqDataSource is now ready to be bound to a data control such as a GridView or ListView.
303
Evjen c07.tex V2 - 01/28/2008 2:01pm Page 304
Chapter 7: Data Binding in ASP.NET 3.5
Notice that the markup generated by the control includes three properties:
EnableInsert
,
EnableUpdate
and
EnableDelete
. These properties allow you to configure the control to allow Insert, Update and
Delete actions if the underlying data source supports them. Because the data source control knows that
it is connected to a LINQ To SQL data context object, which by default supports these actions, it has
automatically enabled them.
The LinqDataSource also includes a number of other basic configuration options you can use to
control the selection of data from the context object. As shown in Figure 7-10, the configuration wizard
also allows you to select specific fields to include in its resultset.
While this can be a convenient way to control which fields are displayed in a bound control such as the
GridView, it also causes the underlying LINQ query to return a custom projection. A side effect of this
is that the resulting dataset no longer supports the inserting, updating, or deletion of data. If you simply
want to limit the data shown by the bound list control, you may want to consider defining the fields to
display in the bound list control rather than in the data source control.
If you choose to select specific fields for the LinqDataSource control to return, the wizard adds the
Select
attribute to the markup with the appropriate LINQ projection statement. This is shown in Listing 7-10,

where the control has been modified t o return only the CustomerID, ContactName, ContactTitle, and
Region fields.
Listing 7-10: Specifying LinqDataSource control data fields
<
asp:LinqDataSource ID="LinqDataSource1" runat="server"
ContextTypeName="NorthwindDataContext" TableName="Customers"
Select="new (CustomerID, ContactName, ContactTitle, Region)"
>
<
/asp:LinqDataSource
>
Binding the control to the GridView, you will now see that only these four specified fields are displayed.
If no
Select
property is specified, the LinqDataSource control simply returns all public properties
exposed by the data object.
Query Operations
The LinqDataSource control also allows you to specify different query parameters such as
Where
and
OrderBy
. Configuration of either option is available by clicking the Where or OrderBy buttons in the
Controls Configuration Wizard.
Defining a Where Clause
The
Where
parameters are created using the same basic
Parameters
syntax used by other Data Source
controls, which means that you can provide values from a variety of runtime sources such as Form fields,

Querystring values, or even Session values. Listing 7-11 demonstrates the use of
Where
parameters.
Listing 7-11: Specifying Where clause parameters
<
asp:LinqDataSource ID="LinqDataSource1" runat="server"
ContextTypeName="NorthwindDataContext" TableName="Customers"
Select="new (CustomerID, ContactName, ContactTitle, Region)"
Where="CustomerID == @CustomerID"
>
<
whereparameters
>
304