- •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
178 |
Saturday Evening |
Building a Command
There are many ways of building, or constructing, a command object with ADO.NET. You can explicitly set the command object’s properties, pass parameters into the command object’s constructor, or a combination of the two. Following are several examples of how to initialize (or construct) an OleDbCommand object:
oCmd = New OleDbCommand()
oCmd = New OledbCommand(sSQL)
oCmd = New OledbCommand(sSQL, oConn)
In the previous listing, oConn is an OleDbConnection object and sSQL is a query command string.
Listing 18-1 shows an example of how you might build a command that returns all of the rows in the t_bands table in the Music database:
Listing 18-1 Building a Command
<%@ Page Language=”VB” %>
<%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.Data.OleDb” %> <SCRIPT LANGUAGE=”VB” RUNAT=”server”>
Sub Page_Load(Sender As Object, E As EventArgs) Dim oConn As OledbConnection
Dim oCmd As OledbCommand Dim sSQL As String
sSQL = “SELECT * FROM t_bands”
oConn = New OledbConnection
oConn.ConnectionString = “Provider=SQLOLEDB;Data Source=(local);Initial
Catalog=Music;User ID=music;Password=music”
oConn.Open()
oCmd = New OledbCommand(sSQL, oConn)
oConn.Close oConn = Nothing
End Sub </SCRIPT> <HTML> <BODY>
Building a Command with ADO.NET! </BODY>
</HTML>
You’ll notice in the previous sample that we build the OleDbCommand by passing two parameters to its constructor. The first parameter is a string representing the SQL command we want to execute. The second parameter is an OleDbConnection object.
Session 18—Executing Commands |
179 |
The OleDbCommand object has several properties that you can explicitly set. Some of these properties are Connection, CommandText, CommandType, and CommandTimeout.
Connection property
The Connection property is used to set or get the connection against which to execute the command. You must pass a valid OleDbConnection object to the Connection property or you will receive an error.
Listing 18-2 shows an example of how you might explicitly set the Connection property by passing it a valid OleDbConnection object.
Listing 18-2 Explicitly setting a Command object’s Connection properties
<%@ Page Language=”VB”%>
<%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.Data.OleDb” %> <SCRIPT LANGUAGE=”VB” RUNAT=”server”>
Sub Page_Load(Sender As Object, E As EventArgs) Dim oConn As OleDbConnection
Dim oCmd As OleDbCommand Dim sSQL As String
Dim sConnString As String
sSQL = “SELECT * FROM t_bands”
sConnString = “Provider=SQLOLEDB;Data Source=(local);Initial
Catalog=Music;User ID=music;Password=music”
oConn = New OleDbConnection
With oConn
.ConnectionString = sConnString
.Open()
End With
oCmd = New OledbCommand(sSQL)
With oCmd
.Connection = oConn
End With
oConn.Close() End Sub </SCRIPT> <HTML>
<BODY>
Building a Command with ADO.NET! </BODY>
</HTML>
180 |
Saturday Evening |
CommandText property
The CommandText property gives you a means of holding your command (as a string) for later execution. It can contain a SQL statement, a stored procedure name, or a table name. For example, you can assign a simple SQL statement to the CommandText property as follows:
oCmd.CommandText = “SELECT band_id, band_title, music_type_id, record_company_id FROM t_bands”
Alternatively, you could assign a stored procedure name to the CommandText property and tell the Command object you are using a stored procedure by setting the CommandType property accordingly:
oCmd.CommandText = “prGetBands” oCmd.CommandType = CommandType.StoredProcedure
CommandType property
The CommandType property gets the CommandText or sets how it is interpreted. The possible values, or enumerations, of the CommandType property are
StoredProcedure
TableDirect
Text
When the CommandType property is set to StoredProcedure, the CommandText property is interpreted as a stored procedure. Go figure! If the CommandType is set to TableDirect and the CommandText property is set to a valid table name, then all the rows and columns for the specified table are returned. This is generally not a good idea, for performance reasons, when executing the command against a large database. Finally, if the CommandType property is set to Text, then the CommandText is executed as a SQL text command.
Listing 18-3 presents an example of how to execute a stored procedure called prCountBands.
Listing 18-3 Executing a stored procedure
<%@ Page Language=”VB” debug=”true” %> <%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.Data.OleDb” %> <SCRIPT LANGUAGE=”VB” RUNAT=”server”>
Sub Page_Load(Sender As Object, E As EventArgs) Dim oConn As OledbConnection
Dim oCmd As OledbCommand Dim sSQL As String
Dim iBandCount As Integer
oConn = New OledbCOnnection(“Provider=SQLOLEDB;Data
Source=(local);Initial Catalog=Music;User ID=music;Password=music”)
oConn.Open()
Session 18—Executing Commands |
181 |
oCmd = New OleDbCommand(sSQL, oConn) oCmd.CommandType = CommandType.StoredProcedure oCmd.CommandText = “prCountBands”
iBandCount = oCmd.ExecuteScalar()
oConn.Close()
lblBandCount.Text = iBandCount End Sub
</SCRIPT>
<HTML>
<BODY>
There are <asp:Label ID=”lblBandCount” Text=”” Runat=”server” /> bands in the database.
</BODY>
</HTML>
CommandTimeout property
The CommandTimeout property gets or sets the time, in seconds, to wait while executing the command before terminating the attempt and generating an error. The syntax for setting the CommandTimeout property follows:
oCmd.CommandTimeout = 60
The default value for the CommandTimeout property is 30 seconds. The CommandTimeout property is not inherited from the command’s Connection. The command object’s CommandTimeout property and the connection object’s CommandTimeout property are completely disparate properties. The Command object’s CommandTimeout property sets the maximum amount of time, in seconds, for a command to attempt to execute before returning an error. The Connection object’s ConnectionTimeout works the same way. The connection object attempts to open the connection for a designed amount of time before returning an error.
Setting the CommandTimeout property’s value to 0 indicates that the command will attempt to execute indefinitely. We do not recommend this!
Note
Appending parameters
The OleDbCommand object supports a collection property named Parameters. The Parameters property is actually a OleDbParameterCollection object that can contain more than one OleDbParameter object. The Parameters property enables you to append parameters to the Command object. Parameters are generally attached to commands that are executing stored procedure that require input parameters. For example, you could write the following stored procedure to return a band’s title based on its band_id:
182 |
Saturday Evening |
CREATE PROCEDURE prGetBandTitle @iID AS INT = 0
AS
SELECT band_title from t_bands WHERE band_id = @iID RETURN
So how do you append parameters? First you create an OleDbParameter object. An OledbParameter object can be constructed in several ways. For now, we’ll focus on constructing the OleDbParameter object by setting its properties explicitly rather than passing them into the OleDbParameter object constructor. The properties we’ll set are
ParameterName, DBType, and Value as follows:
oParam = New OleDbParameter() oParam.ParameterName = “@iID” oParam.DBType = OleDbType.Integer oParam.Value = 1
The OledbParameter object supports an Add() method that you can call to append the OLEDBParameter to your OLEDBCommand as shown in Listing 18-4.
Listing 18-4 Appending a parameter to a command
<%@ Page Language=”VB” %>
<%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.Data.OleDb” %> <SCRIPT LANGUAGE=”VB” RUNAT=”server”>
Sub Page_Load(Sender As Object, E As EventArgs) Dim oConn As OleDbConnection
Dim oCmd As OleDbCommand Dim oParam As OleDbParameter Dim sSQL As String
oConn = New OleDbConnection(“Provider=SQLOLEDB;Data
Source=(local);Initial Catalog=Music;User ID=music;Password=music”)
oConn.Open()
oCmd = New OleDbCommand(sSQL, oConn) oCmd.CommandType = CommandType.StoredProcedure oCmd.CommandText = “prGetBandTitle”
oParam = New OleDbParameter() oParam.ParameterName = “@iID” oParam.DBType = OleDbType.Integer oParam.Value = 1
oCmd.Parameters.Add(oParam) End Sub
</SCRIPT>
<HTML>
<BODY>
Appending a Parameter to a Command with ADO.NET! </BODY>
</HTML>