- •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
Session 19—Using DataReaders |
189 |
Using DataReader Properties
OK, so now that you have your DataReader object, what can you do with it? Well, just like all other objects, the DataReader object has numerous properties and methods. We’ll start with the properties:
Item property
The Item property returns the value for a given column in its native format. In order to reference the value of a column, you need to pass a string representing the column name or an integer representing the column’s index. Take for example the following table called t_bands:
band_id |
band_title |
music_type_id |
record_company_id |
1 |
Hootie & The Blowfish |
1 |
1 |
|
|
|
|
2 |
Toad the Wet Sprocket |
1 |
1 |
|
|
|
|
You could reference the band_title field in either of the following ways:
oDR.Items(“band_title”) oDR.Items(1)
You’ll notice that we passed a one (1) to the DataReader object’s Items property. To clarify, the 1 is the column index or location of the column in the row from which we want to retrieve the data. We used 1 as the index, because the numbering of column indexes begins with 0.
FieldCount property
The FieldCount property, which is obviously read-only, returns the number fields, as an integer, in the current record. Here is some sample syntax for getting the FieldCount:
Dim iFCount As Integer iFCount = oDR.FieldCount
One possible application of the FieldCount property is to iterate through the columns in a DataReader and write out the column’s value as shown in Listing 19-2.
Listing 19-2 FieldCount property application
<%@ 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
Continued
190 |
Saturday Evening |
Listing 19-2 |
Continued |
Dim oCmd As OleDbCommand
Dim oDR As OleDbDataReader
Dim iFieldCount As Integer
Dim x As Integer
oConn = New OleDbConnection(“Provider=SQLOLEDB;Data
Source=(local);Initial Catalog=Music;User ID=music;Password=music”)
oConn.Open()
oCmd = New OleDbCommand() oCmd.Connection = oConn oCmd.CommandType = CommandType.Text
oCmd.CommandText = “SELECT * FROM t_bands” oDR = oCmd.ExecuteReader()
iFieldCount = oDR.FieldCount
While oDR.Read()
Dim oRow As New TableRow() For x = 0 To (iFieldCount - 1)
Dim oCell As New TableCell() oCell.Text = oDR.Item(x) oRow.Cells.Add(oCell)
Next tblExample.Rows.Add(oRow)
End While
oDR.Close
oConn.Close End Sub </SCRIPT> <HTML> <BODY>
<asp:Table ID=”tblExample” BorderWidth=1 GridLines=”both” Runat=”server”/> </BODY>
</HTML>
In Listing19-2, all we’ve done is open our OleDbDataReader object, obtain the number of fields in the DataReader using the FieldCount property, and iterate through the rows of the DataReader using the Read method. (We discuss the Read method later in this session.) For each row, we loop through the fields and create a table cell containing the columns’ value. Simple!
IsClosed property
The IsClosed method returns a Boolean value indicating whether the DataReader is closed. A value of true means that the DataReader is closed.
Session 19—Using DataReaders |
191 |
RecordsAffected property
The RecordsAffected property returns the number of rows that are changed, inserted, or deleted by the Command object that opens the DataReader. 0 is returned from the RecordsAffected property if no records were affected by the command object, and –1 is returned for SELECT commands. The RecordsAffected property is not set until the
DataReader object is closed. The isClosed and RecordsAffected are the only DataReader properties that can be accessed after the DataReader has been closed.
Listing 19-3 illustrates how you can use the isClosed and RecordsAffected properties to display information about the Command that was executed to create a DataReader object.
Listing 19-3 Displaying command information
<%@ 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 oDR As OleDbDataReader
Dim oParam As OleDbParameter
Dim iBandID As Integer = 0
If Page.IsPostBack Then iBandID = lstBands.SelectedItem.Value
oConn = New OleDbConnection(“Provider=SQLOLEDB;Data
Source=(local);Initial Catalog=Music;User ID=music;Password=music”)
oConn.Open()
oCmd = New OleDbCommand() With oCmd
.Connection = oConn
.CommandType = CommandType.StoredProcedure
.CommandText = “prBandDelete” oParam = New OleDbParameter With oParam
.ParameterName = “BandID”
.OleDbType = OleDbType.Integer
.Value = iBandID End With
.Parameters.Add(oParam) Try
oDR = .ExecuteReader() lstBands.Items.Clear lstBands.Items.Add(New ListItem(“”,0))
While oDR.Read()
Continued
192 |
Saturday Evening |
Listing 19-3 |
Continued |
lstBands.Items.Add(New ListItem(oDR.Item(“band_title”),oDR.Item(“band_id”)))
End While
Catch err As Exception
Response.Write(“The following error occurred:<BR>” & err.Message & “<BR>”)
End Try End With
oDR.Close
oConn.Close
If oDR.isClosed Then
If oDR.RecordsAffected > 0 Then lblDeleted.Text = “You deleted “ & oDR.RecordsAffected & “ bands from the database.”
End If End Sub </SCRIPT> <HTML> <BODY>
<FORM ID=”frmBandDelete” Runat=”server”>
<asp:ListBox ID=”lstBands” Size=”1” AutoPostBack=”true” Runat=”server”/> <BR/><BR/>
<asp:Label ID=”lblDeleted” Text=”” ForeColor=”Red” Runat=”server”/> </FORM>
</BODY>
</HTML>
You’ll notice that Listing 19-3 uses a ListBox Web Control, lstBands, to allow a user to select a band that he or she would like to delete from the t_bands table. When the Web Form containing lstBands is submitted, the id of the band is gathered and passed to
OleDbCommand, oCmd, as an OleDbParameter, oParam. The prBandDelete stored procedure, which deletes the selected band and returns a recordset containing the remaining bands, is then executed by calling the Command object’s ExecuteReader method. Following is the code for the prBandDelete stored procedure.
CREATE PROCEDURE prBandDelete @BandID INT = 0
AS
IF @iBandID > 0 BEGIN
DELETE FROM t_songs WHERE album_id IN (SELECT album_id FROM t_albums WHERE band_id = @iBandID)
DELETE FROM t_albums WHERE band_id = @iBandID DELETE FROM t_band_members WHERE band_id = @iBandID DELETE FROM t_bands WHERE band_id = @iBandID
END
SELECT
band_id, band_title