Data Access with Microsoft Visual Studio 2005

Overview:

In this course, you will learn the core and advanced skills of accessing and editing data by using ADO.NET and XML in a focused, hands-on environment. You'll get the knowledge and skills you need to use advanced data access features and techniques in Microsoft .NET Framework and Microsoft Visual Studio 2005. Learn to access data and implement database functionality by using Microsoft ADO.NET 2.0 and Microsoft SQL Server 2005. Also, learn to locate, edit, and transform XML by using XPath and Extensible Style sheet Language for Transformations (XSLT).

This course incorporates material from the following Official Microsoft Learning Products:

• 2541: Core Data Access with Microsoft® Visual Studio® 2005
• 2542: Advanced Data Access with Microsoft Visual Studio 2005

Description:

1. Connecting to Databases and Reading Data

Get an introduction to the fundamental skills required to connect to a
database and read data from the database. Learn to use ADO.NET data providers to
connect to various different kinds of databases and to execute a query that
returns a scalar value from the database. Learn to use connection pooling to
achieve scalability and learn to handle connection events and exceptions.

• ADO.NET
• Connecting to a Database and Reading Data
• What Is Connection Pooling?

2. Querying and Updating Databases by Using Commands

Learn to create and run commands that return a scalar value, return a result
set, update data in the database, or update the schema of the database. Also
learn to create and run parameterized commands.

• ADO.NET Commands
• Passing Parameters into Commands

3. Performing Transactional Operations

Learn to manage transactions in a .NET application at the middle tier, and
learn how to write ADO.NET code to start, commit, and rollback local
transactions. Specify an appropriate isolation level for a transaction and
enlist in distributed transactions.

• Transactions
• Managing Local Transactions
• Managing Distributed Transactions
• Isolation Levels

4. Performing Disconnected Operations Programmatically

Create and use DataSets programmatically in this unit. Learn to create
DataSet, DataTable, and DataColumn objects, populate a DataSet manually, and
load and save data by using a DataAdapter. You'll also learn to create in-memory
views on data by using a DataView.

• The ADO.NET Disconnected Model
• Loading and Saving Data in a DataSet
• DataViews

5. Performing Disconnected Operations by Using Visual Studio 2005 Wizards

Learn to create typed DataSets, DataAdapters, and TableAdapters by using the
TableAdapter Configuration Wizard and the Data Source Configuration Wizard in
Visual Studio 2005. Further, learn to write type-safe code to access data in a
typed DataSet.

• Comparing Untyped DataSets with Typed DataSets
• What Are Table Adapters?
• Demonstration: Creating a Typed DataSet by Using Visual Studio 2005
  Wizards

6. Performing XML Operations on Disconnected Data

In this unit, learn to read and write DataSets in XML format. Read and write
data only, schema only, or a combination of the two. Also, learn to read and
write a DataSet as a DiffGram, so that modifications to the data can be retained
when the DataSet is serialized to XML format.

• XML Representations of DataSets
• DiffGrams

7. Reading and Writing XML Data

Learn to use the XmlReader and XmlWriter classes to serially read and write
XML data. Read elements, attributes, and text content in an XML document, and
perform validation against an XML schema. You'll also learn to create an XML
document and write elements, attributes, namespace declarations, and text
content.

• Serially Reading XML Data
• Serially Writing XML Data

8. Processing XML Data by Using DOM

Learn to load an XML document into a DOM tree and validate the XML document
against an XML schema. Read existing XML content, modify XML content, and save
the DOM tree to an XML document.

• DOM
• DOM Trees
• Types of XML Nodes in a DOM Tree

9. Minimizing and Handling Database Operation Conflicts

Learn to minimize data access conflicts and handle them when they occur.
Implement optimistic concurrency in the ADO.NET disconnected model, and
implement optimistic concurrency by using the various isolation levels available
in SQL Server 2005.

• Why Data Conflicts Arise
• Isolation Levels Available in SQL Server 2005
• Guidelines for Using SQL Server 2005 Isolation Levels

10. Handling Large Objects

Learn to read and write large values efficiently to a SQL Server database and
to read large binary values and large text values by using SequentialAccess for
a SqlDataReader. Also, learn to write large binary values and large text values
and conserve resources when writing large values.

• Binary Large Objects and Character Large Objects
• Reading Large Objects from a Database
• Writing Large Objects to a Database

11. Enhancing Database Performance

Enhance database performance by using new features available in ADO.NET 2.0.
Perform asynchronous data operations, create multiple active result sets,
perform batch updates, and perform bulk copies.

• ADO.NET Enhancements in the .NET Framework 2.0
• SQL Server Provider Statistics

12. Creating Managed Code Objects for SQL Server 2005

Learn to create database objects for SQL Server 2005 in a .NET Framework
programming language. Learn to create stored procedures, triggers, user-defined
functions, aggregates, and user-defined types in managed code. Additionally,
learn to deploy an assembly that contains managed objects into SQL Server 2005
and to declare database objects to reference the managed objects.

• Benefits of Creating Managed Code Objects
• Demonstration: Importing an Existing Assembly into SQL Server 2005
• Demonstration: Implementing Managed Code Objects in SQL Server 2005

13. Querying XML by Using XPath

Use XPath in a .NET Framework application. Learn to create an XPathNavigator
object on an XML document and use it to locate content and evaluate expressions.
Also, use the XPathNavigator object to edit XML data.

• The XPath Data Model
• Selecting and Editing XML Data Using XPathNavigator
• Evaluating XPath Expressions Using XPathNavigator

14. Transforming XML by Using XSLT Style Sheets

Learn to load an XSLT style sheet in an application and execute the style
sheet to transform an XML document. Learn to pass parameters into a style sheet
and create and use extension objects.

• XSLT
• Executing an XSLT Style Sheet
• Extension Objects

Agenda:

Lab 1: Connecting to Databases and Reading Data

• Connecting to a Database
• Saving a Connection String Securely in an Application Configuration File
• Retrieving Data from a Database
• Handling Connection Events and Exceptions
• Configuring and Using Connection Pooling (if time permits)

Lab 2: Querying and Updating Databases by Using Commands

• Creating and Running Query Commands
• Creating and Running Parameterized Commands
• Creating and Running Update Commands
• Using Globalized Formats for Storing and Accessing Data (if time permits)

Lab 3: Performing Transactional Operations

• Performing Data Updates within a Transaction
• Choosing an Appropriate Isolation Level for a Transaction
• Enlisting in a Distributed Transaction
• Managing Transactions in the Data Tier (if time permits)

Lab 4: Performing Disconnected Operations Programmatically

• Creating a DataSet Programmatically
• Populating and Saving a DataSet
• Adding, Modifying, and Deleting Data in a DataSet
• Merging DataSets
• Creating and Using DataViews (if time permits)

Lab 5: Performing Disconnected Operations by Using Visual Studio 2005 Wizards

• Creating a Typed DataSet Using the DataSet Designer
• Loading, Displaying, and Saving Data in a Typed DataSet
• Adding Code to a Typed DataSet
• Creating a Typed DataSet Using the Data Source Configuration Wizard
• Adding Queries to a Table Adapter (if time permits)

Lab 6: Performing XML Operations on Disconnected Data

• Saving a DataSet as XML Data
• Loading a DataSet from XML
• Saving and Loading DataSet Schema Information

Lab 7: Reading and Writing XML Data

• Writing XML Data by Using XmlWriter
• Reading XML Data by Using XmlReader

Lab 8: Processing XML Data by Using DOM

• Reading XML Data Using DOM
• Writing XML Data Using DOM

Lab 9: Minimizing and Handling Database Operation Conflicts

• Reading Committed Data Using Locks
  Reading Committed Data Using Statement-Level Snapshots
  Reading Committed Data Using Transaction-Level Snapshots
  Handling Data Concurrency Using ADO.NET

Lab 10: Handling Large Objects

• Reading Large Values from SQL Server
• Writing Large Values to SQL Server
• Conserving Resources When Writing Large Values to SQL Server

Lab 11: Enhancing Database Performance

• Accessing Multiple Result Sets Concurrently
• Performing Asynchronous Data Access Operations
• Performing a Batch Update
• Performing a Bulk Data Cop

Lab 12: Creating Managed Code Objects for SQL Server 2005

• Creating Managed Stored Procedures and Triggers
• Creating Managed User-Defined Functions
• Creating a Managed Aggregate
• Creating a Managed User-Defined Type
• Importing Existing Assemblies into SQL Server 2005 (if time permits)

Lab 13: Querying XML by Using XPath

• Selecting XML Data Using XPathNavigator
• Evaluating XPath Expressions Using XPath Navigator
• Creating and Using Compiled XPath Expressions
• Editing XML Data Using XPathNavigator

Lab 14: Transforming XML by Using XSLT Style Sheets

• Transforming an XML Document Using an XSLT Style Sheet
• Resolving External Resources During XSLT Processing
• Passing Parameters into an XSLT Style Sheet
• Creating and Using Extension Objects

Audience:

Corporate and Independent Software Vendor (ISV) application developers who have a desire to learn more about specific technology areas in distributed application developments.

Objectives:

• Connect to databases and read data
• Query and update databases by using commands
• Perform transactional operations
• Perform disconnected operations programmatically
• Perform disconnected operations by using Visual Studio 2005 wizards
• Perform XML operations on disconnected data
• Read and write XML data
• Process XML data by using the Document Object Model (DOM)
• Minimize and handle database operation conflicts
• Handle large objects
• Enhance database performance
• Create managed code objects for SQL Server 2005
• Query XML by using XPath
• Transform XML by using XSLT style sheets