In today's business landscape, data integration has become increasingly essential to achieving a competitive edge. The ability to access data from disparate systems such as Dynamics 365 and Azure SQL is necessary to make informed decisions. In this article, we will discuss how to write a console application that gets data from Azure SQL and saves it into Dynamics CRM. We will also explore how to deploy the console app as a WebJob to run after every two hours. The data that we will be collecting is order records and related order items.
Step 1: Create the console application
The first step is to create a new console application project in Visual Studio. To do this, open Visual Studio and navigate to File > New > Project. Select Console Application from the list of available templates and give your project a name.
Step 2: Add the necessary references
To access Dynamics 365 and Azure SQL, we need to add the required references. Right-click on your project in the Solution Explorer and select Manage NuGet Packages. In the NuGet Package Manager window, search for and install the following packages:
Microsoft.CrmSdk.CoreAssemblies
Microsoft.CrmSdk.XrmTooling.CoreAssembly
Microsoft.Azure.Services.AppAuthentication
Microsoft.Azure.KeyVault
Step 3: Create an application account and app registration for Dynamics 365
To access Dynamics 365, you need an application user account, and you also need to register your dynamics 365 apps with the user account. This will require an article on its own, so I will share the link that contains step by step for setting up the account from Paul Nieuwelaar - Click here
Step 4: Create Azure SQL Database
If you don't have a database created already you can follow the link shared to do so. You can create your own tables instead of using order and order items which I have decided to use in this article - Click here
Step 5: Connect to Azure SQL
To connect to Azure SQL, we will use the SqlConnection class. In your console application, add the following code to connect to Azure SQL.
Create appseeting.json config file and add your conn string.
I created a DAL class and I am connecting to my Azure SQL within the class constructor.
Note: Make sure to replace the placeholders with your actual server’s name, database name, username, and password.
Step 6: Query the data from Azure SQL
Once you have established the connection to Azure SQL, you can retrieve the required data. In this example, we will be fetching order records and related order items. Note that I am using dapper to save my data into a list. Create an order model as well. The following code can be used to fetch the data:
Note: The SQL query should be adjusted based on your table and column names.
Step 7: Connect to Dynamics CRM
To connect to Dynamics CRM, we will use the CrmServiceClient class. Add your connection string in app. config or appsseting.json file. Add the following code to connect to Dynamics CRM:
Note: Make sure you have an app. config file set up to access Dynamics.
Step 8: Save the data to Dynamics CRM
Once you have fetched the data from Azure SQL and established the connection to Dynamics CRM, you can create records in Dynamics CRM. In this example, we will be creating orders and related order items in Dynamics CRM. The following code can be used to create records:
This code works perfectly if you have followed everything outlined from the beginning.
Step 9: Deploy as a web job
To deploy this as a WebJob, follow C# design patterns and solid principles. It will help you to write a clean readable and you can always add more functionality to it. You will need more than what you see on the screen for a client project.
Note: C# Design patterns and Principles can be found on the following links.
Step 10: Results
Once your WebJob is deployed and running you can check your logs.
Note. This is a sample code taken from the original code with more details. I hope this article helps someone in future to have an easy start when developing their first WebJob. Any advice or comment or suggestion will be taken into consideration. I am here to learn and share.
Happy Coding!!!
Published by
Samuel Mothiba
Dynamics 365 CE and Power Platform Consultant at First Technology Digital,
Samuel is a Dynamics 365 consultant in the Innovation & IoT team at First Technology Digital, a company that provides digital solutions and services to businesses. In his role he has been responsible for developing, implementing, and supporting Dynamics 365 solutions for various clients. His experience includes but not limited to Configuring and customizing Dynamics 365 modules, such as sales, customer service, and Marketing. He also worked on integrating Dynamics 365 with other Microsoft and Third party system using Power Automate, Azure Webjobs, Azure Logic apps and other custom dev(APIs). His experience extends to working with Power BI for data visualization and Power Apps for creating custom business applications. In addition to his technical expertise, he is a team player with excellent communication skills. He is also the most trusted advisor to his seniors in terms of Dynamics 365 modules offered by Microsoft. Overall his role is to help clients to make the most of their data capturing system and draw some insights regarding their customers to achieve their business goals