Discover how a bimodal integration strategy can address the major data management challenges facing your organization today.
Get the Report →Provide OData Services of NetSuite Data from a WCF Application
In this article, we will demonstrate the process of generating an OData feed for NetSuite data by developing a WCF Service Application.
The CData ADO.NET Provider for NetSuite enables you to rapidly develop service-oriented applications using the Windows Communication Foundation (WCF) framework, providing NetSuite data data to OData consumers. This article guides you through creating an entity data model for connectivity and a WCF Data Service to expose OData services. You can then consume the feed with various OData clients, such as Power Pivot or applications using the CData ADO.NET Provider for OData.
About NetSuite Data Integration
CData provides the easiest way to access and integrate live data from Oracle NetSuite. Customers use CData connectivity to:
- Access all editions of NetSuite, including Standard, CRM, and OneWorld.
- Connect with all versions of the SuiteTalk API (SOAP-based) and SuiteQL, which functions like SQL, enabling easier data querying and manipulation.
- Access predefined and custom reports through support for Saved Searches.
- Securely authenticate with Token-based and OAuth 2.0, ensuring compatibility and security for all use cases.
- Use SQL stored procedures to perform functional actions like uploading or downloading files, attaching or detaching records or relationships, retrieving roles, getting extra table or column info, getting job results, and more.
Customers use CData solutions to access live NetSuite data from their preferred analytics tools, Power BI and Excel. They also use CData's solutions to integrate their NetSuite data into comprehensive databases and data warehouse using CData Sync directly or leveraging CData's compatibility with other applications like Azure Data Factory. CData also helps Oracle NetSuite customers easily write apps that can pull data from and push data to NetSuite, allowing organizations to integrate data from other sources with NetSuite.
For more information about our Oracle NetSuite solutions, read our blog: Drivers in Focus Part 2: Replicating and Consolidating ... NetSuite Accounting Data.
Getting Started
Create the OData Service
Follow the steps below to create a WCF service application that will provide connectivity to NetSuite data via OData.
- Open Visual Studio and create a new project. Select the WCF Service Application template.
- Delete the autogenerated IService.cs and Service1.svc.
- Install Entity Framework 6:
Use the Package Manager Console in Visual Studio to install the latest version of Entity Framework. Run the following command to download and install Entity Framework automatically:
Install-Package EntityFramework
- Register the Entity Framework provider:
- Add the following provider entry in the "providers" section of your App.config or Web.config file. This section should already exist if the Entity Framework installation was successful.
<configuration> ... <entityFramework> <providers> ... <provider invariantName="System.Data.CData.NetSuite" type="System.Data.CData.NetSuite.NetSuiteProviderServices, System.Data.CData.NetSuite.Entities.EF6" /> </providers> </entityFramework> </configuration>
- Add a reference to System.Data.CData.NetSuite.Entities.dll, located in lib/4.0 in the installation directory.
- Build the project to complete the setup for using EF6.
- Add the following provider entry in the "providers" section of your App.config or Web.config file. This section should already exist if the Entity Framework installation was successful.
- Click Project -> Add New Item -> ADO.NET Entity Data Model.
- In the Entity Data Model wizard that is displayed, select the 'EF Designer from Database' option.
- In the resulting Choose Your Connection dialog, click New Connection.
In the Connection properties dialog, select the CData NetSuite Data Source and enter the necessary credentials.
A typical connection string is below:
Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;
The User and Password properties, under the Authentication section, must be set to valid NetSuite user credentials. In addition, the AccountId must be set to the ID of a company account that can be used by the specified User. The RoleId can be optionally specified to log in the user with limited permissions.
See the "Getting Started" chapter of the help documentation for more information on connecting to NetSuite.
- Select NetSuite tables and views that you want OData clients to access.
- Click Project -> Add New Item -> WCF Data Service.
Specify the data source class and configure access to the new WCF Data Service. In the example below, the Access Rule for the entities is set to All. This means that any user will be able to read and modify data.
using System; using System.Collections.Generic; using System.Data.Services; using System.Data.Services.Common; using System.Linq; using System.ServiceModel.Web; using System.Web; namespace NetSuiteService{ public class NetSuiteDataService : DataService<NetSuiteEntities> { public static void InitializeService(DataServiceConfiguration config) { config.SetEntitySetAccessRule("*", EntitySetRights.All); config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3; } } }
- Run the project. Applications that support OData can now access the Salesforce data and reflect any changes. You can access the feed in your browser. The feed will resemble the following:
Consume the OData Service from Power Pivot
You can now use the service from any OData client; for example, Excel Power Pivot.
- Open Excel and click on the Power Pivot Window button.
- A new pop-up will appear. Select the option From Data Feeds.
- In the resulting Table Import Wizard, enter the OData URL. For example, http://localhost:12449/NetSuiteDataService.svc/.
- After connecting to the OData service, click the Next button at the bottom of the window.
- A table listing of the available tables will appear in the next window of the wizard. Select which tables you want to import and click Finish.
- Click Close to import the data in Power Pivot.