Thinking in C# phần 6 pdf

422 Thinking in C# www.ThinkingIn.NET

relational table data and XML) as well as making it much easier to program widely￾distributed database applications. However, this model increases the possibility

that two users will make incompatible modifications to related data – they’ll both

reserve the last seat on the flight, one will mark an issue as resolved while the other

will expand the scope of the investigation, etc. So even a minimal introduction to

ADO.NET requires some discussion of the issues of concurrency violations.

Getting a handle on data with DataSet

The DataSet class is the root of a relational view of data. A DataSet has

DataTables, which have DataColumns that define the types in DataRows. The

relational database model was introduced by Edgar F. Codd in the early 1970s. The

concept of tables storing data in rows in strongly-typed columns may seem to be

the very definition of what a database is, but Codd’s formalization of these concepts

and others such such as normalization (a process by which redundant data is

eliminated and thereby ensuring the correctness and consistency of edits) was one

of the great landmarks in the history of computer science.

While normally one creates a DataSet based on existing data, it’s possible to create

one from scratch, as this example shows:

//:c10:BasicDataSetOperations.cs

using System;

using System.Data;

class BasicDataSetOperations {

public static void Main(string[] args){

DataSet ds = BuildDataSet();

PrintDataSetCharacteristics(ds);

}

private static DataSet BuildDataSet() {

DataSet ds = new DataSet("MockDataSet");

DataTable auTable = new DataTable("Authors");

ds.Tables.Add(auTable);

DataColumn nameCol = new DataColumn("Name",

typeof(string));

auTable.Columns.Add(nameCol);

DataRow larryRow = auTable.NewRow();

Chapter 10: Collecting Your Objects 423

larryRow["Name"] = "Larry";

auTable.Rows.Add(larryRow);

DataRow bruceRow = auTable.NewRow();

bruceRow["Name"] = "Bruce";

auTable.Rows.Add(bruceRow);

return ds;

}

private static void PrintDataSetCharacteristics(

DataSet ds){

Console.WriteLine(

"DataSet \"{0}\" has {1} tables",

ds.DataSetName, ds.Tables.Count);

foreach(DataTable table in ds.Tables){

Console.WriteLine(

"Table \"{0}\" has {1} columns",

table.TableName, table.Columns.Count);

foreach(DataColumn col in table.Columns){

Console.WriteLine(

"Column \"{0}\" contains data of type {1}",

col.ColumnName, col.DataType);

}

Console.WriteLine(

"The table contains {0} rows",

table.Rows.Count);

foreach(DataRow r in table.Rows){

Console.Write("Row Data: ");

foreach(DataColumn col in table.Columns){

string colName = col.ColumnName;

Console.Write("[{0}] = {1}",

colName, r[colName]);

}

Console.WriteLine();

}

}

}

}///:~

The .NET classes related to DataSets are in the System.Data namespace, so

naturally we have to include a using statement at the beginning of the program.

424 Thinking in C# www.MindView.net

The Main( ) method is straightforward: it calls BuildDataSet( ) and passes the

object returned by that method to another static method called

PrintDataSetCharacteristics( ).

BuildDataSet( ) introduces several new classes. First comes a DataSet, using a

constructor that allows us to simultaneously name it “MockDataSet.” Then, we

declare and initialize a DataTable called “Author” which we reference with the

auTable variable. DataSet objects have a Tables property of type

DataTableCollection, which implements ICollection. While

DataTableCollection does not implement IList, it contains some similar

methods, including Add, which is used here to add the newly created auTable to

ds’s Tables.

DataColumns, such as the nameCol instantiated in the next line, are associated

with a particular DataType. DataTypes are not nearly as extensive or extensible

as normal types. Only the following can be specified as a DataType:

Boolean DateTime

Decimal Double

Int16 Int32

Int64 SByte

Single String

TimeSpan UInt16

UInt32 UInt64

In this case, we specify that the “Name” column should store strings. We add the

column to the Columns collection (a DataColumnCollection) of our auTable.

One cannot create rows of data using a standard constructor, as a row’s structure

must correspond to the Columns collection of a particular DataTable. Instead,

DataRows are constructed by using the NewRow( ) method of a particular

DataTable. Here, auTable.NewRow( ) returns a DataRow appropriate to our

“Author” table, with its single “Name” column. DataRow does not implement

ICollection, but does overload the indexing operator, so assigning a value to a

column is as simple as saying: larryRow["Name"] = "Larry".

The reference returned by NewRow( ) is not automatically inserted into the

DataTable which generates it; that is done by:

Chapter 10: Collecting Your Objects 425

auTable.Rows.Add(larryRow);

After creating another row to contain Bruce’s name, the DataSet is returned to the

Main( ) method, which promptly passes it to PrintDataSetCharacteristics( ).

The output is:

DataSet "MockDataSet" has 1 tables

Table "Authors" has 1 columns

Column "Name" contains data of type System.String

The table contains 2 rows

Row Data: [Name] = Larry

Row Data: [Name] = Bruce

Connecting to a database

The task of actually moving data in and out of a store (either a local file or a

database server on the network) is the task of the IDbConnection interface.

Specifying which data (from all the tables in the underlying database) is the

responsibility of objects which implement IDbCommand. And bridging the gap

between these concerns and the concerns of the DataSet is the responsibility of the

IDbAdapter interface.

Thus, while DataSet and the classes discussed in the previous example

encapsulate the “what” of the relational data, the IDataAdapter, IDbCommand,

and IDbConnection encapsulate the “How”:

What How

DataColumn

DataRow

IDbCommand

IDbConnection

DataTable 1..*

0..*

IDataAdapter *

1

DataSet

0..*

*

Figure 10-7: ADO.NET separates the “What data” classes from the “How we get it”

classes

The .NET Framework currently ships with two managed providers that implement

IDataAdapter and its related classes. One is high-performance provider

426 Thinking in C# www.ThinkingIn.NET

optimized for Microsoft SQL Server; it is located in the System.Data.SqlClient

namespace. The other provider, in the System.Data.OleDb namespace, is based on

the broadly available Microsoft JET engine (which ships as part of Windows XP

and is downloadable from Microsoft’s Website). Additionally, you can download an

ODBC-suppporting managed provider from msdn.microsoft.com. One suspects

that high-performance managed providers for Oracle, DB2, and other high-end

databases will quietly become available as .NET begins to achieve significant

market share.

For the samples in this chapter, we’re going to use the OleDb classes to read and

write an Access database, but we’re going to upcast everything to the ADO.NET

interfaces so that the code is as general as possible.

The “Northwind” database is a sample database from Microsoft that you can

download from http://msdn.microsoft.com/downloads if you don’t already have it

on your hard-drive from installing Microsoft Access. The file is called “nwind.mdb”.

Unlike with enterprise databases, there is no need to run a database server to

connect to and manipulate an Access database. Once you have the file you can

begin manipulating it with .NET code.

This first example shows the basic steps of connecting to a database and filling a

dataset:

//:c10:DBConnect.cs

using System;

using System.Data;

using System.Data.OleDb;

class BasicDataSetOperations {

public static void Main(string[] args){

DataSet ds = Employees("Nwind.mdb");

Console.WriteLine(

"DS filled with {0} rows",

ds.Tables[0].Rows.Count);

}

private static DataSet Employees(string fileName){

OleDbConnection cnctn = new OleDbConnection();

cnctn.ConnectionString=

"Provider=Microsoft.JET.OLEDB.4.0;" +

"data source=" + fileName;

DataSet ds = null;

try {

Chapter 10: Collecting Your Objects 427

cnctn.Open();

string selStr = "SELECT * FROM EMPLOYEES";

IDataAdapter adapter =

new OleDbDataAdapter(selStr, cnctn);

ds = new DataSet("Employees");

adapter.Fill(ds);

} finally {

cnctn.Close();

}

return ds;

}

}///:~

After specifying that we’ll be using the System.Data and System.Data.OleDb

namespaces, the Main( ) initializes a DataSet with the results of a call to the

static function Employees( ). The number of rows in the first table of the result is

printed to the console.

The method Employees( ) takes a string as its parameter in order to clarify the

part of the connection string that is variable. In this case, you’ll obviously have to

make sure that the file “Nwind.mdb” is in the current directory or modify the call

appropriately.

The ConnectionString property is set to a bare minimum: the name of the

provider we intend to use and the data source. This is all we need to connect to the

Northwind database, but enterprise databases will often have significantly more

complex connection strings.

The call to cnctn.Open( ) starts the actual process of connecting to the database,

which in this case is a local file read but which would typically be over the network.

Because database connections are the prototypical “valuable non-memory

resource,” as discussed in Chapter 11, we put the code that interacts with the

database inside a try…finally block.

As we said, the IDataAdapter is the bridge between the “how” of connecting to a

database and the “what” of a particular relational view into that data. The bridge

going from the database to the DataSet is the Fill( ) method (while the bridge

from the DataSet to the database is the Update( ) method, which we’ll discuss in

our next example). How does the IDataAdapter know what data to put into the

DataSet? The answer is actually not defined at the level of IDataAdapter. The

428 Thinking in C# www.MindView.net

OleDbAdapter supports several possibilities, including automatically filling the

DataSet with all, or a specified subset, of records in a given table. The

DBConnect example shows the use of Structured Query Language (SQL), which is

probably the most general solution. In this case, the SQL query SELECT * FROM

EMPLOYEES retrieves all the columns and all the data in the EMPLOYEES table of

the database.

The OleDbDataAdapter has a constructor which accepts a string (which it

interprets as a SQL query) and an IDbConnection. This is the constructor we use

and upcast the result to IDataAdapter.

Now that we have our open connection to the database and an IDataAdapter, we

create a new DataSet with the name “Employees.” This empty DataSet is passed

in to the IDataAdapter.Fill( ) method, which executes the query via the

IDbConnection, adds to the passed-in DataSet the appropriate DataTable and

DataColumn objects that represent the structure of the response, and then

creates and adds to the DataSet the DataRow objects that represent the results.

The IDbConnection is Closed within a finally block, just in case an Exception

was thrown sometime during the database operation. Finally, the filled DataSet is

returned to Main( ), which dutifully reports the number of employees in the

Northwind database.

Fast reading with IDataReader

The preferred method to get data is to use an IDataAdapter to specify a view into

the database and use IDataAdapter.Fill( ) to fill up a DataSet. An alternative, if

all you want is a read-only forward read, is to use an IDataReader. An

IDataReader is a direct, connected iterator of the underlying database; it’s likely

to be more efficient than filling a DataSet with an IDataAdapter, but the

efficiency requires you to forego the benefits of a disconnected architecture. This

example shows the use of an IDataReader on the Employees table of the

Northwind database:

//:c10:DataReader.cs

using System;

using System.Data;

using System.Data.OleDb;

class DataReader {

public static void Main(){

EnumerateEmployees("Nwind.mdb");

}