How to Query Salesforce Data Using Visual Studio

ComponentOne DataConnectors is a data-connectivity library built over ADO.NET architecture which lets you bind to many popular data sources, including Salesforce, Google Analytics, Service Now, and more. Among other exciting features, it supports Visual Studio integration for all the available data sources. You can establish a connection to the data source and query it using Server Explorer without having to write even a single line of code.

In this blog, we will learn how to establish a connection and query the datasource using Server Explorer. We will be using Salesforce datasource as an example throughout the blog sections.

Ready to test out DataConnectors? Download ComponentOne for a FREE trial today!

Integrate C1 DataConnectors with Visual Studio

To get started, we must install the latest version of Data Services using the latest version of ComponentOne Control Panel, which can be downloaded from here. This automatically integrates C1 DataConnectors with Visual Studio, making all the supported data sources available under the list of data sources when establishing a connection using Server Explorer.

Once you have the latest version of C1 DataConnectors installed on your machine, you can follow the steps ahead to establish a connection with Salesforce using the Server Explorer window in Visual Studio and create queries with and without SQL.

Connect to Salesforce using Server Explorer

1. We will start by creating a new Windows Forms Application and opening the Server Explorer Window, which can be accessed via the View option of the menu bar.

2. Next, we need to establish a connection to Salesforce using the Connect to Database option available in Server explorer, as depicted in the image below:

3. Clicking the Connect to Database option opens up Add Connection window, which is by default populated to use one of the built-in data sources, for example, Microsoft SQL Server Database File as showcased below:

So, firstly we should change the data source and choose the appropriate data provider by clicking on the Change button next to the Data Source textbox. This opens the Change Data Source Window, which lists all the data sources supported by C1 DataConnector and the appropriate provider; choosing a data source automatically updates the data provider.

The image below showcases all the data sources supported by C1 DataConnector, and you can even observe how choosing C1 Salesforce Data Source updates the Data Provider to the C1 ADO.NET provider for Salesforce.

4. As soon as we choose the C1 Salesforce Data Source, a property grid listing the properties for the Salesforce connection appears in the Add Connection window. To establish the connection, we need to populate the Salesforce connection string value in the ConnectionString property.

Here is the sample connection string with all the expected attributes:

username=******;password=******;security token=******;oauth client id=******;oauth client secret=******;
oauth token endpoint=https://ap16.salesforce.com/services/oauth2/token;url=https://ap16.salesforce.com/services/data/v42.0;api version=42.0

Below, you can observe the populated ConnectionString property, which leads to a successful connection to the data source as tested by clicking the Test Connection button.

Once the connection has been successfully established, you can find all the data tables from the Salesforce data source listed below the C1 Salesforce connection in the Server Explorer window, as depicted in the following image:

Create and Execute SQL Queries

After connecting to Salesforce, we will learn how to create and execute queries on tables using Server Explorer Window in this section.

We can create a new query by right-clicking the Tables node in the server explorer and choosing the New Query option from the context menu, as shown in the below image:

When we click on this option, a new SQL query window opens with four panes: Diagram Pane, Criteria Pane, SQL Pane, and Results Pane. The Diagram pane is used to create SQL queries without writing SQL, the Criteria pane is used to specify query options, the SQL Pane lets you define SQL queries, and the Results pane shows the result of any executed SQL query.

An Add Table window is also displayed, which lets you add the table onto the Diagram pane to define a query.

The image below portrays all the panes along with the Add Table window:

For now, we will not choose any table and close this window. Doing so, you can observe a Query Designer menu starting to appear below the main menu bar, which provides different options to control the visibility of the panes mentioned above, execute the SQL query, and more. The image below highlights a few of these options we will use in the sections ahead.

Now, let's move on to creating and executing queries.

Create SQL Queries using SQL Pane

To create SQL queries in the SQL pane, you must type in the SQL query with the correct syntax in the SQL pane and click on the execute query button in Query Designer Menu to get the results in the Results pane.

We will demonstrate the same using SELECT, INSERT and JOIN queries described next.

SELECT QUERY:

The SELECT query selects five fields, namely, LeadSource, Country, Company, Name, and Status, from the Lead table.

The image below depicts the SELECT query in the SQL pane, and the query execution result in the results pane.

INSERT QUERY:

The INSERT query inserts a new row with values for five fields, namely, LeadSource, Country, Company, Name, and Status, into the Lead table. On execution of an INSERT query, no results are returned; just a message displaying the rows affected appears. Hence, we execute the SELECT query again to observe the INSERT query results.

The image below shows both the queries in the SQL pane along with the results of the SELECT query in the results pane:

JOIN QUERY:

The JOIN query here joins two tables, i.e., Lead and Opportunity, fetching values for six fields, namely, Country, Company from the Lead table and StageName, Amount, IsWon, IsClosed from the Opportunity table after performing an INNER JOIN on both the tables based on the common field OwnerId.

The image below depicts the JOIN query in the SQL pane along with the query execution result in the results pane:

Create SQL Queries using Diagram Pane

When creating queries with the diagram pane's help, we drag-drop the tables for which we would like to define a query onto the diagram pane and perform other actions specific to the query, such as selecting fields, providing new values, defining JOIN operation, and more. The query results can be obtained by executing the query using the Execute Query button available in the Query Designer menu.

We will demonstrate query creation and execution in the diagram pane using SELECT, INSERT and JOIN queries described next.

SELECT QUERY:

The SELECT query selects five fields, namely, LeadSource, Country, Company, Name, and Status, from the Lead table. To implement the same:

1. Drag-drop the Lead table from the server explorer onto the diagram pane.
2. Select fields for the SELECT query by checking the available checkbox against each field in the Lead table diagram in the diagram pane.

This completes the creation of the SELECT query. Click on the execute query button to get the query result in the results pane.

The GIF below depicts the same steps:

INSERT QUERY:

The INSERT query inserts a new row with values for five fields, namely, LeadSource, Country, Company, Name, and Status, into the Lead table. To implement the same:

1. Drag-drop the Lead table from the server explorer onto the diagram pane.
2. Change the type of query to Insert Values using the Change Type option from the Query designer menu.
3. In the Diagram pane, click the checkbox for each column you want to supply new values. Those columns will show in the Criteria pane.
4. In the New Value column of the Criteria pane, enter the new value for the column. You can enter literal values, column names, or expressions. The value must match (or be compatible with) the data type of the column you are updating.

This completes the creation of the INSERT query. For details on creating an Insert Query, refer to the following link.

However, when you execute an Insert Values query, no results are reported in the results pane. Instead, a message appears indicating how many rows were affected. Hence, we execute the SELECT query again by simply changing the query type to Select using the Change Type option in the query designer menu to observe the INSERT query results.

The GIF below depicts the same steps:

JOIN QUERY:

The JOIN query joins two tables, i.e., Lead and Opportunity, fetching values for six fields: Country, Company from the Lead table and StageName, Amount, IsWon, IsClosed from the Opportunity table after performing an INNER JOIN on both the tables based on the common field OwnerId. To implement the same:

1. Drag-drop the Lead table and Opportunity table from server explorer onto the diagram pane.
2. Select query fields by checking the checkboxes next to each field for both the tables in the table diagrams created in the diagram pane.
3. Drag the name of the join column in the first table and drop it onto the related column in the second table. You cannot base a join on text, ntext, or image columns. The Join operator by default is '=' and uses the INNER JOIN clause. You can alter both these if required. For details, refer to Query with Joins - Visual Database Tools.

This completes the creation of the JOIN query. Click on the execute query button to get the query result in the results pane.

The GIF below depicts the same steps:

Adding Table to DataSet from ServerExplorer

The Salesforce and Server Explorer integration lets you add DataTable to a DataSet using the DataSet designer. The steps below describe the same:

1. Add a new DataSet to the project by choosing the Add New Item option from the project’s context menu.

2. In the Add New Item dialog box that appears, select Data in the left-hand pane and then select DataSet in the middle pane. Name the new DataSet SalesforceDataset and then choose Add, as depicted in the image below:

As soon as the DataSet is added to the project, the DataSet Designer opens up in the project window, as depicted in the image below:

3. Drag the Lead table from Server Explorer onto the Dataset Designer. A Lead data table and LeadTableAdapter are added to the dataset as depicted in the image below:

Once you have added the DataTable to DataSet, you can bind it as a datasource to any data-bound control. You can go through the following blog, which showcases FlexGrid DataSet binding.

So, this completes the understanding of the Visual Studio integration with Salesforce. Refer to C1 DataConnector demos and documentation to understand other powerful features and even explore all the data sources supported by C1 DataConnector.

Ready to test out DataConnectors? Download ComponentOne for a FREE trial today!