Official Notification

As of 17th of February 2009, we received a request from Sage (UK) Limited to cease offering our SageDataObjects software without their consent. We have since stopped offering the SageDataObjects software component for sale.
As of the 5th of November 2011, we have also stopped offering our "Line 50" application for mobile phones. 

C#4, Dynamic Types and Sage

I was reading an article in this month's VSJ, and I was interested to read about C# 4.0's new Dynamic type. This type is C#'s answer to the weakly typed objects of languages like Basic, PHP, etc., This gives some advantages when dealing with .NET interfaces that lay above COM infrastructures, especially the Sage Data Objects library.

Therefore, with dynamics, code such as

SageDataObject50.InvoicePost InvPost;

InvPost = (SageDataObject50.InvoicePost)ws50.CreateObject("InvoicePost");

can become:

dynamic dInvoicePost = ws50.CreateObject("InvoicePost");

The cast to InvoicePost will be done at runtime, and the code will still compile, even if you don't know what the return object of this particular CreateObject method can be cast to.

The downside to this, as in every weakly typed language, is that many errors will not be spotted until runtime, and you may need to go through a process of trial-and-error to see what methods are available on a particular dynamic type.

According to VSJ this method for invoking COM objects should actually be more efficient, and certainly more elegant than using reflection.

Hopefully, I should be able to find a CTP download of C#4 to put together a code example.

Using COM reflection

I read with interest this month's MSDN magazine article on COM reflection. The article provides an example of using reflection to uncover hidden properties and methods of the Speech library, and also explains how the elusive System.__ComObject can give up its goodies, so to speak.

It works fine on the sample Speech API DLL on the site, however, it isn't so hot when it comes to Sage. I tried plugging in some common CLSID's from Sage's inner workings, but it gave up no further information. I then tried passing a System.__ComObject to it, with the following code;

Dim oUnknown As Object = o.GetCompanies("C:\Program Files\Sage\Accounts")
DumpCOMObject(oUnknown)

However, it crashed out when IDispatch::GetTypeInfoCount returned 0. According to MSDN reference this is done to prevent run-time inspection of the DLL, perhaps for security purposes.

So, I guess this is a bit of an anti-pattern when it comes to Sage development, but I hope that this research saves another developer some time when it comes to trying to figure out the million dollar question 'what do I cast this object to now?'

Connecting to SAGE using VBA

I'm often asked if Sage Data Objects can be used with VBA (Visual Basic for Applications), for embedding into excel spreadsheets, and the like. The answer is of course, yes. As with all development with VBA, you loose the luxury of Intellisense, so development is alot harder than when using strongly-typed languages like VB.NET.

But in a sense, it could be considered somewhat easier than languages that, one could say, are too strongly typed, such as C#. With it's refusal to support Duck-typing, that is almost vital in many instances to avoid the use of reflection.

The below example can be used to print a list of customers registered in Sage. The below code snippet should be saved as "customers.vbs", then it can be run at the command line by typing "cscript customers.vbs".


Datapath = "C:\Line50\ACCDATA\"
Set SDOEngine = CreateObject("SDOEngine.5")
Set WS = SDOEngine.Workspaces.Add("Sage")
WS.Connect Datapath, "manager", "", "Sage"
Set srCustomer = WS.CreateObject("SalesRecord")
srCustomer.MoveFirst
Do
WScript.StdOut.Write srCustomer.Fields("Account_Ref") & Vbcrlf
srCustomer.MoveNext()
Loop until srCustomer.IsEof
WS.Disconnect

Posting a stock transaction in VB.NET

In order to manage stock levels within Sage, it is often necessary to record the movement of stock in an out of the company, by way of Stock Transactions.

Stock levels are managed automatically by Sage from the result of Sales or Purchases. However, these are not the only stock movements that can happen. Stock levels can be increased (Adustment In) with deliveries and returns. Stock levels can be reduced (Adustment Out) with non Invoiced sales and losses. Stock levels can also be reset using a stock-take.

The following code creates an Adustment In (i.e. delivery) of 2 units of the first product found. At a cost price of £1.

Dim objStockPost As SageDataObject50.StockPost
objStockPost = ws50.CreateObject("StockPost")
Dim objStockRec As SageDataObject50.StockRecord
objStockRec = ws50.CreateObject("StockRecord")
objStockRec.MoveFirst()
objStockPost.Header.Fields("Stock_Code").Value = objStockRec.Fields.Item("Stock_Code").Value
Dim objStockTran As SageDataObject50.StockTran
objStockTran = objStockPost.Items.Add()
objStockTran.Fields.Item("Stock_Code").Value = objStockRec.Fields.Item("Stock_Code").Value
objStockTran.Fields.Item("Date").Value = DateTime.Now.ToString("MM/dd/yyyy")
objStockTran.Fields.Item("Type").Value = SageDataObject50.StockTransType.sdoAI
objStockTran.Fields.Item("Quantity").Value = CType(2, Double)
objStockTran.Fields.Item("Cost_Price").Value = CType(1, Double)
objStockTran.Fields.Item("Reference").Value = "Test Ref"
objStockTran.Fields.Item("Details").Value = "Test Transaction"
' Update the StockPost object
Dim bFlag As Boolean
bFlag = objStockPost.Update()
If bFlag Then
MessageBox.Show("Success")
Else
MessageBox.Show("Failure")
End If

Disconnecting from Sage

It is always important to disconnect from Sage, once your operations on it are finished, either successfully or in error. otherwise the next person to log in, will be told of an existing login, and this can cause hassle for manual operators.

Some users of the SDO have informed us of an intermittent problem, where the user can be left logged into Sage once a Disconnect() has been called. The brute-force solution to this was to delete the QUEUE.DTA file in the ACCDATA folder, but this is best avoided.

A simple but novel solution was put forward by one of our users, to simply change the GUID in the Connect() parameters to the Username, and from all accounts, this seems to work.

Quirky IsEOF()

The IsEOF() function works a little different to how you'd expect it, and without understanding this function, you may end up missing your last entered customer, or invoice.

IsEOF() is flagged true after reading the second-last record. That is to say, if you have 10 records, after reading record 9, then IsEOF() becomes true. This means you can still read record 10, but no more.

The code example in the download from the website, actually misses this point, so here's how to fix it:

SageDataObject50.SalesRecord srCustomer50 = (SageDataObject50.SalesRecord)ws50.CreateObject("SalesRecord");
srCustomer50.MoveFirst();
bool blnLast = false;
while(true)
{
strDescription = srCustomer50.Fields.Item(ref objDescription).Value.ToString();
this.lvCustomer.Items.Add(strDescription);
srCustomer50.MoveNext();
if (blnLast) break;
blnLast = srCustomer50.IsEOF();
}
break;

The code added is highlighted in brown. Note how IsEOF is flagged before the last record is read.