ADO.NET Using Visual C# 2005 with Ken Getz and Andy Baron

ADO.NET Using Visual C# 2005

with Ken Getz and Andy Baron

Course Description: In this course, learn about the features provided by ADO.NET 2.0. Learn to connect to data sources, retrieve and manipulate data, and perform data updates. Examine the various ADO.NET classes, including Connection, Command, DataReader, DataSet, DataTable, DataRelation, and more. See how to apply constraints and relationships to disconnected data. Learn how to update data, including how to handle stored procedures, parameters, and return values. Understand how to search, sort, and filter data stored in a DataSet or DataTable. Become familiar with strongly typed DataSets and learn their advantages. See how to leverage the power of XML through serialization, Diffgrams,and the XMLDataDocument object. Examine new ADO.NET features that support SQL Server 2005 technologies, including asynchronous commands, Multiple Active Result Sets, bulk inserts, and more.

15+ hours of media runtime 

Understand ADO.NET

The Evolution of ADO.NET
Benefits of ADO.NET
ADO.NET Object Model
.NET Data Provider Objects
Connected Provider Objects
Data-Caching Objects
Understanding DataAdapters
Putting it all Together

Connect to Data

Course Sample Details
Overview: Sample Database
Overview: app.config
Overview: Connection Settings
Create Main Project Forms
Connection String Builder
Demo: Connect to SQL Server
Demo: Connect to Access
Code: Connect to SQL Server
Code: Connect to Access
Track State Changes

Pooling and Statistics

Overview: Connection Pooling
SqlConnectiion Pooling Props.
Conn. Pool onitoring Tools
Connection Pooling Stats

Beg. Commands/DataReaders

Overview: Command Classes
Create Command Objects
Create Cmd. Constructors
Retrieve Connection Info.
Command Object Methods
Query Returning No Rows
Execute a DML Query
Execute a DDL Query
ExecuteNonQuery Method
Results from Running Query
Close Conns. Automatically
ExecuteReader Method
Query Returning Single Value
Demo: ExecuteScalar Method
Retrieve Results Faster
Retrieve Data w/DataReaders

Adv. Commands/DataReaders

Retrieve Multiple Result Sets
Retrieve Schema Information
DataReaders Block Connections
DataReader over DataTable
DataTableReader Object
Call Stored Proc./Saved Query
Query with No Parameters
Passing Parameters
Using Parameters
SQL Ad Hoc w/Params
OLE Db Ad Hoc w/Params
Stored Proc w/Output Param
Manage Multiple Namespaces
Multiple Providers
Using Inheritance
Create Command Method
Data Binding in ASP.NET

Populate DataSets/DataTables

Explore DataSets/DataTables
Populate a DataTable
Define Columns / Rows
Fill Using a DataAdapter
Dig into DataSets/DataTables
Use the Load Method
Handle Multiple Results
Demo: FillAuto
Fill Using Named DataTable
Table and Column Mappings

Schema and XML

Explore Schema Sample
Explore XML Sample
DataColumn Properties
MissingSchemaAction
Use FillSchema
Work with XML Data
Write XML to a File
Read XML from a File
Get XML Filename
Display XML Data in IE

Prim. Key/Unique Constraints

Explore the Sample
Demo: Fill Multiple Times
Demo: Add Unique Constraints
Demo: Add a Primary Key
Demo: Merge DataSets
Demo: DataSets u/Primary Key

Foreign Key / Constraints

Explore the Sample
Setup Foreign Key Constraints
Demo: Cascading Actions
Cascading Actions Explained
Other Constraints
Disallow Nulls / MaxLength

Create DataRelations

Relations in Databases
Relations in ADO.NET
Create Relations
Create Relations w/Constraints
Demo: Set Relations Code
Relations and DataBinding
Relations w/Constraints Code

Navigation Pathways

Explore the Sample
Demo: Fill the DataSet
Demo: GetChildRows
Demo: GetParentRow
Expressions and Relations
Aggregate / Lookups
Specify XML Nesting

Create / Execute Commands

Code: Grab the Sample Data
Code: CommandBuilder
Code: Update w/Cmd Builder
Create / View Commands
Update Data w/Cmd Builder
Overview: ConflictOption
Code: Create Custom Commands
Create Custom Commands
DataRowVersion
RowState
Accept / Reject Changes
Code: GetChanges

Work with Stored Procedures

Advantages of Stored Procs
Demo: View the Sample Form
Create Stored Procedures
Delete Stored Procs
Use Insert Stored Procedure
Use AcceptChanges
Use Delete Stored Procedure
Demo: Use Stored Procedures
Demo: Perform Batch Updates
Perform Batch Updates
Set UpdateBatchSize
Set UpdatedRowSource

Intro to Typed Datasets

Use Loosely Typed DataSet
Standard DataSet Pitfalls
A Better Way for DataSets
Create Strongly Typed DataSet
Create Data Connection
Intro. to the TableAdapter
View the XSD File
Investigate the New Class
Use the Class View Window
Write Code to Test DataSet
Use a Strongly Typed DataSet
Work with Untyped DataSet
Work with Typed DataSet
Work with Typed DataTable
Review: Typed DataSet
Review: TableAdapter
Find Row in Typed DataSet
Edit Row in Typed DataSet
Add Row in Typed DataSet

Advanced Typed Datasets

Work with Null Values
Nav. Rels. in Typed DataSet
Demo: Navigate Relations
Create TableAdapter Queries
Demo: TableAdapter Queries
Issues w/Strongly Typed DS
Typed DataSets/Data Binding
Typed/Untyped DS Efficiency
Demo: Typed / Untyped Efficiency
Convert Bet. Typed/Untyped
Using Annotations
Demo: Using Annotations
Attrib. to Modify Class Names

Structured Exception Handling

Basic Exception Handling
Simple Try/Catch
Try/Catch/Finally
No Error Handling
Use the Exception Object
Demo: Exception Sample
Exception Assistant

Data Errors/Transactions

SqlException Severity Levels
Handle Multiple Errors
SQL Errors Property
Handle Informational Errors
Handle Multiple Severity Errors
Demo: Handle Multiple Errors
Handle InfoMessage Errors
Errors and InfoMessages
Demo: Using Transactions
Update using Transaction
Nested Exception Handling
Transaction Isolation Levels

Handle Concurrency Errors

Handle Optimistic Concurrency
Use Update Criteria
Demo: Run Scripts from VS
Update w/Stored Procs
Check Validations
Perform the Update
@@ERROR/@@ROWCOUNT
Handle Concurrency Errors
Try/Catch in SQL Server 2K5
SQL Server 2K5 Scripts
Data Updates with ADO.NET
Page_Load/Refresh Data
Code for Updating Changes
Code for Cancel Changes
Code to Save Changes
RowUpdated event
Code for Concurrency Errors

Search Data Tables

Setup Sample Project
MissingSchemaAction
Work with Datatables
Search Primary Key Values
IndexOf Method
Simple Dynamic Searches
Search Primary Key Values
Search Multi-Column Key
Simple Dynamic Search
Search with Wildcards
Managing Delimiters
Other DataTypes / Delimiters
Demo: Manage Delimiters
Additional Filter Issues
Search with Row State
Use DataViewRowState
Demo: Search with Sort
Search DataViewRowState

Search Data Views

What is a DataView
Create a DataView
Sort Data in a DataView
Demo: Sort Data
Filter Data in a DataView
Demo: Filter Data
Demo: Filter on Row State
Iterate through Rows
Demo: Iterate through Rows
Find a Row in a DataView
Find Multi. Rows in DataView
Demo: Find a Row
Demo: Find Multiple Rows
Modifying Data in a DataView
Add a New Row in a DataView
Modify a Row in a DataView
Delete a Row in a DataView
Demo: Modify Data
DataTable from DataView
DataView.ToTable Method

Create AutoIncrement Cols.

AutoIncrement Typed DataSet
AutoIncrement Property
AutoIncrement SQL Server
AutoIncrement with Access
Work with AutoIncrement
Retrieve Database Values
Use SCOPE_IDENTITY()
Use the RowUpdated Event
Reset Identity in Access/Jet
Reset the SQL Server Identity

Solve AutoIncrement Probs.

Issues with AutoIncrement
AutoIncrement - No Conflicts
Issues Merging Rows
Merge Rows - No Conflicts
Code: Fix Merge Rows Issue

ADO and XML

DataSet vs DataTable
Read and Write XML
Visual Studio Tools for XML
Work w/XML Programmatically
ReadXml/WriteXml Methods
Save Data as XML
GetXml Method
Demo: GetXml Method
DataSet XML Output Options
WriteXml Method
XML Output as a DiffGram
Demo: WriteXml Method
Demo: DataSet XML Options
Demo: Setup Schema Options
Demo: XML as a DiffGram
Load XML Data into a DataSet
Read XML - Infer Schema
Read XML - Internal Schema
Read XML - External Schema
Read XML - DiffGram
XML String to DataSet

XML Data Document/Xquery

DataSets and XML
Use an XmlDataDocument
Demo: Use XmlDataDocument
View Full Input
View Full Output
Format XML Data with XSLT
Demo: xsl Compiled Transform
Query DataSet Data
Demo: Query with XPath
XML in SQL Server
Using XQuery
Conn. to AdventureWorks
Demo: Retrieve Last Names
Demo: Filter the Results
XQuery and ADO.NET
Demo: XQuery Queries
Return Single Value
XQuery u/value Method
XQuery FLWOR Statements
Demo: Use FLWOR to Filter
Modify XML Data using Xquery

Asynchronous Processing

Async Query Execution
Async Completion Signaling
Asynchronous Processing
View Connection String
View SQL Strings
SqlCommands/WaitHandles
Setup Asynchronous Loop
Setup the Results Textbox

Bulk Copy and Snapshot

Bulk Copy Options
Run Script to Create Table
Using Bulk Copy to Load Data
Code: Bulk Data Copy
WriteToServer Method
Code: Snapshot Isolation
Pooling Set to False
Enable Snapshot Isolation
Transaction Isolation Levels
Serializable
Snapshot
ReadCommitted
ReadUncommitted
Demo: Snapshot Isolation

Development Enhancements

Enumerate SQL Servers
MARS Scenarios and Caveats
Mutliple Active Result Sets
Demo: Max DataTypes
Retrieve Max DataTypes
Retrieve Text Values
Enable Query Notifications
Rules for Query Notifications
Code: Query Notifications
View Global.asax