Click here to Skip to main content
Click here to Skip to main content

DnB Company Tree Comparison

, 31 Jul 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
An Application to view D&B Company Tree data and compare to Tree Data in CRM

Please note

This article is an entry in our DnB Developer Challenge. Articles in this sub-section are not required to be full articles so care should be taken when voting.

Introduction

This article shows the implementation for the idea I proposed to retrieve the D&B company family tree data, display the data in an ASP.NET TreeView control and indicate which companies in the tree are already in my CRM database.

The application uses the D&B Sandbox data on Windows Azure. The D&B Sandbox contains 10,000 business records sampled from D&B's database of over 215 Million business records. The business records are accessed via a cloud hosted API that provides a variety of functions for accessing the content.

The Application that I wrote is an ASP.NET web site meant to take advantage of the Company Family Tree data
available through the API. Given the DUNS number of a Company, the application will retrieve all the related companies from the Sandbox data. My application will process the data and display it in a treeview control. I also retrieve the Company data from my CRM database and indicate the nodes in the tree that are also in the CRM by checking the textbox associated with the node.

It is very useful for our business to be able to see the family tree with indicators of which companies we have already sold to and what companies we might want to reach out to. Once we see the tree we can find out more information about a subsidiary and sell them a product. We can also use the data to clean data records in our CRM and add records that are missing.

Background

Each company location has a unique number assigned to it called a DUN. It is a unique 9 digit number assigned by D&B. This data has been collected for over 50 years and is used by businesses and government agencies. Once a DUNS number is assigned it remains with the company forever.

One of the basic functions available through the API retrieves the Company Family Hierarchy information when a DUNS number is provided. (If you do not know the DUNS it can be retrieved via a different API call.) This shows the legal structure of the company group or family such as if it is the Headquarters, Subsidiary, Parent or Ultimate Parent. It can be used in a CRM to show related accounts or used to research companies for marketing purposes.

Our CRM stores the DUNS on the account record. With the DUNS number we can use the API to retrieve the company hierarchy. We usually only have a subset of the companies in the CRM from the hierarchy so it is very helpful to be able to see what parts of the full tree that we have. From that view we can decide if we want to add companies into our CRM.

Using the code

I used ASP.NET for my application because it has a good treeview control available. I used C# as my programming language.

During my investigation of the Sandbox data I discovered that there were no full company tree available in the data and one could not be provided in the short time range of the contest. With only 10,000 records this is not really surprising because many trees have 100's of records in the hierarchy. I worked around this limitation by finding a company (Wells Fargo) that had 13 records with the same ultimate parent (the root node) The data had been scrubbed so the immediate parents DUNS were all set to 0 for these companies so I filled in the parent DUNS in with a combination of the DUNS numbers of the 13 available records to make a tree. The majority of my code is generic to use for any tree but I will point out the sections just to make my test set work.

The first step is to sign up for access to the Sandbox. Without a UserID and Key you can not access the Sandbox.

Here is the code to connect to the Sandbox

 string USER_ID = "Your UserId";
 string SECURE_ACCOUNT_KEY = ey"Your Account K";
 string SERVICE_ROOT_URI = "https://api.datamarket.azure.com/DNB/DeveloperSandbox/v1";
 var uri = new Uri(SERVICE_ROOT_URI);
 var data = new DnB.DNBDeveloperSandboxContainer(uri);
 data.Credentials = new NetworkCredential(USER_ID, SECURE_ACCOUNT_KEY); 

Next I use the DUNS number provided in a LINQ query to retrieve the Family Hierarchy record for that company.

var Company = (from c in data.FamilyHierarchy
 where c.DUNSNumber == DUNS
 select c);  

From this record I retrieve the DomesticUltimateDUNS which is the root node or headquarters for that company. Then I use another LINQ Query to select all the companies that have the same DomesticUltimateDUNS. I am using the example Wells Fargo and there are 13 companies returned.

string Ultimate = Company.ToList()[0].DomesticUltimateDUNS;
 var companies = (from d in data.FamilyHierarchy
 where d.DomesticUltimateDUNS == Ultimate
 select d); 

Now I have to do a bit of finagling of date. As I mentioned before there are no full trees in the data so I take the records that there are and add link them to each other by setting the ParentDUNS to one of the other 13 so they are in a hierarchy.

var altered = companies.ToList();
altered[0].ParentDUNS = null;
altered[1].ParentDUNS = "003460458";
altered[2].ParentDUNS = "031730658";
altered[3].ParentDUNS = "079006771";
altered[4].ParentDUNS = "079608345";
altered[5].ParentDUNS = "079608345";
altered[6].ParentDUNS = "079608345";
altered[7].ParentDUNS = "079608345";
altered[8].ParentDUNS = "152433470";
altered[9].ParentDUNS = "153247114";
altered[10].ParentDUNS = "031730658";
altered[11].ParentDUNS = "156107596";
altered[12].ParentDUNS = "156151383"; 
 

Now I have the data I need to fill my tree view. Each item in the treeview is a node. I define a class that is a node item for my tree.

 public class TreeViewNodeItem
<pre>{
 public int ID { get; set; }
 public int ParentID { get; set; }
 public string CompanyName { get; set; }
} 

Now I create Nodes for each of 13 records returned by the D&B Sandbox call. I could show any of the data in the tree view that I was interested in from the returned record such as CEO or address information but for now I want to see the DUNS and the Company name.

foreach (var c in altered)
{
    treeViewList.Add(new TreeViewNodeItem()
    {
        ID = Convert.ToInt32(c.DUNSNumber),
        ParentID = Convert.ToInt32(c.ParentDUNS),
        CompanyName = c.Company
    });
}  

Next I bind my nodes to the tree at the appropriate level based on the parent DUNS. This requires recursion so the parent and child nodes are populated

private void BindTree(IEnumerable<TreeViewNodeItem> list, TreeNode parentNode)
{
    var nodes = list.Where(x => parentNode == null ? x.ParentID == 0 : x.ParentID ==int.Parse(parentNode.Value));
foreach (var node in nodes)
{
 TreeNode newNode = new TreeNode((node.ID + " " + node.CompanyName), node.ID.ToString());
if (parentNode == null)
{
    TreeView1.Nodes.Add(newNode);
}
else
{
    parentNode.ChildNodes.Add(newNode);
}
    BindTree(list, newNode);
}
}  

The Tree View is now fully populated with the data from the Sandbox. Now I want to Get the data from my CRM database table. I create a SQL connection and retrieve the data from the Company table. My test table is really simple and just contains 3 rows that are all Wells Fargo companies.

 private void RetrieveCompaniesFromCRM(List<Int32> CompaniesinCRM)
{
 SqlConnection con = newSqlConnection(WebConfigurationManager.ConnectionStrings["ConnString"].ToString());
 SqlDataAdapter SqlDataAdapter = new SqlDataAdapter();
 SqlCommand SqlCommand = new SqlCommand();
con.Open();
SqlCommand.CommandText = "select * from Company ";
SqlCommand.Connection = con;
 SqlDataReader dr = SqlCommand.ExecuteReader();
 if (dr.HasRows)
{
 while (dr.Read())
{
 Int32 data = dr.GetInt32(2);
CompaniesinCRM.Add(data);
}
}
dr.Close();
con.Close();
} 

Finally, I search the tree for the values returned from the CRM and Set the checkbox to "Checked" when I find the matching companies. This is also a recursive function so I am able to drill down into the child nodes of the treeview data.

 foreach (Int32 dnum in CompaniesinCRM)
<pre>{
<pre>DUNS = dnum.ToString();
FindByText(DUNS);
} 
  private void FindByText(String DUNS)
{
 TreeNodeCollection nodes = TreeView1.Nodes;
 foreach (TreeNode n in nodes)
{
 int Length = DUNS.Length;
 if (n.Text.ToString().Substring(0, Length) == DUNS.Substring(0, Length))
n.Checked = true;
FindRecursive(n, DUNS);
}
}
 private void FindRecursive(TreeNode treeNode, String DUNS)
{
 foreach (TreeNode tn in treeNode.ChildNodes)
{
 int Length = DUNS.Length;
 if (tn.Text.ToString().Substring(0, Length) == DUNS.Substring(0, Length))
tn.Checked = true;
FindRecursive(tn, DUNS);
}
}
} 

So now I have a treeview with the full data from the D&B sandbox and the companies that I already have in my CRM are shown with a checkbox. This way I can decide if there are other related companies that I can market to or need to add to CRM. 

 The Treeview

What the treeview looks like

Points of Interest

The D&B sandbox is an Azure cloud offering. This means that in using the real application transactions that use the API to retrieve data must be paid for. This means it is to the best advantage to minimize the number of transactions used to retrieve the company data. In my application I use two transactions to retrieve the entire tree, The first to get the DomesticUltimateDUNS and the second to get all the companies that have the same DomesticUltimateDUNS. Then I do my own processing to create the tree, This is a fairly economical use of the API.

There are a few things I know I can improve upon if there were more time:

  1. Allowing the user to enter the company name and address instead of the DUNS since they may not already know the DUNS Number.
  2. Change the treeview checkboxes to highlights of the node or something else that the user can not click to update.
  3. Use a better more realistic way to retrieve the data from the CRM database. In my old CRM code I got the family tree by using SQL common table expressions and recursion.

History

Keep a running update of any changes or improvements you've made here.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

relhok13
Database Developer Minitab
United States United States
No Biography provided

Comments and Discussions

 
QuestionDUNSNumber Pinmembermudnug30-Jul-13 19:38 
AnswerRe: DUNSNumber Pinmemberrelhok1331-Jul-13 2:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.1411023.1 | Last Updated 31 Jul 2013
Article Copyright 2013 by relhok13
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid