ADO.NET - .NET at JKU





Overview

[pic]

Objectives

This module introduces , the evolutionary next step for Microsoft® ActiveX® Data Objects (ADO).

What You Will Learn

• The difference between ADO and

• The benefits of

• core concepts and architecture, including the object model, introduction to the System.Data namespace, the DataSet, and data views; .NET data providers, and more

Recommended Reading

• "ADO+ Guides the Evolution of the Data Species"

• Microsoft newsgroup: microsoft.public.dotnet.framework.adonet

Overview

[pic]

and the .NET Framework

is part of the Microsoft .NET Framework. If you break down the Framework into sections (common language runtime, base classes, data and XML, Web Services and user interface), then sits squarely in the data and XML section.

Overview

[pic]

ADO vs. (1/2)

ADO

You may wonder what the difference between ADO and is, besides the name change. The long and short of it is that is the next step for ADO, consisting of many enhancements. Let us begin by looking at ADO.

Designed for connected access

ADO is based on the concept of a 24/7 (24 hours, 7 days a week) “connected world,” such as is found on a corporate local area network (LAN). You create a RecordSet; connect it to a data source—most often a database—and work. The RecordSet stays “plugged in,” if you will, to the data source, and changes to the data are posted to the data store immediately.

A model based on connected data can make it difficult and impractical to exchange data across application and organizational boundaries. If two components need to share the same data, both have to be connected, or a way must be devised for the components to pass data back and forth.

There are times when it is still useful to work with connected data. For example, in an application that performs a high volume of updates with possible record contention, a connected data architecture can be very important. A typical scenario is a ticket-booking application, where users need to work with information that is up to the moment. For these types of applications, you might wish to design your data access around ADO.

However, a connected architecture is impractical (if not impossible!) in the disconnected world of the Internet. For an Internet-based solution, you should certainly look at as your data access technology.

RecordSet is one table that contains all the data

The ADO RecordSet is limited in flexibility and functionality. For example, most useful data analysis or presentation requires views of your data that span multiple tables or data sources. Using ADO, this cannot be accomplished without performing a SQL JOIN. As you may or may not know, this is a performance drag. It consumes memory and CPU power on the database server—precious resources especially with today’s Internet user demands.

Because a RecordSet is essentially a single result table, navigation is sequential, row-by-row. Thus, if you perform a joining query the resulting data is “flattened”; any relations that may have existed are not relationally navigable.

Another, perhaps more impressive point is that ADO does not support an abstract data model. It is tied to the physical data source.

Data types are bound to COM/COM+ data types

A rather significant limitation of ADO is that the available data types are restricted to Component Object Model (COM) and COM+ data types. That means that sometimes you need to fit a square peg in a round hole.

For example, in COM/COM+ programming the BSTR is typically used to represent strings that need to be interoperable across languages. For those of you who do not know, the BSTR type is a null-terminated string whose leading WORD contains the length of the string. Unfortunately, the BSTR type is really a stranger to all languages and only makes sense in the COM context. For C, C++, and other lower level languages you must use special COM run-time functions to create and destroy them, and rapid application development (RAD) environments like Microsoft Visual Basic® need explicit support in the runtime to handle these types.

Data sharing via COM marshalling

Sharing data between components in your application or elsewhere is done through COM marshalling. This limits sharing of data to a COM or COM-friendly environment.

Problems marshalling through firewalls (DCOM, binary)

There are also problems with marshalling through firewalls, because they tend to restrict the type of data that can pass through them. COM marshalling requires (COM) system services on the “other side” of the firewall (the server), but firewalls are often set up to block such traffic because it could pose a security threat.

Overview

[pic]

ADO vs. (2/2)



Designed for disconnected access

In contrast to ADO, , like .NET in general, is designed with Internet technology, although not limited to creating Internet-based solutions. As such, the access model of is a disconnected one.

Can model data logically!

An additional improvement over ADO is that provides the means to model your data abstractly. This is achieved through the successor of the RecordSet: the DataSet.

DataSet can contain multiple tables

The DataSet can model data logically or abstractly because, unlike the RecordSet, the DataSet is not a data container capable only of stuffing rows of data into itself. You now have the ability to create an in-memory data schema that includes multiple tables and the relationships between them!

Two intended results of this are that relational navigation is now possible and you can create views on multiple tables or data sources without performing a database JOIN, but more on this later.

Data types are only bound to XML schema—No data type conversions required

Considering the flexibility the DataSet affords, it should not surprise you that gives you the ability to use any data type. This is accomplished through its use of XML schemata. If you can describe it in the schema, you can use it—and avoid data conversions!

XML, like HTML, is plaintext: “Firewall friendly”

The use of XML and XML schemata in also makes it possible to share data between DataSets and other components or applications—without limitation. Because XML is just plaintext it can more readily pass through firewalls without being burned. The reason is that, unlike binary data (COM/COM+), firewalls can inspect XML data —“it's just text.”

Overview

[pic]

Benefits of

We have already touched on the benefits of verses ADO in our comparison. As you learned, XML is used throughout and this has its own benefits.

Interoperability through use of XML

XML is an open Web standard, is human readable and decipherable text, and is data that describes itself (metadata). This makes it easy to share, interpret, and work with. For these reasons, XML is used to represent and transfer all data in .

Scalability through the disconnected DataSet

On the scalability side of things, because connections are not maintained for long periods, there is little possibility that database locking will occur. However, by specifically using Service Components you can perform locking.

brings the connected world of the Internet to your solutions through its disconnected model. You can now design and implement your applications without the extra consideration toward resource loads on your database-server tier. Simply: Connect, execute one or more queries, and disconnect.

Maintainability

, like much of .NET, is built around the idea of separation of data logic and user interface. This means you can create your application in independent layers. For example, you can establish a data access layer, a business logic layer, and an interface façade (or user interface). Designing and building your application this way limits “collateral damage” when you update any given aspect of your solution.

Overview

[pic]

Visual Enhancements

Typed programming—a programming style

Typed programming is a programming style in which end-user words are used to construct statements or evaluate expressions.

For example, if you wish to select the “Balance” column for “Jones” from the “Customer” table it is certainly more intuitive to do so by writing

DataSet.Customer(“Jones”).Balance

to access the Customer table than by writing

Table(“Customer”)(“Jones”).Column(“Balance”)

The typed programming model is also easier to read and comprehend for both programmers and nonprogrammers!

To this end, enables you to create typed DataSets in which you define the schema for the DataSet and its data. Microsoft Visual dynamically creates code based on a schema (.xsd) and generates a file (.cs) containing the strongly typed DataSet. Visual uses this for statement completion, as well as compile-time type checking.

Wizard Support

Visual provides several “wizards” aimed to make rapid application development possible. These wizards enable you to visually select the tables you wish to work with from your data source, for instance, and then automatically generate the select, insert, update and delete queries for you! The wizards also make creaing DataSet objects based on your selection of tables automatic – by selecting “Generate DataSet” the appropriate object is generated.

There is also an XML Designer that makes it easy to create data schemas. By using the designer in this way you create the DataSet and its corresponding DataTable objects and relations. There is plenty of additional wizard support for in Visual , but that it not the topic of this module.

Core Concepts and Architecture

[pic]

Core Concepts and Architecture

The Object Model

The object model is comprised of several namespaces, but in sum it is: DataSet and its related objects and the .NET data providers.

-related Namespaces

is organized into five main namespaces:

• System.Data

• System.Data.OleDb

• System.mon

• System.Data.SqlClient

• System.Data.SqlTypes

The core namespaces of that you will work with are System.Data and System.Data.OleDb. The System.mon namespace contains the classes that are common to all .NET data providers.

Two other namespaces that are arguably part of are System.Data.SqlClient and System.Data. SQLTypes; these are both optimized for use with Microsoft SQL Server™.

Combined, these five namespaces provide you with the means to connect to data sources, retrieve information, store it (in an in-memory cache), update your cached data, and post it back to the data sources. You also have the ability to modify the data source’s schema from your DataSet. That is, you can create new tables, make new relations, and update the data source with the information.

Core Concepts and Architecture

[pic]

-related Namespaces

You may think of as a pyramid in which the top-most, main tier is the System.Data namespace. Below that you have the System.mon, System.Data.OleDb, System.Data.SqlClient, and System.Data.SqlClientTypes namespaces.

Core Concepts and Architecture

[pic]

System.Data Namespace

The object model is contained in the System.Data namespace.

Contains the basis and bulk of

As its name implies, System.Data is “all about data”! For this reason, System.Data is the centerpiece of and the data-centric namespace.

Provides the means to work on and with your data!

• It contains a myriad of classes for working with data

• Ability to create views of your data

• Means to logically represent your data

• Enables the use of XML to view, share, and store data

It is the means to work on and with your data!

Core Concepts and Architecture

[pic]

Introducing the Objects…

Following are a few of the objects that are “first class citizens” in the System.Data namespace. You will find yourself working with these classes regularly.

• System.Data

Contains the “main” classes of

• DataSet

In-memory cache of data and data-related information

• DataTable

In-memory cache of a database table

• DataRow

Used to manipulate a row in a DataTable

• DataColumn

Used to define the columns in a DataTable

• DataRelation

Used to relate two DataTables to each other. As you will learn, this is similar to how a relation is created in a SQL database.

• DataViewManager

Used to create one or more views (using DataView objects) on the DataTable objects of a DataSet

Core Concepts and Architecture

[pic]

Putting the Objects Together…

This diagram shows you how some of the classes in the System.Data namespace fit together with relation to the all-important DataSet. This diagram does not show which objects a DataSet must contain but what it can—or more accurately, most likely will—contain. It should be clear to you from this diagram that the DataSet is the view of data.

In this example we have a DataSet in which three DataTables have been added and two DataRelations. Notice that the DataTables are contained by the DataSet Tables container and the DataRelations are contained by the Relations container.

You may add the appropriate items to each of these containers using their Add methods. Access to members of the containers is achieved using array-style indexing.

Note that the lines connecting the DataTables to the DataRelations are meant to show that a relation between the DataTables has been specified. You may do this by specifying that one or more columns in two different tables are related. Note that the DataColumn objects of each of the tables you wish to relate must be of the same data type.

The DataView and DataViewManager objects shown are meant to convey that one or more views may be created on any given DataTable.

Core Concepts and Architecture

[pic]

Working the Data – The DataSet

Now that we have an overview of the DataSet and understand that it acts as the main component of System. Data, let us explore a little deeper into the DataSet.

An in-memory cache of data from a data source

A DataSet is an in-memory cache of data loaded from your data source or sources using one or more query commands. Because it is in memory, manipulation and viewing of the data is obviously fast.

Common way to represent and manipulate data

The real advantage that the DataSet provides is that it is a common way to represent and manipulate data. In other words, it is a “universal data container.” You may ask what “universal” means. Well, it is universal because it is designed to contain data from any data source. That source might as easily prove to be a component in your application as a database. When you think DataSet, think “set of data” not “cache of data from the database"! Taken in this context, you should see the potential of using DataSets throughout your application solution—not only when querying a database but any time you need to manipulate or move data around.

Logical representation of data

A DataSet is a logical representation of data depending on the query/result set. The DataSet does not restrict you to mapping directly to the physical data source layout. Alternatively, you may create your own logical representation of the data.

For example, assume your database has three tables: Customers, Addresses, and CreditCards. You can perform a query on your for customers whose addresses are in a particular region and also retrieve their credit card information. The result of your query would be stored in the DataSet, but the corresponding Customers, Addresses, and CreditCards tables need not exist in the DataSet.

On the other hand, you can create tables in the DataSet, called DataTables, and create relations between them. Again, the tables and relations you create in the DataSet need not be bound to those in the data source but may be your own logical representation.

Disconnected from the data source

The DataSet is an in-memory cache designed to be disconnected from the data source. You simply connect to the data source using an OleDbConnection object (or SqlConnection object), execute your query or queries, and then close the connection. You can then work with the cached data, and when you are ready to update the data source you simply reconnect to the data source and issue an update command. We have already learned some of the advantages of this disconnected philosophy.

XML used to read and write data and schema

In the .NET Framework, XML plays an important and pervasive role. So, why should be any different? As we have already learned, DataSet objects communicate using XML documents, which means they can communicate with just about anything!

You will learn more about the reading and writing XML documents in later. At this time, suffice it to say that the DataSet stores and communicates using XML and XML schemata.

Core Concepts and Architecture

[pic]

Properties & Methods of Interest

Collections are used to add and remove tables and relations

Before we discuss the properties of the DataSet, let us first talk about properties in general with a focus on those that return collections.

When a property returns (or “gets”) a collection of items, the general rule is that the collection’s type name is the property name appended with “Collection.” In the case of the Tables property, for instance, the collection is of type DataTableCollection. You may then iterate over the collection to get each object it contains. You may also use a collection’s Add() or Remove() methods to add or remove objects.

Samples will follow.

Properties of Interest

Now let us delve into some of the DataSet properties of interest.

• Tables

This property is used to retrieve the DataTableCollection, the collection used to hold any DataTable objects added to the DataSet.

• Relations

Returns the RelationsCollection of DataRelation objects, representing logical relationships between tables in the DataSet.

• Namespace

Get or sets the namespace associated with the DataSet. This is used for scoping elements and data, which is especially useful when reading or writing a DataSet’s XML schema or data

Using Properties Sample

The following code snippet uses the Tables property of the DataSet myDataSet to add a newly created DataTable to the DataSet.

DataTable newTable = new DataTable(

“Addresses”

);

// Creation of the columns and rows of the

// Address table is not shown.

myDataSet.Tables.Add( myTable);

In the following example, the DataTableCollection of a DataSet is returned so that you may iterate over it to retrieve the DataTables that are contained in the DataSet.

DataTableCollection myDataTableCollection;

myDataTableCollection = myDataSet.Tables;

// Iterate over the collection...

// Iteration not shown.

Core Concepts and Architecture

[pic]

All About Data…

Simply to reiterate a very important point: a DataSet is a Universal Data Container—It’s not just for databases!

Core Concepts and Architecture

[pic]

The DataTable

Maps DataSet data to a physical table in the data source

You may create a DataTable object and map it to a data source’s table. That is, if your data schema contains a table called Customers you can create a DataTable in the DataSet to correspond to the physical table in a one-to-one manner. It is also possible to create DataTable objects that do not correspond to any table in the data source. The choice is yours, depending solely on what makes sense logically to you.

Can be related to one another through DataRelations

In a physical model of the database or other data schema, tables are often related to one another in different logical ways. For example, a CustID column in one table may have a corresponding column in another table. An example would be a Customers table and an Addresses table (for customers). As a customer may have more than one address, it is logical that a one-to-many correspondence may exist via a common column, such as the customer name or ID.

facilitates creating this type of relation between DataTable objects with DataRelation objects.

Properties of Interest

• Columns

Gets the columns as a collection. This returns the ColumnsCollection containing any/all DataColumn objects.

• Rows

Returns the rows of the DataTable. Technically, a RowsCollection of DataRow objects is returned.

• ParentRelations

Gets the parent relations as a RelationsCollection. These are the logical relationships of one table to other tables.

• Constraints

Returns the table’s constraints as a ConstraintsCollection.

• DataSet

Returns the DataSet to which the DataTable belongs.

• PrimaryKey

Gets or sets the primary key of the table using an array of DataColumn objects. The contained combined objects comprise the table’s primary key.

Core Concepts and Architecture

[pic]

System.Data—DataSet and DataTable

Create a DataTable using a DataSet.

1. Create a DataColumn, providing the column name and data type.

2. Add the DataColumn to the DataTable.

3. Use the DataSet.Tables property to add the DataTable to the DataSet.

4. Repeat until satisfied!

Core Concepts and Architecture

[pic]

Relating Data – The DataRelation

Having already learned something about the DataRelation when you learned about the DataTable and DataSet objects, it is time to expand on DataRelation objects.

Used to relate two DataTable objects to each other

The DataRelation object uses DataColumn objects to create the relation between two DataTables.

If you have any database experience, it will be obvious to you that the DataType (the type of data) for both columns must be the same. For example, one column cannot be of the integer type while the related column is of the character type.

A nice feature of DataRelations in is that you can name the relation anything you like, making it easier to remember, and use this name to refer to the relation later.

For instance, it would be logical (and easy to remember) if a relation between a Customers table and an Orders table were named CustomerOrders. You will see more on DataRelations later.

Makes relational navigation possible

Relational navigation is possible in simply because you can create relations by which to navigate! As DataRelation objects are created using two DataColumn objects, one from the parent and one from the child DataTables, a connection by which to navigate exists.

RelationsCollection used to hold/group DataSet’s relations

As you have learned, DataRelations are retrieved or set using the DataSet object’s Relations property or the DataTable object’s ParentRelations property. Both properties return a RelationsCollection, which is a collection of all DataRelation objects of a DataSet.

Core Concepts and Architecture

[pic]

Creating Relations With DataRelation

Keeping in mind the previous example of creating DataTable and DataColumn objects, let us explore how to create relations between the Customers DataTable and a second Orders DataTable object.

Three basic steps are all that are needed:

1. Retrieve the DataColumn objects that you will base the relation on from the appropriate DataTable using the DataSet’s Tables property.

2. Create a named DataRelation using the columns.

3. Add the relation to the DataSet using the DataSet’s Relations property.

4.

Core Concepts and Architecture

[pic]

XML and the DataSet

DataSet can read/write XML for its data and/or schema

As mentioned previously, the DataSet is capable of reading and writing its data and schema as XML. This is important if you consider that XML is an open, industry standard that is popular among most software solutions providers. At this time, XML is pervasive, found both in Internet solutions and in traditional applications. The fact that it is designed to both contain and describe data (data that describes itself) makes it the perfect choice for a universal data container such as the DataSet!

Furthermore, the ability of a DataSet to both read and write data and schema as XML makes it possible for you to both create and modify data in a DataSet using XML or XML enabled solution, such as Microsoft SQL Server 2000.

XML-related DataSet Methods for Reading

• ReadXml: Reads an XML schema and data into the DataSet

• ReadXmlSchema: Reads an XML schema into the DataSet

And for writing:

The methods for reading XML into a DataSet of course have complimentary means of writing XML from a DataSet: WriteXml and WriteXmlSchema. Two additional methods provided are: GetXml and GetXmlSchema. These methods return the data or schema as a string.

Namespace property: Sets the namespace for serialization

Full support for SQL Server-style DiffGrams

DiffGrams are an XML means to track changes to a DataSet's data and schema. They provide a “before and after” picture of things, if you will.

Core Concepts and Architecture

[pic]

Methods of Reading and Writing XML

This code sample shows you how to use some of the XML-specific methods and properties of a DataSet.

Core Concepts and Architecture

[pic]

DataSet, DataRelation, and Data…Views

This slide provides an overview of the relationships between DataSet, DataTable, DataRelation, DataView and DataViewManager objects.

Core Concepts and Architecture

[pic]

Viewing Data – The DataView

Create multiple views on DataTable objects

Using DataViews you can create multiple views on any given table. For instance, you may have an Addresses table containing the primary and secondary addresses but wish to create two different views on the data: one for the primary and one for the secondary addresses.

Bindable to user interface controls

The DataView is designed so that you can bind it directly to Windows Forms or Web Forms UI controls.

Properties of Interest

• Table

Gets or sets the source DataTable for the view.

• Sort

Gets or sets the sort column or columns and the sort order for the table. You may set the sort order to either ascending (“ASC”) or descending (“DESC”); the default is ascending.

• RowFilter

Get or set the expression used to filter which rows are displayed.

• RowStateFilter

Get or set the row state filter. The possible values are any one of the following DataViewRowState values: CurrentRows, Deleted, ModifiedCurrent, None, ModifiedOriginal, New, OriginalRows, Unchanged.

Core Concepts and Architecture

[pic]

Creating a DataView By Example

This sample shows you how to:

• Create two DataView objects (view1 and view2) on a DataTable (myTable).

• Specify a Sort and RowStateFilter on the view1 and view2 objects, respectively.

• Bind a DataView (view1) to a DataGrid.

Core Concepts and Architecture

[pic]

Viewing More Data - DataViewManager

Similar to a DataView but DataSet oriented

Instead of setting a view on an individual table, DataViewManager permits the creation of more complex views on related tables in a DataSet. Additionally, each table may have its own filter or sorting settings.

Used to create multiple views on a DataSet

This is a more advanced viewing feature than the DataView, enabling you to set individual filters on each of the tables.

Properties of Interest

• DataViewSettings

Returns a DataViewSettingsCollection object. DataViewSetting objects cannot be added or removed by the user. Instead the properties of a DataViewSetting may be modified (see sample).

• DataSet

Used to get or set the DataSet to be viewed.

Core Concepts and Architecture

[pic]

DataViewManager By Example

1. Create a DataViewManager using an existing DataSet.

2. Create a DataView on the Orders table.

3. Set the DataViewSettings on the DataTable “Orders” in the DataSet myDS to Sort in ascending (ASC) order on the CustID column.

4. Repeat steps 1-3 for the Customer table.

5. Set the data source for the the DataGrid (dataGrid1) to the DataViewManager and then set the data member (i.e. the table) that the DataGrid “looks upon” (i.e. “Table1”).

Core Concepts and Architecture

[pic]

The (ADO).NET Data Providers

A collection of classes for accessing data sources

Let us first step back from the scope and into the broader .NET arena.

.NET data providers are comprised of a collection of classes for accessing various data sources. Included in the .NET Framework you will find classes specifically optimized for SQL Server 2000, SQL Server 7, and Microsoft Data Engine (MSDE). Of course, there is also a host of other classes for working with any OLE DB providers, including: Oracle, Jet, and SQL OLE DB providers.

As the term .NET data providers implies, these classes are designed to provide connectivity to the data source; they are the connection between DataSet objects and the data stores.

Two .NET data providers

In the .NET Framework there are currently two .NET data providers:

• ADO: via the System.Data.OleDb namespace

• SQL Server: via the System.Data.SqlClient namespace

The latter is optimized for SQL Server; it is the SQL Server managed provider.

System.Data.OleDb is the managed provider

The System.Data.OleDb namespace is the managed provider.

Core Concepts and Architecture

[pic]

.NET Data Providers Hierarchy

We revisit the System.Data pyramid discussed earlier but instead focus on the .NET .NET data providers.

As you will recall, System.Data is the data-centric root of the .NET data providers. Both System.Data.SqlClient and System.Data.OleDb are descendents that are siblings to each other. In fact, you may think of them as identical twins, as they look, sound, and function in nearly identical manners; perhaps “clone” is a better term.

They are indistinguishable except by prefix name and the fact that one is targeted specifically at SQL Server.

While in fact the System.mon namespace is a sibling to System.Data.OleDb and .SqlClient, logically it takes on more of an ancestral role its classes are shared (or inherited) by the other .NET data provider namespaces.

Core Concepts and Architecture

[pic]

OleDbConnection and SqlConnection

Let us now venture a little deeper into the .NET data providers by looking at a few of the classes that handle connecting to the data source and issuing queries.

This discussion continues with a focus on the managed provider unless otherwise specified.

Represents a unique session with a data source

This OleDbConnection class represents a unique session with a data source.

Create, Open, Close a connection to a data source

The OleDbConnection class is your connection to your data source. It is used to:

• Create a connection to a specified data source, by passing a data source connection string to the OleDbConnection or SqlConnection class constructor.

• Open a connection to the data source using the Open() method.

• Close a connection to the data source using the Close() method.

Additional functionality is also available.

Functionality and Methods to Perform Transactions

OleDbConnection provides a BeginTransaction method that returns an OleDbTransaction object. You then can use the returned object to perform transactional operations:

• CommitTransaction: Called to commit (and end) a transaction

• RollbackTransaction: Called to “undo” a transaction

OleDbConnection Example

This simple code snippet simply demonstrates the creation of an OleDbConnection object, using it to open a connection to the data source and then closing the connection.

Core Concepts and Architecture

[pic]

OleDbDataAdapter Class (1/2)

Bridge between the DataSet and the data source

The OleDbDataAdapter inherits the functionality of the OleDbDataAdapter class, which enables it to act as a bridge between the DataSet and the data store. It exposes a set of commands and a data source connection that are used together to fill a DataSet and update a data source.

Means to modify the DataSet and data source

As it is designed for issuing queries against the data source, the OleDbDataAdapter class has several properties designed to aid you, as you will see in a minute.

Core Concepts and Architecture

[pic]

OleDbDataAdapter Class (2/2)

Properties of Interest

• DeleteCommand

Gets or sets a command used to delete records in the data source.

• InsertCommand

Gets or sets a command used to insert records in the data source.

• SelectCommand

Gets or sets a command used to select records from the data source.

• UpdateCommand

Gets or sets a command used to update records in the data source.

• TableMappings

Maps the a data source table to a DataTable in the DataSet

OleDbCommands are retrieved or set by “command” properties

Note that the “command” properties referred to are actually OleDbCommand classes that contain the queries to perform on the data source. You will learn more about this in the OleDbCommand section.

Inherits methods from OleDbDataAdapter class

The OleDbDataAdapter class inherits methods, such as FillData() and Update(), from the OleDbDataAdapter class. You will find that you often use these methods when retrieving or updating your DataSet.

Core Concepts and Architecture

[pic]

OleDbCommand Class

Represents a query to execute on the data source

In addition containing the query to execute, the OleDbCommand class provides a few additional items, as you can see from the properties of interest.

Properties of Interest

• ActiveConnection

Get or set the data source connection.

• CommandText

Get or set the query (text) command, which may be either an SQL statement or the name of a stored procedure.

• CommandType

Get or set how to interpret the command text. The possible CommandType values are: Text (a SQL text statement), StoredProcedure (the name of a stored procedure), or TableDirect (the table name whose columns are returned).

• CommandTimeout

The seconds until connection timeout.

• OleDbTransaction

Get or set the transaction in which the OleDbCommand is to execute (if any).

Core Concepts and Architecture

[pic]

OleDbDataReader (1/2)

Forward-only data access

Designed only for reading data from a data source, the OleDbDataReader is a “simple class” providing forward-only data access.

“Lightweight” programming model

Because its functionality is very specific (or limited), it is “lightweight.” This is especially true if you compare using the OleDbDataReader to using OleDbDataAdapter. A point worth mentioning is that the OleDbDataReader is actually instantiated “behind the scenes” when you use the OleDbDataAdapter!

Instantiated & returned by OleDbCommand.ExecuteReader

You cannot actually instantiate the OleDbDataReader directly. As you will see in the sample, you declare the OleDbDataReader and the OleDbCommand.Execute() method will instantiate and return it.

Ties up the OleDbCommand until it is finished reading

Be aware that the OleDbCommand class cannot process any other requests while it is reading data; it blocks until it has finished.

Core Concepts and Architecture

[pic]

OleDbDataReader (2/2)

The following are the properties and methods of interest when working with the OleDbDataReader.

Properties of Interest

• FieldCount

Returns the number of fields in the result set.

• RecordsAffected

Returns the number of affected records .

Methods to retrieve data

The OleDbDataReader provides the means to retrieve data from a specific column in the currently indexed row. All the Get…() methods are “type specific” - except for GetValue() and GetValues() - meaning that if the column you wish to retrieve is of String type, use GetString().

There are corresponding Get calls for each data type (GetInt32, GetDecimal, and so on).

GetValue() returns an object in its native format which you then must handle appropriately.

GetValues() returns the entire row as an array of Objects.

Object [] cols = new Object[10] ;

reader.GetValues( cols );

The Read method is used to advance the reader to next record and returns false when no more records exist.

Core Concepts and Architecture

[pic]

OleDbDataReader Sample

This sample shows you how to create and use an OleDbDataReader.

Summary

[pic]

Summary

As you have learned, is a powerful successor to ADO, providing you with the same disconnected philosophy as the Web. Its architecture is flexible, allowing you represent your data in any logical way you wish. Furthermore, the use of XML for representing data carries forward the .NET philosophy and ensures that data can be communicated with a wide variety of data sources, objects, and applications.

Appendix—Exploring and Extending DuwamishOnline C#

[pic]

Duwamish Online

The Duwamish Online store is one of the Enterprise Samples delivered with the Visual product. Duwamish implements a fictitious e-commerce bookstore complete with catalogue, personal profile management, and order handling.

The Duwamish Sample is shipping in both a C# and a version. Before we are going to start the walkthrough for the technologies that have been presented in this module, you will first learn how to install the Duwamish sample and how the sample is organized.

Appendix—Exploring and Extending DuwamishOnline C#

[pic]

Installing the Sample (1/2)

Install the “Enterprise Samples” with Visual

When you install Visual , make sure to include the “Enterprise Samples” into your installation. By default, they will be installed. If you did not install them, run setup again and simply add them.

Location of the C# Version

The C# version of the Duwamish sample is located below your Visual installation folder in the “.\EnterpriseSamples\DuwamishOnline CS” subdirectory.

Location of the Visual Basic Version

The Visual Basic version does, of course, reside in the “.\EnterpriseSamples\DuwamishOnline VB” directory.

Installation Tasks

Before you install the sample, you should check the following prerequisites:

You should install the sample on Microsoft Windows® 2000 Server with SQL Server 2000 preinstalled. Duwamish Online uses the “English Query” feature of SQL Server, so you should make sure that this feature is installed for SQL Server 2000.

For more detailed instructions you should read the Readme.htm file before proceeding.

Once you are sure that your system has all of the required components, run the Duwamish.msi installer package by double-clicking it from the Microsoft Windows Explorer.

Appendix—Exploring and Extending DuwamishOnline C#

[pic]

Installing the Sample (2/2)

The installation wizard will guide you

The installation package will start the installation wizard that will take you through all necessary steps.

Defaults should be OK for almost everybody

Unless you want to install both the C# and Visual Basic versions side-by-side, the default values should be OK.

You may want to pay special attention to the database account (which must have administrative rights) and its password.

If you want to install both demos, you should install the first with the default values and choose different settings and directory names for the second one, so that they will not produce conflicts. The exact procedure and options will be obvious once you have installed the first version.

Setup will install database, Web site, and code

Once you have confirmed all settings, the installer will proceed to install the database, the Web site, and all related projects and code.

After installation is complete

After the installation is complete, you will find a “Duwamish.sln” file, which you can open with Visual using Open Solution on the File menu.

To run an initial build of the sample code, click Build Solution on the Build menu.

Appendix—Exploring and Extending DuwamishOnline C#

[pic]

Duwamish Architecture Overview

The architecture of Duwamish is mostly equivalent to what the enterprise templates of Visual will generate for you as a skeleton.

All access to the database is essentially encapsulated in the DataAccess module, which is being used by the BusinessRules and BusinessFacade to retrieve and store data.

Data is communicated throughout the layers using objects that are being provided by the Common module, while the SystemFramework supplies auxiliary functionality for diagnostics and other technical tasks.

The Web module implements the user interface for the application, accessing all functionality through the BusinessFacade.

Appendix—Exploring and Extending DuwamishOnline C#

[pic]

Common Components

mon

The Common namespace (and subproject) contains all configuration options that are common to all parts of the system.

Also common to the entire system are the definitions for the catalogue (Books, Categories) and the order system (Customer, OrderData) and consequently they are located in this shared project that is being used by all layers.

While the data is held in DataSets, this should not be confused with being the actual database layer. The Common namespace provides its own, internal relational view of the Duwamish data that’s being mapped to a physical data store by the DataAccess layer (next slide).

Duwamish7.SystemFramework

The SystemFramework contains all utility classes that are implemented to serve a specific technical purpose but are not immediately related to the business code. All of the code in this project is generally useful and applies to projects beyond the scope of Duwamish.

It contains diagnostic utility classes, pre and post condition checking, and dynamic configuration tools.

Appendix—Exploring and Extending DuwamishOnline C#

[pic]

Duwamish7.DataAccess

Contains all database-related code

The DataAccess namespace contains all database-related code in Duwamish, providing a central point for maintenance if the underlying data model needs to be optimized or extended.

The DataAccess module maps the common definitions for the internal data representation that are defined in the Common.Data namespace to the physical layout of the underlying database.

Uses architecture

The project builds on the infrastructure and uses the SQL Server managed provider to access the data store.

To retrieve and manipulate data, DataAccess uses OleDbDataAdapters bound to the DataSets provided by the Common.Data subsystem.

Optimized for performance using stored procedures

To optimize performance, the sample uses only a minimal set of “ad-hoc” SQL commands and relies heavily on stored procedures, which are substantially faster.

Appendix—Exploring and Extending DuwamishOnline C#

[pic]

Duwamish7.BusinessRules

Implements all business rules

The BusinessRules layer serves to implement all logic that is mandated by the system requirements. It validates data, implements calculations, and performs the manipulation of data.

All data access performed through DataAccess

All modifications that are being made to the underlying data store are performed through the DataAccess layer.

Appendix—Exploring and Extending DuwamishOnline C#

[pic]

Duwamish7.BusinessFacade

Implements logical business subsystems

The BusinessFacade sits on top of the BusinessRules and provides a logical subsystem view. The BusinessFacade provides consistent, separate interfaces to the customer system, the order system, and the product system.

While data is read through the DataAccess layer, all manipulation is validated and performed through the BusinessRules.

Appendix—Exploring and Extending DuwamishOnline C#

[pic]

Duwamish7.Web

Implements the user interface for Web access

The Web namespace implements the full user interface for the application.

Uses architecture

The UI is built on top of using Web Forms, custom Web controls, validation, “code behind forms,” and many more innovations.

All functionality accessed through BusinessFacade

Of course, all of the data displayed to the user and all interactions run through the BusinessFacade. This layered model enables reusing the entire back end for a shop with a radically different look and behavior or to expose parts of the application as a Web Service or a Windows Forms application.

Appendix—Exploring and Extending DuwamishOnline C#

[pic]

Shop at Duwamish

Demo: Duwamish in Action

Appendix—Exploring and Extending DuwamishOnline C#

[pic]

Exploring Features in Duwamish Online

To familiarize ourselves with the Duwamish sample code and to see in action, we’re first going to take a brief tour through the various building blocks.

At this point it is assumed that you could successfully install the Duwamish sample for C# and that you are able to open the solution file “Duwamish.sln” using File/Open Solution in Visual .

To make navigation a bit easier and to allow us to more easily find what we want to show you, we’re first going to identify the two key tools in Visual that are used in this walkthrough.

On the upper right side of your Visual window you should see a number of tabbed, overlapped tool windows—try locating the Solution Explorer and the Class View windows.

If you can’t find them there, bring both up by clicking Solution Explorer and Class View on the View menu.

Common

First, go to the Solution Explorer. The Duwamish sample consists of six separate projects—one for each subsystem that we’ve seen in the architectural overview.

The Common project is the logical first stop on this tour, as it is where we find the DataSet support in Duwamish.

Looking at the Common project you will notice that there are four .cs files: BookData, CategoryData, CustomerData, and OrderData. In fact, each of these defines a class that contains a DataTable.

We will start exploring the code by opening the file CustomerData.cs in the Common project.

The first thing you’ll notice is that the Duwamish team used cascaded namespaces to organize their code into logical groups. The base namespace for the entire Duwamish sample is Duwamish7, and the subnamespace for this project is mon.Data.

Beneath the namespace declaration you’ll see the using declarations for all namespaces that are being used and referenced within the CustomerData.cs compilation unit (file).

As we scroll down we find that CustomerData.cs defines a single, public class CustomerData that implements a simple wrapper around Customer data. To get a better idea of the code structure, use the key sequence CTRL+-M, CTRL+-O, or select Edit/Outlining/Collapse to Definitions.

The CustomerData class implements two public methods (constructors) that build a DataTable to hold the customer data and one “helper” BuildDataTable.

Before we get ahead of ourselves, let us first look at data members of the CustomerData class; you can find these at the top of the class declaration. Let us concern ourselves only with a subset to keep things simple:

public const String CUSTOMERS_TABLE = "Customers";

public const String EMAIL_FIELD = "Email";

public const String NAME_FIELD = "Name";

public const String ADDRESS_FIELD = "Address";

//...

It should be obvious from the declaration what the “Customers” data member is: The name of the table to be created. The others may be less obvious. These are the names of the table columns (the DataColumn names).

Now, if we examine the body of either of the CustomerData constructors we will notice a call to the private method BuildDataTable. Scanning down to the BuildDataTable definition, we come to the interesting part.

Here we can see that the DataTable is created using the Customers string:

DataTable Table = new DataTable(CUSTOMERS_TABLE);

Next, the DataTable’s columns collection is retrieved. This is needed in order to add columns to the table.

ColumnsCollection Columns = Table.Columns;

With the ColumnsCollection in hand, each DataColumn is added to the DataTable using the collection’s Add method.

The last line of the method adds the new DataTable to the DataSet:

this.Tables.Add(Table);

The other classes in the Common project are quite similar; we won’t explore them here.

DataAccess

We will start exploring the DataAccess project by opening the file Customers.cs.

As we scroll down we find that Customers.cs defines a single, public class “Customers” that implements a simple, common wrapper around data access for information on Customers.

The Customers class is designed to manage Customer information. It performs queries on the data source using a SQLOleDbDataAdapter class and the CustomerData class declared and defined in the CustomerData.cs file.

Before we get ahead of ourselves, let us first look at data members of the class and note the declaration of a SQLOleDbDataAdapter object.

protected SQLOleDbDataAdapter m_DSCommand;

This data member is used to issue the queries to the data source and DataSet.

Next, look into the Customers constructor. Here you will find the instantiation of the SQLOleDbDataAdapter and, more interesting, the use of TableMappings.

m_DSCommand.TableMappings.Add (

"Table",

CustomerData.CUSTOMERS_TABLE );

As the well-commented code conveys, the purpose of this statement is to map the data source’s table to a corresponding DataSet: CustomerData.

Now look at the UpdateCustomers method responsible for synchronizing the information in the CustomerData with the database Customer table.

The first thing we need to note is that the UpdateCustomers method takes one “in” parameter, Customer, of type CustomerData which we know to be the DataSet.

Looking into the method body, we see that the update command must first be created, or in this case, retrieved.

m_DSCommand.UpdateCommand = GetUpdateCommand();

Next we see that the SQLOleDbDataAdapter calls the Update method to update the records in the data source:

m_DSCommand.Update( Customer,

CustomerData.CUSTOMERS_TABLE );

The Update method takes two parameters: the DataSet as the source and the data source table used for the table mappings.

The code of course checks for update errors and if none occur, it commits the data changes made to the DataSet:

Customer.AcceptChanges();

That’s it! As you can see, Duwamish lends itself to exploring . Its separation of Common and DataAccess make it easy to explore and learn on your own.

Legal Notices

[pic]

Unpublished work. © 2001 Microsoft Corporation. All rights reserved.

Microsoft, ActiveX, Visual Basic, Visual Studio, and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download