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