Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I am trying to learn CAML Queries. I must render a lot of data on Calendar. I made a code, but if it has got more then 1000 elements it's doing very long about 4-5 seconds. It's longer then adding to list 1000 elements. I made a CAML Query on U2U CAML Query Builder. This is my CAML Query:

<Query> <Where> <And> <Lt> <FieldRef Name='Dzien' /> <Value Type='DateTime'>[Today+30Day(s)]</Value> </Lt> <Gt> <FieldRef Name='Dzien' /> <Value Type='DateTime'>[Today-30Day(s)]</Value> </Gt> </And> </Where> </Query>

It's good enough to very faster my code ? I'm very new on CAML. Can I have indexes on CAML like in Data Base ?

Here is my render code before CAML:

`protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)
    {
        e.Cell.VerticalAlign = VerticalAlign.Top;
        SPWeb witryna = SPContext.Current.Web;
        SPListItemCollection listaDniPrzeznaczonychNaProjekt = witryna.Lists["Szczegoly"].Items;

            // Gdy nie ma zaznaczonej osoby to wtedy wyświetlamy wszystkie daty urlopów

            int index;

        if (DropDownList1.SelectedItem.Text == "<-- Wszystcy pracownicy -->" || DropDownList1.SelectedItem.Text == "")
        {

            foreach (SPListItem olistaDniPrzeznaczonychNaProjekt in listaDniPrzeznaczonychNaProjekt)
            {

                if (e.Day.Date.Date.ToShortDateString() == Convert.ToDateTime(olistaDniPrzeznaczonychNaProjekt["Dzien"]).ToShortDateString())
                {
                    e.Cell.Style.Add("background-color", "#FF0000");
                    e.Cell.CssClass = "dni_swiateczne";
                }

            }
        }
        else

        // Renderowanie urlopu dla pojedyńczej zaznaczonej osobie
        {
            foreach (SPListItem olistaDniPrzeznaczonychNaProjekt in listaDniPrzeznaczonychNaProjekt)
            {
                index = olistaDniPrzeznaczonychNaProjekt["Pracownik"].ToString().IndexOf("#");

                if (DropDownList1.SelectedItem.Text == olistaDniPrzeznaczonychNaProjekt["Pracownik"].ToString().Substring(index + 1))
                {
                    if (e.Day.Date.Date.ToShortDateString() == Convert.ToDateTime(olistaDniPrzeznaczonychNaProjekt["Dzien"]).ToShortDateString())
                    {
                        e.Cell.Style.Add("background-color", "#F5BCA9");
                        e.Cell.CssClass = "dni_swiateczne";
                        e.Cell.Controls.Add(new LiteralControl(("<p style='color:blue; font-size:8px;'>" + olistaDniPrzeznaczonychNaProjekt["Kontrakt"].ToString() + "</p><p style='color:#5D198E;'>" + olistaDniPrzeznaczonychNaProjekt["Procenty"].ToString().Substring(3) + "</p>")));
                    }

                }

            }

        }
        SPWeb witryna1 = SPContext.Current.Web;
        SPListItemCollection listaDniSwiatecznych = witryna1.Lists["DR2013 Święta"].Items;

        foreach (SPListItem olistaDniSwiatecznych in listaDniSwiatecznych)
        {
            // Wyświetlenie na kalendarzu weekendów
            if (e.Day.Date.Date.DayOfWeek.ToString() == "Saturday" || e.Day.Date.Date.DayOfWeek.ToString() == "Sunday")
            {
                e.Cell.Style.Add("background-color", "#FF9900");
                e.Cell.CssClass = "dni_swiateczne";
            }

            // Wyświetlenie na kalendarzu dni świątecznych
            if (e.Day.Date.Date.ToShortDateString() == Convert.ToDateTime(olistaDniSwiatecznych["Data"]).ToShortDateString())
            {
                e.Cell.Style.Add("background-color", "#FFFF00");
                e.Cell.CssClass = "dni_swiateczne";
                e.Cell.Controls.Add(new LiteralControl(("<p style='color:red; font-size:8px;'>" + olistaDniSwiatecznych["Data"].ToString().Substring(0, 10) + "</p>")));
            }

        }
} // Koniec protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)

` It's render after change month of Calendar about 4-5 secounds

share|improve this question
    
Grzegorz, firstly its CAML and not CALM :).. Also, I didn't see any SPQuery object in your code.. where do you wish to run this query?? –  Arsalan Adam Khatri Oct 20 '12 at 6:36
    
It is Upper.Before code: <Query> <Where> <And> <Lt> <FieldRef Name='Dzien' /> <Value Type='DateTime'>[Today+30Day(s)]</Value> </Lt> <Gt> <FieldRef Name='Dzien' /> <Value Type='DateTime'>[Today-30Day(s)]</Value> </Gt> </And> </Where> </Query> –  Grzegorz Z Oct 20 '12 at 7:35
    
I don't add SPQuery, because I don't know where schould I add it. –  Grzegorz Z Oct 20 '12 at 7:53
    
Where are you binding Calendar1? DataSource for Calendar1? –  Arsalan Adam Khatri Oct 20 '12 at 9:33
    
Only on Render procedure. –  Grzegorz Z Oct 20 '12 at 17:43

1 Answer 1

up vote 1 down vote accepted

When no project is selected your CAML query should only select by date something like:

<Where>
  <Eq>
    <FieldRef Name='Dzien' />
    <Value Type='DateTime'>2012-10-22T00:00:00Z</Value>
  </Eq>
</Where>

When a project is selected it should do the query on date and project like:

<Where>
  <And>
    <Eq>
      <FieldRef Name='Dzien' />
      <Value Type='DateTime'>2012-10-22T00:00:00Z</Value>
    </Eq>
    <Eq>
      <FieldRef Name='Pracownik' />
      <Value Type='Lookup'>ProjectName</Value>
    </Eq>
  </And>
</Where>

I'm not entirely sure how you want your background color to work. But this should give you the data as fast as possible:

    protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)
    {
        e.Cell.VerticalAlign = VerticalAlign.Top;
        if (e.Day.Date.Date.DayOfWeek == DayOfWeek.Saturday || e.Day.Date.Date.DayOfWeek == DayOfWeek.Sunday)
        {
            e.Cell.Style.Add("background-color", "#FF9900");
            e.Cell.CssClass = "dni_swiateczne";
        }

        string DateOnlyQuery = "<Where><Eq><FieldRef Name='{0}' /><Value Type='DateTime'>{1}</Value></Eq></Where>";
        string FullQuery = "<Where><And><Eq><FieldRef Name='{0}' /><Value Type='DateTime'>{1}</Value></Eq><Eq><FieldRef Name='Pracownik' /><Value Type='Lookup'>{2}</Value></Eq></And></Where>";
        string dateISO = SPUtility.CreateISO8601DateTimeFromSystemDateTime(e.Day.Date.ToUniversalTime());

        SPWeb witryna = SPContext.Current.Web;
        var query1 = new SPQuery();
        if (DropDownList1.SelectedItem.Text == "<-- Wszystcy pracownicy -->" || DropDownList1.SelectedItem.Text == "")
        {
            query1.Query = String.Format(DateOnlyQuery, "Dzien", dateISO);
        }
        else
        {
            query1.Query = String.Format(FullQuery, "Dzien", dateISO, DropDownList1.SelectedItem.Text);
        }
        query1.ViewFields = "<FieldRef Name='Kontrakt' /><FieldRef Name='Procenty' />";
        query1.ViewFieldsOnly = true;

        SPListItemCollection listaDniPrzeznaczonychNaProjekt = witryna.Lists["Szczegoly"].GetItems(query1);


        // Gdy nie ma zaznaczonej osoby to wtedy wyświetlamy wszystkie daty urlopów 
        foreach (SPListItem olistaDniPrzeznaczonychNaProjekt in listaDniPrzeznaczonychNaProjekt)
        {
            e.Cell.Style.Add("background-color", "#F5BCA9");
            e.Cell.CssClass = "dni_swiateczne";
            e.Cell.Controls.Add(new LiteralControl(("<p style='color:blue; font-size:8px;'>" + olistaDniPrzeznaczonychNaProjekt["Kontrakt"].ToString() + "</p><p style='color:#5D198E;'>" + olistaDniPrzeznaczonychNaProjekt["Procenty"].ToString().Substring(3) + "</p>")));
        }

        SPWeb witryna1 = SPContext.Current.Web;
        var query2 = new SPQuery();
        query2.Query = String.Format(DateOnlyQuery, "Data", dateISO, DropDownList1.SelectedItem.Text);
        query2.ViewFields = "<FieldRef Name='Data' />";
        query2.ViewFieldsOnly = true;
        SPListItemCollection listaDniSwiatecznych = witryna1.Lists["DR2013 Święta"].GetItems(query2);

        foreach (SPListItem olistaDniSwiatecznych in listaDniSwiatecznych)
        {
            e.Cell.Style.Add("background-color", "#FFFF00");
            e.Cell.CssClass = "dni_swiateczne";
            e.Cell.Controls.Add(new LiteralControl(("<p style='color:red; font-size:8px;'>" + olistaDniSwiatecznych["Data"].ToString().Substring(0, 10) + "</p>")));
        }

    }
share|improve this answer
    
Its works great now :) Thanks !! :) –  Grzegorz Z Oct 21 '12 at 15:39
    
How can I learn CAML ? –  Grzegorz Z Oct 21 '12 at 15:43
    
Its works very fast, but this isn't show me when I select <==Wszyscy pracownicy ==> thats caled all workers. –  Grzegorz Z Oct 21 '12 at 15:57
    
Make sure the text in the dropdown and the if statement are exactky the same (including spaces and - vs =) –  Per Jakobsen Oct 21 '12 at 16:17
    
I look on it, but in Your code isn't his color of days for all workers - Pracownicy Red color: Wchich should Color all workers days first off for Red :) ` foreach (SPListItem olistaDniPrzeznaczonychNaProjekt in listaDniPrzeznaczonychNaProjekt) { if (e.Day.Date.Date.ToShortDateString() == Convert.ToDateTime(olistaDniPrzeznaczonychNaProjekt["Dzien"]).ToShortDateString(‌​)) { e.Cell.Style.Add("background-color", "#FF0000"); e.Cell.CssClass = "dni_swiateczne"; }` –  Grzegorz Z Oct 21 '12 at 17:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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