List of Figures

Chapter 1: ADO.NET Basics

Figure 1-1: ODBC architecture

Figure 1-2: DAO model

Figure 1-3: OLE DB model

Figure 1-4: The ADO.NET components model

Figure 1-5: The relationship between Connection, DataAdpater, and a data source

Figure 1-6: The relationship between the Command object and the Connection object

Figure 1-7: Creating a transaction from a Connection object

Figure 1-8: The relationship between DataAdapter and Command

Figure 1-9: Creating a DataReader from a Command object

Figure 1-10: The relationship between DataAdapter and CommandBuilder

Figure 1-11: The relationship between DataAdapter and DataSet

Figure 1-12: The relationship between classes in a DataSet

Figure 1-13: The relationship between DataSet and DataView objects

Figure 1-14: Adding a reference to the System.Data.dll assembly

Figure 1-15: Displaying data on the console using DataReader

Figure 1-16: Output of the ADO.NET application in a DataGrid control

Chapter 2: Data Components in Visual Studio .NET

Figure 2-1: Table Mappings dialog box

Figure 2-2: Previewing data for the DataAdapter

Figure 2-3: Output of the Employee data to a DataGrid control

Figure 2-4: A DataSet's Properties window showing a typed DataSet

Figure 2-5: Creating a typed DataSet from the Add New Item window

Figure 2-6: Empty Dataset1.xsd in the VS .NET IDE

Figure 2-7: Design view of the DataSet's XML schema

Figure 2-8: DataView Properties window

Figure 2-9: Sorted and filtered data in a DataGrid

Figure 2-10: Adding a Data Form Wizard

Figure 2-11: Choosing tables and views

Figure 2-12: Selecting Customers as the parent and Orders as the child table to create the CustOrderRelation relationship

Figure 2-13: Choosing columns to display on the Data Form Wizard

Figure 2-14: Choosing between a grid and individual controls on the Data Form Wizard

Figure 2-15: Grid DataForm output

Figure 2-16: The Single Record in Individual Controls option

Figure 2-17: Data Form Wizard-generated form for the Single Record in Individual Controls option

Chapter 3: ADO.NET in Disconnected Environments

Figure 3-1: ADO.NET architecture

Figure 3-2: The System.Data namespace class hierarchy

Figure 3-3: The System.Data.Common namespace hierarchy

Figure 3-4: The System.Data.SqlTypes namespace hierarchy

Figure 3-5: The System.Data.OleDb namespace hierarchy

Figure 3-6: Relationship between the DataTable, the DataRow, and the DataColumn

Figure 3-7: The DataGrid view of an empty DataTable

Figure 3-8: A DataTable with three rows

Figure 3-9: A DataGrid with data relations

Figure 3-10: Orders record for Customers id 1001

Figure 3-11: Add, delete, sort, and search operations in a DataTable

Figure 3-12: Adding rows to the DataTable

Figure 3-13: Deleting rows from the DataTable

Figure 3-14: Result of clicking the Search button for Name="Amy"

Figure 3-15: Relationship between the DataSet, DataTable, and DataView

Figure 3-16: The relationship between DataSet, DataAdapter, and DataView objects

Figure 3-17: Attaching DataTables to a DataSet

Chapter 4: ADO.NET in Connected Environments

Figure 4-1: A generic data provider model

Figure 4-2: Output of the Customers table from the DataReader

Figure 4-3: Creating a Windows Forms application and adding controls to the form

Figure 4-4: Filling data from an Access database to a DataGrid control using OleDbDataAdapter

Figure 4-5: Filling data from a SQL Server database to a DataGrid control using SqlDataAdapter

Figure 4-6: Column mapping

Figure 4-7: Default multiple views application

Figure 4-8: Multiple views with different filter and sorting criteria

Chapter 5: Handling ADO.NET Events

Figure 5-1: Event handler and event argument classes defined in the System.Data namespace

Figure 5-2: OleDb data provider event handler and event arguments

Figure 5-3: Output of the Open button click

Figure 5-4: Output of the Close button click

Chapter 6: Integrating XML with ADO.NET

Figure 6-1: XML .NET architecture

Figure 6-2: XmlReader and its derived classes

Figure 6-3: The XmlWriter class and its derived classes

Figure 6-4: The XmlElement class inheritance

Figure 6-5: The XSL transformation

Figure 6-6: The XmlDataDocument class inheritance

Figure 6-7: Reading and writing data using XmlDataDocument

Figure 6-8: The XML designer

Figure 6-9: The XML schema Toolbox

Figure 6-10: Adding a schema element and its type

Figure 6-11: Adding a new bookstore element

Figure 6-12: A complexType item

Figure 6-13: Adding other types to complexType item

Figure 6-14: The book complexType and its elements

Figure 6-15: The author and book complexTypes in an XML schema

Figure 6-16: Viewing the XML code for a schema

Figure 6-17: XML designer–generated schema

Figure 6-18: The DataSet-derived class in the Class View

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

Figure 7-1: Synchronization between a data source and data-bound controls

Figure 7-2: Record navigation form

Figure 7-3: Record navigation system in action

Figure 7-4: ListBox and ComboBox data-binding form

Figure 7-5: Data synchronization in ComboBox and ListBox controls

Figure 7-6: The DataGrid's parent items and background

Figure 7-7: The DataGrid's parts

Figure 7-8: DataGrid-related style objects

Figure 7-9: Column reshuffling form

Figure 7-10: Getting a DataGrid control's column styles

Figure 7-11: Pop-up menu on DataGrid right-click menu

Figure 7-12: DataGrid navigation system

Figure 7-13: Implementing search functionality in a DataGrid control

Figure 7-14: Filtered data after searching

Chapter 9: ADO.NET Exception Handling

Figure 9-1: A system-generated error

Figure 9-2: An exception-handled error message

Figure 9-3: The custom error message

Figure 9-4: SqlException messages for SQL Server not found

Figure 9-5: SqlException messages when a database table not found

Chapter 10: Working with the ODBC .NET Data Provider

Figure 10-1: The output of Listing 10-1

Figure 10-2: Export Text Wizard options

Figure 10-3: The advanced options of the Export Text Wizard

Figure 10-4: Delimiter options of the Export Text Wizard

Figure 10-5: Exported Employees.txt file from Nothwind.mdb

Figure 10-6: Selecting the Microsoft Text Driver (*.txt, *.csv) option

Figure 10-7: Setting the DSN name and description

Figure 10-8: Selecting the directory and file types

Figure 10-9: Defining a text file format and column settings

Figure 10-10: Employees.xls data view

Figure 10-11: Output of Listing 10-4

Chapter 11: Stored Procedures and Views

Figure 11-1: Viewing available stored procedures in the Northwind database

Figure 11-2: Create, edit, delete, and run stored procedure options

Figure 11-3: Stored procedure editor

Figure 11-4: Creating a stored procedure

Figure 11-5: The mySP stored procedure in the editor

Figure 11-6: The mySP stored procedure listed for the Northwind database

Figure 11-7: The output of the mySP stored procedure in VS .NET

Figure 11-8: Stored procedure with input parameter

Figure 11-9: Registering a stored procedure parameter

Figure 11-10: The output of stored procedure CustOrdersDetail

Figure 11-11: The output of stored procedure mySP

Figure 11-12: A stored procedure with parameters

Figure 11-13: Output of Listing 11-2

Figure 11-14: Adding tables to the view designer

Figure 11-15: Creating a view after selecting columns from three tables

Figure 11-16: Saving a view

Figure 11-17: Entering a name for the view

Figure 11-18: Available views in the Server Explorer

Figure 11-19: Results of the CustEmpView view after executing it from the Server Explorer

Figure 11-20: The output the CustEmpView view from a program

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

Figure 12-1: Adding a reference to System.Data.OracleClient.dll

Figure 12-2: Adding a reference to Microsoft.Data.SqlXml.dll

Chapter 13: Developing a Custom Data Provider

Figure 13-1: A Web-service-based data provider that accesses a Relational Database Management System (RDBMS)

Figure 13-2: A custom data provider at a high level

Figure 13-3: Data from pipeddata.txt in Notepad

Figure 13-4: The output of the PipedDataProvider application

Chapter 14: Developing Database Web Applications using ASP.NET

Figure 14-1: The FirstWebApplication project

Figure 14-2: Default WebForm1.aspx page

Figure 14-3: HTML view of WebForm1.aspx

Figure 14-4: An ASP.NET page's right-click options

Figure 14-5: An ASP.NET document's page properties

Figure 14-6: Web Forms controls

Figure 14-7: WebForms1.aspx Design mode after adding Web Forms controls

Figure 14-8: Properties window for the Web controls

Figure 14-9: Final page of the Web application after changing some control properties

Figure 14-10: Document Outline viewer

Figure 14-11: HTML view of the Document Outline viewer

Figure 14-12: Output of your first Web application

Figure 14-13: Your first ADO.NET Web application

Figure 14-14: An item

Figure 14-15: Multi-item controls

Figure 14-16: Data-bound controls

Figure 14-17: DataGrid properties

Figure 14-18: DataGrid's Auto Format dialog box

Figure 14-19: DataGrid general properties

Figure 14-20: DataGrid Columns properties page

Figure 14-21: DataGrid Paging properties page

Figure 14-22: DataGrid Format properties page

Figure 14-23: DataGrid Border properties page

Figure 14-24: The final DataGrid after setting various properties

Figure 14-25: Viewing data in a DataGrid using the design-time data binding method

Figure 14-26: Paging option in a DataGrid

Figure 14-27: Adding, editing, and deleting pages

Figure 14-28: Adding a new record in the Web application

Figure 14-29: Updating records in a Web application

Figure 14-30: Table schema of Guest table of GuestBook.mdb

Figure 14-31: MyGuestBook.aspx submission page

Figure 14-32: The ViewGuestBook.aspx page

Figure 14-33: Thank you page

Figure 14-34: Welcome to my guest book

Figure 14-35: The Thanks.aspx page of the guest book

Figure 14-36: My guest book entries

Chapter 15: Using ADO.NET in Xml Web Services

Figure 15-1: Creating a new Web service project

Figure 15-2: Initially running the Web service Service1.asmx

Figure 15-3: The Web service after you add attributes

Figure 15-4: The Order DataSet displayed by the browser in XML format

Figure 15-5: Design View for searching for and displaying an order

Figure 15-6: The Add Web Reference locator

Figure 15-7: Web services available on the local server

Figure 15-8: Adding Web references

Figure 15-9: Files generated by the Web References Wizard to access the Web service

Figure 15-10: Result of the Fill method in a DataGrid

Figure 15-11: The InsertOrder and GetOrderFromDatabase methods of OrderRetrievalWebService

Figure 15-12: Design View of the order customer entry Web service

Figure 15-13: Adding an order to the database using Enter Order

Chapter 16: ASP.NET Server Controls and Data Binding

Figure 16-1: Adding Button controls to a Web page

Figure 16-2: Loading an image in an ImageButton control

Figure 16-3: The Calendar control in action

Figure 16-4: Using a ListBox Web server control

Figure 16-5: Data binding in a ListBox control

Figure 16-6: Data binding in a DropDownList control

Figure 16-7: Data binding in a DataList control

Figure 16-8: Multiple columns in a DataList control

Figure 16-9: Editable DataList control

Figure 16-10: A DataList control in editable mode

Figure 16-11: The Properties window of a DataList control

Figure 16-12: Data binding in a DataGrid control

Figure 16-13: The DataGrid control with the new styles

Figure 16-14: DataGrid with BoundColumns

Figure 16-15: A DataGrid control with button columns

Figure 16-16: Using template columns

Figure 16-17: DataGrid with EditCommandColumn

Figure 16-18: DataGrid with Save and Cancel options

Chapter 17: Building Real-World Web Applications

Figure 17-1: The Online Job Board application architecture

Figure 17-2: Schema of the Job table

Figure 17-3: Schema of the Resumes table

Figure 17-4: Schema of the Users table

Figure 17-5: The Login.aspx page

Figure 17-6: Setting the NavigateUrl property of a HyperLink control

Figure 17-7: The Register.aspx page

Figure 17-8: The Contact.aspx page

Figure 17-9: The PostJob.aspx page

Figure 17-10: The PostResume.aspx page

Figure 17-11: The DisplayData.aspx page

Figure 17-12: The Resumes.aspx page

Figure 17-13: The dnjHeader.aspx page

Figure 17-14: The job board

Figure 17-15: View Jobs page

Figure 17-16: The View Resumes page

Chapter 18: Object-Relational Mapping in .NET

Figure 18-1: Basic concept of OR mapping

Figure 18-2: The VideoTape test case output

Figure 18-3: The output of the UserTestCase program

Figure 18-4: The dependencies in the video store application

Figure 18-5: The output of the CheckInCheckOut test case

Chapter 19: Mapped Objects: Performance Considerations and Data Binding

Figure 19-1: The output of the TestDataGrid program

Figure 19-2: The output of the TestGridObjects page

Figure 19-3: The output of the FancyBinding page with the description shortened

Figure 19-4: The output of the FancyBinding page with the description expanded

Figure 19-5: The data in the VideoCategory table

Figure 19-6: The output of the test harness

Figure 19-7: The BlockedTree table with data

Figure 19-8: The output of the LazyLoading page

Figure 19-9: The incremental lazy loading test case output

Chapter 20: COM Interoperability and ADO.NET

Figure 20-1: Adding a reference to a COM library

Figure 20-2: Namespace after adding ADODB namespace to the project

Figure 20-3: Displaying data from an ADO recordset to a DataGrid

Figure 20-4: Adding a reference to ADOX library

Figure 20-5: ADOX namespace listed in project references

Figure 20-6: Browsing the msadomd.dll library

Figure 20-7: Adding a reference to msadomd.dll library

Figure 20-8: ADOMD namespace listed in the project namespaces

Figure 20-9: Windows form to test ADOMD

Figure 20-10: All available cubes in the FoodMart 2000 database

Figure 20-11: Getting dimensions of a cube

Figure 20-12: Viewing dimension properties

Chapter 21: Messaging

Figure 21-1: Viewing the Messaging Queuing node from Computer Management

Figure 21-2: Managing message queues through the Server Explorer

Figure 21-3: Message Queue Manager form

Figure 21-4: A simple messaging application

Figure 21-5: Messaging application in action

Figure 21-6: Creating a transactional queue using the Server Explorer

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

Figure 22-1: The Manage Indexes menu in Enterprise Manager

Figure 22-2: The Manage Indexes dialog box

Figure 22-3: The Create New Index dialog box

Figure 22-4: The Trace Properties dialog box

Figure 22-5: The SQL Server Profiler with a trace running

Figure 22-6: The Query Analyzer's recommendations for the current query

Figure 22-7: The Query Analyzer's recommendation to create an index that would help the query run faster

Figure 22-8: The estimated execution plan of the query in Listing 22-1

Figure 22-9: The estimated execution plan of the query in Listing 22-2

Figure 22-10: A join table that doesn't need a primary key

Figure 22-11: Generic data access class consumer application

Appendix A: Relational Databases: Some Basic Concepts

Figure A-1: Customers table before normalization

Figure A-2: Two rows of data from the Customers table

Figure A-3: Customers table schema after 1NF

Figure A-4: Data of the Customers table after 1NF

Figure A-5: Customers table after 2NF

Figure A-6: Orders table after 2NF

Figure A-7: Relationship between the Customers and Orders tables

Figure A-8: Customers table after 2NF

Figure A-9: Orders table after 2NF

Figure A-10: Orders table after 3NF

Figure A-11: Data of the Orders table after 3NF

Appendix B: Commonly Used SQL Statements

Figure B-1: Creating a new query in Access 2000

Figure B-2: Selecting database tables

Figure B-3: The Query Editor of Access 2000

Figure B-4: Customers table schema of the Northwind database

Figure B-5: Orders table schema of the Northwind database

Figure B-6: Output of the SELECT statement

Figure B-7: Output of SELECT * FROM Customers statement

Figure B-8: Output of the SELECT...WHERE statement

Figure B-9: Output of query that uses the SQL SUM function

Figure B-10: Output of COUNT

Figure B-11: Output of GROUP BY

Figure B-12: Output of the Alias statement

Figure B-13: Output of the JOIN statement

Appendix C: ADO.NET Frequently Asked Questions

Figure C-1: Paging in an ADO.NET application

Figure C-2: Getting a database schema programmatically

Figure C-3: Users table schema

Figure C-4: Reading and writing images in a database final form

Figure C-5: Displaying a bitmap after reading data from a database

Figure C-6: DiffGram format