Integrate third party application data with finOps | include C# and X++
Usama Mehmood
Senior Technical Consultant ERP / Microsoft Dynamics 365 Finance & Operations | Integration & Customization specialist | Enabler of Business | Digital Transformation
In this article, I am going to explain the procedure to retrieve the data from external database and store that specific data in finOps. Mostly, we have seen requirement's where we need to fetch the data from third party application. Some time's we have gained the restfull Api's of that data base, but some time's didn't have Api's of that record.
In this article, I will explain the process of middleware, which we develop to get the record's from external database. It comprises C#, .net libraries & X++ code. Mainly in C# we have performed following database operations using system.data.SqlClient. The operations which we have performed are INSERT, UPDATE, SELECT and DELETE record from external database.
To get the record's in finOps, we need to Implement the custom web service in Finance and Operation's which fetch the record's from External C# Service and store record's in Finops table's. We need to deploy the custom C# service on environment from where we need to fetch the record, the service get's the record from external Db and call our service and pass data from External service to D365 Service.
How to build connection with external database using C# | Source code
using System;
using System.Data.SqlClient;
namespace Database_Operation {
class DBConn {
????// Main Method
????static void Main()
????{
????????Connect();
????????Console.ReadKey();
????}
????static void Connect()
????{
????????string constr;????????// for the connection to sql server database
????????SqlConnection conn;
????????// Data Source is the name of the
????????// server on which the database is stored.
????????// The Initial Catalog is used to specify
????????// the name of the database
????????// The UserID and Password are the credentials
????????// required to connect to the database.
????????constr = @"Data Source=DESKTOP-GP8F496;Initial Catalog=Demodb;User ID=sa;Password=24518300";
????????conn = new SqlConnection(constr);
????????// to open the connection
????????conn.Open();
????????Console.WriteLine("Connection Open!");
????????// to close the connection
????????conn.Close();
????}
}
}
How to select the record's from external database using C# in class library project | Source Code
using System;
using System.Data.SqlClient;
namespace Database_Operation {
class SelectRecordfromExtDB{
????static void Main()
????{
????????Read();
????????Console.ReadKey();
????}
????static void Read()
????{
????????string constr;
????????SqlConnection conn;
????????constr = @"Data Source=DESKTOP-GP8F496;Initial Catalog=Demodb;User ID=sa;Password=24518300";
????????conn = new SqlConnection(constr);
????????conn.Open();
????????SqlCommand cmd;
????????SqlDataReader dreader;
????????string sql, output = "";
?
?????????// use to fetch rows from demo table
????????sql = "Select articleID, articleName from demo";
?
????????// to execute the sql statement
????????cmd = new SqlCommand(sql, conn);
?
????????// fetch all the rows
????????dreader = cmd.ExecuteReader();
?
????????// for one by one reading row
????????while (dreader.Read()) {
????????????output = output + dreader.GetValue(0) + " - " +
????????????????????????????????dreader.GetValue(1) + "\n";
????????}
????????// to display the output
????????Console.Write(output);
????????// to close all the objects
????????dreader.Close();
????????cmd.Dispose();
????????conn.Close();
????}
}
How to Insert record's in external database using C# .net Libraries | Source Code
using System;
using System.Data.SqlClient;
namespace Database_Operation {
class InsertRecordExtDB {
????static void Main()
领英推荐
????{
????????Insert();
????????Console.ReadKey();
????}
????statc void Insert()
????{
?????????string constr;
????????SqlConnection conn;
????????constr = @"Data Source=DESKTOP-GP8F496;Initial Catalog=Demodb;User ID=sa;Password=24518300";
??
????????conn = new SqlConnection(constr);
????????conn.Open();
????????SqlCommand cmd;
?????????
????????// data adapter object is use to
????????// insert, update or delete commands
????????SqlDataAdapter adap = new SqlDataAdapter();
?????
????????string sql = "";
??????
????????// use the defined sql statement
????????// against our database
????????sql = "insert into demo values(3, 'Python')";
????
????????// use to execute the sql command so we
????????// are passing query and connection object
????????cmd = new SqlCommand(sql, conn);
?????????
????????// associate the insert SQL
????????// command to adapter object
????????adap.InsertCommand = new SqlCommand(sql, conn);
?????????
????????// use to execute the DML statement against
????????// our database
????????adap.InsertCommand.ExecuteNonQuery();
????????// closing all the objects
????????cmd.Dispose();
????????conn.Close();
????}
}
}
Custom Web service in X++ | Source Code
Web services are basically Web APIs that are used to GET or POST data in D365 FO. There are some standard web services included in FO but users can create their custom web service to utilize it in a third-party application.
According to requirement's we need to create the contract class:
}
Data Provider Class:
Now Add a service by right click on the project and select add item to folder, in the service section select service, name the service and click add button.
Open the service properties.
Enter name of the provider class “BasicServiceProvider” also enter same name in external name otherwise it would give an error when we call the service.
Now right click on service and add service operation this is basically the method which we need to execute according to our need.
Now add a service group, right click the service group and add our service in the service group
Now build the project.
Open postman and in the URL tab type your resource/api/services/your service group/your service/your method.
How to Authenticate Post Man.
Go to Azure.Portal.com -> app registrations register your
Then go to Clients and secrets copy client secret value.
Then go to Postman Add URL
https://login.microsoftonline.com/Coomon/oauth2/token ?Replace common with Directory (tenant) ID
Then create Key in Post Man
grant_type????= ?client_credentials (as is)
?client_id ??????= ?Application (client) ID ) (from azure portal)
client_secret = ?secret Value (from azure portal)
resource??????= URL of D365 FO
Then Add URL Web Service Link in Post Man go to Authorization select bearer Token and paste access token that was get by authentication.
Click on body button and select raw and in the editor select and write Jason to send salesID as parameter. Here sales order is the name of the object which we are getting in provider class as a parameter.
Happy Learning!!