caml query

http://www.ilikesharepoint.de/2012/12/sharepoint-2010-join-2-lists-by-using-caml/

Get items from 3lists with CAML query

SharePoint and List Joins with CAML

This came to me as an issue when I started migrating one project from SharePoint 2010 to SharePoint 2013. The code uses LINQ to SharePoint and it was not written in a clever way. We decide to use CAML queries and optimize the code.

The scenario: Create gridview that gathers information from three(or more) lists.
The problem: How to create the CAML query joins and projected fields correctly.

Explanation:

Option 1: Get generated CAML query from LINQ to SharePoint code
This doesn’t work. I wrote the LINQ to SharePoint code connected the three lists. When I executed the code the following error was thrown – “The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet.” Here is some explanation about it and why this can’t be used.

Option 2: Write the query yourself
I found this article in MSDN. Read it !
The second part in it is exactly my scenario. But it takes me a lot of time to understand how exactly to write this in server-side code. So here is a working code of the described scenario:

And yes, this works but what if we want to get more columns. Here is same scenario but more complicated.

I have added new colums and added new projected fields in the query.
The following code get all the additional data:

Some important points:

1. Only the Order’s list columns can be used with their real internal names.
2. The other columns are all projected and there are limitaton of the column's type. For example the 'Contact Person' column in my scenario can't be a Lookup or Person and Group.
3. The type of the projected field is always ‘Lookup’
4. ShowField is the value of the internal names of the fields
5. The ListAlias and List in the Join statement are not the real names(or Urls) of the list. The next Join statement is equal to the above one:

End.

List of ListTemplated Id

List Template ID

           List of ListTemplated Id are given below.

• 100   Generic list
• 101   Document library
• 102   Survey
• 103   Links list
• 104   Announcements list
• 105   Contacts list
• 106   Events list
• 107   Tasks list
• 108   Discussion board
• 109   Picture library
• 110   Data sources
• 111   Site template gallery
• 112   User Information list
• 113   Web Part gallery
• 114   List template gallery
• 115   XML Form library
• 116   Master pages gallery
• 117   No-Code Workflows
• 118   Custom Workflow Process
• 119   Wiki Page library
• 120   Custom grid for a list
• 130   Data Connection library
• 140   Workflow History
• 150   Gantt Tasks list
• 200   Meeting Series list
• 201   Meeting Agenda list
• 202   Meeting Attendees list
• 204   Meeting Decisions list
• 207   Meeting Objectives list
• 210   Meeting text box
• 211   Meeting Things To Bring list
• 212   Meeting Workspace Pages list
• 301   Blog Posts list
• 302   Blog Comments list
• 303   Blog Categories list
• 1100   Issue tracking
• 1200   Administrator tasks list

Example : The following example is a console application that retrieves data from all lists that are based on the Contacts list template.

using System;
using System.Data;
using Microsoft.SharePoint;

namespace Test
{
   class ConsoleApp
   {
      static void Main(string[] args)
      {
         using (SPSite site = new SPSite("http://localhost"))
         {
            using (SPWeb web = site.OpenWeb())
            {
               SPSiteDataQuery query = new SPSiteDataQuery();

               // Query all Web sites in this site collection.
               query.Webs = "<Webs Scope=\"SiteCollection\">";

               //Ask for all lists created from the contacts template.
               query.Lists = "<Lists ServerTemplate=\"105\" />";

               // Get the Title (Last Name) and FirstName fields.
               query.ViewFields = "<FieldRef Name=\"Title\" />"; 
               query.ViewFields += "<FieldRef Name=\"FirstName\" Nullable=\"TRUE\"/>";

               DataTable results = web.GetSiteData(query);

               foreach (DataRow row in results.Rows)
                  Console.WriteLine("{0} {1}", row["FirstName"], row["Title"]);
            }
         }
         Console.ReadLine();
      }
   }
}

Example2: 

merge two identical SharePoint Lists into one list?

using (SPSite site = new SPSite(url))
{
    using (SPWeb web = site.OpenWeb())
    {
        SPList usersList1 = web.Lists[strListName1];
        SPList usersList2 = web.Lists[strListName2];
        SPList mergeList = web.Lists[strMergeList];

        ..loop through list 1, check if item in list 2, if it isn't add to merge list

        ..loop through list 2, check if in merge list, if it isn't add to merge list

        mergeList.Update();
    }
}

Retrieving list data using CAML to display in gridview

Question: What is retrieving list data using CAML?
 

In simple terms "It enables retrieve list data using SPQuery with CAML based notation".
Step 1: Open SharePoint 2010 Central Administration and navigate to a specific site.

Step 2: Open up Visual Studio 2012 and try to "SharePoint Visual Web Part" project, as in:

Step 3: Select "Deploy as a farm solution" as in the following and click the "Finish" button.

 

Step 4: The complete code of visualwebpart1usercontrol.ascx looks like this:

<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>

<%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register TagPrefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls"

    Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register TagPrefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register TagPrefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>

<%@ Import Namespace="Microsoft.SharePoint" %>

<%@ Register TagPrefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages"

    Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@  Control Language="C#" AutoEventWireup="true" CodeBehind="VisualWebPart1.ascx.cs"

    Inherits="RetrieveListDataUsingCAML.VisualWebPart1.VisualWebPart1" %>

<div>

    <table style="font-family: Verdana; text-align: center;">

        <tr>

            <td colspan="2">

                <asp:label id="Label1" runat="server" text="Select List Data Using CAML - SharePoint 2010 via Visual Studio 2012"

                    font-bold="true" forecolor="Maroon" font-size="Large"></asp:label>

            </td>

        </tr>

        <tr>

            <td colspan="2">

                <asp:button id="Button1" runat="server" text="Retrieve List Data - CAML" forecolor="Orange"

                    font-bold="true" backcolor="Black" onclick="Button1_Click" width="261px" />

            </td>

        </tr>

        <tr>

            <td colspan="2">

                <asp:label id="Label4" runat="server" font-bold="true"></asp:label>

            </td>

        </tr>

        <tr>

            <td align="center">

                <br />

                <br />

                <asp:gridview id="GridView1" runat="server" backcolor="LightGoldenrodYellow" bordercolor="Tan"

                    borderwidth="1px" cellpadding="2" enablemodelvalidation="True" forecolor="Black"

                    gridlines="None" autogeneratecolumns="False"><AlternatingRowStyle BackColor="PaleGoldenrod"/><FooterStyle BackColor="Tan" /><HeaderStyle BackColor="Tan" Font-Bold="True" /><PagerStyleBackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" /><SelectedRowStyleBackColor="DarkSlateBlue" ForeColor="GhostWhite" /><Columns><asp:BoundField DataField="ID"HeaderText="Student Id" ReadOnly="true" SortExpression="ID" /><asp:BoundField DataField="Title"HeaderText="First Name" ReadOnly="true" /><asp:BoundField DataField="LastName" HeaderText="Last Name"ReadOnly="true" /><asp:BoundField DataField="Age" HeaderText="Age" ReadOnly="true" /></Columns></asp:gridview>

            </td>

        </tr>

    </table>

</div>

Step 5: The complete code of visualwebpart1usercontrol.ascx.cs looks like this:

using Microsoft.SharePoint;

using System;

using System.ComponentModel;

using System.Web.UI.WebControls.WebParts;namespace RetrieveListDataUsingCAML.VisualWebPart1

{

    [ToolboxItemAttribute(false)]

    public partial class VisualWebPart1 : WebPart

   

    {// Uncomment the following SecurityPermission attribute only when doing Performance Profiling using

        // the Instrumentation method, and then remove the SecurityPermission attribute when the code is ready

        // for production. Because the SecurityPermission attribute bypasses the security check for callers of

        // your constructor, it's not recommended for production purposes.

        // [System.Security.Permissions.SecurityPermission(System.Security.Permissions.SecurityAction.Assert, UnmanagedCode = true)]

        public VisualWebPart1()

        {

        }

        protected override void OnInit(EventArgs e)

        {

            base.OnInit(e);

            InitializeControl();

        }

        protected void Page_Load(object sender, EventArgs e)

        {

        }

        protected void Button1_Click(object sender, EventArgs e)

        {

            SPWeb web = SPContext.Current.Web;

            SPList list = web.Lists["Student"];

            SPQuery query = new SPQuery();

            query.ViewFields = "<FieldRef Name='ID'/>" +"<FieldRef Name='Title'/>" + "<FieldRef Name='LastName'/>" +"<FieldRef Name='Age'/>";

            SPListItemCollection item = list.GetItems(query);

            GridView1.DataSource = item.GetDataTable();

            GridView1.DataBind();

        }

    }

}

Step 6: Deploy the solution file and add the new webpart to a SharePoint site.

Step 7: The output of the application looks like this:

 

Step 8: The list data output of the application looks like this:

 

programmatically upload document in library Visual Web Part

Creating and Deploying a Visual Web Part using Microsoft Visual Studio 2010

Export SPGridView to Excel spreadsheet in Sharepoint 2007

Export SPGridView to Excel spreadsheet in Sharepoint 2007

Export XML to Excel in SharePoint 2010

http://www.c-sharpcorner.com/uploadfile/anavijai/export-xml-to-excel-in-sharepoint-2010/

gridview data and splist items filtering export to excel

Creation of SPGridView Webpart with Sorting, Filtering and Pagination OOTB

2         venu

Hi,

This post will simply explain how to use SPGridView in SharePoint 2010 VisualWebpart with sorting, filtering and paging functionality without writing much code, mostly using OOTB features,

This includes below feature

• SPGridView Webpart
• Pagination
• Sorting
• Filtering
• Export to Ecel

How it looks:

1. Screen when page load:

2. Screen when filter:

3. Item per page:

See the below code how to implement the above screen with ootb

1. CSS in user control

<style type=”text/css”>
.pagination
{
font-size: 80%;
}

.pagination a
{
text-decoration: none;
color: #15B;
}

.pagination a, .pagination span
{
font-family: Sans-Serif;
font-size: 11px;
border-bottom: #ccc 1px solid;
border-left: #ccc 1px solid;
padding-bottom: 3px;
margin: 1px;
padding-left: 4px;
padding-right: 4px;
display: inline-block;
border-top: #ccc 1px solid;
font-weight: bold;
border-right: #ccc 1px solid;
padding-top: 3px;
-moz-border-radius: 3px;
-webkit-border-radius: 3px;
}
.pagination .current
{
background: #26B;
color: #fff;
border: solid 1px #AAE;
}
.lblWTHeading
{
font-family: Arial;
font-size: 18px;
font-weight: bold;
color: White;
}
.textAlignLeft
{
text-align: left;
}
</style>

2. SPGridView  and ObjectDataSource in user control

<table>
<tr>
<td align=”right” style=”border: 1px ridge #C0C0C0″>
<table cellpadding=”3″ cellspacing=”3″>
<tr>
<td>
<asp:Label ID=”Label2″ runat=”server” Text=”Export To:” Font-Size=”13px” Font-Names=”Arial”></asp:Label>
</td>
<td>
<asp:ImageButton ID=”imgExcel” runat=”server” ImageUrl=”~/_layouts/images/ExportToExcel.png”
OnClick=”imgExcel_Click” ToolTip=”Export to Excel” Height=”22px” Width=”22px” />
</td>
<td>
<asp:Label ID=”Label1″ runat=”server” Text=”Item per page:” Font-Size=”13px” Font-Names=”Arial”></asp:Label>
</td>
<td>
<asp:DropDownList ID=”DropDownList1″ runat=”server” AutoPostBack=”True” OnSelectedIndexChanged=”DropDownList1_SelectedIndexChanged”>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>20</asp:ListItem>
<asp:ListItem>50</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style=”border: 1px ridge #C0C0C0″>
<SharePoint:SPGridView ID=”oGrid” runat=”server” AutoGenerateColumns=”false” AllowSorting=”True”
DataSourceID=”myDataSource” FilterDataFields=”Title,Status” AllowFiltering=”true”
AllowPaging=”True” FilteredDataSourcePropertyName=”FilterExpression” FilteredDataSourcePropertyFormat=”{1} like ‘{0}’”
Font-Names=”Arial” Font-Size=”13px”>
<Columns>
<SharePoint:SPBoundField DataField=”Title” HeaderText=”Title” SortExpression=”Title”>
<ControlStyle Width=”220px” />
</SharePoint:SPBoundField>
<SharePoint:SPBoundField DataField=”Status” HeaderText=”Status” SortExpression=”Status”>
<ControlStyle Width=”100px” />
</SharePoint:SPBoundField>
<asp:TemplateField HeaderText=”Get Candidates”>
<ItemTemplate>
<asp:LinkButton ID=”lnkBtnGetCandidates” runat=”server” Text=”Get Candidates” CausesValidation=”False”
CommandName=”Select” CommandArgument=’<%#Eval(“Description”)%>’></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
<PagerStyle CssClass=”pagination” HorizontalAlign=”Center” VerticalAlign=”Middle”
Font-Size=”14pt” Wrap=”True” BackColor=”White” />
<RowStyle HorizontalAlign=”Left” />
</SharePoint:SPGridView>
<asp:ObjectDataSource ID=”myDataSource” runat=”server” TypeName=”SPGridview.VisualWebPart1.DataTableWrapper, $SharePoint.Project.AssemblyFullName$”
SelectMethod=”GetTable”></asp:ObjectDataSource>
</td>
</tr>
</table>

3. Code in Visual web part

private DataTable sourceDataTable;
private DataTableWrapper myDataTable;
private string[] _ssep = { “AND” };
private char[] _sep = { ‘,’ };

protected override void CreateChildControls()
{
string js = @”_spSuppressFormOnSubmitWrapper = true;”;
this.Page.ClientScript.RegisterStartupScript(this.GetType(), “js”, js, true);

myDataSource.ObjectCreating += new ObjectDataSourceObjectEventHandler(ds_ObjectCreating);
#region “Pagination”

oGrid.PageSize = Convert.ToInt16(DropDownList1.SelectedValue);
oGrid.RowDataBound += new GridViewRowEventHandler(oGrid_RowDataBound);

//Default Pagination
oGrid.PageIndexChanging += new GridViewPageEventHandler(oGrid_PageIndexChanging);
oGrid.PagerTemplate = null;
#endregion
base.CreateChildControls();

}

void oGrid_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
oGrid.PageIndex = e.NewPageIndex;
oGrid.DataBind();
}

private void oGrid_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (sender == null || e.Row.RowType != DataControlRowType.Header)
{ return; }

SPGridView grid = sender as SPGridView;

// Show icon on filtered and sorted columns
for (int i = 0; i < grid.Columns.Count; i++)
{
DataControlField field = grid.Columns[i];

if (((Microsoft.SharePoint.WebControls.SPGridView)(sender)).FilterFieldName.Contains(field.SortExpression))
{
try
{
PlaceHolder panel = HeaderImages(field, “/_layouts/images/filter.gif”);
e.Row.Cells[i].Controls[0].Controls.Add(panel);
break;
}
catch (Exception)
{ }
}

}
}

void ds_ObjectCreating(object sender, ObjectDataSourceEventArgs e)
{

myDataTable = new DataTableWrapper(sourceDataTable);
e.ObjectInstance = myDataTable;

}

private PlaceHolder HeaderImages(DataControlField field, string imageUrl)
{
Image filterIcon = new Image();
filterIcon.ImageUrl = imageUrl;
filterIcon.Style[HtmlTextWriterStyle.MarginLeft] = “2px”;

Literal headerText = new Literal();
headerText.Text = field.HeaderText;

PlaceHolder panel = new PlaceHolder();
panel.Controls.Add(headerText);

//add the sort icon if needed
if (FilterExpression.Contains(field.SortExpression) &&
SortExpression.Contains(field.SortExpression))
{
string url = sortImage(field);
Image sortIcon = new Image();
sortIcon.ImageUrl = url;
sortIcon.Style[HtmlTextWriterStyle.MarginLeft] = “1px”;
panel.Controls.Add(sortIcon);
//change the left margin to 1
filterIcon.Style[HtmlTextWriterStyle.MarginLeft] = “1px”;
}

panel.Controls.Add(filterIcon);
return panel;
}

private string sortImage(DataControlField field)
{
string url = string.Empty;
string[] fullSortExp = SortExpression.Split(_sep);
List<string> fullSortExpression = new List<string>();
fullSortExpression.AddRange(fullSortExp);

//does the sort expression already exist?
int index = fullSortExpression.FindIndex(s => s.Contains(field.SortExpression));
if (index >= 0)
{
string s = fullSortExpression[index];
if (s.Contains(“ASC”))
{ url = “_layouts/images/sortup.gif”; }
else
{ url = “_layouts/images/sortdown.gif”; }
}
return url;
}

protected override void LoadViewState(object savedState)
{
base.LoadViewState(savedState);

if (Context.Request.Form["__EVENTARGUMENT"] != null &&
Context.Request.Form["__EVENTARGUMENT"].Contains(“__ClearFilter__”))
{
// Clear FilterExpression
ViewState.Remove(“FilterExpression”);
}
}

string FilterExpression
{
get
{
if (ViewState["FilterExpression"] == null)
{ ViewState["FilterExpression"] = “”; }

return (string)ViewState["FilterExpression"];
}
set
{
string thisFilterExpression = “(” + value.ToString() + “)”;
List<string> fullFilterExpression = new List<string>();

if (ViewState["FilterExpression"] != null)
{
string[] fullFilterExp = ViewState["FilterExpression"].ToString().Split(_ssep, StringSplitOptions.RemoveEmptyEntries);
fullFilterExpression.AddRange(fullFilterExp);

//if the filter is gone expression already exist?
int index = fullFilterExpression.FindIndex(s => s.Contains(thisFilterExpression));
if (index == -1)
{ fullFilterExpression.Add(thisFilterExpression); }
}
else
{
fullFilterExpression.Add(thisFilterExpression);
}
//loop through the list<T> and serialize to string
string filterExp = string.Empty;
fullFilterExpression.ForEach(s => filterExp += s + ” AND “);
filterExp = filterExp.Remove(filterExp.LastIndexOf(” AND “));
if (!filterExp.EndsWith(“))”) && filterExp.Contains(“AND”))
{ filterExp = “(” + filterExp + “)”; }
ViewState["FilterExpression"] = filterExp;
}
}

string SortExpression
{
get
{
if (ViewState["SortExpression"] == null)
{ ViewState["SortExpression"] = “”; }

return (string)ViewState["SortExpression"];
}
set
{
string[] thisSE = value.ToString().Split(‘ ‘);
string thisSortExpression = thisSE[0];
List<string> fullSortExpression = new List<string>();

if (ViewState["SortExpression"] != null)
{
string[] fullSortExp = ViewState["SortExpression"].ToString().Split(_sep);
fullSortExpression.AddRange(fullSortExp);

//does the sort expression already exist?
int index = fullSortExpression.FindIndex(s => s.Contains(thisSortExpression));
if (index >= 0)
{
string s = string.Empty;
if (value.ToString().Contains(“DESC”))
{ s = value.ToString(); }
else
{
s = fullSortExpression[index];
if (s.Contains(“ASC”))
{ s = s.Replace(“ASC”, “DESC”); }
else
{ s = s.Replace(“DESC”, “ASC”); }
}
//reset the sort direction
fullSortExpression[index] = s;
}
else
{
if (value.ToString().Contains(“DESC”))
{ fullSortExpression.Add(value.ToString()); }
else
{ fullSortExpression.Add(thisSortExpression + ” ASC”); }
}
}
else
{
if (value.ToString().Contains(“DESC”))
{ fullSortExpression.Add(value.ToString()); }
else
{ fullSortExpression.Add(thisSortExpression + ” ASC”); }
}
//loop through the list<T> and serialize to string
string sortExp = string.Empty;
fullSortExpression.ForEach(s => sortExp += s);
sortExp = sortExp.Replace(” ASC”, ” ASC,”);
sortExp = sortExp.Replace(” DESC”, ” DESC,”);
ViewState["SortExpression"] = sortExp.Remove(sortExp.LastIndexOf(‘,’));
}
}

private void ExportToExcel(DataTable dt)
{
Table table = ConvertDatatabletoTable(dt);
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);

table.RenderControl(htw);

HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(“content-disposition”, string.Format(“attachment; filename={0}”, “CountryState.xls”));
HttpContext.Current.Response.ContentType = “application/ms-excel”;
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}

protected Table ConvertDatatabletoTable(DataTable dtSource)
{
Table tbl = new Table();
tbl.CellPadding = 0;
tbl.CellSpacing = 0;
bool AddedColumnName = false;

foreach (DataRow dtRow in dtSource.Rows)
{
TableRow row = new TableRow();
foreach (DataColumn col in dtSource.Columns)
{
if (AddedColumnName == false)
{
TableCell cell = new TableCell();
cell.Text = col.ColumnName;
row.Cells.Add(cell);
}
else
{
TableCell cell = new TableCell();
cell.Text = dtRow[col].ToString();
row.Cells.Add(cell);
}
}
tbl.Rows.Add(row);
AddedColumnName = true;
}
return tbl;
}

protected void imgExcel_Click(object sender, ImageClickEventArgs e)
{

DataTable dt = (DataTable)HttpContext.Current.Session["Hello"];
ExportToExcel(dt);

}

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
oGrid.PageSize = Convert.ToInt16(DropDownList1.SelectedValue);
}

public class DataTableWrapper
{
private DataTable _dt = new DataTable();

public DataTableWrapper(DataTable dt)
{
_dt = dt;
}

public DataTable GetTable()
{
return _dt;
}
}