ADO .NET USING VISUAL BASIC 2005

Course Title: ADO .NET USING VISUAL BASIC 2005

Course #: ADO4I

Duration: 3 days

Overview:

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.

Topics:

Accessing Data with ADO.NET

• The ADO.NET Object Model
• ODBC
• DAO
• RDO and ODBCDirect
• ADO
• ADO.NET
• Connected and Disconnected Objects
• Connecting to Data
• Connection Strings
• The Using Block
• SQL Server Connections
• Microsoft Access Connections
• Understanding Connection Pooling
• Closing Connections
• Turning Off Connection Pooling
• Managing the Pool with SqlClient
• Using SQL Profiler to Monitor the Pool
• Using the Performance Monitor
• Using Provider Statistics

Retrieving Data

• Introducing Command Classes
• Creating Command Objects
• Command Object Methods
• Working with Data Readers
• Retrieving Results Faster
• Retrieving Multiple Result Sets
• Retrieving Information about DataReader Schema
• Using a Reader that Retrieves Data from a DataTable
• Digging Deeper into Commands and DataReaders
• Calling a Stored Procedure of Saved Query
• Passing Parameters
• Managing Multiple Namespaces with One Procedure
• Calling a Stored Procedure with an Output Parameter
• Binding DataReaders in ASP.NET
• Filling the GridView Contro
• Hooking Up the Data

Caching Data in DataSets

• DataSets and DataTables
• DataSets Are Disconnected
• DataTables
• Populating a DataTable
• Defining DataTable Columns
• Adding Rows with the NewRow Method
• Binding a DataGrid to a DataTable
• Filling DataSets Using a DataAdapter
• DataAdapter Command Objects
• Filling a DataTable
• Using the Load Method
• Handling Multiple Result Sets
• Retrieving Schema Information
• DataColumns and Schema
• DataSets and XML
• Reading and Writing XML Data

Applying Constraints

• Primary Keys and Using Constraints
• Merging DataSets
• Foreign Key Constraints
• Referential Integrity
• Column-Based Constraints
• AllowDBNull
• MaxLength
• Default Values

Data Relations

• Creating DataRelations
• Creating Relationships in SQL Server
• Relations in ADO.NET
• The DataRelation Object
• Navigation Pathways
• GetChildRows
• GetParentRow
• Expressions and Relations
• Aggregate Calculated Expressions
• Lookup Expressions
• Specifying XML Nesting

Updating Data

• Creating Commands with CommandBuilders
• Generating Commands
• Updating Data Using a Command Builder
• Creating Commands
• Specifying Parameters
• Updating with the DataAdapter
• Understanding DataRow Version and RowState
• Using Stored Procedures
• Advantages of Stored Procedures
• The Insert Stored Procedure
• The Delete Stored Procedure
• Adding New Customers with Stored Procedures
• Deleting Customers with Stored Procedures
• Performing Batch Updates
• Setting UpdateBatchSize

Using Strongly Typed DataSet Classes

• Using a Loosely Typed DataSet
• Creating a Strongly Typed DataSet
• Investigating the Class
• Use the Class View Window
• Using a Strongly Typed DataSet
• Working with an Untyped DataSet
• Working with a Typed DataSet
• Using a TableAdapter
• Finding a Row in a Typed DataSet
• Editing a Row in a Typed DataSet
• Adding a New Row to a Typed DataSet
• Working with Null Values in a Typed DataSet
• Navigating Relations in a Typed DataSet
• Creating New TableAdapter Queries
• Issues with Using a Strongly Typed DataSet
• Strongly Typed DataSet and Data Binding
• Comparing Typed and Untyped DataSet Efficiency
• Converting Between Typed and Untyped DataSet Instances
• Using Annotations to Control the Generation of a DataSet Class
• Should You Use a Typed DataSet?

Handling Exceptions and Data Errors

• Using Structured Exception Handling
• The Exception Base Class
• The Try/Catch block
• Unhandled Exceptions
• Using the Exception Object
• Handling Provider Errors
• Use SqlException
• Using InfoMessage
• Dealing with Errors and InfoMessages
• Using Transactions
• Implicit Transactions
• Explicit Transactions
• Transaction Isolation Levels
• The SqlTransaction Object
• Handling Concurrency Conflicts
• Optimistic Concurrency Issues
• Update Criteria
• Handling Data Updates with ADO.NET

Searching, Sorting, and Filtering

• The Sample Project
• Data in the Sample Project
• Displaying Results
• Working with DataTables
• Searching Primary Key Values
• Searching for Multiple Columns
• Simply Dynamic Searches
• Searching with Wildcards
• Managing Delimiters
• More Filtering Issues
• Working with DataViews
• Sorting Data Using a DataView
• Filtering Data
• Filtering on Row State
• Iterating through Rows
• Finding a Row
• Finding Multiple Rows
• Modifying Data in a DataView
• Creating a DataTable from a DataView

Handling AutoIncrement Columns

• The AutoIncrement Property
• AutoIncrement in a Typed DataSet
• Retrieving New Database Values
• Using Batched SQL Statements
• Using the RowUpdated Event
• Resetting the SQL Server identity Seed
• Avoiding Multi-User Conflicts
• Understanding the Problem
• Setting AutoIncrementSeed and AutoIncrementStep
• Merging Inserted Rows
• Understanding the Problem
• Inspecting the Code
• Solving the Merge Problem
• Other Considerations
• Handling the Parent/Child Inserts
• The Easy Way: Using GUIDs

XML Techniques

• ADO.NET and XML
• Getting Started Reading and Writing XML
• Working with XML Programmatically
• Saving DataSet Data as XML
• Using the GetXml Method
• Controlling DataSetXML Output Options
• Using the WriteXml Method
• Writing XML Output as a DiffGram
• Loading XML Data into a DataSet
• The Ground Rules
• Inferring a Schema
• Using Internal Schema
• Using External Schema
• Reading a DiffGram
• Loading a DataSet or DataTable from an XML String
• Working with DataSets and XML Together
• Using an XmlDataDocument
• Format XML Data with XSLT
• Query DataSet Data with XPath
• Working with XML Data in SQL Server
• Using XQuery to Query and Modify XML Data in SQL Server 2005

Supporting SQL Server 2005 Features

• Optimizing Performance
• Performing Asynchronous Processing
• Using Bulk Copy to Load Data Quickly
• Enhancing Concurrency with Snapshot Isolation
• Easing Application Development
• Listing Available SQL Servers
• Retrieving Multiple Active Result Sets (MARS)
• Working with max Data Types
• Using Query Notifications with ASP.NET Caching

Audience: Visual Basic .NET developers needing to communicate with datasources.

Prerequisites: SQL Programming, Fundamentals of XML and The .NET Framework Using Visual Basic. The ability to write simple Windows Forms applications and a working knowledge of SQL Server are recommended.