Umbraco integration with PostgreSQL

We’ve been working on a project over the last few months migrating a website from an older version of Umbraco up to the latest Umbraco 7.  Our work with PostgreSQL has been in two areas: firstly creating a customer Management Information System which provides real time reports of sales from the company’s PostgreSQL ERP database; and secondly integrating Umbraco Membership with data from the Customer Accounts data in PostgreSQL.

This has been somewhat of a challenge as the tooling on .NET for PostgresSQL isn’t great.  Throughout the rest of the project we’ve been working on home turf using Entity Framework to query a SQL Server database.  But we found early on that while it is possible to use Entity Framework with PostgreSQL the results weren’t performant. Instead we’ve chosen to use good old fashioned ADO.NET using the Npgsql driver for PostgreSQL.

Once we got the basic querying up and running to display reports - we moved on to enriching our Umbraco Membership implementation with Customer Account data. You will have read in previous posts that we have experience of customising Umbraco membership, especially in terms of SAML based single sign on.  

In this instance all Membership data is mastered in Umbraco but we need additional customer account data.  On this project new customers can sign up via a registration form, but Editors can mark them as trade customers to gain access to additional information about inventory and also to access the MIS reporting system.

So we developed a series of tools to enable Editors to associate an Umbraco member with a trade account from the PostgreSql database.  We’ve created a series of custom data editors for the Umbraco Membership page to make it easy for editors to view customer account codes and associate them with Members. Once the link is made, we have added code so that when the Member is saved we retrieve account information onto their Umbraco member profile.  We then use Amazon Simple Queueing Service to enable the ERP system to inform us of changes to the account data, to keep the profile up to date.

Under the hood we extended the Umbraco Membership Helper object to help manage the extra data in code. We achieved this by developing the codebase in two key areas. Firstly we created a simple POCO member that represented our member type in the Umbraco back office. This enabled us to pass round a strongly typed member to different parts of the application which made data retrieval that bit easier. Secondly we developed a bunch of extension methods on the helper class which were designed to bind facts about the member to our POCO member.

Lastly we’ve developed a series of back end membership controls to make it easy for Editors to search and maintain member profiles as Members can be listed by Trade Account code.

This is just another small example of the work we do extending the reach of Umbraco, exposing data in internal systems via Umbraco and integrating with back end systems.  If you’re about to start an Umbraco project and would like to work with an agency that knows Umbraco integration backwards and forwards then come and speak to us.