- •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 9: ADO.NET
ServerName, InstanceName, DatabaseName, Username, and Password with the appropriate details for your server.
Visual Basic |
File: AccessingData.aspx (excerpt) |
' Define database connection Dim conn As New SqlConnection(
"Server=ServerName\InstanceName;" & _ "Database=DatabaseName;User ID=Username;" & _ "Password=Password")
C# |
File: AccessingData.aspx (excerpt) |
// Define database connection SqlConnection conn = new SqlConnection(
"Server=ServerName\\InstanceName;" + "Database=DatabaseName;User ID=Username;" + "Password=Password");
Reading the Data
Okay, so you’ve opened the connection and executed the command. Let’s do something with the returned data!
A good task for us to start with is to display the list of employees we read from the database. To do this, we’ll simply use a While loop to add the data to a Label control that we’ll place in the form. Start by adding a Label named employeesLabel to the AccessingData.aspx web form. We’ll also change the title of the page to “Using ADO.NET.”
File: AccessingData.aspx (excerpt)
<html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">
<title>Using ADO.NET</title> </head>
<body>
<form id="form1" runat="server"> <div>
<asp:Label ID="employeesLabel" runat="server" />
</div>
</form>
</body>
</html>
342
Reading the Data
Now, let’s use the SqlDataReader’s Read method to loop through the data items held in the reader; we’ll display them by adding their text to the employeesLabel object as we go.
Visual Basic |
File: AccessingData.aspx (excerpt) |
'Open connection conn.Open()
'Execute the command
Dim reader As SqlDataReader = comm.ExecuteReader()
'Read and display the data While reader.Read()
employeesLabel.Text &= reader.Item("Name") & "<br />" End While
'Close the reader and the connection
reader.Close()
conn.Close()
C# |
File: AccessingData.aspx (excerpt) |
//Open connection conn.Open();
//Execute the command
SqlDataReader reader = comm.ExecuteReader();
//Read and display the data while(reader.Read())
{
employeesLabel.Text += reader["Name"] + "<br />";
}
//Close the reader and the connection reader.Close();
conn.Close();
Figure 9.4. Displaying the list of employees
343
Chapter 9: ADO.NET
We already know that the SqlDataReader class reads the data row by row, in a forward-only fashion. Only one row can be read at any moment. When we call reader.Read, our SqlDataReader reads the next row of data from the database. If there’s data to be read, it returns True; otherwise—if we’ve already read the last record returned by the query—the Read method returns False. If we view this page in the browser, we’ll see something like Figure 9.4.
Using Parameters with Queries
What if the user doesn’t want to view information for all employees, but instead, wants to see details for one specific employee?
To get this information from our Employees table, we’d run the following query, replacing EmployeeID with the ID of the employee in which the user was interested.
SELECT EmployeeID, Name, Username, Password
FROM Employees
WHERE EmployeeID = EmployeeID
Let’s build a page like the one shown in Figure 9.5 to display this information.
Figure 9.5. Retrieving details of a specific employee
Create a new web form called QueryParameters.aspx and alter it to reflect the code shown here:
File: QueryParameters.aspx (excerpt)
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
344
Using Parameters with Queries
<script runat="server"> </script>
<html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">
<title>Using Query Parameters</title> </head>
<body>
<form id="form1" runat="server"> <div>
User ID:
<asp:TextBox ID="idTextBox" runat="server" /> <asp:Button ID="submitButton" runat="server"
Text="Get Data" /><br />
<asp:Label ID="userLabel" runat="server" />
</div>
</form>
</body>
</html>
With these amendments, we’ve added a Textbox control into which users can type in the ID of the employee whose information they want to see. We’ve also added a Button that will be used to submit the form and retrieve the data.
Next, we need to add a Click event handler to the Button control. When this button is clicked, our web form will need to execute the following tasks:
1.Read the ID typed by the user in the idTextBox control.
2.Prepare an SQL query to retrieve data about the specified employee.
3.Execute the query and read the results.
Now, we could perform this query using the following code:
comm = New SqlCommand( _
"SELECT EmployeeID, Name, Username, Password " & _
"FROM Employees WHERE EmployeeID = " & idTextBox.Text , conn)
If the user entered the number 5 into the text box and clicked the button, the following query would be run:
SELECT EmplyeeID, Name, Username, Password
FROM Employees
WHERE EmployeeID = 5
345
Chapter 9: ADO.NET
The database would run this query without complaint, and your program would execute as expected. However, if—as is perhaps more likely—the user entered an employee’s name, your application would attempt to run the following query:
SELECT EmployeeID, Name, Username, Password
FROM Employees
WHERE EmployeeID = Zac Ruvalcaba
This query would cause an error in the database, which would, in turn, cause an exception in your web form. As a safeguard against this eventuality, ADO.NET allows you to define parameters in your query, and to give each of those parameters a type. Inserting parameters into your query is a pretty simple task:
comm = New SqlCommand( _
"SELECT EmployeeID, Name, Username, Password " & _ "FROM Employees WHERE EmployeeID = @EmployeeID", conn)
We’ve added a placeholder for our parameter to the query above. To do so, we add the @ symbol, followed by an identifier for our parameter (in this case, we’ve used EmployeeID). Next, we need to add this parameter to the SqlCommand object, and give it a value:
Visual Basic
comm.Parameters.Add("@EmployeeID", System.Data.SqlDbType.Int) comm.Parameters("@EmployeeID").Value = idTextBox.Text
C#
comm.Parameters.Add("@EmployeeID", System.Data.SqlDbType.Int); comm.Parameters["@EmployeeID"].Value = idTextBox.Text
Here, we call the Add method of conn.Parameters, passing in the name of the parameter (EmployeeID) and the parameter’s type; we’ve told ADO.NET that we’re expecting an int to be passed to the database, but we could specify any of the SQL Server data types here.
One of the most common SQL Server data types is nvarchar. If your query involved an nvarchar parameter named @Username, for example, you could set its value with the following code:
Visual Basic
comm.Parameters.Add("@Username", Data.SqlDbType.NVarChar, 50) comm.Parameters("@Username").Value = username
346
Using Parameters with Queries
C#
comm.Parameters.Add("@Username", SqlDbType.NVarChar, 50); comm.Parameters["@Username"].Value = username;
Notice that we’ve included an additional parameter in our call to the Add method. This optional parameter tells the SqlCommand object the maximum allowable size of the nvarchar field in the database. We’ve given the Username field in our Employees table a maximum size of 50 characters, so our code should reflect this.
For a list of all the types you can use when calling conn.Parameters.Add, see the entry on System.Data.SqlDbType Enumeration in the .NET Framework’s SDK Documentation.
Let’s put parameters into action in QueryParameters.aspx. First, create a Click event handler for the Button control by double-clicking it in Design View. Next, fill the event handler with the code shown below:
Visual Basic File: QueryParameters.aspx (excerpt)
Protected Sub submitButton_Click(ByVal sender As Object, _ ByVal e As System.EventArgs)
' Define data objects
Dim conn As SqlConnection Dim comm As SqlCommand
Dim reader As SqlDataReader ' Initialize connection
conn = New SqlConnection("Server=localhost\SqlExpress;" & _ "Database=Dorknozzle;Integrated Security=True")
' Create command
comm = New SqlCommand( _
"SELECT EmployeeID, Name, Username, Password " & _ "FROM Employees WHERE EmployeeID=@EmployeeID", conn)
' Verify if the ID entered by the visitor is numeric Dim employeeID As Integer
If (Not Integer.TryParse(idTextBox.Text, employeeID)) Then ' If the user didn't enter numeric ID...
userLabel.Text = "Please enter a numeric ID!" Else
' Add parameter
comm.Parameters.Add("@EmployeeID", System.Data.SqlDbType.Int) comm.Parameters("@EmployeeID").Value = employeeID
'Open the connection conn.Open()
'Execute the command
reader = comm.ExecuteReader() ' Display the requested data
347
Chapter 9: ADO.NET
If reader.Read() Then
userLabel.Text = "Employee ID: " & _ reader.Item("EmployeeID") & "<br />" & _ "Name: " & reader.Item("Name") & "<br />" & _
"Username: " & reader.Item("Username") & "<br />" & _ "Password: " & reader.Item("Password")
Else
userLabel.Text = _
"There is no user with this ID: " & employeeID
End If |
|
' Close the reader and the connection |
|
reader.Close() |
|
conn.Close() |
|
End If |
|
End Sub |
|
C# |
File: QueryParameters.aspx (excerpt) |
protected void submitButton_Click(object sender, EventArgs e)
{
//Declare objects SqlConnection conn; SqlCommand comm; SqlDataReader reader;
//Initialize connection
conn = new SqlConnection("Server=localhost\\SqlExpress;" + "Database=Dorknozzle;Integrated Security=True");
// Create command
comm = new SqlCommand(
"SELECT EmployeeID, Name, Username, Password " + "FROM Employees WHERE EmployeeID=@EmployeeID", conn);
// Verify if the ID entered by the visitor is numeric int employeeID;
if (!int.TryParse(idTextBox.Text, out employeeID))
{
// If the user didn't enter numeric ID...
userLabel.Text = "Please enter a numeric ID!";
}
else
{
// Add parameter
comm.Parameters.Add("@EmployeeID", System.Data.SqlDbType.Int); comm.Parameters["@EmployeeID"].Value = employeeID;
//Open the connection conn.Open();
//Execute the command
reader = comm.ExecuteReader();
348
Using Parameters with Queries
//Display the requested data if (reader.Read())
{
userLabel.Text = "Employee ID: " + reader["EmployeeID"] + "<br />" + "Name: " + reader["Name"] + "<br />" +
"Username: " + reader["Username"] + "<br />" + "Password: " + reader["Password"];
}
else
{
userLabel.Text =
"There is no user with this ID: " + employeeID;
}
//Close the reader and the connection reader.Close();
conn.Close();
}
}
Now, when the user clicks the button, the Click event is raised, and the event handler is executed. In that method, we grab the Employee ID from the Text property of the TextBox control, and check that it’s a valid integer. This check can be done with the Integer.TryParse method in VB, or the int.TryParse method in C#:
Visual Basic File: QueryParameters.aspx (excerpt)
' Verify if the ID entered by the visitor is numeric Dim employeeID As Integer
If (Not Integer.TryParse(idTextBox.Text, employeeID)) Then
|
|
C# |
File: QueryParameters.aspx (excerpt) |
// Verify if the ID entered by the visitor is numeric int employeeID;
if (!int.TryParse(idTextBox.Text, out employeeID))
{
This method verifies whether or not the string we pass as the first parameter can be cast to an integer, and if yes, the integer is returned through the second parameter. Note that in C#, this is an out parameter. Out parameters are parameters that are used to retrieve data from a function, rather than send data to that function. Out parameters are similar to return values, except that more than one
349
Chapter 9: ADO.NET
of them can exist for any method. The return value of TryParse is a Boolean value that specifies whether or not the value could be properly converted.
If the ID that’s entered isn’t a valid number, we notify the user, as Figure 9.6 illustrates.
Figure 9.6. Invalid input data generating a warning
We also notify the user if the query doesn’t return any results. This feature is simple to implement, because reader.Read only returns True if the query returns a record.
Visual Basic File: QueryParameters.aspx (excerpt)
' Display the requested data
If reader.Read() |
Then |
userLabel.Text |
= "Employee ID: " & reader.Item("EmployeeID") & _ |
|
|
|
|
C# |
File: QueryParameters.aspx (excerpt) |
// Display the requested data if (reader.Read())
{
userLabel.Text = "Employee ID: " + reader["EmployeeID"] +
Figure 9.7 shows the message you’ll see if you enter an ID that doesn’t exist in the database.
There are still a couple of details that we could improve in this system. For example, if an error occurs in the code, the connection will never be closed. Let’s look at this problem next.
350