Transcript ADO.NET - Embarcadero Conferences Home Page

Microsoft .NET Framework
Data Access
Ramesh Theivendran
John Powell
Borland Software Corporation
Agenda
Database Connectivity in .NET
Borland Data Provider (BDP)
Resolving .NET DataSet
Data Remoting in .NET
Message Queues
Database Connectivity .NET
Data Access
- BDP, SQLClient, OLEDB, ODBC, ODP.NET
Data Manipulation
- .NET DataSet
Data Remoting
- .NET Remoting
- ASP.NET Web Services (Stateless)
- Message Queues (Asynchronous)
Data Access
Relational data access
•
•
•
ADO.NET (.NET)
ODBC, OLEDB, ADO, BDE, dbExpress (Win32)
JDBC (Java)
Object data access
•
•
•
ECO, ObjectSpaces (.NET)
BOLD, MTS
(Win32)
JDO, EJB (Java)
OLAP, Data Mining
•
•
•
•
MDX (MultiDimensional Expressions)
DSO (Decision Support Objects)
PivotTable Services
XML/A
ADO.NET
Disconnected, n-tier data access model with good
XML and XSD support.
Core Components:
– .NET DataSet
– .NET Data Provider
DataSnap vs ADO.NET
dbExpress
.NET Data Provider
SQLConnection
SQLCommand
SQLCursor
IDbConnection
IDbCommand
IDataReader
DataSnap vs ADO.NET
DataSnap
.NET DataSet
ClientDataset
DataProvider
DataSet
IDbDataAdapter
Better representation of relational data
Tighter XML and XSD support
Resolving to be handled by the developer
XML for data remoting

DataSet methods
Update()/AcceptChanges()
GetChanges()
RejectChanges()
ReadXml()
ReadXmlSchema()
WriteXml()
WriteXmlSchema()
GetXml()
GetXmlSchema()
Clear()
ApplyUpdates()
Delta
CancelUpdates()
LoadFromFile()
SaveToFile()
Data
XMLDataSet
EmptyDataSet()
.NET Data Provider Architecture
D
O
T
N
E
T
.NET Client
OLEDB
Managed
BDP
Managed
SQL Server
Managed
COM Interop layer
C
OleDb
O
Provider
M
OleDb
Provider
TDS
GDS32
MSSQL
IB
OCI
DB client
RDBMS
ORACLE
BDP Components
Data Explorer
BDP Designers
ISQLConnection
ISQLCommand
ISQLCursor
ISQLMetaData
ISQLResolver
ISQLSchemaCreate
ISQLDataSource
bdpConnections.xml
DB client wrapper
DB
bdpDatasources.xml
BDP for .NET
Namespace Borland.Data.Provider
– BdpConnection
– BdpTransaction
– BdpCommand
– BdpParameter, BdpParameterCollection
– BdpDataReader
– BdpDataAdapter
– BdpException
– BdpError, BdpErrorCollection
– BdpCommandBuilder
BDP for .NET
Namespace Borland.Data.Common
– ISQLConnection
– ISQLCommand
– ISQLCusor
– BdpType
Namespace Borland.Data.Schema
– ISQLDataSource
– ISQLMetaData
– ISQLResolver
– ISQLSchemaCreate
Namespace Borland.Data.Design
BdpConnection
Implements IDbConnection
Delegates to ISQLConnection implementation
Properties:
ConnectionString
ConnectionOptions
State
Methods:
Open(),Close()
CreateCommand()
BeginTransaction()
ChangeDatabase()
GetMetaData(), GetResolver()
BdpCommand
Implements IDbCommand
Delegates to ISQLCommand implementation
Properties:
Connection
CommandType
CommandText
Parameters, ParameterCount
Transaction
CommandOptions
Methods:
Prepare(), ExecuteNonQuery(),
ExecuteReader(), ExecuteScalar(), Close()
BdpDataReader
Implements IDataReader, IDataRecord
Delegates to ISQLCursor Implementation
Properties:
IsClosed
RecordsAffected
FieldCount
Depth
Methods:
Read(), NextResult()
GetShemaTable(),
GetName(), GetFieldType(), GetDataTypeName(),
GetOrdinal(),GetDataType(), GetDataSubType()
IsDBNull(), GetValues(), GetInt16(), GetInt32()….Close()
BdpTransaction
Implements IDbTransaction
Delegates to ISQLConnection implementation
Properties:
Connection
IsolationLevel
Methods:
Commit()
Rollback()
Sample code
// Create a new Connection
BdpConnection Conn = new BdpConnection();
String ConnStr = " provider=Interbase; assembly=Borland.Data.Interbase,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=91d62ebb5b0d1b1b;
database=c:\\IB71\\examples\\database\\employee.gdb;
username=sysdba;password=masterkey";
Conn.ConnectionString = ConnStr;
//Establish connection to the database
Conn.Open();
BdpTransaction Trans = Conn.BeginTransaction();
BdpCommand Comm = Conn.CreateCommand();
Comm.Connection = Conn;
Comm.Transaction = Trans;
Comm.CommandText = " SELECT * FROM ADDRESSBOOK";
BdpDataReader Reader = Comm.ExecuteReader();
Sample code
if ( Reader != null )
{
for (Int32 index = 0; index < Reader.FieldCount; index++)
Console.WriteLine("Column Name = " + Reader.GetName(index));
while (Reader.Read())
{
for (Int32 index = 0; index < Reader.FieldCount; index++)
{
//Assuming CHAR or VARCHAR columns
Console.WriteLine(Reader.GetString(index));
}
}
Reader.Close();
}
Trans.Commit();
Command.Close();
Conn.Close();
BdpParameter
BdpParameter: Implements IDbDataParameter,
IDataParameter
Properties:
ParameterName
DbType
BdpType
BdpSubType
Direction
Value
Precision, Scale, MaxPrecision
Demos
1. Retrieving data using BdpDataReader
2. Inserting records using runtime
Parameter binding
BdpDataAdapter
Extends DbDataAdapter and Implements IDbDataAdapter
Properties:
SelectCommand, DeleteCommand, InsertCommand,
UpdateCommand
DataSet
Active
StartRecord
MaxRecords
TableMappings
Methods:
Fill(),FillSchema()
Update(), AutoUpdate()
GetFillParameters()
BdpCommandBuilder
Properties:
DataAdapter
QuotePrefix
QuoteSuffix
ReadOnly
ExcludeFilter
Methods:
GetInsertCommand()
GetDeleteCommand()
GetUpdateCommand()
RefreshSchema()
Demos
3. Provide and resolve data using BdpDataAdapter
4. SQL Generation using the BdpCommandBuilder
5. Calling Stored Procedures
6. Blob access
7. BDP and dbWeb
MetaData Services
MetaData retrieval
Schema Creation
Create Table,View,Indices
Alter Table
Drop Table, View, Indices
Data Migration
BdpCopyTable component
SourceCommand
Destination
DestinationTable
Why BDP.NET
Open Architecture: Lets you add support to more DB’s easily
Portable code : Write ones and connect to all DB’s
Logical Data types mapped to .NET Native types
Consistent data type mapping across DB’s
Unlike OLEDB .NET Provider need not go through a COM interop layer.
Support for Database specific features
Supports metadata, schema creation and data migration services
Cross platform availability (may be)
BDP supported RDBMS
INTERBASE 7, 7.5
ORACLE 9i,10g
DB2 V 7.2, 8.x
MSSQL 2000 / MSDE
MSAccess
Sybase 12.5
.NET DataStore (EBU)
and more to follow…
Demos
8. Metadata retrieval
9. Schema Creation
10. Data Migration - BdpCopyTable
.NET DataSet Resolving
DataHub and DataSync
Provide and resolve data from multiple data source, master-detail
Generates optimal SQL for resolving to BDP data sources
Supports Live Data at design-time from any .NET data provider
DataHub
Properties:
Active
DataSet, DataPort
Methods:
ApplyChanges()
Refresh()
DataSync
CommitBehavior
.NET DataSet Resolving
DataTables
DataSet
Interbase
BDP
DataHub
DataSync
BDP
MSSQL
SQLClient
ODP.NET
Oracle
Demos
11. 2-tier DataHub and DataSync
12. Master-Detail resolving
.NET Remoting
Interprocess communication between
application domains
To Remote:
1. A remotable object
2. A host app domain to host the remote object and listen for
requests
3. A client app domain that makes request for the object
.NET Remoting
Remotable Objects
Marshal-By-Value
ISerializable
SerializableAttribute
MarshalByValueComponent
State of the object copied
Marshal-By-Ref
MarshalByRefObject
State of the object stays in the app domain it was created
.NET Remoting
Client Process
Server Process
Proxy
Remote Object
Formatter
Formatter
Client ChannelSink
TransportSink
Server ChannelSink
TransportSink
.NET Remoting
MBR Activation model
Server activated Objects (SAO)
Server object created only on the first method call
Only a proxy is created when the client requests to create
Default constructor only allowed
Singleton
One object servers all clients
Lifetime controlled by lease
Maintain state between clients
SingleCall
Separate object for each request
Does not participate in lifetime lease
No state maintained, best choice for load balancing
.NET Remoting
MBR Activation model
Client activated Objects (CAO)
Created on the server upon request to create a new object
Non Default constructors can be used
ObjRef is returned and a proxy is created on the client
Can maintain state between client method calls
Lifetime controlled by lease
Published Objects
Publish an already created object on the server
Behaves as a Singleton SAO afterwards
.NET Remoting
MBR Lifetime
Lease manager and Sponsors keep MBR alive
Every app domain has a lease manager
Lease manager periodically checks for lease expiry
Infinite lifetime
By overriding MarshalByRefObject.InitializeLifetimeService
public override object InitializeLifetimeService()
{
return null;
}
Demos
13. Simple DataSet remoting
DataSet Remoting
RemoteServer, RemoteConnection
public interface IDataService
{
String[] GetProviderList();
IDataProvider GetDataProvider(String ProviderName);
}
public interface IDataProvider
{
DataSet GetData();
Int32 SaveData(DataSet ds);
DataProviderCollection Providers { get; set;}
}
RemoteTracker: ITrackingHandler
RemoteClientSponsor
DataSet Remoting
DataSet
RemoteServer (SAO)
Interbase
DataHub
DataSync (CAO)
MSSQL
RemoteConnection
DataSync (CAO)
Oracle
Demos
13. Multi-tier using RemoteConnection and
RemoteServer
Remoting vs Web Services
Stateless objects
Yes
Yes
Singleton objects
Yes
No
Statefull objects
Yes
No
TCP sockets for communication
Yes
No
HTTP for communication
Yes
Yes
Hosting in IIS
Yes
Yes
Custom Hosting
Yes
No
SOAP serialization
Yes
Yes
Binary serialization
Yes
No
Interoperability
No
Yes
Message Queues
MOM: Asynchronous distributed applications
Reliable, offline access
Robust, guaranteed delivery
Priority messaging
Transactional messages
Secure
Message Queues
Server
Client1
Client2
1
Message
Queue
Client3
Ack.Queue
Server2
Message Queues
System.Messaging Namespace
MessageQueue
Message
MessageQueueEnumerator
MessageEnumerator
System.Messaging.MessageQueue
Create(),Delete(),Purge()
Exists()
Path
Send()
Receive(), BeginReceive(), EndReceive()
Peek(), BeginPeek(), EndPeek()
MessageQueue
Identifying a Queue:
Path
- Uniquely identified by Computer and Queue name
- Online queues
FormatName
- Unique identifier generated by MSMQ
- Online and Offline queues
Label
- Name given by the queue administrator
- Useful when Message Queue are moved
MessageQueue
Queue types:
Private Queue – Local to the machine
MachineName\Private$\QueueName
FORMATNAME:PRIVATE=MachineGUID\Queue#
Label:QueueName
Public Queue – Local or any computer you have rights
MachineName\QueueName
FORMATNAME:PUBLIC=QueueGUID
Journal Queue - Save copies
MachineName\QueueName\Journal$
FORMATNAME:PUBLIC=QueueGUID;JOURNAL
Dead-letter Queue, Transactional dead-letter Queue
Demo
14. Send and Receive simple message
15. Priority messages
Message Acknowledgement
Two types of acknowledgements:
a. Message reached destination Queue
b. Message retrieved from the destination Queue
Positive or Negative acknowledgement
Requesting for an Acknowledgement:
AdministrationQueue Property
AcknowledgeType Property
None
FullReachQueue
FullReceiveQueue
NegativeReceive
PostiveArrive, PostiveReceive
Ack. Messages have no Body, only a header with Correlation ID
Journal Queues
System Queue - Allow saving copies of messages
Read-only for applications
Messages are not removed as they are received
Have Maximum quota and don’t report errors
One Journal Queue per message Queue
MessageQueue.UseJournalQueue Property
Enable storage for any message received by the Queue
One System Journal Queue per machine
Message.UseJournalQueue Property
This message, when sent will be recorded on the system journal
MessageQueue.MaximumJournalSize
Demo
16. Message with Acknowledgement
17. Journal messages
18. Purge Journal messages
Receiving Messages
Formatter Property
IMessageFormatter
XMLMessageFormatter
BinaryMessageFormatter
ActiveXMessageFormatter
DenySharedReceive Property
Close() will free it for others
MessageReadPropertyFilter Property
Enables or disable what property you want to receive
Peek(), Receive()
Retrieving Messages
Static and Dynamic retrieval Messages:
MessageQueue.GetAllMessage()
MessageQueue.GetEnumerator()
Static and Dynamic retrieval Message Queues:
MessageQueue.GetPrivateQueuesByMachine()
MessageQueue.GetMessgeQueueEnumerator()
Transactional Queue
All messages are delivered once and in order.
Two types:
Internal Transactions
Between one or more Message queues
External Transactions
Message Queues and other resources through DTC
MessageQueueTransaction
Begin()
Commit()
Rollback()
Demo
19. MessageQueue and Message Retrieval
20. Transactional Queues
Reliable MOM
MSMQ
Supports Acknowledgements,Journaling,
Recovery, Transactions, Asynchronous
Receive/Peek, Priority
“Every DAD needs MOM”
“Real DAD needs no MOM”
“A DAD needs MOM”
Questions?
[email protected]