What is the difference between Borland DataSnap and Microsof ADO.NET

Problem/Question/Abstract:

What is the difference between Borland DataSnap and Microsof ADO.NET

Answer:

// Torry's Delphi Tips - Database
// Author Pablo Reyes
// Listed 04.10.2003
Borland DataSnap vs Microsof ADO.NET
====================================
Delphi 7 and .NET Framework 1.1
A comparison between the tools provided by these two technologies for
building data aware applications.

Data access technologies:
=========================
Everybody knows that Borland DataSnap provides four data access technologies.
While ADO.NET is a data access technology, it provides four embedded data access technologies.
Third party companies provide data access technologies for both.

*** Borland DataSnap ***
- Borland Database Engine (BDE)
- dbExpress (DBX)
- InterBase Express (IBX)
- Activex Data Objects (ADO)
- Third party

*** Microsoft ADO.NET ***
- SQL for MS SQL Server
- OleDb
- ODBC
- Oracle
- Third party

Provide/Resolve
===============
Both uses a provide/resolve mechanism. You first provide data to a
component which holds data in memory in a disconnected fashion.
Changes to this data are hold in memory too.
Then you apply this changes to the underlying database.
So what you need is...

Components to establish a connection:
-------------------------------------
This components let you establish a connection with a
database and manage transactions.

*** Borland DataSnap ***
- Session and Database
- SQLConnection
- IBDatabase and IBTransaction
- ADOConnection
- Third party

*** Microsoft ADO.NET ***
- SQLConnection
- OleDbConnection
- ODBCConnection
- OracleConnection
- Third party

Borland DataSnap connection components have many similarities
between them but they all are different. All Microsoft ADO.NET
connection components implements the same interface.

Components to obtain data:
--------------------------
This components let you obtain a data set form a database
through one of the connection components.

*** Borland DataSnap ***
- Table, Query, StoredProc
- SQLTable, SQLQuery, SQLStoredProc, SQLDataSet
- IBTable, IBQuery, IBStoredProc, IBDataSet
- ADOTable, ADOQuery, ADOStoredProc, ADODataSet
- Third party

*** Microsoft ADO.NET ***
- SQLCommand, SQLDataReader
- OleDbCommand, OleDbDataReader
- ODBCCommand, OleDbDataReader
- OracleCommand, OracleDataReader
- Third party

Borland DataSnap components used to obtain data have many similarities between
them but they all are different. All Microsoft ADO.NET components used to obtain
data implements the same interface.
While some of the Borland DataSnap components used to obtain data let you obtain
a read-write, bi-directional data sets, all Microsoft components used to obtain
data provide read-only, forward-only data sets.

Components to provide data and resolve changes:
-----------------------------------------------
This components do two main things: provide data and resolve changes.
While Borland DataSnap component can perform it in a connected and disconnected fashion,
Microsoft ADO.NET component can perform it only in a disconnected fashion.

*** Borland DataSnap ***
- DataSetProvider

Provide:
- Connect it to a DataSet and it provides data to a ClientDataSet.
The DataSet must implements IProviderSupport interface.
- If the connected DataSet is a master DataSet it recognize the
master/detail relationship and provide data treating master record
and its detail records as a unit.

Resolve:
- It generates SQL statements on the fly using information form the
connected DataSet, even if you use JOINs.
- It treats master and details as a unit and generates transactions
for updating master and details in the same transaction and updates
data in the correct order (for inserts, first master and then details;
for deletes, first details and then master).
- It lets you configure how SQL statement should be generated.
- It manages concurrency.

*** Microsoft ADO.NET ***
- DataAdapter

Provide:
- You use a DataAdapter to fill a DataSet with records.
It could use embedded Command components or it could be connected
to your Command components.
- DataAdapter does not recognize master/detail relationships.
You need to use one DataAdapter for each table.

Resolve:
- It generates SQL statements on the fly using information from the SELECT statement,
but only for single tables.
- You must use one DataAdapter for each table so it updates only one table.
- It does not let you configure how SQL statement should be generated.
- It manages concurrency.

Components to hold data and changes in memory:
----------------------------------------------
This components hold data and changes to that data in memory.
Both can save its data to a file on disk and resolve updates later.

*** Borland DataSnap ***
- ClientDataSet

- It is aware of the DataSetProvider so they work together to apply
updates and reconcile update errors.
- Details are an extra field of the master. If you modify details
you also modify the master.
- It provides functionality to obtain details and BLOBs on demand.

*** Microsoft ADO.NET ***
- DataSet

- It doesn't aware of the DataAdapter so they don't work synchronized
and it doesn't know about update errors.
- Has a collection of tables and relations between this tables.
If you modify details you don't modify the master.
- It doesn't provide functionality to obtain details and BLOBs on demand.

Conclusions
===========
Borland DataSnap is a mature technology while Microsoft ADO.NET is an emerging,
new technology with an excellent starting point. You can accomplish the same task
with both technologies but you need to code a lot more with Microsoft ADO.NET.
With DataSetProvider and ClientDataSet from Borland DataSnap you get more than
with DataAdapter and DataSet from Microsoft ADO.NET.

Pablo Reyes