Hubspot

Introduction

The CData ADO.NET Provider for HubSpot 2016 allows developers to build .NET applications with connectivity to HubSpot using the familiar ADO.NET interfaces. The provider abstracts the underlying data source into tables, views, and stored procedures that can be used to both retrieve and update data.

Connection String

HubSpot uses the OAuth authentication standard. The connection string properties describe the various options that can be used to establish a connection. The connection string can be set to a series of option=value strings separated by semicolons. To specify a location to the database where the tables, views, and stored procedures are located, set the Location property. Caching Data can be enabled by using the appropriate options. Get The OAuth Access Token Using the ClientID and ClientSecret.

Connecting to HubSpot

OAuth requires the authenticating user to interact with HubSpot using the browser. The provider facilitates this in various ways as described below.

Create an App

The CData ADO.NET Provider for HubSpot 2016 is already registered with HubSpot; to display your own information to users when they log in, follow the steps below to register an app: Follow the steps below to obtain the OAuth client credentials, the OAuthClientId and OAuthClientSecret:

  1. Log into your HubSpot developer account using https://developers.hubspot.com/.

  2. Click Create App.

  3. If you will only use the app to connect to your portal, select Private. If other users will use the app to connect to their own portals, select Public.

  4. Click the name of your app.

  5. Enter values to be displayed to users when you connect. These values include the app name, author name, and a description of the app.

    Additionally, if you are making a desktop application, set CallbackURL to http://localhost when you connect. If you are making a Web application, set CallbackURL to a trusted URL where users will return after they authorize your application.

    Note: The ClientId and ClientSecret form this page is used to generate a OAuthAccessToken used in the connection string.

Authenticate to Hubspot from a Web Application

When connecting via a Web application, or if the provider is not authorized to open a browser window, you need to exchange a verifier code for the OAuthAccessToken:

  1. Get the URL to the OAuth endpoint.
  2. Open the URL and log in. After granting permissions to the application, you are redirected back to the callback URL.
  3. The callback URL contains the verifier code in a query string parameter named “code”. Extract the verifier code from the callback URL.
  4. Get OAuthAccessToken. Set OAuthAccessToken to connect to data. To automatically refresh the token, set InitiateOAuth to “REFRESH” and set OAuthRefreshToken to the value returned by GetOAuthAccessToken.

Data Migrations

To Check for the data migrations:

  1. Add a new connection in Loome Integrate as shown.

    • Go to tasks and click on Connections.

    • Add a new connection Using Add New Connection Option.

    • Choose HubSpot Connector from the available Connector Options.

  2. Using the connection string parameters created, Verify the connection using the VERIFY CONNECTION Option in Loome Integrate.

  3. Create a Job Using ADD A JOB option in Loome Integrate, add a data migration task to the job.

  4. Create a New Task by right clicking job list and then Add a New Task Option. If you want to edit an Existing Task Use Edit Task.

  5. Choose a Data Migration Task.

  6. Choose the source and the destination for the migration of data. Name the Task.

  7. Choose the Tables or the data that need to be migrated from the source to the destination. You can Copy all Tables by checking Copy all tables box.

  8. Execute the Job. Check for results and the details of the data migrated in Execution History.

Data Models

The CData ADO.NET Provider for HubSpot 2016 models HubSpot entities as relational Tables, Views, and Stored Procedures. You can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

Tables describe the available tables. The tables are normalized and contain an Id column, which is the primary key.

Name Description
Companies Companies may be kept track of in HubSpot just like contacts. They can be added to, updated, and removed from this table.
CompanyProperties Company properties are the standard and custom pieces of field data that appear in HubSpot.
CompanyPropertyGroups Company property groups in HubSpot offer a way of organizing individual types of properties for companies. Each company property must belong to a property group.
ContactLists Contact lists in HubSpot can be used to group together contacts with similar characteristics.
ContactProperties Contact properties are the standard and custom pieces of field data that appear in HubSpot.
ContactPropertyGroups Contact property groups in HubSpot offer a way of organizing individual types of properties for contacts. Each contact property must belong to a property group.
Contacts Your contacts in HubSpot may be used for marketing campaigns and can be retrieved, inserted, updated, and deleted from this table.
ContactsInList Retrieves the contacts in a given list in HubSpot. New contacts may be added to the list or removed from it. A ListId must be specified to retrieve the current contacts in the list.
DealProperties Deal properties are the standard and custom pieces of field data that appear in HubSpot.
DealPropertyGroups Contact property groups in HubSpot offer a way of organizing individual types of properties for contacts. Each contact property must belong to a property group.
Deals Your deals in HubSpot which may be used for making offers to potential customers.
EmailCampaigns Email campaigns in HubSpot allow you to keep track of and update email marketing campaigns. This table allows you to create, update, and delete your email campaigns in HubSpot.
EmailSubscriptions The subscription types a given email is subscribed to. An email must be specified to return results. Subscriptions may be removed by deleting them.
Engagements Engagements represent any of a number of different types of engagements you have in HubSpot.
Forms Create and manage HubSpot Forms.
Settings Select, insert, update, and delete HubSpot settings.
SocialMediaMessages Create and manage HubSpot social media messages.
Workflows Select and manage HubSpot workflows

Views

Views are similar to tables in the way that data is represented; however, views do not support updates. Data sources that are represented as views are typically read-only data sources. Often, a stored procedure is available to update the data if such functionality is applicable to the data source. Queries can be executed against a view as if it were a normal table, and the data that comes back is similar. Views are composed of columns and pseudo columns.

Name Description
BlogAuthors Retrieve the available blog authors in HubSpot.
BlogPosts Retrieve the available blog posts in HubSpot.
Blogs Retrieve the available blogs in HubSpot.
BlogTopics Retrieve the available blog topics in HubSpot.
Comments Retrieve the available comments from your blog in HubSpot.
ContactListMemberships Returns the list memberships of contacts in HubSpot.
Domains Retrieve the available domains in HubSpot.
EmailCampaignEvents The events associated with an email campaign or a recipient.
EmailSubscriptionTypes A list of email subscription types for a HubSpot hub.
Files Retrieves information about the available files in HubSpot.
Folders Retrieves information about the available folders in HubSpot.
Owners Retrieve the owners in HubSpot.
Pages Retrieve the available pages in HubSpot.
ProspectActivity Select activity events for HubSpot prospects.
Prospects List and filter available HubSpot prospects.
SocialMediaChannels List available social media channels in HubSpot
Templates Retrieve the available templates in HubSpot.
UrlMappings Retrieve the available url mappings in HubSpot

Stored Procedure

Stored Procedures are available to complement the data available from the Data Model. Sometimes it is necessary to update data available from a view using a stored procedure because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.

Name Description
ContactStatistics Basic contact statistics that can be retrieved from HubSpot such as the total number and when the last new contact was created..
GetOAuthAccessToken Gets the OAuth access token from HubSpot.
GetOAuthAuthorizationURL Gets the HubSpot authorization URL. Access the URL returned in the output in an Internet browser. This requests the access token that can be used as part of the connection string to HubSpot.
InsertEngagement Allows the insertion of engagements into HubSpot. Currently there is no good means of retrieving existing engagements via the HubSpot API.
RefreshOAuthAccessToken Refreshes the OAuth access token used for authentication with various HubSpot services.