Sybex - Mastering Visual Basic.NET Database Programming (2002), ASP.NET
[ Pobierz całość w formacie PDF ]
SYBEX Sample Chapter
Mastering
™
Visual Basic
®
.NET
Database Programming
Evangelos Petroutsos; Asli Bilgin
Chapter 6: A First Look at ADO.NET
Copyright © 2002 SYBEX Inc., 1151 Marina Village Parkway, Alameda, CA 94501. World rights reserved. No part of this
publication may be stored in a retrieval system, transmitted, or reproduced in any way, including but not limited to
photocopy, photograph, magnetic or other record, without the prior agreement and written permission of the publisher.
ISBN: 0-7821-2878-5
SYBEX and the SYBEX logo are either registered trademarks or trademarks of SYBEX Inc. in the USA and other
countries.
TRADEMARKS: Sybex has attempted throughout this book to distinguish proprietary trademarks from descriptive terms
by following the capitalization style used by the manufacturer. Copyrights and trademarks of all products and services
listed or described herein are property of their respective owners and companies. All rules and laws pertaining to said
copyrights and trademarks are inferred.
This document may contain images, text, trademarks, logos, and/or other material owned by third parties. All rights
reserved. Such material may not be copied, distributed, transmitted, or stored without the express, prior, written consent
of the owner.
The author and publisher have made their best efforts to prepare this book, and the content is based upon final release
software whenever possible. Portions of the manuscript may be based upon pre-release versions supplied by software
manufacturers. The author and the publisher make no representation or warranties of any kind with regard to the
completeness or accuracy of the contents herein and accept no liability of any kind including but not limited to
performance, merchantability, fitness for any particular purpose, or any losses or damages of any kind caused or alleged
to be caused directly or indirectly from this book.
Sybex Inc.
1151 Marina Village Parkway
Alameda, CA 94501
U.S.A.
Phone: 510-523-8233
www.sybex.com
Chapter 6
A First Look at ADO.NET
How does ADO.NET work?
Using the ADO.NET object model
The Connection object
The Command object
The DataAdapter object
The DataReader object
The DataSet object
Navigating through DataSets
Updating Your Database by using DataSets
Managing concurrency
It’s time now to
get into some real database programming with the .NET Framework compo-
nents. In this chapter, you’ll explore the Active Data Objects (ADO).NET base classes. ADO.NET,
along with the XML namespace, is a core part of Microsoft’s standard for data access and storage.
As you recall from Chapter 1, “Database Access: Architectures and Technologies,” ADO.NET com-
ponents can access a variety of data sources, including Access and SQL Server databases, as well as
non-Microsoft databases such as Oracle. Although ADO.NET is a lot different from classic ADO,
you should be able to readily transfer your knowledge to the new .NET platform. Throughout this
chapter, we make comparisons to ADO 2.
x
objects to help you make the distinction between the
two technologies.
For those of you who have programmed with ADO 2.
x
, the ADO.NET interfaces will not
seem all that unfamiliar. Granted, a few mechanisms, such as navigation and storage, have changed,
but you will quickly learn how to take advantage of these new elements. ADO.NET opens up a
whole new world of data access, giving you the power to control the changes you make to your
data. Although native OLE DB/ADO provides a common interface for universal storage, a lot of
228
Chapter 6
A FIRST LOOK AT ADO.NET
the data activity is hidden from you. With client-side disconnected RecordSets, you can’t control how
your updates occur. They just happen “magically.” ADO.NET opens that black box, giving you
more granularity with your data manipulations. ADO 2.
x
is about common data access. ADO.NET
extends this model and factors out data storage from common data access. Factoring out functional-
ity makes it easier for you to understand how ADO.NET components work. Each ADO.NET com-
ponent has its own specialty, unlike the RecordSet, which is a jack-of-all-trades. The RecordSet could
be disconnected or stateful; it could be read-only or updateable; it could be stored on the client or on
the server—it is multifaceted. Not only do all these mechanisms bloat the RecordSet with function-
ality you might never use, it also forces you to write code to anticipate every possible chameleon-like
metamorphosis of the RecordSet. In ADO.NET, you always know what to expect from your data
access objects, and this lets you streamline your code with specific functionality and greater control.
Although a separate chapter is dedicated to XML (Chapter 10, “The Role of XML”), we must
touch upon XML in our discussion of ADO.NET. In the .NET Framework, there is a strong syn-
ergy between ADO.NET and XML. Although the XML stack doesn’t technically fall under
ADO.NET, XML and ADO.NET belong to the same architecture. ADO.NET persists data as
XML. There is no other native persistence mechanism for data and schema. ADO.NET stores data
as XML files. Schema is stored as XSD files.
There are many advantages to using XML. XML is optimized for disconnected data access.
ADO.NET leverages these optimizations and provides more scalability. To scale well, you can’t main-
tain state and hold resources on your database server. The disconnected nature of ADO.NET and
XML provide for high scalability.
In addition, because XML is a text-based standard, it’s simple to pass it over HTTP and through
firewalls. Classic ADO uses a binary format to pass data. Because ADO.NET uses XML, a ubiqui-
tous standard, more platforms and applications will be able to consume your data. By using the
XML model, ADO.NET provides a complete separation between the data and the data presentation.
ADO.NET takes advantage of the way XML splits the data into an XML document, and the
schema into an XSD file.
By the end of this chapter, you should be able to answer the following questions:
What are .NET data providers?
What are the ADO.NET classes?
What are the appropriate conditions for using a DataReader versus a DataSet?
How does OLE DB fit into the picture?
What are the advantages of using ADO.NET over classic ADO?
How do you retrieve and update databases from ADO.NET?
How does XML integration go beyond the simple representation of data as XML?
Let’s begin by looking “under the hood” and examining the components of the ADO.NET stack.
HOW DOES ADO.NET WORK?
229
How Does ADO.NET Work?
ADO.NET base classes enable you to manipulate data from many data sources, such as SQL Server,
Exchange, and Active Directory. ADO.NET leverages .NET data providers to connect to a database,
execute commands, and retrieve results.
The ADO.NET object model exposes very flexible components, which in turn expose their own
properties and methods, and recognize events. In this chapter, you’ll explore the objects of the
ADO.NET object model and the role of each object in establishing a connection to a database and
manipulating its tables.
Is OLE DB Dead?
Not quite. Although you can still use OLE DB data providers with ADO.NET, you should try to use the man-
aged .NET data providers whenever possible. If you use native OLE DB, your .NET code will suffer because
it’s forced to go through the COM interoperability layer in order to get to OLE DB. This leads to performance
degradation. Native .NET providers, such as the
System.Data.SqlClient
library, skip the OLE DB layer
entirely, making their calls directly to the native API of the database server.
However, this doesn’t mean that you should avoid the OLE DB .NET data providers completely. If you are
using anything other than SQL Server 7 or 2000, you might not have another choice. Although you will expe-
rience performance gains with the SQL Server .NET data provider, the OLE DB .NET data provider compares
favorably against the traditional ADO/OLE DB providers that you used with ADO 2.
x
. So don’t hold back
from migrating your non-managed applications to the .NET Framework for performance concerns. In addi-
tion, there are other compelling reasons for using the OLE DB .NET providers. Many OLE DB providers are
very mature and support a great deal more functionality than you would get from the newer SQL Server
.NET data provider, which exposes only a subset of this full functionality. In addition, OLE DB is still the way
to go for universal data access across disparate data sources. In fact, the SQL Server distributed process
relies on OLE DB to manage joins across heterogeneous data sources.
Another caveat to the SQL Server .NET data provider is that it is tightly coupled to its data source. Although
this enhances performance, it is somewhat limiting in terms of portability to other data sources. When
you use the OLE DB providers, you can change the connection string on the fly, using declarative code such
as COM+ constructor strings. This loose coupling enables you to easily port your application from an SQL
Server back-end to an Oracle back-end without recompiling any of your code, just by swapping out the con-
nection string in your COM+ catalog.
Keep in mind, the only native OLE DB provider types that are supported with ADO.NET are
SQLOLEDB
for
SQL Server,
MSDAORA
for Oracle, and
Microsoft.Jet.OLEDB.4
for the Microsoft Jet engine. If you are so
inclined, you can write your own .NET data providers for any data source by inheriting from the
Sys-
tem.Data
namespace.
At this time, the .NET Framework ships with only the SQL Server .NET data provider for data access within
the .NET runtime. Microsoft expects the support for .NET data providers and the number of .NET data
providers to increase significantly. (In fact, the ODBC.NET data provider is available for download on
Microsoft’s website.) A major design goal of ADO.NET is to synergize the native and managed interfaces,
advancing both models in tandem.
230
Chapter 6
A FIRST LOOK AT ADO.NET
You can find the ADO.NET objects within the
System.Data
namespace. When you create a new
VB .NET project, a reference to the
System.Data
namespace will be automatically added for you, as
you can see in Figure 6.1.
Figure 6.1
To use ADO.NET,
reference the
System.Data
namespace.
To comfortably use the ADO.NET objects in an application, you should use the
Imports
state-
ment. By doing so, you can declare ADO.NET variables without having to fully qualify them. You
could type the following
Imports
statement at the top of your solution:
Imports System.Data.SqlClient
After this, you can work with the SqlClient ADO.NET objects without having to fully qualify the
class names. If you want to dimension the SqlClientDataAdapter, you would type the following short
declaration:
Dim dsMyAdapter as New SqlDataAdapter
Otherwise, you would have to type the full namespace, as in:
Dim dsMyAdapter as New System.Data.SqlClient.SqlDataAdapter
Alternately, you can use the visual database tools to automatically generate your ADO.NET code
for you. As you saw in Chapter 3, “The Visual Database Tools,” the various wizards that come with
VS .NET provide the easiest way to work with the ADO.NET objects. Nevertheless, before you use
these tools to build production systems, you should understand how ADO.NET works program-
matically. In this chapter, we don’t focus too much on the visual database tools, but instead concen-
trate on the code behind the tools. By understanding how to program against the ADO.NET object
model, you will have more power and flexibility with your data access code.
[ Pobierz całość w formacie PDF ]