Data Source Controls and Connections

This chapter is excerpted from Programming ASP.NET 3.5, Fourth Edition by Jesse Liberty, Dan Maharry, Dan Hurwitz, published by O'Reilly Media

In previous chapters, you created web pages but they did not interact with real data. In this chapter, you'll look at the various sources of data with which you can fill your pages and the controls that ASP.NET provides to access them.

Then you'll look more deeply at databases, how you connect your sites to them, and how you bind the information they contain to the controls on your page. In particular, you'll look at the SqlDataSource control to access SQL Server and the server controls that can use it.

Sources of Data and DataSource Controls

It may be of some comfort that the various sources of data your websites can access have not changed. They are:

  • Databases (e.g., SQL Server, Oracle, MySQL)

  • XML documents (e.g., RSS feeds, the metabase for your Internet Information Services [IIS] web server)

  • Business objects

  • Flat files (e.g., IIS logfiles, CSV files, Excel files)

In addition, the various ways provided to bind data to items on a web page remain the same as well. This chapter will concentrate on the numerous data source controls available since ASP.NET 2.0.

Tip
The name generally used to refer to the ways in which you can access and use data in ASP.NET is ADO.NET. The ADO.NET team at Microsoft is responsible for the contents of the System.Data and System.Linq namespaces.

A data source control, which is derived from the System.Web.UI.DataSourceControl class, provides a single object that you can define declaratively (in your web page) or programmatically (in your code-behind file). It will manage internally the connection to the data, its selection, and various options concerning its presentation (such as paging and caching) so that all you need to do is tell a UI control what data source to use and everything else is taken care of. In some cases, the controls also allow you to send changes to that data back to the source from which it came. The entire life cycle of the query to and response from the data source is encapsulated in this one control.

Many data source controls exist for accessing data from SQL Server, from ODBC or OLE DB servers, from XML files, and from business objects. They are divided into two groups: those representing sources of hierarchical data (XML and Sitemap documents) and those representing flat or table-based data (in business objects, databases, or through LINQ sources), with members of both groups binding to UI objects (such as DataList and GridView) in the same way and handling the plumbing for you. This is in marked contract to ASP.NET 1.1 development, where you had to work with the plumbing directly using the ADO.NET object model.

Tip
In Chapter 9, ADO.NET, you'll look at ADO.NET in some detail because as with all abstractions, a DataSource control works well up to a point, but ADO.NET has many features that a DataSource control does not include, and some UI controls just don't work with DataSource controls.

The data source controls included with .NET 3.5 for hierarchical data are:

  • XMLDataSource

  • SiteMapDataSource

Those for flat or table-based data include:

  • ObjectDataSource

  • SqlDataSource

  • LinqDataSource

  • AccessDataSource

You will see the SqlDataSource (and controls derived from it) used frequently in this book to access databases. We use the SiteMapDataSource in the "Navigation" section in Chapter 13, Master Pages and Navigation, and the LinqDataSource in Chapter 10, Presenting LINQ. Figure 7.1, "The DataSource class hierarchy" shows how this division of controls is managed in the System.Web.UI.Control class hierarchy.

Although all of the data source controls shown in Figure 7.1, "The DataSource class hierarchy" share a common parent class, the properties they inherit from it aren't actually related to their association with either a source of data or its binding to a set of UI controls. Instead, they define their own unique set of properties relevant to the type of data they access. In the case of those inheriting from the DataSourceControl class, several of these properties have similar themes, as shown in Table 7.1, "DataSource properties with common themes".

Figure 7.1. The DataSource class hierarchy

The DataSource class hierarchy

Table 7.1. DataSource properties with common themes

Theme

Properties

Description

ObjectDS

LinqDS

SqlDS

Retrieving data from a data source

SelectCommand (SQL) SelectMethod (object)

Sets a SQL command or object method that retrieves data.

Yes

No

Yes

SelectParameters

Sets the parameters for the select command/method.

Yes

Yes

Yes

Updating a data source

UpdateCommand (SQL) UpdateMethod (object)

Sets a SQL command or object method that updates the original data store with changes made on the page.

Yes

No

Yes

UpdateParameters

Sets the parameters for the update command/method.

Yes

Yes

Yes

Adding new data to a source

InsertCommand (SQL) InsertMethod (object)

Sets a SQL command or object method that adds new data from the page to the original data store.

Yes

No

Yes

InsertParameters

Sets the parameters for the insert command/method.

Yes

Yes

Yes

Deleting data from a source

DeleteCommand (SQL) DeleteMethod (object)

Sets a SQL command or object method that deletes the data selected on the page from the original data store.

Yes

No

Yes

DeleteParameters

Sets the parameters for the delete command/method.

Yes

Yes

Yes

Caching data in the DataSource control

EnableCaching

true or false. Sets whether or not retrieved data is cached by the data source object.

Yes

No

Yes

CacheDuration

Sets the number of seconds for which the data source will cache data.

Yes

No

Yes

CacheExpiration-Policy

Defines how the cache behaves once data in the cache has expired.

Yes

No

Yes

CacheKeyDependency

Identifies a key for the controls that auto-expires the content of its cache if it is removed.

Yes

No

Yes


The key themes of creating, selecting, caching, changing, and finally deleting data apply to any and all data when working with web forms. Let's look at how these data source controls implement these themes across three different sources of data, starting with any business objects that you may have created for your site. Later on, you'll look at working with the data stored in XML documents and finally with any data stored in a database.

Most of the applications we'll look at in this book are two-tier, separating the user interface from the backend data. Many larger commercial applications, however, are n-tier, with at least one middle business-logic layer to separate the retrieval of data from a database from the manipulation (and validation) of that data before presentation. This separation means that business-layer logic can be more readily tested and refactored so code is not duplicated between pages when it does not need to be.

Take, for example, the Customer class in Example 7.1, "A simple business layer object class". It's necessarily simple for the purposes of this example and generates its own data rather than querying any external source of data, but the key idea is that it contains several methods-such as GetCustomers-returning different views of the customer data in the database as a generic DataSet container for the ObjectDataSource to then access and present onscreen.

Example 7.1. A simple business layer object class

using System.Data;

/// <summary>
/// An example Customer class
/// </summary>

public class Customer
{
   public int CustomerID { get; set; }
   public string Name { get; set; }
   public string City { get; set; }

   public Customer(  )
   { }

   /// <summary>
   /// Gets Customers and returns them in DataSet
   /// </summary>
   public DataSet GetCustomers(  )
   {
      // Add logic here for actual retrieval of data from DB

      DataSet ds = new DataSet(  );

      DataTable dt = new DataTable("Customers");
      dt.Columns.Add("CustomerId", typeof(System.Int32));
      dt.Columns.Add("CustomerName", typeof(System.String));
      dt.Columns.Add("CustomerCity", typeof(System.String));

      dt.Rows.Add(new object[] {1, "Test Customer", "Glasgow"});

      ds.Tables.Add(dt);
      return ds;
   }
}
Tip
Note the new C# 3.0 syntax for "automatic" properties, where:
public int CustomerID { get; set; }
is equivalent to:
private int customerID;
public int CutomerID
{
   get { return customerID;}
   set { customerID = value; }
}

With this class added to a website's App_Code directory, you can then create an ObjectDataSource to access the data returned by your GetCustomers method and bind it to a UI control on the page.

Create a new website called C7_DataSources and add Example 7.1, "A simple business layer object class" as Customer.cs to its App_Code directory. Now add a new web form to the site and call it ObjectDataSource.aspx. In Design view, drag an ObjectDataSource control onto the page and select Configure Data Source from its Common Tasks panel.

Tip
If you do not see the ObjectDataSource control, choose View → Visual Aids → ASP.NET Non Visual Controls from the VS2008 menu, or press Ctrl-Shift-N.

The dialog in Figure 7.2, "Configuring an ObjectDataSource, part 1" will appear, asking you to choose a business object to bind to. Choose Customer from the drop-down list and click Next.

Figure 7.2. Configuring an ObjectDataSource, part 1

Configuring an ObjectDataSource, part 1

You're now asked to associate methods exposed by the Customer object with the select, update, insert, and delete operations on the database. Our simple example has only one method for selecting all data, so select GetCustomers from the drop-down list, as shown in Figure 7.3, "Configuring an ObjectDataSource, part 2", and click Finish.

Now drag a GridView control onto the page, and in its Common Tasks panel, set its data source to ObjectDataSource1. You'll see the customer information created in the GetCustomers method displayed in a table, as shown in Figure 7.4, "ObjectDataSource.aspx in action".

Figure 7.3. Configuring an ObjectDataSource, part 2

Configuring an ObjectDataSource, part 2

Figure 7.4. ObjectDataSource.aspx in action

ObjectDataSource.aspx in action
Tip
We've added some color to the GridView control to make it easier for you to see its various rows and columns in screenshots. However, the attributes to achieve that color aren't in the code samples to keep them straightforward and easy to follow. If you'd like to add some color to the GridView, open its Common Tasks panel and click AutoFormat. You can choose from a number of color schemes in the subsequent dialog. We're using "Black and Blue 2".

Behind the scenes, VS2008 has generated the following markup for the ObjectDataSource and GridView:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
   SelectMethod="GetCustomers" TypeName="Customer">
</asp:ObjectDataSource>

<asp:GridView ID="GridView1" runat="server"
   DataSourceID="ObjectDataSource1">
</asp:GridView>

The GridView's DataSourceID property is set to the ID of the ObjectDataSource to link the two controls. Meanwhile, the ObjectDataSource uses two properties to associate the GetCustomers method with the retrieval of data to set into the GridView. TypeName identifies the object class, and SelectMethod identifies the method within that class that the control must use to retrieve that data. If you had selected methods for another operation in the screen shown in Figure 7.3, "Configuring an ObjectDataSource, part 2", you would see that operation's InsertMethod, UpdateMethod, and DeleteMethod properties added as appropriate.

The separation of UI layer from business logic within a website that the ObjectDataSource control offers has proven quite popular among some communities. For example, the encapsulation of several tables of information and relationships inside a middle-tier business object representing a "data entity" is a staple of the object-relational mapping (ORM) community, and the ObjectDataSource provides a direct connection from these entity classes to a website's presentation layer if required.

Tip
In "LINQ to SQL" in Chapter 10, Presenting LINQ, you'll see how Microsoft has gone another step in ASP.NET 3.5 to make the creation of data entities and hooking them into a website even easier.

XML is pretty much everywhere these days. It's in our web services, in our AJAX calls, in our RSS feeds, and, for the standards-compliant, in every web page being created at the moment. But it's important to remember that every XML document is still a flat file that contains a uniformly structured set of hierarchical data which can be accessed with an XmlDataSource as required. And if it's not in a format you can use, the XmlDataSource lets you apply an XSLT transform to it before the data is retrieved.

Let's take an example and use an O'Reilly Atom feed for O'Reilly's new book releases (http://feeds.feedburner.com/oreilly/newbooks) as the source of data for a web page. Add to the chapter's website a new page called XmlDataSource.aspx, and drag an XmlDataSource control onto it.

Tip
If you do not see the XmlDataSource control, choose View → Visual Aids → ASP.NET Non Visual Controls from the VS2008 menu, or press Ctrl-Shift-N.

Click Configure DataSource in the XmlDataSource's Common Tasks panel and fill it in as shown in Figure 7.5, "Configuring the XmlDataSource control".

Figure 7.5. Configuring the XmlDataSource control

Configuring the XmlDataSource control

When binding to an XmlDataSource, an ASP.NET control automatically looks for the attributes on an XML element rather than its child elements as you might assume. For example, the Atom feed this example consumes has the following outline:

<feed>
   <entry>
      <title> ...  </title>
      <updated>  ...  </updated>
   </entry>
   <entry>  ...  </entry>
   ...
</feed>

An XmlDataSource will find nothing to add to a DataGrid unless the XML it finds looks like this:

<feed>
   <entry title="..." updated="..."> ... </entry>
   <entry title="..." updated="..."> ... </entry>
   ...
   <entry title="..." updated="..."> ... </entry>
</feed>

To this end, the XmlDataSource must use an XSLT transform to get the Atom feed in the correct form and then look at the item elements to get the needed information, which is why in Figure 7.5, "Configuring the XmlDataSource control" it is given a transform file and an XPath document to filter out the rest of the RSS feed information.

Tip
Extensible Stylesheet Language Transformations (XSLT) is a web standard for transforming an XML document from one format into another. XPath is also a web standard, this time for identifying an element or a set of elements within an XML document. For more information about both, have a look at XSLT, Second Edition, by Doug Tidwell (O'Reilly).

Create an XSLT file in the same directory as XmlDataSource.aspx and add the code in Example 7.2, "XSLT transform code for Atom".

Example 7.2. XSLT transform code for Atom

<?xml version="1.0"?>
<xsl:stylesheet
   version="1.0"
   xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema"
   xmlns:atom="http://www.w3.org/2005/Atom"
   xmlns:msxsl="urn:schemas-microsoft-com:xslt">
  <xsl:strip-space elements="*"/>
  <xsl:output method="xml"
      omit-xml-declaration="yes"
      indent="yes"
      standalone="yes" />
  <xsl:template match="/">
    <xsl:for-each select="atom:feed">
      <xsl:element name="feed">
        <xsl:for-each select="atom:entry">
          <xsl:element name="entry">
            <xsl:attribute name="title">
              <xsl:value-of select="atom:title"/>
            </xsl:attribute>
            <xsl:attribute name="updated">
              <xsl:value-of select="atom:updated"/>
            </xsl:attribute>
          </xsl:element>
        </xsl:for-each>
      </xsl:element>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

Now drag a GridView control onto the page, and in its Common Tasks panel set its data source to XmlDataSource1. Save the page and run it. You'll see the titles and dates of the new books displayed in a table, as shown in Figure 7.6, "XmlDataSource.aspx in action".

Figure 7.6. XmlDataSource.aspx in action

XmlDataSource.aspx in action

Behind the scenes, the markup for the XmlDataSource and GridView looks like this:

<asp:XmlDataSource ID="XmlDataSource1" runat="server"
   DataFile="http://feeds.feedburner.com/oreilly/newbooks"
   TransformFile="~/XSLTFile.xsl" XPath="feed/entry">
</asp:XmlDataSource>

<asp:GridView ID="GridView1" runat="server"
   DataSourceID="XmlDataSource1">
</asp:GridView>

As with the previous ObjectDataSource example, only the DataSourceID property needs to be set on the GridView. The onus is on the XmlDataSource to retrieve and bind the data to it. Table 7.2, "XmlDataSource properties" lists the properties you can set on an XmlDataSource control.

Table 7.2. XmlDataSource properties

Name

Values

Description

CacheDuration

Integer

Sets the number of seconds for which the data source will cache data

CacheExpirationPolicy

Absolute, Sliding

Defines how the cache behaves once data in the cache has expired

CacheKeyDependency

String

Identifies a key for the controls that auto-expires the content of its cache if it is removed

Data

String

A string containing the XML for the XmlDataSource control to use

DataFile

String

The path to a file (relative or absolute) containing the XML for the control to use

EnableCaching

Boolean

Specifies whether or not to cache data retrieved from the XML in the control

Transform

String

A string containing the XSLT transform to be used on the target XML data before the control uses it

TransformArgumentList

XsltArgumentList

A list of arguments to be used by the transform given in the Transform or TransformFile property

TransformFile

String

The path to a file (relative or absolute) containing the XSLT transform to be used on the target XML data before the control uses it

XPath

String

An XPath expression identifying which elements in the XML should be used to provide the data for the control


And then, of course, there are databases - relational or otherwise. Be it Microsoft's SQL Server, MySQL, Oracle, PostgreSQL, Firebird, DB/2, Access, or something else, databases are the most common way to store data for our web applications, and you'll spend the next few chapters looking at how to interact with databases successfully and efficiently.

The AdventureWorksLT Database

In this book, you'll use the sample AdventureWorksLT database running on SQL Server for all the examples. You can download it from the Releases page on http://www.codeplex.com/MSFTDBProdSamples. You'll need to download and run AdventureWorksLT.msi if you're running a 32-bit version of SQL Server or AdventureWorksLT_x64.msi if you're running a 64-bit version. You'll also need to make sure you're downloading the sample for the correct version of SQL Server-2005 or 2008. Click the correct version in the Releases box on the right side of the page to make that selection.

Once you have downloaded and installed the database, you'll need to attach it to your copy of SQL Server. If you're using SQL Server Express, simply copy the AdventureWorksLT_data.mdf you installed to your App_Data directory and SQL Server Express will do the rest.

If you're using SQL Server Standard, Developer, or Enterprise, you'll need to attach it to the database engine. Open SQL Server Management Studio (SSMS) and connect to the server on which you just installed the database. Right-click the Databases node in SSMS and select Attach from the menu. In the Attach Databases dialog that appears, click Add, and select AdventureWorksLT_data.mdf, as shown in Figure 7.7, "Attaching the AdventureWorksLT database". By default, this file is installed in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\. Finally, click OK.

Figure 7.7. Attaching the AdventureWorksLT database

Attaching the AdventureWorksLT database
Tip
Be aware that if you're using Windows Vista, you'll need to log on as an administrator to install the AdventureWorksLT database and then to attach the database.

Using the Server Explorer

As we mentioned in Chapter 2, Visual Studio 2008, the Visual Studio Server Explorer allows you to access any server to which you have network access. With respect to databases, you use the Server Explorer window (also called the Database Explorer in Visual Web Developer) to open data connections and to access and edit database information. To connect to the AdventureWorksLT database from the Server Explorer right-click the Data Connections node and select Add Connection. The dialog in Figure 7.8, "Creating a connection for the Server Explorer" appears, for which you'll need to supply the name of the database server and the database to which to connect-AdventureWorksLT. Click OK.

Figure 7.8. Creating a connection for the Server Explorer

Creating a connection for the Server Explorer

The Server Explorer will now have an entry for the new connection which you can expand to view all the programmatic elements of the database. For example, Figure 7.9, "The Server Explorer in action" shows the database in the Server Explorer, expanded to show all the tables it contains.

Figure 7.9. The Server Explorer in action

The Server Explorer in action

Visual Studio 2008 (VS2008) offers a crude but rapid page prototyping facility through the Server Explorer that binds a GridView control to a table of data. To demonstrate, create in the chapter's website a new web form called SqlDataSource.aspx, and in Design view drag the BuildVersion table from the AdventureWorksLT database in the Server Explorer onto the page. VS2008 automatically generates a SqlDataSource control that retrieves all the data from this table, and a GridView control that uses the SqlDataSource control to supply it with data. Running the page (after applying a little formatting to the GridView for screenshot purposes) results in a fully functional page, as shown in Figure 7.10, "SqlDataSource.aspx in action".

If you have a look at the markup generated for the page in VS2008, as shown next, you'll see that the SqlDataSource generated contains ConnectionString, ProviderName, and SelectCommand properties. You'll look at what each of these does and how you can tweak them later in this chapter.

Figure 7.10. SqlDataSource.aspx in action

SqlDataSource.aspx in action
<asp:GridView ID="GridView1" runat="server"
   AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
   EmptyDataText="There are no data records to display.">
   <Columns>
      <asp:BoundField DataField="SystemInformationID"
         HeaderText="SystemInformationID"
         ReadOnly="True" SortExpression="SystemInformationID"></asp:BoundField>
      <asp:BoundField DataField="Database_Version" HeaderText="Database_Version"
         SortExpression="Database_Version"></asp:BoundField>
      <asp:BoundField DataField="VersionDate" HeaderText="VersionDate"
         SortExpression="VersionDate"></asp:BoundField>
      <asp:BoundField DataField="ModifiedDate" HeaderText="ModifiedDate"
         SortExpression="ModifiedDate"></asp:BoundField>
   </Columns>
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
   ConnectionString="<%$ ConnectionStrings:AdventureWorksLTConnectionString1 %>"
   ProviderName=
      "<%$ ConnectionStrings:AdventureWorksLTConnectionString1.ProviderName %>"
   SelectCommand="SELECT [SystemInformationID], [Database Version] AS
      Database_Version, [VersionDate], [ModifiedDate] FROM [BuildVersion]">
</asp:SqlDataSource>

Meanwhile, VS2008 has automatically created individual columns in the GridView that map to each column in the BuildVersion table you dragged onto Design view. This represents the simplest way to bind a table of data to an ASP.NET control.

Configuring the SqlDataSource

The more conventional, flexible way to bind a table of data to an ASP.NET control is to use the Configure Data Source Wizard to set up a SqlDataSource control as you set up the ObjectDataSource and XmlDataSource controls earlier. In this next demo, you'll bind the Customer table from the AdventureWorksLT database to a GridView.

Add to the chapter's website a new web form called SqlDataSourceWizard.aspx. Drag a SqlDataSource control from the Toolbox onto the page and select Configure Data Source from its Common Tasks dialog.

Tip
If you do not see the SqlDataSource control, choose View → Visual Aids → ASP.NET Non Visual Controls from the VS2008 menu, or press Ctrl-Shift-N.

Your first option (see Figure 7.11, "Choosing the connection for your SqlDataSource control") is to choose an existing connection or to click the New Connection button. You could choose to use the connection to the database created by the previous demo-you'll see it in the drop-down list-but in this case, you'll create a second connection to go through all the steps in the wizard.

Figure 7.11. Choosing the connection for your SqlDataSource control

Choosing the connection for your SqlDataSource control

Click New Connection. When you create a new connection, you'll be asked to fill in the server name. Decide whether you want to use a trusted connection (Windows Authentication) or a specific username and password. You'll also be asked which database to connect to (as shown previously in Figure 7.8, "Creating a connection for the Server Explorer").

Tip
To use a trusted connection, you will need to modify your SQL Server database through SSMS. If you're using IIS 5.x, go to the Security section and add machineName\ASPNET as a user. Then go to the AdventureWorksLT database, add the ASPNET user as a user, and set its role to dbo_owner. If you're using IIS 6 or 7, perform the preceding steps but add machinename\NETWORK SERVICE as a user instead of machinename\ASPNET.

Click the Test Connection button to ensure your connection is correct. Then click OK to save the connection.

You now have the option of saving the connection string in the web.config file (the alternative is to save the connection string in the page as a property of the control). Generally, you'll want to save the connection string in web.config, as shown in Figure 7.12, "Saving the connection". Here it is more secure, as by default, web.config will never be sent to the browser. It can also be encrypted, but more on that later. Click Next to save the string.

Figure 7.12. Saving the connection

Saving the connection

The next step is to specify your query or to pick the columns you want from a specific table (see Appendix B, Relational Database Technology: A Crash Course for a crash course on relational databases and querying for data). For this example, you'll choose the CustomerID, FirstName, LastName, CompanyName, and EmailAddress columns from the Customer table, as shown in Figure 7.13, "Choosing columns from the Customer table".

While you are here, click the Advanced button to see that you can instruct the wizard to generate SQL statements used to update the database, which you'll look at later in this chapter. For now, however, you can leave this unchecked.

The next step in the wizard allows you to test your query. However, clicking Test Query will result in an error, as shown in Figure 7.14, "Problems querying the Customer table".

Figure 7.13. Choosing columns from the Customer table

Choosing columns from the Customer table

Figure 7.14. Problems querying the Customer table

Problems querying the Customer table

So, why did this work for the BuildVersion table in the previous example but not for the Customer table? The answer lies in a feature of SQL Server 2005 called schemas. Like C# namespaces within an assembly, or XML namespaces within a document, the purpose of a database schema is to disambiguate the purpose of a group of tables within that database from others. It's also a way to partition database objects for security reasons. In the AdventureWorksLT database, the Customer table is part of the SalesLT schema, so all SQL statements used to access it must refer to it as SalesLT.Customer, something that the SqlDataSource configuration wizard doesn't do by default.

Tip
You can spot a schema in a database by looking at its list of tables. In SSMS, all tables within a schema will be listed as schema.tablename. In the VS2008 Server Explorer (shown earlier in Figure 7.9, "The Server Explorer in action"), they will be listed as tablename (schema).

You have two options to change the SELECT statement to reference the Customer table correctly. The first is to click Finish on the wizard, switch to Source view, and change the SQL statement in the SqlDataSource's SelectCommand property to the following:

SELECT [CustomerID], [FirstName], [LastName], [CompanyName],
   [EmailAddress] FROM [SalesLT].[Customer]

The second option is to stay in the wizard, go back to the previous screen, and click the radio button shown in Figure 7.13, "Choosing columns from the Customer table", marked "Specify a custom SQL statement or stored procedure," and click Next. The wizard then allows you to write a SQL statement from scratch or to edit the one it has already generated, as shown in Figure 7.15, "Defining a custom SQL statement for the SqlDataSource".

Figure 7.15. Defining a custom SQL statement for the SqlDataSource

Defining a custom SQL statement for the SqlDataSource

The advantage of this option is your ability to then test that your custom SELECT statement works, as shown in Figure 7.16, "Testing the query".

Figure 7.16. Testing the query

Testing the query

Clicking Finish creates the connection. Now all you need to do is drag a GridView control onto the page and set its DataSource to SqlDataSource1 (which you just created) with the GridView's Common Tasks panel. Finally, run the page and there you have it, as shown in Figure 7.17, "SqlDataSourceWizard.aspx in action".

Figure 7.17. SqlDataSourceWizard.aspx in action

SqlDataSourceWizard.aspx in action

Example 7.3, "SqlDataSourceWizard.aspx in full" shows the final markup for SqlDataSourceWizard.aspx. There is no additional code-behind to note.

Example 7.3. SqlDataSourceWizard.aspx in full

<%@ Page Language="C#" AutoEventWireup="true"
   CodeFile="SqlDataSourceWizard.aspx.cs"
   Inherits="SqlDataSourceWizard" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
   <title>SqlDataSource Wizard Demo</title>
</head>

<body>
   <form id="form1" runat="server">
   <div>

      <asp:SqlDataSource ID="SqlDataSource1" runat="server"
         ConnectionString=
            "<%$ ConnectionStrings:AdventureWorksLTConnectionString %>"
         SelectCommand="SELECT [CustomerID], [FirstName], [LastName],
           [CompanyName], [EmailAddress] FROM [SalesLT].[Customer]">
      </asp:SqlDataSource> 

      <asp:GridView ID="GridView1" runat="server"
         AutoGenerateColumns="False" DataKeyNames="CustomerID"
         DataSourceID="SqlDataSource1">
         <Columns>
            <asp:BoundField DataField="CustomerID"
               HeaderText="CustomerID" InsertVisible="False"
               ReadOnly="True" SortExpression="CustomerID">
            </asp:BoundField>
            <asp:BoundField DataField="FirstName"
               HeaderText="FirstName" SortExpression="FirstName">
            </asp:BoundField>
            <asp:BoundField DataField="LastName"
               HeaderText="LastName" SortExpression="LastName">
            </asp:BoundField>
            <asp:BoundField DataField="CompanyName"
               HeaderText="CompanyName" SortExpression="CompanyName">
            </asp:BoundField>
            <asp:BoundField DataField="EmailAddress"
               HeaderText="EmailAddress" SortExpression="EmailAddress">
            </asp:BoundField>
         </Columns>
      </asp:GridView>
   </div>
   </form>
</body>
</html>

Besides the mandatory ID and runat properties, VS2008 has generated two properties for the SqlDataSource control (highlighted in Example 7.3, "SqlDataSourceWizard.aspx in full"). SelectCommand contains the custom SQL SELECT statement you had to tweak to retrieve data from the database, and ConnectionString contains a reference to the connection string to the database rather than the string itself. This is one of a number of options for working with connection strings expanded upon in the next section.

In addition to those commonly themed properties already mentioned in Table 7.1, "DataSource properties with common themes", the SqlDataSource object also has the properties given in Table 7.3, "Additional SqlDataSource properties".

Table 7.3. Additional SqlDataSource properties

Property

Values

Description

CancelSelectOnNullParameter

Boolean

Cancels the selection of data from the database if the value of any parameter for the SelectCommand is null.

ConflictDetection

CompareAllValues, OverwriteChanges

Sets what happens if the data you want to update has already been updated before your page tries to make its changes. Default is OverwriteChanges.

ConnectionString

String

A reference to or the actual connection string to be used to connect to the database.

DataSourceMode

DataReader, DataSet

Sets which ADO.NET object the DataSource control will use to retrieve data. See Chapter 9, ADO.NET for the difference between the two.

FilterExpression

String

Data retrieved using the given SelectCommand will not be bound to a UI control unless it also satisfies the condition given in the FilterExpression. If not specified, all data retrieved is bound to the control.

FilterParameters

ParameterCollection

The collection of parameters associated with the FilterExpression.

ProviderName

String

The name of the .NET data provider being used by the SqlDataSource object to connect to the database.

SqlCacheDependency

String

A semicolon-delimited list of tables in the database in which a change will trigger a refresh of the cache in the DataSource control. More on this in Chapter 17, Caching and Performance.


Storing Connection Strings

In the previous example, the Configure Data Source Wizard created a connection string based on your input, saved it in web.config, and left a reference to it in the markup for the SqlDataSource control:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
   ConnectionString=
      "<%$ ConnectionStrings:AdventureWorksLTConnectionString %>"
      ...>
</asp:SqlDataSource>

The connection string itself is saved in the website's web.config file in the <connectionStrings> element:

<connectionStrings>
   <add name="AdventureWorksLTConnectionString"
      connectionString="Data Source=(local)\sql2k5;
         Initial Catalog=AdventureWorksLT;Integrated Security=True"
      providerName="System.Data.SqlClient" />
 </connectionStrings>

As you can see, the string is actually stored within a key-value pair, and the key-in this case, AdventureWorksLTConnectionString-is then used to refer to the string itself using this syntax:

"<%$ ConnectionStrings:key %>"

This is one of several ways to create, store, and make available a connection string to a DataSource upon request. Other possibilities include the following:

  1. Putting the connection string directly into the DataSource's ConnectionString property:

    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
       ConnectionString=
          "Data Source=(local)\sql2k5; Initial Catalog=AdventureWorksLT;
           Integrated Security=True" ...>
    </asp:SqlDataSource>
    

    This isn't a good idea for two reasons: it's not very secure, and you can't reuse the string elsewhere unless you redeclare it, which, as proponents of Cascading Style Sheets (CSS) will testify, is not a good thing.

  2. Extracting the connection string out of web.config into its own config file. Most elements in web.config have a configSource property that you can use to specify a separate file containing all the information for that element. So, instead of web.config containing your connection string section:

    <connectionStrings>
       <add name="AdventureWorksLTConnectionString"
          connectionString="Data Source=(local)\sql2k5;
             Initial Catalog=AdventureWorksLT;Integrated Security=True"
          providerName="System.Data.SqlClient" />
     </connectionStrings>
    

    you can replace it with:

    <connectionStrings configSource="~/connectionstrings.config" />
    

    and then create a file called connectionstrings.config which contains only the <connectionStrings> element and its contents. This has the advantage of making the connection string easier to find and to secure. Changing it also doesn't cause an application restart and the loss of session state. For example, you could put connectionString.config in your site's App_Code directory, from which IIS will never allow a download.

  3. Using the aspnet_setreg utility to encrypt the data string, store it in the web server's Registry, and then leave a reference to the Registry in web.config.

    Although this is definitely the most secure option, you have to run aspnet_setreg every time you want to change the connection string. See http://support.microsoft.com/kb/821616 for more on this technique.

  4. Storing the string as a constant somewhere in your data access code and assigning it programmatically to the DataSource as the code-behind file is run. This has the advantage that the string is hidden in compiled code. The disadvantage is that to change the string, you have to change the code, build and redeploy it, and then restart the website. Compare this to storing it in web.config, where changing it there will automatically restart the web app because IIS is aware the string has been changed.

  5. Using the SqlConnectionStringBuilder class to build up the string programmatically rather than storing it as a constant. This has the same pros and cons as storing the string as a string constant in code, but with the extra advantage that the builder class's properties present you with options they will add into the string that you might not have realized were there.

Warning
Be aware that it's actually quite easy to decompile code and retrieve the connection string from it. Hence, options 4 and 5 are significantly less secure than the other three.

Option 5 does pose an interesting question. What information can a connection string store? Most often, the connection string to a SQL Server database looks like this if it is using Windows Security:

Data Source=(local)\sql2k5;
  Initial Catalog=AdventureWorksLT;Integrated Security=True"

Or it looks like this if it is using a SQL Server account:

Data Source=(local)\sql2k5;
  Initial Catalog=AdventureWorksLT;User Id=uid;Password=pwd"

You might be well advised to include the Application Name property in your connection string, which allows you to identify calls made into the database by your website, which is very handy while debugging or monitoring performance. For example:

Data Source=(local)\sql2k5;Initial Catalog=AdventureWorksLT;
   Integrated Security=True;Application Name=myWebSite"
Tip
What else you include in your connection string will depend on how you're using your database. Perhaps you're using it as a mirror, accessing it asynchronously or connecting to it over TCP/IP rather than the default Named Pipes protocol. It will also depend on which database product you're using. Consult http://connectionstrings.com for a full list of possibilities.

Passing Parameters to the Select Query

Sometimes you do not want to display all the records in a table. Take, for instance, the previous example in which you simply selected all the rows in the Customer table and dumped them into a GridView; it's not easy to read, and chances are your client will want to view only portions of this full list-for example, just the customers dealt with by a particular staff member. To do this, you'll need a way to select a company and a way to pass the ID of the selected staff member to the grid to display that staff member's clients. In this example, you'll use a DropDownList.

In your website, make a copy of SqlDataSourceWizard.aspx and call it SqlDataSourceParameters.aspx. Rename SqlDataSource1 to CustomersDataSource and GridView1 to CustomerGridView.

In Design view, add a second SqlDataSource control to the page and give it the ID StaffDataSource. Use the Data Source Configuration Wizard to connect it to the AdventureWorksLT database and, by specifying a custom SQL statement in the wizard (rather than directly in the source to fix the schema problem), set its SELECT statement to:

SELECT DISTINCT [SalesPerson] FROM [SalesLT].[Customer]

The DISTINCT keyword used here ensures that staff members are included in the list only once, regardless of how many times they are actually listed in the database table. Figure 7.18, "Using a SELECT DISTINCT statement" shows the wizard.

Figure 7.18. Using a SELECT DISTINCT statement

Using a SELECT DISTINCT statement

Now drag a DropDownList onto the page and click Choose a Data Source. Select StaffDataSource as the data source and SalesPerson to be both the text and the value for each item in the DropDownList, as shown in Figure 7.19, "Hooking a DataSource to a DropDownList". Click OK.

Figure 7.19. Hooking a DataSource to a DropDownList

Hooking a DataSource to a DropDownList

If you run the page now, you'll see the DropDownList populated with staff members, but there's no connection between the member you select in the list and the customers shown in the table. Close the page, and back in Design view open the Data Source Configuration Wizard for CustomersDataSource. Skip the connection screen and go to the SELECT statement step, and then click the WHERE button on the right side of the dialog.

The Add WHERE Clause dialog opens. SQL SELECT statements use WHERE clauses to create conditions that must be satisfied by a row of data before that row is returned to the data source. In this case, you want to retrieve the customer details only if they were served by the staff member selected in the drop-down list.

First, pick the column on which you want to match-in this case, SalesPerson. Next, pick the operator, which can be equals, less than/greater than, like, contains, and so on. In this case, you'll use the default (=).

The third drop down (Source) lets you pick the source for the SalesPerson. You can pick None if you will be providing a source in code, or you can obtain the source from the form, a user's profile, a QueryString, or session state. In this case, you'll obtain the source of the SalesPerson from the DropDownList, so choose Control.

When you choose Control, the Parameter Properties window wakes up. You are asked to provide the ID of the Control providing the parameter, in this case DropDownList1, and (optionally) a default value. Once you've made all your choices, the screen will look like Figure 7.20, "Adding a WHERE clause".

Figure 7.20. Adding a WHERE clause

Adding a WHERE clause

Now 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.

Click OK until you are back at the Configure Select Statement dialog box. While you're at it, sort the results on the customer's LastName in ascending order by clicking the "Sort by" button, as shown in Figure 7.21, "Sorting LastName in ascending order".

After you finish creating this SqlDataSource control, switch to Source view and look at the declaration created by VS2008. You'll notice the wizard hasn't added the SalesLT schema to its SQL statement, so you'll need to add it, as highlighted in the following code.

Figure 7.21. Sorting LastName in ascending order

Sorting LastName in ascending order
<asp:SqlDataSource ID="CustomersDataSource" runat="server"
    ConnectionString=
       "<%$ ConnectionStrings:AdventureWorksLTConnectionString %>"
    SelectCommand=
       "SELECT [CustomerID], [FirstName], [LastName],
          [CompanyName], [EmailAddress]
        FROM [SalesLT].[Customer]
        WHERE ([SalesPerson] = @SalesPerson)
        ORDER BY [LastName]">
    <Select Parameters>
       <asp:ControlParameter ControlID="DropDownList1"
          Name="SalesPerson"
          PropertyName="SelectedValue" Type="String" />
    </SelectParameters>
 </asp:SqlDataSource>

The SELECT statement now has a WHERE clause that includes a parameterized value (@SalesPerson). In addition, within the definition of the SqlDataSource control is a definition of SelectParameters, which includes one parameter of type asp:ControlParameter and is a parameter that knows how to get its value from a control. The asp:ControlParameter has one property, ControlID, that tells it which control to check for its value, and a second property, PropertyName, that specifies which property in the DropDownList to check. A third property, Type, tells it that it is getting a value of type string, so it can properly pass that parameter to the SELECT statement.

Now run your page and try to select different names in the list. Note that the grid contents don't change because changing the selected item in a DropDownList does not automatically post the page back to the server for updates. To change this, set the AutoPostBack property for the DropDownList to true and run the page again. This time, as expected, as you select each staff member his or her clients are displayed in the grid below. Example 7.4, "SqlDataSourceParameters.aspx in full" shows the source for SqlDataSourceParameters.aspx in full.

Example 7.4. SqlDataSourceParameters.aspx in full

<%@ Page Language="C#" AutoEventWireup="true"
   CodeFile="SqlDataSourceParameters.aspx.cs"
   Inherits="SqlDataSourceParameters" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
   <title>Data Source Parameters Demo</title>
</head>

<body>
   <form id="form1" runat="server">
   <div>
      <asp:SqlDataSource ID="StaffDataSource" runat="server"
         ConnectionString=
         "<%$ ConnectionStrings:AdventureWorksLTConnectionString1 %>"
         SelectCommand=
         "SELECT DISTINCT [SalesPerson] FROM [SalesLT].[Customer]">
      </asp:SqlDataSource>

      <asp:DropDownList ID="DropDownList1" runat="server"
         DataSourceID="StaffDataSource" DataTextField="SalesPerson"
         DataValueField="SalesPerson" AutoPostBack="true">
      </asp:DropDownList>

      <asp:SqlDataSource ID="CustomersDataSource" runat="server"
         ConnectionString=
         "<%$ ConnectionStrings:AdventureWorksLTConnectionString %>"
         SelectCommand=
         "SELECT [CustomerID], [FirstName], [LastName],
            [CompanyName], [EmailAddress]
          FROM [SalesLT].[Customer]
          WHERE ([SalesPerson] = @SalesPerson)
          ORDER BY [LastName]">
         <SelectParameters>
            <asp:ControlParameter ControlID="DropDownList1"
               Name="SalesPerson" PropertyName="SelectedValue"
               Type="String" />
         </Select Parameters>
      </asp:SqlDataSource>
   </div>

   <asp:GridView ID="CustomerGridView" runat="server"
      AutoGenerateColumns="False" DataKeyNames="CustomerID"
      DataSourceID="CustomersDataSource">
      <Columns>
         <asp:BoundField DataField="CustomerID"
            HeaderText="CustomerID" InsertVisible="False"
            ReadOnly="True" SortExpression="CustomerID" />
         <asp:BoundField DataField="FirstName"
            HeaderText="FirstName" SortExpression="FirstName" />
         <asp:BoundField DataField="LastName"
            HeaderText="LastName" SortExpression="LastName" />
         <asp:BoundField DataField="CompanyName"
            HeaderText="CompanyName" SortExpression="CompanyName" />
         <asp:BoundField DataField="EmailAddress"
            HeaderText="EmailAddress" SortExpression="EmailAddress" />
      </Columns>
   </asp:GridView>
   </form>
</body>
</html>

To extend the example, you could also add an AJAX UpdatePanel to the page so the page does not appear to post back to the server when a new staff member is chosen from the list.

Writing Data to a Database

In the SqlDataSourceWizard.aspx example, the SqlDataSource control you created has only a SELECT statement to extract data from the database:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
   ConnectionString=
      "<%$ ConnectionStrings:AdventureWorksLTConnectionString %>"
   SelectCommand=
      "SELECT [CustomerID], [FirstName], [LastName], [CompanyName],
      [EmailAddress] FROM [SalesLT].[Customer]">
</asp:SqlDataSource>

However, to make your work easier you can ask your data source control to create the remaining SQL CREATE, UPDATE, and DELETE statements using a wizard. Create a copy of SqlDataSourceWizard.aspx in your website and rename it SqlReadWrite.aspx. Open your new web form and switch to Design view.

Click 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 7.13, "Choosing columns from the Customer table". This time, click the Advanced button.

This opens the Advanced SQL Generation Options dialog box. Click the "Generate INSERT, UPDATE, and DELETE statements" checkbox, as shown in Figure 7.22, "Generating SQL write statements with the Configure Data Source Wizard".

Figure 7.22. Generating SQL write statements with the Configure Data Source Wizard

Generating SQL write statements with the Configure Data Source Wizard

Clicking this checkbox instructs the wizard to create the remaining SQL statements, and it also enables the second checkbox: "Use optimistic concurrency". Do not check this yet. Click OK, then Next, and then Finish. Your GridView is now bound to a data source control that provides all four CRUD methods.

Tip
CRUD is shorthand for Create, Retrieve, Update, and Delete, the four basic operations you can perform on data in a database.

Open the GridView's smart tag and check Enable Editing and Enable Deleting.

Unfortunately, the wizard is no better at generating SQL write commands that use database schemas than it is at generating SQL SELECT statements, so you need to add them back in manually. Example 7.5, "A newly readable/writable SqlDataSource control" shows the HTML generated for the SqlDataSource control and highlights the changes you'll need to make to allow for the SalesLT schema in the AdventureWorksLT database.

Example 7.5. A newly readable/writable SqlDataSource control

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
   ConnectionString=
      "<%$ ConnectionStrings:AdventureWorksLTConnectionString %>"
   SelectCommand=
      "SELECT [CustomerID], [FirstName], [LastName],
      [CompanyName], [EmailAddress]
      FROM [SalesLT].[Customer]"

   DeleteCommand=
      "DELETE FROM [SalesLT].[Customer]
        WHERE [CustomerID] = @CustomerID"

   InsertCommand=
      "INSERT INTO [SalesLT].[Customer]
      ([FirstName], [LastName], [CompanyName], [EmailAddress])
      VALUES (@FirstName, @LastName, @CompanyName, @EmailAddress)"

   UpdateCommand=
      "UPDATE [SalesLT].[Customer]
      SET [FirstName] = @FirstName, [LastName] = @LastName,
      [CompanyName] = @CompanyName, [EmailAddress] = @EmailAddress
      WHERE [CustomerID] = @CustomerID">

   <DeleteParameters>
      <asp:Parameter Name="CustomerID" Type="Int32" />
   </DeleteParameters>
   <UpdateParameters>
      <asp:Parameter Name="FirstName" Type="String" />
      <asp:Parameter Name="LastName" Type="String" />
      <asp:Parameter Name="CompanyName" Type="String" />
      <asp:Parameter Name="EmailAddress" Type="String" />
      <asp:Parameter Name="CustomerID" Type="Int32" />
   </UpdateParameters>
   <InsertParameters>
      <asp:Parameter Name="FirstName" Type="String" />
      <asp:Parameter Name="LastName" Type="String" />
      <asp:Parameter Name="CompanyName" Type="String" />
      <asp:Parameter Name="EmailAddress" Type="String" />
   </InsertParameters>
</asp:SqlDataSource>

Working through this, the SqlDataSource has seven properties. You've seen the ID, runat, ConnectionString, and SelectCommand properties before. The last three, however, are new-each representing one of the SQL write statements you just generated-and are accompanied by new child elements representing the parameters that each statement requires to work.

The DeleteCommand deletes the record in the Customer table containing the CustomerID stored in the parameter @CustomerID which is specified in the DeleteParameters element:

DeleteCommand=
   "DELETE FROM [SalesLT].[Customer]
   WHERE [CustomerID] = @CustomerID"
<DeleteParameters>
   <asp:Parameter Name="CustomerID" Type="Int32" />
</DeleteParameters>

The UpdateCommand makes changes to some, all, or none of the values in the Customer record with the CustomerID stored in the parameter @CustomerID. It contains a parameter for each field that the DataSource control retrieves in its SelectCommand (not each field the table contains), which will be filled either with the current value of the field or with a new one according to the user.

UpdateCommand=
   "UPDATE [SalesLT].[Customer]
   SET [FirstName] = @FirstName, [LastName] = @LastName,
   [CompanyName] = @CompanyName, [EmailAddress] = @EmailAddress
   WHERE [CustomerID] = @CustomerID">

<UpdateParameters>
   <asp:Parameter Name="FirstName" Type="String" />
   <asp:Parameter Name="LastName" Type="String" />
   <asp:Parameter Name="CompanyName" Type="String" />
   <asp:Parameter Name="EmailAddress" Type="String" />
   <asp:Parameter Name="CustomerID" Type="Int32" />
</UpdateParameters>

Finally, the InsertCommand adds a new Customer record to the table containing the values given by the parameters in the command. Note that CustomerID is not included because it is generated automatically by the database.

InsertCommand=
   "INSERT INTO [SalesLT].[Customer]
   ([FirstName], [LastName], [CompanyName], [EmailAddress])
   VALUES (@FirstName, @LastName, @CompanyName, @EmailAddress)"

<InsertParameters>
   <asp:Parameter Name="FirstName" Type="String" />
   <asp:Parameter Name="LastName" Type="String" />
   <asp:Parameter Name="CompanyName" Type="String" />
   <asp:Parameter Name="EmailAddress" Type="String" />
</InsertParameters>

Save and run SqlReadWrite.aspx. The contents of the customer database table are loaded into the GridView just as they were in the previous example, SqlDataSourceWizard.aspx. The difference now is the addition of the two links on the left side of each row marked Edit and Delete, as shown in Figure 7.23, "New Edit and Delete buttons for the GridView".

When you click the Edit link, the GridView automatically enters edit mode. You'll notice that the editable text fields change to text boxes, and the links change from Edit and Delete to Update and Cancel, as shown in Figure 7.24, "Editing a record".

Figure 7.23. New Edit and Delete buttons for the GridView

New Edit and Delete buttons for the GridView

Figure 7.24. Editing a record

Editing a record

Make a change to a field and click Update. When you do, the grid and the database are updated, as you can see on the page and in the Customer table itself using VS2008's Server Explorer window. If you click Delete against a row, the DataSource control will attempt to delete that customer from the database. Note, though, that the database may not allow that to happen if the customer has any orders logged for it.

Tip
In this example, the parameters for the UpdateCommand are set transparently by the GridView. You'll look at how to set them explicitly in Chapter 8, Using Data-Aware Controls.

If you prefer to have buttons for Edit and Delete, rather than links, click the smart tag and then click Edit Columns. When the Fields dialog box opens, click Selected Fields on the Command Field entry. This brings up the Command Field properties in the righthand window, where you can change the ButtonType from Link to Button by clicking ButtonType in the Appearance section of the Fields editor.

Tip
You'll find more GridView customizations in Chapter 8, Using Data-Aware Controls.

Multiuser Updates

As things stand now, you read data from the database and move the data into your GridView through the SqlDataSource. You have now added the ability to update (or delete) that information. Of course, more than one person may be interacting with the database simultaneously (few web applications support only single-user access).

You can easily imagine that this could cause tremendous problems of data corruption. Consider, for example, two people downloading a record:

Co-operative Web Ltd. / Birmingham / Dan Maharry

The first editor changes the city from Birmingham to London. The second editor changes the contact name from Dan Maharry to Dan Mahoney. Now, things get interesting. The first editor writes back the data record and the database has the following record:

Co-operative Web Ltd. / London / Dan Maharry

A moment later, the second editor updates the database. Now the database has the following record:

Co-operative Web Ltd. / Birmingham / Dan Mahoney

The values updated earlier are overwritten and lost. The technical term for this is bad.

To prevent this problem, you might be tempted to use any of the following strategies:

  • Lock the records. When one user is working with a record, other users can read the record but they cannot update it. This is called pessimistic record locking, and if you have many users, the database quickly becomes fully locked and unusable.

  • Update only the columns you change. This is great in theory, but it exposes you to the risk of having a database that is internally consistent but no longer reflects reality. Suppose two salespeople each check the inventory for a given book. The NumberOnHand is 1. They each change only the NumberOnHand field to 0. The database is perfectly happy, but one customer is not going to get the book because you can sell a given book only once (much to our chagrin). To prevent this, you are back to locking records, and you already read we don't like that solution.

  • You could decide that before you make an update, you'll check to see whether the record has changed and make the update only to unchanged records. Unfortunately, this still does not solve the problem. If you look at the database before updating it, there is the (admittedly small) chance that someone else will update the database between the time you peek at it and the time you write your changes. Given enough transactions over enough time, collisions and corrupted data will occur.

    This is also inefficient because it requires accessing the database twice for each update (to read and then to write). In a high-volume application, the performance hit will be costly.

  • Attempt the change in a way that is guaranteed to generate an error if the record has changed, and then handle these (rare) errors as they occur. This is called optimistic concurrency.

To implement optimistic concurrency, your WHERE clause will include the original values (stored for you automatically by the data set) so you can ensure the record will not be updated if it has been changed by another user. Thus, you do not need to "preread" the record; you can write (once) and if the record has changed, it will not be updated.

This approach has tremendous efficiency advantages. In the vast majority of cases, your update will succeed, and you will not have bothered with extra reads of the database. If your update succeeds, no lag exists between checking the data and the update, so there is no chance of someone sneaking in another write. Finally, if your update fails, you will know why and can take corrective action.

For this approach to work, your updates must fail if the data has changed in the database since the time you retrieved the data. Because the data source can tell you the original values it received from the database, you only need to pass those values back into the stored procedure as parameters and then add them to the Where clause in your Update statement. That is, you must extend your Where statement to say "where each field still has its original value."

When you update the record, the original values are checked against the values in the database. If they are different, you will not update any records until you fix the problem (which could only have been caused by someone else updating the records before you did).

To see how this is done, let's go back and turn on optimistic concurrency. Go back to Design view for SqlReadWrite.aspx, reopen the Data Source Configuration Wizard and click Next. Because you changed the SQL statements to use the [SalesLT].[Customer] table, you can't click the Advanced button yet. Check the radio button to specify columns from a table or view and reselect CustomerID, FirstName, LastName, CompanyName, and EmailAddress from the Customer table. Now you can click Advanced and select both checkboxes to enable optimistic concurrency.

Now click OK, then Next, and then Finish to close the wizard. You'll need to go back into the source code now to add the schema back into the SQL statements, as shown earlier, but that requires much less effort than writing all the additional code you just generated, as highlighted in Example 7.6, "SQL statements using optimistic concurrency".

Example 7.6. SQL statements using optimistic concurrency

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
   ConnectionString=
      "<%$ ConnectionStrings:AdventureWorksLTConnectionString %>"
   SelectCommand=
      "SELECT [CustomerID], [FirstName], [LastName],
      [CompanyName], [EmailAddress] FROM [SalesLT].[Customer]"

   DeleteCommand="DELETE FROM [SalesLT].[Customer]
      WHERE [CustomerID] = @original_CustomerID
      AND [FirstName] = @original_FirstName
      AND [LastName] = @original_LastName
      AND [CompanyName] = @original_CompanyName
      AND [EmailAddress] = @original_EmailAddress"

   InsertCommand=
      "INSERT INTO [SalesLT].[Customer]
      ([FirstName], [LastName], [CompanyName], [EmailAddress])
      VALUES (@FirstName, @LastName, @CompanyName, @EmailAddress)"

   UpdateCommand=
      "UPDATE [SalesLT].[Customer]
      SET [FirstName] = @FirstName, [LastName] = @LastName,
      [CompanyName] = @CompanyName, [EmailAddress] = @EmailAddress
      WHERE [CustomerID] = @original_CustomerID
      AND [FirstName] = @original_FirstName
      AND [LastName] = @original_LastName
      AND [CompanyName] = @original_CompanyName
      AND [EmailAddress] = @original_EmailAddress"
      ConflictDetection="CompareAllValues"
      OldValuesParameterFormatString="original_{0}"> 

   <DeleteParameters>
      <asp:Parameter Name="original_CustomerID" Type="Int32" />
      <asp:Parameter Name="original_FirstName" Type="String" />
      <asp:Parameter Name="original_LastName" Type="String" />
      <asp:Parameter Name="original_CompanyName" Type="String" />
      <asp:Parameter Name="original_EmailAddress" Type="String" />
   </DeleteParameters>
   <UpdateParameters>
      <asp:Parameter Name="FirstName" Type="String" />
      <asp:Parameter Name="LastName" Type="String" />
      <asp:Parameter Name="CompanyName" Type="String" />
      <asp:Parameter Name="EmailAddress" Type="String" />
      <asp:Parameter Name="original_CustomerID" Type="Int32" />
      <asp:Parameter Name="original_FirstName" Type="String" />
      <asp:Parameter Name="original_LastName" Type="String" />
      <asp:Parameter Name="original_CompanyName" Type="String" />
      <asp:Parameter Name="original_EmailAddress" Type="String" />
   </UpdateParameters>
   <InsertParameters>
      <asp:Parameter Name="FirstName" Type="String" />
      <asp:Parameter Name="LastName" Type="String" />
      <asp:Parameter Name="CompanyName" Type="String" />
      <asp:Parameter Name="EmailAddress" Type="String" />
   </InsertParameters>
</asp:SqlDataSource>

Don't panic. There are only two actual differences between Examples Example 7.5, "A newly readable/writable SqlDataSource control" and Example 7.6, "SQL statements using optimistic concurrency". First, the WHERE clause of the Delete and Update commands has been extended to ensure that the record being deleted or updated has not been altered in between the data being retrieved to the page and the command being executed. This is the purpose of the original_xxx parameters.

The wizard has also added the following attributes:

ConflictDetection="CompareAllValues"
OldValuesParameterFormatString="original_{0}">

The two possible values for the ConflictDetection parameter are CompareAllValues (in which case no changes will be made to the database if the original values have changed) and OverwriteChanges (in which case the new values will overwrite the old).

Warning
OverwriteChanges blasts away anything anyone else has entered and writes your updates to the database. As you can imagine, this is used rarely and only with great caution. Most of the time, you'll use CompareAllValues.

Finally, OldValuesParameterFormatString simply indicates the naming convention used to identify the parameters representing the original values for the record being updated.

Some programmers get nervous when a control does so much work invisibly. After all, when all goes well, it is great not to have to sweat the details, but if something does go wrong, how can you tell whether your connection failed, no records were updated, an exception was thrown, or exactly what happened? Related to that, what if you want to modify the behavior of the control in some way?

The ASP.NET controls in general, and the data controls in particular, overcome these concerns by providing numerous events that you can handle. For example, the SqlDataSource control described in this chapter has nine events you can handle that are not inherited from its parent control. There is an event you can handle when the DataSource is about to run a SELECT statement (Selecting) and one that you can handle immediately after the SELECT statement has finished executing (Selected). Indeed, there are three other pairs of events for each of the other SQL statements that a DataSource works with, as shown in Table 7.4, "SQL statements and their respective DataSource events". The ninth event, Filtering, can be used to validate or alter the values being used in WHERE clauses before they are used with a statement.

Table 7.4. SQL statements and their respective DataSource events

SQL statement

Event occurring before statement is run

Event occurring after statement has run

SELECT

Selecting

Selected

INSERT

Inserting

Inserted

UPDATE

Updating

Updated

DELETE

Deleting

Deleted

WHERE clause

Filtering

N/A


To see this at work, let's use the Selected event to reflect back to the page how many customers a staff member is associated with.

Make a copy of SqlDataSourceParameters.aspx on the site and call it SqlDataSourceEvents.aspx. In Design view, drag a Label control onto the page above the GridView, but below the DropDownList control. Give it the ID lblSelectStats and delete the Text property.

Now select the CustomerDataSource control, and in the Properties dialog click the lightning bolt icon to bring up a list of the control's events. Double-click in the space next to the Selected event and add the highlighted code to the empty handler which is generated for you in SqlDataSourceEvents.aspx.cs:

protected void CustomersDataSource_Selected(
   object sender, SqlDataSourceStatusEventArgs e)
{
   lblSelectStats.Text =
      String.Format("Number of rows selected: {0}", e.AffectedRows);
}

Now run the page and you'll see that the Label reflects the number of rows in the GridView each time you select a staff member, as shown in Figure 7.25, "Using the Selected event in SqlDataSourceEvents.aspx".

In this example, the SqlDataSourceStatusEventArgs object provides a handle to the number of rows selected from the database through its AffectedRows property. Its three other properties provide access to the actual SELECT command sent to the database (Command), any exception thrown as a result of the command (Exception), and a value indicating to the DataSource whether the exception was handled (ExceptionHandled). This last property can come in handy if you need to ignore an Exception (by setting it to true) more directly than through a try-catch construct.

Figure 7.25. Using the Selected event in SqlDataSourceEvents.aspx

Using the Selected event in SqlDataSourceEvents.aspx

Looking back at the other DataSource objects you've seen in this chapter, the XmlDataSource supports only one event-Transforming-that occurs just before your XSLT stylesheet is applied to the XML document being used as your source of data. In comparison, the ObjectDataSource supports the same nine events as the SqlDataSource control, plus an additional three signifying that the object being used as the source of data is going to be created (ObjectCreating), has been created (ObjectCreated), and is being disposed of by .NET (ObjectDisposing).

In the next chapter, you'll look at all the various DataSource-aware controls supplied by .NET, how they work, and how to customize them to your specifications.

Show: