The ADO.NET provider for Dynamics 365 Sales provides a wide range of features that enable connectivity to Dynamics 365 Sales from .Net applications. The documentation will help you understand the C1.AdoNet.D365S namespace, which includes all the available classes that can be used to connect and retrieve data from a Dynamics 365 Sales service.

DataConnectors are mostly used in combination with other ComponentOne components, such as DataEngine and FlexPivot. For a better understanding of this application, please see Dynamics 365 Sales Sample live demo. The procedure below describes how to use the DataConnector in a console application within Visual Studio.

How to create a new Console Application

The ADO.NET provider for D365S can be used in any application. In this guide, a console application is created:

1. Open Visual Studio.
2. Select Create a new project from the Get Started pane.
3. In the Create a new project window, select Console Application and click Next, as in the screenshot below:
4. In the Configure your new project window, write your project name, choose a location to save your project, and click Create.

How to add the NuGet packages

To use the ADO.NET provider for D365S in an application, the respective NuGet package should be added:

1. From the Project menu, select Manage NuGet Packages.        
2. In the NuGet Package Manager, click the Package source drop-down and select nuget.org
3. In the left pane of the Browse tab, select C1.AdoNet.D365S
4. In the right pane of the Browse tab, click Install to add the reference to the package.

How to use ADO.Net provider for D365S to retrieve data

Quick example

C1D365SConnection implements the ADO.NET DbConnection similar to the standard ADO.NET connection object. Thus retrieving data can be as easy as the following few lines, which print some data from the Northwind data service, by using C1D365SDataAdapter to retrieve a single result set of all the data that matches a query (for other connection options, click here).

static void CountTableRows()
{
   var adapter = new C1.AdoNet.D365S.C1D365SDataAdapter(
       connectionString: "Url = {url};" +
                       "OAuth Client Id = {clientId};" +
                       "OAuth Client Secret = {clientSecret};" +
                       "OAuth Token Endpoint = {tokenEnpoint};" +
                       "OAuth Extend Properties = {extendProperties}",
       commandText: "SELECT * FROM accounts LIMIT 10");

   var table = new System.Data.DataTable();
   adapter.Fill(table);

   Console.WriteLine("Fetched {0} rows.", table.Rows.Count);
}

The above code is only for quick script-like usage. Real applications require more structured approaches. However, the high-level steps are similar:

1. Create a connector.
2. Use the connector to fill an in-memory table.
3. Render the table's contents.

Detailed structured example

This example expands the above steps into reusable methods within a class.

1. Prepare the general structure of the code:

   C#	Copy Code
   using C1.AdoNet.D365S; using System.Data; using System.Text; public static class D365SGettingStarted { public static void Run() { // Specify a data source and what data to fetch from there // Prepare a new adapter for the specified source and reuse it to fetch the specified data // Print the fetched data } }

2. Add inside the class the following method, which prepares the needed C1 AdoNet D365S objects:

   C#	Copy Code
   // Returns a new adapter ready to fetch data served at the passed connection string static C1D365SDataAdapter MakeAdapterForConString(string conString) { var connection = new C1D365SConnection(conString); var command = new C1D365SCommand(connection); return new C1D365SDataAdapter(command); }

3. Add inside the class the following extension method, which fills a table with the data:

   C#	Copy Code
   // Fetches the data for the past SELECT statement and returns it in a new datatable static DataTable FetchTableForSql(this C1D365SDataAdapter adapter, string sql) { adapter.SelectCommand.CommandText = sql; var table = new DataTable(); adapter.Fill(table); return table; }

4. Add inside the class the following extension method, which appends the table to a string builder:

   C#	Copy Code
   // Appends to the builder the printing of the passed datatable's rows static StringBuilder AppendRowsOfTable(this StringBuilder builder, DataTable table) { foreach (DataRow row in table.Rows) { builder.AppendJoin(" - ", row.ItemArray).AppendLine(); } return builder.AppendLine(); // Return self for method chaining }

5. Call the above methods in order, by filling in the entry method Run with the following code:

   C#

   Copy Code

   // Specify a data source and what data to fetch from there string crmConString = "Url = {url};" + "OAuth Client Id = {clientId};" + "OAuth Client Secret = {clientSecret};" + "OAuth Token Endpoint = {tokenEnpoint};" + "OAuth Extend Properties = {extendProperties}"; string revenueSql = "SELECT accountid, openrevenue FROM accounts WHERE (openrevenue > 500000)"; string versionSql = "SELECT accountid, name, versionnumber FROM accounts WHERE (versionnumber > 132000000)"; // Prepare a new adapter for the specified source and reuse it to fetch the specified data using C1D365SDataAdapter adapter = MakeAdapterForConString(crmConString); using DataTable revenueTable = adapter.FetchTableForSql(revenueSql); using DataTable versionTable = adapter.FetchTableForSql(versionSql); // Print the fetched data var builder = new StringBuilder(); builder.AppendLine() .AppendLine("Printing for revenue:") .AppendRowsOfTable(revenueTable) .AppendLine("Printing for version:") .AppendRowsOfTable(versionTable) .AppendLine("End of printing."); Console.Write(builder);

Running the console application should produce an output like this one: