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 dataTighter XML and XSD supportResolving 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

XMLDataSetEmptyDataSet()

.NET Data Provider Architecture

OLEDBManaged

.NET Client

SQL ServerManaged

COM Interop layer

OleDbProvider

RDBMS

DB client

OleDbProvider

BDPManaged

TDS GDS32

MSSQL IB

DOTNET

COM

ORACLE

OCI

ISQLConnectionISQLCommand

ISQLCursorISQLMetaDataISQLResolver

ISQLSchemaCreateISQLDataSource

DB client wrapper

bdpDatasources.xml

DB

BDP Designers

BDP Components

Data Explorer

bdpConnections.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 IDbConnectionDelegates to ISQLConnection implementationProperties:

ConnectionStringConnectionOptionsState

Methods:Open(),Close()CreateCommand()BeginTransaction()ChangeDatabase()GetMetaData(), GetResolver()

BdpCommand

Implements IDbCommandDelegates to ISQLCommand implementationProperties:

ConnectionCommandTypeCommandTextParameters, ParameterCountTransactionCommandOptions

Methods:Prepare(), ExecuteNonQuery(), ExecuteReader(), ExecuteScalar(), Close()

BdpDataReaderImplements IDataReader, IDataRecordDelegates to ISQLCursor ImplementationProperties:

IsClosedRecordsAffectedFieldCountDepth

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();

BdpParameterBdpParameter: 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 IDbDataAdapterProperties:

SelectCommand, DeleteCommand, InsertCommand, UpdateCommandDataSetActiveStartRecordMaxRecordsTableMappings

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 CreationCreate Table,View,Indices

Alter Table

Drop Table, View, Indices

Data MigrationBdpCopyTable 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.5ORACLE 9i,10gDB2 V 7.2, 8.xMSSQL 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 DataSyncProvide and resolve data from multiple data source, master-detailGenerates optimal SQL for resolving to BDP data sources Supports Live Data at design-time from any .NET data provider

DataHubProperties:

ActiveDataSet, DataPort

Methods:ApplyChanges()Refresh()

DataSyncCommitBehavior

.NET DataSet Resolving

DataHub

DataSet

DataSync

BDP

BDP

SQLClient

ODP.NET

Interbase

Oracle

MSSQL

DataTables

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

Proxy

Formatter

Client ChannelSinkServer ChannelSink

Formatter

Remote Object

Client Process Server Process

TransportSink TransportSink

.NET Remoting

MBR Activation modelServer activated Objects (SAO)

Server object created only on the first method callOnly a proxy is created when the client requests to createDefault constructor only allowedSingleton

One object servers all clientsLifetime controlled by leaseMaintain state between clients

SingleCall Separate object for each requestDoes not participate in lifetime leaseNo 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 managerLease 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: ITrackingHandlerRemoteClientSponsor

DataSet Remoting

DataHub

DataSet

Interbase

Oracle

MSSQL

RemoteConnection

RemoteServer (SAO)

DataSync (CAO)

DataSync (CAO)

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

Client2Message

Queue

Client3

Client1

Server2

Ack.Queue

Server

1

Message Queues

System.Messaging NamespaceMessageQueueMessageMessageQueueEnumeratorMessageEnumerator

System.Messaging.MessageQueueCreate(),Delete(),Purge()Exists()PathSend()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$\QueueNameFORMATNAME: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 Queueb. Message retrieved from the destination Queue

Positive or Negative acknowledgement

Requesting for an Acknowledgement:AdministrationQueue PropertyAcknowledgeType Property

None FullReachQueueFullReceiveQueueNegativeReceivePostiveArrive, PostiveReceive

Ack. Messages have no Body, only a header with Correlation ID

Journal QueuesSystem Queue - Allow saving copies of messages Read-only for applicationsMessages are not removed as they are receivedHave Maximum quota and don’t report errors

One Journal Queue per message QueueMessageQueue.UseJournalQueue Property

Enable storage for any message received by the Queue

One System Journal Queue per machineMessage.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 MessagesFormatter Property

IMessageFormatter

XMLMessageFormatter

BinaryMessageFormatterActiveXMessageFormatter

DenySharedReceive PropertyClose() will free it for others

MessageReadPropertyFilter PropertyEnables 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 QueueAll messages are delivered once and in order.

Two types:

Internal Transactions

Between one or more Message queues

External TransactionsMessage 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?

Ramesh.Theivendran@borland.com