- •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
Inserting Records
a DropDownList, because the database operations are always carried out using the items’ IDs.
The DataTextField property of the DropDownList needs to be set to the name of the column that provides the text to be displayed, and the DataValueField must be set to the name of the column that contains the ID. This allows us to pass the ID of the category or subject along to any part of the application when a user makes a selection from the drop-down lists.
When the page loads, all the categories and subjects will be loaded into their respective DropDownList controls, as shown in Figure 9.10.
Inserting Records
The code that inserts records from your application into a database isn’t too different from what we’ve already seen. The main difference is that now we need to retrieve data from the user input controls in the page, and use this data as the parameters to our INSERT query, rather than simply firing off a simple SELECT query. As we discussed earlier in this chapter, to execute such a query, you’d need to use the ExecuteNonQuery method of the SqlCommand object, as INSERT queries don’t return results.
When inserting user-entered data into the database, you need to be extra careful about validating that data in case the users don’t type whatever you expect them to (those users always seem to find unimaginable ways to do things!).
A typical INSERT statement is coded as follows:
Visual Basic
comm = New SqlCommand( _
"INSERT INTO HelpDesk (Field1, Field2, …) " & _ "VALUES (@Parameter1, @Parameter2, …)", conn)
Once the SqlCommand object has been created with a parameterized INSERT query, we simply pass in the necessary parameters, similarly to the process we followed for SELECT queries:
Visual Basic
comm.Parameters.Add("@Parameter1", System.Data.SqlDbType.Type1) comm.Parameters("@Parameter1").Value = value1 comm.Parameters.Add("@Parameter2", System.Data.SqlDbType.Type2) comm.Parameters("@Parameter2").Value = value2
371
Chapter 9: ADO.NET
Keep in mind that in C#, the syntax for accessing the parameters collection is slightly different:
C#
comm.Parameters.Add("@Parameter1", System.Data.SqlDbType.Type1); comm.Parameters["@Parameter1"].Value = value1; comm.Parameters.Add("@Parameter2", System.Data.SqlDbType.Type2); comm.Parameters["@Parameter2"].Value = value2;
To demonstrate the process of inserting records into the database, let’s finish the help desk page.
When employees visit the help desk page, they’ll fill out the necessary information, click Submit Request, and the information will be saved within the HelpDesk table. The HelpDesk table acts as a queue for IT personnel to review and respond to reported issues.
First, open HelpDesk.aspx, and add a label just below the page’s heading.
File: HelpDesk.aspx (excerpt)
<h1>Employee Help Desk Request</h1>
<asp:Label ID="dbErrorMessage" ForeColor="Red" runat="server" />
<p>
Station Number:<br />
<asp:TextBox id="stationTextBox" runat="server" CssClass="textbox" />
The form already contains numerous validation controls that display error messages if they find problems with the entered data. We’re adding this Label control to display errors that arise when an exception is caught while the database query is executing. This is necessary because, although the validation controls prevent most of the errors that could occur, they can’t guarantee that the database query will run flawlessly. For example, if the database server is rebooted, and we try to run a database query, we’ll receive an error until the database is up and running again. There could be other kinds of errors, too. An example of an error message is shown in Figure 9.11.
You already have a Click event handler for the Submit Request button in Help- Desk.aspx—we added it in Chapter 6, when we added validation controls to the page. The event handler should look like this:
Visual Basic |
File: HelpDesk.aspx.vb (excerpt) |
Protected Sub submitButton_Click(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles submitButton.Click
372
Inserting Records
Figure 9.11. Displaying an error message in the catch block
If Page.IsValid Then
' |
Code that uses the data entered by the user |
End |
If |
End Sub |
|
|
|
C# |
File: HelpDesk.aspx.cs (excerpt) |
protected void submitButton_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
// Code that uses the data entered by the user
}
}
373
Chapter 9: ADO.NET
Modify this method by adding code that inserts the user-submitted help desk request into the database, as shown below:
Visual Basic |
File: HelpDesk.aspx.vb (excerpt) |
Protected Sub submitButton_Click(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles submitButton.Click
If Page.IsValid Then
'Define data objects Dim conn As SqlConnection Dim comm As SqlCommand
'Read the connection string from Web.config Dim connectionString As String = _
ConfigurationManager.ConnectionStrings( _ "Dorknozzle").ConnectionString
'Initialize connection
conn = New SqlConnection(connectionString) ' Create command
comm = New SqlCommand( _
"INSERT INTO HelpDesk (EmployeeID, StationNumber, " & _ "CategoryID, SubjectID, Description, StatusID) " & _ "VALUES (@EmployeeID, @StationNumber, @CategoryID, " & _ "@SubjectID, @Description, @StatusID)", conn)
' Add command parameters
comm.Parameters.Add("@EmployeeID", System.Data.SqlDbType.Int) comm.Parameters("@EmployeeID").Value = 5 comm.Parameters.Add("@StationNumber", _
System.Data.SqlDbType.Int) comm.Parameters("@StationNumber").Value = stationTextBox.Text comm.Parameters.Add("@CategoryID", System.Data.SqlDbType.Int) comm.Parameters("@CategoryID").Value = _
categoryList.SelectedItem.Value comm.Parameters.Add("@SubjectID", System.Data.SqlDbType.Int) comm.Parameters("@SubjectID").Value = _
subjectList.SelectedItem.Value comm.Parameters.Add("@Description", _
System.Data.SqlDbType.NVarChar, 50) comm.Parameters("@Description").Value = _
descriptionTextBox.Text comm.Parameters.Add("@StatusID", System.Data.SqlDbType.Int) comm.Parameters("@StatusID").Value = 1
' Enclose database code in Try-Catch-Finally Try
'Open the connection conn.Open()
'Execute the command comm.ExecuteNonQuery()
374
Inserting Records
' Reload page if the query executed successfully Response.Redirect("HelpDesk.aspx")
Catch
' Display error message dbErrorMessage.Text = _
"Error submitting the help desk request! Please " & _ "try again later, and/or change the entered data!"
Finally
' Close the connection |
|
conn.Close() |
|
End Try |
|
End If |
|
End Sub |
|
C# |
File: HelpDesk.aspx.cs (excerpt) |
protected void submitButton_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
//Define data objects SqlConnection conn; SqlCommand comm;
//Read the connection string from Web.config string connectionString =
ConfigurationManager.ConnectionStrings[
"Dorknozzle"].ConnectionString;
//Initialize connection
conn = new SqlConnection(connectionString); // Create command
comm = new SqlCommand(
"INSERT INTO HelpDesk (EmployeeID, StationNumber, " + "CategoryID, SubjectID, Description, StatusID) " + "VALUES (@EmployeeID, @StationNumber, @CategoryID, " + "@SubjectID, @Description, @StatusID)", conn);
// Add command parameters
comm.Parameters.Add("@EmployeeID", System.Data.SqlDbType.Int); comm.Parameters["@EmployeeID"].Value = 5; comm.Parameters.Add("@StationNumber",
System.Data.SqlDbType.Int); comm.Parameters["@StationNumber"].Value = stationTextBox.Text; comm.Parameters.Add("@CategoryID", System.Data.SqlDbType.Int); comm.Parameters["@CategoryID"].Value =
categoryList.SelectedItem.Value; comm.Parameters.Add("@SubjectID", System.Data.SqlDbType.Int); comm.Parameters["@SubjectID"].Value =
subjectList.SelectedItem.Value;
375
Chapter 9: ADO.NET
comm.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar, 50);
comm.Parameters["@Description"].Value = descriptionTextBox.Text;
comm.Parameters.Add("@StatusID", System.Data.SqlDbType.Int); comm.Parameters["@StatusID"].Value = 1;
// Enclose database code in Try-Catch-Finally try
{
//Open the connection conn.Open();
//Execute the command comm.ExecuteNonQuery();
//Reload page if the query executed successfully Response.Redirect("HelpDesk.aspx");
}
catch
{
// Display error message dbErrorMessage.Text =
"Error submitting the help desk request! Please " + "try again later, and/or change the entered data!";
}
finally
{
// Close the connection conn.Close();
}
}
}
Make Sure you’ve Set the Identity Property!
Note that when we’re inserting a new record into the HelpDesk table, we rely on the ID column, RequestID, to be generated automatically for us by the database. If we forget to set RequestID as an identity column, we’ll receive an exception every time we try to add a new help desk request!
Did you notice the use of the ExecuteNonQuery method? As you know, we use this method when we’re executing any SQL query that doesn’t return a set of results, such as INSERT, UPDATE, and DELETE queries.
You’ll remember that, in order to make the example simpler, we hard-coded the EmployeeID (to the value of 5), and the Status (to the value of 1). To make the application complete, you could add another drop-down list from which employees
376
Inserting Records
Figure 9.12. Submitting the Help Desk Request form
could select their names, and take the IDs from there. For now, just make sure that the Employees table has a record with an EmployeeID of 5, otherwise the query won’t execute successfully.
The other potentially unfamiliar part of this code is the final line of the Try block, which uses Response.Redirect. This method should be quite familiar to developers who are experienced with ASP. Response.Redirect simply redirects the browser to another page. For example, the following line of code redirects the user to a page called SomeForm.aspx:
Visual Basic
Response.Redirect("SomeForm.aspx")
377