List of Listings

Chapter 1: ADO.NET Basics

Listing 1-1: Your ADO.NET Application

Listing 1-2: Viewing Data from a SQL Server Database to a DataGrid Control

Chapter 2: Data Components in Visual Studio .NET

Listing 2-1: Added Sql Server Provider Components

Listing 2-2: DataAdapter Connection through TableMapping

Listing 2-3: Form's Load Event Handler

Listing 2-4: Calling DataForm1 from a Button Click

Listing 2-5: Calling DataForm2 from a Button Click

Listing 2-6: The Load and Update Button Click Event Handlers

Listing 2-7: LoadDataSet Method Generated by the Data Form Wizard

Listing 2-8: The FillDataSet Method Generated by the Data Form Wizard

Listing 2-9: The UpdateDataSource and UpdateDataSet Methods Generated by the Data Form Wizard

Listing 2-10: Cancel All Button Click Event Handler

Chapter 3: ADO.NET in Disconnected Environments

Listing 3-1: Creating Columns Using Different DataColumn Constructors

Listing 3-2: Creating a DataColumn and Setting Its Properties

Listing 3-3: Setting a DataColumn as the Primary Key

Listing 3-4: Creating the Id and Name Data Columns of the Customers Table

Listing 3-5: Creating a Table Using DataTable and DataColumn

Listing 3-6: Using the ColumnMapping Property of DataColumn

Listing 3-7: Adding and Removing Custom Properties

Listing 3-8: Using the Ordinal and MaxLength Properties

Listing 3-9: Setting the Values of the Id, Address, and Name Columns of a DataRow

Listing 3-10: Adding Rows to a DataTable Using DataRow

Listing 3-11: Calling DataRow's RejectChanges and Delete Methods

Listing 3-12: Calling the RowState Property

Listing 3-13: Creating a Customer/Order Relationship Using DataRelation

Listing 3-14: Form's Constructor Calling CreateCustomersTable, CreateOrdersTable, and BindData

Listing 3-15: Customer/Orders Relationship Example

Listing 3-16: Class-Level DataTable and DataSet Variables

Listing 3-17: The CreateCustomersTable Method

Listing 3-18: The AddRowBtn_Click Method

Listing 3-19: The DeleteRow_Click Method

Listing 3-20: The SearchButtonClick Method

Listing 3-21: Sorting Data Using the Select Method

Listing 3-22: Saving a DataTable Data in an XML Document

Listing 3-23: Using the Contains, Find, and Remove Method of DataRowCollection

Listing 3-24: Using the InsertAt and RemoveAt Methods

Listing 3-25: Removing a Column from a Collection

Listing 3-26: Determining the State of a Row

Listing 3-27: Editing Rows Using BeginEdit, CancelEdit, and EndEdit Methods

Listing 3-28: Setting and Reading Custom Properties of a DataSet

Listing 3-29: Adding DataTable Objects to a DataSet

Listing 3-30: Using Copy, Clone, and Clear Methods of DataSet

Listing 3-31: Merging Two DataSetse

Listing 3-32: Merging DataRow and DataTable Objects

Listing 3-33: Creating DataView Objects from a DataTable

Listing 3-34: Adding, Updating, and Deleting Rows of a DataView

Listing 3-35: Finding Rows in a DataView Using the Find Method

Chapter 4: ADO.NET in Connected Environments

Listing 4-1: Creating SqlConnection Objects

Listing 4-2: Connecting to an OleDb Database and Reading the OleDbConnection Properties

Listing 4-3: Connecting to a SQL Server Using the OleDb Data Provider

Listing 4-4: Connecting to a SQL Server and Reading the SqlConnection Properties

Listing 4-5: Creating Two Connections with Different Strings

Listing 4-6: Calling ReleaseObjectPool

Listing 4-7: Setting and Getting SqlCommand Properties

Listing 4-8: Using SqlCommand to Read Data from a Database

Listing 4-9: Using OleCommand to Access an Access Database

Listing 4-10: Calling a Stored Procedure Using SqlCommand

Listing 4-11: The Sales By Year Stored Procedure in the Northwind Database

Listing 4-12: Executing and Reading the Results of a Stored Procedure

Listing 4-13: Adding the Subtotal Listing to the Output of the Stored Procedure Results

Listing 4-14: Using TableDirect to Read a Table

Listing 4-15: Adding Records to a Table Using the INSERT SQL Statement

Listing 4-16: Using the ExecuteScalar Method to Retrieve a Single Value

Listing 4-17: DataReader Reads Data from a SQL Server Database

Listing 4-18: Executing Batches Using DataReader

Listing 4-19: Creating SqlDataAdapter Instances

Listing 4-20: Executing a SELECT * Statement Using OleDbDataAdapter

Listing 4-21: Using SqlDataAdapter Properties

Listing 4-22: Calling a DataAdapter's Fill Method

Listing 4-23: Using the FillSchema Method of SqlDataAdapter

Listing 4-24: Displaying the Orders Table Data in a DataGrid Using OleDbDataAdapter

Listing 4-25: Displaying the Customers Table's Data in a DataGrid Using SqlDataAdapter

Listing 4-26: Setting the DeleteCommand Property of DataAdapter

Listing 4-27: Creating a SqlParameter

Listing 4-28: Calling the Fill Method of a DataAdapter

Listing 4-29: Removing a Row and Calling the Update Method of a DataAdapter

Listing 4-30: Calling a DataAdapter's Update Method

Listing 4-31: Using DataTableMapping to Map the Orders Table of Northwind

Listing 4-32: Using DataTableMapping with the Table Option

Listing 4-33: Using DataColumnMapping

Listing 4-34: Creating and Using the SqlCommandBuilder Class

Listing 4-35: Creating a Parameter

Listing 4-36: Filling Data from Multiple Tables

Listing 4-37: Merging Two DataSet Objects

Listing 4-38: Adding, Editing, and Deleting a DataSet Rows

Listing 4-39: Using the AcceptChanges and RejectChanges Methods of a DataSet

Listing 4-40: Saving Only Modified Rows of a DataSet

Listing 4-41: Private Members

Listing 4-42: The Load Data Button's Click Event Handler

Listing 4-43: Optimistic Concurrency

Listing 4-44: Pessimistic Concurrency

Listing 4-45: Using Savepoints in the Sql Data Provider

Listing 4-46: Executing Multiple SQL Queries Using SqlTransaction

Chapter 5: Handling ADO.NET Events

Listing 5-1: Writing Event Handlers for the InfoMessage and StateChange Events

Listing 5-2: Writing Code That Executes Connection Events

Listing 5-3: SqlDataAdapter Event Handler Bodies

Listing 5-4: DataAdapter FillError Event Handler Code

Listing 5-5: The RowUpdated Event Handler

Listing 5-6: The RowUpdating Event Handler

Listing 5-7: Calling the DataAdapter's Fill and Update Methods

Listing 5-8: Writing Code to Call MergeFailed Event Handler

Listing 5-9: Writing the ColumnChanging and ColumnChanged Event Handlers

Listing 5-10: Writing the RowChanging and RowChanged Event Handlers

Listing 5-11: Writing the RowDeleting and RowDeleted Event Handlers

Listing 5-12: The XmlDataDocument Event Handling Example

Listing 5-13: The NodeChanged Event Handler

Listing 5-14: The NodeInserted Event Handler

Listing 5-15: The NodeRemoved Event Handler

Listing 5-16: DataView OnListChanged Event Handler

Listing 5-17: The DataView Event Handler Caller

Chapter 6: Integrating XML with ADO.NET

Listing 6-1: Reading an XML File

Listing 6-2: Getting XML Node Information

Listing 6-3: Getting Node Information

Listing 6-4: Using the MoveToContent Method

Listing 6-5: Using the GetAttribute of a Node

Listing 6-6: Using the Skip Method

Listing 6-7: XmlWriter Example

Listing 6-8: Output of the XmlWriterSample.vb Class

Listing 6-9: Using the XmlConvert Class

Listing 6-10: The XmlDocumentFragment Sample

Listing 6-11: Appending in an XML Document Fragment

Listing 6-12: Adding Nodes to a Document

Listing 6-13: Getting the Root Node of a Document

Listing 6-14: Removing All Items of a Node

Listing 6-15: Calling RemoveAll for books.xml

Listing 6-16: Calling ReplaceChild

Listing 6-17: Inserting an XML Fragment into a Document

Listing 6-18: Adding a Node with Attributes

Listing 6-19: An XSL Transformation

Listing 6-20: Using the WriteXml Method

Listing 6-21: WriteXml Method Output

Listing 6-22: Using the WriteXmlSchema Method

Listing 6-23: XmlDataDocumentSample.vb

Listing 6-24: Saving the DataSet Data to an XML Document

Listing 6-25: Moving to Root and First Child Nodes Using XPathNavigator

Listing 6-26: Reading a Document Using XPathNavigator

Listing 6-27: Using XPathIterator and Select

Listing 6-28: The XML Schema Generated for a Database Table

Chapter 7: Data Binding and Windows Forms Data-Bound Controls

Listing 7-1: Binding a TextBox Using Binding

Listing 7-2: Binding Multiple Controls Using Binding

Listing 7-3: Counting and Removing Binding Objects

Listing 7-4: Reading All the Bindings of a Form

Listing 7-5: Reading a TextBox Control's Binding Properties

Listing 7-6: Adding Format and Parse Event Handlers

Listing 7-7: Reading All Controls Participating in Data Binding

Listing 7-8: Record Navigation System Variables

Listing 7-9: LoadData and GetDataSet Methods

Listing 7-10: Move Next, Move Previous, Move First, and Move Last Button Code

Listing 7-11: Adding a ListBox Control Event Handler

Listing 7-12: Raising ListBox Events

Listing 7-13: The Form Load Event Handler

Listing 7-14: The FillDataSet Method

Listing 7-15: Binding ListBox and ComboBox Controls

Listing 7-16: Setting the DataSource and DataMember Properties of a DataGrid Control

Listing 7-17: Creating and Adding a DataGridTableStyle

Listing 7-18: Creating a DataGridTableStyle from CurrencyManager

Listing 7-19: Creating a DataGridBoolColumn

Listing 7-20: AllowNavigation in Action

Listing 7-21: Using Some of the DataGrid's Color and Font Properties

Listing 7-22: The DataGrid's Caption Properties

Listing 7-23: Form's Load Method

Listing 7-24: The CreateDataSet Method

Listing 7-25: The FillDataGrid Method

Listing 7-26: Seeing HitTest in Action

Listing 7-27: The FillDataGrid and Form_Load Methods

Listing 7-28: Exchanging the Button Click Handler

Listing 7-29: The ReshuffleColumns Method

Listing 7-30: Getting a DataGrid Columns' Style Using DataGridColumnStyle

Listing 7-31: Getting a DataGrid Column Header Name

Listing 7-32: Getting the Current DataGridColumnStyle

Listing 7-33: Sorting a DataGrid Control's Columns

Listing 7-34: CustRecordSet.vb

Listing 7-35: Creating a Custom Recordset

Listing 7-36: Moving Record Button Click Event Handlers

Listing 7-37: FillDataGrid Method

Listing 7-38: Seach Button Click Event Handler

Listing 7-39: Saving Updated Data in a Data Source from a DataGrid Control

Chapter 8: Constraints and Data Relations

Listing 8-1: Creating a FOREIGN KEY Constraint

Listing 8-2: Creating a UNIQUE Constraint

Listing 8-3: Adding and Removing Constraints

Listing 8-4: Adding UNIQUE Constraints

Listing 8-5: Reading All the Constraints of a Collection

Listing 8-6: Adding a PRIMARY KEY Constaint to a DataTable

Listing 8-7: Filling a DataSet from Two Tables

Listing 8-8: Creating a DataRelation

Listing 8-9: Getting DataRelation Properties

Listing 8-10: Deleting a Data Relation from the Collection

Chapter 9: ADO.NET Exception Handling

Listing 9-1: Opening a File

Listing 9-2: A Simple Exception Handling Block

Listing 9-3: A Simple Try...Catch Block

Listing 9-4: The Try...Catch...Finally Statement

Listing 9-5: Using Nested Try...Catch and Multiple Catch Blocks

Listing 9-6: Defining a Custom Filter and Error Message

Listing 9-7: Reading Data from a SQL Server

Listing 9-8: Using the SqlError and SqlErrorCollection Objects

Chapter 10: Working with the ODBC .NET Data Provider

Listing 10-1: Reading Data from Northwind Using the ODBC Data Provider

Listing 10-2: Accessing a MySQL Database

Listing 10-3: Accessing the TextDB.txt File

Listing 10-4: Accessing Employees.xls Using the ODBC Data Provider

Chapter 11: Stored Procedures and Views

Listing 11-1: Executing the mySP Stored Procedure Using the Sql Data Provider

Listing 11-2: Using Parameters in a Stored Procedure

Listing 11-3: AddCat1 Stored Procedure

Listing 11-4: Executing a Stored Procedure with Output Parameter

Listing 11-5: Executing a View Programmatically

Chapter 12: Oracle, SQLXML, and Other .NET Data Providers

Listing 12-1: Connecting to an Oracle Database

Listing 12-2: FillDataGrid Method

Listing 12-3: ExecuteSqlQuery Method

Listing 12-4: Constructing SQL INSERT, UPDATE, and DELETE Queries

Listing 12-5: Reading Data Using OracleDataReader

Listing 12-6: Using the Oracle-Specific DataReader Methods

Listing 12-7: Reading and Writing Data Using OracleDataAdapter

Listing 12-8: Using SqlXmlAdapter

Listing 12-9: Using SqlXmlCommand to Execute Commands

Chapter 13: Developing a Custom Data Provider

Listing 13-1: The Implementation for an Enumerator

Listing 13-2: The PipedDataConnection Object

Listing 13-3: The PipedDataCommand Object

Listing 13-4: The PipedDataReader Object

Listing 13-5: The PipedDataAdapter Object

Listing 13-6: The PipedDataProvider Test Case

Chapter 14: Developing Database Web Applications using ASP.NET

Listing 14-1: Filling Data from a Database to Data-Bound Controls

Listing 14-2: ASP.NET Version of Your First ASP.NET Application

Listing 14-3: Filling the DataSet from DataAdapter

Listing 14-4: The PageIndexChanged Handler of the DataGrid Control

Listing 14-5: HTML View of asp:DataGrid

Listing 14-6: Setting the AllowPaging, PageSize, and AllowSorting Properties

Listing 14-7: Setting DataGrid's PageStyle Modes

Listing 14-8: Setting the DataGrid Control's PageStyle Text

Listing 14-9: Setting an Image as the DataGrid Control's Next and Previous Page Text

Listing 14-10: The FillDataGrid Method

Listing 14-11: The ExecuteSQL Method

Listing 14-12: Add Button Click Event Handler

Listing 14-13: Updating Data on the Update Button Click

Listing 14-14: Deleting Data on the Delete Button Click

Listing 14-15: Adding Guest Data to the Database

Listing 14-16: Opening ViewGuestBook.aspx

Listing 14-17: Page_Load Event Handler Code of ViewGuestBook.aspx

Listing 14-18: My Home Page and View Guest Book Buttons

Chapter 15: Using ADO.NET in Xml Web Services

Listing 15-1: The GetOrdersFromDatabase Method for Obtaining an Order from an Order ID

Listing 15-2: Adding Descriptions to the WebService and WebMethod Attributes

Listing 15-3: Button-Click Event Handler That Calls the Order Retrieval Web Service

Listing 15-4: Web Service Method for Populating an Order in the Database

Listing 15-5: Client Event Handler for Executing the Web Service

Listing 15-6: ExecuteQuery Method

Chapter 16: ASP.NET Server Controls and Data Binding

Listing 16-1: Writing Button Click Handlers

Listing 16-2: Getting Selected Date of a Calendar Control

Listing 16-3: Setting a Calendar Control's Properties

Listing 16-4: The VisibleMonthChanged Event

Listing 16-5: Applying a Simple Template on a Repeater Control

Listing 16-6: Adding Templates to a DataList Control

Listing 16-7: Adding Formatting to the Templates of a DataList Control

Listing 16-8: Using the Repeater Control

Listing 16-9: Data Binding in ListBox Control

Listing 16-10: Data Binding in a DropDownList Control

Listing 16-11: Data Binding in the TextBox, Button, and Image Controls

Listing 16-12: Data Binding in a DataList Control

Listing 16-13: Setting a DataList Control's Properties

Listing 16-14: Setting a DataList Control's Styles

Listing 16-15: Adding Update, Delete, Cancel, and Edit Command Handlers

Listing 16-16: The Code for the Update, Delete, Cancel, and Edit Command Handlers

Listing 16-17: Editable DataList Control

Listing 16-18: Data Binding in a DataGrid Control

Listing 16-19: Setting a DataGrid Control's Style Properties

Listing 16-20: Rearranging a DataGrid Column's Sequence

Listing 16-21: Formatting BoundColumn's Header and Footers

Listing 16-22: The ButtonColumn Click Event Handler

Listing 16-23: DataGrid with Button Columns

Listing 16-24: Using Template Columns

Listing 16-25: Using Template Columns in a DataGrid Control

Listing 16-26: Editable DataGrid

Listing 16-27: Implementing Sorting in a DataGrid Control

Chapter 17: Building Real-World Web Applications

Listing 17-1: Login Button Click Event Handler Code

Listing 17-2: The Button Click Event Handlers

Listing 17-3: The Register.aspx Code

Listing 17-4: Send Message Click Event Handler

Listing 17-5: The Post Job Button Click Event Handler

Listing 17-6: The Reset Fields Click Event Handler

Listing 17-7: The Done Button Click Event Handler

Listing 17-8: Adding Options to Post Resume Page

Listing 17-9: The Post Resume Button Click Event Handler

Listing 17-10: The Done Button Click Event Handler

Listing 17-11: DisplayData.aspx HTML View

Listing 17-12: The FillDataGrid and Page_Load Methods

Listing 17-13: The FillPartialData Method

Listing 17-14: The FillDataGrid and Page_Load Methods

Listing 17-15: Resumes.aspx HTML View

Chapter 18: Object-Relational Mapping in .NET

Listing 18-1: The VideoTapeData Object

Listing 18-2: The VideoTape Object

Listing 18-3: The VideoTapeDataAccess Object

Listing 18-4: The Test Case for the VideoTape Component

Listing 18-5: The User Object in the User Component

Listing 18-6: The UserData Object in the User Component

Listing 18-7: The UserDataAccess Object in the User Component

Listing 18-8: The UserTestCase Object

Listing 18-9: SelectVideosCheckedOutToUser

Listing 18-10: The VideoCheckInCheckOut Object

Listing 18-11: The VideoCheckInCheckOut Object

Listing 18-12: The Test Case for the VideoCheckInCheckOut Component

Chapter 19: Mapped Objects: Performance Considerations and Data Binding

Listing 19-1: The Code-Behind File of the TestDataGrid Program

Listing 19-2: The HTML Side of the TestDataGrid Page

Listing 19-3: The HTML of the TestGridObjects Page

Listing 19-4: The Theoretical Shopping Cart Example

Listing 19-5: The HTML of the FancyBinding Page

Listing 19-6: The Code-Behind Object of the FancyBinding Page

Listing 19-7: VideoCategory and Related Objects

Listing 19-8: The GetBlockedCategory Stored Procedure

Listing 19-9: The Addition of the Videos() Array to the VideoCategory Object

Listing 19-10: The HTML Page of the LazyLoading Page

Listing 19-11: The Code-Behind Object of the LazyLoading Page

Listing 19-12: The Incremental Lazy Loading Stored Procedure

Listing 19-13: VideoCategoryIndexer

Listing 19-14: The VideoCategoryIndexer Test Case

Listing 19-15: The WeakCache Object

Chapter 20: COM Interoperability and ADO.NET

Listing 20-1: Using an ADODB Namespace to Access a Database

Listing 20-2: Using ADOX from Managed Code

Listing 20-3: Getting All Available Cubes from the FoodMart 2000 Database

Listing 20-4: Getting All Dimensions of a Cube

Listing 20-5: Getting Dimension Members

Chapter 21: Messaging

Listing 21-1: Retrieving Private Queues

Listing 21-2: Retrieving Public Queues

Listing 21-3: Filtering Queues Based on Category, Machine Name, and Label

Listing 21-4: Creating a Queue

Listing 21-5: Deleting a Queue

Listing 21-6: Getting a Queue's Properties

Listing 21-7: Sending Messages to a Queue

Listing 21-8: Receiving Messages from a Queue

Listing 21-9: Sending and Receiving Transactional Messages

Listing 21-10: Sending Encrypted Messages

Chapter 22: SQL Server and ADO.NET: Notes on Performance

Listing 22-1: The Query from SelectVideosCheckedIn, Not Optimized

Listing 22-2: An Optimized Version of the SelectVideosCheckedIn Query

Listing 22-3: Using sp_executesql to Execute a Database Query

Listing 22-4: Generic Data Access Class

Listing 22-5: User-Defined Variables

Listing 22-6: Using the Generic Data Access Class

Appendix C: ADO.NET Frequently Asked Questions

Listing C-1: Getting a Database Table Schema Using SqlCommand

Listing C-2: Reading DataSet Columns and Their Data Types

Listing C-3: GetPageData Method

Listing C-4: Implementing Paging in ADO.NET

Listing C-5: Transactions in the OleDb Data Provider

Listing C-6: Reading a Database Schema Programmatically

Listing C-7: Getting a Database's Tables from a SQL Server Database

Listing C-8: Getting a Database Table Schema

Listing C-9: Selecting Distinct Rows from a Database Table

Listing C-10: Reading and Writing to and from the Windows Registry

Listing C-11: Reading ODBC DSNs

Listing C-12: Browse Button Click Event Handler

Listing C-13: Save Image Button Click Event Handler

Listing C-14: Reading Binary Data

Listing C-15: A DiffGram Format

Listing C-16: Reading DiffGrams

Listing C-17: Reading Only Modified Rows of a DataSet