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 unitof information. Data Storage Unstructured Structured Hierarchical Relational Database Excel CSV XML SQL Server Oracle 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 Connection Command RecordSet ADO.NET XxxConnection XxxTransaction XxxCommand XxxDataReader XxxDataAdapter DataSet ADO.NET vs. ADO

10. Demonstration OleDbConnection conn=new OleDbConnection(); OleDbCommand comm=new OleDbCommand(); OleDbDataAdapter adapt=new OleDbDataAdapter(comm); DataSet data=new DataSet(); conn.ConnectionString= “Provider=Microsoft.Jet.OLEDB.4.0; c:\myexcel.xls;Extended Properties=""Excel 8.0;HDR=NO""“; comm.Connection=conn; comm.CommandText=“select * 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 conn=new SqlConnection(); • Conn.ConnectionString=“User ID=sa;password=; Data Source=MyServer;Initial Catalog=Northwind;” • 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 orstored 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. Stored Procedure Connection What Are DataSets and DataTables DataSet Server DataTable DataTable Database 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 Web Services Client Data Source Request data SQL query 1 2 Results XML 4 3 SQL updates Updated XML 5 6 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 • There’s 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 <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="CustomerID" SqlType="nchar" Precision="5" /> <rsd:Column Name="CompanyName" SqlType="nvarchar" Precision="40" /> </rsd:Columns> <rsd:Constraints> <rsd:PrimaryKey Name="PK_Customers"> <rsd:ColumnRef Name="CustomerID" /> </rsd:PrimaryKey> </rsd:Constraints> </rsd:Table> </rsd:Tables> </r:Schema> </rsd:Database>

30. OSD • Objects description <osd:ExtendedObjectSchema Name="DataTypesOSD" 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:Class> </osd:Classes> </osd:ExtendedObjectSchema>

31. MSD • Mapping Schemes <m:MappingSchema xmlns:m="http://schemas.microsoft.com/data/2002/09/28/mapping"> <m:DataSources> <m:DataSource Name="NorthwindRSD" Type="SQL Server" Direction="Source"> <m:Schema Location="RSD.XML" /> <m:Variable Name="Customers" Select="Customers" /> </m:DataSource> <m:DataSource Name="DataTypesOSD" Type="Object" Direction="Target"> <m:Schema Location="OSD.XML" /> </m:DataSource> </m:DataSources> <m:Mappings> <m:Map SourceVariable="Customers" TargetSelect="Samples.Customer"> <m:FieldMap SourceField="CustomerID" TargetField="Id" /> <m:FieldMap SourceField="CompanyName" TargetField="Company" /> </m:Map> </m:Mappings> </m:MappingSchema>

32. ObjectSpace methods • BeginTransaction • Commit • Rollback • GetObject • GetObjectReader • GetObjectSet • MarkForDeletion • PersistChanges • Resync • StartTracking

33. msdn.microsoft.com/library