Advanced ADO.NET Techniques: Advanced ADO.NET Techniques Jackie Goldstein Renaissance Computer Systems Ltd. MSDN Regional Director, Israel Jackie@Renaissance.co.il 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?