SPQuery strQuery = new SPQuery();
strQuery.Query = string.Format(@"<Where>
<And>
<Eq>
<FieldRef Name='Code' LookupId='True'/>
<Value Type='Lookup'>{0}</Value>
</Eq>
<Eq>
<FieldRef Name='Status' />
<Value Type='Text'>POL</Value>
</Eq>
</And>
</Where>", ddlClient.SelectedValue.Split(new[] { ";#" }, StringSplitOptions.None)[0]);
strQuery.ViewFields = string.Concat(@"<FieldRef Name='ID' />");
strQuery.ViewFieldsOnly = true; //Using this take View Fields only
strQuery.QueryThrottleMode = SPQueryThrottleOption.Override;//using this fetching above 8000 records
//Adding filter column using Array
ArrayList strarrayIds= new ArrayList();
foreach (SPListItem li in myList1items)
{
if (!strarrayIds.Contains(li["GID"]))
{
strarrayIds.Add(GetIDValueFromLookup(li["GID"]));
}
}
string strViewFields = string.Concat(@"<FieldRef Name='ID' />");
//Fetching data into Listitem
List<SPListItem> myList2items = getGenericListItemsWithAnd(strarrayIds, "ChildListName", "ID",strViewFields, true);
//Fetching data from list using IN Operator into Sharepoint List
public List<SPListItem> getGenericListItemsWithAnd(ArrayList arrayIds, string listname, string FieldName,string strViewFields, bool IsLookup)
{
List<SPListItem> lsGenericItems = new List<SPListItem>();
using (SPSite site = new SPSite(ConfigurationManager.AppSettings["InternalSiteURL"]))
{
using (SPWeb web = site.OpenWeb())
{
SPListItemCollection objListCol = null;
SPList lstToQuery = web.Lists.TryGetList(listname);
SPQuery objQuery = new SPQuery();
int intInOperCount = InOperatorCount();
int intCount = arrayIds.Count / intInOperCount;
int intBal = arrayIds.Count % intInOperCount;
if (intBal > 0)
intCount = intCount + 1;
for (int i = 0; i < intCount; i++)
{
ArrayList listTemp = new ArrayList();
int x = 0;
if (i == 0)
x = intInOperCount * i;
else
x = (intInOperCount * i);
if (intBal > 0 && i == intCount - 1)
{
listTemp = arrayIds.GetRange(x, intBal);
}
else
{
listTemp = arrayIds.GetRange(x, intInOperCount);
}
#region Code
StringBuilder camlQuery = new StringBuilder();
camlQuery.Append("<Where><And><In>");
camlQuery.Append("<FieldRef Name='" + FieldName + "'/><Values>");
camlQuery.Append(getInOperatorTags("Counter", IsLookup, listTemp));
camlQuery.Append("</Values></In>");
if (this.BuildCamlQuery != null && this.BuildCamlQuery != string.Empty)
{
camlQuery.Append(this.BuildCamlQuery);
}
camlQuery.Append("</And></Where>");
objQuery = new SPQuery();
objQuery.Query = Convert.ToString(camlQuery.ToString().Trim());
objQuery.ViewFields = strViewFields;
objQuery.ViewFieldsOnly = true;
objQuery.RowLimit = 500;
do
{
objListCol = lstToQuery.GetItems(objQuery);
foreach (SPListItem li in objListCol)
{
lsGenericItems.Add(li);
}
objQuery.ListItemCollectionPosition = objListCol.ListItemCollectionPosition;
} while (objQuery.ListItemCollectionPosition != null);
#endregion
}
}
}
return lsGenericItems;
}
//Generate Query For IN operator using CAML
public string getInOperatorTags(string strType, bool isId, ArrayList objArrayList)
{
string strReturn = string.Empty;
string strId = "";
if (isId)
strId = "LookupId='true'";
for (int i = 0; i < objArrayList.Count; i++)
{
if (strReturn == "")
{
strReturn = "<Value Type='" + strType + "' " + strId + ">" + Convert.ToString(objArrayList[i]) + "</Value>";
}
else
{
strReturn = strReturn = strReturn + "<Value Type='" + strType + "' " + strId + ">" + Convert.ToString(objArrayList[i]) + "</Value>";
}
}
return strReturn;
}
//Count for Fetching data for Single time
public int InOperatorCount()
{
return 500;
}
//fetch ID value from lookup
public string GetIDValueFromLookup(object lookupValue)
{
if (lookupValue == null) return string.Empty;
if (isLookup(lookupValue))
{
var value = Convert.ToString(lookupValue).Split(new[] { ";#" }, StringSplitOptions.None)[0];
return value;
}
else
{
return Convert.ToString(lookupValue);
}
}
//Check Condition is it lookup
public bool isLookup(object lookupval)
{
bool retflag = false;
int indexcolon = Convert.ToString(lookupval).IndexOf(';');
int indexhash = Convert.ToString(lookupval).IndexOf('#');
int diff = indexhash - indexcolon;
if (indexcolon > 0 && indexhash > 0 && diff == 1)
retflag = true;
return retflag;
}
No comments:
Post a Comment