Connect to Salesforce with Dapper using C#

In this article, we'll demonstrate how to connect to Salesforce using Dapper and C# with the help of dotConnect for Salesforce. Whether you're building custom business logic, creating reports, or automating workflows, this integration allows you to retrieve, manipulate, and interact with Salesforce data in an efficient and straightforward manner. By using dotConnect, you can harness advanced features designed specifically for connecting C# applications to Salesforce, while Dapper ensures that you can perform high-speed and lightweight data queries.

Let's dive into setting up the connection and using Dapper for seamless Salesforce integration to help you get the most out of both worlds: Salesforce's robust CRM capabilities and C#'s powerful programming features.

Why dotConnect for Salesforce

dotConnect for Salesforce is built to simplify integration between .NET applications and Salesforce. It allows the application developers to connect to Salesforce directly and perform data-related operations efficiently, handling real-time queries and processing large datasets.

The compatibility with ORM frameworks like Entity Framework also enables developers to simplify all their workflows and work with Salesforce data in an object-oriented way.

Prerequisites

Before you begin, make sure you have the following prerequisites at place:

Visual Studio 2022: This is the IDE of choice for .NET development. If you don't have it installed, you can download the free Community Edition from the official Microsoft website.
dotConnect for Salesforce: A high-performance ADO.NET data provider for Salesforce with an enhanced ORM support and flawless database connectivity.
Salesforce account: An active Salesforce account with the necessary permissions for accessing and managing data.

Download and activate dotConnect for Salesforce

30-day free trial version

Download and install dotConnect for Salesforce directly on your machine, or install the Devart.Data.Salesforce NuGet package.

No license key is required, and you can start exploring the product immediately.

Full version

After purchasing the full version, go to your profile's Licenses page. Choose your product and click Details. Here, you'll find the license details and the Activation Key.

License details and the activation key

To activate a connection in your application, add the License Key to your connection string.

Get the Salesforce Security API token

To connect to Salesforce and access the data, you need your valid credentials: the User ID, the password, and the security token.

To obtain that token, sign in to your Salesforce account, go to your profile, and select Settings.

Navigate to Reset My Security Token and request it. A new token will be sent to your email.

Use this Security Token for API access when connecting to Salesforce

Create a .NET project

1. Open Microsoft Visual Studio and select Create a new project.

2. Name your project. For this tutorial, we'll call it SalesforceDapper.

3. In Solution Explorer, right-click the project and select Manage NuGet Packages.

4. Search for and install the following packages:

Check Salesforce database objects

To connect to Salesforce using the built-in Data Explorer, navigate to Tools > Connect to Database.

Select Salesforce as the data source, enter your server credentials, and click Connect. To verify the connection, click Test Connection.

Once connected, you can browse tables, execute queries, and manage data directly within the Data Explorer.

Connect to Salesforce and retrieve data

The application will connect to the Salesforce database, execute the query, and display the data from the Account table in the console.

Create Account.cs (Model Class)

This class maps to the Account table:

public class Account
{
	public string Name { get; set; }
	public string Phone { get; set; }
	public string Website { get; set; }
	public DateTime CreatedDate { get; set; }
}

Create DatabaseConnection.cs (Connection Strings)

The class keeps connection details separate for better maintainability.

public static class DatabaseConnection {
  private
  const string ConnectionString = "Authentication Type=UserNamePassword;" +
	"Host=https://salesforce.com;" +
	"User [email protected];" +
	"Password=**********;" +
	"Security Token=**********;" +
	"License Key=**********;";

  public static string GetConnectionString() {
	if (string.IsNullOrEmpty(ConnectionString)) {
  	throw new InvalidOperationException("Connection string is not set.");
	}

	return ConnectionString;
  }
}

Add the following code to Program.cs.

class Program {
  static void Main() {
	string connectionString = DatabaseConnection.GetConnectionString();

	using(IDbConnection db = new SalesforceConnection(connectionString)) {
  	try {
    	db.Open();
    	Console.WriteLine("Connection successful!");

    	string query = "SELECT Name, Phone, Website, CreatedDate FROM Account LIMIT 10";
    	var accounts = db.Query<Account> (query).ToList();

    	Console.WriteLine("First 10 Accounts:");
    	foreach(var account in accounts) {
      	Console.WriteLine($"Name: {account.Name}, Phone: {account.Phone}, Website: {account.Website}, CreatedDate: {account.CreatedDate}");
    	}
  	} catch (Exception ex) {
    	Console.WriteLine($"Connection failed: {ex.Message}");
  	}
	}
  }
}

Update the connection string

Replace the placeholders in the connection string with your relevant Salesforce database credentials, or create a new JSON file named appsettings.json in your project and include the connection strings.

Note

If you're using a purchased dotConnect for Salesforce license, include the license key in the connection strings.

Run the application

Build and run the application by pressing F5 or selecting Start from the menu.

Batch insert new records using Dapper

This code generates 10 new account objects with test data and inserts them into the Salesforce Account table using a parameterized query.

class Program {
  static void Main() {
	string connectionString = DatabaseConnection.GetConnectionString();

	using(IDbConnection db = new SalesforceConnection(connectionString)) {
  	try {
    	db.Open();
    	Console.WriteLine("Connection successful!");

    	// Insert 10 new test rows
    	var newAccounts = Enumerable.Range(1, 10).Select(i => new Account {
      	    Name = $"Test Account {i}",
        	Phone = $"123-456-789{i}",
        	Website = $"http://testaccount{i}.com"
    	}).ToList();

    	string insertQuery = "INSERT INTO Account (Name, Phone, Website) VALUES (@Name, @Phone, @Website)";
    	db.Execute(insertQuery, newAccounts);

    	Console.WriteLine("Inserted 10 new test rows.");

    	// Retrieve and display the newly inserted rows
    	string selectQuery = "SELECT Name, Phone, Website, CreatedDate FROM Account WHERE Name LIKE 'Test Account%' ORDER BY CreatedDate DESC LIMIT 10";
    	var insertedAccounts = db.Query<Account> (selectQuery).ToList();

    	Console.WriteLine("Newly Inserted Accounts:");
    	foreach(var account in insertedAccounts) {
      	Console.WriteLine($"Name: {account.Name}, Phone: {account.Phone}, Website: {account.Website}, CreatedDate: {account.CreatedDate}");
    	}
  	} catch (Exception ex) {
    	Console.WriteLine($"Error: {ex.Message}");
  	}
	}
  }
}

The result is as follows:

Update Salesforce data using Dapper

The UPDATE statement modifies the Phone and Website fields for all accounts with names that match the pattern 'Test Account%'. This ensures that only the previously inserted test rows are updated.

class Program {
  static void Main() {
	string connectionString = DatabaseConnection.GetConnectionString();

	using(IDbConnection db = new SalesforceConnection(connectionString)) {
  	try {
    	db.Open();
    	Console.WriteLine("Connection successful!");

    	// Update the previously inserted rows
    	string updateQuery = "UPDATE Account SET Phone = '987-654-3210', Website = 'http://updatedwebsite.com' WHERE Name LIKE 'Test Account%'";
    	db.Execute(updateQuery);

    	Console.WriteLine("Updated the test rows.");

    	// Retrieve and display the updated rows
    	string selectQuery = "SELECT Name, Phone, Website, CreatedDate FROM Account WHERE Name LIKE 'Test Account%' ORDER BY CreatedDate DESC LIMIT 10";
    	var updatedAccounts = db.Query<Account> (selectQuery).ToList();

    	Console.WriteLine("Updated Accounts:");
    	foreach(var account in updatedAccounts) {
      	Console.WriteLine($"Name: {account.Name}, Phone: {account.Phone}, Website: {account.Website}, CreatedDate: {account.CreatedDate}");
    	}
  	} catch (Exception ex) {
    	Console.WriteLine($"Error: {ex.Message}");
  	}
	}
  }
}

Let's test the application.

Delete Salesforce data using Dapper

The DELETE statement removes all accounts with names that match the pattern 'Test Account%'. This ensures that only the previously inserted test rows are deleted.

class Program {
  static void Main() {
	string connectionString = DatabaseConnection.GetConnectionString();

	using(IDbConnection db = new SalesforceConnection(connectionString)) {
  	try {
    	db.Open();
    	Console.WriteLine("Connection successful!");

    	// Delete the previously inserted rows
    	string deleteQuery = "DELETE FROM Account WHERE Name LIKE 'Test Account%'";
    	int rowsAffected = db.Execute(deleteQuery);

    	Console.WriteLine($"Deleted {rowsAffected} test rows.");

    	// Verify deletion by attempting to retrieve the rows
    	string selectQuery = "SELECT Name, Phone, Website, CreatedDate FROM Account WHERE Name LIKE 'Test Account%'";
    	var remainingAccounts = db.Query<Account> (selectQuery).ToList();

    	if (remainingAccounts.Any()) {
      	Console.WriteLine("Some test rows still exist:");
      	foreach(var account in remainingAccounts) {
        	Console.WriteLine($"Name: {account.Name}, Phone: {account.Phone}, Website: {account.Website}, CreatedDate: {account.CreatedDate}");
      	}
    	} else {
      	Console.WriteLine("All test rows have been successfully deleted.");
    	}
  	} catch (Exception ex) {
    	Console.WriteLine($"Error: {ex.Message}");
  	}
	}
  }
}

You can see that all test rows have been deleted.

Conclusion

Dapper's lightweight and fast data access, combined with the power of dotConnect for Salesforce, ensures that you can work with Salesforce data efficiently without sacrificing performance. Whether building custom solutions, integrating Salesforce data into your business logic, or creating reporting tools, this method provides a scalable and reliable approach.

As you continue to develop and extend your applications, the ability to connect to Salesforce using C# and Dapper will empower you to unlock the full potential of Salesforce's rich CRM features and maintain smooth and effective data flow between systems. So, dive in and start building your Salesforce-integrated solutions with ease, speed, and reliability!