ADO.NET: Working in Disconnected Environment

1
ADO.NET Working in Disconnected Environment

• Sergey Baidachni
• MCT, MCSD, MCDBA

2
Overview

• Introduction
• Architecture of ADO.NET
• ObjectSpaces New technologies

3
Introduction

• Data Storage
• Connected environment
• Disconnected environment
• ADO.NET vs. ADO
• Demonstration (ADO.NET and Excel)

4
Data Storage
Data storage - method of storing specific items
that together constitute a unit of information.
Data Storage
Hierarchical
Structured
Unstructured
Relational Database
Excel
SQL Server
Oracle
XML
CSV
Access
Active Directory
ADO.NET
5
Connected Environment (Scenario)

• 1. Open connection
• 2. Execute command
• 3. Process rows in reader
• 4. Close reader
• 5. Close connection

6
Connected Environment

• Working with data directly via open connection
• Advantages
• Simple security realization
• Work with real data
• Simple organization of distributed work
• Drawbacks
• Continual connection
• Not available via Internet

7
Disconnected Environment (Scenarion)

• 1. Open connection
• 2. Fill the DataSet
• 3. Close connection
• 4. Process the DataSet
• 5. Open connection
• 6. Update the data source
• 7. Close connection

8
Disconnected Environment

• Storage of data local copy from repository
• Possibility to update the main data source
• Advantages
• Economy of server resources
• Does not require continual connection
• Drawbacks
• Demands conflict resolution while data update
• Data is not always up to date

9
ADO.NET vs. ADO

• ADO
• Connection
• Command
• RecordSet

• ADO.NET
• XxxConnection
• XxxTransaction
• XxxCommand
• XxxDataReader
• XxxDataAdapter
• DataSet

10
Demonstration

• OleDbConnection connnew OleDbConnection()
• OleDbCommand commnew OleDbCommand()
• OleDbDataAdapter adaptnew OleDbDataAdapter(comm)
  
• DataSet datanew DataSet()
• conn.ConnectionString ProviderMicrosoft.Jet.OLE
  DB.4.0 c\myexcel.xlsExtended
  Properties""Excel 8.0HDRNO""
• comm.Connectionconn
• comm.CommandTextselect from Sheet1
• adapt.Fill(data)

11
Architecture of ADO.NET

• Data providers
• What does it look like?
• Connection to the source
• Command creation
• Cursors
• Data storage in memory
• DataAdapter automatic data upload
• XML Integration

12
.NET Data Providers

• Concept of data provider
• Provider types
• SQL .NET Data Provider
• Oracle .NET Data Provider
• OleDB .NET Data Provider
• Odbc .NET Data Provider
• How to select data provider

13
??? ??? ????????
.NET Data Provider
Connection
SelectCommand
Command
InsertCommand
DataReader
DeleteCommand
DataAdapter
UpdateCommand
DataSet
DataColumn
DataTable
DataRow
DataRelation
DataConstraint
14
Connection

• What is Connection?
• Define Connection
• SqlConnection connnew SqlConnection()
• Conn.ConnectionStringUser IDsapassword
  Data SourceMyServerInitial
  CatalogNorthwind
• ConnectionString Parameters
• Provider
• Data Source
• Initial Catalog
• Integrated Security
• UserID/Password

15
Connection (Error and Pooling)

• System.Data.SqlClient.SqlException
• Errors collection
• SqlError
• Class
• LineNumber
• Message
• Number
• Pooling and Dispose method

16
Command Object

• A command object is a reference to a SQL
  statement or stored procedure
• Properties
• Connection
• CommandType
• CommandText
• Parameters
• Methods
• ExecuteNonQuery
• ExecuteReader
• ExecuteScalar

17
DataReader Object

• What is query?
• Forward-only cursor
• Read method
• Read next record
• Return true if record is exist
• IsDbNull
• Close method
• NextResult for multiply select statements

18
What Are DataSets and DataTables
DataSet
DataTable
DataTable
Database

• Server

Data Store
19
The DataSet Object Model

• Common collections
• Tables (collection of DataTable objects)
• Relations (collection of DataRelation objects)
• Data binding to Web and Windows controls
  supported
• Schema can be defined programmatically or using
  XSD

DataColumn








DataRow
DataTable
Constraints
DataRelation
20
What Is a DataAdapter?
DataSet
Data source
DataAdapter
DataTable




Fill
Update
DataAdapter
DataTable




Fill
Update
21
The XxxDataAdapter Object Model
XxxDataAdapter
SelectCommand
UpdateCommand
InsertCommand
DeleteCommand
XxxDataReader
XxxCommand
XxxCommand
XxxCommand
XxxCommand
XxxConnection
sp_SELECT
sp_UPDATE
sp_INSERT
sp_DELETE
22
Demo
23
XML Support

• ADO.NET is tightly integrated with XML

• Using XML in a disconnected application

DataSet
DataSet
24
Object Spaces

• Introduction to problem
• What is Object Spaces?
• What do we need?
• How can we resolve the problem

25
Introduction to Problem

• Theres something apparently missing
• Current situation

public struct Customer int customerID
string customerName .. Customer
LoadCustomer(int id) void SaveCustomer(Customer
obj)
Database
26
What is Object Spaces?

• Relation between object and database
• Way to avoid long-drawn coding

ObjectSpace
Database
Map Files
27
What do we need?

• Single table to single Object
• Object hierarchy to many tables
• Object hierarchy to single table
• Single Object to multiple tables
• Inheritance
• All types in single table
• Table for base type and related table per derived
  type

28
How we can resolve problem

• XML Again!
• RSD Relational Schema Definition
• OSD Object Schema Definition
• MSD Mapping Schema definition
• MappingSchema class

29
RSD

• Tables, fields and relations descriptions
• ltrsdDatabase Name"Northwind" Owner"sa"
  xmlnsrsd"http//schemas.microsoft.com/data/2002
  /09/28/rsd"gt
• ltrSchema Name"dbo" xmlnsr"http//schemas.mic
  rosoft.com/data/2002/09/28/rsd"gt
• ltrsdTablesgt
• ltrsdTable Name"Customers"gt
• ltrsdColumnsgt
• ltrsdColumn Name"CustomerID"
  SqlType"nchar" Precision"5" /gt
• ltrsdColumn Name"CompanyName"
  SqlType"nvarchar" Precision"40" /gt
• lt/rsdColumnsgt
• ltrsdConstraintsgt
• ltrsdPrimaryKey Name"PK_Customers"gt
• ltrsdColumnRef Name"CustomerID" /gt
• lt/rsdPrimaryKeygt
• lt/rsdConstraintsgt
• lt/rsdTablegt
• lt/rsdTablesgt
• lt/rSchemagt
• lt/rsdDatabasegt

30
OSD

• Objects description
• ltosdExtendedObjectSchema Name"DataTypesOSD"
  xmlnsosd"http//schemas.microsoft.com/data/.../p
  ersistenceschema"gt
• ltosdClassesgt
• ltosdClass Name"Samples.Customer"gt
• ltosdMember Name"Id" Key"true" /gt
• ltosdMember Name"Company" /gt
• lt/osdClassgt
• lt/osdClassesgt
• lt/osdExtendedObjectSchemagt

31
MSD

• Mapping Schemes
• ltmMappingSchema xmlnsm"http//schemas.microsoft
  .com/data/2002/09/28/mapping"gt
• ltmDataSourcesgt
• ltmDataSource Name"NorthwindRSD" Type"SQL
  Server" Direction"Source"gt
• ltmSchema Location"RSD.XML" /gt
• ltmVariable Name"Customers" Select"Customers"
  /gt
• lt/mDataSourcegt
• ltmDataSource Name"DataTypesOSD" Type"Object"
  Direction"Target"gt
• ltmSchema Location"OSD.XML" /gt
• lt/mDataSourcegt
• lt/mDataSourcesgt
• ltmMappingsgt
• ltmMap SourceVariable"Customers"
  TargetSelect"Samples.Customer"gt
• ltmFieldMap SourceField"CustomerID"
  TargetField"Id" /gt
• ltmFieldMap SourceField"CompanyName"
  TargetField"Company" /gt
• lt/mMapgt
• lt/mMappingsgt
• lt/mMappingSchemagt

32
ObjectSpace methods

• BeginTransaction
• Commit
• Rollback
• GetObject
• GetObjectReader
• GetObjectSet
• MarkForDeletion
• PersistChanges
• Resync
• StartTracking

33

• msdn.microsoft.com/library