- •Table of Contents
- •Preface
- •What is ASP.NET?
- •Installing the Required Software
- •Installing the Web Server
- •Installing Internet Information Services (IIS)
- •Installing Cassini
- •Installing the .NET Framework and the SDK
- •Installing the .NET Framework
- •Installing the SDK
- •Configuring the Web Server
- •Configuring IIS
- •Configuring Cassini
- •Where do I Put my Files?
- •Using localhost
- •Virtual Directories
- •Using Cassini
- •Installing SQL Server 2005 Express Edition
- •Installing SQL Server Management Studio Express
- •Installing Visual Web Developer 2005
- •Writing your First ASP.NET Page
- •Getting Help
- •Summary
- •ASP.NET Basics
- •ASP.NET Page Structure
- •Directives
- •Code Declaration Blocks
- •Comments in VB and C# Code
- •Code Render Blocks
- •ASP.NET Server Controls
- •Server-side Comments
- •Literal Text and HTML Tags
- •View State
- •Working with Directives
- •ASP.NET Languages
- •Visual Basic
- •Summary
- •VB and C# Programming Basics
- •Programming Basics
- •Control Events and Subroutines
- •Page Events
- •Variables and Variable Declaration
- •Arrays
- •Functions
- •Operators
- •Breaking Long Lines of Code
- •Conditional Logic
- •Loops
- •Object Oriented Programming Concepts
- •Objects and Classes
- •Properties
- •Methods
- •Classes
- •Constructors
- •Scope
- •Events
- •Understanding Inheritance
- •Objects In .NET
- •Namespaces
- •Using Code-behind Files
- •Summary
- •Constructing ASP.NET Web Pages
- •Web Forms
- •HTML Server Controls
- •Using the HTML Server Controls
- •Web Server Controls
- •Standard Web Server Controls
- •Label
- •Literal
- •TextBox
- •HiddenField
- •Button
- •ImageButton
- •LinkButton
- •HyperLink
- •CheckBox
- •RadioButton
- •Image
- •ImageMap
- •PlaceHolder
- •Panel
- •List Controls
- •DropDownList
- •ListBox
- •RadioButtonList
- •CheckBoxList
- •BulletedList
- •Advanced Controls
- •Calendar
- •AdRotator
- •TreeView
- •SiteMapPath
- •Menu
- •MultiView
- •Wizard
- •FileUpload
- •Web User Controls
- •Creating a Web User Control
- •Using the Web User Control
- •Master Pages
- •Using Cascading Style Sheets (CSS)
- •Types of Styles and Style Sheets
- •Style Properties
- •The CssClass Property
- •Summary
- •Building Web Applications
- •Introducing the Dorknozzle Project
- •Using Visual Web Developer
- •Meeting the Features
- •The Solution Explorer
- •The Web Forms Designer
- •The Code Editor
- •IntelliSense
- •The Toolbox
- •The Properties Window
- •Executing your Project
- •Using Visual Web Developer’s Built-in Web Server
- •Using IIS
- •Using IIS with Visual Web Developer
- •Core Web Application Features
- •Web.config
- •Global.asax
- •Using Application State
- •Working with User Sessions
- •Using the Cache Object
- •Using Cookies
- •Starting the Dorknozzle Project
- •Preparing the Sitemap
- •Using Themes, Skins, and Styles
- •Creating a New Theme Folder
- •Creating a New Style Sheet
- •Styling Web Server Controls
- •Adding a Skin
- •Applying the Theme
- •Building the Master Page
- •Using the Master Page
- •Extending Dorknozzle
- •Debugging and Error Handling
- •Debugging with Visual Web Developer
- •Other Kinds of Errors
- •Custom Errors
- •Handling Exceptions Locally
- •Summary
- •Using the Validation Controls
- •Enforcing Validation on the Server
- •Using Validation Controls
- •RequiredFieldValidator
- •CompareValidator
- •RangeValidator
- •ValidationSummary
- •RegularExpressionValidator
- •Some Useful Regular Expressions
- •CustomValidator
- •Validation Groups
- •Updating Dorknozzle
- •Summary
- •What is a Database?
- •Creating your First Database
- •Creating a New Database Using Visual Web Developer
- •Creating Database Tables
- •Data Types
- •Column Properties
- •Primary Keys
- •Creating the Employees Table
- •Creating the Remaining Tables
- •Executing SQL Scripts
- •Populating the Data Tables
- •Relational Database Design Concepts
- •Foreign Keys
- •Using Database Diagrams
- •Diagrams and Table Relationships
- •One-to-one Relationships
- •One-to-many Relationships
- •Many-to-many Relationships
- •Summary
- •Speaking SQL
- •Reading Data from a Single Table
- •Using the SELECT Statement
- •Selecting Certain Fields
- •Selecting Unique Data with DISTINCT
- •Row Filtering with WHERE
- •Selecting Ranges of Values with BETWEEN
- •Matching Patterns with LIKE
- •Using the IN Operator
- •Sorting Results Using ORDER BY
- •Limiting the Number of Results with TOP
- •Reading Data from Multiple Tables
- •Subqueries
- •Table Joins
- •Expressions and Operators
- •Transact-SQL Functions
- •Arithmetic Functions
- •String Functions
- •Date and Time Functions
- •Working with Groups of Values
- •The COUNT Function
- •Grouping Records Using GROUP BY
- •Filtering Groups Using HAVING
- •The SUM, AVG, MIN, and MAX Functions
- •Updating Existing Data
- •The INSERT Statement
- •The UPDATE Statement
- •The DELETE Statement
- •Stored Procedures
- •Summary
- •Introducing ADO.NET
- •Importing the SqlClient Namespace
- •Defining the Database Connection
- •Preparing the Command
- •Executing the Command
- •Setting up Database Authentication
- •Reading the Data
- •Using Parameters with Queries
- •Bulletproofing Data Access Code
- •Using the Repeater Control
- •More Data Binding
- •Inserting Records
- •Updating Records
- •Deleting Records
- •Using Stored Procedures
- •Summary
- •DataList Basics
- •Handling DataList Events
- •Editing DataList Items and Using Templates
- •DataList and Visual Web Developer
- •Styling the DataList
- •Summary
- •Using the GridView Control
- •Customizing the GridView Columns
- •Styling the GridView with Templates, Skins, and CSS
- •Selecting Grid Records
- •Using the DetailsView Control
- •Styling the DetailsView
- •GridView and DetailsView Events
- •Entering Edit Mode
- •Using Templates
- •Updating DetailsView Records
- •Summary
- •Advanced Data Access
- •Using Data Source Controls
- •Binding the GridView to a SqlDataSource
- •Binding the DetailsView to a SqlDataSource
- •Displaying Lists in DetailsView
- •More on SqlDataSource
- •Working with Data Sets and Data Tables
- •What is a Data Set Made From?
- •Binding DataSets to Controls
- •Implementing Paging
- •Storing Data Sets in View State
- •Implementing Sorting
- •Filtering Data
- •Updating a Database from a Modified DataSet
- •Summary
- •Security and User Authentication
- •Basic Security Guidelines
- •Securing ASP.NET 2.0 Applications
- •Working with Forms Authentication
- •Authenticating Users
- •Working with Hard-coded User Accounts
- •Configuring Forms Authentication
- •Configuring Forms Authorization
- •Storing Users in Web.config
- •Hashing Passwords
- •Logging Users Out
- •ASP.NET 2.0 Memberships and Roles
- •Creating the Membership Data Structures
- •Using your Database to Store Membership Data
- •Using the ASP.NET Web Site Configuration Tool
- •Creating Users and Roles
- •Changing Password Strength Requirements
- •Securing your Web Application
- •Using the ASP.NET Login Controls
- •Authenticating Users
- •Customizing User Display
- •Summary
- •Working with Files and Email
- •Writing and Reading Text Files
- •Setting Up Security
- •Writing Content to a Text File
- •Reading Content from a Text File
- •Accessing Directories and Directory Information
- •Working with Directory and File Paths
- •Uploading Files
- •Sending Email with ASP.NET
- •Configuring the SMTP Server
- •Sending a Test Email
- •Creating the Company Newsletter Page
- •Summary
- •The WebControl Class
- •Properties
- •Methods
- •Standard Web Controls
- •AdRotator
- •Properties
- •Events
- •BulletedList
- •Properties
- •Events
- •Button
- •Properties
- •Events
- •Calendar
- •Properties
- •Events
- •CheckBox
- •Properties
- •Events
- •CheckBoxList
- •Properties
- •Events
- •DropDownList
- •Properties
- •Events
- •FileUpload
- •Properties
- •Methods
- •HiddenField
- •Properties
- •HyperLink
- •Properties
- •Image
- •Properties
- •ImageButton
- •Properties
- •Events
- •ImageMap
- •Properties
- •Events
- •Label
- •Properties
- •LinkButton
- •Properties
- •Events
- •ListBox
- •Properties
- •Events
- •Literal
- •Properties
- •MultiView
- •Properties
- •Methods
- •Events
- •Panel
- •Properties
- •PlaceHolder
- •Properties
- •RadioButton
- •Properties
- •Events
- •RadioButtonList
- •Properties
- •Events
- •TextBox
- •Properties
- •Events
- •Properties
- •Validation Controls
- •CompareValidator
- •Properties
- •Methods
- •CustomValidator
- •Methods
- •Events
- •RangeValidator
- •Properties
- •Methods
- •RegularExpressionValidator
- •Properties
- •Methods
- •RequiredFieldValidator
- •Properties
- •Methods
- •ValidationSummary
- •Properties
- •Navigation Web Controls
- •SiteMapPath
- •Properties
- •Methods
- •Events
- •Menu
- •Properties
- •Methods
- •Events
- •TreeView
- •Properties
- •Methods
- •Events
- •HTML Server Controls
- •HtmlAnchor Control
- •Properties
- •Events
- •HtmlButton Control
- •Properties
- •Events
- •HtmlForm Control
- •Properties
- •HtmlGeneric Control
- •Properties
- •HtmlImage Control
- •Properties
- •HtmlInputButton Control
- •Properties
- •Events
- •HtmlInputCheckBox Control
- •Properties
- •Events
- •HtmlInputFile Control
- •Properties
- •HtmlInputHidden Control
- •Properties
- •HtmlInputImage Control
- •Properties
- •Events
- •HtmlInputRadioButton Control
- •Properties
- •Events
- •HtmlInputText Control
- •Properties
- •Events
- •HtmlSelect Control
- •Properties
- •Events
- •HtmlTable Control
- •Properties
- •HtmlTableCell Control
- •Properties
- •HtmlTableRow Control
- •Properties
- •HtmlTextArea Control
- •Properties
- •Events
- •Index
Chapter 12: Advanced Data Access
However, SqlDataReader isn’t the only means of getting to your data, and in many scenarios, it makes sense to use one of the two popular alternatives:
1.The first alternative involves using the new ADO.NET data source controls, which are tightly integrated with the GridView and DetailsView controls, and allow you to implement reading, updating, deleting, inserting, paging, and sorting features very easily—for the most part, you don’t even need to write any code!
2.The second alternative involves using the SqlDataAdapter class in conjunction with the DataTable, DataView, and DataSet classes, which are able to read data from the database and store it locally, allowing you to browse, filter, and sort data in your code without leaving a connection to the database open. This method occupies more memory on the server that runs your application, and means that fewer of Visual Web Developer’s automated features are available to you (you have to write more code!), but it does give you more flexibility in terms of what you can do with your data.
In this chapter, you’ll learn how to use both of these alternate data access methods.
Using Data Source Controls
The .NET framework offers five data source controls: SqlDataSource,
AccessDataSource, ObjectDataSource, XmlDataSource, and SiteMapDataSource. These objects enable automatic connection to various data sources, and provide easy ways to read or modify your database using data-bound controls.
SqlDataSource allows you to connect to any data source that has an ADO.NET data provider. The default providers that ship with .NET 2.0 are
SqlClient, OracleClient, OleDb, and Odbc. Even though the name of the class is SqlDataSource, the fact that its name begins with Sql doesn’t meant it works only with SQL Server—it’s really very flexible. This is the data source we’ll work with in this chapter.
AccessDataSource is the data source object we use to connect to Access databases.
ObjectDataSource allows us to connect to custom data access classes, and is appropriate when we’re creating complex application architectures.
XmlDataSource knows how to connect to XML files.
470
Using Data Source Controls
SiteMapDataSource knows how to connect to a sitemap data source, and can be used to generate sitemaps. We worked a little with this data source control in Chapter 4.
You can find these controls in the Data tab of Visual Web Developer’s Toolbox, as Figure 12.1 shows.
Figure 12.1. The Data Source Controls in the Toolbox
In Chapter 11, we implemented the functionality required to show employee details in the Dorknozzle address book page (AddressBook.aspx). We used the
SqlDataReader class for the task, which means we’ve achieved the best possible performance. However, we wrote quite a bit of code to implement the viewing and editing features for that page, and we’d need to do even more hand coding to implement paging, sorting, and inserting features.
This time, to make it easier on our fingers, we’ll use the SqlDataSource object instead. This object can automate many tasks for us, and while it may not always provide the best performance or the greatest flexibility, it’s important that we know how to use it, because it can come in very handy for quick programming tasks.
471
Chapter 12: Advanced Data Access
Binding the GridView to a SqlDataSource
We’ll start by binding the GridView control in AddressBook.aspx to a SqlDataSource; we’ll deal with the DetailsView control later. Since the data sources work with different SQL queries, we’ll need to create two data sources: one that reads all employees (to populate the GridView), and one that reads the details of one employee (to populate the DetailsView).
Let’s start by deleting all the code in the code-behind file (AddressBooks.aspx.vb or AddressBook.aspx.cs). Yes, you’ve read this correctly: we’re starting from scratch! As you’ll see, we can implement a large number of features without any code at all when using the SqlDataSource class. Leave your code-behind files like this:
Visual Basic |
File: AddressBook.aspx.vb (excerpt) |
Imports System.Data.SqlClient
Partial Class AddressBook
Inherits System.Web.UI.Page
End Class
C# File: AddressBook.aspx.cs (excerpt)
using System; using System.Data;
using System.Configuration; using System.Collections; using System.Web;
using System.Web.Security; using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class AddressBook : System.Web.UI.Page
{
}
If you’re using C#, you’ll also need to delete the event handler declarations from
AddressBook.aspx. Remove the OnSelectedIndexChanged property from the GridView control, and the OnModeChanging and OnItemUpdating properties from the DetailsView control.
Open AddressBook.aspx in Design View, and drag the SqlDataSource control from the Toolbox (it’s located under the Data tab) onto the form. You can place
472
Binding the GridView to a SqlDataSource
it anywhere you like—the location isn’t relevant because the control doesn’t display in the browser. Of course, it will appear in the Design View, as Figure 12.2 shows.
Figure 12.2. AddressBook.aspx with an SqlDataSource control
Rename the object employeesDataSource. In Source View, the code for the new control should look like this:
File: AddressBook.aspx (excerpt)
<asp:SqlDataSource id="employeesDataSource" runat="server"> </asp:SqlDataSource>
Switch back to Design View, click the SqlDataSource control’s smart tag, and select Configure Data Source. A dialog will appear, giving us the opportunity to provide the details of the data source. In the first page of the dialog, we specify the data connection we want to use. If we hadn’t already added the Dorknozzle connection string to the Web.config file, we could have clicked the New Connection… button, and used the wizard to add a connection string to Web.config.
473
Chapter 12: Advanced Data Access
However, as we’ve already set up the connection string, we simply choose it from the drop-down list, as shown in Figure 12.3.
Figure 12.3. Specifying the connection string
After we’ve selected the Dorknozzle connection, we click Next. This is where the fun begins!
In the next screen, we can specify the database table and the columns that we want our data source object to handle. Select the Employees table, and check the following columns: EmployeeID, Name, City, and MobilePhone, as depicted in Figure 12.4.
Click the ORDER BY… button and select the Name column (or any other column by which you want to sort your employees), as illustrated in Figure 12.5.
474
Binding the GridView to a SqlDataSource
Figure 12.4. Choosing columns
Figure 12.5. Specifying an ORDER BY clause
475
Chapter 12: Advanced Data Access
Figure 12.6. Testing the data source
Click OK, then Next. In the dialog that appears, press the Test Query button to test that the query will work with this data source. If everything worked well, you should be shown a list of employees similar to the one depicted in Figure 12.6.
Finally, click Finish.
Before we move on, let’s take a look at the new code we’ve added to AddressBook.aspx. If you switch to Source View, you’ll see that quite a bit of code has been created for you. Let’s look at the SqlDataSource object first:
File: AddressBook.aspx (excerpt)
<asp:SqlDataSource id="employeesDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:Dorknozzle %>" SelectCommand="SELECT [EmployeeID], [Name], [City],
[MobilePhone] FROM [Employees] ORDER BY [Name]"> </asp:SqlDataSource>
This object is amazing in its simplicity, yet the GridView can connect to it and display the required data with very little additional effort. Let’s use this
SqlDataSource object to populate the GridView.
476
Binding the GridView to a SqlDataSource
In AddressBook.aspx, use either Source View or the Properties window in Design View to set the following properties of the GridView control:
Table 12.1. Properties to set for the GridView control
Property |
Value |
DataSourceID |
employeesDataSource |
DataKeyNames |
EmployeeID |
AllowPaging |
True |
AllowSorting |
True |
PageSize |
3 |
Don’t Overwrite the Columns!
If you set the DataSourceID property in Design View, Visual Web Developer will ask if you’d like to clear the column data and replace it with that from the data source, as Figure 12.7 illustrates. Make sure you choose No, because we’re happy with the columns we decided to display when creating the grid in Chapter 11.
Figure 12.7. We’re not refreshing the GridView fields
PageSize specifies the number of records the GridView should display on every page of products. Normally, we’d want this number to be greater than three, but we’ve set it to a low number here so that we can test the paging functionality. AllowPaging enables GridView’s paging functionality, which will cause (working) paging links to be displayed. When we set AllowSorting to True, the column names become links that users can click to sort the data on the basis of that field.
Let’s also deal with style issues by adding the line below to the skin file, SkinFile.skin. The PagerStyle defines the style used by the cells that contain the paging buttons; we’ll see these buttons in a moment.
477
Chapter 12: Advanced Data Access
File: SkinFile.skin (excerpt)
<asp:GridView runat="server" CssClass="GridMain" CellPadding="4" GridLines="None">
<RowStyle CssClass="GridRow" /> <SelectedRowStyle CssClass="GridSelectedRow" /> <HeaderStyle CssClass="GridHeader" />
<PagerStyle CssClass="GridRow" />
</asp:GridView>
<asp:DetailsView runat="server" CssClass="GridMain" CellPadding="4" GridLines="None">
<RowStyle CssClass="GridRow" /> <HeaderStyle CssClass="GridHeader" />
<PagerStyle CssClass="GridRow" />
</asp:DetailsView>
Execute the project. If everything goes well, you should see a functional GridView, with working paging buttons, like the one in Figure 12.8.
Figure 12.8. Address Book paging in action
Yes, paging works, and you didn’t write a single line of C# or VB code to implement it! You can even select rows—although when you do, nothing happens, because we haven’t implemented any functionality for the DetailsView control as yet! (Remember that in the beginning of the chapter, we deleted all the code.)
478