Tuesday, December 31, 2013

Converting SalesForce SOQL results to a Linked Dataset for use by C#

In my last post, I illustrated the 'simple' way of getting the data back - by just creating a Xml Document from the results. This is not always efficient -- in fact, I have gotten out of system memory errors on some queries.

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.

Monday, December 30, 2013

Handing Salesforce SOQL Relationship aka Joins in C#

Salesforce SOQL is not SQL which often causes great frustrations to experienced database developers when they work with it. Being of the generation that worked with Network Data Models and Hierarchical Data Model  - i.e. pre-Relational Databases! (as well as database on Tape!), I actually find a lot of "familiar tones" in SOQL.

Recently I was needing to build a query that in SQL would be:

SELECT Account.Name AS AccountName, 
  Contact.Name, Contact.Email, 
  Case.CaseNumber, Case.CreatedDate, Case.Subject, Case.Status, Case.Resolution__c,  Case.ClosedDate, Case.SuppliedEmail,      Case.SuppliedName 
 FROM Account  JOIN Case ON ... JOIN Contact ON ....
 WHERE Contact.Email in ({0})
  AND Case.CreatedDate = LAST_N_DAYS:366 
   AND Case.Origin != 'Internal' 
   AND Case.OwnerId !=     '00G40000001RTyyEAG' 
   AND Case.RecordTypeId IN ('01240000000UTWP', '01240000000UWxM', '01240000000UWxl', '01240000000UWxb')

The equivalent query in SOQL turned out to be less complex to read (because of the intrinsic joins that exists in Salesforce). Understanding these intrinsic - relationships is essential for writing queries!

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}))

The next piece is dealing with the query results. When you execute the above in the developer's console, you will see that the data returns as JSON.
This means a little magic when the response is received. What we have received is a dataset consisting of three linked tables. There are two approaches that seem obvious:
  • Explode the dataset into a single data table
  • Use the dataset as a linked table
In my case, I needed to display the results in a flat grid of cases, so I went down the first path. There's a little magic that needed to be done because one account has many contacts(1:n) and many cases (1:m). This means that you need to walk the cases, and lookup the contact for each case record. As a result, I have to include the ContactId in Cases to flatten the data structure and get the matching contact ID. The result should be a table with m rows.


SELECT Name, 
  (SELECT Id, Name, Email FROM Contacts), 
  (SELECT ContactId, 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}))

Examining the results reveal that we are dealing with XML and can thus convert them easily via XML activites
I noticed that applying LINQ to SOQL results seems to be difficult for some, but if you work at the XML level, some of the pain disappears quickly.

The code below uses the "fat" XmlDocument (and thus should not be used in real systems) for purposes of illustration:

        public static XmlDocument QueryCases(string myquery)
        {
            XmlDocument dom = new XmlDocument();
            dom.LoadXml("");
            XmlNode recordNode;
            try
            {
                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)
                    {
                        dom.DocumentElement.AppendChild(recordNode = dom.CreateElement("record"));
                        foreach (var record in results.records)
                        {
                            foreach (XmlElement key in record.Any)
                            {
                                recordNode.AppendChild(dom.ImportNode(key, true));
                            }
                        }
                        done = results.done;
                    }
                }
            }
            catch (Exception ex)
            {
                AdminConsole.Diagnostics.SplunkLogger.Error(ex);
            }           
            return dom;
        }

Now you have your data is a format that you can proceed easily with via LINQ etc.