How to return a DataSet from an Epicor Function?

tomdomurat · May 11, 2023, 1:59pm

I want to return a dataset from an Epicor Function (v.10.2.500) which calls an API. Request parameter to the Function is a String and the Response Parameter defined as System.Data.DataSet.
It will always be one row of data, but 20+ fields. I need fields of dataset returned.

I want to call this Function from a Data Directive and doing the following:

part = ud27.PartNum;
InfoMessage.Publish("Search - Part: " + part);
{
// Call Web Service to get the Part Table.
var result = (Tuple<System.Data.DataSet>)this.InvokeFunction(“PartWebService”, “PartSearch”, Tuple.Create(part));

DataSet dataSet = result?.Item1;	
var outputStr = dataSet?.Tables[0]?.Rows[0][0]?.ToString();
InfoMessage.Publish("PartSearch :" + outputStr);

} // end webservice call

josecgomez · May 11, 2023, 2:09pm

You just need to create / populate the data set in the function as you show in your screen shot there should be a dataSet paramter in your function so you can do

DataTable dt = new DataTable();
dt.Columns.Add(....)
dataSet.Tables.Add(dt)

etc...

tomdomurat · May 11, 2023, 2:25pm

Yes, I am populating the DataSet inside the Function as follows. I am not sure how to return DataSet to the DataDirective. Am I calling the Function correctly? I know v.10.2.500 was the first release of Functions and a bit buggy.

    DataTable dataTable = new DataTable();
	dataTable.Columns.Add("PartNumber", typeof(string));
	dataTable.Columns.Add("ItemType", typeof(string));
	dataTable.Columns.Add("Description", typeof(string));
	dataTable.Columns.Add("MeasurementUnits", typeof(string));
	dataTable.Columns.Add("BeltWidth", typeof(string));
	dataTable.Columns.Add("BeltLength", typeof(string));
	dataTable.Columns.Add("BeltLengthSubInches", typeof(string));
	dataTable.Columns.Add("MetricWidth", typeof(string));
	dataTable.Columns.Add("MetricLength", typeof(string));
	dataTable.Columns.Add("ImperialWidth", typeof(string));
	dataTable.Columns.Add("ImperialLengthFeet", typeof(string));
	dataTable.Columns.Add("ImperialLengthInches", typeof(string));
	dataTable.Columns.Add("MeasurementReferenceCode", typeof(string));
	dataTable.Columns.Add("Material", typeof(string));
	dataTable.Columns.Add("Finish", typeof(string));

	DataRow row = dataTable.NewRow();
	row["PartNumber"] = partInfo.PartNumber;
	row["ItemType"] = partInfo.ItemType;
	row["Description"] = partInfo.Description;
	row["MeasurementUnits"] = partInfo.FB.measurementUnits;
	row["BeltWidth"] = partInfo.FB.beltWidth;
	row["BeltLength"] = partInfo.FB.beltLength;
	row["BeltLengthSubInches"] = partInfo.FB.beltLengthSubInches;
	row["MetricWidth"] = partInfo.FB.metricWidth;
	row["MetricLength"] = partInfo.FB.metricLength;
	row["ImperialWidth"] = partInfo.FB.imperialWidth;
	row["ImperialLengthFeet"] = partInfo.FB.imperialLengthFeet;
	row["ImperialLengthInches"] = partInfo.FB.imperialLengthInches;
	row["MeasurementReferenceCode"] = partInfo.FB.measurementReferenceCode;
	row["Material"] = partInfo.FB.material;
	row["Finish"] = partInfo.FB.finish;

	dataTable.Rows.Add(row);
	
	dataSet.Tables.Add(dataTable);

josecgomez · May 11, 2023, 2:30pm

What is the issue? Yeah this is the first release of Functions and I don’t even know if it works in Data Directives at this point but it looks fine.
Are you just getting an error?

tomdomurat · May 11, 2023, 3:00pm

I am not getting any errors in either the data directive nor function. But, I am getting no data back either. The API definitely works as I can call this in Visual Studio code. I am not sure if it is bug in this release or just something I am doing wrong calling it. I have called other Functions from data directive but just passing strings, this is the first time trying to return a DataSet.

klincecum · May 11, 2023, 3:31pm

Did you name your DataTable, and are referencing that name correctly?

I don’t see it.

Dragos · May 11, 2023, 3:40pm

You forgot to initialize your dataset. Add the line below at the top of your code:

dataSet = new DataSet();

tomdomurat · May 11, 2023, 4:04pm

I do, I just did not include in the code snippet. This is the entire code in the Function:

string apiUrl = "http://apiurllink";

HttpWebRequest request = (HttpWebRequest)WebRequest.Create(apiUrl);
request.Method = "GET";
DataSet dataSet = new DataSet();

using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
{
    if (response.StatusCode == HttpStatusCode.OK)
    {
        using (Stream stream = response.GetResponseStream())
        {
            using (StreamReader reader = new StreamReader(stream))
            {
                string content = reader.ReadToEnd();

                // Deserialize the JSON response
                dynamic responseData = JsonConvert.DeserializeObject(content);

                // Access the data
                string message = responseData.message;
                dynamic partInfo = responseData.partInfo;
                bool isError = responseData.isError;

                if (!isError)
                {
                    DataTable dataTable = new DataTable();
                    dataTable.Columns.Add("PartNumber", typeof(string));
                    dataTable.Columns.Add("ItemType", typeof(string));
                    dataTable.Columns.Add("Description", typeof(string));
                    dataTable.Columns.Add("MeasurementUnits", typeof(string));
                    dataTable.Columns.Add("BeltWidth", typeof(string));
                    dataTable.Columns.Add("BeltLength", typeof(string));
                    dataTable.Columns.Add("BeltLengthSubInches", typeof(string));
                    dataTable.Columns.Add("MetricWidth", typeof(string));
                    dataTable.Columns.Add("MetricLength", typeof(string));
                    dataTable.Columns.Add("ImperialWidth", typeof(string));
                    dataTable.Columns.Add("ImperialLengthFeet", typeof(string));
                    dataTable.Columns.Add("ImperialLengthInches", typeof(string));
                    dataTable.Columns.Add("MeasurementReferenceCode", typeof(string));
                    dataTable.Columns.Add("Material", typeof(string));
                    dataTable.Columns.Add("Finish", typeof(string));

                    DataRow row = dataTable.NewRow();
                    row["PartNumber"] = partInfo.PartNumber;
                    row["ItemType"] = partInfo.ItemType;
                    row["Description"] = partInfo.Description;
                    row["MeasurementUnits"] = partInfo.FB.measurementUnits;
                    row["BeltWidth"] = partInfo.FB.beltWidth;
                    row["BeltLength"] = partInfo.FB.beltLength;
                    row["BeltLengthSubInches"] = partInfo.FB.beltLengthSubInches;
                    row["MetricWidth"] = partInfo.FB.metricWidth;
                    row["MetricLength"] = partInfo.FB.metricLength;
                    row["ImperialWidth"] = partInfo.FB.imperialWidth;
                    row["ImperialLengthFeet"] = partInfo.FB.imperialLengthFeet;
                    row["ImperialLengthInches"] = partInfo.FB.imperialLengthInches;
                    row["MeasurementReferenceCode"] = partInfo.FB.measurementReferenceCode;
                    row["Material"] = partInfo.FB.material;
                    row["Finish"] = partInfo.FB.finish;

                    dataTable.Rows.Add(row);
                    
                    dataSet.Tables.Add(dataTable);
                    
                    // Write to log here:
                    //Ice.Diagnostics.Log.WriteEntry($"[ {efxName} ] writing to application log...");
                }
            }
        }
    }
}

klincecum · May 11, 2023, 6:45pm

Have you tested that function in swagger?

tomdomurat · May 11, 2023, 7:48pm

I finally got it working in a Data Directive. (I will share the code when it is cleaned-up).

Now, I want to call the Function in a form customization as well but there is no 10.2.500.x version of Epicor.Lib.RestClient.dll…does anyone have this? I tried using a more current version DLL, but could not get the code compile in the Editor.

klincecum · May 11, 2023, 8:44pm

Use httpclient or add restsharp.

josecgomez · May 12, 2023, 12:18pm

You can use the EpiRest Library you just have to deploy it with the client.

using EpicorRestAPI;
SetupEpicorRest("APIKey")
var response = EpicorRest.EfxPost("CustomerPortal", "GenerateNewPassword", new { });
GeneratePwdResponse pwdResp = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet>(response.ResponseBody);
if(!response.IsErrorResponse)
{
// do something with response.Properties
}
//......


public bool SetupEpicorRest(string apiKey)
    {
        try
        {
            string appUri =AppSettingsHandler.GetValue(AppSettingsSections.Application, "AppServerURL", null);
            var uri = new Uri(appUri);
            EpicorRestAPI.EpicorRest.AppPoolHost=uri.Host;
            EpicorRest.AppPoolInstance = uri.LocalPath.Replace("/","");
            EpicorRest.APIKey = apiKey;
            var session = oTrans.Session as Ice.Core.Session;
			EpicorRest.Company = session.CompanyID;

			Assembly sm = Assembly.LoadFrom("Epicor.ServiceModel.dll");  // Kinetic Uplift
			object authClass = sm.CreateInstance("Epicor.ServiceModel.Wcf.Security.AuthenticationWcf");  // Kinetic Uplift
			Type smType = authClass.GetType();  // Kinetic Uplift
			BindingFlags bf = BindingFlags.Instance|BindingFlags.NonPublic|BindingFlags.Public;  // Kinetic Uplift
			MethodInfo mi = smType.GetMethod("IsWindows", bf);  // Kinetic Uplift
			object[] invParams = new object[1]{AppSettingsHandler.AuthenticationMode};  // Kinetic Uplift
			bool isWindows = (bool)mi.Invoke(authClass,invParams);  // Kinetic Uplift
			

            var accessTokenFunc = typeof(Ice.Core.Session).GetProperty("GetAccessTokenFunc", BindingFlags.Instance|BindingFlags.NonPublic|BindingFlags.Public).GetValue(session) as Func<bool,string>;
            if(accessTokenFunc !=null)
            {
                EpicorRest.BearerToken = accessTokenFunc(false);
            }
			else if(isWindows) // Kinetic Uplift
            {
                EpicorRest.UserName = Environment.UserName;
                EpicorRest.Password = "";
            }
            else
            {
                object clientCreds = typeof(Ice.Core.Session).GetProperty("ClientCredentials", BindingFlags.Instance|BindingFlags.NonPublic|BindingFlags.Public).GetValue(session);
                if(clientCreds!=null)
                {
                    object clientCredsUname = clientCreds.GetType().GetProperty("UserName").GetValue(clientCreds);
                    EpicorRest.UserName =clientCredsUname.GetType().GetProperty("UserName").GetValue(clientCredsUname) as String;
                    EpicorRest.Password =clientCredsUname.GetType().GetProperty("Password").GetValue(clientCredsUname) as String;
                }
            }
            return true;
        }
        catch(Exception ex)
        {
            return false;
        }
    }

Topic		Replies	Views
Function question: How to pass a DataSet or DataTable to a function? Epicor ERP 10 e10 , epicor , kinetic , functions	4	1452	July 24, 2023
Convert Erp.Tablesets to System.Data.DataSet Epicor ERP 10 e10 , function	12	6425	September 25, 2023
Pulling data from web via REST BPM Epicor ERP 10 bpm , rest	25	5710	May 17, 2023
How to convert a JObject to System.Data.DataSet? Or how to deserialize json to dataset? Kinetic ERP rest , integration , functions	8	9180	May 14, 2024
Passing a View to a Function Kinetic ERP kinetic , application-studio	8	5307	September 16, 2021

How to return a DataSet from an Epicor Function?

Related Topics