0

Can I write a CAML query inside a for loop?

Copied from comment

I have a funds list which displays funds and user name. I have populated a DataTable into a GridView and able to display all the data perfectly. Now I need to add 4 columns and need to add user details {ph,address,name and guid }. So while I loop in the DataTable, I check the row with user name and then query the Users list. I was thinking if its ok/best practice to write a CAML query inside a for loop

    fundEPTable = new DataTable();
        query.Query = querynine;
        FundDataCollection = listFunds.GetItems(query);
        if (FundDataCollection.Count > 0 && FundDataCollection != null)
        {
            fundEPTable = FundDataCollection.GetDataTable();
            fundsTableView = fundEPTable.DefaultView;
            fundsTableView.Sort = "FundName ASC";
            fundEPTable = fundsTableView.ToTable();


            for (int i = 0; i < fundEPTable.Rows.Count; i++)
            {
                if (fundEPTable.Rows[i]["CurrentComp"].ToString() == "0")
                {
                    fundEPTable.Rows[i]["CurrentComp"] = "No";
                }
                else
                {
                    fundEPTable.Rows[i]["CurrentComp"] = "Yes";
                }

                if (fundEPTable.Rows[i]["CurrentSci"].ToString() == "0")
                {
                    fundEPTable.Rows[i]["CurrentSci"] = "No";
                }
                else
                {
                    fundEPTable.Rows[i]["CurrentSci"] = "Yes";
                }

                if (fundEPTable.Rows[i]["GUID"].ToString() !=null)
                {
                    string guid = fundEPTable.Rows[i]["GUID"].ToString();
                    SPList Admin = web.Lists["ListName"];
                    SPQuery queryAdmin = new SPQuery();
                    queryAdmin.Query = "myquery";
                    SPListItemCollection collAdmin = Admin.GetItems(queryAdmin);
                    if (collAdmin.Count > 0 && collAdmin !=null)
                    {
                        fundEPTable.Rows[i]["Address"] = collAdmin[0]["Address"].ToString();
                    }
                }
    }
  • Yes. What are you trying to achieve? – Amal Hashim Feb 2 '15 at 23:11
  • I have a funds list which displays funds and user name . I have populated a datatable into a gridview and able to display all the data perfectly . Now i need to add 4 columns and need to add user details {ph,address,name and guid } . So while i loop in the data table ,i check the row with user name and then query the Users list . I was thinking if its ok/best practise to write a caml query inside a for loop :( – Nikhil J Feb 2 '15 at 23:15
3

In your case you need to find whether the item exists or not, so there is no problem with invoking the CAML for each check.

Another approach is to form a dynamic query based on the number of rows to validate, for example if you want to validate 3 rows of data then form a query in with condition like

<Or>
<Or>
    <Eq>
        <FieldRef Name="UserName"/><Value Type="Text">UserNameInRow1</Value>
    </Eq>
    <Eq>
        <FieldRef Name="UserName"/><Value Type="Text">UserNameInRow2</Value>
    </Eq>
</Or>
<Eq>
    <FieldRef Name="UserName"/><Value Type="Text">UserNameInRow3</Value>
</Eq>
</Or>

Then check in the return SPListItemCollection and make sure the row exists. This approach requires only one database call as opposed to 'N', where 'N' is the number of rows to validate.

  • i have put the code for better understanding . I write a caml query get details . Further loop in each item and write another query . Does this approach acceptable wrt coding practise ? – Nikhil J Feb 2 '15 at 23:39
  • 1
    The best practice is, using a loop create a dynamic CAML query from the table fundEPTable. Then create an in-memory list which states whether the username exists. Then inside your current loop, you just need to check inmemorylist.Contains(guidPwC) instead of if (collAdmin.Count > 0 && collAdmin !=null) – Amal Hashim Feb 2 '15 at 23:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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