ARTICLE

LINQ to SharePoint with WCF 4.0

Posted by Dhananjay Kumar Articles | SharePoint 2010 April 13, 2011
How can we perform CRUD operations on a SharePoint 2010 list from a WCF Service? Assume we need to create a WCF Service and that service will perform a CRUD operation on a SharePoint 2010 list. We are going to see how to do that in this article.
Reader Level:


Objective

How can we perform CRUD operations on a SharePoint 2010 list from a WCF Service? Assume we need to create a WCF Service and that service will perform a CRUD operation on a SharePoint 2010 list. We are going to see how to do that in this article.

Flow Diagram

LinqSPWCF1.gif

Now we will follow the steps below to perform all the above operations.

Step 1: Setting up the SharePoint list

We have a custom list

  1. Name of the list is Test_Product.
  2. Columns of the list is as below:

    LinqSPWCF2.gif
     
  3. There are two items in the list.

    LinqSPWCF3.gif
     
  4. URL of the SharePoint site is:

    http://dhananjay-pc/my/personal/Test1


Now we will access the SharePoint site on the above URL.

Step 2: Creating a WCF Service

Now we will create a WCF Service. A WCF Service will have four operation contracts for CRUD operations on a list.

To create a WCF service, open Visual Studio and from the WCF tab select WCF Service application.

LinqSPWCF4.gif

Now to make sure that we are able to use SPLinq (or LINQ to SharePoint) we need to change the target framework and Platform target.
So to do that follow the steps below:
  1. Right click and click on Properties of WCF Service

    LinqSPWCF5.gif
     
  2. Click on the Build tab and change Platform Target to Any CPU.

    LinqSPWCF6.gif
     
  3. Click on the Application tab and change the Target framework type to .Net Framework 3.5

    LinqSPWCF7.gif

Now we need to create a DataContext class of the SharePoint list such that we can perform LINQ against that class to perform a CRUD operation. To create a context class we need to perform the steps below.
  1. Open the command prompt and change directory to

    C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN

    Type command CD C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN

    LinqSPWCF8.gif
     
  2. Now we need to create the class for corresponding list definitions.

    C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN> spme
    tal.exe /web:http://dhananjay-pc/my/personal/Test1 /namespace:nwind /code:Product.cs

    In the above command we are passing a few parameters to spmetal.exe; they are:

    a. /web:Url

    Here we need to provide the URL of the SharePoint site

    /web:http://dhananjay-pc/my/personal/Test1 /

    ://dhananjay-pc/my/personal/Test1 / is URL of SharePoint site, I created for myself. You need to provide your SharePoint site URL here.

    b. /namespace:nwind

    This would be the namespace under which the class of the list will be created. In my case the name of the namespace would be nwind.

    c. /code:Product.cs

    This is the file name of the generated class. Since we are giving name Product for the file then the class generated will be ProductDataContext.

    The class's default location is the same folder as SPMetal.exe. So to see where the class was created we need to navigate to folder:

    C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN

    Add created class to the WCF Service project

    Now add this class to the project. To do this, right click on the WCF Service project and select Add Existing Item. Then browse to the above path and select Product.cs

Host the WCF Service in IIS

To work with SPLInq in WCF service, we need to host the service in IIS with the same application pool SharePoint is running.

See the link below for a step by step explanation of how to host a WCF 4.0 Service in IIS 7.5.

http://dhananjaykumar.net/2010/09/07/walkthrough-on-creating-wcf-4-0-service-and-hosting-in-iis-7-5/

We only need to make sure that the Hosted WCF service is sharing the same application pool with SharePoint.

LinqSPWCF9.gif

Select the Application pool to SharePoint-80

Create Contracts

We need to create a DataContract or Data Transfer object. This class will represent the Product Data context class (we generated this class in a previous step) 0

Create Data Contract,

[DataContract]
  public class ProductDTO
  {
      [DataMember]
      public string ProductName;
      [DataMember]
      public string ProductId;
      [DataMember]
      public string ProductPrice;

  }

Add References to work with LINQ to SharePoint

  • Microsoft.SharePoint

  • Microsoft.SharePoint.Linq

Right click on Reference then select Add Reference. To locate the Microsoft.SharePoint and Microsoft.SharePoint.Linq DLLs browse to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI. All the SharePoint DLLs are in that location.

Add the namespace

LinqSPWCF10.gif

Nwind is the name of the namespace of the data context class we created in previous steps. We need to make sure that we have added the Product class to the WCF Service application project as Add an Existing item.

Create Service Contract

We need to create a Service contract. There would be four operation contracts, one for each operation in the Sharepoint list.

IService1.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
using System.Runtime.Serialization;
using System.ServiceModel.Description;

namespace WcfService1
{
  [ServiceContract ]
    public  interface IService1
    {
 
      [OperationContract]
      List<ProductDTO> GetProduct();
      [OperationContract]
      bool InsertProduct(ProductDTO product);

      [OperationContract]
      bool UpdateProduct(ProductDTO product);

      [OperationContract]
      bool DeleteProduct(ProductDTO product);

    }

Implement the Service

Now we need to implement the service to perform operations.

Retrieving all the list items

public  List<ProductDTO> GetProduct()
        {
            using (ProductDataContext context = new ProductDataContext("http://dhananjay-pc/my/personal/Test1"))
            {
 
                ProductDTO product;
                List<ProductDTO> lstProducts = new List<ProductDTO>();
                var result = from r in context.Test1_Product select r;
                foreach (var r in result)
                {
 
                    product = new ProductDTO { ProductId = r.ProductId, ProductName = r.ProductName, ProductPrice = r.ProductPrice.ToString ()
};
                    lstProducts.Add(product);
                }

                return lstProducts;
            }

        }


In the above code:

  1. Creating instance of ProductDataContext.

  2. Using LINQ to SharePoint retrieving all the list items.

  3. Creating instance of ProductDTO class with values fetched from SharePoint list.

  4. Adding the instance of ProductDTO class in List of ProductDTO.

  5. Returning list of ProductDTO

Inserting an element in the List

public bool InsertProduct(ProductDTO product)

        {

           try

            {

                using(ProductDataContext context = new ProductDataContext("http://dhananjay-pc/my/personal/Test1"))

                {

                EntityList<Test1_ProductItem> products = context.GetList<Test1_ProductItem>("Test1_Product");

                Test1_ProductItem itemToInsert = new Test1_ProductItem()
                                                 {
                                                     ProductId = product.ProductId ,
                                                     ProductName = product.ProductName ,
                                                     ProductPrice = Convert.ToDouble(product.ProductPrice)
                                                 };
                products.InsertOnSubmit(itemToInsert);
                context.SubmitChanges();
                return true;
           }
            }
            catch (Exception ex)
            {
                return false;
            }

        }

In the above code

  1. Boolean is being returned. If an item is saved successfully then true will be returned by the service else false.

  2. An instance of ProductDataContext is being created and submitchanges() will be called on that instance of ProductDataContext.

  3. An instance of Test_ProductItem is being created and we are passing values from ProductDTO to create an instance of Test_ProductItem. A point we need to note here is that Test_Product is the name of the SharePoint list.

  4. Calling InsertOnSubmit() to submit a list to item to get inserted.

Updating an Element in the List

        {
            try
            {
                using (ProductDataContext context = new ProductDataContext("http://dhananjay-pc/my/personal/Test1"))
                {
                    var itemToUpdate = (from r in context.Test1_Product where r.ProductId == product.ProductId select r).First();
                    itemToUpdate.ProductName = product.ProductName;
                    itemToUpdate.ProductPrice = Convert.ToDouble(product.ProductPrice);
                    context.SubmitChanges();
                    return true;
                }
            }
            catch(Exception ex)
            {
                return false;
 
          }

          }

In the above code

  1. We are fetching a list item to be updated on the basis of ProductId of ProductDTO.

  2. Assigning the new values

  3. Performing the submitchnages on instance of ProductContext class.

Deleting an Element from the List

public bool DeleteProduct(ProductDTO product)
        {
            try
            {
                using (ProductDataContext context = new ProductDataContext("http://dhananjay-pc/my/personal/Test1"))
                {
                    EntityList<Test1_ProductItem> products = context.GetList<Test1_ProductItem>("Test1_Product");
                    var itemToDelete = (from r in context.Test1_Product where r.ProductId == product.ProductId  select r).First();
                    products.DeleteOnSubmit(itemToDelete);
                    context.SubmitChanges();
                    return true;
                }
            }
            catch (Exception ex)
            {
                return false;
            }

        }

In the above code

  1. We are fetching the list item to be deleted.

  2. Calling DeleteOnSubmit() and passing the item to be deleted.

  3. Calling SubmitChanges() on an instance of ProductContext.

Modify the Web.Config

  1. We will put Binding as basicHttpBinding.

  2. In ServiceBehavior attribute, we will enable the servicemetadata

  3. In ServiceBehavior, we will make IncludeserviceException as false.

  4. We will configure MetadataExchnagePoint in the service

So, now in Web.Config Service configuration will look like

system.serviceModel>
              <
behaviors>
                     <
serviceBehaviors>
       
                           <
behavior name="MyBeh">
                                                             
          <
serviceMetadata httpGetEnabled="true"/>
                          
<
serviceDebug includeExceptionDetailInFaults="false"/>
                           </behavior>
                     </
serviceBehaviors>
              </
behaviors>
              <
services>
                     <
service name="WcfService1.Service1"  behaviorConfiguration="MyBeh">
                           <endpoint address="" binding="basicHttpBinding" contract="WcfService1.IService1"/>
                           <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"/>
                            <host>
                                  <
baseAddresses>
                                         <
add baseAddress="http://localhost:8181/Service1.svc/"/>
                                  </baseAddresses>
                           </
host>
                     </
service>
              </
services>
              <!--
<serviceHostingEnvironment multipleSiteBindingsEnabled="true"/>-->
       </
system.serviceModel>

Now our service is created. We will be hosting this service in IIS as we discussed in a previous step. From IIS we will browse to test whether service is up and running or not.

For reference, the full source code for service implementation is as below:

Service1.svc.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using Microsoft.SharePoint.Linq;
using Microsoft.SharePoint;
using nwind;
namespace WcfService1
{
   
    public class Service1 : IService1 
    {
        public  List<ProductDTO> GetProduct()
        {
            using (ProductDataContext context = new ProductDataContext("http://dhananjay-pc/my/personal/Test1"))
            {
 
                ProductDTO product;
                List<ProductDTO> lstProducts = new List<ProductDTO>();
                var result = from r in context.Test1_Product select r;
                foreach (var r in result)
                {
 
                    product = new ProductDTO { ProductId = r.ProductId, ProductName = r.ProductName, ProductPrice = r.ProductPrice.ToStrin
() };

                    lstProducts.Add(product);
                }

                return lstProducts;
            }

        }

        public bool InsertProduct(ProductDTO product)
        {
           try
            {
                using(ProductDataContext context = new ProductDataContext("http://dhananjay-pc/my/personal/Test1"))
                {
                EntityList<Test1_ProductItem> products = context.GetList<Test1_ProductItem>("Test1_Product");
                Test1_ProductItem itemToInsert = new Test1_ProductItem()
                                                 {
                                                     ProductId = product.ProductId ,
                                                     ProductName = product.ProductName ,
                                                     ProductPrice = Convert.ToDouble(product.ProductPrice)
                                                 };
                products.InsertOnSubmit(itemToInsert);
                context.SubmitChanges();
                return true;
           }
            }
            catch (Exception ex)
            {
                return false;
            }
        }
 
 
        public bool UpdateProduct(ProductDTO product)
        {
            try
            {
                using (ProductDataContext context = new ProductDataContext("http://dhananjay-pc/my/personal/Test1"))
                {
                    var itemToUpdate = (from r in context.Test1_Product where r.ProductId == product.ProductId select r).First();
                    itemToUpdate.ProductName = product.ProductName;
                    itemToUpdate.ProductPrice = Convert.ToDouble(product.ProductPrice);
                    context.SubmitChanges();
                    return true;
                }
            }
            catch(Exception ex)
            {
                return false;
            }
        }

        public bool DeleteProduct(ProductDTO product)
        {
            try
            {
                using (ProductDataContext context = new ProductDataContext("http://dhananjay-pc/my/personal/Test1"))
                {
                    EntityList<Test1_ProductItem> products = context.GetList<Test1_ProductItem>("Test1_Product");
                    var itemToDelete = (from r in context.Test1_Product where r.ProductId == product.ProductId  select r).First();
                    products.DeleteOnSubmit(itemToDelete);
                    context.SubmitChanges();
                    return true;
                }
            }
            catch (Exception ex)
            {
                return false;
            }

        }

    }
}



Creating a Managed Application (Console client to consume Service)

Now we will create a new project by selecting console application as project type. Right click on the project and add a service reference. Give the URL of the WCF Service hosted in IIS.

We need to call the service to perform the CRUD operation on SharePoint list from the console client.

Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
using ConsoleApplication1.ServiceReference1;
using System.Collections.Generic;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
               Service1Client proxy = new Service1Client();

// Insertion
               ProductDTO productToInsert = new ProductDTO { ProductId = "99", ProductName = "Ball", ProductPrice = "700" };
               if (proxy.InsertProduct(productToInsert))
                   Console.WriteLine("Inserted successfully ");
               else
                   Console.WriteLine("Opps some problem in Insertion ");
 
// Update

               ProductDTO productToUpdate = new ProductDTO { ProductId = "99", ProductName = "Ball for New game ", ProductPrice = "7000" };
               if (proxy.InsertProduct(productToInsert))
                   Console.WriteLine("Updated  successfully ");
               else
                   Console.WriteLine("Opps some problem in Updation ");
 
//Delete

               ProductDTO productToDelete = new ProductDTO { ProductId = "99" };
               if (proxy.InsertProduct(productToInsert))
                   Console.WriteLine("Deleted  successfully ");
               else
                   Console.WriteLine("Opps some problem in Deletion ");

//Reterive

               Console.WriteLine("Records are as below ");
               List<ProductDTO>  p = proxy.GetProduct().ToList();
               foreach (var r in p)
               {
                   Console.WriteLine(r.ProductName + r.ProductId + r.ProductPrice);
               }
               Console.ReadKey(true);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            Console.ReadKey(true);
        }
           
    }
}

Calling a service is very simple. We just need to create a proxy of the service and call the methods on the service as a normal function with required parameters.

Output

LinqSPWCF11.gif
 

COMMENT USING
Employers - Post Free Jobs