Integrate third party application data with finOps | include C# and X++
Usama Mehmood
MCP Bizz Apps | SCM | WMS | Finance | HRM | Do Centric Advanced Reporting | Integration & Business process specialist | Technical Lead - D365 F&O | Digital Transformation
In this article, I am going to explain the procedure to retrieve the data from external database
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 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!!