Programming Microsoft® ADO.NET 2.0 Core Reference, 2nd Edition

Programming Microsoft® ADO.NET 2.0 Core Reference, 2nd Edition

by David Sceppa
Released August 2006
Publisher(s): Microsoft Press
ISBN: 9780735622067

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Get a practical introduction to the Microsoft .NET Framework 2.0 libraries (ADO.NET 2.0) that communicate, access, sort, and interact with data from .NET-connected applications. Includes coverage of XML data and Microsoft SQL Server 2005.

Table of contents

  1. Programming Microsoft® ADO.NET 2.0 Core Reference
  2. A Note Regarding Supplemental Files
  3. Acknowledgments
  4. Introduction
    1. Who This Book Is For
    2. How This Book Is Organized
    3. System Requirements
    4. Configuring SQL Server 2005 Express Edition
    5. Code Samples
    6. Support for This Book
      1. Questions and Comments
  5. I. Getting Started with Microsoft ADO.NET 2.0
    1. 1. Overview of ADO.NET
      1. No New Object Model?!?
      2. The ADO.NET Object Model
        1. .NET Data Providers
        2. Why Use Separate Classes and Libraries?
          1. Better Performance
          2. Greater Extensibility
          3. Proliferation
        3. Coverage of .NET Data Providers in This Book
        4. Connected Objects
          1. ProviderFactory Class
          2. Connection Class
          3. ConnectionStringBuilder Class
          4. Command Class
          5. DataReader Class
          6. Transaction Class
          7. Parameter Class
          8. DataAdapter Class
        5. Disconnected Classes
          1. DataTable Class
          2. DataColumn Class
          3. Constraint Class
          4. DataRow Class
          5. DataSet Class
          6. DataRelation Class
          7. DataView Class
        6. Metadata
        7. Strongly Typed DataSet Classes
      3. Questions That Should Be Asked More Frequently
    2. 2. Building Your First ADO.NET Application with Microsoft Visual Studio 2005
      1. Everyone Loves a Demo
        1. The Data Form Wizard Has Been Deprecated
      2. Creating Your Data Access Form Without Code
        1. Create the New Project
        2. Add a New Data Source
          1. Choose Your Connection
          2. Connect to Database
            1. How Does This Relate to ADO.NET?
            2. Connecting to Other Types of Databases
          3. Saving the Connection String
          4. Select Tables and Columns to Display
            1. How Does This Relate to ADO.NET?
          5. What Did the Data Source Configuration Wizard Create?
            1. A New Project Item
            2. A New Connection in Server Explorer
            3. A New Data Source
        3. Using the Data Source to Add Items to the Form
          1. Adding the First Item to the Form
            1. Label and TextBox
            2. Navigation Control
            3. Strongly Typed DataSet
            4. BindingSource
            5. TableAdapter
          2. Adding Other Items to the Form
        4. Running the Project
          1. Customer Data Appears
          2. Navigate Through the Available Customers
          3. Modify Data and Submit Changes
          4. Verifying Your Changes Were Submitted
        5. Adding Related Data to the Form
          1. Drag Orders onto the Form
          2. What Was Added?
          3. Re-Run the Project
          4. Modifying Order Information
        6. Examining the Code Generated by Visual Studio
          1. Load Event
          2. SaveItem Click Event
        7. You Can Write Better Data-Access Code Than the Designers!
          1. Load Event
          2. SaveItem Click Event
      3. Questions That Should Be Asked More Frequently
  6. II. Getting Connected: Using a .NET Data Provider
    1. 3. Connecting to Your Database
      1. Creating SqlConnection Objects
      2. Opening SqlConnection Objects
      3. Closing SqlConnection Objects
      4. Cleaning Up After Yourself
      5. Connection Strings
        1. What Is a Connection String?
          1. Connecting to the Local Default Instance of SQL Server
          2. Connecting to a Named Instance
          3. Specifying an Initial Catalog
          4. Connecting Using a Specific User Name and Password
          5. Connecting Using Integrated Security
        2. Introducing Connection String Builders
          1. Using a Connection String Builder
          2. Connection Strings, Meet IntelliSense; IntelliSense, Meet Connection Strings
          3. Handling Complex Connection String Option Values
          4. Malicious Connection String Input, Also Known as Connection String Injection
          5. Preventing Connection String Injection with Connection String Builders
          6. Recognizing Keyword Aliases
          7. Creating a Connection-String Dialog Box with a Connection-String Builder
        3. Connection String Security
          1. Using the SqlClientPermission Class to Restrict Data Access
          2. Combining Permissions
          3. Synonyms for Connection String Keywords
          4. Scoping Permissions
          5. Enforcing Connection String Security Through Procedure Attributes
      6. Connection Pooling
        1. Connection Handles and Physical Connections
        2. What Is Connection Pooling?
        3. How Connection Pooling Can Improve Your Code
        4. Enabling Connection Pooling
        5. When Will My Pooled Connection Be Closed?
        6. Disabling Connection Pooling
        7. Answering Your Own Questions About Connection Pooling
        8. How ADO.NET Determines Whether to Use a Pooled Connection
        9. Forcing ADO.NET to Use a New Pool
        10. Manually Releasing Pooled Connections
        11. What Other Pooling Options Do I Have?
          1. Connection Reset
          2. Min Pool Size
          3. Max Pool Size
      7. Using the SqlConnection as a Starting Point
        1. Creating SqlCommands
        2. Starting SqlTransactions
        3. Retrieving Schema Information
      8. Visual Studio Design-Time Features
        1. Working with Connections in Server Explorer
        2. Adding a Data Connection to Server Explorer
        3. What Happened to the Drag-and-Drop Features?
      9. SqlConnection Object Reference
        1. Properties of the SqlConnection Class
          1. ConnectionString Property
          2. ConnectionTimeout Property
          3. Database and DataSource Properties
          4. FireInfoMessageEventOnUserErrors Property
          5. PacketSize Property
          6. ServerVersion Property
          7. State Property
          8. WorkstationId Property
        2. Methods of the SqlConnection Class
          1. BeginTransaction Method
          2. ChangeDatabase Method
          3. ClearPool and ClearAllPools Methods
          4. Close Method
          5. CreateCommand Method
          6. EnlistDistributedTransaction Method
          7. EnlistTransaction Method
          8. GetSchema Method
          9. Open Method
          10. RetrieveStatistics and ResetStatistics Methods
        3. Events of the SqlConnection Class
          1. InfoMessage Event
          2. StateChange Event
      10. Questions That Should Be Asked More Frequently
    2. 4. Querying Your Database
      1. Using SqlCommand Objects in Code
        1. Creating a SqlCommand Object
        2. Using a SqlCommand to Execute Queries
        3. Executing a Row-Returning Query
        4. Retrieving a Single Value
        5. Executing a Query That Does Not Return a Resultset
        6. Executing Batches of Action Queries
        7. Executing a Query to Retrieve XML Data
        8. Executing a Query in a Transaction
        9. Executing a Query Asynchronously
          1. Execute a Query Asynchronously and Wait for Results
          2. Execute Multiple Queries Asynchronously and Wait for One to Complete
          3. Passing Additional State to the Begin Method
          4. Executing a Query Asynchronously with a Callback Function
            1. Asynchronous queries and Windows applications
      2. Working with the SqlDataReader
        1. Examining the Results of Your Query
        2. Closing Your SqlDataReader
        3. Examining the Schema of Your Resultset
          1. Determining the Number of Available Fields
          2. Determining the Number of Rows Returned
          3. Determining the Name of the Field
          4. Determining the .NET Data Type for a Field
          5. Determining the Database Data Type for a Field
          6. Determining the Ordinal for a Field
          7. Additional Resultset Schema Information
          8. Using CommandBehavior
            1. SchemaOnly
            2. KeyInfo
        4. Fetching Data Faster with Ordinal-Based Lookups
        5. Strongly Typed Getters
        6. Handling Null Values
          1. DBNull.Value
          2. Using the IsDBNull Method
        7. SqlTypes
        8. Handling Multiple Resultsets from a Query
        9. SQL Server 2005 and Multiple Active Resultsets
          1. Life before MARS
            1. Use an off-line data cache
            2. Use server-side cursors
            3. Perform the work on another connection
          2. Enabling MARS
          3. Why Is MARS Disabled by Default?
          4. Should I Use MARS?
          5. Using MARS
      3. Working with Parameterized Queries
        1. Formatting User Input in a Query String
        2. Query Construction and SQL Injection
        3. Parameterized Queries
          1. Parameter Data Types
            1. Inferring the data type
            2. Explicitly setting the data type
          2. Parameter Direction
        4. Stored Procedures
          1. Populating the Parameters Collection
      4. Microsoft Visual Studio Design-Time Features
        1. What Happened to the Drag-and-Drop Features?
      5. Object Reference for SqlCommand, SqlDataReader, and SqlParameter
        1. Properties of the SqlCommand Class
          1. CommandText Property
          2. CommandTimeout Property
          3. CommandType Property
          4. Connection Property
          5. Notification Property
          6. NotificationAutoEnlist Property
          7. Parameters Property
          8. Transaction Property
          9. UpdatedRowSource Property
        2. Methods of the SqlCommand Class
          1. BeginExecuteReader, BeginExecuteNonQuery, and BeginExecuteXmlReader Methods
          2. Cancel Method
          3. Clone Method
          4. CreateParameter Method
          5. EndExecuteReader, EndExecuteNonQuery, and EndExecuteXml Reader Methods
          6. ExecuteNonQuery Method
          7. ExecuteReader Method
          8. ResetCommandTimeout Method
            1. CloseConnection
            2. KeyInfo and SchemaOnly
            3. SequentialAccess
            4. SingleRow and SingleResult
          9. ExecuteScalar Method
          10. Prepare Method
        3. Event of the SqlCommand Class
          1. StatementCompleted Event
        4. Properties of the SqlDataReader Class
          1. Depth Property (and GetData Method)
          2. FieldCount Property
          3. HasRows Property
          4. IsClosed Property
          5. Item Property
          6. RecordsAffected Property
        5. Methods of the SqlDataReader Class
          1. Read Method
          2. GetValue, GetSqlValue, and GetProviderSpecificValue Methods
          3. Get<DataType> Methods
          4. GetValues, GetSqlValues, and GetProviderSpecificValues Methods
          5. NextResult Method
          6. Close Method
          7. GetName, GetOrdinal, and GetDataTypeName Methods
          8. GetFieldType and GetProviderSpecificFieldType Methods
          9. GetSchemaTable Method
          10. GetData Method (and Depth Property)
        6. Creating SqlParameter Objects
        7. Properties of the SqlParameter Class
          1. ParameterName Property
          2. Direction Property
          3. Value and SqlValue Properties
          4. SourceColumn, SourceVersion, and SourceColumnNullMapping Properties
          5. DbType and SqlDbType Properties
          6. Precision, Scale, and Size Properties
      6. Questions That Should Be Asked More Frequently
    3. 5. Retrieving Data Using SqlDataAdapter Objects
      1. What Is a SqlDataAdapter Object?
        1. How the SqlDataAdapter Differs from Other Query Objects
          1. The SqlDataAdapter Is Designed to Work with Disconnected Data
          2. There Is No Direct Connection Between the DataAdapter and the DataSet
          3. The SqlDataAdapter Contains the Updating Logic to Submit Changes Stored in Your DataSet Back to Your Database
          4. You Control the Updating Logic in the SqlDataAdapter
        2. Anatomy of the SqlDataAdapter Class
          1. Child Commands
          2. TableMappings Collection
      2. Creating and Using SqlDataAdapter Objects
        1. Creating a SqlDataAdapter
          1. SqlDataAdapter Constructors
        2. Retrieving Results from a Query
          1. Using the Fill Method
          2. Retrieving Results Using SqlTypes
          3. Creating DataTable and DataColumn Objects Using the Fill Method
          4. Using Overloaded Fill Methods
            1. Specifying the DataTable
            2. Paging with the SqlDataAdapter class’s Fill method
          5. Opening and Closing Connections
          6. Making Multiple Calls to the Fill Method
        3. Mapping the Results of Your Query to Your DataSet
          1. The SqlDataAdapter Class’s TableMappings Collection
          2. The MissingMappingAction Property
        4. Working with Batch Queries
        5. Retrieving Rows from a Stored Procedure
        6. Fetching Schema Information
          1. The MissingSchemaAction Property
          2. The FillSchema Method
      3. Visual Studio 2005 Design-Time Features
        1. What Happened to the Drag-and-Drop Features?
      4. SqlDataAdapter Reference
        1. Properties of the SqlDataAdapter Class
          1. SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand
          2. TableMappings Property
          3. MissingMappingAction and MissingSchemaAction Properties
          4. AcceptChangesDuringFill and AcceptChangesDuringUpdate Properties
          5. ContinueUpdateOnError Property
          6. ReturnProviderSpecificTypes Property
          7. FillLoadOption Property
          8. UpdateBatchSize Property
        2. Methods of the SqlDataAdapter Class
          1. Fill Method
          2. FillSchema Method
          3. GetFillParameters Method
          4. Update Method
        3. Events of the SqlDataAdapter Class
          1. FillError Event
          2. RowUpdating and RowUpdated Events
      5. Questions That Should Be Asked More Frequently
  7. III. Working with Data Offline—The ADO.NET DataSet
    1. 6. Working with DataSet Objects
      1. Features of the DataSet Class
        1. Working with Disconnected Data
        2. Scrolling, Sorting, Searching, and Filtering
        3. Working with Hierarchical Data
        4. Caching Changes
        5. XML Integration
        6. Uniform Functionality
      2. Using DataSet Objects
        1. Creating a DataSet Object
        2. Examining the Structure Created by Calling SqlDataAdapter.Fill
          1. DataTable Objects
          2. DataColumn Objects
        3. Examining the Data Returned by a SqlDataAdapter
          1. DataRow Objects
          2. Examining the Data Stored in a DataRow
          3. Examining the DataRow Objects in a DataTable
        4. Validating Data in Your DataSet
          1. Validation Properties of the DataColumn
          2. The DataTable Class’s Constraints Collection
          3. Retrieving Schema Information Using SqlDataAdapter.FillSchema
        5. Creating DataTable Objects in Code
          1. Creating a DataTable Object
          2. Adding Your DataTable to a DataSet Object’s Tables Collection
          3. Adding Columns to Your DataTable
          4. Specifying a Data Type for a DataColumn
          5. Adding a Primary Key
          6. Adding Other Constraints
          7. Working with Autoincrement Columns
        6. Autoincrement Do’s and Don’ts
          1. Adding an Expression-Based Column
          2. Creating DataTable Objects for the Customers, Orders, and Order Details Tables
        7. Modifying the Contents of a DataTable
          1. Adding a New DataRow
          2. Modifying an Existing Row
          3. Working with Null Values in a DataRow
          4. Deleting a DataRow
          5. Removing a DataRow
          6. Using the DataRow.RowState Property
          7. Controlling the RowState of your DataRow
          8. Examining the Pending Changes in a DataRow
        8. ADO.NET 2.0 DataSet Serialization and Remoting Options
          1. Serializing and Remoting DataTables
          2. DataColumn.DateTimeMode
          3. DataSet.SchemaSerializationMode
          4. DataSet.RemotingFormat
      3. Working with DataSet Objects in Visual Studio
        1. Creating Strongly Typed DataSets in Visual Studio
        2. Creating an Untyped DataSet
      4. DataSet, DataTable, DataColumn, DataRow, UniqueConstraint, and ForeignKeyConstraint Class Reference
        1. Properties of the DataSet Class
          1. CaseSensitive Property
          2. DataSetName Property
          3. DefaultViewManager Property
          4. DesignMode Property
          5. EnforceConstraints Property
          6. ExtendedProperties Property
          7. HasErrors Property
          8. IsInitialized Property
          9. Locale Property
          10. Namespace and Prefix Properties
          11. Relations Property
          12. RemotingFormat Property
          13. SchemaSerializationMode Property
          14. Tables Property
        2. Methods of the DataSet Class
          1. AcceptChanges and RejectChanges Methods
          2. BeginInit and EndInit Methods
        3. DataSet, DataTables, and the ISupportInitializeNotification Interface
          1. Clear Method
          2. Clone and Copy Methods
          3. CreateDataReader Method
          4. GetChanges Method
          5. GetXml and GetXmlSchema Methods
          6. HasChanges Method
          7. Load Method
          8. Merge Method
          9. ReadXml and WriteXml Methods
          10. ReadXmlSchema, WriteXmlSchema, and InferXmlSchema Methods
          11. Reset Method
        4. Events of the DataSet Class
          1. Initialized Event
          2. MergeFailed Event
        5. Properties of the DataTable Class
          1. CaseSensitive Property
          2. ChildRelations and ParentRelations Properties
          3. Columns Property
          4. Constraints Property
          5. DataSet Property
          6. DefaultView Property
          7. DesignMode Property
          8. ExtendedProperties Property
          9. HasErrors Property
          10. IsInitialized Property
          11. Locale Property
          12. MinimumCapacity Property
          13. Namespace and Prefix Properties
          14. PrimaryKey Property
          15. RemotingFormat Property
          16. Rows Property
          17. TableName Property
        6. Methods of the DataTable Class
          1. AcceptChanges and RejectChanges Methods
          2. BeginInit and EndInit Methods
          3. BeginLoadData and EndLoadData Methods
          4. Clear Method
          5. Clone and Copy Methods
          6. Compute Method
          7. CreateDataReader Method
          8. GetChanges Method
          9. GetErrors Method
          10. ImportRow, LoadDataRow, and NewRow Methods
          11. Load Method
          12. ReadXml, ReadXmlSchema, WriteXml, and WriteXmlSchema Methods
          13. Reset Method
          14. Select Method
        7. Events of the DataTable Class
          1. ColumnChanged and ColumnChanging Events
          2. Initialized Event
          3. RowChanged and RowChanging Events
          4. RowDeleted and RowDeleting Events
          5. TableClearing and TableCleared Events
          6. TableNewRow Event
        8. Properties of the DataColumn Class
          1. AllowDBNull Property
          2. AutoIncrement, AutoIncrementSeed, and AutoIncrementStep Properties
          3. Caption Property
          4. ColumnMapping Property
          5. ColumnName Property
          6. DataType Property
          7. DateTimeMode Property
          8. DefaultValue Property
          9. Expression Property
          10. ExtendedProperties Property
          11. MaxLength Property
          12. Namespace and Prefix Properties
          13. Ordinal Property
          14. ReadOnly Property
          15. Table Property
          16. Unique Property
        9. Properties of the DataRow Class
          1. HasErrors Property
          2. Item Property
          3. ItemArray Property
          4. RowError Property
          5. RowState Property
          6. Table Property
        10. Methods of the DataRow Class
          1. AcceptChanges and RejectChanges Methods
          2. BeginEdit, CancelEdit, and EndEdit Methods
          3. ClearErrors Method
          4. Delete Method
          5. GetChildRows Method
          6. GetColumnError and SetColumnError Methods
          7. GetColumnsInError Method
          8. GetParentRow, GetParentRows, and SetParentRow Methods
          9. HasVersion Method
          10. IsNull Method
          11. SetAdded and SetModified Methods
        11. Properties of the UniqueConstraint Class
          1. Columns Property
          2. ConstraintName Property
          3. ExtendedProperties Property
          4. IsPrimaryKey Property
          5. Table Property
        12. Properties of the ForeignKeyConstraint Class
          1. AcceptRejectRule, DeleteRule, and UpdateRule Properties
          2. Columns and RelatedColumns Properties
          3. ConstraintName Property
          4. ExtendedProperties Property
          5. RelatedTable and Table Properties
      5. Questions That Should Be Asked More Frequently
    2. 7. Working with Relational Data
      1. A Brief Overview of Relational Data Access
        1. Join Queries
        2. Separate Queries
        3. Hierarchical ADO Recordset Objects
        4. ADO.NET DataRelation Objects
      2. Working with DataRelation Objects in Code
        1. Creating DataRelation Objects
        2. Locating Related Data
          1. The DataRow Class’s GetChildRows Method
          2. The DataRow Class’s GetParentRow Method
          3. Using the DataRow Class’s GetParentRows Method to Access Multiple Parent Rows
          4. Choosing the Version of Data to View
        3. Using DataRelation Objects to Validate Your Data
          1. Creating Constraints
          2. Using Existing Constraints
          3. Look, Ma! No Constraints!
        4. Self-Referencing DataRelationship Objects
        5. Many-to-Many Relationships
        6. Using DataRelation Objects in Expression-Based DataColumn Objects
        7. Cascading Changes
          1. The ForeignKeyConstraint Class’s DeleteRule and UpdateRule Properties
        8. Moving Away from Join Queries
      3. Creating DataRelation Objects in Microsoft Visual Studio
        1. Adding a DataRelation to a Strongly Typed DataSet
        2. Adding a DataRelation to an Untyped DataSet
      4. DataRelation Object Reference
        1. Properties of the DataRelation Class
          1. ChildColumns Property
          2. ChildKeyConstraint Property
          3. ChildTable Property
          4. DataSet Property
          5. ExtendedProperties Property
          6. Nested Property
          7. ParentColumns Property
          8. ParentKeyConstraint Property
          9. ParentTable Property
          10. RelationName Property
      5. Questions That Should Be Asked More Frequently
    3. 8. Sorting, Searching, and Filtering
      1. Using the DataTable Class’s Searching and Filtering Features
        1. Locating a Row by Its Primary Key Values
        2. Conducting More Dynamic Searches
        3. Conducting Wildcard Searches
        4. Working with Delimiters
        5. Using the Additional Select Methods
          1. Including a Sort Order
          2. Specifying the RowState of the Rows to Search
      2. What Is a DataView Object?
        1. DataView Objects Return Data from a DataTable
        2. DataView Objects Are Not SQL Queries
      3. Working with DataView Objects in Code
        1. Creating DataView Objects
        2. Using the RowStateFilter Property
        3. Using the DataRowView Class
        4. Examining All Rows of Data Available Through a DataView
        5. Searching for Data in a DataView
          1. The Find Method
          2. The FindRows Method
        6. Modifying DataRowView Objects
        7. Using a DataView to Create a New DataTable
      4. Creating DataView Objects in Microsoft Visual Studio
        1. Adding a New DataView Object to Your Designer
        2. Setting Properties of Your DataView Object
      5. DataView Object Reference
        1. AllowDelete, AllowEdit, and AllowNew Properties
        2. ApplyDefaultSort Property
        3. Count and Item Properties
        4. DataViewManager Property
        5. IsInitialized Property
        6. RowFilter Property
        7. RowStateFilter Property
        8. Sort Property
        9. Table Property
        10. Methods of the DataView Class
          1. AddNew and Delete Methods
          2. BeginInit and EndInit Methods
          3. CopyTo Method
          4. Find and FindRows Methods
          5. ToTable Method
        11. Events of the DataView Class
          1. Initialized Event
          2. ListChanged Event
        12. Properties of the DataRowView Class
          1. DataView Property
          2. IsEdit and IsNew Properties
          3. Item Property
          4. Row Property
          5. RowVersion Property
        13. Methods of the DataRowView Class
          1. BeginEdit, CancelEdit, and EndEdit Methods
          2. CreateChildView Method
          3. Delete Method
      6. Questions That Should Be Asked More Frequently
    4. 9. Working with Strongly Typed DataSet Objects and TableAdapters
      1. Strongly Typed DataSets
      2. Creating Strongly Typed DataSet Objects
        1. The Hard Way
          1. Using the DataSet Class’s WriteXmlSchema Method
          2. Using the XML Schema Definition Tool
        2. The Easy Way
          1. Where Is the Class File?
      3. Using Strongly Typed DataSet Objects
        1. Adding a Row
        2. Finding a Row
        3. Editing a Row
        4. Working with Null Data
        5. Working with Hierarchical Data
        6. Other DataSet, DataTable, and DataRow Features
        7. Adding Your Own Code
      4. When to Use Strongly Typed DataSet Objects
        1. Software Components and Pocket Knives
        2. Design-Time Benefits
        3. Run-Time Benefits
        4. Additional Considerations
          1. Making Structural Changes
          2. Converting DataSet Objects
          3. Untyped Features of Strongly Typed DataSet Objects
        5. Manually Adding Tables and Columns
          1. Creating a DataTable Manually
          2. Adding DataColumns
          3. Setting Properties on a DataColumn
          4. Setting the DataTable’s PrimaryKey Property
          5. Adding DataRelations
        6. Improving on the DataSets (De)Faults
          1. Associate Autogenerated DataRelations with ForeignKeyConstraints
          2. Force Auto-Increment Columns to Generate Negative Placeholder Values
      5. Introducing TableAdapters
        1. Creating a TableAdapter
          1. Drag and Drop from Server Explorer
          2. Adding a TableAdapter from the Visual Studio Menu
        2. Using a TableAdapter
          1. Fill (and ClearBeforeFill)
            1. The TableAdapter’s ClearBeforeFill Property
          2. GetData
          3. Controlling the TableAdapter’s Connection
          4. Update
          5. Using the DBDirect Methods
          6. BaseClass
        3. Adding More Queries
        4. Adding Your Own Code
        5. TableAdapter Limitations
          1. A Component, Not a DbDataAdapter
          2. Row Refresh Logic Not Compatible with Batch Updates
      6. Choosing Your Path
      7. Questions That Should Be Asked More Frequently
    5. 10. Submitting Updates to Your Database
      1. Submitting Updates Using Parameterized SqlCommands
        1. Submitting a New Row
        2. Updating an Existing Row
          1. Modifying Values in the SET Clause
          2. Key Columns in the WHERE Clause
          3. Multiple SqlParameters for a Column
          4. Additional Concurrency Checks
            1. Alternative Concurrency Options
          5. Determining the Success or Failure of Your Update Attempt
            1. Additional Logic: @@ROWCOUNT and NOCOUNT
          6. Working with Null Values
        3. Deleting an Existing Row
        4. What More Could You Need?
      2. Submitting Updates Using a SqlDataAdapter
        1. Create a procedure (or function) that accepts multiple DataRows
        2. Look for DataRows that contain pending changes
        3. Access a SqlCommand that contains the updating logic that corresponds to the DataRow’s RowState property
        4. Assign values from the DataRow’s columns to the Value property on the corresponding SqlParameters
          1. SqlParameter.SourceColumn
          2. SqlParameter.SourceVersion
          3. SqlParameter.SourceColumnNullMapping
          4. Execute the SqlCommand
          5. Check the number of records affected and indicate failure scenarios, possibly by raising an exception
          6. Call the DataRow’s AcceptChanges method if submitting the pending change in the DataRow succeeded
          7. Events Before and After Update Attempts
          8. The Ability to Assign Retrieved Values Back to DataRows
          9. Batch Updates
      3. Using SqlDataAdapter Objects to Submit Updates
      4. Manually Configuring Your SqlDataAdapter Objects
        1. Bound Parameters
        2. Using Stored Procedures to Submit Updates
        3. Supplying Your Own Updating Logic
          1. Benefits
          2. Drawbacks
      5. Using a SqlCommandBuilder to Generate Updating Logic
        1. How the CommandBuilder Generates Updating Logic
        2. Concurrency Options Using the SqlCommandBuilder
        3. Benefits and Drawbacks of Using the SqlCommandBuilder
      6. Using the Visual Studio TableAdapter Configuration Wizard to Generate Updating Logic
        1. Examining the TableAdapter’s Updating Logic
        2. Options for Building Updating Logic
        3. Using Stored Procedures to Submit Updates
        4. Benefits and Drawbacks of Using the Wizard
      7. Return of the DataAdapters!
        1. Invoking the Data Adapter Configuration Wizard
        2. Creating a Simple Data Access Layer with SqlDataAdapters
          1. Adding DataAdapters (and a Connection)
          2. Create a Strongly Typed DataSet
          3. Add Code to Your Data-Access Layer Component
      8. Submitting Updates in SqlTransactions
        1. Using the TableMappings Collection
        2. The Best Way to Update
      9. SqlCommandBuilder Object Reference
        1. Properties of the SqlCommandBuilder Class
          1. ConflictOption
          2. DataAdapter
          3. SetAllValues
        2. Methods of the SqlCommandBuilder Class
          1. DeriveParameters
          2. GetDeleteCommand, GetInsertCommand, and GetUpdateCommand
          3. QuoteIdentifier and UnquoteIdentifier
          4. RefreshSchema
      10. Questions That Should Be Asked More Frequently
    6. 11. Advanced Updating Scenarios
      1. Refreshing a Row After Submitting an Update
        1. Refreshing the Contents of a DataRow After Submitting Changes
        2. Using Batch Queries to Retrieve Data After You Submit an Update
          1. The SqlCommand Class’s UpdatedRowSource Property
          2. Improving the Batch Query
        3. Retrieving New Data Using Output Parameters
        4. Using the SqlDataAdapter Class’s RowUpdated Event to Retrieve Data After You Submit an Update
        5. RefreshAfterUpdate Sample Code
      2. Retrieving Newly Generated Auto-Increment Values
        1. Working with SQL Server
          1. SCOPE_IDENTITY() vs. @@IDENTITY
        2. Working with Access Databases
        3. Working with Oracle Sequences
          1. Generating Placeholder Values for Your Sequence DataColumn Objects
        4. Sample Applications That Retrieve Auto-Increment Values
      3. Submitting Hierarchical Changes
        1. Submitting Pending Insertions and Deletions
          1. Using the DataTable Object’s Select Method to Submit Hierarchical Changes
          2. Using the GetChanges Method to Submit Hierarchical Changes
        2. Working with Auto-Increment Values and Relational Data
      4. Isolating and Reintegrating Changes
        1. Saving Bandwidth Using the GetChanges Method
          1. The Merge Method of the DataSet Class
          2. The Merge Method and the RowState Property
          3. The Merge Method and Auto-Increment Values
            1. Purge Before Merge
            2. Changing the Primary Keys in Your DataSet Objects
            3. Reviewing Your Options
        2. Isolating and Reintegrating Changes Sample Code
      5. Handling Failed Update Attempts
        1. Planning Ahead for Conflicts
          1. The SqlDataAdapter Class’s ContinueUpdateOnError Property
        2. Informing the User of Failures
        3. Fetching the Current Contents of Conflicting Rows
        4. If at First You Don’t Succeed. . .
          1. Importing "New Original" Values into a DataRow
        5. The Conflicts Sample Application
      6. Working with Distributed Transactions
        1. Transaction Coordinators and Resource Managers
          1. Two-Phase Commits
        2. Distributed Transactions in the .NET Framework
        3. Database Support for Distributed Transactions
        4. Building Your Components
          1. System.Transactions to the Rescue!
          2. TransactionScope
          3. CommittableTransaction
          4. SQL Server 2005 and Promotable Transactions
        5. System.Transactions Samples
      7. Batch Queries
        1. Using Transactions with Batched Updates
        2. Choosing an Appropriate Value for UpdateBatchSize
        3. Events
        4. Refreshing Rows
        5. Batched Updating Samples
      8. SQL Bulk Copy
        1. Creating a SqlBulkCopy Object
        2. Writing Data to the Server
        3. Mapping Data to the Destination Table
        4. The SqlBulkCopyOptions Enumeration
        5. BulkCopy Sample Code
      9. DataSet Objects and Transactions
        1. DataSet Objects and Transactions Sample Code
      10. When Handling Advanced Updating Scenarios, Use ADO.NET
      11. Questions That Should Be Asked More Frequently
    7. 12. Working with XML Data
      1. Bridging the Gap Between XML and Data Access
      2. Reading and Writing XML Data
        1. The DataSet Class’s XML Methods
          1. GetXml Method
          2. WriteXml and ReadXml Methods
          3. WriteXmlSchema, ReadXmlSchema, and InferXmlSchema Methods
        2. Inferring Schemas
        3. ADO.NET Properties That Affect the Schema of Your XML Document
          1. Names of Elements and Attributes
          2. Choosing Elements or Attributes
          3. Nesting Relational Data
          4. Namespaces and Prefixes
        4. Caching Changes and XML Documents
          1. ADO.NET Diffgrams
          2. New to ADO.NET 2.0: DataTable-level XML Features
      3. DataSet + XmlDocument = XmlDataDocument
        1. Using the XmlDataDocument Class
        2. Accessing Your DataSet as an XML Document
        3. Caching Updates to the XML Document
      4. Using SQL Server 2005’s XML Features
        1. SQL Server 2005’s XML Type
          1. Retrieving XML Data via ADO.NET
          2. Submitting XML Data via ADO.NET
          3. Adding an OrderDetailsAsXml Column to the Northwind Orders Table
          4. Example: Adding the OrderDetailsAsXml Column to the Northwind Orders Table
        2. Executing an XPath Query to Retrieve Data
          1. Using sql:variable to Avoid Potential SQL Injection
          2. Improving Performance of XPath Queries via XML Indexes
          3. Example: Querying the OrderDetailsAsXml Column Using XPath
        3. Retrieving Query Results as XML Using XQuery
          1. Format for the Desired Results
          2. Example: Retrieving Order Information as XML Using XQuery
      5. Retrieving XML Data from SQL Server 2000 via SELECT...FOR XML
        1. Executing a SELECT ... FOR XML Query in SQL Server Query Analyzer
        2. Loading the Results of Your Query into an XmlDocument Object
      6. The SQL XML .NET Data Provider
        1. Using a SqlXmlCommand to Load Data into an XmlDocument
        2. Using a SqlXmlAdapter to Load Data into a DataSet
        3. Working with Template Queries
          1. Executing a Template Query Using a SqlXmlCommand
          2. Parameterized Template Queries
        4. Working with XPath Queries
          1. Adding Schema Information
        5. Applying an XSL Transform
        6. Submitting Updates
          1. SqlXmlCommand Updating Logic
      7. A Simple ADO.NET and XML Sample
        1. Two Paths, One Destination
        2. ADO.NET and XML: A Happy Couple
      8. Questions That Should Be Asked More Frequently
  8. IV. Building Effective Applications with ADO.NET 2.0
    1. 13. Building Effective Microsoft Windows—Based Applications
      1. Building a User Interface Quickly by Using Data Binding
        1. Step 1: Creating Your Strongly Typed DataSet
        2. Step 2: Adding Simple Bound Controls to the Form
          1. Introducing the BindingSource Class
            1. Explicit Creation
            2. Indirection
            3. Navigation Methods
            4. Surfacing Properties from the DataView
          2. Creating a BindingSource
          3. Binding Simple Controls Using the Properties Window
          4. Binding Simple Controls Programmatically
          5. Changing the Control Type Associated with a Column
        3. Step 3: Retrieving Data
        4. Step 4: Navigating Through the Results
          1. What Is a BindingNavigator Control?
          2. How Does It Work?
          3. How Can I Create a BindingNavigator Control at Design Time?
          4. How Can I Create a BindingNavigator Control in Code?
          5. Can I Customize the BindingNavigator?
        5. Step 5: Adding and Deleting Items
          1. Adding Custom Logic to a ToolStripItem’s Click Event
          2. Changing the Behavior of an Item on a BindingNavigator
        6. Step 6: Submitting Changes
          1. Adding Items to a BindingNavigator Control
        7. Step 7: Adding Edit, Accept, and Reject Buttons
        8. Step 8: Viewing Child Data
          1. Adding Order Detail Information to the Strongly Typed DataSet
          2. Adding a Query to the TableAdapter to Retrieve Just the Order Details for a Customer
          3. Adding a DataGridView That Displays Child Data
          4. Changing the TableAdapter.Fill Code in the Form’s Load Event
          5. Submitting Changes to Both Tables
        9. Step 9: Binding a Second Form to the Same Data Source
        10. Step 10: Improving the User Interface
          1. Adding Lookup Functionality Using a ComboBox Control
          2. Adding Expression-Based Calculated Columns
          3. Controlling the Format of Bound Data
        11. Step 11: If You Want Something Done (Just) Right...
        12. Data Binding Summary
      2. Application Design Considerations
        1. Fetching Only the Data You Need
        2. Updating Strategies
          1. Immediate Updates vs. Cached Updates
          2. Re-Fetching Before Allowing Changes
          3. ADO.NET and Pessimistic Locking
        3. Connection Strategies
          1. Connecting and Disconnecting
          2. Connection Pooling
        4. Working with BLOB Data
          1. Delaying BLOB Fetching
          2. Handling BLOBs in DataSet Objects
          3. Handling BLOBs Using DataReader Objects
          4. Binary BLOBs in the Northwind Database
          5. A Sample BLOB Application
        5. User Interfaces Built with ADO.NET Power
      3. Questions That Should Be Asked More Frequently
    2. 14. Building Effective Web Applications
      1. Brief Introduction to Web Applications
        1. ASP.NET Makes Building Web Applications Easier
        2. The Good and Bad of Statelessness
          1. Forgetful Server, Dumb Client
          2. ASP.NET Caching Features
      2. Connecting to Your Database
        1. Connecting with Integrated Security
          1. Impersonating Users
        2. Working with Microsoft Office Access Databases
      3. Challenges Interacting with Databases in ASP.NET 1.0
      4. Introducing Data Source Controls
        1. Displaying Data by Using a SqlDataSource Control
          1. Getting Started
          2. Adding a SqlDataSource Control
          3. Connecting to Your Database
          4. Constructing Your Query
          5. Testing Your Query
          6. Displaying the Query Results on Your Page
        2. Features of the SqlDataSource Control
          1. Code-Free Data Binding
          2. Connection String Management
          3. Integrating External Input in the Query’s WHERE Clause
          4. Submitting Changes
            1. Adding Updating Logic to the SqlDataSource
            2. Enabling Updates in the GridView
          5. Caching
            1. The EnableCaching and EnableViewState Properties
            2. The CacheDuration and CacheExpirationPolicy Properties
            3. Using SQL Server 2005 Query Notifications with the SqlDataSource Control
          6. Linking Multiple SqlDataSources and Multiple Bound Controls
      5. Caching Data Between Roundtrips
        1. The Stateless Approach—Maintaining No State
          1. Benefits to Statelessness
          2. Drawbacks to Statelessness
        2. Caching Data at the Client
          1. Cookies
            1. Benefits to Maintaining State by Using Cookies
            2. Drawbacks to Maintaining State by Using Cookies
          2. Hidden Fields
          3. ViewState
            1. Benefits to Maintaining State in ViewState
            2. Drawbacks to Maintaining State in ViewState
        3. Maintaining State in Your Web Server
          1. Session
            1. Benefits to Maintaining State in Session
            2. Drawbacks to Maintaining State in Session
          2. Application
            1. Benefits to Maintaining State in Application
            2. Drawbacks to Maintaining State in Application
          3. Cache
          4. Output Caching
            1. Benefits to Using Output Caching
            2. Drawbacks to Using Output Caching
        4. Using SQL Server 2005 Notification Services
          1. Using Notification Services from ADO.NET
            1. Enabling Notification Services
            2. The SqlNotificationRequest Class
            3. The SqlDependency Class
            4. SqlDependency and the ASP.NET Cache
            5. The SqlCacheDependency Class
        5. Maintaining State in Your Database
          1. Benefits to Maintaining State in Your Database
          2. Drawbacks to Maintaining State in Your Database
        6. Guidelines for Maintaining State
          1. Storing Data in ViewState
          2. Storing Data in the Application Object
          3. Storing Data in the Session Object
          4. Storing Data in the Database
          5. Using Output Caching
      6. Paging
        1. Paging Features of the GridView Control
          1. AllowPaging
          2. PageSize
          3. PagerSettings
        2. Paging Features of the DataAdapter Classes
        3. Building Queries That Return a Page of Data
          1. Building SQL Server 2005 Page-Specific Queries by Using ROW_NUMBER
          2. Building Page-Specific Oracle Queries
          3. Integrating Custom Paging with the SqlDataSource and GridView Controls
            1. Adding a Query to Retrieve the Number of Pages
            2. Interacting with the Parameters in the SqlDataSource’s Query
            3. Programmatically Adding Links to Other Pages
            4. Adding Updating Logic for Page-Specific Queries in the SqlDataSource
            5. Custom Paging Sample
      7. Questions That Should Be Asked More Frequently
    3. 15. SQL Server 2005 Common Language Runtime Integration
      1. Extending SQL Server the Old Way—Extended Stored Procedures
      2. Extending SQL Server the New Way—CLR Integration
      3. Using Microsoft Visual Studio 2005 to Simplify Building SQL CLR Code
        1. Setting Properties on Your SQL Server Project
        2. Building and Deploying Your Project
        3. Adding Items to Your Project
        4. Testing Your Project Using a SQL Script
      4. SQL CLR Scenarios
        1. Creating a Scalar Function to Validate Data
        2. Creating an Aggregate Function
          1. Adding Information via the SqlUserDefinedAggregate Attribute
            1. IsInvariantToDuplicates
            2. IsInvariantToOrder
            3. Name
          2. Testing Your New Aggregate Function
        3. Querying the Current Database Using the Context Connection
        4. Building SQL CLR Table Valued Functions
        5. Returning Query Results from a Stored Procedure
        6. Returning Data Through Stored Procedure Parameters
          1. Specifying Size Information for Parameters Using the SqlFacet Attribute
      5. Creating a SQL CLR User-Defined Type
        1. What’s in a SQL CLR User-Defined Type?
          1. Testing for Null
          2. Representing the Type as a String
          3. Serializing the Contents
        2. Exposing Methods and Properties on the User-Defined Type
          1. Exposing Methods
          2. Exposing Properties
            1. Deterministic Functions in the SQL CLR
        3. Using Your User-Defined Type in a Client Application
          1. Change Tracking
          2. XML Serialization
      6. Summary
      7. Questions That Should Be Asked More Frequently
  9. V. Appendixes
    1. A. Using Other .NET Data Providers
      1. The Provider Factory Model
        1. Limitations of the ADO.NET Common Interfaces
          1. Interfaces Aren’t Easily Extensible
          2. ADO.NET 1.1 Does Not Provide a Way to Create Instances of Some Classes
          3. ADO.NET 1.1 Offers No Way to Determine the Available .NET Data Providers
        2. How the Provider Factory Model Addresses Previous Limitations
          1. Extending the ADO.NET Model via Abstract Base Classes
          2. Using the DbProviderFactory Class to Create Objects
            1. Accessing Provider Factories
          3. Discovering Available .NET Data Providers
        3. Provider Factory Model Limitations
          1. Many Query Constructs Are Database-Specific
          2. Setting CommandText for Parameterized Queries Might Require Provider-Specific Code
          3. Specifying Parameter Data Types Might Require Provider-Specific Code
        4. Database Schema Discoverability
          1. Querying for Available Schemas
          2. Filter the Data Returned by GetSchema
      2. The ODBC .NET Data Provider
        1. Connecting to Your Database Using an OdbcConnection
        2. Executing Queries Using an OdbcCommand
          1. Examining the Results of a Query Using an OdbcDataReader
          2. Working with Parameterized Queries
          3. Calling a Stored Procedure
        3. Retrieving the Results of a Query Using an OdbcDataAdapter
        4. Retrieving Database Schema Information
          1. Listing Schemas and Restrictions
          2. Returning Filtered Schema Information
      3. The OLE DB .NET Data Provider
        1. Connecting to Your Database Using an OleDbConnection
        2. Executing Queries Using an OleDbCommand
          1. Examining the Results of a Query Using an OleDbDataReader
          2. Working with Parameterized Queries
          3. Calling a Stored Procedure
        3. Retrieving the Results of a Query Using an OleDbDataAdapter
        4. Retrieving Database Schema Information
          1. Listing Schemas and Restrictions
          2. Returning Filtered Schema Information
      4. The Oracle Client .NET Data Provider
        1. Connecting to Your Oracle Database Using an OracleConnection
          1. Pooling Oracle Connections
        2. Executing Queries Using an OracleCommand
          1. Examining Results Using an OracleDataReader
          2. Working with Parameterized Queries
          3. Calling a Stored Procedure
          4. Retrieving Oracle REF Cursors as OracleDataReaders
          5. Retrieving Schema Information for Your Queries
          6. Retrieving Results Using Oracle-Specific Data Types
        3. Using the OracleDataAdapter Class
          1. Retrieving the Results of a Query Using an OracleDataAdapter
          2. Excuting Queries That Return a Page of Rows
          3. Retrieving the Contents of REF Cursors
          4. Batched Updating
          5. Fetching Server-Generated Key Sequence Values
        4. Retrieving Database Schema Information
          1. Listing Schemas and Restrictions
          2. Returning Filtered Schema Information
    2. B. Samples and Tools
      1. Sample .NET Data Provider—DSP
      2. ADO.NET Data Explorer
      3. AdapterSet
    3. C. About the Author
  10. Index
  11. About the Author
  12. Copyright

Product information

  • Title: Programming Microsoft® ADO.NET 2.0 Core Reference, 2nd Edition
  • Author(s): David Sceppa
  • Release date: August 2006
  • Publisher(s): Microsoft Press
  • ISBN: 9780735622067