O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

ADO.NET in a Nutshell

ADO.NET in a Nutshell

by Bill Hamilton, Matthew MacDonald
Publisher: O'Reilly Media, Inc.
Release Date: April 2003
ISBN: 0596003617
View table of contents
Start reading

Book Description

Written by experts on the Microsoft® .NET programming platform, ADO.NET in a Nutshell delivers everything .NET programmers will need to get a jump-start on ADO.NET technology or to sharpen their skills even further. In the tradition of O'Reilly's In a Nutshell Series, ADO.NET in a Nutshell is the most complete and concise source of ADO.NET information available.ADO.NET is the suite of data access technologies in the .NET Framework that developers use to build applications services accessing relational data and XML. Connecting to databases is a fundamental part of most applications, whether they are web, Windows®, distributed, client/server, XML Web Services, or something entirely different. But ADO.NET is substantially different from Microsoft's previous data access technologies--including the previous version of ADO--so even experienced developers need to understand the basics of the new disconnected model before they start programming with it.Current with the .NET Framework 1.1, ADO.NET in a Nutshell offers one place to look when you need help with anything related to this essential technology, including a reference to the ADO.NET namespaces and object model. In addition to being a valuable reference, this book provides a concise foundation for programming with ADO.NET and covers a variety of issues that programmers face when developing web applications or Web Services that rely on database access. Using C#, this book presents real world, practical examples that will help you put ADO.NET to work immediately.Topics covered in the book include:

  • An Introduction to ADO.NET
  • Connections, Commands and DataReaders
  • Disconnected Data
  • Advanced DataSets
  • Transactions
  • DataViews and Data Binding
  • XML and the DataSet
Included with the book is a Visual Studio .NET add-in that integrates the entire reference directly into your help files. When combining ADO.NET in a Nutshell with other books from O'Reilly's .NET In a Nutshell series, you'll have a comprehensive, detailed and independent reference collection that will help you become more productive.

Table of Contents

  1. A Note Regarding Supplemental Files
  2. Preface
    1. Audience
    2. Contents of This Book
    3. What’s on the CD-ROM
    4. Conventions Used in This Book
    5. Comments and Questions
    6. Acknowledgments
      1. Bill
      2. Matthew
      3. CD-ROM Acknowledgments
  3. I. ADO.NET Tutorial
    1. 1. Introduction
      1. ADO.NET Data Providers
      2. Connected and Disconnected Data
        1. Connected Classes
        2. Disconnected Classes
    2. 2. .NET Data Providers
      1. Data Providers
        1. Microsoft SQL Server
        2. OLE DB
        3. ODBC
        4. Oracle
        5. SQLXML Managed Classes
        6. Other Providers
      2. Selecting a Data Provider
      3. Creating a Custom Data Provider
    3. 3. Connections
      1. Connection Object Overview
      2. The Connection String
        1. Setting Connection String Parameters
          1. The SQL Server connection string
          2. The MSDE connection string
          3. The OLE DB connection string
          4. The ODBC .NET connection string
          5. The Oracle .NET connection string
        2. Security Risks of the Connection String
      3. Opening and Closing Connections
        1. Connection Events
        2. Connections and Exception Handling
        3. Disposing Connections
      4. Connection Pooling
        1. SQL Server and Oracle Connection Pooling
        2. OLE DB and ODBC Connection Pooling
        3. Connection Pooling and Application Domains
        4. Connection Pooling and Performance Counters
    4. 4. Commands
      1. Command Object Overview
      2. Creating and Executing a Command
        1. Executing a Command That Doesn’t Return Rows
          1. Updating a record
          2. Deleting a record
          3. Inserting a record
        2. Executing a Command That Returns a Single Value
      3. Parameter Object Overview
        1. Creating Parameters
      4. Parameterized Commands
      5. Commands with Stored Procedures
        1. Executing a Stored Procedure
        2. Output Parameters
        3. Stored Procedure Return Values
        4. Deriving Parameters
      6. Commands and Data Definition Language (DDL)
    5. 5. DataReaders
      1. DataReader Object Overview
      2. Performing a Query with a DataReader
        1. Using Column Ordinals
        2. Using Typed Accessors
        3. Retrieving Null Values
        4. Returning Multiple Result Sets
        5. Showing All Columns with the DataReader
        6. Reading Single Rows with a DataReader
        7. Retrieving BLOB Data
      3. Stored Procedures with the DataReader
      4. DataReaders and Schema Information
        1. Retrieving Schema Information for a Query
        2. Retrieving Schema Tables
          1. Retrieving schema tables with SQL Server
          2. Retrieving schema tables with the OLE DB provider
    6. 6. DataSets
      1. Creating an Untyped DataSet
      2. Working with Tables in the DataSet
      3. Adding and Removing Relations
      4. Adding Custom Information
      5. Cloning the Schema
      6. Copying the DataSet
      7. Merging Two DataSets
      8. Removing All Data
      9. Resetting the DataSet
      10. Committing and Discarding Changes
        1. AcceptChanges and RejectChanges
        2. HasChanges and GetChanges
    7. 7. DataTables
      1. Creating a DataTable
      2. Working with Columns
      3. Constraints
      4. Primary Key
      5. Rows
      6. Loading Data
      7. Committing and Discarding Changes
      8. Cloning the Schema of the Table
      9. Copying the Table
      10. Selecting a Subset of Rows
      11. Performing Aggregate Calculations
      12. Removing All Data
      13. Resetting the Table
      14. Identifying Errors in the Table
      15. DataTable Events
        1. ColumnChanged and ColumnChanging
        2. RowChanged, RowChanging, RowDeleted, and RowDeleting
    8. 8. DataColumns
      1. Creating DataColumns
      2. Creating AutoIncrement Columns
      3. Creating Expression Columns
      4. Handling Null Values
      5. Mapping .NET Data Provider Types to .NET Framework Types
    9. 9. DataRows
      1. Creating a DataRow
      2. Updating Rows
      3. Deleting Rows
      4. Using Row State Information
      5. Using Row Version Information
      6. Accepting or Rejecting Changes to Rows
      7. Navigating Parent and Child Rows
      8. Using Row Error Information
    10. 10. Constraints
      1. Constraint Object Overview
      2. The UniqueConstraint
        1. Constraints and FillSchema( )
      3. The ForeignKeyConstraint
        1. Referential Integrity with ForeignKeyConstraint Rules
    11. 11. DataRelations
      1. DataRelation Object Overview
        1. DataRelations and Constraints
      2. Navigating Relational Data
        1. Modeling a One-to-Many Relationship
        2. Modeling a Many-to-Many Relationship
        3. Creating Expression-Based Columns Using Relations
    12. 12. DataViews and Data Binding
      1. The DataView and DataViewManager
        1. Binding to a DataView
        2. Binding to a DataViewManager
      2. Sorting and Filtering
        1. Sorting with the DataView
        2. Filtering by Column
          1. Filter operators
          2. Pattern-matching filters
          3. Filter-supported functions
          4. Aggregate functions and relations in filters
        3. Filtering by Row State
        4. Displaying Multiple Views
      3. Accessing Data Through a DataView
        1. Searching a DataView
        2. Navigating Relations with a DataView
      4. Windows Data Binding
        1. The CurrencyManager and BindingContext
        2. List Binding
        3. Single-Value Binding
        4. Format and Parse
        5. Controlling Navigation
        6. Master-Detail Forms
        7. Creating New Binding Contexts
      5. ASP.NET Data Binding
        1. List Binding
        2. ASP.NET Templated Data Controls
        3. The DataList
        4. Templates and Styles
    13. 13. Strongly Typed DataSets
      1. Creating a Strongly Typed DataSet
        1. Discussion of Underlying Classes
      2. Adding a Row
      3. Editing a Row
      4. Finding a Row
      5. Null Data
      6. Navigating Hierarchical Data
      7. Annotations
    14. 14. DataAdapters
      1. Creating DataAdapter Object
      2. Retrieving Data from the Data Source
      3. Retrieving Schema Information from the Data Source
      4. Updating the Data Source
      5. Mapping Tables and Columns
      6. AcceptChangesDuringFill
      7. ContinueUpdateOnError
      8. DataAdapter Events
    15. 15. Updating the Data Source
      1. SqlCommandBuilder Class Overview
      2. Updating a Data Source Using Command Builder
      3. Updating a Data Source Using Custom Logic
      4. Refreshing Data After Updating
      5. Retrieving Updated Values from the Data Source
      6. Updating Data in Related Tables
      7. Handling Concurrency Issues
      8. Optimization
    16. 16. Transactions
      1. Manual Transactions
      2. Isolation Levels
      3. Savepoints
      4. Nested Transactions
      5. Transactions Using a DataAdapter
      6. Automatic Transactions
    17. 17. XML and the DataSet
      1. DataSet XML Methods
        1. Dissecting the DataSet XML
        2. Dissecting the DataSet XML Schema
        3. XML Write and Read Modes
        4. Dissecting the DiffGram
      2. Shaping DataSet XML
        1. Attributes and Elements
        2. Relational XML Data
      3. Other .NET XML Classes
      4. XmlDataDocument Object Overview
        1. Searching a DataSet with XPath
        2. Transforming DataSet XML with XSLT
      5. Using the Data Objects to Edit XML
        1. Inferring XML Structure
      6. SQL Server 2000 XML
        1. The SQLXML Provider
        2. Converting to XML on the Client-Side
        3. Submitting Direct XPath Queries
        4. Batch Updates with the DiffGram
  4. II. ADO.NET Core Classes
    1. 18. The Connection Class
      1. Comments/Troubleshooting
      2. Properties Reference
      3. Methods Reference
      4. Events Reference
    2. 19. The Command Class
      1. Comments/Troubleshooting
      2. Properties Reference
      3. Collections Reference
      4. Methods Reference
    3. 20. The Parameter Class
      1. Comments/Troubleshooting
      2. Properties Reference
    4. 21. The DataReader Class
      1. Comments/Troubleshooting
      2. Properties Reference
      3. Methods Reference
    5. 22. The DataSet Class
      1. Comments/Troubleshooting
      2. Properties Reference
      3. Collections Reference
      4. Methods Reference
      5. Events Reference
    6. 23. The DataTable Class
      1. Comments/Troubleshooting
      2. Properties Reference
      3. Collections Reference
      4. Methods Reference
      5. Events Reference
    7. 24. The DataColumn Class
      1. Comments/Troubleshooting
      2. Properties Reference
      3. Collections Reference
    8. 25. The DataRow Class
      1. Comments/Troubleshooting
      2. Properties Reference
      3. Collections Reference
      4. Methods Reference
    9. 26. The Constraint Class
      1. Comments/Troubleshooting
      2. Properties Reference
      3. Collections Reference
    10. 27. The DataRelation Class
      1. Comments/Troubleshooting
      2. Properties Reference
      3. Collections Reference
    11. 28. The DataView Class
      1. Comments/Troubleshooting
      2. Properties Reference
      3. Methods Reference
      4. Events Reference
    12. 29. The DataAdapter Class
      1. Comments/Troubleshooting
      2. Properties Reference
      3. Collections Reference
      4. Methods Reference
      5. Events Reference
    13. 30. The CommandBuilder Class
      1. Comments/Troubleshooting
      2. Properties Reference
      3. Methods Reference
    14. 31. The Transaction Class
      1. Comments/Troubleshooting
      2. Properties Reference
      3. Methods Reference
  5. III. API Quick Reference
    1. 32. How to Use This Quick Reference
      1. Finding a Quick-Reference Entry
      2. Reading a Quick-Reference Entry
        1. Type Name, Namespace, Assembly, Type Category, and Flags
        2. Description
        3. Synopsis
          1. Member availability and flags
          2. Functional grouping of members
        4. Class Hierarchy
        5. Cross References
        6. A Note About Type Names
    2. 33. Converting from C# to VB Syntax
      1. General Considerations
      2. Classes
      3. Structures
      4. Interfaces
      5. Class, Structure, and Interface Members
        1. Fields
        2. Methods
        3. Properties
        4. Events
      6. Delegates
      7. Enumerations
    3. 34. The System.Data Namespace
      1. AcceptRejectRule
      2. CommandBehavior
      3. CommandType
      4. ConnectionState
      5. Constraint
      6. ConstraintCollection
      7. ConstraintException
      8. DataColumn
      9. DataColumnChangeEventArgs
      10. DataColumnChangeEventHandler
      11. DataColumnCollection
      12. DataException
      13. DataRelation
      14. DataRelationCollection
      15. DataRow
      16. DataRowAction
      17. DataRowChangeEventArgs
      18. DataRowChangeEventHandler
      19. DataRowCollection
      20. DataRowState
      21. DataRowVersion
      22. DataRowView
      23. DataSet
      24. DataSysDescriptionAttribute
      25. DataTable
      26. DataTableCollection
      27. DataView
      28. DataViewManager
      29. DataViewRowState
      30. DataViewSetting
      31. DataViewSettingCollection
      32. DBConcurrencyException
      33. DbType
      34. DeletedRowInaccessibleException
      35. DuplicateNameException
      36. EvaluateException
      37. FillErrorEventArgs
      38. FillErrorEventHandler
      39. ForeignKeyConstraint
      40. IColumnMapping
      41. IColumnMappingCollection
      42. IDataAdapter
      43. IDataParameter
      44. IDataParameterCollection
      45. IDataReader
      46. IDataRecord
      47. IDbCommand
      48. IDbConnection
      49. IDbDataAdapter
      50. IDbDataParameter
      51. IDbTransaction
      52. InRowChangingEventException
      53. InternalDataCollectionBase
      54. InvalidConstraintException
      55. InvalidExpressionException
      56. IsolationLevel
      57. ITableMapping
      58. ITableMappingCollection
      59. MappingType
      60. MergeFailedEventArgs
      61. MergeFailedEventHandler
      62. MissingMappingAction
      63. MissingPrimaryKeyException
      64. MissingSchemaAction
      65. NoNullAllowedException
      66. ParameterDirection
      67. PropertyAttributes
      68. PropertyCollection
      69. ReadOnlyException
      70. RowNotInTableException
      71. Rule
      72. SchemaType
      73. SqlDbType
      74. StateChangeEventArgs
      75. StateChangeEventHandler
      76. StatementType
      77. StrongTypingException
      78. SyntaxErrorException
      79. TypedDataSetGenerator
      80. TypedDataSetGeneratorException
      81. UniqueConstraint
      82. UpdateRowSource
      83. UpdateStatus
      84. VersionNotFoundException
      85. XmlReadMode
      86. XmlWriteMode
    4. 35. The System.Data.Common Namespace
      1. DataAdapter
      2. DataColumnMapping
      3. DataColumnMappingCollection
      4. DataTableMapping
      5. DataTableMappingCollection
      6. DbDataAdapter
      7. DBDataPermission
      8. DBDataPermissionAttribute
      9. DbDataRecord
      10. DbEnumerator
      11. RowUpdatedEventArgs
      12. RowUpdatingEventArgs
    5. 36. The System.Data.SqlClient Namespace
      1. SqlClientPermission
      2. SqlClientPermissionAttribute
      3. SqlCommand
      4. SqlCommandBuilder
      5. SqlConnection
      6. SqlDataAdapter
      7. SqlDataReader
      8. SqlError
      9. SqlErrorCollection
      10. SqlException
      11. SqlInfoMessageEventArgs
      12. SqlInfoMessageEventHandler
      13. SqlParameter
      14. SqlParameterCollection
      15. SqlRowUpdatedEventArgs
      16. SqlRowUpdatedEventHandler
      17. SqlRowUpdatingEventArgs
      18. SqlRowUpdatingEventHandler
      19. SqlTransaction
    6. 37. The System.Data.OleDb Namespace
      1. OleDbCommand
      2. OleDbCommandBuilder
      3. OleDbConnection
      4. OleDbDataAdapter
      5. OleDbDataReader
      6. OleDbError
      7. OleDbErrorCollection
      8. OleDbException
      9. OleDbInfoMessageEventArgs
      10. OleDbInfoMessageEventHandler
      11. OleDbParameter
      12. OleDbParameterCollection
      13. OleDbPermission
      14. OleDbPermissionAttribute
      15. OleDbRowUpdatedEventArgs
      16. OleDbRowUpdatedEventHandler
      17. OleDbRowUpdatingEventArgs
      18. OleDbRowUpdatingEventHandler
      19. OleDbSchemaGuid
      20. OleDbTransaction
      21. OleDbType
    7. 38. The System.Data.SqlTypes Namespace
      1. INullable
      2. SqlBinary
      3. SqlBoolean
      4. SqlByte
      5. SqlCompareOptions
      6. SqlDateTime
      7. SqlDecimal
      8. SqlDouble
      9. SqlGuid
      10. SqlInt16
      11. SqlInt32
      12. SqlInt64
      13. SqlMoney
      14. SqlNullValueException
      15. SqlSingle
      16. SqlString
      17. SqlTruncateException
      18. SqlTypeException
  6. IV. Appendixes
    1. A. ADO.NET Providers
      1. The SQL Server Provider
      2. The OLE DB Provider
      3. The ODBC .NET Provider
      4. The Oracle .NET Provider
      5. The ODP.NET Provider
    2. B. ADO.NET XML Extensions
      1. codegen Namespace
      2. msdata Namespace
      3. diffgr Namespace
    3. C. Microsoft Data Engine (MSDE)
      1. Installing MSDE
      2. MSDE Essentials
      3. Adding the Northwind Data
      4. Migrating MSDE to SQL Server
    4. Type, Method, Property, and Field Index
  7. Index
  8. About the Authors
  9. Colophon
  10. Copyright