Creating a SharePoint 2010 External Content Type with CRUD Methods using LINQ and a SQL LOB System

Synopsis:

First, this lab builds on an article I saw on MSDN which discussed how to create an External List using Business Data. I noticed that they showed methods for the code but showed no foundation or a Use Case, nor did it discuss the LOB System and how the class entity relationship is defined.

In this Lab/Demo we will show a SQL LOB system which holds Personnel data. Imagine if you will, a system such as SAP, PeopleSoft, Dynamics, etc.  Imagine also that this LOB system is the Source of Record for your organization for things such as Human Resource related data which may be consumed by Active Directory, Sales, Marketing, Finance(Payroll) etc.  How can we surface that data in a Read, Create, Update and Delete fashion maintaining fidelity and ease of use?  here is how…

First

• Check out your Database and pay special to the Columns which will be your fields in your Class Definition, also document your data types.
• Create a Visual Studio Project using the Business Data Connectivity Model Template
• Connect to your Database
• Model the Entity
• Use LINQ to create the methods to LOB Systems
• Create the External List
• Test and Verify

Above: First we need to have our database ready. Above we can infer that we will be going against a server called “Tico”, a Database called “FabianPlayPen” and a Table called “Employee” please take time to notice the current data records currently in there. Once you are done, crack open your Visual Studio 2010 Beta 2.

Note: To work with the Beta 2 bits of SharePoint 2010 you MUST use Visual Studio 2010 Beta 2 for the Templates to work.

Above:  Create a new Project of type “Business Data Connectivity Model” the guy you need for BCS.  I have my VS 2010 set for C# only but you can do this with VB.NET also. I have named my Project “SPSDCEmployees”; this is important because I will be using this name in conjunction with the Entity Class to be created later on in this demo/lab.

Above:  Once you have selected and named your Project you are prompted to choose the site for debugging. I have already created a site called “http://tico/sites/BCSAlpha” for this venture and i have so identified it in my dialog box.

Above:  Once the Project is created, the first order of business is to create a connection to our database.  We are doing this as the means to:

1. Create a connection to our LOB System
2. Use it through LINQ to SQL as our model to abstract our Class/Entity

Above:  Once you click on Add Connection you will be presented with the dialog box above, please indicate your Server Name and the Database you are interested in.  In my example my Server is my doggie’s name “Tico” and the Database is called “FabianPlayPen” very apropos wouldn’t you say? 

Above:  After connecting your Database we need to include another  Template to our project. This time we are including a “LINQ to SQL” Template as a means to create our Entity

Above:  By default and design, when you create a BCS Project an Entity called “Entity1” will be created, shortly we will remove that entity because as part of that process two classes are also created which we will subsequently delete. Reason being, there is a lot of code already inplace that we will need tie into, by deleting the Entity Object and the related Classes and subsequently recreating our own, we have  better control over our build process. In our example we named our New Item “FabianPlayPen” and a file called FabianPlayPen.dbml is crated in our solution.

Above:  The blank design window is what you are initially presented with and is the framework where we will model our Entity Class. 

Above:  I followed the steps below:

• On the View menu, click Server Explorer.
• In Server Explorer, expand the node that represents the Employee database, and then expand the Tables node.
• Drag the Employee table onto the O/R Designer.

Above:  The newly created entity is now present in the OR designer nomenclature is the name of the table in the LOB System.  An entity class is created and appears on the design surface. The entity class has properties that map to the columns in the Employee table.  Now that that process is complete, we need to do some clean-up to aid our design process.

Above:  In Solution Explorer we dobule click on BdcModel1.bdcm and we get the desing pane above.

• In Solution Explorer, expand the BdcModel1 node, and then double-click the BdcModel1.bdcm file.
• The Business Data Connectivity model file opens in the BDC designer.
• In the designer, right-click Entity1, and then click Delete.
• In Solution Explorer, right-click Entity1.cs, and then click Delete.
• Right-click Entity1Service.cs, and then click Delete.

as you see below…

Above and Below: The process to delete the associated .cs files.

 

—————————————————————————————–

Above: We will now create our new entity which will abstract our Employee LOB System table from our FabianPlayPen database.

• On the View menu, click Toolbox.
• 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.cs
• On the View menu, click Properties Window.
• In the Properties window, set Name to Employee
• On the designer, right-click the entity, click Add, and then click Identifier.
• A new identifier appears on the entity.
• In the Properties window, change the name of the identifier to EmployeeID

See Below

Below is a representation of our work so far, it includes the newly created Entity and our Identifier. Next we will create our Methods to Create, Read, Update and Delete.

Above:  We will begin the process to create a Finder Method. This method is used to basically surface a List of “ALL” items in the database

1. On the BDC designer, select the Employee 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 Finder Method.

1. Visual Studio adds a method, a return parameter, and a type descriptor.
2. In the BDC Method Details window, click the drop-down list that appears for the EmployeeList type descriptor, and then click Edit as seen below

1. The BDC Explorer opens. The BDC Explorer provides a hierarchical view of the model.
2. In the Properties window, set the Type Name property to System.Collections.Generic.IEnumerable`1[SPSDCEmployees.Employee, BdcModel1] as seen below

In the BDC Explorer, expand the EmployeeList node and select the Contact node.

In the Properties window, set the Type Name property to SPSDCEmployees.Employee, BdcModel1.

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

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

In the Properties window, set the Name property to EmployeeID.

Set the Type Name property to System.Int32.

From the Identifier drop-down list, select ContactID.

Repeat step 8 to create a type descriptor for each of the following fieldsAs seen in the below 3 ScreenShots.

Screen Shot 1

Screen Shot 2

Screen Shot 3

Now we need to create the remainder of the Type Descriptors for the Employee Entity; here is where we go back to our LOB System or our Entity Class created earlier and get the Names and Data Type of the columns to create our TypeDescriptors. Once we have done that it is time to add the code for our  ReadList Method

Above:  Double-Click on the ReadList in the Employee Entity to enter the code view as seen below.  By default a method is created and we need to remove the entry in the method and replace with our own.

Above:  Our code is inserted in the ReadList Method

• A connection string is created to attach to our LOB System
• An Instance of the Employee List is created and populated with all the data from the LOB System

That it. Easy isnt it! Next we need to create a Specific Finder Method which is responsible for returning a single item as requested in the SharePoint UX. As before we go back to the BCS Designer to add the Method and add our code… see below next three screen shots.

1. In the BDC designer, select the Employee 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 Specific Finder Method.
4. Visual Studio adds the following elements to the model. These elements appear in the BDC Method Details window.
   1. A method named ReadItem.
   2. An input parameter for the method.
   3. A return parameter for the method.
   4. A type descriptor for each parameter.
   5. A method instance for the method.
5. In the BDC designer, on the Employee entity, double-click the Readitem method and add the code you see in the second (2nd) screen shot.

We will create two additional method Create and Update.. lets go with Create first then the Update Method.  As before we begin from the Entity and select new method from the BDC Method Details Pane. Click the Method and add the code as reflected the screen shot.

Next we create the Update Method

TEST AND VERIFY

Finally we get to deploy our solution and see it in action. Click build then Deploy Solution to get our code up to our Farm.

Above:  Click Build then Deploy Solution…

Above:  To verify that the solution is uploaded we go to Central Admin under Application Management, Click on Business Data Connectivity to see if the Build was uploaded to the server Farm, see below…

Above:  Evidenced that our External Content Type is on the Server Farm, we now move to creating our External List to surface the information abstracted by the External Content Type.

Above:  Under Site Actions, click Create More, then choose External List. 

Above:  Select the External Content Type we created as above…

Above:  Create a name and ensure that we have the correct Data Source Configuration

Above:  So… Viola! we have our external content type representative of the SQL Database Table called Employee, neat huh! 

Above:  as part of our verification process we have confirmation that our columns are consistent to the Entity and LOB System. Now lets kick the tires on the newly created methods..

Above:  Lets click “Add New Item” this will instantiate and fire off the Create Method of our External Content Type. The next four screen shots represent the new form, the entered values, the post and final resultant Read List with the created item. 

Above: The blank auto-generate form when you click Add New Item

Above:  Filling in the form with what will be the new data

Above:  Awesomness! we have our newly created item in our List… later we will do a SQL query to validate the record. Next, let us investigate the Update Method…

Above:  In this example we click on the custom action by the Picker field and we elect the “Edit Item” Once we click it we see the below…

Above:  The item in question is noted in the Update Form… I am picking  on my buddy Todd Baginski here, he does and awesome work on BCS and other SharePoint 2010 elements. Visit him here

Above:  Well because his name is synonymous to  good ol Frodo I relocated Todd to the Shire…

Above:  Confirm the changes above… 

Above:  Finally we check the LOB system and we can see our Create, and Our Update, come to the SPS DC for the Delete… LOL