Aroh's Microsoft 365 Musings: How to: Create an external list in SharePoint 2010 using BCS via Visual Studio 2010

Hi all,

In last post, I have demonstrated how we can create an external list using BCS via SharePoint Designer 2010.

In SharePoint 2007 context, we used have a couple of problems such as

Business Data Catalog (BDC) provided only read-only solutions.
SharePoint Designer 2007 did not support BDC modeling i.e. there was completely lack of a designer. Although, its possible to develop BDC entities using Visual Studio 2008. Here we can connect to a sample database such as "AdventureWorks" database (SQL Server 2005/2008), create a BDC meta data file that defines a single entity "Customer" that can be exposed to the SharePoint 2007 site. Eventually, we will expose "Customer" entity as defined in the database via Business Data List in SharePoint. Again, we just uploading an XML file in central administration and not a SharePoint solution (WSP) in this case. (Please refer to MSDN Visual How To's create Business Data Catalog entities in Microsoft Office SharePoint Server 2007.)
No capability to take all SharePoint content offline.

In SharePoint 2010 came up with new features to circumvent the shortcomings of SharePoint BDC:

BCS data could be read/write which as huge improvement.
SharePoint Designer 2010 supports BCS.
The ability to take all SharePoint content offline.

Different tools are provided by Microsoft to leverage BCS:

"Simple" solutions: Using SP Designer 2010 and browser. It connects to existing back-end integration services or simple databases.
"Advanced" solutions: Using Visual Studio 2010 for advanced scenarios.

But lets discuss a very brief details on BSC architecture.

SharePoint 2010 BSC Architecture:

If we look at MSDN's BSC architecture, it looks Microsoft has invested into BSC a lot and has enhanced SharePoint platform capabilities with out-of-the-box features, services and tools that streamline development of solutions with deep integration of external data and services.

NOTE:
With respect the SharePoint 2007, BDC introduced business integration feature that exposes business data from back-end server application without any coding. But the there were lot of issues mentioned above. BDC DOES facilitates to surface the external data. For an instance, we can develop an XML definition file that defines the mapping between SharePoint and the external data such as data connections to SQL Server etc. Subsequently, the XML file could be rendered as a BDC web part but its packaged as SharePoint solution. BDC uses the same metadata model. Metadata is the data about the business application's APIs. For each business application, metadata defines the business entities that the business application interacts with and the methods available in the business application. The developer is responsible to define metadata by using XML. The BDC stores the metadata in the metadata repository.

The most promising feature of BSC is the its read/write capabilities and access to the external data from line-of-business (LOB) systems, web services, databases, and other external systems within SharePoint 2010 and Office 2010 applications. These are new BSC features:

BDC Metadata store: provides storage for a collection of external content types, each of which describes how to connect the external store. External content types are fundamental building block of BSC.
Solution Packaging: BSC can be packaged as a Visual Studio Tools.
Out of Box UI - BSC carries forward the ability to display external data through a Web Part UI and provides deeper integration through the addition of external lists.
Design Tools: SharePoint Designer 2010 provides a lot of out-of-the-box functionality where no coding is required. Visual Studio 2010 provides a professional developer to extend those capabilities to create advanced solutions.

In this post, lets walk through the steps how can develop a simple BCS model and write an entity using Visual Studio 2010.

Step1: Create a New project in VS2010 RC New Project >> SharePoint 2010 as template >> Choose "Business Data Connectivity Model" >> Project Name: AdventureWorksContacts>> Click OK.

Step2: For the BDC model, we have only farm solution that we can be deployed as WSP. Go ahead by entering the local site for debugging and click on "Finish" button.

Step3: We be presented with business data catalog designer and we can now leverage the visual surface to develop entities. The entities could be contact, customer, sales order etc in enterprise data source. Lets delete the default entity ("Entity1") and Entity1.cs, Entity1Service.cs etc which are created by Visual Studio 2010. After deleting the entity, we are go ahead to develop the solution from the scratch.

Step4: Go to the Toolbox >> Drag and drop the Entity >> Rename to "Contact" as shown below.

Step5: Every entity needs an unique identifier. Lets call it "ContactID".

Once we have define a new identifier, we proceed to change the its Type Name. ContactID is defined as System.Int32 in the database. So, lets change the data type.

Step6: Now, in order to make it possible for the consumer of this model to display a list of contacts and to make possible for users to select the details of the contact, we need to add methods that the consumer calls. There are two methods that we required to enable this scenario.

Finder method.
Specific finder method.

We will use specific finder method as its easy to implement. Go to the BDC model designer and right click on "Methods" >> Click on "Add new Method"

A new window will come up ("BDC Method Details"), click on "Add a Method" and create a specific finder method.

This is going to generate a meta-data representation of the method signature which has a return parameter (contact) and input parameter (contactID). The input parameter will be supplied by the consumer of the method. We are responsible for the returning the contact.

"BDC Method Details", in the "Return" part, we "Edit" the "Contact". When we edit the contact, we will get "BDC Explorer" window. Entity "Contact" thinks that its a string. But its not a string, it will be object. However, we have not defined the object yet. This will mean that we have to define a class that holds data fields in current project. This object will return back to consumer eventually. We will create this class in a new way.

Step7: I have already downloaded SQL Server 2008 database and also downloaded "AdventureWorks" database as well. GO to SQL Server 2008 console and connect to "AdventureWorks: database.

Switch to VS2010 >> Tools >> Connect to database >> Choose Server and database names as shown below.

Step8: In the solution explorer, right click on the "AdventureWorksContacts" project >> Add >> New Item and in the "Data" template choose "LINQ to SQL Classes" >> Name: "AdventureWorks" >> Click Add button. Using this method, it will generate a class with fields in it. We can then refer these fields in our BDC model.

Next, we go to Server Explorer, locate "Contact" table and drag/drop to "AdventureWorks.dbml" as shown below. "Contact" entity has all the fields. We are done with the LINQ to SQL class part. The class is already generated.

If you go to Step 6, I have mentioned "Contact" is not a string but its an object. In Step 8, we have already defined object using "LINQ to SQL classes" with all the fields defined in our entity.
Go to BDC Explorer >> BdcModel1 >> Contact >> ReadItem >> contact >> Contact

"Contact" properties, we have to change "Type Name" from string to our own object which defined in Step 8. In Type Name click on the drop down >> Current Project >> AdventureWorksContacts >> Contact.

Now, we get the real object.

Step9: We then add type descriptor for each field that I want to return to the consumer.

The first field will be "ContactID" and type name will be System.Int32.
We have to add something additional in the "Identifier" property and set that field to "ContactID". Its only for this field. For other fields Identifier is not needed.

The second field will be "FirstName" and type name will be string.

This is something very interesting here. What we are doing here we are leveraging visual studio 2010 to create fields using a designer interface. In the background, VS2010 is actually generating an XML file. If we are not using VS2010, we to manually code this XML file.

We can add those fields along the same lines.

Step10: We are done with defining the all the fields for the "Contact" object. We can go to BdcModel1.bdcm >> Methods >> ReadItem >> and go to view code.

We write some code under "ReadItem" method:

public static Contact ReadItem(int contactID)
        {
            const string ServerName = @"SP2010\SQLEXPRESS";
            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;
        }

In this simple code, we declare a string that holds name of server. It uses a generated class i.e. dataContext that comes with our model.

In later part of code we use LINQ expressions. We create an instance of the contact and method returns single instance of the contact by passing the contactID.

Here is the fun part. We want to create a finder method which will return a list of contacts. The cool part is that we can go to method window, create a finder method and we DONE!.

VS2010 is smart enough and looked at the definition which we created in previous methods. The fields are copied and we do not need them manually.

We can go to BdcModel1.bdcm >> Methods >> ReadList >> and go to view code. Add this code:

public static IEnumerable<Contact> ReadList()
        {
            const string ServerName = @"SP2010\SQLEXPRESS";
            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;
        }
Deploy the solution.

Step11: Go to the SharePoint 2010 Site >> Site Actions >> More Options