The Data Layer

Hello again 3.0 troopers,

Hopefully by now, if you followed the last section The Axapta Business Connector, you should have an operational NT service ready for use to access the properties and methods of the Axapta Business Connector, which I will refer to as the AxAPI from here on out. This section is where the magic starts. I mean to say that this is where we add the things that Microsoft left out of the 3.0 release, but thankfully gave us the tools and the knowledge to self heal.

Let's start by examining our requirements and then understanding the gaps between them and our poor AxAPI. We can start with a basic set of needs for our foundational data layer, which I will refer to as the AxFoundationalServices layer from here on out.

1) We need to "wrap" the AxAPI to make it .NET friendly. (COM Wrapper)
2) We need to encapsulate and standardize access to Axapta for team developers and provide a consistent, structured, environment that ensures all access to the Axapta is done as the AxAPI, and Microsoft, dictates.
3) We need to provide generic methods for reading, inserting, updating, and deleting Axapta data. The keyword here is "GENERIC", as we are going to prop a lot on top of this layer so we can't make it too complex or specific to one solution. (refer to lame examples on MSDN)
4) We need to provide an Interface to manage logging into Axapta. Why an interface? I will get into more detail later. but lets just say the AxAPI does this poorly.
5) We need to support enumeration of the IAxaptaRecord, it does NOT do this without help and its vital to being generic. (requirement 4)
6) We need error handling and error logging to the Event Sytem.

Ok, so now we know what our AxFoundationalService absolutely has to do. I'm sure you can think of more, we sure did, but I want to keep this section as brief as possible and answer any additional questions you have later.

Now lets look at our gaps between what we have to have and what the AxAPI doesn't do for us without help.
1) No Wrapper
2) No standard method of access
3) No Generic methods
4) No interface to manage AxAPI logins
5) No Enumeration
6) Arguably, no error handling and poor logging of events

... wow, almost none of our requirement is supported out of the box, big suprise there. Sure we can read and write a record with the AxAPI, but how much code will we have to write each time, how do we make sure our programming standards are adhered to? Sure we can log the AxAPI in, but can we multi thread it like that? You may say that the 3.0 AxAPI doesn't multi-thread.... well, it doesn't as far as MS says, but keep reading in the later sections and you'll see why it is a requirement at this layer. Also, like I mentioned earlier, we need to build an enumerator interface around the IAxaptaRecord object to marshal generic calls to it. Finally, we need to do a better job with the event system. Remember the core programming concepts.. Manageable,Reusable, Extendable, Secureable, and Portable. Nothing changes here from what we know is best practices in any development shop.

Seems daunting doesn't it? Well, it was at first, but remember Microsoft gave us the tools to overcome these obstacles, so put on your kernel builder hat and let's get started.

First of we need to create a Class Library project in Visual Studio. My setup is VS2005 and The 2.0 Framework. We have not made the move to 3 yet as we we have to test a lot of things first. For us, that's no small project.

NOTE: I will be telling you to add interfaces and class methods that you may not understand right now, but just know that this layer is part of the bigger picture and that all of what is included may not be accessed by the end developer directly, in fact, this layer is used primarily by the object model. It can be used alone by working with the IAxaptaRecord interface directly in your presentation, but why? The idea here is that the end developer needs to know very little about Axapta and instead be skilled at object oriented programming. make it EASY, remember?

1) Name your project AxFoundationalServices.
2) Add a reference to the Axapta COM Connector 1.2 type library, you will find it in the COM tab of the add references dialog, and also a reference to System.Configuration.
3) Add a new interface named IAxObject, this will act as our interface to the IAxaptaRecord object of the AxAPI for our object model, in the next section. Add the reference to the COM connector and rename your public interface to IAxObject. Next add a method tho your interface name AxSetProperties.

Your interface should now look like this..
*********************************************
//AxObject Interface
using System;
using System.Collections.Generic;
using System.Text;
using AxaptaComConnector;

namespace AxFoundationalServices
{
public interface IAxObject
{
void AxSetProperties(IAxaptaRecord axaptaRecord);
}
}
*********************************************

Build your project and let's move on...

4)Next we need to add a destructor class, so add a new class named "Destructor", add delegate to marshal it, and also the destructor methods. We need to ensure the AxAPI is totally killed when we exit methods, as it hangs with normal garbage collection.
This class will look like this...
*********************************************
//Destructor
using System;
using System.Collections.Generic;
using System.Text;


namespace AxFoundationalServices
{
public delegate void DestructorDelegate();

public class Destructor
{
private DestructorDelegate destructorMethod = null;

public Destructor(DestructorDelegate method);
{
destructorMethod = method;
}
~Destructor()
{
if (destructorMethod !=null)
{
destructorMethod();
}
}
}
}

*****************************************************
Build your project and let's move on.
Also remember I am typing this not cut and pasting, so check your formatting.

5) We are now going to add a utility class to encapsulate common methods. Mine is full of methods, but for brevity, I will only add the relevant ones to this layer, and the layer above it. Basically adding the event handler, application name retrieve (for the calling apps), date conversion and some query helpers. Name the new class AFS_Utils and it should look like this..

***********************************************************************
// Utilities
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Data;

namespace AxFoundationalServices

{
public class AFS_Utils
{
public static string GetAppName()
{
return ConfigurationManager.AppSettings["ApplicationName"];
}

public static void LogEvent(string appName, string logMessage,
EventLogEntryType eventLogType)
{
EventLog el = new EventLog();
el.Source = appName;
el.WriteEntry(logMessage, eventLogType);
}

// we need this to seperate the date and time for axapta
public static void ConvertDateTimeToDateAndTime(DateTime date,
out DateTime dateOnly,
out int timeOnly)
{
dateOnly = date.Date;
timeOnly = Convert.ToInt32(date.TimeOfDay.TotalSeconds);
}


public static double ConvertToDouble(string doubleString)
{
double returnDouble = 0.0;
if (doubleString != "" && doubleString != null)
{
returnDouble = Convert.ToDouble(doubleString);
}

return returnDouble;
}

// Axapta takes dates in a certain format
public static string ConvertToAxaptaDateString(DateTime sqlDate)
{
return sqlDate.Day + "\\" + sqlDate.Month + "\\" + sqlDate.Year;
}

}
}

*************************************************************

Cross your fingers, build it, and let's move on to the Data Access class.

6) This class needs some explanation, as it does the majority of the processing and also consumes the Axapta2Class and IAxaptaRecord structures of the AxAPI. You notice our friend the ADO.NET DataTable is used here too.This is all part of the translation, or porting, to the .NET world. How do you turn a IAxaptaRecord object, with no enumerator, into a typesafe DataTable? Well, thats a good question, and it's not done here. For now just know we are going to use it as our vehicle to and from the BaseObject class that will be explained in the next section, The Object Layer.

It handles the following..

a) Logging the AxAPI in to do work.
It does this generically by methods taking the axapta configuration file, user name, and password as arguments. Notice how the Axapta2Class Login method takes 4 arguments. The first one is the UserID, from the Axapta users table, and the last one is the NT file path location of the configuration we want it to use when it logs in. The UserID should be the Service Account you made in Axapta.
Also notice they are both retrieved from the AppSettings scope, more importantly, the App.Config from your PRESENTATION LAYER. This was a design decision. If your company has as many Axapta environments as mine, like Development, Testing, Staging, and Production, it's pretty easy to understand. I had 2 choices, encapsulate it in the build, or let the developer have control over environment target. Being a developer, I chose the latter. I want the team to be able to test their solutions off any environment quickly, and plus I dont want to manage 8 versions of the platform. If your company doesn't have multiple environments then you should be flogged as that merits a completely different topic.
You can also see how we are keeping the Axapta2Class static! Early on in the design of this layer we built it on the basis that Axapta would handle us logging in all multi-threaded like. It kind of worked for the go-live data loading services we wrote, as we ran most of the loaders one at a time and their processing was more sequential in nature, but as we started taking concurrent users in testing, it started to break down quickly. One of the team members, Doug Max, suggested we try for a sort of ghetto pooling on the AxAPI. Hence, the Statics and the locks.
Understanding how the AxAPI works is the key. It is merely logging in to the application without a User Interface. Just click on your Axapta configuration and see how long it takes you to get to the main menu.... that's about how long it takes the AxAPI to log in. Now imagine your doing that 60 to 100 times a minute... OUCH!

b) Query.
Query is a separated from the writes to Axapta because it acts differently. It only returns data to the caller, it doesn't take data as an argument like a write does. It takes arguments like the Axapta Table Name, IAxObject interface, and of course the query we want to run.
We use the Axapta2Class's CreateRecord method to initialize the IAxaptaRecord schema by using the Axapta Table Name we passed in, then we execute the query to populate that container. Finally, we send it off and set the properties of our IAxObject interface. That last part will get clearer to you as we go, but it gives you a hint of some of the cool thinks we are going to do in the layer above us.

c) Inserts and Updates
Inserts and Updates are different. We need to pass in the updated data containers. We are going to pass in .NET DataTables. that's right, lots of cool stuff on the next layer up. Basically we are passing in DataTables and iterating through the values to find the RecID column first. We need that because ALL inserts, updates, and deletes are done by RecID. We will retrieve an Updateable IAxaptaRecord cursor, iterate through the set again and update any data column that has changed using the IAxaptaRecord's set_field method, with the exception of RecID, DataAreaID, DEL_*, and the Memo field designator. The RecID you absolutely can NOT update, the DataAreaID is implied from the Logon configuration, who cares about DEL_* fields, and the Memo field indicator is another topic.

Things to Note:
I am using the TTSBegin, TTSCommit, and TTSAbort methods of the Axapta2Class. This is NOT an option. It will work without it, but why? It sets the transaction up and allows for rollback just like in Axapta, so let it do it's work.

I deal with the Axapta array columns, like Dimension[], in an easier way to process on the object side. Axapta's Dimension[3] becomes DataTable column name DimensionArray3. This is still a topic of discussion with the team, but it save a lot of processing over other options so it stays for now.

On Insert, I pass back the newly created RecID to the caller.

All errors are handled and logged gracefully. We reset the static connection object, tell the Event System, and in many instances, just carry on like it didn't happen. With the exception of the Logon. If that blows up, your configuration settings are wrong or Axapta is down.

Well, I know you have been waiting for it so here it is. Your AFS_DataAccess class should look like this...
**********************************************************

using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Security.Principal;
using System.Configuration;
using System.IO;
using AxaptaCOMConnector;

namespace AxFoundationalServices
{
public class AFS_DataAccess
{
//private System.ComponentModel.Container components = null;
//Axapta object
private string applicationName = "";
private static Axapta2Class staticAxapta2Class;
private static Destructor myDestructor;
private static object lockAxObject = new object();

static AFS_DataAccess()
{
DestructorDelegate destructorMethod;
destructorMethod = new DestructorDelegate(Destroy);
myDestructor = new Destructor(destructorMethod);

staticAxapta2Class = new Axapta2Class();
LOGON(staticAxapta2Class);
}

private static void Destroy()
{
//Already disconnected from business connector at this point.
staticAxapta2Class.Logoff();
}

private void ResetStaticLogon()
{
AFS_Utils.LogEvent("AFS_DataAccess", "ResetStaticLogon Called! ", EventLogEntryType.Error);

lock (lockAxObject)
{
try
{
staticAxapta2Class.Logoff();
}
catch (Exception)
{ }

staticAxapta2Class = new Axapta2Class();
LOGON(staticAxapta2Class);
}
}

public AFS_DataAccess()
{
try
{
if (applicationName == "")
applicationName = ConfigurationManager.AppSettings["ApplicationName"];
}
catch (Exception)
{ }
}

public static string GetAxConfig()
{
return ConfigurationManager.AppSettings["AxConfigFile"];
}

public static string GetAxUser()
{
return ConfigurationManager.AppSettings["AxConfigUser"];
}

public static void LOGON(Axapta2Class axaptaClass)
{
try
{
axaptaClass.Logon(GetAxUser(), "", "", GetAxConfig());
}
catch (Exception e)
{
AFS_Utils.LogEvent("AFS_DataAccess", "Business Connector Log In Failed! " + e.Message, EventLogEntryType.Error);
throw;
}
}

public void AxExecQuery(IAxObject iAxObject, string query, string table)
{
IAxaptaRecord dt;
lock (lockAxObject)
{
try
{
dt = staticAxapta2Class.CreateRecord(table);
dt.ExecuteStmt(query);
// Call interface to set properties on AxObject
iAxObject.AxSetProperties(dt);
}
catch (Exception e)
{
AFS_Utils.LogEvent("AFS_DataAccess", "Business Connector execQuery Failed! " + e.Message, EventLogEntryType.Error);
ResetStaticLogon();
throw e;
}
}
}

public void AxInsert(DataTable values, string collectionName, out int identity)
{
identity = 0;
lock (lockAxObject)
{
try
{
InsertNoLogon(staticAxapta2Class, values, collectionName, out identity);
}
catch (Exception e)
{
ResetStaticLogon();
throw e;
}
}
}

private void InsertNoLogon(Axapta2Class axaptaClass, DataTable values, string collectionName, out int identity)
{
IAxaptaRecord axaptaRecord;
axaptaRecord = axaptaClass.CreateRecord(collectionName);
axaptaRecord.InitValue();

for (int i = 0; i < values.Columns.Count; i++)
{
if (values.Columns[i].ColumnName != "RECID" &&
values.Columns[i].ColumnName != "DATAAREAID" &&
!values.Columns[i].ColumnName.StartsWith("IsMemo_"))
{
string columnName = values.Columns[i].ColumnName.ToString();
string sqlColumnName = values.Columns[i].ColumnName.ToString();
string arrayColumnName = string.Empty;
if (columnName.IndexOf("Array") >= 0)
{
arrayColumnName = columnName.Substring(0, columnName.IndexOf("Array")) +
"[" + columnName.Substring(columnName.IndexOf("Array") + 5) + "]";
sqlColumnName = arrayColumnName;
columnName = sqlColumnName;
}
axaptaRecord.set_field(columnName, values.Rows[0][i]);
}
}
axaptaRecord.Insert();
identity = (int)axaptaRecord.get_field("RECID");
}

public void AxUpdate(DataTable values, string collectionName)
{
lock (lockAxObject)
{
try
{
UpdateNoLogon(staticAxapta2Class, values, collectionName);
}
catch (Exception e)
{
ResetStaticLogon();
throw e;
}
}
}

private void UpdateNoLogon(Axapta2Class axaptaClass, DataTable values, string collectionName)
{
string recID = string.Empty;
IAxaptaRecord axaptaRecord;
axaptaRecord = axaptaClass.CreateRecord(collectionName);
for (int i = 0; i < values.Columns.Count; i++)
{
if (values.Columns[i].ColumnName == "RECID")
{
recID = values.Rows[0][i].ToString();
}
}
string query = "Select FORUPDATE * From %1 Where %1.RecID == " + recID;
axaptaClass.TTSBegin();
axaptaRecord.ExecuteStmt(query);
for (int i = 0; i < values.Columns.Count; i++)
{
if ((values.Columns[i].ColumnName != "RECID") &&
(!values.Columns[i].ColumnName.ToUpper().StartsWith("DEL_")) &&
(!values.Columns[i].ColumnName.StartsWith("IsMemo_")))
{
string columnName = values.Columns[i].ColumnName.ToString();
string sqlColumnName = values.Columns[i].ColumnName.ToString();
string arrayColumnName = string.Empty;
if (columnName.IndexOf("Array") >= 0)
{
arrayColumnName = columnName.Substring(0, columnName.IndexOf("Array")) +
"[" + columnName.Substring(columnName.IndexOf("Array") + 5) + "]";
sqlColumnName = arrayColumnName;
columnName = sqlColumnName;
}
axaptaRecord.set_field(columnName, values.Rows[0][i]);
}
}
axaptaRecord.Update();
axaptaClass.TTSCommit();
}
}
}
**************************************************************
build it and woohooo! Your done with the AxFoundationalServices Data Layer!


To wrap it up:
I know many may be wondering, why the memo field mystery? Wheres the Delete method?
The objective here is to not write your code, but to give you an understanding of what it's doing. Getting an real understanding of this concept will give you the ability to do the rest. Have I done it? You bet.

This Data Access Layer is designed to work with the AxObject layer above it. Developers should never write applications to this layer directly. Is it possible? Sure, but why? All a developer should have to worry about is retrieve data and save data, not whether its an insert or update. He or she should not have to manage concurrency, and your company doesn't want that either. Part of the architects role is to design reusable, scalable, bullet proof development environments, and enforce standards. The next section, The Object Model, goes into that, as soon as I get time.

Is your head hurting yet? Are you still with me?
So many things about the Object model above this has gone unsaid..
So many nuances like this IAxObject interface you may not understand.
How is any of this useful to you?

Rest now children, the answers are coming.....


Thanks,


H

The Axapta 3.0 Business Connector

In this topic, we will cover how we get the Business Connector up and running as a stable NT Service, ready to handle requests from our Data Layer. We will get into the Data Layer discussion in the next section, but for the purpose of this topic, we want to make sure we set the Business Connector up in such a way that the Data Layer has no issues consuming it. I say that because the settings we use while installing the connector will determine how stable the extensions, services, or integrations will be. There are many different settings on the Business Connector's configuration and the process that I use was definitely trial and error, as much of this was undocumented material. The process I will describe is the way I have found to be the most reliable.

Lets get started.


1) Creating the Axapta Service Account.
This is basically creating an Axapta User Account and an Employee Account to match it. The important things to remember here are that the account must have the necessary permissions to do whatever work you intend your extension to do. In my case, I needed it to be able to do everything basically and that is still possible while complying with any Sarbanes or Security policy your company adheres to. Team up with with administration and have them use whatever password policy and retention policies are in place. You will just need the resulting configuration file in the next sections.

2) Configure the Axapta Business Connector
Using the Axapta Configuration Tool, and a working configuration, select the Business Connector tab and specify the password of the Axapta Service account you created. Click "Register.." when completed.


3) Register the Business Connector as COM+



4) Configure Component Services.
Navigate to Administrative Tools >> Component Services. Now bring up the Properties panel of the "Navision Axapta Business Connector" component.



Go to the Activation tab and select "Server Application" and also the "Run application as NT Service" check box. Running our business connector as a service allows us to manage it like an NT service, which is crucial to stability in our findings. We need windows to control this COM component because, left to it's own management, it gets real flaky and unreliable. If any of you have found differently, I would love to hear it, but in my world COM is just junk and is just waiting for a chance to blow up and let me down.


5) Set up and Start your new Axapta Business Connector NT Service.
Navigate to Administrative Tools >> Services, or just use the Services snap in that opens with Component Services if your already there. This is where we want to set the startup type and also the security on the service. Open the properties window.

On the General tab, select "Startup Type" Automatic, so that your Business Connector service will start by itself when the server is bounced.


From the "Log On" tab, select "Local System Account". I have used both a real NT service account as well as local system. It seems to work fine with no issues as local system, but your network domain policies may differ from mine. It should work fine, but season to taste for your environment.


Now just start the service and check the Event Log for any errors, there should be none. As an NT service, we can monitor the health of our COM component and also see information about cache clears, logins, and lots more. All from where we manage our other Non Axapta services. Thats the real bonus. We get the stability of an NT service and centralized management. Win Win!

As always, this doesn't mean this is Microsoft's way, or that they endorse it, just that they certainly gave us the options to do it like this. In my book, if they don't say no, you can try it.

At this point, you should have a happy, healthy, Axapta Business Connector NT service, ready to handle requests from our Data Layer, which we will get into in the next segment. Talk to you soon.

Thanks,

H

Extending Axapta 3.0 with C# - Introduction

This topic will begin the process and explanations on how to get Ax 3.0 talking to the outside world. If you want to build cool .NET Web Services, .NET Windows Services, or .NET Forms applications, it starts here.

Before we begin, I will start by saying that none of what I have done, or will do, is meant to imply that Microsoft endorses these solutions, only that they are done using their tool sets. First, and most importantly, no access or integration is done with Axapta by bypassing the Business Connector. The Business Connector is our entry point into Axapta and it enforces the security, rules, constraints, and concurrency models of the core application.

Phew, now that the disclaimer is out of the way we can get started.

When I said I use the business connector as the root of my extension, I didn't mean that this was going to be another example of using it like an old ADO connection or even ADO.Net. There are plenty of sites, including MSDN, that will show you those lame examples. In fact, there is little in common between the ADO Recordset and the Business Connector's IAxaptaRecord objects, other then they are both COM components, therefore unreliable at best. The Business Connector, otherwise known as the COM Connector, needs a lot of help to compete with even the flaky ADO components.

I'm sure you have all seen the example of creating the Axapta COM object in an ASP.NET page. It's trash really and nothing you can trust a billion dollar business solution to, BUT seeing it 2 years ago showed me what I was going to build off of it.

What if I told you the below examples were not only possible, but are the most basic examples of what is possible.

Example 1

private string RetrieveEmployeeNameByID(string emplID)
{
EmplTable emplTable = new EmplTable();
emplTable.RetrieveByEmplID(emplID);

return emplTable.Name;
}

Example 2

private void UpdateEmployeeNameByID(string emplID, string name)
{
EmplTable emplTable = new EmplTable();
emplTable.RetrieveByEmplID(emplID);

emplTable.Name = name;
emplTable.Save();
}

... are you with me now? Would something that simple make your life easier? Does it look like a lot of work under the hood?

Well, if your still reading, chances are Yes to all of the above. I can help you on number 3.

Basically, it's all the inheritance model, in this order.

1) Business Connector
2) Data Layer classes to consume the Business Connector
3) Object Class model to consume the Data Layer
4) Presentation to marshal the Object Model.

That's all there is to it, and also the 4 parts to this series. Remember, this is the most important part of your extension. Number 4 is up to you and it's limitless, but you have to do 1, 2, and 3 right to make it sturdy.

That's the introduction folks. This series implies that you have a working Business Connector installed, a good understanding of the .NET Framework and Visual Studio, and strong C# skills. For this series I will be coding in it, but may be able to help with VB.NET too, I just won't like it.

Thanks all, and stay tuned for Part 1 - The Business Connector.

H

Convergence 2008

Well, another Convergence has come and gone...

New and cool ways to build applications, but unfortunately I'm stuck with 3.0 for the foreseeable future. Lots of new enhancements in Ax 2009 and lots of sessions on them, but I also picked up many frustrations being vented from users about their 3.0 implementations. The usual blocking, performance, and bug related issues that we all know and love on our 3.0's. I heard from so many developers, and business folk alike, that even the simplest of tasks can seem impossible.
Some feel like MS is rolling away into the sunset sometimes, leaving them with an incomplete application and an upgrade path... Not true!

Axapta 3.0 and Visual Studio are the only tools you need to create a limitless amount of new technology solutions targeted at some of the coolest platforms. Maybe not out of the box really, but thats what we developers do. Well, that and complain about it...

How do I know?
This is what my MS team did with no Axapta knowledge before 2 years ago..
Implement 800 seats.
Integrated to 4 external applications.
Integrated ONBASE document imaging and Bar Coding.
Integrated Voice Transcription and Adaptation into Ax Projects.
Developed 3 CF.NET C# applications targeted at over 150 employees on a wireless infrastructure.
Implemented B2B integrations with ADP HR / Payroll Systems and the State of Florida DMV.
Developed a service controller to manage the code promotion process.
Extended the entire Ax data structure into an object model with code generation.
The list is still growing, but basically, we haven't found anything we can't do yet.


Why did I just start this blog now?
I've been busy, but this last Convergence just convinced me there is a huge need for what I know and I think I can help many that are in the same boat we were when our C# shop made the commitment to Microsoft Axapta.

My talents are in the foundational aspect of extending Axapta, but the last 2 years have forced me into knowing quite a bit about x++ and MorphX too.

If anyone has specific question, please email me, but in the meantime, my next posts will be concentrating on showing you how to get Axapta portable through the use of data layers, base, and object models.

You have to do that right, before you can do anything else.

Thanks,


H