The Role of ADO.NET Data Providers
C# and the .NET Pl	form, Second Edition
by Andrew Troelsen	ISBN:1590590554

Rather than providing a single set of objects to communicate to a variety of data stores, ADO.NET makes use

Apress © 2003 (1200 pages)

of multiple data providers. Simply put, a data provider is a set of types (within some .NET assembly) that

This comprehensive text starts with a brief overview of the

understand how toC#communicatelanguage ndwiththenaquicklyspecificmovesdatatosourcekey technical. Althoughandthe names of these types will differ among data providers,architecturaleach providerissues forwill.NEThavedevelopers(at minimum). a set of class types that implement some key interfaces defined in the System.Data namespace, specifically IDbCommand, IDbDataAdapter, IDbConnection and IDataReader. As you would guess, these interfaces define the behaviors a managed provider must suppor

Table of Contents

to provide connected and disconnected access to the underlying data store (see Figure 17-1).

C# and the .NET Platform, Second Edition

Introduction

Part

Chapter

Chapter

Part

Chapter

Chapter

Chapter

Chapter

Chapter

Chapter

Part

Chapter
Chapter
Chapter	Programming
Part

Figure 17-1: ADO.NET data providers provide access to a given DBMS.

Chapter 12 - Object Serialization and the .NET Remoting Layer

Chapter 13 - Building a Better Window (Introducing Windows Forms)

To better understand the core functionality of any data provider, let's check out the formal definition of each

Chapter 14 - A Better Painting Framework (GDI+)

interface type (of course, numerous details are to follow).

Chapter 15 - Programming with Windows Forms Controls

Chapter 16 - The System.IO Namespace

Note In version 1.0 of the .NET platform, data providers were termed managed providers. Therefore, if you Chapter 17 are- DatareadingAccessearlierwith ADO.NET.NETliterature (including the first edition of this text), understand that these two

Part Five - WebtermsApplicationsare completelyand XMLsynonymousWeb Serviceswith each other.

Chapter 18 - ASP.NET Web Pages and Web Controls

Chapter 19 - ASP.NET Web Applications

The Role of the IDbConnection and IDbTransaction Interfaces

Chapter 20 - XML Web Services

IndexFirst we have the IDbConnection type, which is implemented by a data provider's connection object. This

Linterfacest of Figuresdefines a set of members used to connect to (and disconnect from) a specific data store, as well as

Listallowingf Tablesyou to obtain the data provider's transactional object, which (surprise, surprise) implements the System.Data.IDbTransaction interface:

public interface System.Data.IDbConnection : IDisposable

{

string ConnectionString { get; set; } int ConnectionTimeout { get; }

string Database { get; } ConnectionState State { get; }

System.Data.IDbTransaction BeginTransaction();

System.Data.IDbTransaction BeginTransaction(System.Data.IsolationLevel il); void ChangeDatabase(string databaseName);

void Close();

System.Data.IDbCommand CreateCommand();

void Open();

}

Chapter 7 - Callback Interfaces, Delegates, and Events

Chapter 6 - Interfaces and Collections

C# and the .NET Platform, Second Edition

As you can see, the overloaded BeginTransaction() method provides access to an IDbTransaction-compatible
by Andrew Troelsen	ISBN:1590590554

type. Using the members defined by this interface, you are able to programmatically interact with a transactiona

Apress © 2003 (1200 pages)

session and the underlying data store:

This comprehensive text starts with a brief overview of the C# language and then quickly moves to key technical and

architectural issues for .NET developers.

public interface System.Data.IDbTransaction : IDisposable

{

IDbConnection Connection { get; }

Table of Contents

IsolationLevel IsolationLevel { get; }

C# and the .NET Platform, Second Edition

void Commit();

Introduction

void Rollback();

Part One - Introducing C# and the .NET Platform

}

Chapter 1 - The Philosophy of .NET

Chapter 2 - Building C# Applications

Part Two - The C# Programming Language

The Role of the IDbCommand, IDbDataParameter, and IDataParameter

Chapter 3 - C# Language Fundamentals ChaptInterfacesr 4 - Object-Oriented Programming with C#

Chapter 5 - Exceptions and Object Lifetime

Next up, we have the IDbCommand interface, which will be implemented by a data provider's command object Like other data access object models, command objects allow programmatic manipulation of SQL statements,

stored procedures, and parameterized queries (note that each parameter object implements the

Chapter 8 - Advanced C# Type Construction Techniques

IDbDataParameter type). In addition, command objects also provide access to the data provider's data reader

Part Three - Programming with .NET Assemblies

type via the overloaded ExecuteReader() method:

Chapter 9 - Understanding .NET Assemblies

Chapter 10 - Processes, AppDomains, Contexts, and Threads

public interface System.Data.IDbCommand : IDisposable

Chapter 11 - Type Reflection, Late Binding, and Attribute-Based Programming

{

Part Four - Leveraging the .NET Libraries

string CommandText { get; set; }

Chapter 12 - Object Serialization and the .NET Remoting Layer int CommandTimeout { get; set; }

Chapter 13 - Building a Better Window (Introducing Windows Forms)

CommandType CommandType { get; set; }

Chapter 14 - A Better Painting Framework (GDI+)

IDbConnection Connection { get; set; }

Chapter 15 - Programming with Windows Forms Controls

IDataParameterCollection Parameters { get; }

Chapter 16 - The System.IO Namespace

IDbTransaction Transaction { get; set; }

Chapter 17 - Data Access with ADO.NET

UpdateRowSource UpdatedRowSource { get; set; }

Part Fivevoid- WebCancel();Applications and XML Web Services

Chapter System18 - ASP.NETDataWeb.IDbDataParameterPages nd Web ControlsCreateParameter();

Chapter int19 - ASPExecuteNonQuery();.NET Web Applications

Chapter System20 - XML.WebDataServices.IDataReader ExecuteReader();

Index System.Data.IDataReader ExecuteReader(System.Data.CommandBehavior behavior)

object ExecuteScalar();

List of Figures

void Prepare();

List of Tables

}

Notice that the Parameters property returns a strongly typed collection that implements

IDataParameterCollection. This interface provides access to a set of IDbDataParametercompliant data types

(e.g., parameter objects):

public interface System.Data.IDbDataParameter : System.Data.IDataParameter

{

byte Precision { get; set; } byte Scale { get; set; }

int Size { get; set; }

}

IDbDataParameter extends the IDataParameter interface to obtain the following additional behaviors:

C# and the .NET Platform, Second Edition

	by Andrew Troelsen	ISBN:1590590554
public interfaceApressSystem.Data.© 2003 (1200 pages) IDataParameter
{	This comprehensive text starts with a brief overview of the
	DbType DbTypeC# language{ get;and thenset;quickly}	moves to key technical and
	ParameterDirectionarchitectural issuesDirectionfor .NET developers.{ get; set; }
	bool IsNullable { get; }
	string ParameterName { get; set; }
Table of stringContentsSourceColumn { get; set; }
C# and theDataRowVersion.NET Platform, SecondSourceVersionEdition		{ get; set; }
Introductionobject Value { get; set; }
Part}	One - Introducing C# and the .NET Platform

Chapter 1 - The Philosophy of .NET

Chapter 2 - Building C# Applications

As you will see, the functionality of the IDbDataParameter and IDataParameter interfaces allow you to represen

Part Two - The C# Programming Language

parameters within a SQL query (as well as stored procedures) via specific ADO.NET parameter objects, rather

Chapter 3 - C# Language Fundamentals than hard-coded strings.

Chapter 4 - Object-Oriented Programming with C#

Chapter 5 - Exceptions and Object Lifetime

The Role of the IDbDataAdapter and IDataAdapter Interfaces

Chapter 6 - Interfaces and Collections

Chapter 7 - Callback Interfaces, Delegates, and Events

Recall that data adapters are used to push and pull DataSets to and from a given data store. Given this, the

Chapter 8 - Advanced C# Type Construction Techniques

IDbDataAdapter interface defines a set of properties that are used to maintain the SQL statements for the

Part Three - Programming with .NET Assemblies

related SELECT, INSERT, UPDATE, and DELETE operations:

Chapter 9 - Understanding .NET Assemblies

Chapter 10 - Processes, AppDomains, Contexts, and Threads

public interface System.Data.IDbDataAdapter : System.Data.IDataAdapter

Chapter 11 - Type Reflection, Late Binding, and Attribute-Based Programming

{

Part Four - Leveraging the .NET Libraries

IDbCommand DeleteCommand { get; set; }

Chapter 12 - Object Serialization and the .NET Remoting Layer

IDbCommand InsertCommand { get; set; }

Chapter 13 - Building a Better Window (Introducing Windows Forms)

IDbCommand SelectCommand { get; set; }

Chapter 14 - A Better Painting Framework (GDI+)

IDbCommand UpdateCommand { get; set; }

Chapter 15 - Programming with Windows Forms Controls

}

Chapter 16 - The System.IO Namespace

Chapter 17 - Data Access with ADO.NET

In addition to these four properties, an ADO.NET data adapter also picks up the behavior defined in the base

Part Five - Web Applications and XML Web Services

interface, IDataAdapter. This interface defines the key function of a data adapter type: the ability to push and

Chapter 18 - ASP.NET Web Pages and Web Controls

pull DataSets between the caller and underlying data store using the Fill() and Update() methods. Also, the

Chapter 19 - ASP.NET Web Applications

IDataAdapter interface allows you to map database column names to a more human-readable display name

Chapter 20 - XML Web Services

via the TableMappings property:

Index

List of Figures

public interface System.Data.IDataAdapter

List of Tables

{

MissingMappingAction MissingMappingAction { get; set; }

MissingSchemaAction MissingSchemaAction { get; set; }

ITableMappingCollection TableMappings { get; }

int Fill(System.Data.DataSet dataSet);

System.Data.DataTable[] FillSchema(System.Data.DataSet dataSet, System.Data.SchemaType schemaType);

System.Data.IDataParameter[] GetFillParameters();

int Update(System.Data.DataSet dataSet);

}

The Role of the IDataReader and IDataRecord Interfaces

The next key interface to be aware of is IDataReader, which (obviously) represents the common behaviors

List of Tables

List of Figures

supported by a given data reader type. When you obtain an IDataReader-compatible type from an ADO.NET

C# and the .NET Platform, Second Edition

data provider, you are able to iterate over the result set using a forward-only, read-only manner.

by Andrew Troelsen

ISBN:1590590554

Apress © 2003 (1200 pages)

public interface System.Data.IDataReader : IDisposable,

This comprehensive text starts with a brief overview of the

System.Data.IDataRecord

C# language and then quickly moves to key technical and

{architectural issues for .NET developers.

int Depth { get; }

bool IsClosed { get; }

Table ofintContentsRecordsAffected { get; }

void Close();

C# and the .NET Platform, Second Edition

System.Data.DataTable GetSchemaTable();

Introduction

bool NextResult();

Part One - Introducing C# and the .NET Platform

bool Read();

Chapter 1 - The Philosophy of .NET

}

Chapter 2 - Building C# Applications

Part Two - The C# Programming Language

Chapter 3 - C# Language Fundamentals

Finally, as you can see, IDataReader extends IDataRecord, which defines an additional set of members that

Chapter 4 - Object-Oriented Programming with C#

allow you to extract out a strongly typed value from the stream, rather than casting the generic System.Object

Chapter 5 - Exceptions and Object Lifetime

retrieved from the data reader's overloaded indexer method:

Chapter 6 - Interfaces and Collections

Chapter 7 - Callback Interfaces, Delegates, and Events

public interface System.Data.IDataRecord

Chapter 8 - Advanced C# Type Construction Techniques

{

Part Three - Programming with .NET Assemblies

int FieldCount { get; }

Chapter 9 - Understanding .NET Assemblies

object this[ string name ] { get; }

Chapter 10 - Processes, AppDomains, Contexts, and Threads object this[ int i ] { get; }

Chapter 11 - Type Reflection, Late Binding, and Attribute-Based Programming bool GetBoolean(int i);

Part Four - Leveraging the .NET Libraries

byte GetByte(int i);

Chapter 12 - Object Serialization and the .NET Remoting Layer

long GetBytes(int i, long fieldOffset, byte[] buffer,

Chapter 13 -intBuildingbufferoffset,a Better Windowint(In roducinglength);Windows Forms)

Chapterchar14 - AGetChar(intBetter Painti g Frameworki); (GDI+)

Chapterlong15 - ProgrammingGetChars(intwi Windowsi, longFormsfieldoffset,Controls char[] buffer,

int bufferoffset, int length);

Chapter 16 - The System.IO Namespace

System.Data.IDataReader GetData(int i);

Chapter 17 - Data Access with ADO.NET

string GetDataTypeName(int i);

Part Five - Web Applications and XML Web Services

DateTime GetDateTime(int i);

Chapter 18 - ASP.NET Web Pages and Web Controls

Decimal GetDecimal(int i);

Chapter 19 - ASP.NET Web Applications

double GetDouble(int i);

Chapter 20 - XML Web Services

Type GetFieldType(int i);

Index

float GetFloat(int i); Guid GetGuid(int i); short GetInt16(int i); int GetInt32(int i); long GetInt64(int i); string GetName(int i);

int GetOrdinal(string name); string GetString(int i); object GetValue(int i);

int GetValues(object[] values); bool IsDBNull(int i);

}

Now, to be sure, a data provider will supply you with other types beyond the classes that implement the key IDataReader, IDbCommand, IDbConnection, and IDbDataAdapter interfaces. Likewise, the classes in question will certainly define additional members beyond the set specified by the related interface type.

Nevertheless, at this point you should have a better idea of the common functionality found among all .NET

C# and the .NET Platform, Second Edition

data providers. Recall that even though the exact names of the implementing types will differ among data by Andrew Troelsen ISBN:1590590554

providers, you are able to program against these types in a similar manner, given the beauty of interface-based

Apress © 2003 (1200 pages)

polymorphism:

This comprehensive text starts with a brief overview of the C# language and then quickly moves to key technical and

architectural issues for .NET developers.	// Implements IDbConnection!
System.Data.OleDb.OleDbConnection c;
System.Data.Odbc.OdbcConnection c2;	// Implements IDbConnection!
System.Data.Oracle.OracleConnection c3;	// Implements IDbConnection!
Table of Contents	// Implements IDbConnection!
System.Data.SqlServerCe.SqlCeConnection c4;
C# and the .NET Platform, Second Edition	// Implements IDbConnection!
System.Data.SqlClient.SqlConnection c5;
Introduction

Part One - Introducing C# and the .NET Platform

Chapter 1 - The Philosophy of .NET

Note Explicit interface implementation (see Chapter 6) is not used by the types implementing these

Chapter 2 - Building C# Applications

ADO.NET-centric interfaces. Therefore, you can call the interface methods directly from an object

Part Two - The C# Programming Language

reference.

Chapter 3 - C# Language Fundamentals

Chapter 4 - Object-Oriented Programming with C#

Chapter 5 - Exceptions and Object Lifetime

Chapter 6 - Interfaces and Collections

Chapter 7 - Callback Interfaces, Delegates, and Events

Chapter 8 - Advanced C# Type Construction Techniques

Part Three - Programming with .NET Assemblies

Chapter 9 - Understanding .NET Assemblies

Chapter 10 - Processes, AppDomains, Contexts, and Threads

Chapter 11 - Type Reflection, Late Binding, and Attribute-Based Programming

Part Four - Leveraging the .NET Libraries

Chapter 12 - Object Serialization and the .NET Remoting Layer

Chapter 13 - Building a Better Window (Introducing Windows Forms)

Chapter 14 - A Better Painting Framework (GDI+)

Chapter 15 - Programming with Windows Forms Controls

Chapter 16 - The System.IO Namespace

Chapter 17 - Data Access with ADO.NET

Part Five - Web Applications and XML Web Services

Chapter 18 - ASP.NET Web Pages and Web Controls

Chapter 19 - ASP.NET Web Applications

Chapter 20 - XML Web Services

Index

List of Figures

List of Tables

UnderstandingC# andthet .ADONET Platform,.NET NamespacesSecond Edition

by Andrew Troelsen

ISBN:1590590554

.NET version 1.1 ships with five data providers out of the box, each of which is logically represented by a

Apress © 2003 (1200 pages)

specific .NET namespace. In addition, ADO.NET defines some common namespaces that are used by all

This comprehensive text starts with a brief overview of the

data provider implementationsC# language and. Tablethen17quickly-1 givesmovesa quickto keyrundowntechnicalof andeach data-centric .NET namespace.

architectural issues for .NET developers.

Table 17-1: ADO.NET Namespaces

TableADO.NETof ContentsNamespace			Meaning in Life
C# and the .NET Platform, Second			Edition
	System.Data		This core namespace defines types that represent tables, rows,
Introduction			columns, constraints, and DataSets. This namespace does not
Part One - Introducing C# and the .NET Platform
Chapter 1			define types to connect to a data source. Rather, it defines the
		- The Philosophy of .NET
			types that represent the data itself.
	Chapter 2	- Building C# Applications
PartSystem.Data.CommonTwo - The C# Programming LanguageThis namespace contains types shared between data providers.
Chapter 3		- C# Language FundamentalsMany of these types function as base classes to the concrete types
	Chapter 4	- Object-Oriented	defined by a given data provider.
			Programming with C#
	Chapter 5	- Exceptions and Object Lifetime
	System.Data.OleDb		This namespace defines the types that allow you to connect to an
	Chapter 6	- Interfaces and Collections
			OLE DB-compliant data source. Typically you will use this
	Chapter 7	- Callback Interfaces, Delegates, and Events
			namespace only if you need to communicate with a data store that
	Chapter 8		does not have a custom data provider.
		- Advanced C# Type Construction T chniques
Part Three - Programming with			.NET Assemblies
	System.Data.Odbc		This namespace defines the types that constitute the ODBC data
Chapter 9		- Understanding .NET	Assemblies
			provider.
	Chapter 10	- Processes, AppDomains, Contexts, and Threads

ChapterSystem11.Data- Type.OracleClientRefl ctio , LateThisBinding,namespaceand Attributedefines-Basedthe Programmingtypes that constitute the Oracle data

Part Four - Leveraging the .NET Libproviderar es .

	Chapter 12 - Object Serialization			and the .NET Remoting Layer
	System.Data.SqlClient			This namespace defines the types that constitute the SQL data
	Chapter 13 - Building a Better		Window (Introducing Windows Forms)
	Chapter 14 - A Better Painting			provider.
				Framework (GDI+)
	ChapterSystem.Data.SqlServerCe15 - Programming with			WindowsThis namespaceForms Controlsdefines the types that constitute the SQL CE data
	Chapter 16 - The System.IO Namespaceprovider.
	Chapter 17	- Data Access with		ADO.NET
	System.Data.SqlTypes			Represents native data types used by Microsoft SQL Server.
Part Five - Web Applications and				XML Web Services
				Although you are always free to use the corresponding CLR data
Chapter 18		- ASP.NET Web Pages		and Web Controls
				types, the SqlTypes are optimized to work with SQL Server.
Chapter 19		- ASP.NET Web Applications

Chapter 20 - XML Web Services

The System.Data, System.Data.Common, System.Data.OleDb, System.Data.SqlClient,

IndexSystem.Data.Odbc, and System.Data.SqlTypes namespaces are all contained within the System.Data.dll

Listassemblyof Figures. However, the types of the System.Data.OracleClient namespaces are contained within a

Liseparatet of Tablesassembly named System.Data.OracleClient.dll, while the SqlCe types are placed within System.Data.Sqlservice.dll. Thus, like any .NET endeavor, be sure to set the correct external references (and C# "using" statements) for your current project.