70-561 ADO.NET 3.5 Objectives with Useful Links

Hello, I am a fan of  Niall Merrigan and also of certification training, and since he has moved on to covering the .NET 4.0 and new SharePoint exams I thought I would put out an article in the same style for the 70-561- Microsoft .NET Framework 3.5, ADO.NET Application Development ,which he evidently hasn’t covered.

Here goes!

Please note that this is in no way a complete guide and understand that you use this guide at your own risk.

Connecting to Data Sources (12 percent)

·        Manage connection strings. May include but is not limited to: using the ConnectionStringBuilder; leveraging the ConfigurationManager; protecting the connection string; using Security Support Provider Interface (SSPI) or SQL Server authentication; correctly addressing the SQL Server instance; managing “User Instance” and AttachDBfilename

·        User Instances in SQL Express

·        Connectionstrings (general)

·        Connection String Syntax

·        SqlConnectionStringBuilder

·        ConfigurationManager Class

·        Manage connection objects. May include but is not limited to: managing connection state, managing connection pool; implementing persistent data connections; implementing Multiple Active Result Sets (MARS); encrypting and decrypting data

·        MARS (MULTIPLE ACTIVE RESULT SETS)

·        MARS – Multiple Active Result Sets

·        SQL Server Connection Pooling

·        Understanding Connection Pooling

·        Connection String Syntax including encryption

·        ProtectedDataSections

·        Work with data providers. May include but is not limited to: limitations, behaviors, performance, installation issues, deployment issues; ODBC, Microsoft OLE DB, SqlClient, managed providers, third-party providers, native providers

• .Net Framework Data Providers

·        System.Data.OracleClient Namespace

·        Oracle and ADO.NET

·        System.Data.OleDb

·        System.Data.Odbc

·        Connect to a data source by using a generic data access interface. May include but is not limited to: System.Data.Common namespace classes

·        DbProviderFactory

• System.Data.Common

·        Handle and diagnose database connection exceptions. May include but is not limited to: implementing try/catch handlers

• SqlConnection.Open

·        SqlException

·        SqlConnection.InfoMessage Event

Selecting and Querying Data (22 percent)

·        Build command objects. May include but is not limited to: building Parameters collections; using input and output parameters and return values; selecting an appropriate CommandType action; selecting an appropriate Execute method; using the CommandBuilder class; choosing appropriate CommandBehavior

·        SqlParameter

·        SqlCommand.CommandType

·        SqlCommand.CommandType.StoredProcedure

·        SqlCommand

·        SqlCommand.Parameters

·        CommandBuilder

·        CommandBehavior

·        Query data from data sources. May include but is not limited to: writing queries to solve assigned problems; implementing data paging; sorting, filtering, and aggregating data

• ExecuteScalar

·        T-SQL Tutorial

·        Data Paging

·        Sorting and Filtering Data

·        Retrieve data source data by using the DataReader. May include but is not limited to: retrieving data from SELECT statements; retrieving data from stored procedures; working with multiple result sets

·        IDataReader Interface

·        DataSet.CreateDataReader

·        Datareader

·        Data binding

·        DataTable

·        Gridview

·        DataReader.NextResult()

·        SqlBulkCopy

·        How To: Excute a Stored Procedure that Returns Rows

·        Manage data by using the DataAdapter or the TableAdapter. May include but is not limited to: retrieving data from SELECT statements; retrieving data from stored procedures; working with multiple result sets; working with JOIN products; creating updatable DataAdapters and TableAdapters; modifying TableAdapter Fill methods

·        SqlCommandBuilder

·        TableAdapter Overview

·        TableAdapters

·        SqlDataAdapter

·        SqlDataAdapter.Fill()

·        Execute an asynchronous query. May include but is not limited to: handling asynchronous events

·        SqlCommand.BeginExecuteNonQuery

·        AsyncCallback Delegate

·        IAsyncResult Interface

·        Handle special data types. May include but is not limited to: Binary Large Objects (BLOBs); Filestream, Spatial, Table Value Parameters

·        DataSet.WriteXml

·        XmlWriteMode

·        XSD.exe tool

·        SqlCommand

·        Reading and Writing Blobs

·        XmlReader

·        XmlReader.ReadElementContentAsString

·        Loading and Updating Using CLR UDT (User Defined Type)

·        Usage of CLR UDT With DataAdapters

   

·        Query data sources by using LINQ. May include but is not limited to: extended method; CLR delegate; lambda expressions; classes of the System.LINQ.Expressions namespace

·        LINQ

·        LINQ Examples

·        LINQ Joins

·        System.LINQ.Expressions

·        DataRowExtensions.Field<T>

·        Enumerable.SelectMany

·        Enumerable Methods

·        Manage exceptions when selecting data. May include but is not limited to: identifying and resolving syntax errors; security exceptions

·        SqlException

·        SqlError

·        SqlError.Class Property

·        SqlError.Class Property

Modifying Data (24 percent)

·        Manage transactions. May include but is not limited to: selecting an appropriate transaction isolation level; participating in local transactions; participating in distributed transactions; using declarative transactions

·        IEnlistmentNotification Interface

·        TransactionScope Object

·        Set Transaction Isolation Level

·        Manage data integrity. May include but is not limited to: cascading updates or cascading deletes; auto number management; server-generated value management; client-side data validation; business rules, constraints

·        DataTable.Constraints

·        ForeignKeyConstraint

·         DataTable.RowChanging

·        DataRowChangeEventArgs

·        DataViewRowState

·        DataRowVersion

·        Row States and Row Versions

·        UniqueConstraint

·        DataTable

·        MissingSchemaAction

·        ForeignKeyConstraint

·        DataTable.Load

·        LoadOption.Upsert

·        Update data. May include but is not limited to: update data by using stored procedures; update datasets to data source; managing concurrency

• Updating Data Sources with DataAdapters

·        SqlDataAdapter

·        DataAdapter.TableMappings

·        SqlDataAdapter.Fill()

·        DataColumnChangeEventArgs

·        Introduction to DataConcurrency in ADO.NET

·        Handling Data Concurrency Using ADO.NET

·        Manage exceptions when modifying data. May include but is not limited to: resolve exceptions from data integrity, constraint, or referential integrity violations; data locking conflicts; data collision issues

• Implementing an Implicit Transaction Using Transaction Scope

·        SqlException

·        SqlError

·        Optimistic Concurrency

·        SqlRowUpdatedEventArgs

·        UpdateStatus

·        Transform data by using LINQ. May include but is not limited to: aggregation operators; JOIN operator, QUERY operator, LIKE, etc.

·        Data Transformations with LINQ

·        101 LINQ Samples

Synchronizing Data (15 percent)

·        Monitor event notifications. May include but is not limited to: implement notifications by using SqlDependency or SqlNotificationRequest

·        Asychronous Operations

·        SqlDependancy

o   Examples

·        SqlDependency,

·        SqlDependency 2

·        Cache data. May include but is not limited to: SqlCacheDependency, Local Data Cache

·        SqlDependency, SqlDependency 2

• QueryNotifications
• Local DataBase Cache

·        Manage update conflicts between online data and offline data.

• Handle Data Conflicts and Errors

·        Partition data for synchronization.

• How to: Filter Rows and Columns

·        SqlSyncAdapterBuilder

·        Implement Synchronization Services. May include but is not limited to: SyncAgent, SyncTable, ClientSyncProvider, ServerSyncProvider, SyncAdapter

• Architecture and Classes for Client and Server Synchronization

·        Microsoft.Synchronization.Data namespace

·        ServerSyncProvider Proxy

·        DbServerSyncProvider

·        SyncAnchor

·        SyncAdapter

·        SyncConflictResolver

·        SqlCeClientSyncProvider

·        SyncTable

·        SyncGroup

·        SyncAdapter

·        DbServerSyncProvider

·        DBServerSyncProvider.BatchSize

·        SelectNewAnchorCommand

Working with Disconnected Data (16 percent)

·        Manage occasionally connected data.

·        Microsoft.Synchronization.Data namespace

·        ServerSyncProvider Proxy

·        DbServerSyncProvider

·        SyncAnchor

·        SyncAdapter

·        SyncConflictResolver

·        SqlCeClientSyncProvider

·        SyncTable

·        SyncGroup

·        SyncAdapter

·        DbServerSyncProvider

·        DBServerSyncProvider.BatchSize

·        SelectNewAnchorCommand

·        Programmatically create data objects. May include but is not limited to: DataTable; DataSet

• Dataset
• DataTable
• DataRow

·        Work with untyped DataSets and DataTables. May include but is not limited to: populating a DataSet or DataTable

• Merging DataSet Contents

·        Database Relationships and ADO.NET

·        DataRelation

·        SqlDataAdapter

·        DataAdapter.TableMappings

·        DataRelation

·        DataTable.Merge

·        DataColumn

·        DataRow

·        DataRow.Add

·        Datarow.delete

·        Expose a DataTableReader from a DataTable or from a DataSet.

• DataTableReaders

·        Work with strongly typed DataSets and DataTables.

·        DataColumn.Expression

·        DataColumn

·        Preparing Your Application to Receive Data

·        BindingSource.EndEdit

·        TableAdapterManager

·        BindingSource

·        Dataset.CreateDataReader

·        TableAdapter

·        DataTableMapping

Object Relational Mapping by Using the Entity Framework (11 percent)

·        Define and implement an Entity Data Model. May include but is not limited to: mapping schemas and storage metadata; EDM Generator

·        Entity Framework

o   Samples

·        EDM

o   SSDL

o   CSDL

§  ComplexType

§  EntityTypeMapping

·        Storage Schema Definition File (SSDL)

·        Conceptual Schema Definition File (CSDL)

·        CSDL, SSDL, and MSL Specifications

·        Edmgen.exe

·        Query data by using Object Services. May include but is not limited to: querying data as objects and shaping results; working with objects; managing the Object Context; customizing objects

• Object Services
• Customizing Objects

·        Map data by using the Entity SQL Language. May include but is not limited to: manually define mapping files; execute an Entity SQL Query by using EntityCommand

·        Entity Sql Reference        

·        Entity SQL Overview       

·        CSDL Association Element

·        Querying edm schema

·        EntityCommand

·        Access entity data by using the EntityClient Provider. May include but is not limited to: managing EntityConnection; creating EntityCommand; executing a query by using EntityDataReader; managing EntityTransaction

• How To Manually Open the Connection from the Object Context
• Managing Connections in Object Services