• 1.0 Introduction
• 1.1 Pre requisites for this course
• 1.2 What is ADO.NET
• 1.3 Building your first application – Drag and Drop Winforms

• Adding a datasource to your winforms application
• ConnectionStrings section in Web.Config
• Brief introduction to a Strongly Typed Dataset
• Introduction to DataGridView or Details View for Winforms
• Introduction to TableAdapters
• Introduction to BindingSource
• Introduction to BindingNavigator

• 1.4 Building your first application – Drag And Drop ASP.NET

• Authentication, security and connection strings in web based applications
• A slightly more hands on approach
• Introduction to SqlDataSource
• Specifying your own queries to a SqlDataSource
• Tying together a SqlDataSource with a ConnectionString in the config file
• The ASP.NET GridView Control
• Tying an ASP.NET GridView to a SQLDataSource
• Enabling Paging/Sorting on GridView
• Why Drag & Drop is not all you need to know?

• 1.5 The connection object

• The SqlConnection Object
• Connecting to the database
• The ConnectionStringBuilder
• Connection Pooling
• Best practice: Open as late as possible and close as early as you can.

• 1.6 The command object

• The SqlCommand Object
• The various execute methods on a SqlCommand
• Fetching Scalar Values out of the database

• 1.7 Data Readers

• The SqlComamnd.ExecuteReader method
• Checking for the existence of rows in a data reader
• Fetching different datatypes out of a data reader
• The right usage and limitations of a SqlDataReader.
• CommandBehaviors when creating SqlDataReaders
• Configuring the Internal buffering behavior of SqlDataReader using CommandBehavior.SequentialAccess
• Batched TSQL commands
• Using Batched TSQL commands, or stored procedures to return multiple tabular results in a resultset
• Using a SqlDataReader to browse over multiple tabular results.

• 1.8 Asynchronous Commands

• Limitations of running Execute commands on a SqlCommand object
• Basics of ASynch execution
• The Begin/End design pattern for running Asynch operations
• Modifying a synchronous SqlDataReader application to convert it into an Asynch application
• Things to be careful of in an Asynch application.

• 1.9 Connected Nature of Commands and Connections

• 2.0 Disconnected Objects

• The need for disconnected objects
• Desirable characteristics for disconnected objects
• Disconnected data objects
• Business Objects
• Disconnected data objects
• Data Transfer Objects
• DataSet and family

• 2.1 Introduction to DataSet and Strongly typed DataSets

• Creating a Strongly Typed Dataset in Visual Studio
• Specifying relations between tables in Strongly typed datasets
• Examining the auto generated code.
• Using the strongly typed dataset – reading and writing to an XML file
• Controlling the XML output using the Nested Property on DataRelation
• Creating an application to load, edit, create, and save a strongly typed dataset
• Extending the same application to load, edit, create, and save a non-strongly typed dataset
• Compare the usage of strongly typed dataset versus non-strongly typed dataset
• Discussing other possibilities with disconnected objects

• 2.2 Working with disconnected data – adding and editing

• Creating a new row and adding it to a DataTable
• Examining the RowStates for Detached and Added rows
• Modifying a datarow contents
• Comparing the Added vs. Modified Rowstates
• Extracting Changes from a DataSet
• Setting Expressions on new data columns & limitations of non-strongly typed datasets

• 2.3 Working with disconnected data – Searching, Sorting

• Finding a specific row in a DataTable using the Find Method
• Specifying a primary key to a DataTable
• The difference between Removing and Deleting a row from a DataTable
• Finding more than one matching rows using the Select Method
• Sorting DataTable data
• Filtering data as per RowStates
• Comparing the filtering usage in strongly typed datasets
• Browsing through relational data in a dataset

• 2.4 Introduction to DataView objects

• Comparing a dataview to a database view
• DataTable.Select versus DataView.RowFilter
• Sorting using a DataView
• Limiting the number of columns in the resultset
• Comparing holding a reference to a DataTable, to creating a new DataTable
• Doing a select distinct equivalent on a DataTable

• 2.5 Level 2 Recap

• 3.0 Filling, Using and Updating Disconnected Data 3.1 Filling Data – The DataAdapter

• Various ways of setting up a DataAdapter
• Demonstrating the interaction of SqlConnection and DataAdapter, and how DataAdapter gives you better connection pooling
• Modifying disconnected data
• Demonstrating that the data is indeed disconnected

• 3.2 Saving Data – CommandBuilder

• DataAdapter.Update
• The Update that inserts, updates and deletes
• Specifying Insert, Update and Delete commands to a DataAdapter
• Creating the commands automatically using a CommandBuilder object
• Demonstrating the connection between RowStates and the exact command called
• Why are the default CommandBuilder commands are woefully terrible
• Restrictions on using CommandBuilder
• Demonstrate options for fixing CommandBuilder commands Conflict Option

• 3.3 Saving Data – Your own commands

• Situations where you must specify your own commands
• Specifying Stored Procedures to SqlCommands
• Adding Parameters to a SqlCommand
• Connecting a DataTable with a Stored procedure
• The SourceColumn property of a SqlParameter
• Concurrency Issues
• Adding new key values to new rows

• 3.4 A quick primer on concurrency

• Concurrency Management Basics
• Avoiding concurrency conflicts
• Optimistic vs. Pessimistic Concurrency Checks
• Various kinds of Optimistic concurrency mechanisms and their problems
• Concurrency management in hierrachical data

• 3.5 Primary Concurrency Resolution

• Using a DataGridView to restrict user input based on a Strongly Typed Dataset
• Creating a windows based, handwritten data driven application
• Writing your own commands that fetch back identity values
• Specifying negative seed and increment values on the primary key to generate fake keys
• Replacing the fake keys with database generated keys during DataAdapter.update

• 3.6 Level 3 Recap

• 4.0 Transactions

• Transaction Basics
• What is a Transaction?
• Basic Characteristics of a Transaction.

• 4.1 Transactions in Database and Transactions through ADO.NET

• Writing two SqlCommands in a non-transactional code, comparing it to TSQL
• The SqlTransaction data type
• Wrapping those two commands inside a transaction using the SqlTransaction variable.
• Using Try-Catch blocks to write elegant transactional code.
• Best practices in writing Transactional Code

• 4.2 Transactions in disconnected data

• Using SqlTransaction with DataAdapters
• Fetching the last refresh of data, after having saved data using a DataAdapter
• The need for tying in all commands on a dataadapter on a SqlTransaction
• Various Scenarios in transactional updates using DataAdapters
• Using GetChanges and Merge to gracefully refresh data out of the database
• Specifying Schema information to a DataSet, so Merge works intelligently
• AcceptChangesOnUpdate
• Preserving RowStates in event of a failed transaction
• ContinueUpdateOnError
• Continue with further rows in event of a failed command

• 4.3 System.Transactions Theory

• Various options for distributed transactions
• Introduction to System.Transactions
• Important names
• Resource Managers and Transaction Coordinator
• Two phase commit basics
• Lightweight Transaction Manager versus MSDTC
• Enlistment Mechanisms
• Volatile, Durable and Promotable

• 4.4 System.Transactions Example

• Usage of TransactionScope
• Rolling back using System.Transactions
• Demonstrating promotion to MSDTC
• Isolation level increment on promotion of a transaction

• 4.5 Level 4 Recap

• 5.0 CLR Integration – Introduction

• Comparing SQLCLR with TSQL
• Right usage of SQLCLR
• Comparing SQLCLR with extended stored procedures

• 5.1 Writing your first SQLCLR object – A UDF

• Leveraging Visual Studio to write SQLCLR objects
• Enabling SQLCLR Debugging on a server
• Writing a simple UDF in SQLCLR
• Debugging SQLCLR code

• 5.2 Writing a TVF

• Specifying FillRowMethodName and TableDefinition to a UDF
• Returning an IEnumerable for the table
• Seperating out a row’s contents into column details, implementing the Fill Row Method
• Implementing the enumerator
• Pros and Cons of using inbuilt enumerators versus writing your own.

• 5.3 Interacting with the underlying database -Context Connections & Transactions

• An intro to Context connections
• Restrictions on Context Connections
• How many context connections can you open?
• Using SQLCLR code inside a transaction with other TSQL code
• Latching onto the current transaction using System.Transactions
• System.Transactions integration with SQLCLR
• Best practices for using SqlTransaction versus System.Transactions in SQLCLR code.

• 5.4 Course Recap