QuickBooks with .NET

How to integrate Quick Books with .Net application:

If you are a .NET developer and you’ve got an assignment to integrate Quick Books with a .NET C# application then I guess there is something iniquitous you did in the past which goddess Fortuna didn’t like at all and now she is up to your retribution. The situation gets worse since there is not much help on web. So for those folks who consider themselves as “R n’ D champions” and got this assignment; you guys are in big trouble. Experience of integration of C# application with Quick Books was very challenging to me; and now here I am presenting in front of you what I achieved from spending nights after nights, head scratching and wall banging efforts.

Introduction & Architecture:

So let’s start by understanding what are the prerequisites of this project are:

· Quick Books, obviously, make sure it’s installed with multi-user license.

· Quick Books SDK, available here (http://developer.intuit.com/) search for Download the QBSDK 7.0 or latest.

Now let’s quickly go through the design of my small application which will talk to Quick Books. Basically the purpose of this application is to show you how a .NET application can request and retrieve some information e.g. list of vendors from QB. Likewise, if .NET application wants to insert some data e.g. a vendor again; how it can be done. Following is the design architecture of the application we are about to build:

Let me just quickly explain the above architecture and then we can move forward towards coding on this design. Presentation layer consists of a win form designed and developed in C#. Through this form we will perform our CURD (Create, Update, Read and Delete) operations. This form instantiates business objects Vendor, which is present in the Vendor.cs file. Common.cs contains some other helper business objects and classes used in application. Right besides Business Layer, we have Data Access Layer which includes RequestBroker.cs. This class does most of the data transactions with QuickBooks. Finally, quick book’s company file is the data layer. After quickly going through the simple design architecture let’s see how things work here. Foremost, let’s take a quick look at our vendor class which is a business object for Vendor DB/QB entity.

Business Layer:

[Serializable]

public class Vendor

{

private string name;

private string companyName;

private string contactName;

private string phoneNumber;

private string faxNumber;

private bool isActive;

private string type;

public string Name

{

get { return this.name; }

set { this.name = value; }

}

public string Type

{

get { return this.type; }

set { this.type = value; }

}

public string CompanyName

{

get { return this.companyName; }

set { this.companyName = value; }

}

public string ContactName

{

get { return this.contactName; }

set { this.contactName = value; }

}

public string PhoneNumber

{

get { return this.phoneNumber; }

set { this.phoneNumber = value; }

}

public string FaxNumber

{

get { return this.faxNumber; }

set { this.faxNumber = value; }

}

public bool IsActive

{

get { return this.isActive; }

set { this.isActive = value; }

}

public Vendor()

{

this.name = this.companyName = this.contactName =

this.phoneNumber = this.faxNumber = string.Empty;

this.isActive = true;

this.type = “Service”; // Lets set some default vendor type

}

public Vendor(string name) : this()

{

this.name = name;

}

#region Fill Request Methods

public void FillViewRequest(IVendorRet vendorReturn, bool completeInformation)

{

if (vendorReturn.Name != null)

this.name = vendorReturn.Name.GetValue();

if (completeInformation)

{

if (vendorReturn.CompanyName != null)

this.companyName = vendorReturn.CompanyName.GetValue();

if (vendorReturn.Phone != null)

this.phoneNumber = vendorReturn.Phone.GetValue();

if (vendorReturn.Fax != null)

this.faxNumber = vendorReturn.Fax.GetValue();

if (vendorReturn.Contact != null)

this.contactName = vendorReturn.Contact.GetValue();

if (vendorReturn.IsActive != null)

this.isActive = vendorReturn.IsActive.GetValue();

if (vendorReturn.VendorTypeRef != null && vendorReturn.VendorTypeRef.FullName != null)

this.type = vendorReturn.VendorTypeRef.FullName.GetValue();

}

}

public void FillViewRequest(IVendorRet vendorReturn)

{

this.FillViewRequest(vendorReturn, true);

}

public void FillAddTypeRequest(ref IVendorTypeAdd vendorTypeAddRequest)

{

vendorTypeAddRequest.Name.SetValue(this.type.ToString());

vendorTypeAddRequest.IsActive.SetValue(true);

}

public void FillAddRequest(ref IVendorAdd request)

{

if (this.name.Length > 0)

{

request.Name.SetValue(this.name);

request.NameOnCheck.SetValue(this.name);

}

if (this.companyName.Length > 0)

{

request.CompanyName.SetValue(this.companyName);

request.NameOnCheck.SetValue(this.companyName);

}

if (this.phoneNumber.Length > 0)

request.Phone.SetValue(this.phoneNumber);

if (this.faxNumber.Length > 0)

request.Fax.SetValue(this.faxNumber);

if (this.contactName.Length > 0)

request.Contact.SetValue(this.contactName);

request.IsActive.SetValue(this.isActive);

request.VendorTypeRef.FullName.SetValue(this.type);

}

#endregion

}

Vendor class has some basic private data members and their corresponding public properties like name, phone, fax etc, one default constructor and an overloaded constructor with name parameter. For now, don’t worry about the coding done in Fill Request Methods region; I’m going to explain this region in short. Now let’s jump to Common.cs and see what are other common classes that I’ve made and how do they work.

In Common.cs, first we have two enums:

public enum QBOperationResult

{

Failed = 0,

Succeeded,

Warning,

}

QBOperationResult will tell us the result of any operation we’ll do with QB, e.g. if we send a request for getting the list of all the vendors in the QB file, either the request will be granted successfully or granted with some warning or completely denied. To fetch the correct response from QB, this enum will used. Next is:

public enum QBSortDirection

{

Ascending = 0,

Descending = 1,

}

Let’s suppose we get the list of 100 vendors from QB file, now what column we want to sort this list on and in what direction? QBSortDirection enum will be used in sorting the list in ascending or descending order. Next comes:

[Serializable]

public class QBOperationStatus

{

private string inputParamsXML;

private QBOperationResult status;

private string msg;

private string methodName;

private int spErrorID;

private string spName;

private string spErrorText;

private XmlDocument xmlDoc;

public string InputParamsXML

{

get { return inputParamsXML; }

set { inputParamsXML = value; }

}

public QBOperationResult OperationResult

{

get { return status; }

set { status = value; }

}

public string Message

{

get { return msg; }

set { msg = value; }

}

public int SPErrorID

{

get { return spErrorID; }

set { spErrorID = value; }

}

public string MethodName

{

get { return methodName; }

set { methodName = value; }

}

public string SPName

{

get { return spName; }

set { spName = value; }

}

public string SPErrorMessage

{

get { return spErrorText; }

set { spErrorText = value; }

}

public XmlDocument XmlDoc

{

get { return this.xmlDoc; }

}

public QBOperationStatus()

{

this.spErrorText = this.spName =

this.methodName = this.msg = string.Empty;

this.status = QBOperationResult.Failed;

this.xmlDoc = new XmlDocument();

}

public QBOperationStatus(QBOperationResult operationResult) :

this()

{

this.status = operationResult;

}

}

Most of the features, data members and public properties of this class are unused at this point but kept of later version in which I’ll show how to migrate data to and from QB and SQL Server database via stored procedures; so most of the sp*** data members will be used at that time. Fundamental purpose of this class is to compose QBOperationResult enum and get the status of any QB transaction. Next is the turn for:

[Serializable]

public class ComboBoxItem

{

private string _text;

private int _value;

public string Text

{

get { return this._text; }

set { this._text = value; }

}

public int Value

{

get { return this._value; }

set { this._value = value; }

}

public ComboBoxItem(string text, int value)

{

_text = text;

_value = value;

}

public override string ToString()

{

return _text;

}

}

This simple class is used to instantiate combo box items and then to access their Text and Value properties. Last in this file is:

[Serializable]

public class QBList<Entity> : CollectionBase

{

public Entity this[int index]

{

get { return (Entity)List[index]; }

set { List[index] = value; }

}

public int Add(Entity value)

{

return (List.Add(value));

}

public int IndexOf(Entity value)

{

return (List.IndexOf(value));

}

public void Insert(int index, Entity value)

{

List.Insert(index, value);

}

public void Remove(Entity value)

{

List.Remove(value);

}

public bool Contains(Entity value)

{

return (List.Contains(value));

}

public QBList<Entity> Clone()

{

QBList<Entity> list =

(QBList<Entity>)this.MemberwiseClone();

return list;

}

public Entity GetEntity(string keyName, object keyValue)

{

if (keyName.Length == 0)

return default(Entity);

Hashtable htFromList = GetHashTable(keyName);

if (htFromList.Count > 0)

return (Entity)htFromList[keyValue];

return default(Entity);

}

public Hashtable GetHashTable(string keyColumnName)

{

Hashtable htFromList = new Hashtable();

if (keyColumnName.Length != 0)

{

foreach (Entity entity in this.List)

{

PropertyInfo propertyInfo = typeof(Entity).GetProperty(keyColumnName);

if (null != propertyInfo)

{

Object propertyValue = propertyInfo.GetValue(entity, null);

if (null != propertyValue && !htFromList.ContainsKey(propertyValue))

htFromList.Add(propertyValue, entity);

}

}

}

return htFromList;

}

}

Instead of using Arrays or DataTables, I decided to make my own class, similar to Generic List class, derived from CollectionBase to use enriched methods of the parent class, plus my own customized functionality which suits current projects needs. Now before if move to the brain of the application i.e. RequestBroker.cs, I would prefer to explain how UI is working in this application. In that, first comes view vendor list screen:

Presentation Layer:

In this form I simply got a complete list of vendors present in the QB company file. Let’s take a look at the code responsible for that, you’ll be surprised to see the size of the code:

• The application name you add should be 100% same as the application name you give in your C# program.
• Select Ok and then close the QuickBooks.
• Open it again and then go to File menu and select Switch to Multi-user Mode.
• Now your company file is ready for external application access.
• Don’t close QB and try to test you application. QuickBooks will display a certificate dialog box when your application will first time attempt to access the company file. Select Allow in this dialog box, this will register your application’s name in QB for future access.

Note: Due to different version of Quickbooks, some programmers can’t see an “Add Application” button in above mentioned dialogue box. If you can’t see such a button, try skipping this step and move forward. Try running your .NET application while QB is still running, if connection is successful, QB will display a certificate for admin to approve. Approve that certificate and this will add your application in this list too.

So that’s pretty much it is. I hope code snippets and their line-by-line explanations and screenshots will help some programmer to find some solution to his/her assignment. If it does then please don’t forget to give me your feedback on this article.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Configuration;

namespace prjQuickBookIntegration

{

public partial class frmViewVendorList : Form

{

public frmViewVendorList()

{

InitializeComponent();

}

// Line numbers are marked for understanding the code and not the part of actual code

private void frmViewVendorList_Load(object sender, EventArgs e)

{

01 if (ConfigurationSettings.AppSettings[“QBFilePath”].Length > 0)

{

02 RequestBroker broker =

new RequestBroker(ConfigurationSettings.AppSettings[“QBFilePath”]);

03 QBOperationStatus opStatus = new QBOperationStatus();

04 this.grdVendors.DataSource = new QBList<Vendor>();

05 QBList<Vendor> qblVendor = broker.GetVendorList(ref opStatus, true);

06 this.grdVendors.DataSource = qblVendor;

07 if (opStatus.OperationResult == QBOperationResult.Succeeded)

08 this.txtResults.Text = qblVendor.Count.ToString() +

” records retrieved successfully.”;

09 else

10 this.txtResults.Text =

“Vendors could not be retrieved successfully.\r\n” +

opStatus.Message;

}

}

}

}

This is one of the advantages of having a multi-tiered logic of your application that most of the complex part of the application is divided and scattered among different tiers and code looks a lot simple. The first line (line 01) of frmViewVendorList_Load method is checking if app.config entry for the company file path is given or not. App.config entry looks something like this:

<add key=“QBFilePath“ value=“D:\shared\Wisdom.QBW“/>

After config entry is found, I’m instantiating the RequestBroker class by calling the constructor which takes file path as parameter (line 02). Third line (line 03), instantiates our QBOperationStatus class so that it can be used in the QB operation about to be performed next. After that (Line 04), I’m assigning an empty QBList of type Vendor to my grid control to erase all the previous data, if any, present in the grid. In design time I added columns in grdVendors; these columns point to different properties of the vendor class, see screenshot below:

Line 05, the heart of this method; calls the RequestBroker’s GetVendorList method and assigns the result to the QBList type list declared locally in this method. Line 06, sets the data source property of the grid to show the result set on the screen. Line 07 and onward are just the finishing touch of the method, checking the operation status, whether the operation was performed successfully or not. If the operation was, fortuitously, successfully performed then you’ll see the message in the text box below, with the number of vendors returned. In other case, however, you’ll get a message of failed operation along with its reason.

Data Access Layer:

I think now is the right time to see our RequestBroker class party-by-part. Request Broker class composes a struct called QBVersion. This structure is made basically for keeping the name of the country where the Quick book is installed, major and minor versions of the Quick book. The structure looks something like:

public struct QBVersion

{

public string country;

public short majorVersion;

public short minorVersion;

}

At the top of the RequestBroker.cs import some namespaces as follows:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.IO;

using Interop.QBFC7;

To use Interop.QBFC7 you need to add references of two interop libraries and your references folder should look like this:

That’s how RequestBroker class starts:

public class RequestBroker

{

public static QBVersion QBVersion;

public static string CompanyFilePath;

public static string ApplicationName;

private QBSessionManager sessionManager;

private StringBuilder requestBrokerLog = new StringBuilder();

private string logFilePath;

public string LogFilePath

{

get { return this.logFilePath; }

set { this.logFilePath = value; }

}

I’ve kept QBVersion, CompanyFilePath and ApplicationName as static because an application can access only one QuickBook file at a time thus all the instances of this class can easily share the information about Quick book and the company file. CompanyFilePath is the actual physical path of the quick book file through which data will be sent and received back and forth to the our application. ApplicationName is name of our application. The exact same name will be registered at QuickBook telling that our [application name] application needs to share some data with it and it should grant whenever there is a request to view or insert data from this application. SessionManager is the class which will open and close the sessions between our application and QuickBooks. For each transaction we need to create and then close such sessions. RequestBrokerLog and logFilePath are the variables used to log all the activities and transactions and their results done by our application with QB. Following are three overloaded constructors used:

/// <summary>

/// Default constructor sets the country, version and application name fields of the class

/// </summary>

public RequestBroker()

{

RequestBroker.QBVersion.country = “US”;

RequestBroker.QBVersion.majorVersion = 6;

RequestBroker.QBVersion.minorVersion = 0;

sessionManager = new QBSessionManager();

CompanyFilePath = @”D:\shared\Wisdom.QBW”; // Default path to company file

ApplicationName = “WITQBInterface”; // Default application name

}

public RequestBroker(string filePath)

: this()

{

CompanyFilePath = filePath.Length > 0 ? filePath.Trim() : string.Empty;

}

public RequestBroker(string country, short majorVersion, short minorVersion, string filePath, string applicationName)

{

if (country.Length > 0) RequestBroker.QBVersion.country = country;

if (majorVersion > 0) RequestBroker.QBVersion.majorVersion = majorVersion;

if (minorVersion > 0) RequestBroker.QBVersion.minorVersion = minorVersion;

sessionManager = new QBSessionManager();

if (filePath.Length > 0 && File.Exists(filePath)) CompanyFilePath = filePath;

if (filePath.Length > 0 && File.Exists(filePath)) this.logFilePath = filePath.Substring(0, filePath.LastIndexOf(“\\”) + 1) + “QBInterface.log”;

if (applicationName.Length > 0) ApplicationName = applicationName;

}

As you might have noticed our Vendor form uses only the second constructor which further calls the default constructor; the third constructor has been written for future use. More or less, all the constructors are performing the same task i.e. initializing and instantiating the data members of the class. Now comes the heart of this class; the method that actually gets data from Quick books.

/// <summary>

/// This method gets all the active vendors from QB matching given criteria

/// </summary>

/// <returns></returns>

// Line numbers are marked for understanding the code and not the part of actual code

public QBList<Vendor> GetVendorList(ref QBOperationStatus opStatus, bool completeInformation)

{

01 QBList<Vendor> qblVendor = new QBList<Vendor>();

try

{

02 opStatus = BeginSession();

03 if (opStatus.OperationResult == QBOperationResult.Succeeded)

{

04 IMsgSetRequest messageSet = CreateRequestMsgSet();

05 IVendorQuery vendorQuery = messageSet.AppendVendorQueryRq();

06 vendorQuery.ORVendorListQuery.VendorListFilter.ActiveStatus.SetValue(ENActiveStatus.asActiveOnly);

07 IMsgSetResponse responseSet = this.GetResponse(messageSet);

08 this.sessionManager.CloseConnection();

09 opStatus = GetResponseStatus(responseSet);

10 IResponse response;

11 ENResponseType responseType;

12 if (responseSet == null || responseSet.ResponseList == null)

opStatus.Message += “\r\n” + “responseSet is null.”;

else

{

13 for (int i = 0; i < responseSet.ResponseList.Count; i++)

{

14 response = responseSet.ResponseList.GetAt(i);

if (response == null || response.Detail == null)

continue;

15 if (response.Type == null)

opStatus.Message += “\r\n” + “responseType is null.”;

else

{

16 responseType = (ENResponseType)response.Type.GetValue();

17 if (responseType == ENResponseType.rtVendorQueryRs)

{

if (response.Detail == null)

opStatus.Message += “\r\n” + “response Detail is null.”;

else

{

18 IVendorRetList vendorList = (IVendorRetList)response.Detail;

19 for (int vendorIndex = 0; vendorIndex < vendorList.Count; vendorIndex++)

{

20 IVendorRet vendor = (IVendorRet)vendorList.GetAt(vendorIndex);

if (vendor != null)

{

21 Vendor iVendor = new Vendor();

22 iVendor.FillViewRequest(vendor, completeInformation);

23 qblVendor.Add(iVendor);

opStatus.Message += “\r\n” + “Vendor retrieved successfully.” + iVendor.Name;

}

}

}

}

}

}

}

}

}

catch (Exception ex)

{

opStatus.Message += “\r\n\r\n” + ex.Message;

}

finally

{

24 this.sessionManager.EndSession();

}

25 return qblVendor;

}

Now let’s quickly skim through this method line by line. Line 01 simply creates a QBList of vendors. Line 02 starts a session with QB. Method BeginSession() returns the result of session open attempt; if the session is successfully opened it will return our OperationStatus as successful; line 03 checks the same. Line 04 instantiates a message request object, this class is imported from namespace QBFC7. This object tells QB what kind of operation is about to be performed in this session. After message request object has been successfully instantiated, we’ll use this object to create a vendor query object with line 05. Line 06 sets the filter of the vendor query we just created. Currently I’m setting a very simple filter i.e. all the active vendors will be retrieved. Line 07 calls QB to actually get the list of vendors. responseSet object now contains all the vendor list. Line 08 closes the connection after the response has been received successfully. Line 09 gets the response status. Lines 10 and 11 are basically just references of IResponse and ResponseType and will be explained in short. Line 12 checks whether we received anything from QB in response to our query or not. If nothing was returned then we’ll simply display “responseSet is null.” Otherwise we’ll proceed to else block. In line 13 we’ll loop through each and every item in responseList object. Each item of responseList is a Vendor object, all we need to do is convert QB’s vendor object into our business vendor object. In line 14 we are getting the ith item of the list and seeing if it is null or not; if it’s null then just skip the rest of the part of the loop and iterate to next item. Line 15 is checking if the responseType has been set properly, if yes then in Line 16 I’m getting the response type, which according to my calculations should be VendorType. Line 17 is again checking whether the response type is correctly set or not. Too defensive programming, isn’t it? Trust me, its worth when you are dealing with QB. In line 18 I finally get the list of the vendors in IVendorRetList object. Now I’m ready to loop through this list and see how many vendors I’ve got. That’s what I’m doing on line 19. Interop QBFC7, is enriched with classes that can work for any kind of .NET programmer’s needs. For example IVendorRetList is for keeping the returned list of vendors from QB and to keep one vendor object at a time we have IVendorRet object. In line 20 I’m getting one vendor object from the list and in next line I’m checking if it’s not null then I’m good to get that interop library object into my business object. If I get a solid IVendorRet object in line 20 then in line 21 I instantiate my business object and in line 22 I call business object’s FillViewRequest (will be explained later) method to get all the information from IVendorRet object to my Vendor business object. In line 23, after I have the vendor object fully populated I can add it in my custom list object. After setting “successful message”, I’m closing the session in finally block in line 24 and line 25 returns the successfully created vendor list to form to be displayed in grid.

As I promised that I’ll explain FillViewRequest of vendor object so let’s see line-by-line how this method is working. Vendor class is populated with two overloaded FillViewRequest methods.

public void FillViewRequest(IVendorRet vendorReturn)

{

this.FillViewRequest(vendorReturn, true);

}

This method simply calls its big brother with a hard coded true parameter. And the big brother is as follows:

public void FillViewRequest(IVendorRet vendorReturn, bool completeInformation)

{

01 if (vendorReturn.Name != null)

02 this.name = vendorReturn.Name.GetValue();

03 if (completeInformation)

{

04 if (vendorReturn.CompanyName != null)

05 this.companyName = vendorReturn.CompanyName.GetValue();

if (vendorReturn.Phone != null)

this.phoneNumber = vendorReturn.Phone.GetValue();

if (vendorReturn.Fax != null)

this.faxNumber = vendorReturn.Fax.GetValue();

if (vendorReturn.Contact != null)

this.contactName = vendorReturn.Contact.GetValue();

if (vendorReturn.IsActive != null)

this.isActive = vendorReturn.IsActive.GetValue();

if (vendorReturn.VendorTypeRef != null && vendorReturn.VendorTypeRef.FullName != null)

this.type = vendorReturn.VendorTypeRef.FullName.GetValue();

}

}

The purpose of this method is to take IVendorRet object as a parameter and then convert and return it into our business object. For this purpose at line 01 I’m checking if Name property is not null (defensive programming, as usual), if not then I’m get its value in line 02. Line 03 is checking if complete information was requested then perform the similar procedure as we did in line 01 and 02 to get the values of all other properties. Line 04 and 05 is another example of doing the same.

We are pretty much done with understanding how this application works; the only thing that is left in this is how to add a new vendor in QB through our application and that I plan to explain in very near future. Now is the time I dwell upon the issues that you might face while setting up QB company file for giving rights to an external application to make CURD (Create, Update, Read and Delete) transactions to it. So before you download the code and start testing the application, there are a couple of settings in QB you need to make. For an external application to be successfully able to make CURD transactions these are the steps you need to follow:

• Open company file in QB.
• Go to File menu and select Switch to Single-user Mode as follows

• Next thing you need to do is open Preferences dialog box by selecting View menu and then preferences.
• In preferences dialog box select Integrated Applications.
• In this dialog box select Company Preferences tab.
• Uncheck both the checkboxes and Add an application name as follows

• The application name you add should be 100% same as the application name you give in your C# program.
• Select Ok and then close the QuickBooks.
• Open it again and then go to File menu and select Switch to Multi-user Mode.
• Now your company file is ready for external application access.
• Don’t close QB and try to test you application. QuickBooks will display a certificate dialog box when your application will first time attempt to access the company file. Select Allow in this dialog box, this will register your application’s name in QB for future access.

Note: Due to difference between versions of Quickbooks, some programmers don’t see an “Add Application” button in above mentioned dialogue box. If you can’t see such a button, try skipping that step and move forward to next steps. Try running your .NET application while QB is still running, if connection is successful, QB will display a certificate for admin to approve. Approve that certificate and this will add your application in the list of Approved Applications in your Quickbooks.

So that’s pretty much it is. I hope code snippets and their line-by-line explanations and screenshots will help some programmer to find some solution to his/her assignment. If it does then please don’t forget to give me your feedback on this article.

P.S. After reading this article if you are interested in knowing more about QuickBooks integration, and if you are interested in seeing how to add data in QuickBooks through .NET application, click here.