Inserting Sharepoint List Item using SPMetal Classes

Microsoft has introduced a very useful tool, in the form of SPMetal, for performing CRUD (Create Read Update Delete) operations on sharepoint lists. This article shows how to insert a new item into a list. The below screen shot has an example with its explanation as given below.

Insert item using SPMetal

Sample source code

If you have not yet generated the Data Context and entity classes, follow the below link and generate your entity model –

Using SPMetal With Paramenters

  1. Instantiate an object of the data Context class by passing the site Url as a parameter.
    • DemoDataContext demoDataContext = new DemoDataContext(spWeb.Url);
  2. Create a generic object of EntityList having the List Entity as its source.
    • EntityList<CitiesObject> cities = demoDataContext.GetList<CitiesObject>(“Cities”);
    • The EntityList class can be found in  Microsoft.Sharepoint.Linq namespace.
    • In this example I have taken a list named Cities which has three custom columns namely CityName, State and Country apart from the out-of-the-box columns.
    • The entity corresponding to Cities is CitiesObject, which is passed to the generic method.
    • The GetList method of the DataContext object returns a reference to the List to which we are adding an Item.
  3. Create and instantiate an object of List type which is passed as source to the EntityList class above.
    • CitiesObject city = new CitiesObject() { Title=”New City”, CityName=”Pune”,Country=”India”,State=”Maharashtra”};
    • In my case I have created an object of type CitiesObject and have assigned value to its properties.
  4. Add the list item to list.
    • cities.InsertOnSubmit(city);
    • The above method InsertOnSubmit adds the list item to list. But these changes remain in memory.
    • If we have a collection of List Items, we can add the whole collection at on go, using a Method called InsertAllOnSubmit(IEnumerable<T>)
  5. Persist the changes
    • demoDataContext.SubmitChanges();
    • The SubmitChanges method is where the changes made above are actually persisted in the content Database.

Joins in CAML Query

Finally in SharePoint 2010, you can have Joins on List using CAML.

Here is an example:

For Test purpose I have created four lists having relation between them. i.e. Cities, Customers, Product and Orders.
The Customers list contains a lookup field pointing to the Cities list to specify in which city a customer lives. The Orders list contains a lookup field to the Customers and Product list to specify who’s the customer of a given order and what product he has ordered. Please make sure you have these relations in place as lookup fields are the basis on which we apply joins in CAML.

1. Declaring List Object: We have created an object of the Customers list from which records are to be fetched.

SPList CustomerList = SPContext.Current.Web.Lists[“Customers“];

2. Setting Join Property: One thing you woul notice below is that SPQuery object has been enhanced with a property named Joins.  This is the property which you need to set.
SPQuery CustomerCityQuery = new SPQuery();
CustomerCityQuery.Joins =

“<Join Type=’INNER’ ListAlias=’Cities‘>” +
“<Eq>” +
“<FieldRef Name=’City‘ RefType=’Id‘ />” +
“<FieldRef List=’Cities‘ Name=’ID‘ />” +
“</Eq>” +

The “Join” tag has an attribute named “Type”. It specifies the type of join we intend to have. In my case I have set it to “INNER”, the “ListAlias” property points to the list with which we are having this join i.e. Cities.

In my case, I have a lookup column in my Customers list named City which points to the Id in the list Cities. Hence the first “FieldRef” tag has its “Name” set to City  and “RefType” is set to Id.

The second “FieldRef” tag has attribute “List” set to Cities and “RefType” is set to ID signifying the column of the list on which Join has to be implemented.

3. Setting Projected fields: These are fields in joined lists that can be included in the view when one or more other lists have been joined in a query. In my case I have included CityName, State and Country columns from Cities list while creating lookup from Customers. In the below code I am adding CityName & State to the projected fields property of SPQuery.

StringBuilder ProjectedFields = new StringBuilder();

ProjectedFields.Append(“<Field Name=’CityName‘ Type=’Lookup’ List=’Cities’ ShowField=’CityName‘ />”);
ProjectedFields.Append(“<Field Name=’State’ Type=’Lookup’ List=’Cities‘ ShowField=’State‘ />“);
CustomerCityQuery.ProjectedFields = ProjectedFields.ToString();

4. The code ahead is similar to what we have in normal SPQuery operations without joins.
SPListItemCollection Results = CustomerList.GetItems(CustomerCityQuery);
foreach (SPListItem Result in Results)
SPFieldLookupValue CityTitle = new SPFieldLookupValue(Result[“CityName“].ToString());
SPFieldLookupValue CityCountry = new SPFieldLookupValue(Result[“State“].ToString());
Response.Write(string.Format(“Customer {0} lives in {1} – {2}”,

This was my first attempt to have joins using CAML for Sharepoint 2010. Will refine this post as and when possible.

MS CRM 2011

The unveiling of Microsoft CRM 2011 was launched at the Microsoft Conference in America last month.  Formerly code named MS CRM 5, Microsoft CRM 2011 will be available as a beta testing release in September 2010.MS CRM 2011

There are a series of excellent enhancements available in Microsoft CRM 2011 including a great focus of integration with Microsoft Outlook.  The new CRM 2011 release will treat CRM data in the same manner it treats Outlook data – Outlook and CRM 2011 will be a much more deeper experience, with functionality including previews and conditional formatting.  This will result in less clicks, user customizations, grouping of records within CRM 2011 and user ability.  MS CRM 2011 certainly will help users to become more focused on CRM and its high achieving results.

There is a new “ribbon” (tool bars and buttons) for Outlook and web browser user that offers great Microsoft Office navigation.

Real time dashboards that can be role tailored, fully contextual and individually tailored – which will offer great management intelligence for sales pipelines, customer service and service and case management. A great success.

Role tailored client has been incorporated to CRM 2011.  Basically it tailors the client (so the users interface and experiences) to the job they do – so that the users get fast access to the information that they need and then are also prevented from seeing data they do not necessarily need to see.

Now in CRM 2011 it will be possible to have developed code as a hosted option – or in the Cloud as many refer to this.

Impressed so far with Microsoft CRM 2011 – well thats not all.

Other successes to this new release include

  • Advanced user personalization creating views and commonly used records by user
  • Inline data visualizations which facilitates the ability to create and share inline charts
  • Flexible goal management where businesses can define key performance and business health indicators providing a great track and measurement tool against key strategies
  • Integration out of the box with SharePoint so they can work together far more easily

These are the initial updates from Microsoft and I m sure that there will be more features to follow  – as soon as we know we will let you know.

*note:- The content of this blog is based on the articles which I read on the net. The images too are copied from the internet.