2
\$\begingroup\$

The intent of this code is to access a third party OData API to capture only the relevant data to an existing in-house set of MSSQL server tables. Ignore the exception handling for now--the errors will get written to a log. I'm not there yet.

I have no ego attached to this code. If any of you have better practical approaches to managing 3rd party OData/API extracts, please suggest them. I have only one caveat, that is that the C# snippets you see here will actually live in an SSIS script component--meaning that any 3rd party tools, libraries, etc. will not be available through NuGet.

Here's the core access class snippet:

           string fodepartmentEntitiesName = "FODepartment";
           string odataFODepartmentQueryParameters = "$orderby=externalCode desc&$top=2";
           string requestFODepartmentUri = String.Format("{0}?{1}", fodepartmentEntitiesName, odataFODepartmentQueryParameters);
           HttpResponseMessage responseFODepartment = client.GetAsync(requestFODepartmentUri).Result;

           string contentFODepartmentAsString = responseFODepartment.Content.ReadAsStringAsync().Result;

           XmlDocument xmfod = new XmlDocument();
           xmfod.LoadXml(contentFODepartmentAsString);

           // write whole object to dictionary
           odataSF.AddToDictionary(fodepartmentEntitiesName, xmfod);

This is later in the code:

           // read back object from dictionary
           XmlDocument xdocfod = new XmlDocument();
           xdocfod = odataSF.ReadFromDictionaryByKey(fodepartmentEntitiesName);

This takes the raw xml captured in the Dictionary through a cleansing process -- which includes shaving off the namespaces to a bare-bones xml doc to pass off to sql server. The original xml is kept separately in an archive table later in the code.

           string docfodepartments = extractSFobjects.GetFODepartmentData(xdocfod);
           XmlDocument xmlfodepartments = new XmlDocument();

           // This is our now cleansed/abbreviated xml for mssql consumption
           xmlfodepartments.LoadXml(docfodepartments);

Snippet from GetDODepartmentData-- This is very brittle, but sadly it is all I could come up with today.

    public string GetFODepartmentData(XmlDocument xdoc)
    {
        string docfodepartments = "";

        try
        {
            XmlNodeReader nodeReader = new XmlNodeReader(xdoc);
            XmlReaderSettings settings = new XmlReaderSettings();
            settings.ValidationType = ValidationType.None;

            StringBuilder sb = new StringBuilder();
            string endtag = "</FODepartments>";
            string begtag = "<FODepartments>";

            // used for restating raw Xml to abbreviated, easy to pass on to mssql cleansed version
            XmlDocument xmlfodepartments = new XmlDocument();


            // obtain the raw xml from success factors 
            XmlReader reader = XmlReader.Create(nodeReader, settings);
            XElement data = XElement.Load(reader);
            XmlNameTable table = reader.NameTable;
            XmlNamespaceManager manager = new XmlNamespaceManager(table);
            manager.AddNamespace("ns", "http://www.w3.org/2005/Atom");
            manager.AddNamespace("m", "http://schemas.microsoft.com/ado/2007/08/dataservices/metadata");
            manager.AddNamespace("d", "http://schemas.microsoft.com/ado/2007/08/dataservices");
            manager.AddNamespace("base", "https://api20.ourthirdparty.com:443/odata/v2/");
            foreach (XElement entries in data.XPathSelectElements("./ns:entry", manager))
            {
                foreach (var contents in (from entry in entries.Elements() where entry.Name.LocalName.Contains("content") select entry))
                {
                    foreach (var properties in (from content in contents.Elements() where content.Name.LocalName.Contains("properties") select content))
                    {

                        foreach (var property in (from property in properties.Elements()
                                                  where property.Name.LocalName.Contains("startDate")
                                                         || property.Name.LocalName.Contains("externalCode")
                                                         || property.Name.LocalName.Contains("headOfUnit")
                                                         || property.Name.LocalName.Contains("status")
                                                         || property.Name.LocalName.Contains("parent")
                                                         || property.Name.LocalName.Contains("location")
                                                         || property.Name.LocalName.Contains("endDate")
                                                         || property.Name.LocalName.Contains("lastModifiedDateTime")
                                                         || property.Name.LocalName.Contains("lastModifiedOn")
                                                         || property.Name.LocalName.Contains("createdOn")
                                                         || property.Name.LocalName.Contains("createdBy")
                                                         || property.Name.LocalName.Contains("name")
                                                         || property.Name.LocalName.Contains("createdDateTime")
                                                         || property.Name.LocalName.Contains("lastModifiedBy")


                                                  select property)

                                 )

                            // get all the instances the positions w/tags and values
                            sb.Append(string.Format("<{0}>{1}</{0}>", property.Name.LocalName, property.Value, property.Name.LocalName));

                        // add the root tag sets
                        docfodepartments = begtag + sb.ToString() + endtag;

                    }

                }

            }

                // Correct missing tags with regex
            // Note: This code is a pure hack and needs to be revisited.
            Regex tag1 = new Regex(@"<startDate>");
            Regex tag2 = new Regex(@"</lastModifiedBy>");
            docfodepartments = tag1.Replace(docfodepartments, "<FODepartment><startDate>");
            docfodepartments = tag2.Replace(docfodepartments, "</lastModifiedBy></FODepartment>");
            // Correct ampersand xml conversion failures
            // Note: The ampersand was an ill-placed value from a test case, but still made it to the raw xml--there's nothing to prevent it.

            docfodepartments = docfodepartments.Replace("&", "and");  // return scrubbed string for xdoc conversion

        }
        catch (Exception ex)
        {
            // Console.WriteLine("Failed to collect Position data: " + ex.Message + " docpositons set to: " + docpositions);
            // will return a stub doc back.
            docfodepartments = bodydoc + " FODepartments " + enddoc;
        }

        return docfodepartments;
    }

    #endregion

Then finally the xdoc gets passed off to an existing sql server stored proc.

    #region invoke load FODepartment
    public void LoadOurThirdPartyFODepartmentData(XmlDocument xmlfodepartments)
    {
        try
        {
            dao.InsertFODepartmentRows(xmlfodepartments, stagingdb);
        }
        catch (Exception dx)
        {
            Console.WriteLine("Data Access Layer Exception: " + dx.Message + "\n");
        }

    }
    #endregion
\$\endgroup\$
0

1 Answer 1

5
\$\begingroup\$

Focusing on GetFODepartmentData() method

This method is really hard to read, especially the inner most loop, where you didn't add braces {}.

After extracting the creation of the XmlNamespaceManager object to a separate method

private XmlNamespaceManager GetXmlNamespaceManager(XmlNameTable table)
{
    XmlNamespaceManager manager = new XmlNamespaceManager(table);
    manager.AddNamespace("ns", "http://www.w3.org/2005/Atom");
    manager.AddNamespace("m", "http://schemas.microsoft.com/ado/2007/08/dataservices/metadata");
    manager.AddNamespace("d", "http://schemas.microsoft.com/ado/2007/08/dataservices");
    manager.AddNamespace("base", "https://api20.ourthirdparty.com:443/odata/v2/");
    return manager;
}  

and extracting the Contains condition beast to a separate method

private bool IsNeededProperty(String localName)
{
    return localName.Contains("startDate")
        || localName.Contains("externalCode")
        || localName.Contains("headOfUnit")
        || localName.Contains("status")
        || localName.Contains("parent")
        || localName.Contains("location")
        || localName.Contains("endDate")
        || localName.Contains("lastModifiedDateTime")
        || localName.Contains("lastModifiedOn")
        || localName.Contains("createdOn")
        || localName.Contains("createdBy")
        || localName.Contains("name")
        || localName.Contains("createdDateTime")
        || localName.Contains("lastModifiedBy");
}  

Which could be done better, like adding a IList<String> containing these strings and then using a loop which returns true at the moment where localName.Contains(element) would be true.

Your former method could be reduced to

public string GetFODepartmentData(XmlDocument xdoc)
{
    string docfodepartments = String.Empty;

    try
    {
        XmlNodeReader nodeReader = new XmlNodeReader(xdoc);
        XmlReaderSettings settings = new XmlReaderSettings();
        settings.ValidationType = ValidationType.None;

        StringBuilder sb = new StringBuilder();
        string endtag = "</FODepartments>";
        string begtag = "<FODepartments>";

        // obtain the raw xml from success factors 
        XmlReader reader = XmlReader.Create(nodeReader, settings);
        XElement data = XElement.Load(reader);
        XmlNameTable table = reader.NameTable;
        XmlNamespaceManager manager = GetXmlNamespaceManager(table);

        XElement entry = data.XPathSelectElements("./ns:entry", manager).Last();
        XElement contents = entry.Elements().Last(el => el.Name.LocalName.Contains("content"));
        XElement properties = contents.Elements().Last(el => el.Name.LocalName.Contains("properties"));

        foreach (var property in (from property in properties.Elements()
                                    where IsNeededProperty(property.Name.LocalName)
                                    select property)
                    )
        {
            // get all the instances the positions w/tags and values
            sb.Append(string.Format("<{0}>{1}</{0}>", property.Name.LocalName, property.Value, property.Name.LocalName));
        }

        // add the root tag sets
        docfodepartments = begtag + sb.ToString() + endtag;


        // Correct missing tags with regex
        // Note: This code is a pure hack and needs to be revisited.
        Regex tag1 = new Regex(@"<startDate>");
        Regex tag2 = new Regex(@"</lastModifiedBy>");
        docfodepartments = tag1.Replace(docfodepartments, "<FODepartment><startDate>");
        docfodepartments = tag2.Replace(docfodepartments, "</lastModifiedBy></FODepartment>");
        // Correct ampersand xml conversion failures
        // Note: The ampersand was an ill-placed value from a test case, but still made it to the raw xml--there's nothing to prevent it.

        docfodepartments = docfodepartments.Replace("&", "and");  // return scrubbed string for xdoc conversion

    }
    catch (Exception ex)
    {
        // Console.WriteLine("Failed to collect Position data: " + ex.Message + " docpositons set to: " + docpositions);
        // will return a stub doc back.
        docfodepartments = bodydoc + " FODepartments " + enddoc;
    }

    return docfodepartments;
}  

Consider to use String.Empty over "" as it is cleaner to read.

As the XmlReader implements IDisposable it should be enclosed inside a using statement.

The replacements using regex and string, should be extracted to a separate method also.

\$\endgroup\$
2
  • \$\begingroup\$ thanks so much for weeding through this. I will implement your suggestions--right now there are 16+ of these objects similar to FODepartment. \$\endgroup\$
    – plditallo
    Oct 29, 2014 at 16:46
  • 1
    \$\begingroup\$ "using a loop which returns true at the moment where localName.Contains(element) would be true" Or you can use LINQ: return elementNames.Any(name => localName.Contains(name));. \$\endgroup\$
    – svick
    Nov 2, 2014 at 11:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.