Integrate Salesforce Data Using ADO.NET
The ComponentOne 2020v2 release enhances the number of datasources supported by .NET Standard service library DataConnectors, which provides a set of connectors that can connect to different types of data sources. You can now connect to four data sources, namely OData, Microsoft Dynamics 365 Sales, Salesforce Data, and Kintone using an ADO. NET provider for each type. These providers have the same ADO. NET architecture as the native .NET data providers for SQL Server and OLEDB. Therefore, they make accessing data easier by letting you follow a similar set of classes available in the .NET architecture. These providers also have advantageous features such as authentication, caching, and SQL/LINQ query support. To add up to these features, the DataConnector also includes an Entity Framework (EF) Core provider for each type of datasource, which makes this library useful even when working with Entity Framework Core.
This blog will help you understand how to use the ADO. NET provider for Salesforce to connect to the Salesforce data and even explain the basic features, including authentication, querying, and caching. To get started, you will need to install the C1.DataConnector and C1.AdoNet.Salesforce packages from NuGet:
You can also download and install the ComponentOne DataConnectors service component from here and find the installed samples at the following location: 'C:\Users\xxxx\Documents\ComponentOne Samples\ServiceComponents\DataConnector.'
Establishing a Connection to the Salesforce Data
The first step towards working with the provider is to establish a connection to the Salesforce data. The DataConnector service library provides you with the C1SalesforceConnection class, just like the ADO. NET DataConnection class. It is used to establish a connection to the data by creating the connection object. It then passes the connection string as a parameter to the class constructor. The connection string can either be predefined or generated using the C1SalesforceConnectionStringBuilder class.
Here is a sample code snippet depicting how to generate a connection string and create a connection object:
//Configure Connection string
C1SalesforceConnectionStringBuilder builder = new C1SalesforceConnectionStringBuilder();
builder.Url = url;
builder.Username = username;
builder.Password = password;
builder.SecurityToken = securityToken;
builder.OAuthClientId = clientId;
builder.OAuthClientSecret = clientSecret;
builder.OAuthTokenEndpoint = OAuthTokenEndpoint;
//Setup Connection
C1SalesforceConnection conn = new C1SalesforceConnection(builder.ConnectionString);
Access Data with a Secure Connection Using Authentication
Here, we will discuss how the OpenAuth-based authentication, supported by the ADO. NET provider for Salesforce Data, can help you access your data using a secure connection. OpenAuth is an open-standard authorization protocol or framework. It describes how unrelated servers and services can safely allow authenticated access to their assets without sharing the initial, related, single login credential. The ADO. NET provider for Salesforce supports the password credentials or client credentials grant types to establish a secure connection. To implement authentication, you would need to set the appropriate values for OAuthTokenEndPoint, OAuthAccessToken, OAuthRefreshToken, OAuthClientSecret, OAuthClientId, SecurityToken, Username, and Password properties. Based on the provided set of values, the library would automatically generate and consume the AccessToken/RefereshToken to maintain a secure connection.
Here is a sample code depicting the use of client credentials:
//Connection attribute values
const string url = "https://ap16.salesforce.com/services/data/v42.0";
const string username = "*****";
const string password = "*****";
const string securityToken = "*****";
const string clientId = "*****";
const string clientSecret = "*****";
const string OAuthTokenEndpoint = "https://ap16.salesforce.com/services/oauth2/token";
//Configure Connection string
C1SalesforceConnectionStringBuilder builder = new C1SalesforceConnectionStringBuilder();
builder.Url = url;
builder.Username = username;
builder.Password = password;
builder.SecurityToken = securityToken;
builder.OAuthClientId = clientId;
builder.OAuthClientSecret = clientSecret;
builder.OAuthTokenEndpoint = OAuthTokenEndpoint;
//Setup Connection
C1SalesforceConnection conn = new C1SalesforceConnection(builder.ConnectionString);
For details, refer to the detailed documentation topic OAuth Authorization.
How to Query Against the Data Source
The ADO. NET provider for Salesforce Data lets you query against the data source using standard SQL syntax. The provider supports most SQL functionalities, including join queries, functions, and summaries. The basic CRUD operations, such as insertion, updating, and deleting data, can be performed either in the connected or disconnected mode by using the C1SalesforceCommand and C1SalesforceDataAdapter objects.
The sample code below depicts the basic CRUD operations using the C1SalesforceDataAdapter object:
------------------------------------------------------------------------------------------------------------------
//READ QUERY
using (C1SalesforceConnection con = new C1SalesforceConnection(GCSalesforceServerConnectionString))
{
//Populate DataTable
C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString);
C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from Customer__c limit 5");
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
//Display fetched data
foreach (DataRow row in dataTable.Rows)
{
Console.WriteLine("{0}\t{1}", row["Customer_ID__c"], row["Phone__c"]);
}
}
-------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
//INSERT QUERY
using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))<
{
//Populate DataTable
C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from Customer__c");
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
//Create Insert command
adapter.InsertCommand = new C1SalesforceCommand(conn);
adapter.InsertCommand.CommandText = @"Insert into Customer__c (Customer_ID__c, Phone__c) values (@CustomerId, @Phone)";
adapter.InsertCommand.Parameters.Add("@CustomerId", "Customer_ID__c");
adapter.InsertCommand.Parameters.Add("@Phone", "Phone__c");
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
//Insert new row
DataRow customerRow = dataTable.NewRow();
customerRow["Customer_ID__c"] = "LAAR";
customerRow["Phone__c"] = "090-871265";
dataTable.Rows.Add(customerRow);
//Update database
adapter.Update(dataTable);
}
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
//UPDATE QUERY
using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
{
//Populate DataTable
C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from Customer__c");
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
//Create Update command
adapter.UpdateCommand = new C1SalesforceCommand(conn);
adapter.UpdateCommand.CommandText = "UPDATE Customer__c SET Contact_Name__c=@ContactName where Customer_ID__c=@CustomerId";
adapter.UpdateCommand.Parameters.Add("@ContactName", "Contact_Name__c");
adapter.UpdateCommand.Parameters.Add("@CustomerId", "Customer_ID__c");
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
//Update existing row
DataRow customerRow = dataTable.Rows[0];
customerRow["Contact_Name__c"] = "Maria Anders";
//Update database
adapter.Update(dataTable);
}
-------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
//DELETE QUERY
using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
{
//Populate Datatable
C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from Customer__c");
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
//Create Delete command
adapter.DeleteCommand = new C1SalesforceCommand(conn);
adapter.DeleteCommand.CommandText = "Delete from Customer__c where Customer_ID__c=@Id";
adapter.DeleteCommand.Parameters.Add("@Id", "Customer_ID__c");
adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
//Delete a row
DataRow customerRow = dataTable.Rows[0];
customerRow.Delete();
//Update Database
adapter.Update(dataTable);
}
-------------------------------------------------------------------------------------------------------------------
Enhancing Overall Performance with Batch Updates
The ADO. NET provider for Salesforce lets you group INSERT, UPDATE, and DELETE operations from a DataSet or DataTable, supporting batch processing. The batch updates enhance data access and data update operation's overall performance by preventing multiple requests to the server. The UpdateBatchSize property must be set to an appropriate value to ensure that the commands are grouped in the batches of the specified size and sent to the server for processing.
Here is a sample code snippet depicting how to set the UpdateBatchSize property and perform batch update:
using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
{
//Populate Datatable
C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from Customer__c");
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
//Create Update Query
adapter.UpdateCommand = new C1SalesforceCommand(conn);
adapter.UpdateCommand.CommandText = "UPDATE Customer__c SET Contact_Name__c=@ContactName where Customer_ID__c=@CustomerId";//Update Query
adapter.UpdateCommand.Parameters.Add("@ContactName", "Contact_Name__c");
adapter.UpdateCommand.Parameters.Add("@CustomerId", "Customer_ID__c");
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
//Perform Update operation
DataRow customerRow1 = dataTable.Rows[0];
customerRow1["Contact_Name__c"] = "Antonio Moreno";
DataRow customerRow2 = dataTable.Rows[3];
customerRow2["Contact_Name__c"] = "Hanna Moos";
//Set batch size to be updated
adapter.UpdateBatchSize = 2;
//Update database
adapter.Update(dataTable);
}
Enhancing Performance Through Caching
The performance of the ADO. NET provider for Salesforce can further be enhanced by accessing the data from the cache when the user performs similar operations repeatedly. To support this type of functionality, the DataConnector library provides built-in caching support. The UseCache property must be set to true, but you can customize the default cache location by setting the CacheLocation property. The CacheTolerance property is used to set the cache tolerance time in seconds, with the default value set to 600 seconds.
Here is a sample code snippet which depicts how to enable caching by setting all the cache properties in the connection object:
//Connection attribute values
const string url = "https://ap16.salesforce.com/services/data/v42.0";
const string username = "preet@comptest.com";
const string password = "waheguruji03";
const string securityToken = "fSP4lEXHh6YE1r3j1MTGO57v";
const string clientId = "3MVG9n_HvETGhr3DQYdXMQ1Vzd6aMGUbnEH8GSm5UjoFKp4XACMTrO0CypxyjPUrxMWgdifi0eC334qzeYaVb";
const string clientSecret = "FA80698B0899F2C6A4A4526540B939419FD224E57D7AEA29B2449B3E0D79402B";
const string OAuthTokenEndpoint = "https://ap16.salesforce.com/services/oauth2/token";
//Configure Connection string
C1SalesforceConnectionStringBuilder builder = new C1SalesforceConnectionStringBuilder();
builder.Url = url;
builder.Username = username;
builder.Password = password;
builder.SecurityToken = securityToken;
builder.OAuthClientId = clientId;
builder.OAuthClientSecret = clientSecret;
builder.OAuthTokenEndpoint = OAuthTokenEndpoint;
builder.UseCache = true;
builder.CacheTolerance = 500;
builder.CacheLocation = @"C:\temp\c1cache.db";
using (C1SalesforceConnection conn = new C1SalesforceConnection(builder.ConnectionString))
{
Console.WriteLine("Start Time" + DateTime.Now);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "Select * from Customer__c";
var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(String.Format("{0}\t{1}\t{2}", rdr["Customer_ID__c"], rdr["Contact_Name__c"], rdr["Phone__c"]));
}
Console.WriteLine("End Time " + DateTime.Now);
}
Additional Caching Strategy: Incremental Caching
The ADO. NET provider for Salesforce provides another caching strategy known as Incremental Caching. This strategy updates the cache data periodically to add new records from the source entities without refreshing the complete cache data. This technique relies on a unique field and a time-stamp field, which records the last time the record was updated or a new record was inserted. Therefore, any table which needs to implement incremental caching must have a Timestamp column; Salesforce has a default timestamp column "LastModifiedDate." The "IncrementalUpdate" and "IncrementalUpdateColumn" properties of C1SalesforceConnectionStringBuilder class must be set to enable incremental caching. For more details, refer to the documentation topic for incremental caching.
Conclusion: Moving Forward with Salesforce Data and ADO. NET
The steps above have demonstrated how easy it is to connect to a new type of data source if you have a standard interface based on known data access technology such as ADO. NET. So, let's get our hands on something new by using something old. For more detailed information, kindly refer to the DataConnector documentation.
Manpreet Kaur