Microsoft 'NET Framework Data Access

1
Microsoft .NET Framework Data Access

• Ramesh Theivendran
• John Powell
• Borland Software Corporation

2
Agenda

• Database Connectivity in .NET
• Borland Data Provider (BDP)
• Resolving .NET DataSet
• Data Remoting in .NET
• Message Queues

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

4
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

5
ADO.NET

• Disconnected, n-tier data access model with good
  XML and XSD support.
• Core Components
• .NET DataSet
• .NET Data Provider

6
DataSnap vs ADO.NET
7
DataSnap vs ADO.NET

• Better representation of relational data
• Tighter XML and XSD support
• Resolving to be handled by the developer
• XML for data remoting

8
DataSet methods

• Update()/AcceptChanges()
• GetChanges()
• RejectChanges()
• ReadXml()
• ReadXmlSchema()
• WriteXml()
• WriteXmlSchema()
• GetXml()
• GetXmlSchema()
• Clear()

ApplyUpdates() Delta CancelUpdates() LoadFromFile(
) SaveToFile() Data XMLDataSet EmptyDataSet()
9
.NET Data Provider Architecture
.NET Client
D O T N E T
OLEDB Managed
BDP Managed
SQL Server Managed
COM Interop layer
OleDb Provider
C O M
OleDb Provider
TDS
GDS32
OCI
DB client
MSSQL
IB
ORACLE
RDBMS
10
BDP Components
Data Explorer
BDP Designers
ISQLConnection ISQLCommand ISQLCursor ISQLMetaData
ISQLResolver ISQLSchemaCreate ISQLDataSource
bdpDatasources.xml
bdpConnections.xml
DB client wrapper
DB
11
BDP for .NET

• Namespace Borland.Data.Provider
• BdpConnection
• BdpTransaction
• BdpCommand
• BdpParameter, BdpParameterCollection
• BdpDataReader
• BdpDataAdapter
• BdpException
• BdpError, BdpErrorCollection
• BdpCommandBuilder

12
BDP for .NET

• Namespace Borland.Data.Common
• ISQLConnection
• ISQLCommand
• ISQLCusor
• BdpType
• Namespace Borland.Data.Schema
• ISQLDataSource
• ISQLMetaData
• ISQLResolver
• ISQLSchemaCreate
• Namespace Borland.Data.Design

13
BdpConnection

• Implements IDbConnection
• Delegates to ISQLConnection implementation
• Properties
• ConnectionString
• ConnectionOptions
• State
• Methods
• Open(),Close()
• CreateCommand()
• BeginTransaction()
• ChangeDatabase()
• GetMetaData(), GetResolver()

14
BdpCommand

• Implements IDbCommand
• Delegates to ISQLCommand implementation
• Properties
• Connection
• CommandType
• CommandText
• Parameters, ParameterCount
• Transaction
• CommandOptions
• Methods
• Prepare(), ExecuteNonQuery(),
• ExecuteReader(), ExecuteScalar(), Close()

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

16
BdpTransaction

• Implements IDbTransaction
• Delegates to ISQLConnection implementation
• Properties
• Connection
• IsolationLevel
• Methods
• Commit()
• Rollback()

17
Sample code

• // Create a new Connection
• BdpConnection Conn new BdpConnection()
• String ConnStr " providerInterbase
  assemblyBorland.Data.Interbase, Version2.0.0.0,
  Cultureneutral, PublicKeyToken91d62ebb5b0d1b1b
  databasec\\IB71\\examples\\database\\employee.gd
  b usernamesysdbapasswordmasterkey"
• 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()
  

18
Sample code

• if ( Reader ! null )
• for (Int32 index 0 index lt
  Reader.FieldCount index)
• Console.WriteLine("Column Name "
  Reader.GetName(index))
• while (Reader.Read())
• for (Int32 index 0 index lt
  Reader.FieldCount index)
• //Assuming CHAR or VARCHAR
  columns
• Console.WriteLine(Reader.GetStrin
  g(index))
• Reader.Close()
• Trans.Commit()
• Command.Close()
• Conn.Close()

19
BdpParameter

• BdpParameter Implements IDbDataParameter,
  IDataParameter
• Properties
• ParameterName
• DbType
• BdpType
• BdpSubType
• Direction
• Value
• Precision, Scale, MaxPrecision

20
Demos

• Retrieving data using BdpDataReader
• Inserting records using runtime Parameter binding

21
BdpDataAdapter

• Extends DbDataAdapter and Implements
  IDbDataAdapter
• Properties
• SelectCommand, DeleteCommand, InsertCommand,
  UpdateCommand
• DataSet
• Active
• StartRecord
• MaxRecords
• TableMappings
• Methods
• Fill(), FillSchema()
• Update(), AutoUpdate()
• GetFillParameters()

22
BdpCommandBuilder

• Properties
• DataAdapter
• QuotePrefix
• QuoteSuffix
• ReadOnly
• ExcludeFilter
• Methods
• GetInsertCommand()
• GetDeleteCommand()
• GetUpdateCommand()
• RefreshSchema()

23
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

24
MetaData Services

• MetaData retrieval
• Schema Creation
• Create Table,View,Indices
• Alter Table
• Drop Table, View, Indices
• Data Migration
• BdpCopyTable component
• SourceCommand
• Destination
• DestinationTable

25
Why BDP.NET

• Open Architecture Lets you add support to more
  DBs easily
• Portable code Write ones and connect to all
  DBs
• Logical Data types mapped to .NET Native types
• Consistent data type mapping across DBs
• 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)

26
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

27
Demos

• 8. Metadata retrieval
• 9. Schema Creation
• 10. Data Migration - BdpCopyTable

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

29
.NET DataSet Resolving
DataSet
DataTables
Interbase
BDP
BDP
DataHub
DataSync
MSSQL
SQLClient
Oracle
ODP.NET
30
Demos

• 11. 2-tier DataHub and DataSync
• 12. Master-Detail resolving

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

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

33
.NET Remoting
Client Process
Server Process
Proxy
Remote Object
Formatter
Formatter
Server ChannelSink
Client ChannelSink
TransportSink
TransportSink
34
.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

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

36
.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.InitializeLifeti
  meService
• public override object InitializeLifetimeService(
  )
• return null

37
Demos

• 13. Simple DataSet remoting

38
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

39
DataSet Remoting
DataSet
Interbase
RemoteServer (SAO)
DataHub
MSSQL
DataSync (CAO)
RemoteConnection
Oracle
DataSync (CAO)
40
Demos

• 13. Multi-tier using RemoteConnection and
  RemoteServer

41
Remoting vs Web Services
42
Message Queues

• MOM Asynchronous distributed applications
• Reliable, offline access
• Robust, guaranteed delivery
• Priority messaging
• Transactional messages
• Secure

43
Message Queues
Server1
Client1
Message Queue
Server2
Client2
Client3
Ack.Queue
44
Message Queues

• System.Messaging Namespace
• MessageQueue
• Message
• MessageQueueEnumerator
• MessageEnumerator
• System.Messaging.MessageQueue
• Create(),Delete(),Purge()
• Exists()
• Path
• Send()
• Receive(), BeginReceive(), EndReceive()
• Peek(), BeginPeek(), EndPeek()

45
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

46
MessageQueue

• Queue types
• Private Queue Local to the machine
• MachineName\Private\QueueName FORMATNAMEPRIV
  ATEMachineGUID\Queue
• LabelQueueName
• Public Queue Local or any computer you have
  rights
• MachineName\QueueName
• FORMATNAMEPUBLICQueueGUID
• Journal Queue - Save copies
• MachineName\QueueName\Journal
• FORMATNAMEPUBLICQueueGUIDJOURNAL  
• Dead-letter Queue, Transactional dead-letter
  Queue

47
Demo

• 14. Send and Receive simple message
• 15. Priority messages

48
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

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

50
Demo

• 16. Message with Acknowledgement
• 17. Journal messages
• 18. Purge Journal messages

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

52
Retrieving Messages

• Static and Dynamic retrieval Messages
• MessageQueue.GetAllMessage()
• MessageQueue.GetEnumerator()
• Static and Dynamic retrieval Message Queues
• MessageQueue.GetPrivateQueuesByMachine()
• MessageQueue.GetMessgeQueueEnumerator()

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

54
Demo

• 19. MessageQueue and Message Retrieval
• 20. Transactional Queues

55
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

56
Questions?

• Ramesh.Theivendran_at_borland.com