Walkthrough of enabling CRUD for SharePoint 2010 external lists using Visual Studio 2010

Walkthrough of enabling CRUD for SharePoint 2010 external lists using Visual Studio 2010

  • Comments 0

In our previous blog post of this series Walkthrough of creating a SharePoint 2010 external list using Visual Studio 2010 Beta, we introduced how to create a simple “Hello world” external list in SharePoint 2010 Beta using Business Data Connectivity Designer in Visual Studio 2010 Beta.

In this blog, we will show you how to pull data from an external database into an external list and enable Create, Read, Update and Delete (CRUD) functions to the external list.

First of all, you need to have SharePoint 2010 Public Beta and Visual Studio 2010 installed on your machine in order to complete this walkthrough. We’ll use “Northwind” database as external data source, so if you do not have an existing “Northwind” database available, we’ll walk you through to create a local database using SQL Server Express first (SQL Server Express comes with Visual Studio installation by default, in case you don’t yet have it, download it here).

At the end of this post, we will complete with a BDC model project which has a “Customer” entity connects to “Customer” table in “Northwind” database, and have CRUD operations enabled. The finished project can also be downloaded from here.

Prepare the data source

If you already have a “Northwind” database, you can skip this section. Otherwise, please download SharePoint2010_BDCSamples.zip from here and extract the SQL script file CreateSampleNorthwindDB.sql.

Open Visual Studio. Go to View->Server Explorer. Right click Data Connections in Server Explorer, and select Create New SQL Server Database.

1. In the prompt dialog, type “localhost\sqlexpress” in Server Name text box, and give the new database name “SampleNorthwind”.

* If you're using the SQL Express that comes with SharePoint Server, please replace “localhost\sqlexpress" with "localhost\sharepoint”.

2. Start a Command Prompt. Go to Start->Run, type “Cmd” in the text box and click OK.

3. In the Command Prompt, type in following command and press enter:

sqlcmd -S localhost\sqlexpress -d samplenorthwind -i <Path of CreateSampleNorthwindDB.sql file>

Create BDC Project

Create a new C# BDC Model project and rename it “BdcSampleCSharp”. VB code snippets will also be provided, so you can create VB BDC Model project if you want. In this walkthrough, we will use C# project as an example. (Check this blog post on how to create a BDC project)

Connect to external data source

To use the SampleNorthWind database, we add a LINQ to SQL model to the project:

1. On the Project menu, click Add New Item, in the prompt Add New Item dialog select Data in the Installed Templates pane, in the Templates pane select LINQ to SQL Classes, in the Name box, type “Customer”, and then click Add.

2. In the Server Explorer, go to Data Connections->[hostname]\sqlexpress.SampleNorthWind.dbo->Tables->Customers, drag the Customers table and drop it on the Customer.dbml design surface.

3. Add a new class file and rename it “CustomerDataContext.cs”. Replace the code of the class with the following code snippet.

Note: We made the connection string a constant in the code only for demo purpose, if you’re using your own database, modify the connection string as needed. In our future post we will introduce how to set the connection string in a custom property on LobSystemInstance inside the BDC model and read the value through IContextProperty interface at runtime.

C#:

public partial class CustomerDataContext
{
  private const string ConnectionString = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=SampleNorthwind;Integrated Security=True;Pooling=False";
  public CustomerDataContext() : 
            base(ConnectionString, mappingSource)
  {
    OnCreated();
  }
}

VB:

Partial Public Class CustomerDataContext
  Private Const ConnectionString As String = "Data Source=localhost\SQLEXPRESS;Initial Catalog=SampleNorthwind;Integrated Security=True;Pooling=False"
  Public Sub New() MyBase.New(ConnectionString, mappingSource)
    OnCreated()
  End Sub
End Class

Design BDC Model

1. On the design surface, delete entity Entity1 which is created by default. On the View menu, click on Toolbox if it is not shown. Create a new entity by drag and drop the Entity icon from Toolbox onto design surface (see the screenshot below). In the Properties Browser, change the value of Entity’s Name property to “Customer”.

clip_image001

2. Create a new Identifier CustomerID on entity Customer. To do so, on the design surface, right click the entity, click Add->Identifier. A new identifier appears on the entity, and then rename it to “CustomerID”.

3. Create a Specific Finder method for the entity. To do so, on the design surface, select entity Customer, you could find a <Add a Method> command in the Method Details Window. If the Method Details Window is not opened, you can find it in menu View->Other Windows->BDC Method Details. From the <Add a Method> drop-down list, select Create Specific Finder Method:

clip_image002

A method named ReadItem appears on entity Customer. In the Method Details Window, you will find that the method takes a In parameter and a Return parameter. In the next step we will define TypeDescriptors associated with these parameters.

4. Add TypeDescriptors for the return parameter Customer. The edit need to be done in BDC Explorer. You can find it by going to View->Other Windows->BDC Explorer.

a) In the Method Details Window, click <Edit> command in the drop down control from TypeDescriptor Customer as depicted below. After the click the BDC Explorer will get focused on the TypeDescriptor Customer.

clip_image003

b) In BDC Explorer right click the focused TypeDescriptor Customer and select Add Type Descriptor:

clip_image004

A new TypeDescriptor is created under TypeDescriptor Customer. In the Properties Browser, rename it to “CustomerID”. Next you need to set the Identifier property to “CustomerID” to tell the BCS runtime which identifier this TypeDescriptor represents. Here is a screenshot of Properties Browser after this step:

clip_image005

c) Continue to add following TypeDescriptors under Customer by repeating the operation described above: Address, City, CompanyName, ContactName, Country, Fax, Phone, PostalCode and Region, for each TypeDescriptor, you need to change its Type Name to make sure they match the type defined in the LINQ to SQL model. In this example, all the TypeDesriptors have a type of System.String which is the default one so we do not need to change them. After this step, we get the following TypeDescriptors in BDC Explorer:

clip_image006

d) Next we need to define the actual type of the TypeDescriptor Customer. Click TypeDescriptor Customer in BDC Explorer. You will find in the Properties Browser the value of Type Name property is System.String by default, we need to change it to “BdcSampleCSharp.Customer, BdcModel1” which is a LobSystem qualified type name. This specifies the actual the data type of the data structure that is represented by this TypeDescriptor Customer.

5. Create the other types of methods by the same way described in step 6. After this step we will have five methods in the entity Customer: ReadItem, ReadList, Create, Update and Delete. If you check the ReadList method in Method Details Window, the TypeDescriptors of the return parameter have already been defined with the same structure as we just built above. This is because when creating a new method, BDC designer will search the possible TypeDescriptors defined in the other methods of this entity and copy them to the newly created methods. This saves the developers so much time to define them repetitively.

Add code behind to access external data source

Now it’s time to add code to implement the CRUD functions. In Solution Explorer, find and open CustomerService.cs, and then replace the implementation with the following code snippet:

C#:

public static Customer ReadItem(string customerID)
{
  CustomerDataContext context = new CustomerDataContext();
  Customer cust = context.Customers.Single(c => c.CustomerID == customerID);
  return cust;
}

public static IEnumerable<Customer> ReadList()
{
  CustomerDataContext context = new CustomerDataContext();
  IEnumerable<Customer> custList = context.Customers;
  return custList;
}

public static Customer Create(Customer newCustomer)
{
  CustomerDataContext context = new CustomerDataContext();
  context.Customers.InsertOnSubmit(newCustomer); context.SubmitChanges();
  Customer cust = context.Customers.Single(c => c.CustomerID == newCustomer.CustomerID);
  return cust;
}

public static void Delete(string customerID)
{
  CustomerDataContext context = new CustomerDataContext();
  Customer cust = context.Customers.Single(c => c.CustomerID == customerID);
  context.Customers.DeleteOnSubmit(cust);
  context.SubmitChanges();
}

public static void Update(Customer customer, string customerID)
{
  CustomerDataContext context = new CustomerDataContext();
  Customer cust = context.Customers.Single(c => c.CustomerID == customer.CustomerID);
  cust.CustomerID = customer.CustomerID;
  cust.Address = customer.Address;
  cust.City = customer.City;
  cust.CompanyName = customer.CompanyName;
  cust.ContactName = customer.ContactName;
  cust.ContactTitle = customer.ContactTitle;
  cust.Country = customer.Country;
  cust.Fax = customer.Fax;
  cust.Phone = customer.Phone;
  cust.PostalCode =customer.PostalCode;
  cust.Region = customer.Region;
  context.SubmitChanges();
}

VB:

Public Shared Function ReadItem(ByVal customerID As String) As Customer
  Dim context As New CustomerDataContext
  Dim cust = (From c In context.Customers _
  Where c.CustomerID = customerID _
  Select c).Single()
  Return cust
End Function

Public Shared Function ReadList() As IEnumerable(Of Customer)
  Dim context As New CustomerDataContext
  Return context.Customers
End Function

Public Shared Function Create(ByVal newCustomer As Customer) As Customer
  Dim context As New CustomerDataContext
  context.Customers.InsertOnSubmit(newCustomer)
  context.SubmitChanges()
  
  Dim cust = (From c In context.Customers _
  Where c.CustomerID = newCustomer.CustomerID _
  Select c).Single()
  Return cust
End Function

Public Shared Sub Delete(ByVal customerID As String)
  Dim context As New CustomerDataContext
  Dim cust = (From c In context.Customers _
  Where c.CustomerID = customerID _
  Select c).Single()
  
  context.Customers.DeleteOnSubmit(cust)
  context.SubmitChanges()
End Sub

Public Shared Sub Update(ByVal customer As Customer, ByVal customerID As String)
  Dim context As New CustomerDataContext
  Dim cust = (From c In context.Customers _
  Where c.CustomerID = customer.CustomerID _
  Select c).Single()

  cust.Address = customer.Address
  cust.City = customer.City
  cust.CompanyName = customer.CompanyName
  cust.ContactName = customer.ContactName
  cust.ContactTitle = customer.ContactTitle
  cust.Country = customer.Country
  cust.Fax = customer.Fax
  cust.Phone = customer.Phone
  cust.PostalCode = customer.PostalCode
  cust.Region = customer.Region
  context.SubmitChanges()
End Sub

Create an external list to test out the BDC Model!

Now we are done! Let’s deploy the solution and create an external list to test it out (Check this blog to see how to create an external list). After the list is created, you will see the following page appears when you click on the list name:

clip_image007

All the data are pulled out to SharePoint successfully, now you can create, delete or edit customer records.

To create a new Customer, find the List Tools->Items->New Item button on the ribbon shown in the following screenshot:

clip_image008

Click on the button, New Item dialog will pop out as below:

clip_image009

Fill in the dialog and click Save, you will find the item created in the list right away.

To edit or delete the item, just click the down arrow at the right side of CustomerID, all the operations you need are there.

clip_image010

Yanchen Wu

Leave a Comment
  • Please add 3 and 6 and type the answer here:
  • Post