ADO.NET - Conferences

hedgebornabaloneSoftware and s/w Development

Dec 2, 2013 (3 years and 8 months ago)

77 views

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

OLEDB

Managed

.NET Client

SQL Server

Managed

COM Interop layer

OleDb

Provider

RDBMS

DB client

OleDb

Provider

BDP

Managed

TDS

GDS32

MSSQL

IB

D

O

T

N

E

T

C

O

M

ORACLE

OCI

ISQLConnection

ISQLCommand

ISQLCursor

ISQLMetaData

ISQLResolver

ISQLSchemaCreate

ISQLDataSource

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 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



Bdp
Connection 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

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 ChannelSink

Server ChannelSink

Formatter

Remote Object

Client

Process

Server Process

TransportSink

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

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

Client2

Message
Queue

Client3

Client1

Server2

Ack.Queue

Server
1

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?






Ramesh.Theivendran@borland.com