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

Map Custom Attributes from Opportunity Product to Quote Product to Order Product and so on in Microsoft Dynamics CRM Online 2011 and 4.0

The process of mapping custom attributes in Microsoft Dynamics CRM Online from opportunity product to quote product to order product to invoice product is very similiar in CRM Online as it is in on-premise CRM.  There is just one problem that needs to be overcome, and that is getting the GUID for the entitymap entities from the CRM backend to plug into the URL that is required to add custom attributes to the mappings.  

If you want to know how this works in on-premise using SQL management studio please review my older  blogpost at: http://mileyja.blogspot.com/2008_09_01_archive.html.

There are some issues that arise out of using CRM Online as you cannot access the SQL Server your instance resides on, this means you cannot easily get the GUIDs you need from the backend unless you use web services to retrieve them.  I know the metadata browser in 4.0 does not show the entitymap entity in the menu as entity you can drill down on, but believe me you can access this entity using a retrieve or retrievemultiple call using SOAP in jscript through the 2007 web services (which were also conveniently left in place for backward compatibility in CRM 2011).  The other issue is that there is a minor change to the URL you need to use to access the custom mapping as CRM Online follows the IFD url structure for where the orgname is located.

1. First, you will need this jscript that you can attach to any entity form event in CRM to get the guid when that event is executed in order to retrieve the unique entitymapid for your organization to map from salesorderdetail (Order Product) to invoicedetail (Invoice Product).  Since this only needs to be done once, you can just attach it to an event and use the preview form function to retrieve the GUIDs and then remove the jscript without ever publishing it.

First in 4.0 Online or On-Premise:

//create SOAP envelope
var xmlSoapHeader= "" +
"<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">";
var xmlAuthHeader = GenerateAuthenticationHeader();
var xmlSoapBody = "<soap:Body>" +
"<RetrieveMultiple xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\"> " +
"    <query xmlns:q1=\"http://schemas.microsoft.com/crm/2006/Query\" xsi:type=\"q1:QueryByAttribute\" xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
"      <q1:EntityName>entitymap</q1:EntityName>" +
"      <q1:ColumnSet xsi:type=\"q1:ColumnSet\">" +
"        <q1:Attributes>" +
"          <q1:Attribute>entitymapid</q1:Attribute>" +
"          <q1:Attribute>sourceentityname</q1:Attribute>" +
"          <q1:Attribute>targetentityname</q1:Attribute>" +
"        </q1:Attributes>" +
"      </q1:ColumnSet>" +
" <q1:Attributes>" +
" <q1:Attribute>sourceentityname</q1:Attribute>" +
" </q1:Attributes>" +
" <q1:Values>"+
" <q1:Value xsi:type=\"xsd:string\">salesorderdetail</q1:Value>" +
" </q1:Values>" +
"    </query>" +
" </RetrieveMultiple>" +
"  </soap:Body>" +
"</soap:Envelope>" +
"";

var xmlt = xmlSoapHeader + xmlAuthHeader + xmlSoapBody;
var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xmlHttpRequest.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlHttpRequest.setRequestHeader("Content-Length", xmlt.length);
xmlHttpRequest.send(xmlt);
var resultXml = xmlHttpRequest.responseXML.xml;
alert(resultXml);

Now in CRM 2011 Online or On-Premise you can use the same script, but it must be encapsulated in a jscript library as a function that you call from the event:

function testfunc()
{
//create SOAP envelope
var xmlSoapHeader= "" +
"<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">";
var xmlAuthHeader = GenerateAuthenticationHeader();
var xmlSoapBody = "<soap:Body>" +
"<RetrieveMultiple xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\"> " +
"    <query xmlns:q1=\"http://schemas.microsoft.com/crm/2006/Query\" xsi:type=\"q1:QueryByAttribute\" xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
"      <q1:EntityName>entitymap</q1:EntityName>" +
"      <q1:ColumnSet xsi:type=\"q1:ColumnSet\">" +
"        <q1:Attributes>" +
"          <q1:Attribute>entitymapid</q1:Attribute>" +
"          <q1:Attribute>sourceentityname</q1:Attribute>" +
"          <q1:Attribute>targetentityname</q1:Attribute>" +
"        </q1:Attributes>" +
"      </q1:ColumnSet>" +
" <q1:Attributes>" +
" <q1:Attribute>sourceentityname</q1:Attribute>" +
" </q1:Attributes>" +
" <q1:Values>"+
" <q1:Value xsi:type=\"xsd:string\">salesorderdetail</q1:Value>" +
" </q1:Values>" +
"    </query>" +
" </RetrieveMultiple>" +
"  </soap:Body>" +
"</soap:Envelope>" +
"";

var xmlt = xmlSoapHeader + xmlAuthHeader + xmlSoapBody;
var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xmlHttpRequest.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlHttpRequest.setRequestHeader("Content-Length", xmlt.length);
xmlHttpRequest.send(xmlt);
var resultXml = xmlHttpRequest.responseXML.xml;
alert(resultXml);
}

2. Now run the form event you attached the jscript to.

The alert message is a little bit difficult to look at when the script executes, but if you use fiddler to capture your sessions, it will show you the needed entitymapid GUID quite nicely:

3.  Now you can piece together the URL for the mapping interface for the custom attribute mapping.  The syntax to use is:

 https://myorgsubdomain.crm.dynamics.com/Tools/SystemCustomization/Relationships/Mappings/mappingList.aspx?mappingId=entitymapidGUID

Here is a syntax example using the guid above:

https://myspecialcrminstance.crm.dynamics.com/Tools/SystemCustomization/Relationships/Mappings/mappingList.aspx?mappingId=EB562CC6-991B-DF11-87A9-02BF0A0679DE

NOTE:  Since the GUIDs are unique to each organization of CRM, the GUID in the URL won't actually work except in my own personal organization.

4. Navigate to the URL using your browser and below is the resulting screen.  You should see a similiar mapping dialog when you click "New" on the screen.  I chose to show you the end result from 2011 in the following screenshot for the mapping screen, it looks only a bit different in 4.0 Online and functions the same way.

Thats all there is to it, just add you mappings as needed.  If you need to get the other GUIDs for the other detail (product) entities in the sales process chain you just change the entity types in the script and re-run it.


This post is provided as-is and implies no warranty, Jamie Miley does not assume any responsibility for problems arising from the use of this information.