Direct DB Update, Add Row, Epicor Function - Cannot Add, am I missing something?

Also, if it wasn’t clear from the title, this is in an Epicor Function.

Ok, let me explain first, before everyone jumps on me :slight_smile:

I am working purely with a UD Table. I wanted to add a record to UD20,
with a direct DB Update.

Not here to discuss really whether this is good or bad.

But anyway, I can update any fields I want this way, but I can’t figure out how
to ADD a row.

Did they remove that ability? Or am I crazy? (Don’t answer the second question.)

1 Like

Are you saying you could do this before?

I never tried, I assumed you could, because you could do modifications directly.

I guess, to me it was always easier to just use the BO… But you made weird rules here. I guess I’m not sure.

Lol, well that’s what I did, I used the BO.

This is a logging function, I was just trying to keep the overhead down as much as possible.

I’d still like to know if it’s possible. Why can I chose a record, and update it, but I can’t add a record ?

The UD Bo’s are pretty damn lightweight. I’m not sure you’re going to gain much.

1 Like

You’re probably right.

I gotta know though :rofl:

Did you set your Db Context to Read / Write?

Yes.

Well, short answer you cant

Longer answer you can, but its a security issue and I’m not sure you should. :neutral_face:

Wait I found the right knob!! You have to hit updatable in the Table just setting the function as writable doesn’t matter

Then you can Db.AddObject and Db.DeleteObject as options.

3 Likes

Worked yay, and no function security violations :tada:

UD20 ud20Row = new UD20()
{
    Key1 = Guid.NewGuid().ToString()
};

Db.AddObject(ud20Row);
Db.SaveChanges();
2 Likes

Now I want a full statistical analysis of how much time you gained…

1 Like

I’m not gonna rewrite it. Too lazy.

Video Games GIF by South Park

1 Like

OK, You did ask…

Here is the code in the function(s)…

BO Update:

  Exception exceptionObject = JsonConvert.DeserializeObject<Exception>(exception);

  Dictionary<string, object> objectsToSave = new Dictionary<string, object>();
  objectsToSave.Add("Exception", exceptionObject);
  
  CallService<UD20SvcContract>(boUD20 =>
  {
      UD20Tableset ud20Tableset = new UD20Tableset();
      
      DateTime now = DateTime.Now;
      TimeSpan timeAfterMidnight = DateTime.Now - DateTime.Today;
      
      UD20Row logRow = new UD20Row()
      {
          Key1 = Guid.NewGuid().ToString(),
          Key2 = project,
          Key3 = "Error",
          Key4 = "Exception",
          Character01 = exceptionObject.Message,
          Character02 = exceptionObject.InnerException == null ? "" : exceptionObject.InnerException.Message,
          Date01 = DateTime.Today,
          Number01 = Convert.ToInt64(timeAfterMidnight.TotalSeconds),
          ShortChar01 = callContextClient.CurrentUserId,
          ShortChar02 = action,
          Character03 = actionData1,
          Character04 = actionData2,
          Character05 = notes,
          Character06 = JsonConvert.SerializeObject(objectsToSave),
          RowMod = "A",
          SysRevID = now.Ticks 
      };
      
      ud20Tableset.UD20.Add(logRow);
      
      boUD20.Update(ref ud20Tableset);
  });

Direct Update:

  Exception exceptionObject = JsonConvert.DeserializeObject<Exception>(exception);

  Dictionary<string, object> objectsToSave = new Dictionary<string, object>();
  objectsToSave.Add("Exception", exceptionObject);
  
  DateTime now = DateTime.Now;
  TimeSpan timeAfterMidnight = DateTime.Now - DateTime.Today;
  
  UD20 logRow = new UD20()
  {
      Key1 = Guid.NewGuid().ToString(),
      Key2 = project,
      Key3 = "Error",
      Key4 = "Exception",
      Character01 = exceptionObject.Message,
      Character02 = exceptionObject.InnerException == null ? "" : exceptionObject.InnerException.Message,
      Date01 = DateTime.Today,
      Number01 = Convert.ToInt64(timeAfterMidnight.TotalSeconds),
      ShortChar01 = callContextClient.CurrentUserId,
      ShortChar02 = action,
      Character03 = actionData1,
      Character04 = actionData2,
      Character05 = notes,
      Character06 = JsonConvert.SerializeObject(objectsToSave),
      SysRevID = BitConverter.GetBytes(now.Ticks)
  };
  
  Db.AddObject(logRow);
  Db.SaveChanges();

The Test Harness:

  System.Diagnostics.Stopwatch stopwatch1 = new System.Diagnostics.Stopwatch();
  System.Diagnostics.Stopwatch stopwatch2 = new System.Diagnostics.Stopwatch();
  
  stopwatch1.Start();
  
  int recCount = 10000;
  
  for(int cnt = 0; cnt < recCount; cnt++)
  {
      try
      {
          int x = 0;
          int y = 5 / x;
      }
      catch (Exception ex)
      {
          InvokeFunction("KEVUTILITY", "LogException", "LoggingTest", "Test_Exception", "", "", "", JsonConvert.SerializeObject(ex));  
      }
  }
  
  stopwatch1.Stop();


  stopwatch2.Start();
  
  for(int cnt = 0; cnt < recCount; cnt++)
  {
      try
      {
          int x = 0;
          int y = 5 / x;
      }
      catch (Exception ex)
      {
          InvokeFunction("KEVUTILITY", "LogException2", "LoggingTest", "Test_Exception", "", "", "", JsonConvert.SerializeObject(ex));  
      }
  }
  
  stopwatch2.Stop();

  InfoMessage.Publish($"BO Update: {stopwatch1.Elapsed.TotalSeconds.ToString()} seconds\nDirect Update: {stopwatch2.Elapsed.TotalSeconds.ToString()} seconds");

And the Results:

1000 Records

Run 1 (1000) Records

BO Update: 5.4984802 seconds
Direct Update: 2.4482677 seconds

Run 2 (1000) Records

BO Update: 4.8526954 seconds
Direct Update: 2.3025349 seconds

Run 3 (1000) Records

BO Update: 4.9567663 seconds
Direct Update: 2.3572281 seconds

10,000 Records

Run 1 (10000) Records

BO Update: 50.6735952 seconds
Direct Update: 24.6151174 seconds

Run 2 (10000) Records

BO Update: 50.8568047 seconds
Direct Update: 25.8428644 seconds

Run 3 (10000) Records

BO Update: 50.2500386 seconds
Direct Update: 23.5107262 seconds

Conclusion: If it get’s called enough it’s worth it. Direct Update is roughly half the time of the BO.

2 Likes

If anybody is curious, I was so jealous of @hmwillett 's Log Table I started working my own.

someone vision GIF

Fox Tv GIF by BH90210

Well someone has to keep @hmwillett on her toes.

dance feet GIF by New York City Ballet

Flood Dumpster GIF