Figure 1 - SalesForce Account Data in the Browser
Introduction
Salesforce.com is a CRM (Customer Relationship Management) web site that has revolutionized how businesses interact with their clients and prospective clients. A business can manage, communicate, and sell to their clients using all the powerful online tools provided by Salesforce. Such tools include account and customer tracking, lead follow-up tools, collaboration applications, and customer support services. And if that's not enough, Salesforce.com has an API which allows developers to hook into their various CRM products. RSSBus provides a product that makes getting to the CRM data on Salesforce a breeze. As with their other RSSBus tools, such as their Google API Provider and Quickbooks Provider, the Salesforce provider allows you to access the API as an ADO.NET data provider. Because RSSBus is a data provider, this product makes it much easier for developers like you and me to utilize .NET standard practices to access the customer and prospect data in the underlying Salesforce web service. By using a strong presentation library like Syncfusion coupled with the RSSBus Salesforce Data Provider, we can create some useful and visually appealing applications very quickly to take advantage of the CRM data with Salesforce.com.
RSSBus For Salesforce
The RSSBus data provider has six different data table representations for accessing Salesforce data: Accounts, Campaigns, Events, Leads, Opportunities, and Products. There are also database views for Contracts, Cases, and Contacts. In addition to being able to query tables and views of the Salesforce data using the RSSBus provider, you can also call some useful stored procedures. You can use the provider's stored procs to perform CRUD operations to either add, delete, or update an entry in any of the 6 Saleforce data tables provided. RSSBus also has a caching feature which you can activate to speed up querying of the Salesforce data tables.
Getting Started
The only obstacle to using Salesforce application is the difficulty of signing up for the for an API license on Salesforce.com. This took a couple of hours and required using the RSSBus web demo application to test my license login information to ensure that it worked. One of the challenges I ran into was that the system required a security token in addition to the user name and password. Once I figured how to get the token and how to add it to my password (simply by concatenating it), I was home free. Below is a sample connection string that I needed to put in the web.config file of my ASP.NET app, to get things going:
<connectionStrings>
<add name="SalesForceConnectionString" connectionString="[email protected];Password=salesforcebJOm3JgJQ4XKWLGDjL4YfdO1" providerName="System.Data.RSSBus.SalesForce"/>
</connectionStrings>
Displaying Account Data in MVC
I chose ASP.NET MVC technology in order to show the ease of use of RSSBus and how we can leverage Syncfusion to make interactive presentation of our data on the web. The Syncfusion ASP.NET MVC components are well designed and demand little coding from a programmer's standpoint. The grid is a fluent control and requires only a single line of embedded code in the page to produce results from the server. The data for customer accounts on SalesForce.com can be extracted in a few lines of code by leveraging the RSSBus Provider and using its Salesforce DataReader capability.
Creating the Project
Creating a Syncfusion ASP.NET MVC project was fairly straightforward because Syncfusion provides a template for creating projects for the Syncfusion Grid. The project didn't seem to have any sample grid in it, which I would have expected, but perhaps I overlooked it. Fortunately, I was able to find plenty of samples online at the Syncfusion Website. Figure 2 shows the template provided after you install the Syncfusion Essential Grid.
![1.jpg]()
Figure 2 - Syncfusion Project Template for Creating an ASP.NET MVC Project with a Grid
The Code
The code for displaying a Syncfusion grid in an ASP.NET MVC page reminds me a lot of the code used by the MVCContrib grid, but the Syncfusion grid has a lot more functionality. Listing 1 shows the html helper in the Syncfusion library used to produce our Salesforce grid of Account Data. Notice that the embedded grid is a single line of code to produce the entire table (with the help of lambda expressions). The Syncfusion grid also allows us to easily format and sort our columns. Although it is not illustrated here, it is pretty easy to implement paging as well.
Listing 1 - Page using Syncfusion Grid to Display Salesforce Data
<%@ Page Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage<IEnumerable<MvcGridForSalesForce.Models.Customer>>" %>
<%@ Import Namespace="MvcGridForSalesForce.Models" %>
<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
Home Page
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<h2><%: ViewData["Message"] %></h2>
<p>
<%=Html.Syncfusion().Grid<Customer>("Product_grid")
.Datasource(Model)
.Caption("Customers")
.Column( column =>
{
column.Add(p => p.Name).HeaderText("Full Name");
column.Add(p => p.Phone).HeaderText("Phone").Format("{0:1 - (###) ###-####}");
column.Add(p => p.AccountNumber).HeaderText("Account Number");
column.Add(p => p.AnnualRevenue).HeaderText("Annual Revenue").Format("{0:###,###,###,###,##0}");
}).EnableSorting()
%>
</p>
</asp:Content> |
The Controller
As many developers who use MVC on a daily basis know, the page data is served up from the controller (the C in MVC). We'll use the RSSBus technology to populate our Salesforce Account Model using the SalesforceProvider. The Model data will then be rendered on the page using the Syncfusion Grid in Listing 1. The URL to trigger the controller code in Listing 2 is simply a call to the root controller page since the browser call is the Index method. In other words, if the root is local host, then the user could enter http://localhost/home to call the Index method in listing 2.
Listing 2 - Method called by the Browser to get the SalesForce Data
|
namespace MvcGridForSalesForce.Controllers
{
[HandleError]
public class HomeController : Controller
{
public ActionResult Index()
{
ViewData["Message"] = "Customer Data For Global Sales Corp Inc.";
ReadSalesForceDataIntoModel();
return View();
} |
The ReadSalesForceDataIntoModel method in listing 2 will use RSSBus to get the data into the model to be used by the Syncfusion grid. This method uses the RSSBus data provider to extract the data from our Salesforce Accounts. Just like any ADO.NET dataprovider, you can use the connection, command, and reader objects to pull down the data through the provider. The difference is that we are not pulling directly from a database, but from Salesforce.com itself. Listing 3 shows how we populate the Salesforce customer account model using the provider.
Listing 3 - Populating the Model with Salesforce Data
|
private void ReadSalesForceDataIntoModel()
{
// get the connection string from the web.config file
System.Configuration.Configuration rootWebConfig =
System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/MvcGridForSalesForce");
System.Configuration.ConnectionStringSettings connString;
if (rootWebConfig.ConnectionStrings.ConnectionStrings.Count > 0)
{
connString =
rootWebConfig.ConnectionStrings.ConnectionStrings["SalesForceConnectionString"];
// open the connection and read in the Salesforce Account Data with the RSSBus Salesforce Provider
var connection = new SalesForceConnection(connString.ConnectionString);
var cmd = new SalesForceCommand("SELECT * FROM [Accounts]", connection);
connection.Open();
var myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
ViewData.Model = new List<Customer>();
while (myReader.Read())
{
var accountNumber = myReader["AccountNumber"] as string;
var name = myReader["Name"] as string;
var phone = myReader["Phone"] as string;
double annualRevenue = 0.0;
if (!double.TryParse( myReader["AnnualRevenue"] as string, out annualRevenue))
{
}
(ViewData.Model as List<Customer>).Add(new Customer { AccountNumber = accountNumber, Name = name, Phone = phone, AnnualRevenue = annualRevenue});
}
myReader.Close();
}
} |
Charting Revenue
If we look at our grid in Figure 1, the customer data shows a column for Annual Revenue from individual customers. We could chart this data in a bar chart to show it as a report so that people evaluating the business can quickly see which accounts are producing and which are not:
Syncfusion has a rich set of charting controls in ASP.NET MVC for displaying the data on the web. Although they were not as intuitive as I had hoped, the controls did the job in displaying what I needed to see. I had trouble figuring out how to label the x axis of each data point, so I resorted to keeping them as numbers to represent each account. One thing I found necessary when using the Syncfusion controls is that you definitely need to use the project template for each component to make it work correctly; there are line items that get added to the web config and references that are tedious to add manually. A useful addition would be a wizard that lets you specify whether you are using a grid or a chart or both in your project. Different components require different changes to the web config and references in the project, so using multiple components in one project requires you to cut and paste the differences.
Figure 3 shows the bar chart we've created for revenue from our various accounts. Each bar is labeled with the name of the account:
Figure 3 - Bar Chart of Salesforce.com Revenue
The Syncfusion code for displaying the revenue information is shown in listing 4. Syncfusion provides a model called MVCChartModel which you can populate to display your data. The model allows you to set the chart type, the styles in the chart, and most importantly, the data. You can set the style of each individual bar by setting the style index that matches the series x coordinate point.
Listing 4 - Populating the MVCChartModel in Syncfusion with Salesforce Data
|
public ActionResult BasicChartType()
{
// initialize the chart model for rendering
MVCChartModel simpleChart = new MVCChartModel();
simpleChart.Text = "Revenue Per Customer (MM)";
// set the chart size
simpleChart.Size = new Size(700, 600);
simpleChart.Series.Clear();
var customers = ReadSalesForceAccountData();
ChartSeries series = new ChartSeries("Revenue");
int count = 0;
foreach (var customer in customers)
{
series.Points.Add(count, customer.AnnualRevenue/1000000);
count++;
}
// set style to bar chart
series.Type = ChartSeriesType.Bar;
// add account labels for revenue greater than 0
for (int i = 0; i < customers.Count(); i++)
{
if (customers.Skip(i).Take(1).FirstOrDefault().AnnualRevenue > 0d)
{
series.Styles[i].DisplayText = true;
}
series.Styles[i].TextOffset = 300f;
series.Styles[i].Text = customers.Skip(i).Take(1).FirstOrDefault().Name.ToString();
series.Styles[i].Font = new ChartFontInfo();
series.Styles[i].Font.Size = 8;
}
simpleChart.Series.Add(series);
simpleChart.ShowLegend = false;
simpleChart.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.AntiAlias;
simpleChart.Skins = ChartModelSkins.Office2007Blue;
simpleChart.BorderAppearance.SkinStyle = ChartBorderSkinStyle.Pinned;
ViewData.Model = simpleChart; // set the model for rendering on the page
return View();
} |
The web page is a very simple one-liner that renders the model into a simple bar chart using the Syncfusion Chart helper method. The helper method called Chart takes the MVCChartModel and renders the styles and data into the bar chart in figure 3
Listing 5 - ASP.NET MVC Page used to Render the Chart Model
|
<%@ Page Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage<MVCChartModel>" %>
<%@ Import Namespace="MvcGridForSalesForce.Models" %>
<%@ Import Namespace="Syncfusion.Mvc.Chart" %>
<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
Chart
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<h2><%: ViewData["Message"] %></h2>
<p>
<%=Html.Syncfusion().Chart("chart_Model", ViewData.Model)%>
</p>
</asp:Content> |
A Country Pie Chart of Leads
RSSBus allows you to access other data available in Salesforce, such as your sales leads. The sample code below shows you how to extract the data for Leads contained in your Salesforce.com account using the RSSBus Salesforce data provider. Note that the code is almost identical to the ADO.NET code used to obtain Account information. The only significant difference in populating the model is the actual query sent to the provider and the Lead object we use to store the resulting data from the query.
Listing 6 - Extracting Leads From Salesforce.com using RSSBus Data Provider
|
private List<Lead> ReadSalesForceLeadData()
{
// get the connection string from the web.config file
System.Configuration.Configuration rootWebConfig =
System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/MvcGridForSalesForce");
System.Configuration.ConnectionStringSettings connString;
if (rootWebConfig.ConnectionStrings.ConnectionStrings.Count > 0)
{
connString =
rootWebConfig.ConnectionStrings.ConnectionStrings["SalesForceConnectionString"];
// open the connection and read in the Salesforce Lead Data with the RSSBus Salesforce Provider
var connection = new SalesForceConnection(connString.ConnectionString);
var cmd = new SalesForceCommand("SELECT * FROM [Leads]", connection);
connection.Open();
var myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
var result = new List<Lead>();
while (myReader.Read())
{
var name = myReader["Name"] as string;
var industry = myReader["Industry"] as string;
var country = myReader["Country"] as string;
result.Add(new Lead { Name = name, Industry = industry, Country = country });
}
myReader.Close();
return result;
}
return new List<Lead>();
}
|
Once we have pulled the lead data from Salesforce, we can put the data into an MVChartModel to use it to render a pie chart by country. To create a pie chart with wedges representing different countries, we'll need a list of the countries. We'll use LINQ to pull out a list of all the distinct countries as shown in Listing 7. We can also use a LINQ query to help us calculate the percentages of leads for each country. Notice in this example we also set the chart to be 3D using the Series3D property, and we color all the different pie slices using the Styles property.
Listing 7 - Setting up the Syncfusion Chart to Render a 3d Pie Chart of Lead Location
|
public ActionResult PieChartOfCountry()
{
MVCChartModel pieChartModel = new MVCChartModel();
pieChartModel.Series3D = true;
pieChartModel.Text = "Leads % Per Country";
pieChartModel.Series.Clear();
var leads = ReadSalesForceLeadData();
ChartSeries series = new ChartSeries("Country");
int count = 0;
// calculate the percentage that each country represents in leads
var distinctCountries = leads.Select(lead => lead.Country).Distinct();
foreach (var country in distinctCountries)
{
var numberOfCountries = (double) leads.Where(lead => lead.Country == country).Count();
double percentageOfCountry = (numberOfCountries/(double)leads.Count()) * 100.0d;
series.Points.Add(count, percentageOfCountry);
count++;
}
series.Type = ChartSeriesType.Pie;
pieChartModel.Series.Add(series);
pieChartModel.ShowLegend = false;
pieChartModel.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.AntiAlias;
pieChartModel.Skins = ChartModelSkins.Office2007Blue;
pieChartModel.BorderAppearance.SkinStyle = ChartBorderSkinStyle.Pinned;
// set the text of each pie slice
for (int i = 0; i < distinctCountries.Count(); i++)
{
series.Styles[i].DisplayText = true;
series.Styles[i].Text = distinctCountries.Skip(i).Take(1).FirstOrDefault();
}
// set the color of each pie slice
series.Styles[0].Interior = new BrushInfo(GradientStyle.Horizontal, new[] { Color.FromArgb(177, 140, 188), Color.FromArgb(229, 197, 221), Color.FromArgb(201, 163, 202) });
series.Styles[1].Interior = new BrushInfo(GradientStyle.Horizontal, new[] { Color.FromArgb(191, 62, 35), Color.FromArgb(226, 83, 37), Color.FromArgb(255, 195, 127) });
series.Styles[2].Interior = new BrushInfo(GradientStyle.Horizontal, new[] { Color.FromArgb(139, 193, 58), Color.FromArgb(206, 222, 103), Color.FromArgb(227, 231, 135) });
series.Styles[3].Interior = new BrushInfo(GradientStyle.Horizontal, new[] { Color.FromArgb(255, 244, 42), Color.FromArgb(253, 240, 38), Color.FromArgb(255, 216, 25) });
series.Styles[4].Interior = new BrushInfo(GradientStyle.Horizontal, new[] { Color.FromArgb(112, 227, 220), Color.FromArgb(102, 175, 201), Color.FromArgb(104, 142, 191) });
series.Styles[5].Interior = new BrushInfo(GradientStyle.Horizontal, new[] { Color.FromArgb(255, 141, 51), Color.FromArgb(248, 183, 56), Color.FromArgb(249, 228, 70) });
ViewData.Model = pieChartModel;
return View();
}
|
The chart that results from the controller code in listing 7 is shown in figure 4. The chart below was rendered to an entire ASP.NET MVC Page. If we wanted to display several pie charts showing different Salesforce information in the same page, we could have created a few partial views and used similar controller code to view the different charts side by side.
Figure 4 - 3d Pie Chart of Lead Data from Salesforce
Conclusion
To leverage our CRM data inside of Salesforce.com, we can look to the power of RSSBus to greatly simplify our task through a data provider. With Syncfusion technology, we can take this data and display it in our own web page using several available Microsoft supported development environments including ASP.NET MVC, Classic ASP.NET, or Silverlight. Syncfusion gives us a flexible and highly functional grid to allow us to view our data interactively. It also provides us with a range of charting capabilities to see the data we've extracted from Salesforce. Note that using the Syncfusion and RSSBus products, we could have just as easily written our Salesforce application for the desktop as well. I'm looking forward to seeing WP7 mobile solutions from these companies in the future. Finally, if you are creating applications for Salesforce and need components to aid you in writing professional applications that manipulate and display data for the popular CRM, look no further. These two products will help you get your job done efficiently so you can enforce your reputation as a good programmer.