Import Data from Salesforce to QuickBooks Online with ComponentOne Data Connectors

There are many cases where users would want to import data from Salesforce to QuickBooks Online. This blog will explore how to fetch contacts from Salesforce and import them into QuickBooks Online using ComponentOne Salesforce ADO.NET data connector and QuickBooks Online ADO.NET data connector.

To fetch contacts from Salesforce and import them to QuickBooks Online as customers, we need to consider the following steps:

• Understanding data size and service request limits
• Authenticate to Salesforce
• Schema of Salesforce data
• Fetch data from Salesforce
• Map Salesforce data to QuickBooks Online data
• Authenticate to QuickBooks Online
• Insert data to QuickBooks Online

Now let’s look at each step in detail:

Data Size & Service Request Limits

Since Salesforce and QuickBooks system is available online, there are certain limits on result size and the number of calls for load balancing. This Salesforce document and QuickBooks Online document further explain the limits based on the edition and the error when the limits are exceeded.

Authenticate to Salesforce

Salesforce supports OAuth authentication for external applications that wish to connect to its REST API's. This document explains how to set up an app to enable OAuth.

The following information is required to connect to Salesforce:

• Token endpoint
• API URL
• Client ID
• Client secret
• Salesforce username and password
• Security token

Schema of Salesforce data

The schema of the entity to get data is in Salesforce here. Alternatively, you can get schema information from the connection object of ComponentOne ADO.NET data connector for Salesforce.

For the purpose of this blog, contact data is needed. The following fields are required:

• Title
• Salutation
• FirstName
• LastName
• Id (pk to get AccountName and AccountNumber)
• Email
• Phone
• MobilePhone
• Fax
• MailingStreet
• MailingCity
• MailingState
• MailingPostalCode
• MailingCountry
• MailingLatitude
• MailingLongitude

Here is the schema for the above fields as per Salesforce:

Fetch Data from Salesforce

The first step towards working with the provider is to establish a connection to Salesforce. The DataConnector service library provides you with the C1SalesforceConnection class, just like the ADO.NET DbConnection class. It is used to establish a connection to the data source by creating the connection object and passing the connection string as a parameter to the class constructor. The connection string can be predefined or generated using the C1SalesforceConnectionStringBuilder class.

After initializing the connection object, we start the connection by opening it with the Open() method. Then, create a command object with the CreateCommand() method. Just like working with any DbConnection and DbCommand object, we set the SQL command using command.CommandText and command.ExecuteReader() to obtain a DataReader object to evaluate the results. In our case, we want to store results in a DataTable so DataReader is a perfect choice. The full code is as below:

        static class SalesforceSettings
        {
            public const string OAuthTokenEndpoint = @"https://ap17.salesforce.com/services/oauth2/token";
            public const string Url = @"https://ap17.salesforce.com/services/data/v42.0";
            public const string ClientId = @"xxx";
            public const string ClientSecret = @"xxx";
            public const string Username = @"xxx";
            public const string Password = @"xxx";
            public const string SecurityToken = @"xxx";
        }

        static DataTable GetContactsFromSalesforce()
        {
            string sql = @"select c.FirstName, c.LastName, c.Salutation, c.Title, c.Email, c.Phone, c.MobilePhone, c.Fax,
                            c.MailingStreet, c.MailingCity, c.MailingState, c.MailingPostalCode, c.MailingCountry, c.MailingLatitude, c.MailingLongitude,
                            c.Description, a.Name as AccountName, a.AccountNumber From Contact c left join Account a on c.AccountId = a.Id";

            string salesforceConnection = string.Format("Username={0};Password={1};Security Token={2};OAuth Client Id={3}; OAuth Client Secret={4}; OAuth Token Endpoint={5}; Url={6}",
                SalesforceSettings.Username, SalesforceSettings.Password, SalesforceSettings.SecurityToken, SalesforceSettings.ClientId, SalesforceSettings.ClientSecret,
                SalesforceSettings.OAuthTokenEndpoint, SalesforceSettings.Url);

            using (var con = new C1SalesforceConnection(salesforceConnection))
            {
                con.Open();
                var command = con.CreateCommand();
                command.CommandText = sql;

                var contactsReader = command.ExecuteReader();
                var dataTable = new DataTable();
                dataTable.Load(contactsReader);
                return dataTable;
            }
        }

Map Salesforce Data to QuickBooks Online Data

Use the following table to map fields between QuickBooks Online Customer with Salesforce Contact

Authenticate to QuickBooks Online

The first step towards working with the provider is to establish a connection to the QuickBooks Online data. The DataConnector service library provides you with the C1QuickBooksOnlineConnection class, just like the ADO.NET DbConnection class. It is used to establish a connection to the data source by creating the connection object and passing the connection string as a parameter to the class constructor. The connection string can be predefined or generated using the C1QuickBooksOnlineConnectionStringBuilder class.

Here is a sample code snippet depicting how to generate a connection string and create a connection object:

        static class QBOSettings
        {
            public static string OAuthAccessToken = @"xxx";
            public static string OAuthRefreshToken = @"xxx";
            public const string OAuthClientId = @"xxx";
            public const string OAuthClientSecret = @"xxx";
            public static string CompanyId = "xxx";
            public const string MinorVersion = "xxx";

            public static string ConnectionString => $"Company Id={CompanyId};Use SandBox=true;OAuth Client Secret={OAuthClientSecret};OAuth Client Id={OAuthClientId};" +
                            $"OAuth Access Token={OAuthAccessToken};OAuth Refresh Token={OAuthRefreshToken}; OAuth Token Endpoint=;Minor Version={MinorVersion}";
        }

            using (var conn = new C1QuickBooksOnlineConnection(QBOSettings.ConnectionString))
            {
                conn.OAuthTokenRefreshed += OnOAuthTokenRefreshed;
                conn.Open();
                ……
           }

Insert Data into QuickBooks Online

Using the DataTable returned from the GetContactsFromSalesforce method, we will use C1QuickBooksOnlineDataAdapter to bulk insert data to QuickBooks Online.

C1QuickBooksOnlineDataAdapter inherits DbDataAdapter so it provides all the usual functionalities of a DbDataAdapter.

                C1QuickBooksOnlineDataAdapter dataAdapter = new C1QuickBooksOnlineDataAdapter();
                var insertCmd = "Insert Into Customers(Title, GivenName, FamilyName, CompanyName, PrimaryPhone_FreeFormNumber," +
                    "Mobile_FreeFormNumber, Fax_FreeFormNumber, BillAddr_City, BillAddr_CountrySubDivisionCode, BillAddr_PostalCode, BillAddr_Country, BillAddr_Lat," +
                    "BillAddr_Long, Notes) VALUES (@p1, @p2, @p3, @p4, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15)";

                dataAdapter.InsertCommand = new C1QuickBooksOnlineCommand(conn, insertCmd);
                dataAdapter.InsertCommand.Parameters.Add("@p1", DbType.String, "Salutation");
                dataAdapter.InsertCommand.Parameters.Add("@p2", DbType.String, "FirstName");
                dataAdapter.InsertCommand.Parameters.Add("@p3", DbType.String, "LastName");
                dataAdapter.InsertCommand.Parameters.Add("@p4", DbType.String, "AccountName");
                dataAdapter.InsertCommand.Parameters.Add("@p6", DbType.String, "Phone");
                dataAdapter.InsertCommand.Parameters.Add("@p7", DbType.String, "MobilePhone");
                dataAdapter.InsertCommand.Parameters.Add("@p8", DbType.String, "Fax");
                dataAdapter.InsertCommand.Parameters.Add("@p9", DbType.String, "MailingCity");
                dataAdapter.InsertCommand.Parameters.Add("@p10", DbType.String, "MailingState");
                dataAdapter.InsertCommand.Parameters.Add("@p11", DbType.String, "MailingPostalCode");
                dataAdapter.InsertCommand.Parameters.Add("@p12", DbType.String, "MailingCountry");
                dataAdapter.InsertCommand.Parameters.Add("@p13", DbType.String, "MailingLatitude");
                dataAdapter.InsertCommand.Parameters.Add("@p14", DbType.String, "MailingLongitude");
                dataAdapter.InsertCommand.Parameters.Add("@p15", DbType.String, "Description");
                dataAdapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

                //Need to change row state to added for bulk insert to work
                dataTable.TableName = "Customers";
                foreach (DataRow curRow in dataTable.Rows)
                {
                    curRow.SetAdded();
                }

                dataAdapter.UpdateBatchSize = 100;
                int rowAffected = dataAdapter.Update(dataTable);

That’s it! With ComponentOne Salesforce and QuickBooks Online ADO.NET data connectors, we can manipulate data easily between these common data platforms. See our Desktop DataConnectors Explorer or Web DataConnectors Explorer for more data connector demos.