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.
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 for 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”.
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:
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.
b) In BDC Explorer right click the focused TypeDescriptor Customer and select Add Type Descriptor:
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:
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:
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:
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:
Click on the button, New Item dialog will pop out as below:
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.
Posts
