In this post I will take the same query and show a little slight of code. The query was:
SELECT Name,
(SELECT Name, Email FROM Contacts),
(SELECT CaseNumber, CreatedDate, Subject, Status, Resolution__c, ClosedDate, SuppliedEmail,SuppliedName FROM Cases
WHERE CreatedDate = LAST_N_DAYS:366
AND Origin != 'Internal'
AND OwnerId != '00G40000001RTyyEAG'
AND RecordTypeId IN ('01240000000UTWP', '01240000000UWxM', '01240000000UWxl', '01240000000UWxb')
)
FROM Account
WHERE ID IN (SELECT AccountId FROM Contact Where Email in ({0}))
Extension Method to Handle SOQL Result Columns
The code below takes the results from the query and creates a DataTable from one column. This is our fundamental building block suitable for all SalesForce queries.
You could call this in a nested manner if you have nested objects in your query -- but I assume just one layer deep.
///
/// Converts a Salesforce style XMLElement into a datatable
///
/// A "Column" returned from Salesforce
/// DataTable
public static DataTable SalesForceToDataTable(this XmlElement element)
{
var result = new DataTable(element.LocalName);
if (element.ChildNodes.Count == 1)
{
if (!result.Columns.Contains(element.LocalName))
{
result.Columns.Add(element.LocalName);
}
DataRow row = result.NewRow();
row[element.LocalName] = element.InnerText;
result.Rows.Add(row);
}
else
foreach (XmlNode child in element.ChildNodes)
{
switch (child.LocalName)
{
case "size":
case "queryLocator":
case "done": break;
case "records":
DataRow row = result.NewRow();
foreach (XmlElement field in child.ChildNodes)
{
var colName = element.LocalName + "_" + field.LocalName;
if (!result.Columns.Contains(colName))
{
result.Columns.Add(colName);
}
row[colName] = field.InnerText;
}
result.Rows.Add(row);
break;
}
}
return result;
}
Building and linking a DataSet across columns
As we saw in our last post, we have subqueries appearing in a single column, as illustrated below:
The routine above handles each column. To put it all together, we use the code below.
public static DataSet QueryCases(string myquery)
{
var result = new DataSet();
var binding = new AdminConsole.WebSalesForce.SforceService();
binding.SessionHeaderValue = new AdminConsole.WebSalesForce.SessionHeader();
var salesforce = SaleforceUrl;
if (salesforce != null)
{
binding.SessionHeaderValue.sessionId = salesforce.sessionId;
binding.Url = salesforce.serverUrl;
binding.UseDefaultCredentials = true;
var results = binding.query(myquery);
var done = false;
while (!done && results.size > 0)
{
var masterKey = 0;
foreach (var record in results.records)
{
var tableIndex = 0;
foreach (XmlElement key in record.Any)
{
var newTable = key.SalesForceToDataTable();
newTable.Columns.Add(DataSetKey, masterKey.GetType());
foreach (DataRow row in newTable.Rows)
{
row[DataSetKey] = masterKey;
}
// On the first table we ADD to dataset, on subsequent we append the records
if (masterKey > 0)
{
foreach (DataRow row in newTable.Rows)
{
result.Tables[tableIndex].ImportRow(row);
}
}
else
{
result.Tables.Add(newTable);
}
if (result.Tables.Count > 1)
{
result.Relations.Add(result.Tables[0].Columns[DataSetKey], newTable.Columns[DataSetKey]);
}
tableIndex++;
}
}
masterKey++;
done = results.done;
}
}
return result;
}
Converting Linked DataSet to DataTable
What we are doing is adding a new key to tie the data in each column together and adding it as a relationship to the dataset. This is generic code still -- works for almost any query.
The last step is flattening this dataset into a data table so I may display it in a Telerik Grid. I need to match case to contact (which would be easy with a proper SQL language). My solution is shown below:
private DataTable FlattenDataSet(DataSet dataset)
{
var result = new DataTable();
try
{
// Create all columns
foreach (DataTable table in dataset.Tables)
{
foreach (DataColumn col in table.Columns)
{
if (!result.Columns.Contains(col.ColumnName))
result.Columns.Add(col.ColumnName);
}
}
//Import the 1:1 relationships into the table
foreach (DataRow row in dataset.Tables["Cases"].Rows)
{
result.ImportRow(row);
}
// Import the 1: of the 1:m relationship
foreach (DataRow master in dataset.Tables["Name"].Rows)
{
DataRow[] rd = result.Select(DAL.daSalesForce.DataSetKey + "=" + master[DAL.daSalesForce.DataSetKey]);
foreach (DataRow row in rd)
{
foreach (DataColumn col in dataset.Tables[0].Columns)
{
row[col.ColumnName] = master[col.ColumnName];
}
}
}
// For each contact (expected to be fewer than cases) We lookup the cases they submitted
foreach (DataRow master in dataset.Tables["Contacts"].Rows)
{
DataRow[] rd = result.Select("Cases_ContactId='" + master["Contacts_Id"] + "'");
foreach (DataRow row in rd)
{
foreach (DataColumn col in dataset.Tables["Contacts"].Columns)
{
row[col.ColumnName] = master[col.ColumnName];
}
}
}
return result;
}
else
{
return (DataTable)Session["SalesForce"];
}
}
Bottom Line
Salesforce SOQL results can be handled in .Net using an extension and moving the data into a DataSet. Once the data is there, you may have a little dataset grunting to get it transformed into the appropriate format.
Posts
