Helpful Lessons & Snippets, , ,

I recently completed a small project for a client that involves printing barcodes (easy), but required exporting sales order data from their Sage accounting software, MAS 90 (not so fun). Specifically, I had to write a .NET application to export the data, which queried the MAS 90 database with ADO.NET and ODBC. So, I am publishing some of those lessons in case it helps someone else.

Configuring the DSN

You can query the MAS 90 database with ODBC, but you will need to configure a System DSN. Installing the Sage MAS 200 Desktop will place the “MAS 90 4.0 ODBC Driver” files on your system.

If you are running a 32-bit version of Windows, launch “Data Sources (ODBC)” from the Administrative Tools menu. If you are running a 64-bit version (like I am), you will need to use the 64-bit version of the ODBC manager, which is located at:

%windir%\SysWOW64\odbcad32.exe

Once the ODBC manager is running, click the System DSN, then the Add button, select “MAS 90 4.0 ODBC Driver” , and click finish. Note: If you do not run the appropriate version of the ODBC manager (32- versus 64-bit), you will not see the installed MAS driver.

Writing the Queries

I love SQL Server and T-SQL, and I’ve been using it for years. The syntax, the useful functions… You don’t get all that stuff in this scenario. Sorry. You have to stick with ISO standard SQL.

I found the best way to build and test my SQL statements was with Visual Studio Server Explorer, since you can use the query builder, and the System DSN to connect and issue those queries. Just make sure they work before trying them in your application!

Connecting with ADO.NET

Once the DSN is created, you will use it in your connection string in your App.config or Web.config:

  <connectionStrings>
    <add name="MAS90Database" 
         connectionString="DSN=MyMAS90DSN" 
         providerName="System.Data.Odbc" />
  </connectionStrings>

I should point a few things here:

1.) If you have specified the user name, password, and company in the DSN settings, you DO NOT have to specify them in the connection string; if they are not specified in the DSN settings, then you MUST specify them in the connection string:

connectionString="DSN=MyMAS90DSN;UID=<username>;PWD=<password>;Company=<company>;"

2.) The provider is “System.Data.Odbc”, which instructs ADO.NET to use the ODBC libraries instead of the default SQL Server libraries.

Putting it All Together

In your .NET application, your database connection options are pretty flexible. You can use ADO.NET or the Enterprise Library Data Access block (but I haven’t tried Entity Framework). You can also execute the queries and return DataSet objects and IDataReader objects!

Note: If you are compiling on a 64-bit machine, you will probably have to compile to target “x86”, instead of “AnyCPU”. This has to do with the MAS drivers being 32-bit. I first tried executing code that was compiled with “AnyCPU” on the destination machine, and received an ODBC exception. Once I compiled with “x86”, everything worked.

Hopefully this helps someone out there!