- •Preface
- •Who Should Read This Book
- •Organization and Presentation
- •Contacting the Authors
- •Acknowledgments
- •Contents
- •Introduction
- •Why Microsoft .NET?
- •The Microsoft .NET Architecture
- •Internet Standards
- •The Evolution of ASP
- •The Benefits of ASP.NET
- •What Is .NET?
- •.NET Experiences
- •.NET Clients
- •.NET Services
- •.NET Servers
- •Review
- •Quiz Yourself
- •Installation Requirements
- •Installing ASP.NET and ADO.NET
- •Installing the .NET Framework SDK
- •Testing Your Installation
- •Support for .NET
- •Review
- •Quiz Yourself
- •Designing a Database
- •Normalization of Data
- •Security Considerations
- •Review
- •Quiz Yourself
- •Creating a Database
- •Creating SQL Server Tables
- •Creating a View
- •Creating a Stored Procedure
- •Creating a Trigger
- •Review
- •Quiz Yourself
- •INSERT Statements
- •DELETE Statements
- •UPDATE Statements
- •SELECT Statements
- •Review
- •Quiz Yourself
- •The XML Design Specs
- •The Structure of XML Documents
- •XML Syntax
- •XML and the .NET Framework
- •Review
- •Quiz Yourself
- •ASP.NET Events
- •Page Directives
- •Namespaces
- •Choosing a Language
- •Review
- •Quiz Yourself
- •Introducing HTML Controls
- •Using HTML controls
- •How HTML controls work
- •Intrinsic HTML controls
- •HTML Control Events
- •The Page_OnLoad event
- •Custom event handlers
- •Review
- •Quiz Yourself
- •Intrinsic Controls
- •Using intrinsic controls
- •Handling intrinsic Web control events
- •List Controls
- •Rich Controls
- •Review
- •Quiz Yourself
- •Creating a User Control
- •Adding User Control Properties
- •Writing Custom Control Methods
- •Implementing User Control Events
- •Review
- •Quiz Yourself
- •Common Aspects of Validation Controls
- •Display property
- •Type Property
- •Operator Property
- •Using Validation Controls
- •RequiredFieldValidator
- •RegularExpressionValidator
- •CompareValidator
- •RangeValidator
- •CustomValidator
- •ValidationSummaryx
- •Review
- •Quiz Yourself
- •Maintaining State Out of Process for Scalability
- •No More Cookies but Plenty of Milk!
- •Out of Process State Management
- •Review
- •Quiz Yourself
- •Introducing the Key Security Mechanisms
- •Web.config and Security
- •Special identities
- •Using request types to limit access
- •New Tricks for Forms-based Authentication
- •Using the Passport Authentication Provider
- •Review
- •Quiz Yourself
- •ASP.NET Updates to the ASP Response Model
- •Caching with ASP.NET
- •Page Output Caching
- •Absolute cache expiration
- •Sliding cache expiration
- •Fragment Caching
- •Page Data Caching
- •Expiration
- •File and Key Dependency and Scavenging
- •Review
- •Quiz Yourself
- •A Brief History of Microsoft Data Access
- •Differences between ADO and ADO.NET
- •Transmission formats
- •Connected versus disconnected datasets
- •COM marshaling versus text-based data transmission
- •Variant versus strongly typed data
- •Data schema
- •ADO.NET Managed Provider Versus SQL Managed Provider
- •Review
- •Quiz Yourself
- •Review
- •Quiz Yourself
- •Creating a Connection
- •Opening a Connection
- •Using Transactions
- •Review
- •Quiz Yourself
- •Building a Command
- •Connection property
- •CommandText property
- •CommandType property
- •CommandTimeout property
- •Appending parameters
- •Executing a Command
- •ExecuteNonQuery method
- •Prepare method
- •ExecuteReader method
- •Review
- •Quiz Yourself
- •Introducing DataReaders
- •Using DataReader Properties
- •Item property
- •FieldCount property
- •IsClosed property
- •RecordsAffected property
- •Using DataReader Methods
- •Read method
- •GetValue method
- •Get[Data Type] methods
- •GetOrdinal method
- •GetName method
- •Close method
- •Review
- •Quiz Yourself
- •Constructing a DataAdapter Object
- •SelectCommand property
- •UpdateCommand, DeleteCommand, and InsertCommand properties
- •Fill method
- •Update method
- •Dispose method
- •Using DataSet Objects
- •DataSetName property
- •CaseSensitive property
- •Review
- •Quiz Yourself
- •Constructing a DataSet
- •Tables property
- •TablesCollection Object
- •Count property
- •Item property
- •Contains method
- •CanRemove method
- •Remove method
- •Add method
- •DataTable Objects
- •CaseSensitive property
- •ChildRelations property
- •Columns property
- •Constraints property
- •DataSet property
- •DefaultView property
- •ParentRelations property
- •PrimaryKey property
- •Rows property
- •Dispose method
- •NewRow method
- •Review
- •Quiz Yourself
- •What Is Data Binding?
- •Binding to Arrays and Extended Object Types
- •Binding to Database Data
- •Binding to XML
- •TreeView Control
- •Implement the TreeView server control
- •Review
- •Quiz Yourself
- •DataGrid Control Basics
- •Binding a set of data to a DataGrid control
- •Formatting the output of a DataGrid control
- •Master/Detail Relationships with the DataGrid Control
- •Populating the Master control
- •Filtering the detail listing
- •Review
- •QUIZ YOURSELF
- •Updating Your Data
- •Handling the OnEditCommand Event
- •Handling the OnCancelCommand Event
- •Handling the OnUpdateCommand Event
- •Checking that the user input has been validated
- •Executing the update process
- •Deleting Data with the OnDeleteCommand Event
- •Sorting Columns with the DataGrid Control
- •Review
- •Quiz Yourself
- •What Is Data Shaping?
- •Why Shape Your Data?
- •DataSet Object
- •Shaping Data with the Relations Method
- •Review
- •Quiz Yourself
- •OLEDBError Object Description
- •OLEDBError Object Properties
- •OLEDBError Object Methods
- •OLEDBException Properties
- •Writing Errors to the Event Log
- •Review
- •Quiz Yourself
- •Introducing SOAP
- •Accessing Remote Data with SOAP
- •SOAP Discovery (DISCO)
- •Web Service Description Language (WSDL)
- •Using SOAP with ASP.NET
- •Review
- •Quiz Yourself
- •Developing a Web Service
- •Consuming a Web Service
- •Review
- •Quiz Yourself
- •ASP and ASP.NET Compatibility
- •Scripting language limitations
- •Rendering HTML page elements
- •Using script blocks
- •Syntax differences and language modifications
- •Running ASP Pages under Microsoft.NET
- •Using VB6 Components with ASP.NET
- •Review
- •Quiz Yourself
- •Preparing a Migration Path
- •ADO and ADO.NET Compatibility
- •Running ADO under ASP.NET
- •Early Binding ADO COM Objects in ASP.NET
- •Review
- •Quiz Yourself
- •Answers to Part Reviews
- •Friday Evening Review Answers
- •Saturday Morning Review Answers
- •Saturday Afternoon Review Answers
- •Saturday Evening Review Answers
- •Sunday Morning Review Answers
- •Sunday Afternoon Review Answers
- •What’s on the CD-ROM
- •System Requirements
- •Using the CD with Windows
- •What’s on the CD
- •The Software Directory
- •Troubleshooting
- •ADO.NET Class Descriptions
- •Coding Differences in ASP and ASP.NET
- •Retrieving a Table from a Database
- •Displaying a Table from a Database
- •Variable Declarations
- •Statements
- •Comments
- •Indexed Property Access
- •Using Arrays
- •Initializing Variables
- •If Statements
- •Case Statements
- •For Loops
- •While Loops
- •String Concatenation
- •Error Handling
- •Conversion of Variable Types
- •Index
258 |
Sunday Morning |
Why Shape Your Data?
The ability to display hierarchical or parent-child relationships is key to a wide variety of applications. You use these types of relationships whenever you need to list all orders associated with a product, all products associated with a category, all files contained in a folder, or customers located in all cities. Each of these examples can be represented in a parentchild relationship. Although we could set up a series of stored procedures or commands that query a set of data based upon a parent’s unique key in order to return the filtered set of children, this method can create a tremendous amount of overhead.
In ADO.NET, we can use the Relations() method of the DataSet object to identify and enforce parent-child relationships between disparate sets of data, even if those data sets do not have an enforced relationship in the source database. As long as there is a way to connect to distinct data stores through a common key field, then we can enforce a parent-child relationship.
DataSet Object
If you are an ADO programmer, you may look at a DataSet object as a disconnected RecordSet object that contains one or more tables of data. In effect a DataSet is similar to a hierarchical RecordSet that is created using the MSDataShape ADO Provider and then building a SELECT command using the shape syntax as we discussed previously. If you are an XML developer, you’re more likely to see the DataSet object as a specific Document Object Model (DOM), which is set to represent tables of data where each table contains a set of elements with a consistent strongly typed structure. Either of these views provides an accurate representation of the DataSet object.
The DataSet object in ADO.NET provides the ability to hold an unlimited number of data tables. We add data tables to the DataSet object by creating a DataAdapter and supplying a stored procedure or SQL syntax to fill the data table as shown in Listing 25-2:
Listing 25-2 Example of using the DataAdapter and DataSet commands
<%@ Import Namespace=”System.Data.OleDB”%> <%@ Import Namespace=”System.Data” %>
<%@ Page Language=”VB” Debug=”true” %> <HTML>
<SCRIPT LANGUAGE=”VB” RUNAT=”server”>
Sub Page_Load()
Dim myConnection as new OleDBConnection(“provider=sqloledb;Data Source=localhost;Initial Catalog=pubs;User ID=sa;pwd=”)
Dim myDataSet as New DataSet()
Dim StoresDSCommand as new OleDBDataAdapter(“select * from stores”, myConnection)
StoresDSCommand.Fill(myDataSet,”Stores”)
Dim SalesDSCommand as OleDBDataAdapter
SalesDSCommand = new OleDBDataAdapter(“select *, title from sales inner join titles on sales.title_id=titles.title_id”, myConnection)
Session 25—Data Shaping with ADO.NET |
259 |
SalesDSCommand.Fill(myDataSet,”Sales”)
myDataSet.Relations.Add(“StoreSale”,myDataSet.Tables(“Stores”).Columns(“stor_id”),myDataS et.Tables(“Sales”).Columns(“stor_id”))
Dim Store as DataRow
Dim Sale as DataRow
Msg.Text=Msg.Text & “<H2>List of Titles Sold By Store</H2>” for each Store in myDataSet.Tables(“Stores”).Rows
Msg.Text=Msg.Text & “<BR><B><I>”
Msg.Text=Msg.Text & “Store Name: “ & Store(“stor_name”).ToString() Msg.Text=Msg.Text & “</B></I>”
Msg.Text=Msg.Text & “<p>Address: “ & Store(“stor_address”) & “</p>” Msg.Text=Msg.Text & “<blockquote>”
Msg.Text=Msg.Text & “<p><i>List of Titles sold by “ & Store(“stor_name”)
&”</i></p>”
for each Sale in Store.GetChildRows(myDataSet.Relations(“StoreSale”)) Msg.Text=Msg.Text & “<p>Title = “ & Sale(“title”).ToString() & “</p>” next
Msg.Text=Msg.Text &”</blockquote>”
next
end sub
</SCRIPT>
<BODY>
<ASP:LABEL ID=”Msg” RUNAT=”server” /> </BODY>
</HTML>
This code fills the myDataSet object with the contents of the stores table.
SalesDSCommand.Fill(myDataSet,”Sales”)
In order to describe relationships between these tables, so we can easily display or report on any hierarchical relationships that may exist, we need to use the Relations() method of the DataSet object. We will cover this in the next section.
Shaping Data with the Relations Method
The Relations method enables you to set the parent-child relationship between two tables by supplying the name of the relation, the table and column, that should be the parent relation and the table and column that is the child relation. Looking at the code in Listing 25-2 from the previous section, we set up a relationship between the Stores table and the Sales table in myDataSet using the following code segment:
myDataSet.Relations.Add(“StoreSale”,myDataSet.Tables(“Stores”).Columns(“stor_id”),myDataS et.Tables(“Sales”).Columns(“stor_id”))
This will establish a relationship where the Stores table represents the parent and is joined via the stor_id column to the Sales table. Using this relationship, you can quickly loop through each of the stores and provide a list of titles sold to each store. The following
260 |
Sunday Morning |
segment of Listing 25-2, illustrates how you can loop through each of the stores using a simple For...Next loop:
Dim Store as DataRow
Dim Sale as DataRow
Msg.Text=Msg.Text & “<H2>List of Titles Sold By Store</H2>”
for each Store in myDataSet.Tables(“Stores”).Rows Msg.Text=Msg.Text & “<BR><B><I>”
Msg.Text=Msg.Text & “Store Name: “ & Store(“stor_name”).ToString() Msg.Text=Msg.Text & “</B></I>”
Msg.Text=Msg.Text & “<p>Address: “ & Store(“stor_address”) & “</p>” Msg.Text=Msg.Text & “<blockquote>”
Msg.Text=Msg.Text & “<p><i>List of Titles sold by “ & Store(“stor_name”)
&”</i></p>”
In order to access each of the child records, we use the Relation method established earlier and a For...Next loop as illustrated in the following example:
for each Sale in Store.GetChildRows(myDataSet.Relations(“StoreSale”)) Msg.Text=Msg.Text & “<p>Title = “ & Sale(“title”).ToString() & “</p>”
next
Msg.Text=Msg.Text &”</blockquote>”
next end sub
The resulting output of Listing 25-2 can be seen in Figure 25-3.
Figure 25-3 Resulting output of using the relations method in ASP.NET
Session 25—Data Shaping with ADO.NET |
261 |
REVIEW
The use of the Relations method enables you to quickly navigate hierarchical relationships between datasets. This common but powerful technique allows you as a developer to robustly handle complex relationships with ease.
QUIZ YOURSELF
1.How can parent-child relationships be established between data stores from different data sources? (See “Why Shape Your Data?”)
2.Describe the differences between the ADO Shape Command and the ADO.Net Relations method. (See “What Is Data Shaping?” and “Why Shape Your Data?”)
3.What limits are there in establishing tables and relationships for a specified DataSet object? (See “Shaping Data with the Relations Method.”)
S E S S I O N
26
Handling ADO.NET Errors
Session Checklist
Understanding the OLEDBException class and its interaction with the
OLEDBError object
Implementing custom OLEDBError handling in your application
Implementing a generic event handler that writes errors to the Event Log
ADO.NET provides a framework similar to ADO for handling errors that occur when calling ADO.NET components. When the ADO.NET adapter encounters an error, it throws an Exception which can then be evaluated for any contained errors.
There are two types of exceptions that can be thrown: an OLEDBException and a a SQLException. An OLEDBException can be thrown by any OLE DB data source, while a SQLException is thrown by SQL Server. In this session, we will focus on handling the OLEDBException, however the approach for handling a SQLException is nearly identical except you have access to a broader range of properties to evaluate as shown in Table 26-1.
Table 26-1 SQLException Properties not contained in OLEDBException
SQLException Property |
Description |
Class |
Gets the severity level of the error returned from the SQL |
|
Server .NET Data Provider. |
|
|
LineNumber |
Gets the line number within the Transact-SQL command |
|
batch or stored procedure that generated the error. |
|
|
Number |
Gets a number that identifies the type of error. |
|
|
Continued
264 |
Sunday Morning |
Table 26-1 |
Continued |
SQLException Property |
Description |
Procedure |
Gets the name of the stored procedure or remote procedure |
|
call (RPC) that generated the error. |
|
|
Server |
Gets the name of the computer running an instance of SQL |
|
Server that generated the error. |
|
|
State |
Gets the number modifying the error to provide additional |
|
information. |
|
|
The Try . . . Catch . . . Finally error-handling functionality of ASP.NET is very useful for evaluating ADO.NET errors. Let’s start by creating a function, GetRecords(). GetRecords() is a simple function that attempts to connect to a database, fill a dataset, and then return the dataset. In the next section we will implement OLEDBErrorHandler(), a generic error handler that will evaluate the errors that are established during calls in the GetRecords() function and then write those errors to the browser and to the System Event Log.
The code listings 26-1, 26-2, 26-3 are each segments of code extracted from the file errorhandle.aspx in the Session 26 folder of the CD.
Note
Listing 26-1 OLEDBError handling code segment from errorhandle.aspx
<%@ Import Namespace=”System.Diagnostics”%> <%@ Import Namespace=”System.Exception” %> <%@ Import Namespace=”System.Data.OleDB” %> <%@ Import Namespace=”System.Data” %>
<%@ Page Language=”VB” Debug=”False” Trace=”False” %> <HTML>
<SCRIPT LANGUAGE=”VB” RUNAT=”server”>
Sub ExecuteDBCodeBtn_Click(Sender As Object, E as EventArgs) Try
Dim MyDataset As DataSet = GetRecords() grid1.DataSource = MyDataset.Tables(0).DefaultView grid1.DataBind()
Catch ex as Exception
Dim oLabel as new Label() Dim sMess as String oLabel.Text=””
oLabel.Id=”ExecuteDBCodeBtn_ErrorLabel” Page.Controls.Add(oLabel)
sMess = sMess & “<b><p>Code Error Occurred</p></b>” sMess = sMess & “<ul><li>” & ex.message & “</li></ul>” oLabel.Text = oLabel.Text & sMess
End Try