What is a CData ADO.NET Provider for Salesforce Driver?

The CData ADO.NET Provider for Salesforce is a powerful tool that allows developers to connect .NET applications to live Salesforce data. The ADO.NET Provider allows users to use Salesforce Data Provider objects to connect and access data just as you would access any traditional database. You will be able to use the Salesforce Data Provider through Visual Studio Server Explorer, in code through familiar classes, and in data controls like DataGridView, GridView, DataSet, etc.

This article reviews the core features of CData's Salesforce ADO.NET Provider and explores how to use the ADO.NET Provider to connect to Salesforce, query data from a specific table, update data in Salesforce, and execute a stored procedure.

Core features

Comprehensive support for CRUD (Create, Read, Update, and Delete) using the Salesforce Web Services API.
Supports ADO.NET Entity Framework (EF 5 & 6), LINQ to Datasets, etc.
Support for 32-bit and 64-bit operating systems.
Supports .NET Framework 4.0+ and .NET Standard 2.0 (.NET Core 2.1+, .NET 6.0).
DataBind to Salesforce using standard Visual Studio wizards.
Includes support for custom Salesforce entities.
Supports access to tabular, summary, and matrix reports from Salesforce.

Connecting to Salesforce using OAuth

The CData ADO.NET Provider for Salesforce supports authentication to Salesforce via either user credentials or the OAuth standard. The following section reviews an example of using the embedded OAuth credentials to authenticate your connection to Salesforce:


using (SalesforceConnection connection = 
new SalesforceConnection("AuthScheme=OAuth; InitiateOAuth=GETANDREFRESH;"))
{
    connection.Open();
}

VB.NET


Using connection As New SalesforceConnection(" AuthScheme=OAuth; InitiateOAuth=GETANDREFRESH;")
    connection.Open
End Using

When you connect, the provider opens Salesforce's OAuth endpoint in your default browser. Log in and grant permissions to the application. The provider then completes the OAuth process as follows:

Extracts the access token from the callback URL.
Obtains a new access token when the old one expires.
Saves OAuth values in OAuthSettingsLocation so that they persist across connections.

Note: The example above uses an embedded OAuth application to simplify the authentication process. CData provides embedded OAuth credentials for authenticating from a Desktop application or a Headless machine. To connect from a Web application, you must create a custom OAuth application.

Read more about the CData Salesforce ADO.NET Provider authentication schemes and connection properties on the CData Documentation page: CData Salesforce ADO.NET Provider Documentation

Using the ADO.NET Provider

READ

The following code examples connect to Salesforce and run a SELECT SQL query to return the BillingState and Name columns of the Account table. The code uses the SalesforceDataAdapter to retrieve data:


string connectionString = "AuthScheme=OAuth; InitiateOAuth=GETANDREFRESH;";

using (SalesforceConnection connection = new SalesforceConnection(connectionString)) {
    SalesforceDataAdapter dataAdapter = new SalesforceDataAdapter(
        "SELECT BillingState, Name FROM Account", connection);

    DataTable table = new DataTable();
    dataAdapter.Fill(table);

    Console.WriteLine("Contents of Account.");

    foreach (DataRow row in table.Rows) {
        Console.WriteLine("{0}: {1}", row["BillingState"], row["Name"]);
    }
}

VB.NET


Dim connectionString As String = "AuthScheme=OAuth; InitiateOAuth=GETANDREFRESH;"

Using connection As New SalesforceConnection(connectionString)
    Dim dataAdapter As New SalesforceDataAdapter("SELECT BillingState, Name FROM Account", connection)

    Dim table As New DataTable()
    dataAdapter.Fill(table)

    Console.WriteLine("Contents of Account.")

    For Each row As DataRow In table.Rows
        Console.WriteLine("{0}: {1}", row("BillingState"), row("Name"))
    Next
End Using

WRITE


string connectionString = "AuthScheme=OAuth; InitiateOAuth=GETANDREFRESH;";

using (SalesforceConnection connection = new SalesforceConnection(connectionString)) {
    SalesforceDataAdapter dataAdapter = new SalesforceDataAdapter(
        "SELECT BillingState, Name FROM Account", connection);

    dataAdapter.UpdateCommand = new SalesforceCommand(
        "UPDATE Account SET Name = @Name " +
        "WHERE Id = @Id", connection);

    dataAdapter.UpdateCommand.Parameters.Add(new SalesforceParameter("@Name", "Name", DbType.String ));
    dataAdapter.UpdateCommand.Parameters.Add(new SalesforceParameter("@Id", "Id", DbType.String ));
    dataAdapter.UpdateCommand.Parameters[1].SourceVersion = DataRowVersion.Original;

    DataTable table = new DataTable();
    dataAdapter.Fill(table);

    DataRow firstrow = table.Rows[0];
    firstrow["Name"] = "Jon Doe";

    dataAdapter.Update(table);

    Console.WriteLine("Rows after update.");

    foreach (DataRow row in table.Rows) {
        Console.WriteLine("{0}: {1}", row["BillingState"], row["Name"]);
    }
}

VB.NET


Dim connectionString As String = "AuthScheme=OAuth; InitiateOAuth=GETANDREFRESH;"

Using connection As New SalesforceConnection(connectionString)
    Dim dataAdapter As New SalesforceDataAdapter(
        "SELECT BillingState, Name FROM Account", connection)

    dataAdapter.UpdateCommand = New SalesforceCommand(
        "UPDATE Account SET Name = @Name " +
        "WHERE Id = @Id", connection)

    dataAdapter.UpdateCommand.Parameters.Add(new SalesforceParameter("@Name", "Name" DbType.String ))
    dataAdapter.UpdateCommand.Parameters.Add(new SalesforceParameter("@Id", "Id", DbType.String))
    dataAdapter.UpdateCommand.Parameters(1).SourceVersion = DataRowVersion.Original

    Dim table As New DataTable()
    dataAdapter.Fill(table)

    Dim firstrow As DataRow = table.Rows(0)
    firstrow("Name") = "Jon Doe"

    dataAdapter.Update(table)

    Console.WriteLine("Rows after update.")

    For Each row As DataRow In table.Rows
        Console.WriteLine("{0}: {1}", row("BillingState"), row("Name"))
    Next
End Using

Stored procedure

The following code examples execute the UploadAttachment Stored Procedure that is used to upload a local attachment to Salesforce and associate it with an entity:


string connectionString = "AuthScheme=OAuth; InitiateOAuth=GETANDREFRESH;";

using (SalesforceConnection connection = new SalesforceConnection(connectionString)) {
    SalesforceCommand cmd = new SalesforceCommand("EXECUTE UploadAttachment @ObjectId = '0018Z00002nz4TkMAR', @FullPath = 'C:\csv samples\mycsvfile.csv'", connection);
    cmd.Parameters.Add(new SalesforceParameter("@ObjectId", "Insert"));
    // Add other parameters as needed ...

    SalesforceDataReader rdr = cmd.ExecuteReader();
    while (rdr.Read()) {
        for (int i = 0; i < rdr.FieldCount; i++) {
            Console.WriteLine(rdr.GetName(i) + " --> " + rdr[i]);
        }
        Console.WriteLine();    
    }
}

VB.NET


Dim connectionString As String = "AuthScheme=OAuth; InitiateOAuth=GETANDREFRESH;"

Using connection As New SalesforceConnection(connectionString)
    Dim cmd As New SalesforceCommand("EXECUTE CreateJob Action = @Action;", connection)
    cmd.Parameters.Add(New SalesforceParameter("@Action", "Insert"))
    ' Add other parameters as needed ...

    Dim rdr As SalesforceDataReader = cmd.ExecuteReader()
    While rdr.Read()
        For i As Integer = 0 To rdr.FieldCount - 1
            Console.WriteLine(rdr.GetName(i) + " --> " + rdr(i))
        Next
        Console.WriteLine()
    End While
End Using

Unlock seamless Salesforce connectivity in your .NET applications

Are you looking to unlock the full potential of your Salesforce data? CData's Salesforce ADO.NET Provider can seamlessly integrate your .NET applications with your Salesforce data. With the CData ADO.NET Provider for Salesforce Community License, you get free-forever libraries to access your data in personal .NET projects, all through familiar SQL. Request a license and start building Salesforce-connected apps today!