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.

Comments

Popular posts from this blog

Yet once more into the breech (of altered programming logic)

Simple WP7 Mango App for Background Tasks, Toast, and Tiles: Code Explanation

How to convert SVG data to a Png Image file Using InkScape