• State Verified Answer
  • Replies 8 replies
  • Subscribers 98 subscribers
  • Views 2938 views
  • Users 0 members are here
Options
Related

CompositionAPI independent Datatypes

prueegg

If I use HandleByQuery or HandleBySqlStatement I get have a resulting json which reflects the DB structure.

Now I need to provide a REST API for querying some date in the IdentityManager and I want to use my own DateStructure for the resulting json.

As simple example I've done this:

builder.AddMethod(Method.Define("eset")
.HandleGet(async qr =>{
    Query q = Query.From("ESet").Select("Ident_ESet").Where("UID_ESetType = N'e65d9baa-d737-4de9-a005-2f1f8f6263c8'");
    var entityRead = await qr.Session.Source().GetCollectionAsync(q);
    ESetList esets = new ESetList();

    foreach (var eset in entityRead){
        esets.esets.Add(new ESet() { Ident_ESet = eset.GetValue("Ident_ESet") });
    }
    return esets;
}));}

Now the Result looks like:

{
"Result": {
"esets": [
{
"Ident_ESet": "xxxx"
},
{
"Ident_ESet": "yyyy"
},
{
"Ident_ESet": "zzzz"

...

How can I get rid of the "Result:" entry?

I just need:

{

"esets": [

{
"Ident_ESet": "xxxx"
},
{
"Ident_ESet": "yyyy"
},
{
"Ident_ESet": "zzzz"

...

Or is there an easier way to do the request from scratch?

Parents
  • +1

    It works now as expected. The additional bracket with the "Result": {...} disappears while using the CancellationToken as well:

                    .HandleGet(async (qr, ct) =>
                {

                    Query qEsets = Query.From("ESet").Select("Ident_ESet", "Remarks", "UID_PersonResponsible", "UID_ESetType").Where("UID_ESetType = N'e65d9baa-d737-4de9-a005-2f1f8f6263c8' or UID_ESetType=N'83b1694a-1d6c-4fb8-a5ea-f3f0e0d58e1d'");
                    var entityRead = await qr.Session.Source().GetCollectionAsync(qEsets, ct).ConfigureAwait(false);

    The "Api0015;Await construct is missing ..." error in the API Designer was caused by wrong order of brackets. (The API Designer isn't showing that issue as the Visual Studio would).

  • 0 in reply to prueegg

    Coming back to my request:

    I'v just found out, that I should use a predefined SQL for my purpose for better performance, since I have to collect also from DialogMultilanguage.

    Now I have to problem, hat the result contains to much information

                "Columns": {
                "Ident_ESet": {
                    "Value": "SR1_TK_000509",
                    "IsReadOnly": true,
                    "DisplayValue": "SR1_TK_000509"
                },
                "LastUpdated": {
                    "Value": "2020-02-06T13:02:26.293Z",
                    "IsReadOnly": true,
                    "DisplayValue": "2/6/2020 2:02:26 PM"
                },

    The client isn't interested in isReadOnly or not, and Value and DisplayValue are redundant in our case.

    Wow could i transfer that to a more simple json?

    Something like the following is all i need:

        {
        "Ident_ESet": "SR1_TK_000509",
        "LastUpdated": "2020-02-06T13:02:26.293Z"
    },

    Any idea?

    I couldn't find enough information in the documentation nor in the apisamples.

Reply
  • 0 in reply to prueegg

    Coming back to my request:

    I'v just found out, that I should use a predefined SQL for my purpose for better performance, since I have to collect also from DialogMultilanguage.

    Now I have to problem, hat the result contains to much information

                "Columns": {
                "Ident_ESet": {
                    "Value": "SR1_TK_000509",
                    "IsReadOnly": true,
                    "DisplayValue": "SR1_TK_000509"
                },
                "LastUpdated": {
                    "Value": "2020-02-06T13:02:26.293Z",
                    "IsReadOnly": true,
                    "DisplayValue": "2/6/2020 2:02:26 PM"
                },

    The client isn't interested in isReadOnly or not, and Value and DisplayValue are redundant in our case.

    Wow could i transfer that to a more simple json?

    Something like the following is all i need:

        {
        "Ident_ESet": "SR1_TK_000509",
        "LastUpdated": "2020-02-06T13:02:26.293Z"
    },

    Any idea?

    I couldn't find enough information in the documentation nor in the apisamples.

Children
  • 0 in reply to prueegg

    Hi,

    You are already in control of the serialized data, because the ESetList-typed and ESet-typed objects are simply being serialized to generate the JSON result.

    Can you post the definitions of the ESetList/ESet classes please?

    (By the way, using a predefined SQL statement only for performance reasons is not recommended for simple object queries.)

  • 0 in reply to Hanno Bunjes

    Thanks for your fast reply.

    The Eset and ESetList classes are quite simple

        public class ESetList
    {
        public List<ESet> esets { get; set; } = new List<ESet>();
    }
    public class ESet
    {
        public string Key { get; set; }
        public string Ident_ESet { get; set; }
        public string Name_ESet_DE { get; set; }
        public string Name_ESet_FR { get; set; }
        public string Name_ESet_IT { get; set; }
        public string Description_ESet_DE { get; set; }
        public string Description_ESet_FR { get; set; }
        public string Description_ESet_IT { get; set; }
        public string Person_Responsible { get; set; }
        public string Eset_Type { get; set; }
        public DateTime lastUpdate { get; set; }
    }

    Collecting data is quite expensive. For Ident_ESet (Name_Eset) and Remark (Description_ESet) I need the translation for German, French and Italian.
    The LastUpdate should reflect the latest update including the translations. So I need the MAX of XDateUpdate spread over ESet and the corresponding six DialogMultiLanguage Entries.

    My predefined SQL Statement would look like this:

    select e.Ident_ESet,
(SELECT MAX(LastUpdateDate)
      FROM (VALUES (e.XDateUpdated),(td.XDateUpdated),(dd.XDateUpdated),(tf.XDateUpdated),(df.XDateUpdated),(ti.XDateUpdated),(di.XDateUpdated)) AS UpdateDate(LastUpdateDate)) 
   AS LastUpdated,
td.EntryValue as TitelDE,
dd.EntryValue as DescriptionDE, 
ti.EntryValue as TitelFR,
di.EntryValue as DescriptionFR, 
ti.EntryValue as TitelIT,
di.EntryValue as DescriptionIT

from eset e 
left join DialogMultiLanguage td on e.Ident_ESet = td.EntryKey and td.UID_DialogColumn ='QER-E086191A0B4E421C951A926B22C3E771' and td.UID_DialogCulture = 'QBM-CULT-de-CH'
left join  DialogMultiLanguage dd on e.Remarks = dd.EntryKey and dd.UID_DialogColumn ='RMS-A2408193627B46729B9F255D571663CF' and dd.UID_DialogCulture = 'QBM-CULT-de-CH'
left join DialogMultiLanguage tf on e.Ident_ESet = tf.EntryKey and tf.UID_DialogColumn ='QER-E086191A0B4E421C951A926B22C3E771' and tf.UID_DialogCulture = 'QBM-CULT-fr-CH'
left join  DialogMultiLanguage df on e.Remarks = df.EntryKey and df.UID_DialogColumn ='RMS-A2408193627B46729B9F255D571663CF' and df.UID_DialogCulture = 'QBM-CULT-fr-CH'
left join DialogMultiLanguage ti on e.Ident_ESet = ti.EntryKey and ti.UID_DialogColumn ='QER-E086191A0B4E421C951A926B22C3E771' and ti.UID_DialogCulture = 'QBM-CULT-it-CH'
left join  DialogMultiLanguage di on e.Remarks = di.EntryKey and di.UID_DialogColumn ='RMS-A2408193627B46729B9F255D571663CF' and di.UID_DialogCulture = 'QBM-CULT-it-CH'
order by td.UID_DialogCulture

    It collects all the required information fast enough. But if I use .HandleGetBySqlStatement(..) I get the Datatypes with all the additional (Meta)Data.

    Is there a way to convert the result of .HandleGetBySqlStatement into my Data Types, or could i call a predefined SQL Statment in my .HandleGet Code?

  • +1 in reply to prueegg

    HandleGetBySqlStatement uses a fixed result type, so you would have to use the generic HandleGet variant to execute the predefined SQL statement. It would look similar to this:


    Method.Define("some_method")
   .HandleGet(async (request, ct) =>
   {
            var session = request.Session;
            var result = await session.Resolve<IStatementRunner>().SqlExecuteAsync("<Statement_ID>", new[]
            {
                new QueryParameter("<Some_Parameter>", ValType.String, "Some_ParameterValue>")
            }, ct).ConfigureAwait(false);

            using (result)
            {
                while (result.Read())
                {
                // ... process each data row
                }
            }
            
            return result_object;
        }
        }

    Make sure to include the "using" keyword, otherwise an open data reader will cause subsequent SQL statements to fail.

  • 0 in reply to Hanno Bunjes

    Cool, that works perfect. I've just tried out. That is the solution I was looking for.

    Thanks a lot for your support. I wouldn't have any chance to find that out myself.