Small and medium business | Business Central, N...

Cannot insert a record using ODATA from C#

(0) Share

Report

Posted on by Clemens Linders

LS,

We have D365BC and created a table and card page Workers (simply just: First name, Last name, Job descr.).

In C# I use the following code:

        static async Task<Uri> CreateWorkerAsync(WorkersWS _worker)
        {
            string _url = "">api.businesscentral.dynamics.com/.../WorkersWebService";
string _userName = "UserName";
            string _wsKey = "Password";
            byte[] _authenticationParameter = Encoding.UTF8.GetBytes(_userName + ":" + _wsKey);
            Http_client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic", Convert.ToBase64String(_authenticationParameter));
            HttpResponseMessage response = await Http_client.PostAsJsonAsync(_url, _worker);

            try
            {
                response.EnsureSuccessStatusCode();
            }
            catch (Exception ex)
            {

}

return response.Headers.Location;
}

The error happens in the try/catch block (Error 400 bad request).

The url is copied from D365BC.

If I delete a record using the same URL, there is no problem.

Reading from the ODATA web service using the same WorkersWS class is no problem.

I there is someone with experience in C# that can tell me what it is that I do wrong.

Kind regards,

Clemens Linders

I have the same question (0)

All responses (17)

Answers (0)

Suggested answer

Andy Sather on at

Like (0)

Report

Hello - We currently do not have dedicated Dev support via the Dynamics 365 Business Central forums, but I wanted to provide you some additional resources to assist. If you need assistance with debugging or coding I would recommend discussing this on one of our communities.

www.yammer.com/dynamicsnavdev

dynamicsuser.net/.../developers

I will open this up to the community in case they have something to add.

Was this reply helpful? Yes No
Suggested answer

keoma 32,729 on at

Like (0)

Report

creating a record is different. successful creation depends on correct usage of primary key, using the correct data types for the values, the correct sequence of the values, if a field is set to readonly, if a field has a tablerelation and many more. so your concrete creation c# code and the complete table structure is needed for valuation.

Was this reply helpful? Yes No
Clemens Linders 70 on at

Like (0)

Report

Hi Franz,

Here is my complete C# code:

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.IO;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace OData_Test_comm
{
    public partial class Form1 : Form
    {
        //Added Nuget: Install-Package Newtonsoft.Json -Version 12.0.3
        //Added Nuget: Install-Package Microsoft.AspNet.WebApi.Client -Version 5.2.7

        public List<WorkersClass> WorkersReadFromAlWebService = new List<WorkersClass>();
        static HttpClient Http_client = new HttpClient();

        public class WorkersClass
        {
            public string E_Tag = string.Empty;     // Unique key
            public string No = string.Empty;        // In AL No. is renamed to No during serialisation
            public string First_name = string.Empty;// In AL First Name is renamed to First_Name during serialisation ====> used by accident First name (small n) in D365BC!!
            public string Last_Name = string.Empty;
            public string FunctionName = string.Empty;
        }

        public class WorkersWS
        {//For creation I tried passing this record or above, makes no difference, but as there is no E-tag when you create the record, I tried this.
            public string No = string.Empty;        // In AL No. is renamed to No during serialisation
            public string First_name = string.Empty;// In AL First Name is renamed to First_Name during serialisation ====> used by accident First name (small n) in D365BC!!
            public string Last_Name = string.Empty;
            public string FunctionName = string.Empty;
        }

        public Form1()
        {
            InitializeComponent();
        }

        private void btnFindWorkerOdata_Click(object sender, EventArgs e)
        {//WORKS OK
            listBox1.Items.Clear();
            WorkersReadFromAlWebService = new List<WorkersClass>();
            string _url = "">api.businesscentral.dynamics.com/…/CardPage
HttpWebRequest _request = (HttpWebRequest)WebRequest.Create(_url);
            _request.ContentType = "application/json; charset=utf-8";
            _request.Headers["Authorization"] = "Basic " + Convert.ToBase64String(Encoding.GetEncoding("ISO-8859-1").GetBytes("UserName:Password"));
            _request.PreAuthenticate = true;
            HttpWebResponse _response = _request.GetResponse() as HttpWebResponse;
            using (Stream _responseStream = _response.GetResponseStream())
            {
                var x = _response.Headers.AllKeys;
                StreamReader _reader = new StreamReader(_responseStream, Encoding.UTF8);
                string _content = _reader.ReadToEnd();

                string _jasonPart = GetJsonPartMultiRecord(_content);

                List<WorkersClass> _jWorkers = JsonConvert.DeserializeObject<List<WorkersClass>>(_jasonPart);

                foreach (var _worker in _jWorkers)
                {
                    WorkersReadFromAlWebService.Add(_worker);
                    listBox1.Items.Add(_worker.Last_Name + " / " + _worker.First_name + " (No: " + _worker.No + ")");
                }
            }

            ClearWorker();
        }

        private void btnGetWorker1002Odata_Click(object sender, EventArgs e)
        {//WORKS OK
            listBox1.Items.Clear();
            WorkersReadFromAlWebService = new List<WorkersClass>();
            string _url = "">api.businesscentral.dynamics.com/.../CardPage
            //string _url = "">api.businesscentral.dynamics.com/.../List Page
            HttpWebRequest _request = (HttpWebRequest)WebRequest.Create(_url);
            _request.ContentType = "application/json; charset=utf-8";
            _request.Headers["Authorization"] = "Basic " + Convert.ToBase64String(Encoding.GetEncoding("ISO-8859-1").GetBytes("UserName:Password"));
            _request.PreAuthenticate = true;
            HttpWebResponse _response = _request.GetResponse() as HttpWebResponse;
            using (Stream _responseStream = _response.GetResponseStream())
            {
                var x = _response.Headers.AllKeys;
                StreamReader _reader = new StreamReader(_responseStream, Encoding.UTF8);
                string _content = _reader.ReadToEnd();

                string _jasonPart = GetJsonPartSingleRecord(_content);

                WorkersClass _worker = JsonConvert.DeserializeObject<WorkersClass>(_jasonPart);

                WorkersReadFromAlWebService.Add(_worker);
                listBox1.Items.Add(_worker.Last_Name + " / " + _worker.First_name + " (No: " + _worker.No + ")");
            }

            ClearWorker();
        }

        private async void btnDeleteSelectedRecordOdatas_Click(object sender, EventArgs e)
        {//WORKS OK
            try
            {
                //string _url = "">api.businesscentral.dynamics.com/.../Card Page
                string _url = "">api.businesscentral.dynamics.com/.../List Page
                string _userName = "UserName";
                string _wsKey = "Password";
                byte[] _authenticationParameter = Encoding.UTF8.GetBytes(_userName + ":" + _wsKey);
                Http_client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic", Convert.ToBase64String(_authenticationParameter));
                //HttpResponseMessage response = await _client.DeleteAsync(_url + rtbE_Tag.Text);//.PostAsJsonAsync(_url, _worker);
                HttpResponseMessage response = await Http_client.DeleteAsync(_url + "(No='" + tbNo.Text + "')");
                response.EnsureSuccessStatusCode();
            }
            catch (Exception ex)
            {

            }
        }

        private async void btnCreateRecordOdata_Click(object sender, EventArgs e)
        {//Here is the problem, it doesn't work
            WorkersWS _data = new WorkersWS();
            _data.No = tbNo.Text;
            _data.First_name = tbFirstName.Text;
            _data.Last_Name = tbLastName.Text;
            _data.FunctionName = tbFunctionName.Text;

            var url = await CreateWorkerAsync(_data);
        }

        public string GetJsonPartMultiRecord(string _webServiceReply)
        {
            string _retVal = _webServiceReply.Remove(0, _webServiceReply.IndexOf("[")); // Remove part before JSON
            _retVal = _retVal.Replace("@odata.etag", "E_Tag");                           // E_Tag can be a property name, @odata.etag cannot
            _retVal = _retVal.Remove(_retVal.Length - 1);                                // Remove last } (part of what we removed at the start)
            _retVal = _retVal.Replace("\\\"", "");                                       // Remove wrong quotes in value

            return _retVal;
        }

        public string GetJsonPartSingleRecord(string _webServiceReply)
        {
            string _retVal = _webServiceReply.Remove(1, _webServiceReply.IndexOf("\"@odata.etag") - 1); // Remove part before JSON
            _retVal = _retVal.Replace("@odata.etag", "E_Tag");                                           // E_Tag can be a property name, @odata.etag cannot
            _retVal = _retVal.Replace("\\\"", "");                                                       // Remove wrong quotes in value

            return _retVal;
        }

        private void ClearWorker()
        {
            rtbE_Tag.Text = string.Empty;
            tbNo.Text = string.Empty;
            tbFirstName.Text = string.Empty;
            tbLastName.Text = string.Empty;
            tbFunctionName.Text = string.Empty;
        }

        static async Task<Uri> CreateWorkerAsync(WorkersWS _worker)
        {
            string _url = "https://api.businesscentral.dynamics.com/v2.0/SomeFunkyGuid/Sandbox/ODataV4/Company('CRONUS%20NL')/WorkersWebService";//Card Page
string _userName = "UserName";
            string _wsKey = "Password";
            byte[] _authenticationParameter = Encoding.UTF8.GetBytes(_userName + ":" + _wsKey);
            Http_client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic", Convert.ToBase64String(_authenticationParameter));
            HttpResponseMessage response = await Http_client.PostAsJsonAsync(_url, _worker);
            try
            {
                response.EnsureSuccessStatusCode();//Error 400 bad request for both _worker and data
            }
            catch (Exception ex)
            {

            }

            // return URI of the created resource.
            return response.Headers.Location;
        }
    }
}

D365BBC/AL code:

Table: TabWorkers.al

table 50109 "Workers"
{
    DataClassification = ToBeClassified;

    fields
    {
        field(1; "No."; Code[20])
        {
            DataClassification = ToBeClassified;
        }

        field(10; "First name"; Text[50])
        {
            DataClassification = ToBeClassified;
        }

        field(20; "Last Name"; Text[50])
        {
            DataClassification = ToBeClassified;

        }

        field(40; FunctionName; Text[50])
        {
            DataClassification = ToBeClassified;

        }
    }

    trigger OnInsert()
    var
        myInt: Integer;
    begin

    end;

    trigger OnModify()
    var
        myInt: Integer;
    begin

    end;

    trigger OnDelete()
    var
        myInt: Integer;
    begin

    end;
}

Card page: CpWorkers.al

page 50108 "Workers Card"
{
    PageType = Card;
    ApplicationArea = All;
    UsageCategory = Administration;
    SourceTable = Workers;

    layout
    {
        area(Content)
        {
            group(General)
            {
                field("No."; "No.")
                {
                    ApplicationArea = Basic;
                    Importance = Promoted;
                }

                field("First name"; "First name")
                {
                    ApplicationArea = Basic;
                }

                field("Last name"; "Last name")
                {
                    ApplicationArea = Basic;
                }

                field(FunctionName; FunctionName)
                {
                    ApplicationArea = Basic;

                }
            }
        }
    }
}

List page: LpWorkers.al

page 50109 "Workers List"
{
    PageType = List;
    ApplicationArea = All;
    UsageCategory = Lists;
    SourceTable = Workers;

    layout
    {
        area(Content)
        {
            repeater(Group)
            {
                field("No."; "No.")
                {
                    ApplicationArea = Basic;
                }

                field("First name"; "First name")
                {
                    ApplicationArea = Basic;
                }

                field("Last Name"; "Last Name")
                {
                    ApplicationArea = Basic;
                }

                field(FunctionName; FunctionName)
                {
                    ApplicationArea = Basic;
                }
            }
        }
    }
}

WorkersWebService.xml:

<?xml version = "1.0" encoding = "utf-8" ?>
<ExportedData>
    <TenantWebServiceCollection>
        <TenanatWebService>
            <ObjectType>Page</ObjectType>
            <ObjectID>50108</ObjectID>
            <ServiceName>WorkersWebService</ServiceName>
            <Published>true</Published>
        </TenanatWebService>
    </TenantWebServiceCollection>
</ExportedData>

When I insert a record, It is like:

            _data.No = tbNo.Text;// 1003
            _data.First_name = tbFirstName.Text;//aaa
            _data.Last_Name = tbLastName.Text;//bbb
            _data.FunctionName = tbFunctionName.Text;/ccc

I hope this give you an insight to my problem.

Kind regards,

Clemens Linders

Was this reply helpful? Yes No
keoma 32,729 on at

Like (0)

Report

table and page structure seem ok and cannot be a reason for that issue.

seems to be an issue on c# side.

could be, that you may find additional help in c# forums.

maybe following helps: forums.asp.net/.../2141722.aspx

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

The following steps describe the processing logic when an UpsertRequest is received:

Send UpsertRequest with enough data for a create or insert operation.

Common Data Service will look up the record targeted by the target entity.

If the record exists:

Set the ID property of the target entity with the ID of the found record.

Call Update.

Set the RecordCreated to false.

Create an EntityReference from the target entity of the update as the value for Target.

Return the UpsertResponse.

If the record doesn’t exist:

Copy any alternate key values into the target entity attributes.

Call Create.

Set the RecordCreated to true.

Create an EntityReference from the target entity type and the ID result of the Create request as the value for Target.

Return the UpsertResponse.

I hope this information helps!

Was this reply helpful? Yes No
Clemens Linders 70 on at

Like (0)

Report

Hi Lewis,

The question for me would be how to exactly translate your steps into C#.

Kind regards,

Clemens Linders

Was this reply helpful? Yes No
gert-jan.terschure 200 on at

Like (0)

Report
I'd recommend to use the Simple.Odata.Client library to write data to BC and create a C# class with the ETag annotation. It would look something like this:

public class Annotations { [JsonProperty(PropertyName = "@odata.etag")] public string etag {get; set;} } public class Worker : Annotations { // All properties you need } // If using simple.odata, strongly typed var workers = await client.For().FindEntriesAsync().ToList(); foreach(var worker in workers) { worker.prop = something; await client.For().Key(worker.key).Set({changedProperties}).UpdateEntryAsync(); }

If you want to write your own OData library, you need a wrapper to grabs the value[ from odata. Easiest it to create a receiver class that looks something like:

class c { public List value {get; set;} }

Then use Json.Net or System.Text.Json to deserialise the JSON object to your C# class. No need to parse the text yourself.

Was this reply helpful? Yes No
Clemens Linders 70 on at

Like (0)

Report

Hi Gert-Jan,

I'm trying to use your sample code.

I have a variable Http_client of the type HttpClient.

But when I type: await Http_client. I do not get For as an option.

Are you perhaps using a different type than HttpClient, or am I doing something wrong.

Kind regards,

Clemens Linders

Was this reply helpful? Yes No
gert-jan.terschure 200 on at

Like (0)

Report

Hi, that's a feature of the "Simple.Odata.Client" NuGet package (www.nuget.org/.../Simple.OData.Client). It's not the standard HttpClient library. I'd recommend using the package in your application to simplify dealing with Odata.

However, if you want to use the standard HttpClient library you'll need to do things a bit differently.

1. GetAsStringAsync() <- This gives you a string containing the json text.

2. Deserialize the json string into a C# object resembling the 2nd code block in my previous post.

3. Foreach through the Workers in the list

4. Create new PUT request for BC, adding the If-Match header (Value=Etag)

5. Serialize the C# object as json

6. Send request.

This is much more work than using the Simple.Odata.Client library to handle it for you.

Was this reply helpful? Yes No
Clemens Linders 70 on at

Like (0)

Report

Hi Gert-Jan,

I had a look at the Simple.Odata client before.

But when I use this Simple.Odata client I cannot connect to the web service. I think authentication is a problem.

I cannot specify that it is a Basic authentication.

To install Simple.Odata.Client (v4) I used: Install-Package Simple.OData.V4.Client -Version 5.12.0

I started a simple test to read the workers from the web service.

I created a global variable:

        ODataClient Client = new ODataClient("https://api.businesscentral.dynamics.com/v2.0/SomeFunkyGuid/Sandbox/ODataV4/Company('CRONUS%20NL')/WorkersWebService");

As no credentials are set I included at OnLoad:

            ODataClientSettings settings = new ODataClientSettings()
            {
                BaseUri = new Uri("">api.businesscentral.dynamics.com/.../WorkersWebService"),
                Credentials = new NetworkCredential("UserName", "Password")
            };
            Client = new ODataClient(settings);

And as Button.click I included:

        private async void button1_Click(object sender, EventArgs e)
        {
            try
            {
                var workers = await Client.For<Worker>().FindEntriesAsync();
            }
            catch (Exception ex)
            {

            }

      }

In your sanple code you have a .ToList behind FindEntriesAsync. I do not have this option (perhaps this is a difference in Odata v3/v4?).

If I run this code I get an error at: var workers = ...

Not found: {"error":{"code":"BadRequest_NotFound","message":"The request URI is not valid. Since the segment 'WorkersWebService' refers to a collection, this must be the last segment in the request URI or it must be followed by an function or action that can be bound to it otherwise all intermediate segments must refer to a single resource.

The uri is of course 100% identical toi the uri if I use HttpClient.

Using HttpClient I use: Http_client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic", Convert.ToBase64String(_authenticationParameter));

So I specifically set authentication to basic and I convert the authentication parameter to a base64 string.

My guess is that the problem is authentication.

I hope you know how I can fix this. Using Simple.Odata.Client would be my preferred solution.

Kind regards,

Clemens Linders

Was this reply helpful? Yes No