Integrate Data from Dynamics 365 CRM Using ADO.NET

June 10 2020
0 Comments

The ComponentOne 2020v1 release introduces a new .NET Standard service library DataConnectors, which provides a set of connectors that can be used to connect to different types of data sources. Currently, you can connect to two data sources, namely OData and Microsoft Dynamics 365 Sales, use an ADO.NET provider for each type.

These providers have the same ADO.NET architecture as the native .NET data providers for SQL Server and OLEDB. Hence, they ease the complexity of accessing data by letting you follow a similar set of classes available in the .NET architecture. These providers also have advantageous features such as authentication, caching, and SQL/LINQ query support.

To add up to these features, DataConnector also includes an Entity Framework (EF) Core provider for each type of data source, which makes this library useful even when working with Entity Framework Core.

This article shows how to use the ADO.NET provider for Dynamics 365 Sales to connect to the Dynamic 365 Sales data. It will also explain the basic features, including authentication, querying, and caching.

To get started, you would need to install the C1.DataConnector and C1.AdoNet.D365S packages from Nuget:

You can download and install the ComponentOne DataConnectors service components here.

You can find the installed samples at the following location: 'C:\Users\xxxx\Documents\ComponentOne Samples\ServiceComponents\DataConnectors'.

Connecting the Dynamics 365 Sales Data

Let’s start by establishing a connection to the Dynamics 365 sales data. Similar, to the ADO.NET DataConnection class, the DataConnector service library provides you with the C1D365SConnection class, which is used to establish a connection to the data. You can pass the connection string as a parameter to the class constructor and create the connection object.

The connection string can either be predefined or it can be generated using the C1D365SConnectionStringBuilder class.

Here is a sample code snippet depicting how to generate a connection string and create a connection object:

//Build connection string using C1D365SConnectionStringBuilder
C1D365SConnectionStringBuilder builder = new C1D365SConnectionStringBuilder();
builder.Url = urlDynamics;
builder.Username = username;
builder.Password = password;            
builder.MaxPageSize = 100;
builder.UseCache = true;

//Creating Connection using connection string as parameter
var con = new C1D365SConnection(builder.ConnectionString);

Authentication

Moving on, we will discuss how the ADO.NET provider for Dynamics 365 Sales data supports OpenAuth-based authentication to help you access your data using a secure connection. OpenAuth is an open-standard authorization framework or protocol that describes how independent services and servers can allow authentic access to their assets safely, without sharing the initial, related, and single login credentials. The ADO.NET provider Dynamics 365 Sales lets you implement authentication by setting the values for OAuthScope, OAuthAccessToken, OAuthRefreshToken, OAuthClientSecret, OAuthClientId, Username and Password.

You can set either the password credentials or client credentials to establish a secure connection. Based on the provided set of values, the library would automatically generate and consume the AccessToken/RefereshToken to maintain a secure connection.

Here is a sample code depicting the use of client credentials:


//Set valid values here
const string urlDynamics = @"https://xxx.xxx.xxx.xxx.com/api/data/v9.1/";
const string clientID = "";
const string clientSecret = "";
const string tokenEnpoint = @"https://login.microsoftonline.com/common/oauth2/token";
const string extendProperties = @"{""resource"":""https://xxx.xxx.xxx.com/""}";

//Build connection string using C1D365SConnectionStringBuilder
C1D365SConnectionStringBuilder builder = new C1D365SConnectionStringBuilder();
//Client credential in connection string
builder.Url = urlDynamics;
builder.OAuthClientId = clientID;
builder.OAuthClientSecret = clientSecret;
builder.OAuthTokenEndpoint = tokenEnpoint;
builder.OAuthExtendProperties = extendProperties;

//Creating Connection using connection string as parameter
var con = new C1D365SConnection(builder.ConnectionString);

For details, refer to the detailed documentation topic - OAuth Authorization.

Querying

After establishing a secure connection, you can query against the data source using standard SQL syntax as the provider supports most SQL functionality including join queries, functions, and summaries. The basic CRUD operations such as insertion, updating, and deleting data, can be performed either in the connected mode or disconnected mode by using the C1D365SCommand and C1D365SDataAdapter objects.

The sample code below depicts the basic CRUD operations being performed using the C1D365SDataAdapter object:

---------------------------------------------------------------------------------------------------------------------------------------------
//READ QUERY
using (C1D365SConnection conn = new C1D365SConnection(connstr))
{
  conn.Open();
  C1D365SDataAdapter adapter = new C1D365SDataAdapter(conn, "Select  accountid, name  FROM Accounts where name ='Updated_Name'");
  DataTable dataTable = new DataTable();
  adapter.Fill(dataTable);            
}
---------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------------
//INSERT QUERY
using (C1D365SConnection conn = new C1D365SConnection(connstr))
{               
  conn.Open();

  //Populate Datatable
  C1D365SDataAdapter adapter = new C1D365SDataAdapter(conn, "Select * from accounts limit 10");
  DataTable dataTable = new DataTable();
  adapter.Fill(dataTable);//Data Table fill

  //Create Insert Command
  adapter.InsertCommand = new C1D365SCommand(conn);
  adapter.InsertCommand.CommandText = "Insert INTO accounts (accountid, name) VALUES (@accountid,@name)";
  adapter.InsertCommand.Parameters.Add(new C1DbParameter("@accountid", DbType.Guid, "accountid"));//adding Parameters
  adapter.InsertCommand.Parameters.Add(new C1DbParameter("@name", DbType.String, "name"));
  adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

  //Perform Insert Operation
  DataRow bookRow1 = dataTable.NewRow();
  bookRow1["accountid"] = Guid.NewGuid();//Datatable Rows Updated for Insertion
  bookRow1["name"] = "Rajesh";
  dataTable.Rows.Add(bookRow1);

  //Update database
  adapter.Update(dataTable);
}
---------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------------
//UPDATE QUERY
using (C1D365SConnection conn = new C1D365SConnection(connstr))
{               
  conn.Open();

  //Populate Datatable
  C1D365SDataAdapter adapter = new C1D365SDataAdapter(conn, "Select * from accounts limit 100");
  DataTable dataTable = new DataTable();
  adapter.Fill(dataTable);

  //Create Update Command
  adapter.UpdateCommand = new C1D365SCommand(conn);
  adapter.UpdateCommand = new C1D365SCommand(conn, "UPDATE accounts SET name = @upname " + "WHERE name = @name");
  adapter.UpdateCommand.Parameters.Add(new C1DbParameter("@name", DbType.String, "name"));
  adapter.UpdateCommand.Parameters.Add(new C1DbParameter("@upname", DbType.String, "name"));//Adding Parameters
  adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

  //Perform Update Operation
  DataRow bookRow1 = dataTable.Rows[0];
  bookRow1["name"] = "Rajesh";

  //Update database
  adapter.Update(dataTable);
}
---------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------------
//DELETE QUERY
using (C1D365SConnection conn = new C1D365SConnection(connstr))
{               
  conn.Open();

  //Populate Datatable
  C1D365SDataAdapter adapter = new C1D365SDataAdapter(conn, "Select * from accounts limit 10");
  DataTable dataTable = new DataTable();
  adapter.Fill(dataTable);

  //Create Delete Command
  adapter.DeleteCommand = new C1D365SCommand(conn);
  adapter.DeleteCommand.CommandText = "Delete From accounts where name = @name";
  adapter.DeleteCommand.Parameters.Add(new C1DbParameter("@name", DbType.String, "name"));
  adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

  //Perform Delete Operation
  DataRow bookRow1 = dataTable.Rows[3];
  bookRow1["name"] = "Rajesh";
  bookRow1.Delete();

  //Update database
  adapter.Update(dataTable);
}
---------------------------------------------------------------------------------------------------------------------------------------------

Batch Update

The ADO.NET provider for Dynamics 365 Sales supports batch processing and hence lets you group INSERT, UPDATE, and DELETE operations from a DataSet or DataTable. The batch updates prevent multiple requests to the server and enhance the overall performance of data access and data update operation.

Further, you would need to set the UpdateBatchSize property to an appropriate value, to make sure that the commands are grouped in the batches of the specified size and sent to the server for processing.

Here is a sample code snippet depicting how to set the UpdateBatchSize property and perform batch update:

using (C1D365SConnection conn = new C1D365SConnection(connstr))
{
   conn.Open();

   //Populate Datatable
   C1D365SDataAdapter adapter = new C1D365SDataAdapter(conn, "Select * from accounts limit 10");
   DataTable dataTable = new DataTable();
   adapter.Fill(dataTable);

   //Create Update command
   adapter.UpdateCommand = new C1D365SCommand(conn, "UPDATE accounts SET name = @upname " + "WHERE accountid = @accountid");
   adapter.UpdateCommand.Parameters.Add(new C1DbParameter("@upname", DbType.String, "name"));
   adapter.UpdateCommand.Parameters.Add(new C1DbParameter("@accountid", DbType.Guid, "accountid"));
   adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

   //Perform Update operation
   DataRow bookRow1 = dataTable.Rows[0];
   bookRow1["name"] = "Julia";

   DataRow bookRow2 = dataTable.Rows[6];
   bookRow2["name"] = "Nick";

   //Update database
   adapter.UpdateBatchSize = 2;
   adapter.Update(dataTable);
 }

Built-in Caching

The DataConnector library provides built-in caching support, which enhances the performance by accessing the data from the cache when the user performs similar operations repeatedly. To enable the cache, we need to configure the connection object to set the cache properties. You must set the UseCache property to true, customize the default cache location by setting the CacheLocation property, and then use the CacheTolerance property to set the cache tolerance time in seconds, with the default value set to 600 seconds.

Here is a sample code snippet that depicts how to enable caching by setting all the cache properties in the connection object:

//Configure connection with cache settings
C1D365SConnectionStringBuilder d365builder = new C1D365SConnectionStringBuilder();
d365builder.Url = urlDynamics;
d365builder.Pooling = true;
d365builder.UseCache = true;
d365builder.CacheTolerance = 6;
d365builder.CacheLocation = @"C:\temp\c1cache.db";
d365builder.OAuthClientId = clientID;
d365builder.Username = username;
d365builder.Password = password;
d365builder.OAuthTokenEndpoint = tokenEnpoint;
d365builder.OAuthExtendProperties = extendProperties;
using (C1D365SConnection connection = new C1D365SConnection(d365builder.ConnectionString))
{
  Console.WriteLine("Start Time " + DateTime.Now);
  connection.Open();
  C1D365SCommand cmd = new C1D365SCommand(connection,"SELECT accountid, Name FROM Accounts Limit 12'");
  var rdr = cmd.ExecuteReader();
  while (rdr.Read())
  {
     Console.WriteLine("Read and cached the row with AccountId " + rdr["AccountId"]);
  }
}

The steps above demonstrate how easy it is to connect to a new type of data source if you have a common interface based on established and known data access technology such as ADO.NET.

For more detailed information, refer to the DataConnector documentation.

Download Now!

Manpreet Kaur

Senior Software Engineer