Advanced ADO

42 %
58 %
Information about Advanced ADO

Published on June 16, 2007

Author: Me_I


Advanced ADO.NET Techniques:  Advanced ADO.NET Techniques Jackie Goldstein Renaissance Computer Systems Ltd. MSDN Regional Director, Israel Prerequisites for presentation: I assume you know: 1) Visual Basic.NET 2) ADO.NET Level: Advanced ## Jackie Goldstein…:  Jackie Goldstein… General Manager of Renaissance Computer Systems Consulting, Training, andamp; Development, with Microsoft Tools andamp; Technologies Author of 'Database Access with Visual Basic.Net' (ISBN 0-67232-3435, Addison-Wesley) MSDN Regional Director for Israel Founder and monthly host of IVBUG (Israel Visual Basic User Group) Speaker at local andamp; international developer conferences: Microsoft Developer Days, TechEd, VSLive!, VBITS, VB DevCon, SQL2TheMax Selected as SME (Subject Matter Expert) to help develop/review content for DevDays with Microsoft team in Redmond Session Topics:  Session Topics 'Love Will Keep Us Together' Multi-Table Reads and Updates 'My Way' Specifying DataSet XML Format 'We Can Work it Out' Concurrency Conflicts 'Stop in the Name of Love' Pessimistic Locking Questions and Summary Data In The .NET Platform:  Data In The .NET Platform “Love Will Keep Us Together”Multi-Table Reads and Updates:  'Love Will Keep Us Together' Multi-Table Reads and Updates How do I do multi-table reads and updates ? The standard methods of generating update command for the DataAdapter (CommandBuilder and DataAdapter Configuration Wizard) support only single-table updates “Love Will Keep Us Together”Multi-Table Reads and Updates:  'Love Will Keep Us Together' Multi-Table Reads and Updates Use Batch SQL / Stored Procedures to load multiple DataSet tables in one server round-trip Use ExecuteXmlReader to fetch hierarchical data and load into DataSet Update multiple tables using a Stored Procedure Issue multiple update commands by calling the Update methods of the individual DataAdapters “Love Will Keep Us Together”Multi-Table Reads and Updates:  'Love Will Keep Us Together' Multi-Table Reads and Updates Generally update related tables in the following order: Child Table: Delete Records Parent Table: Insert, Update, and Delete records Child Table: Insert and Update records “Love Will Keep Us Together”Multi-Table Reads and Updates:  'Love Will Keep Us Together' Multi-Table Reads and Updates Read multiple tables into the DataSet in a single round-trip to the database server Update the database with changes to multiple tables Demo! “My Way”The DataSet And XML :  'My Way' The DataSet And XML The DataSet Load/save XML data into/out of DataSet Schema can be loaded/saved as XSD Schema can be inferred from XML Data The DataSet can be associated with an XmlDataDocument Exposes a relational view over structured XML According to the DataSet schema Allows strong typing, control binding, and relational access of XML data Preserves full fidelity of XML Document Simultaneously exposes data relationally or as XML Multiple tools on same data “My Way”Controlling how XML is Generated:  'My Way' Controlling how XML is Generated DataSet lets you control how XML is generated Name, Namespace properties on DataSet, DataTable, DataColumn MappingType property on DataColumn defines how data is written Element, Attribute, SimpleType, Hidden Nested Property on DataRelation controls how children are written “My Way Specifying DataSet XML Format:  'My Way Specifying DataSet XML Format Set DataSet properties to specify custom format of exposed XML Demo! “We Can Work it Out”Concurrency Conflicts:  'We Can Work it Out' Concurrency Conflicts Why Optimistic Locking ? Conflict Detection Conflict Resolution DataSet maintains 3 views of field value: Original Current Proposed (during edit) Can force changes, reject changes, or reject changes and reload data from source What defines a conflict ?:  What defines a conflict ? ADO 2.X – Dynamic Property 'Update Criteria' adCriteriaUpdCols (default) adCriteriaAllCols adCriteriaTimeStamp adCriteriaKey ADO.NET Auto-generated commands include PK and all fields in UPDATE and DELETE statements Developer can specify own custom SQL statements (e.g. if 2 of the 5 columns were modified). Differences in ADO.NET:  Differences in ADO.NET Can be automatically generated (but with limitations) Greater flexibility in defining what constitutes a conflict (see previous slide) A little more manual code required Passing different versions of columns Retrieving current database values More flexibility in handling batches with one or more conflicts “We Can Work it Out”Concurrency Conflicts:  'We Can Work it Out' Concurrency Conflicts Detecting and resolving concurrency conflicts using the DataAdapter Configuration Wizard Demo! “Stop in the Name of Love”Pessimistic Locking:  'Stop in the Name of Love' Pessimistic Locking Locking records when read ensures updates don't fail due to concurrency violations Kills scalability of application! Can still use ADO 'classic' (2.X) Supported in ADO.NET through transactions Update records in same transaction as read “Stop in the Name of Love”Pessimistic Locking:  'Stop in the Name of Love' Pessimistic Locking Use ADO.NET Transactions to cause pessimistic locking Updates outside the transaction are blocked until transaction completes Demo! “Sooner or Later”Specifying Metadata at Design Time:  Issue: Extra server roundtrips to gather metadata leads to poor performing, less predictable code Solution: Specify MetaData at DesignTime when known DataReader Strongly Typed Ordinal accessors Str = dr.GetString(0) DataSet Load, don't infer, schema Specify appropriate XmlReadMode Data Adapter Specify insert/update/delete commands Versus CommandBuilder when known Specify Parameter information Versus CommandBuilder.DerviveParameters Specify Primary Key information Versus MissingSchemaAction.AddWithKey 'Sooner or Later' Specifying Metadata at Design Time Summary:  Summary Design and implementation of multi-table DataSets require a little extra thought and care .NET Framework provides extensive support for XML and allows configurable access and manipulation of both relational and XML data Conflict Detection is achieved by adding a WHERE clause to UPDATE and DELETE statements It is still possible to utilize pessimistic locking Titles and Artists:  Titles and Artists 'Love Will Keep Us Together' The Captain andamp; Tennille 'My Way' Frank Sinatra 'We Can Work it Out' The Beatles 'Stop in the Name of Love' Diana Ross and the Supremes Questions?:  Questions?

Add a comment

Related presentations

Related pages

Adobe Deutschland: Kreativität, Marketing und ...

Adobe verändert die Welt durch digitale Erlebnisse. Lösungen von Adobe unterstützen Kunden beim Erstellen, Bereitstellen und Optimieren von Inhalten und ...
Read more

ADO.NET Free PDF Downloads ( 40 Ebooks )

ADO - .NET at JKU Pdf Size: 6.77 MB | Book Pages: 133 ADO.NET, Dietrich Birngruber, TechTalk 2 Contents zPart I: Basics zPart II: Connection-Oriented ...
Read more

Advanced Data Access with ADO.NET and Oracle

Learn how to use ADO.NET 1.1 to retrieve data from complex Oracle data types.
Read more

Excel VBA and ADO Programming - Advanced Excel

Using Excel VBA ADO, you are able to combine multiple data tables and retreive records from databases.
Read more


Bei ADO.NET handelt es sich um einen Satz von Klassen, die Datenzugriffsdienste für .NET Framework-Programmierer verfügbar machen. ADO.NET umfasst eine ...
Read more

Advanced Delphi Developer's Guide to Ado with CDR: Alex ...

Buy Advanced Delphi Developer's Guide to Ado with CDR on FREE SHIPPING on qualified orders
Read more

AdvancedMD - Medical Software

AdvancedMD web-based software is medical billing, practice management, electronic health records, ... Advanced MobileDoc ™ Advanced ...
Read more

Advanced Data Provider

Advanced Data Provider . The Advanced Data Provider - ADP, is a transparent factory for ADO.NET which loads providers dynamically.
Read more

9. (Advanced Programming In Access 2013) DAO vs ADO - YouTube

DAO vs ADO video in the "Advanced Programming in Microsoft Access 2013" series hosted by Steve Bishop. In this free advanced video tutorial ...
Read more

Advanced Materials - Wiley Online Library

Advanced Materials is also available on your mobile device. Click below for our: iOS app; Android app; Sign up for updates on the latest ...
Read more