Learn your way! Get started

ADO.NET Using Visual C# 2005

with experts Andy Baron, Ken Getz

Course at a glance

Included in these subscriptions:

Dev & IT Pro Video
Dev & IT Pro Power Pack

	Release date	10/17/2006
	Level	Advanced
	Runtime	14h 17m
	Closed captioning	N/A
	Transcript	N/A
	eBooks / courseware	Included
	Hands-on labs	Included
	Sample code	Included
	Exams	Included
	Platform	Internet Explorer Only

Enterprise Solutions

Need reporting, custom learning tracks, or SCORM? Learn More

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.

Meet the experts

Andy Baron is a nationally recognized industry expert specializing in Visual Basic, Visual C#, ASP.NET, ADO.NET, SQL Server, and SQL Server Business Intelligence. He is an experienced consultant, author, and editor for several industry publications and speaker at industry conferences. Andy has also been repeatedly selected as a Microsoft MVP for his contributions to Microsoft's online support.

Ken Getz is a featured instructor for several of our Visual Studio courses. He is a Visual Basic and Visual C# expert and has been recognized multiple times as a Microsoft MVP. Ken is a seasoned instructor, successful consultant, and the author or co-author of several best-selling books. He is a frequent speaker at technical conferences like Tech-Ed, VSLive, and DevConnections and he has written for several of the industry's most-respected publications including Visual Studio Magazine, CoDe Magazine, and MSDN Magazine.

Course outline

Module 1

Understand ADO.NET (35:18)

Introduction (02:05)
The Evolution of ADO.NET (13:12)
Benefits of ADO.NET (03:34)
ADO.NET Object Model (01:12)
.NET Data Provider Objects (01:37)
Connected Provider Objects (02:13)
Data-Caching Objects (08:03)
Understanding DataAdapters (01:49)
Putting it all Together (00:33)
Summary (00:57)

Connect to Data (26:47)

Introduction (00:58)
Course Sample Details (00:38)
Overview: Sample Database (00:25)
Overview: app.config (00:21)
Overview: Connection Settings (02:33)
Create Main Project Forms (01:43)
Connection String Builder (06:23)
Demo: Connect to SQL Server (02:14)
Demo: Connect to Access (03:21)
Code: Connect to SQL Server (03:59)
Code: Connect to Access (02:11)
Track State Changes (01:08)
Summary (00:46)

Pooling and Statistics (21:15)

Introduction (00:46)
Overview: Connection Pooling (06:26)
SqlConnectiion Pooling Props. (05:37)
Conn. Pool onitoring Tools (02:24)
Connection Pooling Stats (05:10)
Summary (00:50)

Module 2

Beg. Commands/DataReaders (37:26)

Introduction (00:44)
Overview: Command Classes (02:29)
Create Command Objects (00:34)
Create Cmd. Constructors (03:28)
Retrieve Connection Info. (03:33)
Command Object Methods (00:43)
Query Returning No Rows (01:24)
Execute a DML Query (00:30)
Execute a DDL Query (00:20)
ExecuteNonQuery Method (04:43)
Results from Running Query (00:59)
Close Conns. Automatically (01:32)
ExecuteReader Method (03:51)
Query Returning Single Value (01:00)
Demo: ExecuteScalar Method (01:56)
Retrieve Results Faster (01:14)
Retrieve Data w/DataReaders (06:50)
Summary (01:27)

Adv. Commands/DataReaders (40:39)

Introduction (00:39)
Retrieve Multiple Result Sets (03:19)
Retrieve Schema Information (04:04)
DataReaders Block Connections (01:16)
DataReader over DataTable (01:29)
DataTableReader Object (03:55)
Call Stored Proc./Saved Query (00:59)
Query with No Parameters (01:35)
Passing Parameters (01:19)
Using Parameters (00:56)
SQL Ad Hoc w/Params (02:20)
OLE Db Ad Hoc w/Params (01:35)
Stored Proc w/Output Param (03:05)
Manage Multiple Namespaces (00:23)
Multiple Providers (00:26)
Using Inheritance (00:58)
Create Command Method (03:09)
Data Binding in ASP.NET (06:56)
Summary (02:08)

Module 3

Populate DataSets/DataTables (30:41)

Introduction (01:21)
Explore DataSets/DataTables (02:03)
Populate a DataTable (00:58)
Define Columns / Rows (03:58)
Fill Using a DataAdapter (02:17)
Dig into DataSets/DataTables (01:05)
Use the Load Method (05:29)
Handle Multiple Results (09:27)
Demo: FillAuto (01:12)
Fill Using Named DataTable (00:32)
Table and Column Mappings (01:10)
Summary (01:05)

Schema and XML (25:47)

Introduction (00:58)
Explore Schema Sample (05:55)
Explore XML Sample (04:52)
DataColumn Properties (04:15)
MissingSchemaAction (01:14)
Use FillSchema (00:30)
Work with XML Data (01:45)
Write XML to a File (01:20)
Read XML from a File (01:28)
Get XML Filename (00:42)
Display XML Data in IE (01:07)
Summary (01:34)

Prim. Key/Unique Constraints (19:30)

Introduction (01:04)
Explore the Sample (01:43)
Demo: Fill Multiple Times (02:47)
Demo: Add Unique Constraints (04:59)
Demo: Add a Primary Key (03:43)
Demo: Merge DataSets (03:47)
Demo: DataSets u/Primary Key (00:34)
Summary (00:49)

Module 4

Foreign Key / Constraints (28:30)

Introduction (01:25)
Explore the Sample (06:15)
Setup Foreign Key Constraints (03:46)
Demo: Cascading Actions (02:00)
Cascading Actions Explained (07:38)
Other Constraints (02:02)
Disallow Nulls / MaxLength (04:31)
Summary (00:50)

Create DataRelations (17:07)

Introduction (02:02)
Relations in Databases (03:48)
Relations in ADO.NET (00:42)
Create Relations (02:26)
Create Relations w/Constraints (00:55)
Demo: Set Relations Code (03:24)
Relations and DataBinding (01:49)
Relations w/Constraints Code (00:36)
Summary (01:22)

Navigation Pathways (20:56)

Introduction (01:07)
Explore the Sample (02:06)
Demo: Fill the DataSet (02:29)
Demo: GetChildRows (03:31)
Demo: GetParentRow (02:15)
Expressions and Relations (00:51)
Aggregate / Lookups (04:16)
Specify XML Nesting (03:24)
Summary (00:53)

Create / Execute Commands (33:14)

Introduction (01:34)
Code: Grab the Sample Data (02:59)
Code: CommandBuilder (02:11)
Code: Update w/Cmd Builder (01:12)
Create / View Commands (02:52)
Update Data w/Cmd Builder (00:23)
Overview: ConflictOption (02:39)
Code: Create Custom Commands (04:47)
Create Custom Commands (00:49)
DataRowVersion (04:58)
RowState (03:58)
Accept / Reject Changes (01:22)
Code: GetChanges (02:38)
Summary (00:47)

Module 5

Work with Stored Procedures (35:04)

Introduction (01:04)
Advantages of Stored Procs (06:47)
Demo: View the Sample Form (01:04)
Create Stored Procedures (03:03)
Delete Stored Procs (01:54)
Use Insert Stored Procedure (05:51)
Use AcceptChanges (01:01)
Use Delete Stored Procedure (02:07)
Demo: Use Stored Procedures (01:35)
Demo: Perform Batch Updates (04:03)
Perform Batch Updates (01:21)
Set UpdateBatchSize (00:15)
Set UpdatedRowSource (03:22)
Summary (01:29)

Intro to Typed Datasets (35:52)

Introduction (01:18)
Use Loosely Typed DataSet (01:00)
Standard DataSet Pitfalls (00:35)
A Better Way for DataSets (00:45)
Create Strongly Typed DataSet (00:57)
Create Data Connection (02:32)
Intro. to the TableAdapter (01:30)
View the XSD File (01:32)
Investigate the New Class (00:49)
Use the Class View Window (03:46)
Write Code to Test DataSet (05:25)
Use a Strongly Typed DataSet (00:30)
Work with Untyped DataSet (02:20)
Work with Typed DataSet (01:55)
Work with Typed DataTable (01:40)
Review: Typed DataSet (00:31)
Review: TableAdapter (00:24)
Find Row in Typed DataSet (02:24)
Edit Row in Typed DataSet (01:44)
Add Row in Typed DataSet (02:34)
Summary (01:33)

Advanced Typed Datasets (37:08)

Introduction (01:11)
Work with Null Values (02:30)
Nav. Rels. in Typed DataSet (00:47)
Demo: Navigate Relations (04:27)
Create TableAdapter Queries (01:09)
Demo: TableAdapter Queries (05:47)
Issues w/Strongly Typed DS (00:33)
Typed DataSets/Data Binding (02:30)
Typed/Untyped DS Efficiency (00:47)
Demo: Typed / Untyped Efficiency (04:22)
Convert Bet. Typed/Untyped (04:39)
Using Annotations (02:16)
Demo: Using Annotations (00:56)
Attrib. to Modify Class Names (03:24)
Summary (01:44)

Module 6

Structured Exception Handling (18:01)

Introduction (00:44)
Basic Exception Handling (02:18)
Simple Try/Catch (01:28)
Try/Catch/Finally (02:11)
No Error Handling (00:33)
Use the Exception Object (04:25)
Demo: Exception Sample (01:29)
Exception Assistant (03:44)
Summary (01:04)

Data Errors/Transactions (34:01)

Introduction (01:04)
SqlException Severity Levels (02:58)
Handle Multiple Errors (02:50)
SQL Errors Property (01:20)
Handle Informational Errors (02:08)
Handle Multiple Severity Errors (01:11)
Demo: Handle Multiple Errors (00:31)
Handle InfoMessage Errors (00:10)
Errors and InfoMessages (00:22)
Demo: Using Transactions (05:20)
Update using Transaction (04:55)
Nested Exception Handling (01:13)
Transaction Isolation Levels (07:22)
Summary (02:31)

Handle Concurrency Errors (52:33)

Introduction (01:03)
Handle Optimistic Concurrency (01:56)
Use Update Criteria (04:28)
Demo: Run Scripts from VS (03:25)
Update w/Stored Procs (06:23)
Check Validations (01:22)
Perform the Update (00:23)
@@ERROR/@@ROWCOUNT (01:40)
Handle Concurrency Errors (02:52)
Try/Catch in SQL Server 2K5 (03:11)
SQL Server 2K5 Scripts (00:20)
Data Updates with ADO.NET (06:17)
Page_Load/Refresh Data (04:18)
Code for Updating Changes (00:58)
Code for Cancel Changes (00:22)
Code to Save Changes (05:23)
RowUpdated event (02:09)
Code for Concurrency Errors (04:51)
Summary (01:03)

Module 7

Search Data Tables (23:56)

Introduction (00:51)
Setup Sample Project (00:59)
MissingSchemaAction (02:01)
Work with Datatables (00:23)
Search Primary Key Values (00:45)
IndexOf Method (00:41)
Simple Dynamic Searches (01:01)
Search Primary Key Values (01:23)
Search Multi-Column Key (00:57)
Simple Dynamic Search (01:09)
Search with Wildcards (01:07)
Managing Delimiters (01:07)
Other DataTypes / Delimiters (00:19)
Demo: Manage Delimiters (02:45)
Additional Filter Issues (00:36)
Search with Row State (00:28)
Use DataViewRowState (02:26)
Demo: Search with Sort (01:25)
Search DataViewRowState (02:20)
Summary (01:04)

Search Data Views (32:03)

Introduction (00:55)
What is a DataView (00:40)
Create a DataView (00:32)
Sort Data in a DataView (00:47)
Demo: Sort Data (01:37)
Filter Data in a DataView (02:20)
Demo: Filter Data (01:44)
Demo: Filter on Row State (03:13)
Iterate through Rows (00:58)
Demo: Iterate through Rows (02:05)
Find a Row in a DataView (01:08)
Find Multi. Rows in DataView (00:23)
Demo: Find a Row (02:13)
Demo: Find Multiple Rows (01:23)
Modifying Data in a DataView (00:37)
Add a New Row in a DataView (01:23)
Modify a Row in a DataView (01:40)
Delete a Row in a DataView (00:51)
Demo: Modify Data (02:48)
DataTable from DataView (01:04)
DataView.ToTable Method (01:45)
Summary (01:49)

Create AutoIncrement Cols. (28:58)

Introduction (02:21)
AutoIncrement Typed DataSet (00:22)
AutoIncrement Property (00:55)
AutoIncrement SQL Server (04:33)
AutoIncrement with Access (03:13)
Work with AutoIncrement (00:32)
Retrieve Database Values (01:18)
Use SCOPE_IDENTITY() (04:00)
Use the RowUpdated Event (05:38)
Reset Identity in Access/Jet (02:09)
Reset the SQL Server Identity (02:32)
Summary (01:20)

Module 8

Solve AutoIncrement Probs. (25:58)

Introduction (00:51)
Issues with AutoIncrement (05:41)
AutoIncrement - No Conflicts (04:22)
Issues Merging Rows (02:55)
Merge Rows - No Conflicts (05:22)
Code: Fix Merge Rows Issue (04:24)
Summary (02:20)

ADO and XML (39:22)

Introduction (01:29)
DataSet vs DataTable (00:47)
Read and Write XML (00:17)
Visual Studio Tools for XML (03:24)
Work w/XML Programmatically (00:41)
ReadXml/WriteXml Methods (04:48)
Save Data as XML (01:00)
GetXml Method (00:13)
Demo: GetXml Method (01:36)
DataSet XML Output Options (00:32)
WriteXml Method (01:23)
XML Output as a DiffGram (01:15)
Demo: WriteXml Method (02:15)
Demo: DataSet XML Options (02:32)
Demo: Setup Schema Options (02:54)
Demo: XML as a DiffGram (03:13)
Load XML Data into a DataSet (02:04)
Read XML - Infer Schema (01:32)
Read XML - Internal Schema (01:04)
Read XML - External Schema (01:10)
Read XML - DiffGram (01:39)
XML String to DataSet (01:34)
Summary (01:48)

Module 9

XML Data Document/Xquery (51:14)

Introduction (01:19)
DataSets and XML (01:07)
Use an XmlDataDocument (00:47)
Demo: Use XmlDataDocument (03:36)
View Full Input (00:22)
View Full Output (01:09)
Format XML Data with XSLT (00:50)
Demo: xsl Compiled Transform (04:21)
Query DataSet Data (00:59)
Demo: Query with XPath (04:53)
XML in SQL Server (02:45)
Using XQuery (02:26)
Conn. to AdventureWorks (00:56)
Demo: Retrieve Last Names (05:24)
Demo: Filter the Results (04:05)
XQuery and ADO.NET (00:32)
Demo: XQuery Queries (01:45)
Return Single Value (01:36)
XQuery u/value Method (02:00)
XQuery FLWOR Statements (01:20)
Demo: Use FLWOR to Filter (03:18)
Modify XML Data using Xquery (04:16)
Summary (01:15)

Asynchronous Processing (26:48)

Introduction (01:22)
Async Query Execution (04:59)
Async Completion Signaling (06:24)
Asynchronous Processing (01:11)
View Connection String (01:05)
View SQL Strings (01:17)
SqlCommands/WaitHandles (04:42)
Setup Asynchronous Loop (02:08)
Setup the Results Textbox (02:29)
Summary (01:08)

Module 10

Bulk Copy and Snapshot (32:05)

Introduction (00:34)
Bulk Copy Options (03:22)
Run Script to Create Table (01:32)
Using Bulk Copy to Load Data (01:28)
Code: Bulk Data Copy (03:01)
WriteToServer Method (01:38)
Code: Snapshot Isolation (00:57)
Pooling Set to False (01:36)
Enable Snapshot Isolation (02:14)
Transaction Isolation Levels (00:51)
Serializable (02:01)
Snapshot (02:22)
ReadCommitted (01:33)
ReadUncommitted (02:04)
Demo: Snapshot Isolation (02:37)
Summary (04:07)

Development Enhancements (47:03)

Introduction (00:30)
Enumerate SQL Servers (03:12)
MARS Scenarios and Caveats (07:54)
Mutliple Active Result Sets (06:19)
Demo: Max DataTypes (02:40)
Retrieve Max DataTypes (05:15)
Retrieve Text Values (03:03)
Enable Query Notifications (04:04)
Rules for Query Notifications (08:50)
Code: Query Notifications (03:06)
View Global.asax (00:59)
Summary (01:07)