ADO.NET 2.0 Essentials (5 Levels) DVD Training

ADO.NET 2.0 Essentials (5 Levels) DVD Training

ADO.NET 2.0 Essentials (5 Levels) DVD Training

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

• 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?

• 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.

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

• 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.

• 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.

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

• 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

• 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

• 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

• 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

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

• 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

• 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

• 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

• 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

4.0 Transactions

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

• 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

• 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

• 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

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

5.0 CLR Integration – Introduction

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

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

• 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.

• 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.