1 / 33

ADO.NET: Working in Disconnected Environment

ADO.NET: Working in Disconnected Environment. Sergey Baidachni MCT, MCSD, MCDBA. Overview. Introduction Architecture of ADO.NET ObjectSpaces – New technologies. Introduction. Data Storage Connected environment Disconnected environment ADO.NET vs. ADO Demonstration (ADO.NET and Excel).

Télécharger la présentation

ADO.NET: Working in Disconnected Environment

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.


Presentation Transcript

  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=""> <r:Schema Name="dbo" xmlns:r=""> <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=""> <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=""> <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


More Related