Use ADO.NET tutorials within 'ADO.NET 2.0 Essentials DVD Training' to learn about programming with ADO.NET 2.'

ADO.NET 2.0 Essentials

• 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