![](/user_photo/1438_p9ksI.png)
- •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
![](/html/1438/356/html_PheEOaP5DB.eOjL/htmlconvd-76Dfm0195x1.jpg)
174 |
Saturday Evening |
<%@ Page LANGUAGE=”VB”%>
<%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.Data.ODBC” %> <SCRIPT LANGUAGE=”VB” RUNAT=”server”>
Sub Page_Load(Sender As Object, E As EventArgs) Dim oConn As OdbcConnection
Dim sConnString As String sConnString = “DSN=Bands”
oConn = New OdbcConnection(sConnString) oConn.Open()
oConn.Close() End Sub </SCRIPT> <HTML>
<BODY>
Opening a Connection with ODBC! </BODY>
</HTML>
You’ll notice that the code is very similar to the previous OLEDB examples. Actually, the only difference is how you construct the connection string. Instead of providing all of the database information, such as location, Data Source, and name, Database, you simply supply a DSN name/value pair. You’ve also already provided the User ID or Password when you created the DSN.
Note
You’ll notice in all of the examples, we call a Close method. This method closes the connection that we have opened. Always close connections!!!!! We can’t emphasize this enough. When you close the connection, it is returned to the connection pool and can be used by another request.
Now that the connection is open, what can we do with it? Well, to be quite honest not too terribly much. With the ADO.NET connection objects you can’t directly execute SQL commands against a data source like you could with ADO. In order to create, update, delete, or retrieve data from our data source, you must utilize other ADO.NET objects such as the DataReader and DataAdapter objects, both of which we’ll discuss in Sessions 19 and 20, respectively. The connection does, however, provide several properties and methods to manage transactions.
Using Transactions
By definition, a transaction is an atomic unit of work that either fails or succeeds. There is no such thing as a partially completed transaction. Since a transaction is composed of many steps, each step in the transaction must succeed for the transaction to be successful. If any one part of transaction fails, the entire transaction fails. When the transaction fails, the system needs to return, or roll back, to its original state.
To demonstrate why you may want to use transactions, we will present a sample scenario. Imagine that you own your own bank. A customer wants to transfer $5,000 from checking to savings. So you get on your computer and begin the transaction. Behind the scenes, the following “units” of work need to be completed:
![](/html/1438/356/html_PheEOaP5DB.eOjL/htmlconvd-76Dfm0196x1.jpg)
Session 17—Opening a Connection |
175 |
1.Subtract $5,000 from customer’s checking account.
2.Add $5,000 to customer’s saving’s account.
This is a very simple scenario, but we think it illustrates the need for transactions. If either of these steps fails, but the other succeeds, we have a major problem. If step 1 succeeds, but step 2 is not successful, you will have a very upset customer on your hands. If step 1 fails, but step 2 succeeds, you will have a very satisfied customer, but some upset shareholders. Either way, bad news! So you need to ensure that either both steps are successful or that the system returns to its state prior to beginning the transaction.
This is how ADO.NET supports transactions. The ADO.NET connection objects provide the BeginTransaction method, which, when called, returns a transaction object (either
SQLTransaction or OLEDBTransaction).
The BeginTransation method begins a transaction. When called, the BeginTransaction method returns a Transaction object (either an OleDbTransaction or SQLTransaction). It is through the Transaction objects that you can manage your transactions. The Transaction objects support numerous properties and methods (like any object). We will only discuss a few of them in this book. If you need more information, we suggest taking a quick glance at your .NET Framework documentation. The Transaction object’s Commit method commits the transaction, which means that all the steps in the transaction have completed successfully and the new state should be persisted. The transaction object’s RollBack method returns your data store to its pre-transaction state if one or more of the steps in the transaction fail.
So, the basic framework for a transactional ASP.NET page may look like this:
Dim oConn As OleDBConnection
Dim oTransaction As OleDbTransaction Dim sConnString As String sConnString = “DSN=Bands”
oConn = New OLEDBConnection(sConnString) oConn.Open()
oTransaction = oConn.BeginTransaction()
Try
[Transaction Steps] oTransaction.Commit()
Exit Try
Catch
oTransaction.RollBack()
End Try
We begin the transaction with the BeginTransaction method and then attempt to complete each of the steps of the transaction. Because the steps are within a try . . . catch structure, if a step fails, the transaction will be rolled back using the RollbackTransaction method. If all the steps are completed successfully, the transaction is committed with the CommitTransaction method. The try . . . catch structure is new to VB.NET and, as you can see, is very useful.
![](/html/1438/356/html_PheEOaP5DB.eOjL/htmlconvd-76Dfm0197x1.jpg)
176 |
Saturday Evening |
Note
Transactions should only be used when writing to your data store. You could retrieve data from your data store using transactions, but this would really serve no purpose since you are not attempting to preserve the integrity of your data. Transactions do incur a bit of a performance penalty, so use them only when necessary.
REVIEW
With the ADO.NET connection objects, SQLConnection and OleDbConnection, you, as a developer, can easily connect to relational and non-relational data sources. The ADO connection objects were designed to be very lightweight — and thereby quickly created and destroyed. The connection objects also provide transaction management facilities through the transaction objects, which are vital for enterprise application development.
QUIZ YOURSELF
1.What is the main function of the connection objects? (See session introduction.)
2.What is the major difference between ODBC and OLEDB? (See “Opening a Connection.”)
3.What is a transaction and why are they important for enterprise application development? (See “Using Transactions.”)
![](/html/1438/356/html_PheEOaP5DB.eOjL/htmlconvd-76Dfm0198x1.jpg)
S E S S I O N
18
Executing Commands
Session Checklist
Understanding the function of the Command objects in ADO.NET
Executing SQL commands against a data store
Appending parameters to a Command object
Filling a DataReader using the Command object’s Execute method
In Session 17, “Opening a Connection,” we introduced the ADO.NET Connection objects. In this session, we build upon our previous discussion of connections and introduce the OledbCommand objects. ADO.NET offers two flavors of command objects: OledbCommand
and SqlCommand. Just like the connection object, you can use either object to access a SQL Server database, but need to use the OleDbCommand object to access any other data source. However, using the SqlCommand object with SQL Server does provide some performance gain.
So, what is a command? A command is an instruction — in this case to create, retrieve, update, or delete data in your data store. Most importantly, the Command objects enable you to fill the DataReader objects with data.
In the telephone analogy we used earlier, you are the Command object. When the person on the other end answers the phone, you might say, “May I please speak with Tony?” You have issued a command or request. Or you might say, “Can I have directions to your restaurant?” in which case you are asking for information to be returned to you.
Note
For the remainder of this session, we will focus on the OleDb Managed Provider Command object, OleDbCommand. For the most part, the OleDbCommand and SqlCommand objects map one-to-one. So if you are using a SQL Server database and would like to improve performance, you can use the SqlCommand object. To utilize the SqlCommand object, you will need to import the
System.Data.SqlClient namespace.