+ Start a Discussion
Chris987654321Chris987654321 

Querying __r relationship fields

I am having trouble with doing relationship queries in C#. I'm using the Partner WSDL

 

 

 string query = "select Account.Id, Account.Name, Id from Contact LIMIT 10";
                        
 DataTable table = GetSforceData(query, 200);
 dataGridView1.DataSource = table;

 

DataTable GetSforceData(string strSQL, int iBatchSize)
        {
            DataTable dt = null;
            
            SforceService binding = new SforceService();
            
           
            //get account name, account password and security token here            
            LoginResult lr = binding.login(strAccountName,strAccountPassword + strSecurityToken);

            if (!lr.passwordExpired)
            {
                binding.Url = lr.serverUrl;
                binding.SessionHeaderValue = new partnerWSDL.SessionHeader();
                binding.SessionHeaderValue.sessionId = lr.sessionId;

                partnerWSDL.QueryResult qr = null;
                binding.QueryOptionsValue = new partnerWSDL.QueryOptions();
                binding.QueryOptionsValue.batchSize = iBatchSize;
                binding.QueryOptionsValue.batchSizeSpecified = true;
                try
                {
                    qr = binding.query(strSQL);
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.ToString(), "Error peforming query");
                    return null;
                }

                bool done = false;

                if (qr.size > 0)
                {
                    dt = new DataTable();
                    partnerWSDL.sObject sOtmp = (partnerWSDL.sObject)qr.records[0];
                    
                    for (int i = 0; i < sOtmp.Any.Length; i++)
                    {
                        dt.Columns.Add(sOtmp.Any[i].LocalName);
                    }
                    string[] prValues = new string[sOtmp.Any.Length];
                    while (!done)
                    {
                        for (int i = 0; i < qr.records.Length; i++)
                        {
                            sOtmp = (partnerWSDL.sObject)qr.records[i];
                            for (int j = 0; j < sOtmp.Any.Length; j++)
                            {
                                if (sOtmp.Any[j].ChildNodes.Count > 1)
                                {
                                    prValues.SetValue(sOtmp.Any[j].ChildNodes[2].InnerText, j);
                                }
                                else
                                {
                                    prValues.SetValue(sOtmp.Any[j].InnerText, j);
                                }
                            }

                            dt.Rows.Add(prValues);
                        }

                        if (qr.done)
                        {
                            done = true;
                        }
                        else
                        {
                            qr = binding.queryMore(qr.queryLocator);
                        }
                    }
                }
            }

            return dt;
             
        }

 

 

So the results will only show the Account.Id not the Account.Name.

 

But it if I remove the Account.Id from the query then it will show the Account.Name.

 

It seems like it only can handle one __r type of field per query? Is this a limitation of the API?

 

 

Thanks,

Chris

Best Answer chosen by Admin (Salesforce Developers) 
SuperfellSuperfell

You appear to always pick the 2nd child element 

sOtmp.Any[j].ChildNodes[2].InnerText
Each field from account will be one of these child nodes, so you need to loop through the ChildNodes to get to each of the fields.

All Answers

SuperfellSuperfell

You appear to always pick the 2nd child element 

sOtmp.Any[j].ChildNodes[2].InnerText
Each field from account will be one of these child nodes, so you need to loop through the ChildNodes to get to each of the fields.
This was selected as the best answer
Chris987654321Chris987654321

Thanks, that helped a lot. Here's my updated code in case someone finds it useful. This code is working great:

 

 

 

DataTable GetSforceData(string strSQL, int iBatchSize)
        {
            DataTable dt = null;
            
            #region Query
            
            if (!lr.passwordExpired)
            {
                binding.Url = lr.serverUrl;
                binding.SessionHeaderValue = new partnerWSDL.SessionHeader();
                binding.SessionHeaderValue.sessionId = lr.sessionId;

                partnerWSDL.QueryResult qr = null;
                binding.QueryOptionsValue = new partnerWSDL.QueryOptions();
                binding.QueryOptionsValue.batchSize = iBatchSize;
                binding.QueryOptionsValue.batchSizeSpecified = true;
                try
                {
                    qr = binding.query(strSQL);
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.ToString(), "Error peforming query");
                    return null;
                }
            #endregion

                bool done = false;

                if (qr.size > 0)
                {
                    dt = new DataTable();
                    partnerWSDL.sObject sOtmp = (partnerWSDL.sObject)qr.records[0];
                    
                    //string[] prValues = new string[sOtmp.Any.Length];
                    List<string> prValues = new List<string>();


                    #region Columns
                    for (int i = 0; i < sOtmp.Any.Length; i++)
                    {

                        if (sOtmp.Any[i].ChildNodes.Count > 1)
                        {
                            for (int k = 2; k < sOtmp.Any[i].ChildNodes.Count; k++)
                            {
                                dt.Columns.Add("XXXXXXX_" + i + "_" + k );
                                
                            }

                        }

                        else
                        {
                            dt.Columns.Add("XXXXXXX_" + i );
                        }
                    }
                    #endregion

                    #region Rows
                    //string[] prValues = new string[sOtmp.Any.Length];
                    while (!done)
                    {
                        for (int i = 0; i < qr.records.Length; i++)
                        {
                            sOtmp = (partnerWSDL.sObject)qr.records[i];
                            for (int j = 0; j < sOtmp.Any.Length; j++)
                            {
                                Console.WriteLine("---> J = " + j);


                                Console.WriteLine("Count = " + sOtmp.Any[j].ChildNodes.Count.ToString());
                                int count = 0;
                                if (sOtmp.Any[j].ChildNodes.Count > 1)
                                {
                                    
                                    for (int k = 2; k < sOtmp.Any[j].ChildNodes.Count; k++)
                                    {
                                        
                                        //Console.WriteLine("1 --> " + sOtmp.Any[j].ChildNodes[0].InnerText.ToString());
                                        //Console.WriteLine("2 --> " + sOtmp.Any[j].ChildNodes[1].InnerText.ToString());
                                        //Console.WriteLine("3 --> " + sOtmp.Any[j].ChildNodes[2].InnerText.ToString());
                                        //Console.WriteLine("4 --> " + sOtmp.Any[j].ChildNodes[3].InnerText.ToString());
                                        Console.WriteLine(k + " --> " + sOtmp.Any[j].ChildNodes[k].InnerText.ToString());
                                        

                                        //prValues.SetValue(sOtmp.Any[j].ChildNodes[k].InnerText, j + count);
                                        prValues.Add(sOtmp.Any[j].ChildNodes[k].InnerText);
                                        count++;
                                    }
                                }

                                else
                                {
                                    Console.WriteLine("1 --> " + sOtmp.Any[j].ChildNodes[0].InnerText.ToString());
                                    //prValues.SetValue(sOtmp.Any[j].InnerText, j + count);
                                    prValues.Add(sOtmp.Any[j].InnerText);
                                    count++;
                                }
                            }
                            string[] array = null;

                            try
                            {
                                array = prValues.ToArray();
                                dt.Rows.Add(array);
                                prValues.Clear();
                            }
                            catch (Exception e)
                            {
                                MessageBox.Show(e.ToString());
                                MessageBox.Show(array.Length.ToString());
                                
                            }
                        }

                        if (qr.done)
                        {
                            done = true;
                        }
                        else
                        {
                            qr = binding.queryMore(qr.queryLocator);
                        }
                    }
                }
            }
            #endregion


            return dt;
             
        }