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](https://blog.magnetismsolutions.com/blog/paulnieuwelaar/2021/9/21/setting-up-an-application-user-in-dynamics-365)
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](https://learn.microsoft.com/en-us/azure/azure-sql/database/design-first-database-tutorial?view=azuresql)
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.
[Solid Principles](https://www.c-sharpcorner.com/UploadFile/damubetha/solid-principles-in-C-Sharp/), [Design Pattern](https://www.dofactory.com/net/design-patterns).
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!!!
