Monday, April 19, 2010

How to: Create an external list in SharePoint 2010 using BCS via Visual Studio 2010 - Part 2

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:
  1. "Simple" solutions: Using SP Designer 2010 and browser. It connects to existing back-end integration services or simple databases. 
  2. "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 


Go to List part >> External List >> Create

We look for external content types which we defined in our BDC model.


Fill in the details for the external list.

Step12: Here is the output. This list is not from the content database.


Cheers,
--aaroh

Download the source here.

References:
Creating an External List in SharePoint by Using Business Data (SharePoint 2010)
Using Business Connectivity Services in SharePoint 2010
Creating Business Data Catalog Entities in SharePoint Server 2007

Monday, April 12, 2010

How to: Create an external list in SharePoint 2010 using BCS via SharePoint designer 2010 - Part 1


Hi All, 

I recently attended a session SharePoint user group (Singapore) and agenda was twofold: 
  1. SharePoint Storage Optimization  
  2. Introduction to Business Connectivity Services (BCS) in SharePoint 2010. 
The first session was about proper architecture design and intelligent sense storage management that is crucial for successful SharePoint implementation. The demo was given a company named "AvePoint" that specialize in SharePoint storage and identify organization's unique business objectives, usage patterns, deployment size, and distribution. They talked about: 
  • Reviewing strategies for SharePoint storage optimization via intelligent content life-cycle management
  • Automated Binary Large Object (BLOB) offloading 
Migration-free SharePoint management of legacy data stores such as file share.    
They have different suits for SharePoint 2007 and 2010 such as DocAve File Share Document Connector, DocAve File Share Multimedia Connector, migration tools, testing, data protection etc. Free trial versions available as well. 

The second session was more interesting for me as it deals with SharePoint 2010.  Business Connectivity Services (BCS) is the next generation of Business Data Catalog (BDC, SharePoint 2007) for accessing external data within SharePoint. 

In SharePoint 2007 context, BDC provides the integration feature that enables SharePoint 2007 to incorporate the business data from back-end server applications. For an instance, we are able to connect to display data from our line-of-business (LOB) such as SAP, Siebel via Web Services or database WITHOUT any CODING. The business data could be in SharePoint lists, web parts, search, and custom applications. However, there is problem in this approach: 

  • BDC provides a read-only solutions.Therefore, it was not so simple to create a solution that enabled users to make changes and write that data back to the external store.
  • There is no offline mode. That is, the ability to take all SharePoint content offline. 
  • Lack of Designer.  
In SharePoint 2010,  Business Connectivity Services (BCS, formerly known as the BDC) does all the functionalists which BDC provided with new features: 
  • BCS data could be read/write.
  • The ability to take all SharePoint content offline using SharePoint Workspace 2010 (formerly Microsoft Groove)
  • Auto-sync of lists and libraries (for documents, only actual changes are synched)
  • Leverages SharePoint Designer 2010.
  • Visual Studio 2010 support for creating .NET assembly connectors and we can write more advanced scenarios.
The bottom line is that Business Connectivity Services (BCS) in SharePoint 2010 is all about connecting to external data (External Content Types). Steve Sofian (SharePoint MVP) showed how to get an external lists (not from SharePoint content database) via SharePoint Designer 2010. The concept of external content types is fundamental to BCS.

Understanding External Content Types:
Basically, ETC describes the schema and data access capabilities of an external data source and its behavior within Office and SharePoint 2010. The external data source could be a web service, database, .NET assembly connector, external systems, Web 2.0 service etc. The data that lives outside of SharePoint and it could be line-of-business applications, web services or something custom. We can leverage these external content types in different ways such as using SharePoint 2010 (external lists) and inside office clients.

Different tools are provided by Microsoft to leverage BCS:
  1. "Simple" solutions: Using SP Designer 2010 and browser. It connects to existing back-end integration services or simple databases. 
  2. "Advanced" solutions: Using Visual Studio 2010 for advanced scenarios.
Steve showed simple external list using SharePoint Designer 2010  with no code.

Step1: Fire up SharePoint Designer 2010. Navigate to File >> Sites >> Open Site >> Go to the specific site. 

New SharePoint Designer 2010 Beta
  Step2: New SharePoint Designer 2010 has been revamped completely. Earlier version looked like Front page but latest version comes with all SharePoint 2010 artifacts such as Site Navigation, Site Objects etc. 
Step3: Before we go to SharePoint Designer, we have to make to sure that Business Data Connectivity services has been started. We need to go to Central Administration >> Application Management >> Manage Service Application >> Business Data Connectivity and check if this service is activated or not




Step4: Switch back to SharePoint Designer and click on "External Content Types". I have downloaded the "AdventureWorkLT" database and downloaded to my local machine.  On right top of SharePoint Designer, we click on "External Content Type" button as shown below.


Step5: We then fill in information about this new content type as follows: 
Name: Contacts
Display Name: Contacts
Namespace: Conotoso
Office Item Type: Contact
Office Sync for External List: Enabled. 
What this setting is trying to do is that we can get this list offline using a tool named SharePoint Workspace 2010 (formerly know Microsoft Groove 2007, part of Office client).


Step6: Add a new data connection. 
We click on "Add Connection" button. We set "SQL Server" as the data source type. Data Source Type could have three options: 
>> .NET
>> SQL Server
>> WCF Service




Step7: We then set up connection properties as follows:


Step8: Expand the tables and look for the "Customer" table. We have to create methods to be associated with this external content type. Lets create all the operations. Whats new in SharePoint 2010 unlike SP 2007 which is read-only methods, we are able to read/write methods which were not possible in SP 2007.



Step9: A wizard will come up where we have to set a few operations properties. Click "Next" button to proceed.  


Step10: We will get some errors and we have to address them. 
Go to LastName field and in Office property choose the "Last Name (LastName)" from the drop down. Furthermore, check the "Show In Picker" as well. The error will disappear. 


Go to "rowguid" field and clear the "Required" option. The warning will disappear.

The next screen we have to set some filter because the table has numerous columns and we do not want to show everything. Firstly, we hit the "Add Filter Parameter" and secondly click on filter property as shown below.    


From the "Filter Configuration" window, we choose the filter type to "Limit" and click OK button.


Once the filter limit is set, we can choose the default value to say 100 records.
Click on "Save" button and external content type will be saved to the BDC Metadata store.


Go to Central Administration >>  Application Management >> Manage Service Application >> Business Data Connectivity >> Manage. We will get "Contacts" as external data source.



Step11: Go to SharePoint 2010 site, Site Actions >> More Options >> In "Filter type" choose "List" >> select "External List" >> Hit on "Create" button.


In list name "BCS Contacts" and select the external content type as shown below. 




Select the "Contacts" external content type and click on "OK" button.




The list is created and it is not from the SharePoint content database but from different database.



Unlike BDC in SharePoint 2007 which is read-only solution, in SharePoint 2010 we can modify/delete via SharePoint UI. 


Cheers, 
--aaroh

How to display Sharegate administrative dynamic reports via Power BI on SharePoint Online

After working in the SharePoint domain for a couple of years as SharePoint consultant , I am fortunate enough to help customers from man...