ADO.NET in a Nutshell by Bill Hamilton -- Read -- Imperial Library of Trantor

Index

ADO.NET in a Nutshell A Note Regarding Supplemental Files Preface

Audience Contents of This Book What’s on the CD-ROM Conventions Used in This Book Comments and Questions Acknowledgments

Bill Matthew CD-ROM Acknowledgments

I. ADO.NET Tutorial

1. Introduction

ADO.NET Data Providers Connected and Disconnected Data

Connected Classes Disconnected Classes

2. .NET Data Providers

Data Providers

Microsoft SQL Server OLE DB ODBC Oracle SQLXML Managed Classes Other Providers

Selecting a Data Provider Creating a Custom Data Provider

3. Connections

Connection Object Overview The Connection String

Setting Connection String Parameters

The SQL Server connection string The MSDE connection string The OLE DB connection string The ODBC .NET connection string The Oracle .NET connection string

Security Risks of the Connection String

Opening and Closing Connections

Connection Events Connections and Exception Handling Disposing Connections

Connection Pooling

SQL Server and Oracle Connection Pooling OLE DB and ODBC Connection Pooling Connection Pooling and Application Domains Connection Pooling and Performance Counters

4. Commands

Command Object Overview Creating and Executing a Command

Executing a Command That Doesn’t Return Rows

Updating a record Deleting a record Inserting a record

Executing a Command That Returns a Single Value

Parameter Object Overview

Creating Parameters

Parameterized Commands Commands with Stored Procedures

Executing a Stored Procedure Output Parameters Stored Procedure Return Values Deriving Parameters

Commands and Data Definition Language (DDL)

5. DataReaders

DataReader Object Overview Performing a Query with a DataReader

Using Column Ordinals Using Typed Accessors Retrieving Null Values Returning Multiple Result Sets Showing All Columns with the DataReader Reading Single Rows with a DataReader Retrieving BLOB Data

Stored Procedures with the DataReader DataReaders and Schema Information

Retrieving Schema Information for a Query Retrieving Schema Tables

Retrieving schema tables with SQL Server Retrieving schema tables with the OLE DB provider

6. DataSets

Creating an Untyped DataSet Working with Tables in the DataSet Adding and Removing Relations Adding Custom Information Cloning the Schema Copying the DataSet Merging Two DataSets Removing All Data Resetting the DataSet Committing and Discarding Changes

AcceptChanges and RejectChanges HasChanges and GetChanges

7. DataTables

Creating a DataTable Working with Columns Constraints Primary Key Rows Loading Data Committing and Discarding Changes Cloning the Schema of the Table Copying the Table Selecting a Subset of Rows Performing Aggregate Calculations Removing All Data Resetting the Table Identifying Errors in the Table DataTable Events

ColumnChanged and ColumnChanging RowChanged, RowChanging, RowDeleted, and RowDeleting

8. DataColumns

Creating DataColumns Creating AutoIncrement Columns Creating Expression Columns Handling Null Values Mapping .NET Data Provider Types to .NET Framework Types

9. DataRows

Creating a DataRow Updating Rows Deleting Rows Using Row State Information Using Row Version Information Accepting or Rejecting Changes to Rows Navigating Parent and Child Rows Using Row Error Information

10. Constraints

Constraint Object Overview The UniqueConstraint

Constraints and FillSchema( )

The ForeignKeyConstraint

Referential Integrity with ForeignKeyConstraint Rules

11. DataRelations

DataRelation Object Overview

DataRelations and Constraints

Navigating Relational Data

Modeling a One-to-Many Relationship Modeling a Many-to-Many Relationship Creating Expression-Based Columns Using Relations

12. DataViews and Data Binding

The DataView and DataViewManager

Binding to a DataView Binding to a DataViewManager

Sorting and Filtering

Sorting with the DataView Filtering by Column

Filter operators Pattern-matching filters Filter-supported functions Aggregate functions and relations in filters

Filtering by Row State Displaying Multiple Views

Accessing Data Through a DataView

Searching a DataView Navigating Relations with a DataView

Windows Data Binding

The CurrencyManager and BindingContext List Binding Single-Value Binding Format and Parse Controlling Navigation Master-Detail Forms Creating New Binding Contexts

ASP.NET Data Binding

List Binding ASP.NET Templated Data Controls The DataList Templates and Styles

13. Strongly Typed DataSets

Creating a Strongly Typed DataSet

Discussion of Underlying Classes

Adding a Row Editing a Row Finding a Row Null Data Navigating Hierarchical Data Annotations

14. DataAdapters

Creating DataAdapter Object Retrieving Data from the Data Source Retrieving Schema Information from the Data Source Updating the Data Source Mapping Tables and Columns AcceptChangesDuringFill ContinueUpdateOnError DataAdapter Events

15. Updating the Data Source

SqlCommandBuilder Class Overview Updating a Data Source Using Command Builder Updating a Data Source Using Custom Logic Refreshing Data After Updating Retrieving Updated Values from the Data Source Updating Data in Related Tables Handling Concurrency Issues Optimization

16. Transactions

Manual Transactions Isolation Levels Savepoints Nested Transactions Transactions Using a DataAdapter Automatic Transactions

17. XML and the DataSet

DataSet XML Methods

Dissecting the DataSet XML Dissecting the DataSet XML Schema XML Write and Read Modes Dissecting the DiffGram

Shaping DataSet XML

Attributes and Elements Relational XML Data

Other .NET XML Classes XmlDataDocument Object Overview

Searching a DataSet with XPath Transforming DataSet XML with XSLT

Using the Data Objects to Edit XML

Inferring XML Structure

SQL Server 2000 XML

The SQLXML Provider Converting to XML on the Client-Side Submitting Direct XPath Queries Batch Updates with the DiffGram

II. ADO.NET Core Classes

18. The Connection Class

Comments/Troubleshooting Properties Reference

ConnectionString ConnectionTimeout Database DataSource Provider [OLE DB only] PacketSize [SQL Server only] ServerVersion State WorkstationId [SQL Server only]

Methods Reference

BeginTransaction ChangeDatabase Close CreateCommand EnlistDistributedTransaction GetOleDbSchemaTable [OLE DB only] Open ReleaseObjectPool [OLE DB only]

Events Reference

InfoMessage StateChange

19. The Command Class

Comments/Troubleshooting Properties Reference

CommandText CommandTimeout CommandType Connection Transaction UpdatedRowSource

Collections Reference

Parameters

Methods Reference

Cancel CreateParameter ExecuteNonQuery ExecuteReader ExecuteScalar ExecuteXmlReader [SQL Server only] Prepare ResetCommandTimeout

20. The Parameter Class

Comments/Troubleshooting Properties Reference

DbType Direction IsNullable Offset [SQL Server only] OleDbType [OLE DB only] ParameterName Precision Scale Size SourceColumn SourceVersion SqlDbType [SQL Server only] Value

21. The DataReader Class

Comments/Troubleshooting Properties Reference

Depth FieldCount IsClosed HasRows Item RecordsAffected

Methods Reference

Close Get<TypeName> GetDataTypeName GetName GetOrdinal GetSchemaTable GetValue GetValues IsDBNull NextResult Read

22. The DataSet Class

Comments/Troubleshooting Properties Reference

CaseSensitive DataSetName DefaultViewManager EnforceConstraints HasErrors Locale Namespace Prefix

Collections Reference

ExtendedProperties Relations Tables

Methods Reference

AcceptChanges Clear Clone Copy GetChanges GetXml GetXmlSchema HasChanges InferXmlSchema Merge ReadXml ReadXmlSchema RejectChanges Reset WriteXml WriteXmlSchema

Events Reference

MergeFailed

23. The DataTable Class

Comments/Troubleshooting Properties Reference

CaseSensitive DataSet DefaultView DisplayExpression HasErrors Locale MinimumCapacity Namespace Prefix TableName

Collections Reference

ChildRelations Columns Constraints ExtendedProperties ParentRelations PrimaryKey Rows

Methods Reference

AcceptChanges BeginLoadData Clear Clone Compute Copy EndLoadData GetChanges GetErrors ImportRow LoadDataRow NewRow RejectChanges Reset Select

Events Reference

ColumnChanged ColumnChanging RowChanged RowChanging RowDeleted RowDeleting

24. The DataColumn Class

Comments/Troubleshooting Properties Reference

AllowDBNull AutoIncrement AutoIncrementSeed AutoIncrementStep Caption ColumnMapping ColumnName DataType DefaultValue Expression MaxLength Namespace Ordinal Prefix ReadOnly Table Unique

Collections Reference

ExtendedProperties

25. The DataRow Class

Comments/Troubleshooting Properties Reference

HasErrors Item ItemArray RowError RowState Table

Collections Reference

ItemArray

Methods Reference

AcceptChanges BeginEdit CancelEdit ClearErrors Delete EndEdit GetChildRows GetColumnError GetColumnsInError GetParentRow GetParentRows HasVersion IsNull RejectChanges SetColumnError SetParentRow

26. The Constraint Class

Comments/Troubleshooting Properties Reference

AcceptRejectRule [ForeignKeyConstraint only] Columns ConstraintName DeleteRule [ForeignKeyConstraint only] IsPrimaryKey [UniqueKeyConstraint only] RelatedColumns [ForeignKeyConstraint only] RelatedTable [ForeignKeyConstraint only] Table UpdateRule [ForeignKeyConstraint only]

Collections Reference

ExtendedProperties

27. The DataRelation Class

Comments/Troubleshooting Properties Reference

ChildKeyConstraint ChildTable DataSet Nested ParentKeyConstraint ParentTable RelationName

Collections Reference

ChildColumns ExtendedProperties ParentColumns

28. The DataView Class

Comments/Troubleshooting Properties Reference

AllowDelete AllowEdit AllowNew ApplyDefaultSort Count DataViewManager Item RowFilter RowStateFilter Sort Table

Methods Reference

AddNew Delete Find FindRows

Events Reference

ListChanged

29. The DataAdapter Class

Comments/Troubleshooting Properties Reference

AcceptChangesDuringFill ContinueUpdateOnError DeleteCommand InsertCommand MissingMappingAction MissingSchemaAction SelectCommand UpdateCommand

Collections Reference

TableMappings

Methods Reference

Fill FillSchema GetFillParameters Update

Events Reference

FillError RowUpdated RowUpdating

30. The CommandBuilder Class

Comments/Troubleshooting Properties Reference

DataAdapter QuotePrefix QuoteSuffix

Methods Reference

DeriveParameters GetDeleteCommand GetInsertCommand GetUpdateCommand RefreshSchema

31. The Transaction Class

Comments/Troubleshooting Properties Reference

Connection IsolationLevel

Methods Reference

Begin [OLE DB only] Commit Rollback Save [SQL Server only]

III. API Quick Reference

32. How to Use This Quick Reference

Finding a Quick-Reference Entry Reading a Quick-Reference Entry

Type Name, Namespace, Assembly, Type Category, and Flags Description Synopsis

Member availability and flags Functional grouping of members

Class Hierarchy Cross References A Note About Type Names

33. Converting from C# to VB Syntax

General Considerations Classes Structures Interfaces Class, Structure, and Interface Members

Fields Methods Properties Events

Delegates Enumerations

34. The System.Data Namespace

AcceptRejectRule CommandBehavior CommandType ConnectionState Constraint ConstraintCollection ConstraintException DataColumn DataColumnChangeEventArgs DataColumnChangeEventHandler DataColumnCollection DataException DataRelation DataRelationCollection DataRow DataRowAction DataRowChangeEventArgs DataRowChangeEventHandler DataRowCollection DataRowState DataRowVersion DataRowView DataSet DataSysDescriptionAttribute DataTable DataTableCollection DataView DataViewManager DataViewRowState DataViewSetting DataViewSettingCollection DBConcurrencyException DbType DeletedRowInaccessibleException DuplicateNameException EvaluateException FillErrorEventArgs FillErrorEventHandler ForeignKeyConstraint IColumnMapping IColumnMappingCollection IDataAdapter IDataParameter IDataParameterCollection IDataReader IDataRecord IDbCommand IDbConnection IDbDataAdapter IDbDataParameter IDbTransaction InRowChangingEventException InternalDataCollectionBase InvalidConstraintException InvalidExpressionException IsolationLevel ITableMapping ITableMappingCollection MappingType MergeFailedEventArgs MergeFailedEventHandler MissingMappingAction MissingPrimaryKeyException MissingSchemaAction NoNullAllowedException ParameterDirection PropertyAttributes PropertyCollection ReadOnlyException RowNotInTableException Rule SchemaType SqlDbType StateChangeEventArgs StateChangeEventHandler StatementType StrongTypingException SyntaxErrorException TypedDataSetGenerator TypedDataSetGeneratorException UniqueConstraint UpdateRowSource UpdateStatus VersionNotFoundException XmlReadMode XmlWriteMode

35. The System.Data.Common Namespace

DataAdapter DataColumnMapping DataColumnMappingCollection DataTableMapping DataTableMappingCollection DbDataAdapter DBDataPermission DBDataPermissionAttribute DbDataRecord DbEnumerator RowUpdatedEventArgs RowUpdatingEventArgs

36. The System.Data.SqlClient Namespace

SqlClientPermission SqlClientPermissionAttribute SqlCommand SqlCommandBuilder SqlConnection SqlDataAdapter SqlDataReader SqlError SqlErrorCollection SqlException SqlInfoMessageEventArgs SqlInfoMessageEventHandler SqlParameter SqlParameterCollection SqlRowUpdatedEventArgs SqlRowUpdatedEventHandler SqlRowUpdatingEventArgs SqlRowUpdatingEventHandler SqlTransaction

37. The System.Data.OleDb Namespace

OleDbCommand OleDbCommandBuilder OleDbConnection OleDbDataAdapter OleDbDataReader OleDbError OleDbErrorCollection OleDbException OleDbInfoMessageEventArgs OleDbInfoMessageEventHandler OleDbParameter OleDbParameterCollection OleDbPermission OleDbPermissionAttribute OleDbRowUpdatedEventArgs OleDbRowUpdatedEventHandler OleDbRowUpdatingEventArgs OleDbRowUpdatingEventHandler OleDbSchemaGuid OleDbTransaction OleDbType

38. The System.Data.SqlTypes Namespace

INullable SqlBinary SqlBoolean SqlByte SqlCompareOptions SqlDateTime SqlDecimal SqlDouble SqlGuid SqlInt16 SqlInt32 SqlInt64 SqlMoney SqlNullValueException SqlSingle SqlString SqlTruncateException SqlTypeException

IV. Appendixes

A. ADO.NET Providers

The SQL Server Provider The OLE DB Provider The ODBC .NET Provider The Oracle .NET Provider The ODP.NET Provider

B. ADO.NET XML Extensions

codegen Namespace msdata Namespace diffgr Namespace

C. Microsoft Data Engine (MSDE)

Installing MSDE MSDE Essentials Adding the Northwind Data Migrating MSDE to SQL Server

Type, Method, Property, and Field Index

Index About the Authors Colophon Copyright

← Prev
Back
Next →

← Prev
Back
Next →