ch12_ADO.NET Programming.pdf

(283 KB) Pobierz
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
Programming the .NET Compact Framework in C#
By Paul Yao & David Durant
Chapter 12
ADO.NET Programming
This chapter introduces ADO.NET, a set of Compact Framework classes used for database
programming in managed code. Like its counterpart on the desktop, the ADO.NET classes in Compact
Framework provide a convenient, flexible model for operating on memory-resident databases. Three
options are presented for the persistent storage of ADO.NET data sets: XML files, Microsoft SQL
Server CE databases on a Windows CE device, and Microsoft SQL Server databases (version 7.0 or
2000) on a desktop or server version of SQL Server. [Comment 12cs.1]
Retrieving and Displaying Data ........................................................................................ 33
The Data Reader Class ....................................................................................... 33
Updating a SQL Server CE Database.............................................................................. 41
The SqlCeDataAdapter class ........................................................................................... 42
Using the Data Adapter to Retrieve Data ............................................................ 42
Using the Data Adapter to Update a Database ................................................... 43
Querying Schema Information.......................................................................................... 47
ADO.NET Programing for SQL Server CE – Conclusion ................................................. 51
Microsoft SQL Server .................................................................................................................... 51
Connecting to SQL Server................................................................................................ 52
Creating Command Objects ............................................................................................. 59
Using SQL Server Stored Procedures ............................................................................. 59
Chapter 12 – ADO.NET Programming
Page 1
Copyright © 2003 Paul Yao & David Durant
Programming the .NET Compact Framework in C#
By Paul Yao & David Durant
Using Stored Procedures with DataSets .......................................................................... 65
Datasets and Concurrency ............................................................................................... 68
ADO.NET Programing for SQL Server – Conclusion ...................................................... 68
Web Services................................................................................................................................. 69
XML, XSD and SOAP....................................................................................................... 69
Web Classes.....................................................................................................................70
A Web Service Application ............................................................................................... 71
A Web Services Client Application ................................................................................... 79
Web Services Summary ................................................................................................... 82
Summary ....................................................................................................................................... 82
A Compact Framework program that manages a significant volume of data is
probably going to use the ADO.NET classes to manage that data. ADO.NET provides
managed code support for memory-resident data. The various ADO.NET classes
support concepts familiar to database programmers, like rows and columns, that are the
fundamental elements of a table. Some ADO.NET classes provide the ability to create
data tables, and programmatically add columns. Other classes provide the ability to add,
modify and delete rows, providing a location for a program’s in-memory data. Central to
the design of these classes is the independence of the classes from any particular
database product or storage format. This means that an ADO.NET table might be
constructed with data from two or more data sources. An ADO.NET table can also be
dynamically created, manipulated in memory, and then discarded without writing to any
persistent storage. We expect, however, that most programs that use the ADO.NET
classes will store the results of their work in a more persistent form. [Comment 12cs.2]
Table 12-1 summarizes all of the persistent data storage options available to
Compact Framework programmers. As indicated in this table, ADO.NET supports three
different storage types. Two storage types reside in the file system of a Windows CE-
powered device: SQL Server CE databases and XML files. These are appropriate for a
mobile device, such as a Pocket PC or SmartPhone, which needs to run in a self-
contained, disconnected mode. Using the SQL Server 2000 data provider, a Compact
Framework program can directly access a third type of storage on a connected server, a
SQL Server 2000 database. This requires a live network connection between the
Windows CE client system and a server running SQL Server 2000. [Comment 12cs.3]
Supported ADO.NET Data Providers
As of this writing, we know of 3 ADO.NET data providers which are
available for the Compact Framework: Microsoft SQL Server CE,
Microsoft SQL Server (version 7.0 and 2000), and one for Pocket Access
(available from In The Hand, http://www.inthehand.com). [Comment
The following data providers are available for the desktop .NET
Framework, but as of this writing are not available for Compact
Framework programs: OLE DB, ODBC, Oracle, MySQL, DB2, SyBase
There are two types of ADO.NET classes. One set of classes provides database
programming support, but are not tied to a specific database file format. Instead, the
classes operate on in-memory objects which model relational data tables. [Comment
A second set of classes, the data provider classes , provide the connection between in-
memory objects and physical databases that reside in permanent storage. Alternatively,
Chapter 12 – ADO.NET Programming
Page 2
Copyright © 2003 Paul Yao & David Durant
870585260.027.png
Programming the .NET Compact Framework in C#
By Paul Yao & David Durant
you can choose not to use a formal database, and instead allow the ADO.NET classes
to store your data in XML files in the Window CE file system. [Comment 12cs.7]
Table 12-1 Persistent storage options available on Windows CE-powered devices. [Comment
Type of Storage
Managed API
Native API
SQL Server CE Database
ADO.NET with SQL Server CE Data
Provider
ADOCE
OLEDBCE
SQL Server 2000 Database
ADO.NET with SQL Server 2000
Data Provider
ADOCE
OLEDBCE
XML Files
ADO.NET
DOM provided by msxml.dll
Raw Data Files
Win32 API file access functions
System.IO classes (see chapter 11)
CE Property Databases
n/a
CE-specific Win32 API functions
including CeCreateDataBaseEx ,
etc.
System registry
n/a
Win32 API functions required (see
chapter 11)
We expect that many databases on Windows CE-powered devices will need to be
synchronized with databases on desktop and server systems. Table 12-2 summarizes
the available options for synchronizing data with desktop and server systems. Two of
these methods are appropriate for synchronizing a device-based SQL Server CE
database with a SQL Server 2000 database: Merge Replication and Remote Data
Access (RDA). We discuss these in detail in chapter 13, Synchronizing Mobile Data .
Table 12-2 Data synchronization options on Windows CE-powered devices. [Comment
Synchronization Method
Description
Merge Replication
Publish and subscribe model for moving data between a SQL Server CE database
and a SQL Server 2000 database. Described in detail in chapter 13,
Synchronizing Mobile Data .
Remote Data Access (RDA)
An SQL-driven mechanism for moving data between a SQL Server CE database,
and a SQL Server 6.5, SQL Server 7, or SQL Server 2000 database. Described in
detail in chapter 13, Synchronizing Mobile Data .
XML Web services
General purpose remote procedure call (RPC) support and document delivery
mechanism supported by .NET Framework and .NET Compact Framework. Built
on SOAP, an XML-based industry standard remoting protocol. Described in
detail in chapter 14, Building XML Web Service Clients .
ActiveSync
General purpose mechanism for synchronizing desktop and smart-device data.
Relies on Service Providers, which currently must be implemented in native
DLLs which export a specific set of COM interfaces. Both desktop and device-
side service providers are supported. SQL Server CE does not use ActiveSync for
any of its synchronization.
However, ActiveSync does support synchronizing a Pocket Access (*.cdb) file
with a desktop Microsoft Access (*.mdb) file. Pocket Access is a discontinued
product, but third-party support 1 exists for the Pocket Access file format (in the
form of an ADO.NET-compatible data provider), and also third-party
1 For details on the ADOCE .NET Wrapper available from In The Hand, visit their web site:
http://www.inthehand.com .
Chapter 12 – ADO.NET Programming
Page 3
Copyright © 2003 Paul Yao & David Durant
870585260.028.png 870585260.029.png 870585260.030.png 870585260.001.png 870585260.002.png 870585260.003.png 870585260.004.png 870585260.005.png 870585260.006.png 870585260.007.png 870585260.008.png 870585260.009.png 870585260.010.png 870585260.011.png 870585260.012.png 870585260.013.png 870585260.014.png 870585260.015.png 870585260.016.png 870585260.017.png
 
Programming the .NET Compact Framework in C#
By Paul Yao & David Durant
Synchronization Method
Description
development tools.
Remote API (RAPI)
Part of the ActiveSync support libraries. These provide a general purpose
mechanism for accessing the object store and installable file systems of a smart
device from an ActiveSync-connected desktop system. This mechanism can be
used to install or backup anything on a CE device. However there is no specific
ADO.NET support or support for SQL databases. RAPI is described in detail in
chapter 15, The Remote API (RAPI) .
Introducing ADO.NET
ADO.NET is a set of classes for accessing and managing in-memory data. These
classes provide the primary means for bringing data from a database into a managed
code program. On the desktop, ADO.NET support is provided for accessing data in the
following types of databases: SQL Server, Sybase SQL, MySQL, Oracle, DB2, and
those with OLEDB and ODBC providers. On Windows CE, support from Microsoft is
limited to two types of databases: SQL Server CE and SQL Server (version 7.0 and
later). The ADO.NET classes provide in-memory data support in a variety of
environments for a wide range of application types, including desktop Windows Forms
applications, Web Forms applications, and also in XML Web services and in mobile
device applications. [Comment 12cs.11]
The ADO.NET classes were designed to work with data that is drawn from a variety
of sources, or in the terminology of ADO.NET, from a variety of data providers . Whatever
the source of the data, the ADO.NET classes allow data to be managed and presented
in a standard fashion. To make this happen, some of the ADO.NET classes are provider-
specific, meaning capable of working with only one data provider, such as the
SqlCeConnection class or the ODBCConnection classe. [Comment 12cs.12]
Others classes, such as the DataSet , DataTable and DataView are provider-
independent . The data contained within them has been converted to the ADO.NET data
types; and the structure of the data supported by these classes is independent of the
database engine that provides the data. This creates the intriguing possibility that an
ADO.NET data set might contain data tables with data drawn from multiple sources; for
example, a data set could contain some tables that have been read from a Microsoft
SQL Server CE database and other tables that are read from a Microsoft SQL Server
2000 database. [Comment 12cs.13]
Limitations of ADO.NET in the Compact Framework
Strongly-typed data sets are not supported in the current version of the
Compact Framework. Strongly-typed data sets are bound to the schema
of a database, which means we can use database field names instead of
column numbers. [Comment 12cs.14]
Early-binding makes it easier to write code, makes it easier to read code,
and so makes it easier to maintain the code. There are run-time benefits
as well, because early-bound, strongly-typed data sets allow the compiler
to create code that executes more efficiently; which it can do because the
data set schema is known at compile-time. This capability may become
Chapter 12 – ADO.NET Programming
Page 4
Copyright © 2003 Paul Yao & David Durant
870585260.018.png 870585260.019.png 870585260.020.png 870585260.021.png 870585260.022.png 870585260.023.png 870585260.024.png 870585260.025.png
 
Programming the .NET Compact Framework in C#
By Paul Yao & David Durant
available in future versions of Compact Framework, but it is not available
in the current version. [Comment 12cs.15]
The ADO.NET classes treat data as provider-agnostic, meaning there is no easy way
– and, hopefully, no need to –determine which data came from what data source. You
can even build and populate data sets and data tables from scratch without reference to
a particular database. As part of its provider-independent design, the ADO.NET classes
can read and write their data to XML files. [Comment 12cs.16]
A Tiered Approach
Because of the capabilities of the ADO.NET classes, you can use either a two-tier or
three-tier approach to data management. [Comment 12cs.17]
In a two-tier approach, you submit SQL queries to the database and retrieve the data
into program variables and control properties. You then build and submit SQL
statements to make the user requested changes to the data and submit them to the
database. See figure 12-1. This is often referred to as the connected approach, as the
application must be connected to the database while transferring data. Do not confuse
this meaning of the term connected with being connected to a network. Throughout this
chapter, when we use the term connected we mean connected to the SQL Server or
SQL Server CE database. [Comment 12cs.18]
Figure 12-1 – A two tiered approach
[Hand drawing – to be delivered. David.]
In a three-tier approach, you bring the data into a memory-resident dataset, and then
bind some or all data to controls on your form. Changes made by the user to the data in
the form are automatically propagated back to the dataset; class methods then
propagate changes from a dataset to the database. See figure 12-2. This is often
referred to as the disconnected approach, meaning that the application is not connected
to the database while it is accessing the dataset data. [Comment 12cs.19]
Figure 12-2 – A three tiered approach
[Hand drawing – to be delivered. David.]
The ADO.NET Classes
Table 12-3 presents an overview of the classes which can be used to access SQL
Server CE databases. We present most of these classes in detail later in this chapter.
They fall into four general categories: [Comment 12cs.20]
1. File system classes [Comment 12cs.21]
2. Provider specific ADO.NET classes [Comment 12cs.22]
3. Provider independent ADO.NET classes [Comment 12cs.23]
4. Data binding classes [Comment 12cs.24]
We discuss the file system classes in detail in chapter 11, Storage . And we cover controls used
for data binding in chapter 8, Data Binding . [Comment 12cs.25]
Chapter 12 – ADO.NET Programming
Page 5
Copyright © 2003 Paul Yao & David Durant
870585260.026.png

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin