Introduction to Eclipse, Unit Testing and JUnit

arghtalentΔιαχείριση Δεδομένων

31 Ιαν 2013 (πριν από 4 χρόνια και 8 μήνες)

414 εμφανίσεις

ADO.NET


Andrei Otcheretianski

Tomer Rothschild

February 2nd, 2006

ADO .NET

2

Contents


Introduction


What is ADO.NET?


Data Access


Motivation
-

Why Moving to ADO.NET?


ADO.NET Objects


Content components


Managed
-
provider components


ADO.NET & Transactions


ADO.NET and XML


.NET Framework Data Providers


ADO.NET 2.0 Added Features


Summary
-

Pros & Cons

February 2nd, 2006

ADO .NET

3

What is ADO.NET?


ADO.NET

(ActiveX Data Objects) is the primary relational data
access model for Microsoft .NET
-
based applications.



The data access objects role is to serve as an interface between
the client application and the data provider


the DBMS. This
modularity results in two big advantages:



Allowing the writing of applications that use standard types
and features, resulting in DBMS independent code.


Unified API for the database driver implementers.


February 2nd, 2006

ADO .NET

4

Things Weren’t Always So Simple…



Let’s have a Brief Look at the evolution that lead to ADO.NET


At first, programmatic access to databases was performed by
native libraries, such as DBLib for SQL Server, and the Oracle
Call Interface (OCI) for Oracle.



This allowed for fast database access because no extra layer
was involved.


However, it also meant that modularity was absent. i.e. loosing
the two advantages discussed on the previous slide.



February 2nd, 2006

ADO .NET

5

Data Access Evolution (1)


ODBC
:



As a solution, Microsoft and other companies developed the
Open Database Connectivity, or ODBC.



This provided a common data access layer, which could be used
to access almost any relational database management system.



In practice, there were some differences in the SQL dialect
supported hence, violating the main principle of modularity.



Nonetheless, perhaps the most important feature of ODBC is the
fact that it was an open standard, widely adopted even by the
Open Source community
.








February 2nd, 2006

ADO .NET

6

Data Access Evolution (2)


DAO:


As the importance of Visual Basic grew, there was a need for a
data access technology that could be used more naturally from
VB.


DAO (Direct Access Objects) provided a simple object model for
talking to Microsoft's Access desktop database. As DAO was
optimized for Access, it was very fast.



RDO:


Due to its optimization for Access, DAO was very slow when
used with ODBC data sources. To get round this, Microsoft
introduced Remote Data Objects

(RDO).


RDO was designed specifically for access to ODBC data
sources. RDO is essentially a thin wrapper over the ODBC API.





February
2
nd,
2006

ADO .NET

7

Data Access Evolution (3)


OLE DB:



As part of the OLE (Object linking and Embedding) distributed
object system, the OLE DB is used for accessing different types
of data stores in a uniform manner.



ADO:


ActiveX Data Objects is the technology that gave its name to
ADO.NET (although in reality the differences are far greater than
the similarities).


ADO is merely an OLE DB consumer


a thin layer allowing
users of high
-
level languages to access OLE DB through a
simple object model.


In a way, ADO is to OLE DB more or less what RDO was to
ODBC


February
2
nd,
2006

ADO .NET

8

Why Moving to ADO.NET?


Why not simply continue to use ADO in the .NET framework?


It's perfectly possible to carry on using ADO in .NET
applications through COM interoperability.


However, there are some very good reasons why ADO wasn't
really suited to the new programming environment:



Using Managed Classes


Cleaner Architecture


Cross
-
Language Support


XML Support


Optimized Object Model









February
2
nd,
2006

ADO .NET

9

Why Moving


Managed classes &
Cleaner Architecture


Using Managed Classes


The alternative
-

using .NET then COM interoperability
-

adds
overhead to the application.


Takes advantage of the benefits of the CLR such as JIT
compilation and the managed execution environment.



Cleaner Architecture


As we noted above, ADO is no more than a thin layer over
OLE DB.


ADO.NET can be much faster than ADO, as the providers
communicate directly with the data source.







February
2
nd,
2006

ADO .NET

10

Why Moving


Cross
-
Language Support
& XML Support


Cross Language Support


ADO was aimed primarily at VB programmers. This lead to
using optional method parameters, which are not supported
by C
-
based languages such as C#.


This means that if you use ADO from C#, you will need to
specify all

parameters in method calls. This makes ADO
programming under .NET considerably more time
-
consuming



XML Support


XML is absolutely integral to ADO.NET, and not just an add
-
on.


As we shall see later on in the demo, XML is the format used
to serialize and transport DataSets.







February
2
nd,
2006

ADO .NET

11

Why Moving


Optimized Object Model


Optimized Object Model


The .NET Framework is aimed squarely at developing
distributed applications, and particularly Internet
-
enabled
applications.



In this context, for example, we don't want to hold a
connection open for a long time, as this could create a
bottleneck and destroy scalability.



ADO didn't encourage disconnected recordsets, whereas
ADO.NET has different classes for connected and
disconnected access, and doesn't permit updateable
connected recordsets.







February
2
nd,
2006

ADO .NET

12

ADO .NET
-

Objects

.Net Data Provider

DataSet

Connection

Transaction

Command

Parameters

DataReader

Data Adapter

Select

Insert

Delete

Update

DataRelations

Data Table

DataColumn

DataRow

Constraints

DB

<XML>

February
2
nd,
2006

ADO .NET

13

ADO .NET


Objects(
2
)


In ADO .NET there are two class groups.



Content components



Managed
-
provider components

February
2
nd,
2006

ADO .NET

14

ADO .NET


Objects(
3
)


Content components


The
content components

hold actual data and include


DataSet


DataTable


DataView


DataColumn


DataRow


DataRelation



Managed
-
provider components


These components actually talk to the database to assist in data
retrievals and updates. Such objects include the Connection,
Command, Data Reader and Data Adapter.



The managed
-
provider components are split into several groups
designed for each provider as we will see later.

February
2
nd,
2006

ADO .NET

15

Connection


Connection



Provides

a connection to the database . There are typical Open() and
Close(), plus BeginTransaction() returning an object to control a
database
transaction
.



To open a connection to a data source you need to provide a
connection string.



Example:



"Network Library=DBMSSOCN; Data Source=
132.245.124.37
,
1433
;Initial
Catalog=myDatabaseName;User
ID=myUsername;Password=myPassword"


February
2
nd,
2006

ADO .NET

16

Connection(
2
)


Managing Database Connections



DB connection represent a critical expensive and limited resource.



Connection Pooling



Open connections late and close them early

February
2
nd,
2006

ADO .NET

17

Connection Pooling


Connection Pooling


Database connection pooling enables an application to reuse an
existing connection from a pool, instead of repeatedly establishing a
new connection with the database.


ADO .NET data providers provide transparent connection pooling,
the exact mechanics of which vary for each provider.



For example, using SQL Server .Net data provider you can configure
connection pooling by adding a name
-
value pairs to the connection
string:
“[…];Max Pool Size=
75
; Min Pool Size=
5



Connections are pooled through an exact match algorithm on the
connection string!!! The pooling mechanism is even sensitive to
spaces between name
-
value pairs.



February
2
nd,
2006

ADO .NET

18

Command


Command


This is the pipeline to the backend data.



The
command

object provides direct execution of the SQL
command to the database.



You can use the command to either
ExecuteNonQuery()
, which will
action an SQL statement (such as a DELETE command) upon the
data.


ExecuteReader() links straight in to the
Data Reader
object.


February
2
nd,
2006

ADO .NET

19

Commands and Parameters


Commands and Parameters



When a SQL statement is executed, the database has to generate
an execution plan for it.



Use Prepare() method to generate and store an execution plan in
advance. The plan will be saved in the database cache so
subsequent statements will be executed much faster.



This method should be used only if the SQL statement will be
executed multiple times.




Use Parameter object, instead of hardcoding parameter values in a
SQL statement.

February
2
nd,
2006

ADO .NET

20

Parameters
-

Example

string sConnString =
"Server=(local);Database=Northwind;Integrated Security=True;"
;

string sSQL =
"UPDATE Customers SET City=@sCity WHERE CustomerID=@sCustomerID"
;


SqlConnection oCn =
new

SqlConnection(sConnString))



SqlCommand oCmd =
new

SqlCommand(sSQL, oCn))


oCmd.CommandType = CommandType.Text;


oCmd.Parameters.Add(
"@sCustomerID"
, SqlDbType.NChar,
5
);


oCmd.Parameters.Add(
"@sCity"
, SqlDbType.NVarChar,
15
);


oCn.Open();


oCmd.Prepare();
// Prepare the execution plan


oCmd.Parameters[
"@sCustomerID"
].Value =
"ALFKI"
;


oCmd.Parameters[
"@sCity"
].Value =
“Jerusalem"
;


oCmd.ExecuteNonQuery();


oCmd.Parameters[
"@sCustomerID"
].Value =
"CHOPS"
;


oCmd.Parameters[
"@sCity"
].Value =
“Tel
-
Aviv"
;


oCmd.ExecuteNonQuery();



oCn.Close();


February
2
nd,
2006

ADO .NET

21

Data Reader


Data Reader




This object essentially takes a stream of data from the Command
object and allows you to read it.



It's like a forward
-
only Recordset from the ADO and is very efficient
because it stores only one record in the memory at a time.



However this uses a server
-
side cursor, so you should avoid it too
much as it naturally requires an open connection.

February
2
nd,
2006

ADO .NET

22

Data Adapter


Data Adapter



It essentially serves as a middle man, going through your connection
to retrieve data, then passing that into a
DataSet
.



You can then pass the
DataSet

back to the Data Adapter, which will
go and update the database.




The SQL statements for each command are specified in the
InsertCommand, UpdateCommand, SelectCommand and
DeleteCommand properties.

February
2
nd,
2006

ADO .NET

23

DataSet

DataSet

DataTable

Relations

XML Schema

DataTable

DataColumn

DataRow

Constraints

February
2
nd,
2006

ADO .NET

24

DataSet(
2
)


DataSet



The “king” of the ADO .NET



This object is actually a memory resident representation of data that
provides a consistent relational programming model regardless of the
data source.



The data stored in the DataSet object is
disconnected

from the
database!

February
2
nd,
2006

ADO .NET

25

DataSet(
3
)


The DataSet object model is made up of three collections,
Tables, Relations and ExtendedProperties. These collections
make up the relational data structure of the DataSet



Tables Collection



Relations Collection



ExtendedProperties Collection



February
2
nd,
2006

ADO .NET

26

DataSet(
4
)


Tables Collection


DataSet.Tables



Each DataTable represents a table of data from the data source.


DataTable is made up of a Columns collection and a Rows collection.



Relations Collection


DataSet.Relations


The DataRelation objects define a parent
-
child relationship between
two tables based on foreign key values.



ExtendedProperties Collection


DataSet.ExtendedProperties


The ExtendedProperties is a user
-
defined properties collection.


Can be used to store custom data related to the DataSet, such as the
time when the DataSet was constructed.

February
2
nd,
2006

ADO .NET

27

DataSet(
5
)

DataSet

DataTable

DataView

PrimaryKey

DataRow

DataColumn

DataRelationCollection

ExtendedPropeties

DataTableCollection

ChildRelations

ParentRelations

Constraints

ExtendedProperties

DataColumnCollection

DataRowCollection

ExtendedProperties

February
2
nd,
2006

ADO .NET

28

DataSet(
6
)


Connecting a DataSet to a data source



In order to connect a
DataSet

to a data source, we need to use the
DataAdapter

as an intermediary between the
DataSet
and the .NET
data provider.



Let’s see some code…

February
2
nd,
2006

ADO .NET

29

DataSet


Example


Filling the DataSet:


// Connection String

String conStr =
@"Provider=Microsoft.Jet.OLEDB.
4.0
;DataSource=C:
\
NWind.mdb"
;

//SQL Command

String sqlStr =
"SELECT EmployeeID, FirstName, LastName FROM
Employees"
;

// Create a new DataAdapter object

OleDbDataAdapter da =
new

OleDbDataAdapter(sqlStr, conStr);

// Create a new DataSet

DataSet ds =
new

DataSet();

// Fill the DataSet

da.Fill(ds,
"Employees")
;

February
2
nd,
2006

ADO .NET

30

Transactions


Why do we need transactions?


Consider a Web
-
based retail application that processes purchase
orders. Each order requires three distinct operations that involve
three database updates:


The inventory level must be reduced by the quantity ordered.


The customer’s credit level must debited by the purchase amount.


A new order must be added to the orders database.



It is essential that these three distinct operations be performed as a
unit and in atomic fashion.

February
2
nd,
2006

ADO .NET

31

Transactions(
3
)


Performing Manual Transactions with ADO .NET


ADO .NET supports a transaction object that you can use to begin a
new transaction and then explicitly control whether it should be
committed or rolled back.



The transaction object is associated with a single database
connection and obtained by the
BeginTransaction()

method of the
connection object.



You must explicitly associate each command objects with the
transaction by setting the
Transaction
property.



Note that since a transaction object associated with a single
connection you can perform transactions only with a single database.

February
2
nd,
2006

ADO .NET

32

Transactions


Example


Transaction Example




SqlConnection db =
new

SqlConnection(conStr);


SqlTransaction transaction;



db.Open();


transaction = db.BeginTransaction();


try



{


new

SqlCommand(
"INSERT INTO TransactionDemo(Text) VALUES ('Row
1
')"
, db,




transaction).ExecuteNonQuery();


new

SqlCommand(
"INSERT INTO TransactionDemo(Text) VALUES ('Row
2
');"
,db,




transaction).ExecuteNonQuery();


new

SqlCommand(
"INSERT INTO CrashMeNow VALUES ('Die', 'Die', 'Die');"
, db,




transaction).ExecuteNonQuery();


transaction.Commit();


}


catch

(SqlException sqlError)


{


transaction.Rollback();


}


db.Close();

February
2
nd,
2006

ADO .NET

33

ADO .NET and XML


XML and Traditional Data Access



We have talked about data access as it relates to traditional data
access and relational data.



A wide variety of data can be represented in a non relational way.



ADO .NET makes it extremely easy to create and read XML
documents.



The data in ADO.NET is transported in XML format.



You can parse XML in .NET programmatically using the
System.Xml.XmlDataDocument

object.

February
2
nd,
2006

ADO .NET

34

ADO .NET and XML (
2
)


Saving DataSet as XML



Fill the DataSet with data.


Use
DataSet.WriteXml(fileName)

to write an XML file.



Loading XML files



Loading the XML file is not more complex than writing it.


Use
DataSet.ReadXml(fileName)

to load the data.


Wrap
ReadXml
call with try and catch because we can’t guarantee
that the file contains valid XML.


February
2
nd,
2006

ADO .NET

35

ADO .NET and XML (
3
)


Schemas



Schemas provide a mechanism for defining rules that XML
documents must adhere to, and which help everyone understand
what the data held in an XML document actually is.



Generating a Schema



Fill the dataset


DataSet.WriteXmlSchema(SchemaFileName);

February
2
nd,
2006

ADO .NET

36

ADO .NET and XML (
4
)

February
2
nd,
2006

ADO .NET

37

ADO .NET and XML (
5
)


Typed DataSets



Typed DataSets can provide a more intuitive mechanism for the
manipulation of data.


A typed DataSet is
early

bound to an XML Schema Definition (XSD)
file.


Schemas provide very rigorous definitions for the types of particular
objects. In conjunction with the typed DataSet, they can allow access
to the tables and columns of a DataSet using meaningful names.


This not only improves the readability of the code


Enables Visual Studio .NET's IntelliSense feature to make context
-
sensitive suggestions as you type in code.



February
2
nd,
2006

ADO .NET

38

Data Providers Diagram

COM InterOp

OLE DB .Net Data Provider

OleDbConnection

OleDbCommand

OleDbDataReader

OleDbDataAdapter

SQL Server
6.5
and earlier

Data Table

DataSet

SQL Server/Oracle

.Net Data Provider

Connection

Command

DataReader

DataAdapter

SQL Server
7.0
and later
/ Oracle

OLE DB

.
NET Managed App

Unmanaged


App

ADO

SQLOLEDB

Misc DB

February
2
nd,
2006

ADO .NET

39

Data Providers in the .NET (
1
)
Framework


SqlClient Provider


Should be used to access SQL Server
7.0
or later, and MSDE
databases.


Can't be used with SQL Server
6.5
or earlier.



OleDb Provider


Good for almost anything other than SQL Server
7.0
or later,
or Oracle.


Using the ODBC data access through the OleDb is
discouraged


think of the architecture involved:


ADO.NET


COM interop


(optional) OLE DB services


OLE DB provider


ODBC driver


data source!






February
2
nd,
2006

ADO .NET

40

Data Providers in the .NET (
2
)
Framework


The Odbc Provider


Should be used whenever you need to access a data source
with no direct or OLE DB provider (PostgreSQL, Paradox,
dBase…),



OracleClient Provider


Supports Oracle data types, as well as ref cursors


Avoids the cost of COM interop, and also employs Oracle
-
specific optimizations

February
2
nd,
2006

ADO .NET

41

Generic Interfaces


ADO.NET was designed from the beginning to allow the provider
writer the space to support database
-
specific features.


Programmers who wish to build applications that interact with
databases of more than one brand may resort to the generic
interfaces:


IDbConnection


IDbCommand


IDataReader


IDbTransaction


IDbDataParameter


IDataParameterCollection


IDbDataAdapter



There are problems involved with a common interface instead of
a common base class.


This issue will be covered in more depth in the slides about ado.net
2.0

February
2
nd,
2006

ADO .NET

42

ADO.NET
2.0
Added Features


ADO.NET
2.0
comes with a plethora of new features.
We shall cover the main features in short:



Based
-
Class
-
Based Provider Model


Provider Factories


Asynchronous Commands


SqlDependency


MARS (Multiple Active Resultsets)

February
2
nd,
2006

ADO .NET

43

ADO.NET
2.0
Added Features (
1
)


Based
-
Class
-
Based Provider Model


In ADO.NET
1
.X provider writers implemented a series of
provider
-
specific classes. Generic coding was based on the
generic interface each of the classes implemented.


The new provider model in ADO.NET
2.0
is based on a series
of base classes.


Each of the base classes implements the still
-
required generic
interface for backward compatibility.


Provider Factories


The base ProviderFactory class (DbProviderFactory) and the
ProviderFactories class
(System.Data.Common.ProviderFactories) simplify things a
bit…

February
2
nd,
2006

ADO .NET

44

ADO.NET
2.0
Added Features (
2
)


Conditional code that used to be written like this:




enum provider {sqlserver, oracle, oledb, odbc};



// determine provider from configuration provider



prov = GetProviderFromConfigFile();



IDbConnection conn = null;




switch (prov) {





case provider.sqlserver:





conn = new SqlConnection(); break;





case provider.oracle:





conn = new OracleConnection(); break;




case provider.oledb:





conn = new OleDbConnection(); break;




case provider.odbc:





conn = new OdbcConnection(); break;





// add new providers as the application supports them …



}

February
2
nd,
2006

ADO .NET

45

ADO.NET
2.0
Added Features (
3
)


...Can now be written like this:



// get ProviderInvariantString from configuration string


provstring = GetProviderInvariantString();


DbProviderFactory fact =

DbProviderFactories.GetFactory(provstring);


IDbConnection = fact.CreateConnection();

February
2
nd,
2006

ADO .NET

46

ADO.NET
2.0
Added Features (
4
)


Asynchronous Commands


database command execution can take a long time.


ADO.NET
2.0
SqlClient now provides built
-
in SqlCommand
methods that provide asynchronous execution.



SqlDependency


Caching is good. But we should make sure it’s consistent with
the database.


Until now, this task was accomplished through triggers
that updated a file upon update of the database, or by
refreshing the cache every so often.



February
2
nd,
2006

ADO .NET

47

ADO.NET
2.0
Added Features (
5
)


SqlDependency contacts you when the underlying rows
change.



When notified, the entire set of rows is fetched again.


This functionality is good for a single cache or a limited set of
users.


However, when using it with large numbers of users listening at
the same time
-

the SELECT statements used for refresh could
be a significant hit on the database.


February
2
nd,
2006

ADO .NET

48

ADO.NET
2.0
Added Features (
6
)


MARS (Multiple Active Resultsets)



SQL Server doesn't automatically produce a cursor. Instead, it
uses an optimized method to pull the data in packet
-
size
chunks.



In the versions prior to SQL Server
2005
, there could only be
one cursorless resultset active on a connection at a time.


ADO.NET
1
.X throw an error if you attempt to open a second
cursorless resultset.


ADO "classic" actually opened a new database connection
behind the scenes.


This convenience feature was inadvertently abused by some
programmers and resulted in more database connections
than they bargained for.

February
2
nd,
2006

ADO .NET

49

ADO.NET
2.0
Added Features (
7
)


MARS (contd)



In SQL Server
2005
, the database has been
enhanced to permit MARS.


Each SqlCommand can accommodate a
SqlDataReader, and multiple SqlDataReaders
can be used in tandem.


It’s not just about reducing errors. It can be
extremely useful in conjunction with
asynchronous operations described above.



Example: Filling
20
drop
-
down list boxes on a form at
the same time, using a single connection.


February
2
nd,
2006

ADO .NET

50

Summary: Pros & Cons


Pros


Performance


Managed Classes


XML Support (and Reliance)


Disconnected Operation Model


Rich Object Model


Cons


Managed
-
Only Access


Only four Managed Data Providers (so far)


Learning Curve


Installation

February
2
nd,
2006

ADO .NET

52

Installation


Installing .Net Framework
1.1
is free


http://msdn.microsoft.com/netframework/downloads/framework
1
_
1
/



Getting Visual Studio
2003
is not free


http://msdn.microsoft.com/vstudio/Previous/
2003
/


There is VS Academic “only” for $
99



Installing SQL Server
2000


http://www.microsoft.com/technet/prodtechnol/sql/
2000
/downloads/



NEXT
-
>NEXT
-
>…
-
> FINISH

It’s DEMO Time

Remote DB Access Through
DataSet
-

Demo

February
2
nd,
2006

ADO .NET

55

Overview

Web Service

Web Page

Client

DataSet

HTTP

SQL Server

Data Provider