SharePoint 2013 Data Access Layer using SPMetal

Introduction

This is a step by step tip for creating LINQ to SharePoint Data Access Layer using the out-of-the-box SPMetal.exe tool, We will create a class library project that will expose a Data Context for SharePoint and can be referenced by any SharePoint Project, providing easy and (Entity Framework-like) way to perform CRUD operations on SharePoint Lists.

Considerations

Comparing to CAML queries which -I believe- are as spaghetti as writing SQL in C# code :), It is much more cleaner, easier and more scalable, but it comes with some disadvantages, these are the problems that I found so far:

• Problems in updating or inserting lookup values, but you can work around this
• Slower than using CAML
• and has to be updated each time you change the schema

Background

To get the most out of this tip and -in general- LINQ to SharePoint, you have to have at least:

• Sound knowledge of multi-tier programming
• Worked with LINQ before
• Good knowledge in SharePoint Development

Creating the Data Access Layer (Project)

In this section, we're going to create a class library project that is the access layer and use the SPMetal.exe to create the data context, we will also see how to configure the tool to generate classes for a specified Lists.

SPMetal.exe

It's a tool provided by Microsoft that is used to create Data Context for SharePoint. It comes with SharePoint by default. You don't have to download it, you can find it in: %ProgramFiles%\Common Files\Microsoft Shared\web server extensions\15\BIN. If this is the first time you heard about it, please refer to http://msdn.microsoft.com/en-us/library/office/ee538255(v=office.14).aspx since it's a prerequisite for the next section.

Creating the Project

We need to include the generated .cs file in the project so we can use the resulting DLL as our DataAccess layer in other projects. We will automate the generation using a prebuild command.

Step 1

Open command prompt as admin in SharePoint Server and execute the following commands:

1. cd C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\BIN
2. SPMetal /web:"http://{SiteName}" /code:"C:\SPDataContext.cs"

After execution is done, you will find the cs file "C:\SPDataContext.cs", so far we've created the context, but we can't use it as it is because it's been created for everything which usually causes build errors because of some data types and stuff we don't need -in this case-.

The solution is to limit the tool to generate classes for specified lists by creating an XML file (Parameters) and pass it to the tool. Now create the file "C:\Parameters.xml" and open it in your favorite text editor. Add the below code to it (modify to match your schema):

<?xml version="1.0" encoding="utf-8"?>
<Web xmlns="http://schemas.microsoft.com/SharePoint/2009/spmetal">
  <List Name="List1Name" />
  <List Name="List2Name" />
  <List Name="List3Name" />
  .
  .
  .
  <List Name="ListNname">
  <ExcludeOtherLists />
</Web>

Now execute the following commands to generate the context for the specified lists: (Note: The tool will overwrite the code file each time you run it.)

1. cd C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\BIN
2. SPMetal /web:"http://{SiteName}" /code:"C:\SPDataContext.cs" /parameters:"C:\Parameters.xml"

You can include the namespace that you want the classes to be in by passing the parameter /namespace:SPDataAccessLayer.SPDataAccess to the command.

Step 2

Now to automate this, we need to create a project and include the previous commands as prebuild command for the project.

1. Open Visual Studio as admin and create new project from (Visual C# --> ClassLibrary) and name it "SPDataAccessLayer"
2. Right click the project and add new file Parameters.xml
3. Right click the project and add new file SPDataContext.cs
4. Right click the project --> Properties --> Build Events
5. Paste the following commands in the "Pre-build event command line": cd C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\BIN
   SPMetal /web:"http://{SiteName}" /code:"$(ProjectDir)SPDataContext.cs.cs" /parameters:"$(ProjectDir)Parameters.xml" /namespace:SPDataAccessLayer.SPDataAccess
6. Save the properties and build the project.

We have our Context in the project DLL and now we need to test our DataAccess layer.

Creating Test Project

For testing purposes, we will create simple Windows application to test the Context, but you -ofcourse- use it in any Sharepoint solution (web part, user control, Timer Job, ...etc.).

Step 1

1. Open Visual Studio as admin and create a new project from (Visual C# --> Windows Forms Application) and name it "SPTestApplication".
2. Add reference to the "SPDataAccessLayer" project.
3. Add reference to the "Microsoft.SharePoint.dll" .
4. Add reference to the "Microsoft.SharePoint.Linq.dll" .
5. Open "Form1" in designer mode -if not opened- and Drag-Drop DataGridView control from the toolbox to the form.
6. Open the code file for the form and add "using SPDataAccessLayer.SPDataAccess"
7. In the code file, add the following code and run the project.

private static SPDataContext context = new SPDataContext("http://{SiteName}");
.
.
.
protected void Form_OnLoad(object sender, EventArgs e)
{
   try
   {
       var ContactUsList = (from c in context.List1Name select c).ToList<List1NameItem>();
       dataGridView1.DataSource = ContactUsList;
       dataGridView1.AutoGenerateColumns = true;
   }
   catch (Exception)
   {

       throw;
   }
}

You will notice that it's very slow but don't worry, this is because it's Windows application and it needs to load all necessary DLLs and create the SPContext. But when you run this code in SharePoint Context, it will be only a little bit slower than using SharePoint Object Model.

Notes

I couldn't provide the source code because I don't want to expose our context and schema, and if I exclude this information, the project will be useless. I think they were straightforward steps to create it but if you need any further information, please leave a comment. :).

Points of Interest

The most annoying thing I came across using this was the need to regenerate the context each time you modify the schema -as I mentioned earlier-, but I think it makes sense and it should be like this, this is the same as modifying the database while it's used by EntityFramework. Anyways, you still can add columns to lists and everything will be fine, you only need to regenerate the context when you delete or modify existing columns, but in our case you don't have to worry about regenerating the context because it's being regenerated on each build.

Give It A Try!

Please let me know if you have any comments/questions and please, vote up and share if you find this helpful :).