Essential ADO.NET

      

      Day 1

      Data Modeling Basics

This module takes a few real world examples and discusses designing the database architecture around them. It elucidates the common sense logic, and explains significant points. In addition to serving a good refresher, these real world models will be touched up on over and over again as we dig deeper into ADO.NET. All through the course, we will try and understand what architectural decisions may sway your decision either way in light of ADO.NET 
 

      ADO.NET Basics

This module covers what is wrong with ADO? What is ADO.NET And why are we all learning ADO.NET This module draws the 30,000 feet view of application and where ADO.NET fits in the picture. 
 

      Organization and Architecture of ADO.NET

This module goes to about a 10,000 foot view, and splits ADO.NET into biggest logical parts and the reasoning behind such architecture. It gives a very brief introduction to the class hierarchy, connected and disconnected portions. It serves as a “Meet the players” section. 
 

      Hello World!!

This module concentrates on creating drag and drop ASP.NET and Winforms applications. It then dissects the auto generated code and presents business cases where the auto generated code is woefully inadequate in a real world application, and why the attendee needs to come and attend tomorrow. 
 

                     

      Day 2

      A Hands On Approach of writing a data driven Application

A quick recap of yesterday followed by why the data driven application we wrote yesterday was woefully inadequate and what we can do about that. Write up a quick hands-on application introducing the attendee to SqlConnection, SqlCommand, ConnectionStringBuilder and other such objects. Keep increasing the complexity of the application to demonstrate ExecuteScalar, Reader, multiple tabular resultsets, and asynchronous command execution.  
 

Post Mortem of the application we just wrote

Demonstrate the shortcomings of the connected application we just wrote. Modify the connected asynchronous data reader application to come across those shortcomings, run a real world example to “gasp”, “wow” the attendees, and break for lunch. 
 

      The Need for Disconnected Objects

Based upon the post-mortem, demonstrate why disconnected objects are necessary, and discuss the various choices available – Business Objects or System.Data.Data* objects. Discuss good business object design, and what you need to keep in mind when working with ADO.NET. 
 

The DataSet Family

An introduction to the disconnected data object ADO.NET comes with, and it’s various capabilities. 
 

Working with Disconnected data

Sorting, Searching and Filtering through disconnected data – how to do all that without T-SQL. 
 

 

      Day 3

      Filling DataSets - DataAdapters

Begin by revisiting Day #2. Filling Datasets  - demonstrate using two applications, one with CommandBuilder, demonstrate the sneaky nature of commandbuilder – and discuss the downsides. Demonstrate the alternative by specifying your own commands. Discuss the Ferrari vs. Honda approach to application design. 
 

      Updating disconnected Data Back into the database.

Bring up the drag drop application once again, and pick pieces out of that to demonstrate a hand written application that demonstrates saving disconnected data changes back into the database.  
 

Discuss the various concurrency issues.

Concurrency Mechanisms – a theoretical discussion, followed by an example written demonstrating a concurrency scheme – based on popular demand from the class. 
 

Concurrency and saving disconnected data, when the data is hierarchical in nature.

Close the day with a discussion on issues you need to be careful of when creating a data save mechanism for hierarchical data, and why it is different from a simple single table mechanism – reiterate the Ferrari versus Honda architecture approach. 
 

      Discuss various failure scenarios when updating disconnected data.

Discuss AcceptChangesOnUpdate, ContinueUpdateOnError, etc. Discuss Merge/GetChanges, and their practical usage. Again reiterate Ferrari vs. Honda and educate the attendee on which one to pick, when, and why. 
 
 

               

      Day 4  
 

      Discussion on Transactions

Recap day #3. Discuss Transaction support in ADO.NET, and how that fits into disconnected data updates. Also recap Data Reader, DataAdapter and explain MARS in SQL Server 2005, and explain why you shouldn’t go crazy with it. 
 

      System.Transactions Integration

Discuss theory elaborating two phase commits, RMs, DTC, LTM, various enlistment mechanisms – volatile, durable and PSPE, followed by a demonstration of SqlConnection instances in a distributed transaction. Also demonstrate writing your own RM and encapsulating non-database transactions within a database transaction. 
 

      Let us Learn SQLCLR a.k.a. “Who thinks T-SQL sucks?”

Devote half a day on discussing SQLCLR along with code examples, discuss the Pros and Cons of various approaches. Discuss why you still need to know T-SQL and why SQLCLR is not a replacement for T-SQL. 
 

Transactions in SQLCLR

Discuss transactions using both SqlTransaction and System.Transactions – dig deep into how context and non-context connections behave in SQLCLR transactions. 
 

      Best practices for Application Architecture

Put all the pieces together and discuss a few real world scenarios of where and how you would choose pessimistic vs. optimistic mechanisms, connected versus disconnected models, and SQLCLR versus plain vanilla ADO.NET code to deliver the final solution.