As mentioned earlier in this chapter, .NET 1.1 ships with five data providers. The first of these is the OleDb

Apress © 2003 (1200 pages)

data provider, which is composed of the types defined in the System.Data.OleDb namespace. The OleDb

This comprehensive text starts with a brief overview of the

provider allows youC#tolanguageaccessanddatathenlocatedquicklyin anymovesdatatostorekey technicalthat supportsand the classic OLE DB protocol. Thus, like with classicrchitecturalADO, youissuesmayforuse.NETthedevelopersADO.NET. data provider to access SQL Server, Oracle, or

MS Access databases. Because the types in the System.Data.OleDb namespace must communicate with unmanaged code (e.g., the OLE DB providers), you need to be aware that a number of .NET to COM

Table of Contents

translations occur behind the scenes, which can affect performance. By and large, this namespace is

C# and the .NET Plaretform,attemptingSeco d Edition

useful when you to communicate with a data source that does not have a specific data

Intrproviderductionassembly.

Part One - Introducing C# and the .NET Platform

The SQL data provider offers direct access to MS SQL Server data stores, and only SQL Server data

Chapter 1 - The Philosophy of .NET

stores (version 7.0 and greater). The System.Data.SqlClient namespace contains the types used by the

Chapter 2 - Building C# Applications

SQL provider and provides the same functionality as the OleDb provider. In fact, for the most part, both

Part Two - The C# Programming Language

namespaces have similarly named items. The key difference is that the SQL provider does not use the

Chapter 3 - C# Language Fundamentals

other providers. Recall that while the exact names of the types will differ between namespaces (for

Partexample,Three -OleDbConnectionProgramming withvs.NET. SqlConnectionAssemblies vs. OdbcConnection), semantically related types can be

Chapttreatedr 9in a- polymorphicUnderstandingmanner.NET Assembliesgiven the IDbCommand, IDbConnection, IDbDataAdapter, and

ChapterIDataReader10 - Procinterfacessses, AppDomains,et al. Contexts, and Threads

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

To begin, we'll examine how to connect to a data source using the OleDb data provider, therefore don't

Part Four - Leveraging the .NET Libraries

forget to specify the proper using directives (recall this data provider is contained within the

Chapter 12 - Object Serialization and the .NET Remoting Layer

System.Data.dll assembly):

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

Chapter 14 - A Better Painting Framework (GDI+)

// Using the OleDb data provider.

Chapter 15 - Programming with Windows Forms Controls

using System.Data;

Chapter 16 - The System.IO Namespace

using System.Data.OleDb;

Chapter 17 - Data Access with ADO.NET

Part Five - Web Applications and XML Web Services

Chapter 18 - ASP.NET Web Pages and Web Controls

Once we have checked out how to interact with a data store using the OleDb data provider, we will see

Chapter 19 - ASP.NET Web Applications

how to make use of the types within the System.Data.SqlClient namespace.

Chapter 20 - XML Web Services

Index

List of Figures

List of Tables

Table 17-12 provides a walkthrough of the core types of the System.Data.OleDb namespace. Note that

Apress © 2003 (1200 pages)

each type sports an "OleDb-" prefix.

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

PartOleDbConnectionOne - Introduci g C# and the .RepresentsNET Platforman open connection to a data source.

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

Working withC# andthetheConnected.NET Platform, LayerSecond Editionof ADO.NET

The first step to take when working with the OleDb data provider is to establish a session with the data source

Apress © 2003 (1200 pages)

using the OleDbConnection type (which, as you recall, implements the IDbConnection interface). Much like the

This comprehensive text starts with a brief overview of the

classic ADO ConnectionC# languageobject,andOleDbConnectionthen quickly m vestypesto keyaretechnicalprovidedandwith a formatted connection string, containing a numberarchitecturalof name/valueissues forpairs.NETseparateddevelopersby.semicolons. This information is used to identify the nam of the machine you wish to connect to, required security settings, the name of the database on that machine, and, most importantly, the name of the OLE DB provider. (See online help for a full description of each

Table of Contents name/value pair.)

C# and the .NET Platform, Second Edition

The connection string may be set using the OleDbConnection.ConnectionString property or including it as a

Introduction

constructor argument. Assume you wish to connect to the Cars database on your local machine using the SQL

Part One - Introducing C# and the .NET Platform

OLE DB provider. The following logic does the trick:

Chapter 1 - The Philosophy of .NET

Chapter 2 - Building C# Applications

Part// TwoBuild- TheaC#connectioProgrammingstringLangua .e

ChapterOleDbConnection3 - C# Languagecn Fundamentals= new OleDbConnection();

cn.ConnectionString = "Provider=SQLOLEDB.1;" +

Chapter 4 - Object-Oriented Programm ng with C#

"User ID=sa;Pwd=;Initial Catalog=Cars;" +

Chapter 5 - Exceptions and Object Lifetime

"Data Source=(local);";

Chapter 6 - Interfaces and Collections

Chapter 7 - Callback Interfaces, Delegates, and Events

Chapter 8 - Advanced C# Type Construction Techniques

As you can infer from the preceding code comments, the Initial Catalog name refers to the database you are

// Build a connection string.

OleDbConnection cn = new OleDbConnection();

cn.ConnectionString = "Provider=SQLOLEDB.1;" +

"User ID=sa;Pwd=;Initial Catalog=Cars;" +

"Data Source=(local);";

cn.Open();

// Do some interesting work here...

cn.Close();

In addition to the ConnectionString, Open(), and Close() members, the OleDbConnection class provides a number of members that let you configure attritional settings regarding your connection, such as timeout setting and transactional information. Table 17-14 gives a partial rundown.

Table 17-14: Members of the OleDbConnection Type

C# and the .NET Platform, Second Edition

As you can see, the properties of the OleDbConnection type are typically read-only in nature, and are only usef

Chapter 14 - A Better Painting Framework (GDI+)

when you wish to obtain the characteristics of a connection at runtime. When you wish to override default

Chapter 15 - Programming with Windows Forms Controls

settings, you must alter the construction string itself. For example, ponder the following code, which changes th

Chapter 16 - The System.IO Namespace

default connection timeout setting from 15 seconds to 30 seconds (via the Connect Timeout segment of the

Chapter 17 - Data Access with ADO.NET connection string):

Part Five - Web Applications and XML Web Services

Chapter 18 - ASP.NET Web Pages and Web Controls

OleDbConnection cn = new OleDbConnection();

List of Figures

cn.Open();

List of Tables

Console.WriteLine("***** Info about your connection *****");

Console.WriteLine("Database location: {0}", cn.DataSource); Console.WriteLine("Database name: {0}", cn.Database); Console.WriteLine("Provider: {0}", cn.Provider); Console.WriteLine("Timeout: {0}", cn.ConnectionTimeout); Console.WriteLine("Connection state: {0}", cn.State.ToString()); cn.Close();

Console.WriteLine("Connection state: {0}", cn.State.ToString());

Notice that this connection is explicitly opened and closed each time before making a call to the State property. As mentioned in the previous table, this property may take any value of the ConnectionState enumeration:

public enum System.Data.ConnectionState

{

Broken, Closed,

Connecting, Executing,

This comprehensive text starts with a brief overview of the

While it may be enticingC# languageto ponderand thenthe notionquicklyofmovesaskingtoakeyconnectiontechnical typeand if it is currently in the process of

architectural issues for .NET developers.

querying data (e.g., ConnectionState.Executing), do understand that with the current release of the .NET Framework, the only valid ConnectionState values are ConnectionState.Open and ConnectionState.Closed (th

remaining members of this enum are reserved for future use and are basically no-ops as of .NET 1.1). Also,

Table of Contents

understand that it is always safe to close a connection whose connection state is currently

C# and the .NET Platform, Second Edition

ConnectionState.Closed.

Introduction

Part One - Introducing C# and the .NET Platform

Connecting to an Access Database

Chapter 1 - The Philosophy of .NET

Chapter 2 - Building C# Applications

Much like classic ADO, the process of connecting to an Access database using ADO.NET requires little more

Part Two - The C# Programming Language

than retrofitting your construction string. First, set the Provider segment to the JET engine, rather than

Chapter 3 - C# Language Fundamentals

SQLOLEDB. Beyond this adjustment, set the data source segment to point to the path of your *.mdb file, as

Chapter 4 - Object-Oriented Programming with C# shown here:

Chapter 5 - Exceptions and Object Lifetime

Chapter 6 - Interfaces and Collections

// Be sure to update the data source segment if necessary!

Chapter 7 - Callback Interfaces, Delegates, and Events

OleDbConnection cn = new OleDbConnection();

Chapter 8 - Advanced C# Type Construction Techniques cn.ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;" +

Part Three - Programming with .NET Assemblies

@"data source = C:\cars.mdb";

Chapter 9 - Understanding .NET Assemblies

cn.Open();

Chapter 10 - Processes, AppDomains, Contexts, and Threads

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

PartOnceFourthe- Leveragingconnection thehas.NETbeenLibrariesmade, you can read and manipulate the contents of your data table.

Chapter 12 - Object Serialization and the .NET Remoting Layer

ChapterObtaining13 - BuildingDatabasea Better WindowSchema(IntroduciInformationg Windows F rms)Using Connection Types

Chapter 14 - A Better Painting Framework (GDI+)

ChapterConnection15 - Programmiobjects cangalsowithbeWindowsused toFobtainrms Controlsschema information from a given database through the

GetOleDbSchemaTable() method. As you are most likely aware, "schema information" is little more than

Chapter 16 - The System.IO Namespace

metadata that describes the database itself, rather than the tabular data it is maintaining. The

Chapter 17 - Data Access with ADO.NET

GetOleDbSchemaTable() method takes two parameters, the first of which is a member of the

Part Five - Web Applications and XML Web Services

OleDbSchemaGuid class type. This type exposes a number of read-only fields that map to the sort of metadata

Chapter 18 - ASP.NET Web Pages and Web Controls

you are interested in obtaining (table names, stored procedure names, views, and whatnot).

Chapter 19 - ASP.NET Web Applications

Chapter 20 - XML Web Services

The second parameter is an array of System.Object types that represents what (if any) restrictions should be

Index

placed on the schema information returned by the call. Be aware that the exact format of this array of

List of Figures

System.Object types will vary based on the specified value of the OleDbSchemaGuid parameter. Upon

List of Tables

completion, the GetOleDbSchemaTable() method returns a DataTable type that contains the schema information.

Rather than pound out all of the possible parameter combinations (as they are documented within online help), the following example illustrates how you can make use of an OleDbConnection type to obtain basic schema information regarding each table in the Cars database:

DataTable dtSchemaInfo = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,

new object[] {null, null, null, "TABLE"});

// Print the DataTable.

for(int curRow = 0; curRow < dtSchemaInfo.Rows.Count; curRow++)

{

for(int curCol= 0; curCol< dtSchemaInfo.Columns.Count; curCol++)

{

Console.Write(dtSchemaInfo.Rows[curRow][curCol].ToString().Trim()+ " "

}

Console.WriteLine();

C# and the .NET Platform, Second Edition

}

Apress © 2003 (1200 pages)

As you will see, itThisis alsocomprehensivepossible to obtaintext startsschemawith detailsa brief overviewfor a specificof thetable in a database using data reader types (more detailsC#tolanguagecome). and then quickly moves to key technical and

architectural issues for .NET developers.

Building a SQL Command via OleDbCommand

Table of Contents

Now that you better understand the role of the OleDbConnection type, the next order of business is to check ou

C# and the .NET Platform, Second Edition

how to submit SQL queries to the database in question. The OleDbCommand type (which as you recall

Introduction

implements the IDbCommand interface) is an OO representation of a SQL query, table name, or stored

procedure that is specified using the CommandType property. This property may take any value from the

Chapter 1 - The Philosophy of .NET

CommandType enum:

Chapter 2 - Building C# Applications

Part Two - The C# Programming Language

public enum System.Data.CommandType

Chapter 3 - C# Language Fundamentals

{

Chapter 4 - Object-Oriented Programming with C#

StoredProcedure,

Chapter 5 - Exceptions and Object Lifetime

TableDirect,

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

ChapterIf the comma9 - Understantype doesing .indeedNET Assrepresentmblies a SQL query, the CommandText property can be used to get or se

Chapterthe query10 text- Processes,. When creatingAppDomains,an OleDbCommandContexts, Threadstype, you may establish the SQL query as a constructor

Chparameterpter 11 -orTypedirectlyReflection,via theLateCommandTextBindi g, propertyAttribute.-Based Programming

Part Four - Leveraging the .NET Libraries

Also, when you are creating an OleDbCommand type, you will need to specify the OleDbConnection to be used

Chapter 12 - Object Serialization and the .NET Remoting Layer

Again, you may do so as a constructor parameter or via the Connection property. To illustrate, ponder the two

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

(functionally equivalent) ways to build an OleDbCommand type that associates a SQL SELECT statement with

Chapter 14 - A Better Painting Framework (GDI+) an OleDbConnection variable named "cn":

Chapter 15 - Programming with Windows Forms Controls

Chapter 16 - The System.IO Namespace

// Specify SQL command and connection as constructor parameters.

Chapter 17 - Data Access with ADO.NET

string strSQL1 = "Select Make from Inventory where Color='Red'";

Part Five - Web Applications and XML Web Services

OleDbCommand myCommand1 = new OleDbCommand(strSQL1, cn);

Chapter 18 - ASP.NET Web Pages and Web Controls

// Specify SQL command and connection via properties.

Chapter 19 - ASP.NET Web Applications

string strSQL2 = "Select Make from Inventory where Color='Red'";

Chapter 20 - XML Web Services

OleDbCommand myCommand2 = new OleDbCommand();

Index

myCommand.Connection = cn;

List of Figures

myCommand.CommandText = strSQL2;

List of Tables

Realize that at this point, you have not literally submitted the SQL query to the Cars database, but rather prepp the state of the command type for future use. Table 17-15 highlights some additional members of the OleDbCommand type.

Table 17-15: Members of the OleDbCommand Type

PartParametersTwo - The C# ProgrammingGetsLanguagethe collection of OleDbParameter types used for a parameterized query.

Part Four - Leveraging the .NETsourceLibraries.

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