Share via

Walkthrough: Creating an External List in SharePoint by Using Business Data

  • Article

The Business Data Connectivity (BDC) service enables SharePoint to display business data from back-end server applications, Web services, and databases.

This walkthrough shows you how to create a model for the BDC service that returns information about contacts in a sample database. You will then create an external list in SharePoint by using this model.

This walkthrough illustrates the following tasks:

  • Creating a project.

  • Adding an entity to the model.

  • Adding a finder method.

  • Adding a specific finder method.

  • Testing the project.

Prerequisites

You need the following components to complete this walkthrough:

  • Supported editions of Microsoft Windows and SharePoint. For more information, see Requirements for Developing SharePoint Solutions.

  • Visual Studio 2010 Professional or an edition of Visual Studio Application Lifecycle Management (ALM).

  • Access to the AdventureWorks sample database. For more information about how to install the AdventureWorks database, see SQL Server Sample Databases.

Creating a Project

First, create a project that contains a BDC model.

To create a project

  1. Start Visual Studio 2010.

  2. Open the New Project dialog box, expand the SharePoint node under the language that you want to use, and then click 2010.

  3. In the Templates pane, select Business Data Connectivity Model. Name the project AdventureWorksContacts, and then click OK.

    The SharePoint Customization Wizard appears. This wizard enables you to select the site that you will use to debug the project and the trust level of the solution.

  4. Click Finish to accept the default local SharePoint site and default trust level of the solution.

Adding Data Access Classes to the Project

To add data access classes to the project

  1. On the Tools menu, click Connect to Database.

    The Add Connection dialog box opens.

  2. Add a connection to the SQL Server AdventureWorks sample database. For more information, see Add/Modify Connection (Microsoft SQL Server).

  3. In Solution Explorer, click the project node.

  4. On the Project menu, click Add New Item.

  5. In the Installed Templates pane, select the Data node.

  6. In the Templates pane, select LINQ to SQL Classes.

  7. In the Name box, type AdventureWorks, and then click Add.

    A .dbml file is added to the project and the Object Relational Designer (O/R Designer) opens.

  8. On the View menu, click Server Explorer.

  9. In Server Explorer, expand the node that represents the AdventureWorks sample database, and then expand the Tables node.

  10. Drag the Contact (Person) table onto the O/R Designer.

    An entity class is created and appears on the design surface. The entity class has properties that map to the columns in the Contact (Person) table.

Removing the Default Entity from the BDC Model

The Business Data Connectivity Model project adds a default entity named Entity1 to the model. Remove this entity. Later, you will add a new entity. Starting with an empty model reduces the number of steps required to complete the walkthrough.

To remove the default entity from the model

  1. In Solution Explorer, expand the BdcModel1 node, and then double-click the BdcModel1.bdcm file.

  2. The Business Data Connectivity model file opens in the BDC designer.

  3. In the designer, right-click Entity1, and then click Delete.

  4. In Solution Explorer, right-click Entity1.vb (in Visual Basic) or Entity1.cs (in C#), and then click Delete.

  5. Right-click Entity1Service.vb (in Visual Basic) or Entity1Service.cs (in C#), and then click Delete.

Adding an Entity to the Model

Add an entity to the model. You can drag entities from the Visual Studio Toolbox onto the BDC designer.

To add an Entity to the model

  1. On the View menu, click Toolbox.

  2. From the BusinessDataConnectivity tab of the Toolbox, drag an Entity onto the BDC designer.

    The new entity appears on the designer. Visual Studio adds a file to the project named EntityService.vb (in Visual Basic) or EntityService.cs (in C#).

  3. On the View menu, click Properties Window.

  4. In the Properties window, set Name to Contact.

  5. On the designer, right-click the entity, click Add, and then click Identifier.

    A new identifier appears on the entity.

  6. In the Properties window, change the name of the identifier to ContactID.

  7. In the Type Name drop-down list, select System.Int32.

Adding a Specific Finder Method

To enable the BDC service to display a specific contact, you must add a Specific Finder method. The BDC service calls the Specific Finder method when a user selects an item in a list and then clicks the View Item button in the Ribbon.

Add a Specific Finder method to the Contact entity by using the BDC Method Details window. To return a specific entity, add code to the method.

To add a Specific Finder method

  1. On the BDC designer, select the Contact entity.

  2. On the View menu, click Other Windows, and then click BDC Method Details.

  3. In the BDC Method Details window, from the Add a Method drop-down list, select Create Specific Finder Method.

    Visual Studio adds the following elements to the model. These elements appear in the BDC Method Details window.

    • A method named ReadItem.

    • An input parameter for the method.

    • A return parameter for the method.

    • A type descriptor for each parameter.

    • A method instance for the method.

  4. In the BDC Method Details window, click the drop-down list that appears for the Contact type descriptor, and then click Edit.

    The BDC Explorer opens. The BDC Explorer provides a hierarchical view of the model.

  5. In the Properties window, click the drop-down list that appears next to the TypeName property, click the Current Project tab, and then select Contact.

  6. In the BDC Explorer, right-click the Contact, and then click Add Type Descriptor.

    A new type descriptor named TypeDescriptor1 appears in the BDC Explorer.

  7. In the Properties window, set the Name property to ContactID.

  8. Click the drop-down list next to the TypeName property, and then select Int32.

  9. Click the drop-down list next to the Identifier property, and then select ContactID.

  10. Repeat step 6 to create a type descriptor for each of the following fields.

    Name

    Type Name

    FirstName

    System.String

    LastName

    System.String

    Phone

    System.String

    EmailAddress

    System.String

    EmailPromotion

    System.Int32

    NameStyle

    System.Boolean

    PasswordHash

    System.String

    PasswordSalt

    System.String

  11. In the BDC designer, on the Contact entity, double-click the ReadItem method.

    The Contact service code file opens in Code Editor.

  12. In the ContactService class, replace the ReadItem method with the following code. This code performs the following tasks:

    • Retrieves a record from Contact table of the AdventureWorks database.

    • Returns a Contact entity to the BDC service.

    Note

    Replace the value of the ServerName field with the name of your server.

    Public Shared Function ReadItem(ByVal contactID As Integer) As Contact
    Const ServerName As String = "MySQLServerName"
    Dim dataContext As AdventureWorksDataContext = _
        New AdventureWorksDataContext("Data Source=" & ServerName & _
            ";Initial Catalog=AdventureWorks;Integrated Security=True")

    Dim Contact As Contact = _
        (From TempContacts In dataContext.Contacts.AsEnumerable().Take(20) _
        Where TempContacts.ContactID = contactID _
        Select TempContacts).[Single]()
    Return Contact
End Function

    public static Contact ReadItem(int contactID)
{
    const string ServerName = "MySQLServerName";
    AdventureWorksDataContext dataContext = new AdventureWorksDataContext
          ("Data Source=" + ServerName + ";" +
           "Initial Catalog=AdventureWorks;Integrated Security=True");

    Contact Contact =
        (from contacts in dataContext.Contacts.AsEnumerable().Take(20)
         where contacts.ContactID == contactID
         select contacts).Single();
    return Contact;
}

Adding a Finder Method

To enable the BDC service to display the contacts in a list, you must add a Finder method. Add a Finder method to the Contact entity by using the BDC Method Details window. To return a collection of entities to the BDC service, add code to the method.

To add a Finder method

  1. In the BDC designer, select the Contact entity.

  2. In the BDC Method Details window, collapse the ReadList node.

  3. From the Add a Method drop-down list that appears below the ReadList method, select Create Finder Method.

    Visual Studio adds a method, a return parameter, and a type descriptor.

  4. In the BDC designer, on the Contact entity, double-click the ReadList method.

    The Contact service code file opens in Code Editor.

  5. In the ContactService class, replace the ReadList method with the following code. This code performs the following tasks:

    • Retrieves data from the Contacts table of the AdventureWorks database.

    • Returns a list of Contact entities to the BDC service.

    Note

    Replace the value of the ServerName field with the name of your server.

    Public Shared Function ReadList() As IEnumerable(Of Contact)
    Const ServerName As String = "MySQLServerName"
    Dim dataContext As AdventureWorksDataContext = _
        New AdventureWorksDataContext("Data Source=" & ServerName & _
            ";Initial Catalog=AdventureWorks;Integrated Security=True")

    Dim Contacts As IEnumerable(Of Contact) = _
        From TempContacts In dataContext.Contacts.Take(20) _
              Select TempContacts
    Return Contacts
End Function

    public static IEnumerable<Contact> ReadList()
{
    const string ServerName = "MySQLServerName";
    AdventureWorksDataContext dataContext = new AdventureWorksDataContext
          ("Data Source=" + ServerName + ";" +
           "Initial Catalog=AdventureWorks;Integrated Security=True");

    IEnumerable<Contact> Contacts =
        from contacts in dataContext.Contacts.Take(20)
        select contacts;
    return Contacts;

}

Testing the Project

When you run the project, the SharePoint site opens and Visual Studio adds your model to the Business Data Connectivity service. Create an external list in SharePoint that references the Contact entity. The data for contacts in the AdventureWorks database appear in the list.

Note

You might have to modify your security settings in SharePoint before you can debug your solution. For more information, see Designing a Business Data Connectivity Model.

To test the project

  1. Press F5.

    The SharePoint site opens.

  2. On the Site Actions menu, click More Options

  3. In the Create page, click External List, and then click Create.

  4. Name the custom list Contacts.

  5. Click the browse button next to the External Content Type field.

  6. In the External Content Type Picker dialog box, select AdventureWorksContacts.BdcModel1.Contact, and then click Create.

  7. Click Create to create the contacts list.

    SharePoint creates an external list. Contacts from the AdventureWorks sample database appear in that list.

  8. To test the Specific Finder method, click a contact in the list.

  9. On the Ribbon, click the Items tab.

  10. In the Items tab, click View Item.

    The details of the contact that you selected appear on a form.

Next Steps

You can learn more about how to design models for the BDC service in SharePoint from these topics:

See Also

Concepts

BDC Model Design Tools Overview

Other Resources

Designing a Business Data Connectivity Model

Creating a Business Data Connectivity Model

Integrating Business Data into SharePoint