DbFactory Support

In This Topic

DataConnector supports DbProvider Factories, which can be configured to produce the needed objects for applications and tools to connect to and interact with external ADO.NET data connectors. To explain how this is done, Salesforce is going to be used as an example. The ADO.NET Provider for Salesforce implements the C1SalesforceProviderFactory class to provide support for DbProviderFactory and allow the writing of generic data-access code through the ADO.NET base classes. The following sections demonstrate the creation and usage of DbProviderFactory and DbConnection objects by C1SalesforceProviderFactory.

Register the provider to the configuration context

To create a strongly typed DbProviderFactory for SalesForce in a Windows application, register the ADO.NET provider manually in the configuration context (app.config, or web.config) of DbFactory configuration, by using the code below:

App.config	Copy Code
<system.data> <DbProviderFactories> <add name="C1.AdoNet.Salesforce" invariant="C1.AdoNet.Salesforce" description="C1.AdoNet.Salesforce is a data provider that allows to work with Salesforce service via ADO.NET APIs." type="C1.AdoNet.Salesforce.C1SalesforceProviderFactory, C1.AdoNet.Salesforce, Version=1.0.20*., Culture=neutral, PublicKeyToken=yourToken" /> </DbProviderFactories> </system.data>

App.config

Copy Code

<system.data>
    <DbProviderFactories>
        <add name="C1.AdoNet.Salesforce" invariant="C1.AdoNet.Salesforce" 
            description="C1.AdoNet.Salesforce is a data provider that allows 
            to work with Salesforce service via ADO.NET APIs."
            type="C1.AdoNet.Salesforce.C1SalesforceProviderFactory, 
            C1.AdoNet.Salesforce, Version=1.0.20***.**, Culture=neutral, 
            PublicKeyToken=yourToken" />
    </DbProviderFactories>
</system.data>

The following fragment in the configuration file demonstrates a Salesforce connection string in the context:

App.config	Copy Code
<connectionStrings> <add name="SalesforceServer" providerName="C1.AdoNet.Salesforce" connectionString="Username=**@.com;Password=**; Security Token=yourToken;OAuth Client Id=yourID; OAuth Client Secret=yourClientSecret; OAuth Token Endpoint=yourToken; Url=yourURL; Use Pool = false; Max Page Size = 200" /> </connectionStrings>

App.config

Copy Code

<connectionStrings>
    <add name="SalesforceServer" providerName="C1.AdoNet.Salesforce" 
        connectionString="Username=****@****.com;Password=****;
        Security Token=yourToken;OAuth Client Id=yourID;
        OAuth Client Secret=yourClientSecret;
        OAuth Token Endpoint=yourToken;
        Url=yourURL; Use Pool = false; Max Page Size = 200" />
</connectionStrings>

Acquire the DbProviderFactory and the DbConnection objects

Instantiate a strongly typed DbProviderFactory object, use it to create a DbConnection object, then connect to Salesforce, by using the following code:

C#	Copy Code
DbProviderFactory factory = DbProviderFactories.GetFactory("C1.AdoNet.Salesforce"); // Create connection using (DbConnection connection = factory.CreateConnection()) { connection.ConnectionString = "User=yourUser;Password=yourPassword;Security Token=yourToken;"; connection.Open(); }

Copy Code

DbProviderFactory factory = DbProviderFactories.GetFactory("C1.AdoNet.Salesforce");

// Create connection
using (DbConnection connection = factory.CreateConnection())
{
    connection.ConnectionString = "User=yourUser;Password=yourPassword;Security Token=yourToken;";
    connection.Open();
}

Alternatively, the connection string can also be read from the configuration file of the application. To retrieve the Salesforce connection string defined in the configuration file, use the following code:

C#	Copy Code
ConnectionStringSettingsCollection csettings = ConfigurationManager.ConnectionStrings; // Set connection string, by fetching it from the config file if (csettings != null) { foreach (ConnectionStringSettings connString in csettings) { if (connString.ProviderName == "C1.AdoNet.Salesforce") { connection.ConnectionString = connString.ConnectionString; break; } } }

Copy Code

ConnectionStringSettingsCollection csettings = ConfigurationManager.ConnectionStrings;

// Set connection string, by fetching it from the config file
if (csettings != null)
{
    foreach (ConnectionStringSettings connString in csettings)
    {
        if (connString.ProviderName == "C1.AdoNet.Salesforce")
        {
            connection.ConnectionString = connString.ConnectionString;
            break;
        }
    }
}

Note: The ConnectionStringSettingsCollection class requires a reference to System.Configuration.dll

Execute DbCommands

The acquired DbProviderFactory and DbConnection objects can be used for creating and executing commands to fetch the database information from the data source. For example, a "SELECT" query to Salesforce can be executed with the following code:

C#	Copy Code
// Create command DbCommand command = factory.CreateCommand(); command.Connection = connection; command.CommandText = "Select * from Customer__c limit 5"; // Create DataAdapter DbDataAdapter adapter = factory.CreateDataAdapter(); adapter.SelectCommand = command; // Fill DataTable DataTable dataTable = new DataTable(); adapter.Fill(dataTable); // Bind DataTable to C1FlexGrid c1FlexGrid1.DataSource = dataTable;

Copy Code

// Create command
DbCommand command = factory.CreateCommand();
command.Connection = connection;
command.CommandText = "Select * from Customer__c limit 5";

// Create DataAdapter
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.SelectCommand = command;

// Fill DataTable
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);

// Bind DataTable to C1FlexGrid
c1FlexGrid1.DataSource = dataTable;

The other supported ADO.NET Providers can be similarly registered in context of DbFactory configuration in the local App.config files, using the respective information from the following table:

Provider name

Provider registration string

Connection string

OData

Copy Code

<system.data>
  <DbProviderFactories>
    <add name="C1.AdoNet.OData"
      invariant="C1.AdoNet.OData"
      description="ADO.NET provider for OData service"
      type="C1.AdoNet.OData.C1ODataProviderFactory, 
      C1.AdoNet.OData, Version=1.0.20***.**,
      Culture=neutral, PublicKeyToken=yourToken" />
  </DbProviderFactories>
</system.data>

Copy Code

<connectionStrings>
  <add name="ODataServer"
    providerName="C1.AdoNet.OData" 
    connectionString="Url=https://***/***/***;
    Password=****;Username=****@****.com;
    Use Dynamic Type = 'false'" />
  </connectionStrings>

Dynamic 365 Sales

Copy Code

<system.data>
  <DbProviderFactories>
    <add name="C1.AdoNet.D365S" 
      invariant="C1.AdoNet.D365S"
      description="ADO.NET provider for D365S service."
      type="C1.AdoNet.D365S.C1D365SProviderFactory,
      C1.AdoNet.D365S, Version=1.0.20***.**, 
      Culture=neutral, PublicKeyToken=yourToken" />
  </DbProviderFactories>
</system.data>

Copy Code

<connectionStrings>
  <add name="D365SServer" 
    providerName="C1.AdoNet.D365S" 
    connectionString="Url=https://***/***/***;
    OAuth Client Id=****;
    OAuth Client Secret=****;
    OAuth Token Endpoint=https://***/***/***;
    OAuth Extend Properties=yourExtendProperties;
    Use Etag=true;Max Page Size = 100" />
</connectionStrings>

Kintone

Copy Code

<system.data>
  <DbProviderFactories>
    <add name="C1.AdoNet.Kintone" 
      invariant="C1.AdoNet.Kintone"
      description="ADO.NET provider for Kintone service."
      type="C1.AdoNet.Kintone.C1KintoneProviderFactory,
      C1.AdoNet.Kintone, Version=1.0.20***.**,
      Culture=neutral, PublicKeyToken=yourToken" />
  </DbProviderFactories>
</system.data>

	Copy Code
<connectionStrings> <add name="KintoneServer" providerName="C1.AdoNet.Kintone" connectionString="Url=https://*//; Username=@.com;Password=**;" /> </connectionStrings>

QuickBooks Online

Copy Code

<system.data>
  <DbProviderFactories>
    <add name="C1.AdoNet.QuickBooksOnline" 
      invariant="C1.AdoNet.QuickBooksOnline"
      description="ADO.NET provider for QuickBooksOnline service"
      type="C1.AdoNet.QuickBooksOnline.C1QuickBooksOnlineProviderFactory,
      C1.AdoNet.QuickBooksOnline, Version=1.0.20***.**,
      Culture=neutral, PublicKeyToken=yourToken" />
  </DbProviderFactories>
</system.data>

Copy Code

<connectionStrings>
  <add name="QuickBooksOnlineServer" 
    providerName="C1.AdoNet.QuickBooksOnline" 
    connectionString="Company Id=yourID;
    OAuth Access Token=yourToken;
    OAuth Client Id=****;
    OAuth Client Secret=****;
    OAuth Refresh Token=****;
    OAuth Token Endpoint=https://***/***/***;
    Minor Version=14; Use Pool=false; 
    Max Page Size = 50;Country Code=yourCode;
    Use SandBox=True" />
</connectionStrings>

Google Analytics

Copy Code

<system.data>
  <DbProviderFactories>
    <add name="C1.AdoNet.GoogleAnalytics"
      invariant="C1.AdoNet.GoogleAnalytics"
      description="ADO.NET provider for GoogleAnalytics service"
      type="C1.AdoNet.GoogleAnalytics.C1GoogleAnalyticsProviderFactory,
      C1.AdoNet.GoogleAnalytics, Version=1.0.20***.**, 
      Culture=neutral, PublicKeyToken=yourToken" />
  </DbProviderFactories>
</system.data>

	Copy Code
<connectionStrings> <add name="GoogleAnalyticsServer" providerName="C1.AdoNet.GoogleAnalytics" connectionString="Key File=yourFile; View Id=****" /> </connectionStrings>