ADO NET Working in Disconnected Environment Sergey Baidachni

ADO. NET: Working in Disconnected Environment Sergey Baidachni MCT, MCSD, MCDBA

Overview n n n Introduction Architecture of ADO. NET Object. Spaces – New technologies

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

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

Connected Environment (Scenario) 1. Open connection 2. Execute command 3. Process rows in reader 4. Close reader 5. Close connection

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

Disconnected Environment (Scenarion) 1. Open connection 2. Fill the Data. Set 3. Close connection 4. Process the Data. Set 5. Open connection 6. Update the data source 7. Close connection

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

ADO. NET vs. ADO n Connection ADO. NET n n Command Record. Set n n Xxx. Connection Xxx. Transaction Xxx. Command Xxx. Data. Reader Xxx. Data. Adapter Data. Set

Demonstration Ole. Db. Connection conn=new Ole. Db. Connection(); Ole. Db. Command comm=new Ole. Db. Command(); Ole. Db. Data. Adapter adapt=new Ole. Db. Data. Adapter(comm); Data. Set data=new Data. Set(); conn. Connection. String= “Provider=Microsoft. Jet. OLEDB. 4. 0; c: myexcel. xls; Extended Properties=""Excel 8. 0; HDR=NO""“; comm. Connection=conn; comm. Command. Text=“select * from [Sheet 1$]”; adapt. Fill(data);

Architecture of ADO. NET n n n n Data providers What does it look like? Connection to the source Command creation Cursors Data storage in memory Data. Adapter – automatic data upload XML Integration

. NET Data Providers n n Concept of data provider Provider types q q n SQL. NET Data Provider Oracle. NET Data Provider Ole. DB. NET Data Provider Odbc. NET Data Provider How to select data provider

Как это выглядит. NET Data Provider Connection Command Select. Command Data. Reader Insert. Command Data. Adapter Delete. Command Update. Command Data. Set Data. Table Data. Relation Data. Column Data. Row Data. Constraint

Connection n n What is Connection? Define Connection q q n Sql. Connection conn=new Sql. Connection(); Connection. String=“User ID=sa; password=; Data Source=My. Server; Initial Catalog=Northwind; ” Connection. String Parameters q q q Provider Data Source Initial Catalog Integrated Security User. ID/Password

Connection (Error and Pooling) n n n System. Data. Sql. Client. Sql. Exception Errors collection Sql. Error q q n Class Line. Number Message Number Pooling and Dispose method

Command Object n n A command object is a reference to a SQL statement or stored procedure Properties q q n Connection Command. Type Command. Text Parameters Methods q q q Execute. Non. Query Execute. Reader Execute. Scalar

Data. Reader Object n n n What is query? Forward-only cursor Read method q q n n n Read next record Return true if record is exist Is. Db. Null Close method Next. Result – for multiply select statements

What Are Data. Sets and Data. Tables Data. Set Data. Table Connection Stored Procedure Database Server Data Store

The Data. Set Object Model n Common collections q q n n Tables (collection of Data. Table objects) Relations (collection of Data. Relation objects) Data binding to Web and Windows controls supported Schema can be defined programmatically or using XSD Data. Column Data. Table Data. Relation Data. Row Constraints

What Is a Data. Adapter? Data. Set Data. Adapter Data. Table Fill Update Data source

The Xxx. Data. Adapter Object Model Xxx. Data. Adapter Select. Command. Update. Command. Insert. Command. Delete. Command Xxx. Data. Reader Xxx. Command Xxx. Connection sp_SELECT sp_UPDATE sp_INSERT sp_DELETE

Demo

XML Support ADO. NET is tightly integrated with XML n Using XML in a disconnected application n XML Web Services Client Data Source 1 4 Request data XML 5 Updated XML 2 Data. Set 3 6 SQL query Results SQL updates

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

Introduction to Problem n n There’s something apparently missing Current situation public struct Customer { int customer. ID; string customer. Name; } …. . Customer Load. Customer(int id){…} void Save. Customer(Customer obj){…} Database

What is Object Spaces? n n Relation between object and database Way to avoid long-drawn coding Object. Space Map Files Database

What do we need? n n n Single table to single Object hierarchy to many tables Object hierarchy to single table Single Object to multiple tables Inheritance q q All types in single table Table for base type and related table per derived type

How we can resolve problem n n n XML Again! RSD – Relational Schema Definition OSD – Object Schema Definition MSD – Mapping Schema definition Mapping. Schema class

RSD n Tables, fields and relations descriptions <rsd: Database Name="Northwind" Owner="sa" xmlns: rsd="http: //schemas. microsoft. com/data/2002/09/28/rsd"> <r: Schema Name="dbo" xmlns: r="http: //schemas. microsoft. com/data/2002/09/28/rsd"> <rsd: Tables> <rsd: Table Name="Customers"> <rsd: Columns> <rsd: Column Name="Customer. ID" Sql. Type="nchar" Precision="5" /> <rsd: Column Name="Company. Name" Sql. Type="nvarchar" Precision="40" /> </rsd: Columns> <rsd: Constraints> <rsd: Primary. Key Name="PK_Customers"> <rsd: Column. Ref Name="Customer. ID" /> </rsd: Primary. Key> </rsd: Constraints> </rsd: Tables> </r: Schema> </rsd: Database>

OSD n Objects description <osd: Extended. Object. Schema Name="Data. Types. OSD" xmlns: osd="http: //schemas. microsoft. com/data/. . . /persistenceschema"> <osd: Classes> <osd: Class Name="Samples. Customer"> <osd: Member Name="Id" Key="true" /> <osd: Member Name="Company" /> </osd: Classes> </osd: Extended. Object. Schema>

MSD n Mapping Schemes <m: Mapping. Schema xmlns: m="http: //schemas. microsoft. com/data/2002/09/28/mapping"> <m: Data. Sources> <m: Data. Source Name="Northwind. RSD" Type="SQL Server" Direction="Source"> <m: Schema Location="RSD. XML" /> <m: Variable Name="Customers" Select="Customers" /> </m: Data. Source> <m: Data. Source Name="Data. Types. OSD" Type="Object" Direction="Target"> <m: Schema Location="OSD. XML" /> </m: Data. Sources> <m: Mappings> <m: Map Source. Variable="Customers" Target. Select="Samples. Customer"> <m: Field. Map Source. Field="Customer. ID" Target. Field="Id" /> <m: Field. Map Source. Field="Company. Name" Target. Field="Company" /> </m: Mappings> </m: Mapping. Schema>

Object. Space methods n n n n n Begin. Transaction Commit Rollback Get. Object. Reader Get. Object. Set Mark. For. Deletion Persist. Changes Resync Start. Tracking

msdn. microsoft. com/library