DotNetCurry Logo

LINQ To SharePoint: Performing CRUD operation on Cascade Lists

Posted by: Pravinkumar Dabade , on 4/8/2011, in Category SharePoint
Views: 59404
Abstract: In this article, we will see how to perform LINQ queries on SharePoint cascade lists. We will also see how to perform CRUD (Select, Insert, Update and Delete) operations using LINQ to SQL

In this article, we will see how to perform LINQ queries on SharePoint cascade lists. We will also see how to perform CRUD (Select, Insert, Update and Delete) operations using LINQ to SQL.

The steps to create a SharePoint Site using a Template ‘Team Site’ remains the same, as shown in my previous article, SharePoint Dashboard with Common Filters using PerformancePoint Services 2010.

After you have created a SharePoint site with ‘Team Site’ template, let’s add some lists as described in the tables you will see shortly. To create a list, click on ‘Lists’ link from the left hand navigation pane and click on ‘Create’ button. A ‘Create’ dialog box will appear, as shown below:

sharepoint lists

Once we create the first list, we need to add a couple of columns as described in the tables shown below (scroll down). To create a column, click on list settings from the top ‘Ribbon’

sharepoint lists settings

Now click on ‘Create Column’ link from the list settings page as shown below –

sharepoint create column

Now create the lists as described below. Also add some sample data in the lists –

Customers List –

sharepoint customer list

Products List –

sharepoint product list

Orders List –

sharepoint order list

Note: When you create a lookup column, you can implement a cascade effect in SharePoint 2010. So for ‘ProductID’ column do not enforce the ‘Delete cascade’ rule whereas apply delete cascade rule for ‘CustomerID’ lookup column. A sample is shown below –

sharepoint look up

Now as we have created all the lists with the sample data, let’s focus on our main requirement, i.e. to query the list data using LINQ.

SharePoint provides a tool to convert all the ‘Lists’ into ‘Entities’ which we can add to our Visual Studio project and then can query against the SharePoint Lists. So to create the entity from a SharePoint list, we will use a tool called ‘SPMetal.exe’. We can find this tool at the following path –

spmetal sharepoint

To generate the ‘Entities’ from our ‘Lists’, open ‘Command Prompt’ and change the path as shown above. Now write the following command to generate the entities –

spmetal sharepoint

Now go to the path shown above and find the file ‘SPPurchaseOrder.cs’. We will use this file to query our ‘SharePoint Lists’.

Now let’s create a ‘Window Project’ with the name ‘LINQToSharePoint’ using ‘Microsoft Visual Studio 2010’ as shown below –

Windows project

Now add the file ‘SPPurchaseOrder.cs’ file to our project. Let’s design a Windows Form for our operations with the following controls, as shown below –

sharepoint windows form

sharepoint windows form

Most important setting – I have seen many developers who develop using Visual studio 2010, forget an important step.

Right click the ‘Windows Project’ in Solution explorer and go to properties. From the properties window, choose ‘Build’ option from the right hand side and set the ‘Platform target’ to ‘x64’ as shown below –

platform x64

Now add a reference to the ‘Microsoft.SharePoint.LINQ.dll’ file to our project. Let’s import the namespace ‘SPPurchaseOrderNS’ in our code behind and declare an object of the data context as shown below –

SPPurchaseOrderDataContext dataContext = new SPPurchaseOrderDataContext("http://localhost:21068");

Now let’s write some code in the ‘Form_Load’ event that binds the query result to the Datagridview –

Datagridview sharepoint

On the ‘New’ button click event, write the following code to reset the controls –

Datagridview sharepoint New

Now to add the item in the ‘Customers’ list, write the following code in the click event of ‘Save’ button –

Datagridview sharepoint Save

Finally insert the record as shown below and click on the ‘Save’ button –

Datagridview sharepoint Insert

Once you insert the item in the ‘Customers’ list, check your customers list in SharePoint site and confirm that it has been correctly inserted –

Sharepoint List

Now write some code for updating a record from the ‘Customers’ list. Write the code shown below in the click event of the ‘Update’ button –

Sharepoint update list

Now if you enter the ‘Customer ID’ and change the name of the customer, it will get updated in SharePoint list as shown below –

Sharepoint update list

The earlier name was ‘Pravinkumar R. D.’ and after an update, it is ‘Pravin D’. The final step is to add functionality to delete a customer record from ‘Customers’ list and see how the cascading clause deletes the dependent ‘Orders’ from orders list. So for the ‘Delete’ functionality, write the following code –

Sharepoint delete list

Now enter the ‘CustomerID’ and click on ‘Delete’ button. If you observe, your customer record as well as orders attached with that customer, will be deleted from the lists. Let’s observe this –

Before ‘Delete’, the items in ‘Customer List’ and ‘Orders List’ look like this –

Sharepoint delete list

And after deleting the item, it looks this –

Sharepoint delete list

So now we are sure that our cascade settings on the Orders list is in effect. As soon as you delete the item from Customers list, the associated Orders for that Customer, will be deleted.

Summary – In this article we have seen how to create SharePoint Lists with cascade and perform CRUD operations on SharePoint Lists.

The entire source code of this article can be downloaded over here

Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+
Further Reading - Articles You May Like!
Author
Pravinkumar, works as a freelance trainer and consultant on Microsoft Technologies. He is having over 10 years of experience in IT and is also a Microsoft Certified Trainer(MCT). He has conducted various corporate trainings on all versions of .NET Technologies including .NET, SharePoint Server, Microsoft SQL Server, Silverlight, ASP.NET, Microsoft PerformancePoint Server 2007 (Monitoring). He is passionate about learning new technologies from Microsoft. You can contact Pravinkumar at dabade[dot]pravinkumar [attherate] gmail[dot]com


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by somesh Batra on Tuesday, December 20, 2011 6:05 AM
This is one of the best articles so far I have read online. No crap, just useful information. Very well presented. Its really helpful for beginner as well as developer. Thanks for sharing with us. I have found another nice post with wonderful explanation about Insert, Update, Delete in SharePoint 2010 list using Client Object Model. for more details of that post check out this link...

http://mindstick.com/Articles/50c4e9b3-2190-4bbf-9e57-035f228f1516/?Insert,%20Update,%20Delete%20in%20SharePoint%202010%20list%20using%20Client%20Object%20Model

Thanks

Comment posted by Ajay on Tuesday, February 28, 2012 5:53 AM
Hello sir your article is very good. I ant to implement same code in grid view. Can you please provde any link.
Comment posted by Adriano on Monday, May 7, 2012 10:36 AM
I have the following error

Error 4 Can not implicitly convert type 'System.DateTime?' to 'System.DateTime'. An explicit conversion exists (check for missing a conversion) C: \ Users \ laureano.rubio \ Desktop \ LINQToSharePoint \ LINQToSharePoint \ Form1.cs 39 164 LINQToSharePoint
Comment posted by Pramod Yadav on Tuesday, August 7, 2012 6:58 AM
very good article ,self explanatory thanks a lot.........