Course Highlights
- Connect to a data source, execute commands and retrieve data from
a data source.
- Fill, use and update disconnected data.
- Work with both the Connection and Command objects.
- Saving data using CommandBuilder or your own commands.
- Understand Concurrency management basics and learn how to avoid concurrency
conflicts.
About This Course
This Microsoft ADO.NET 2.0 Training Course provides development professionals
with an introduction to working with Microsoft ADO.NET. In this course,
you’ll get started right away building your first application using
drag and drop WinForms. You’ll then move on to build subsequent
applications learning ADO.NET features as you build.
Throughout this course, your instructor, Sahil Malik, will walk you through
the process of working with data readers and asynchronous commands. You’ll
also learn about CLR Integration and working with transactions through
ADO.NET, among other topics.
There are over 150 lessons across 6 levels in this course – all
designed to provide increase your productivity in the Microsoft ADO.NET
development environment.
Course Outline
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
|