Another walkthrough of enabling CRUD BCS for SP2010

 

Introduction

Chapter 9 in my Book (Sahil Malik; Building Solutions for SharePoint 2010) deals with Business Connectivity Services and provides a rather simplistic example listing Northwind Customers in a read-only manner. Finishing this chapter I have decided to complete that passive list with Delete, Update, Create (CRUD) functionality and searched the web a little bit for existing examples. And here it is “Walkthrough of enabling CRUD for SharePoint 2010 external lists using Visual Studio 2010“. So what’s the hell, repeating and reporting something already completed is not too much fun. Let’s hence fore do this example somewhat differently concerning two new aspects:

  1. The cited above example uses a connection string physically stick/built into the fabric of the package as a hardcoded library setting. Therefore it is rather inconvenient recompiling and redeploying the whole package due to SQL connection modifications. In my example I have decided to put the connection string into the web.config which of course makes possible  later updates without redeploying the solution. Honestly note however any manual change of a configuration section which is under SharePoint’s modification control is definitely NOT a best practice.  
  2. Secondly I have decided to use ADO.NET Entity Framework instead of the originally proposed LINQ to SQL model. This decision seemed to be an easy leap however later on I have realized there is a workaround needed to get the solution work.

The first Steps

So you could start with the example referenced above. In order to have all the steps in one single place (who knows how long referenced links live) I’m going to provide all the steps of a complete solution from the scratch.

Start with a new Visual Studio 2010 Solution and take the Business Data Connectivity Model template. Name the solution e.g. EntityFrameworkBdcNw which is of course a Farm solution like expected.

We have got a default Entity1 class (Picture 1) in the designer displayed which is not to be confused with ADO.NET Entity Framework entities even the look and feel suggests it is such. This entity is rather originated in the strangely named LobSystem (Line of Business) defined in the Microsoft.SharePoint.BusinessData namespace.

image

Picture 1

Modifying the web configuration

Let’s focus firstly on the connection string problem. As suggested I’m going to put it into the Web-Application’s web.config file using the SPWebConfigModification class. The idea is to add an Event-Receiver for a particular Feature and while FeatureActivated is called insert the connection string as needed and at FeatureDeactivating cleanup and remove all modifications from web.config. At this step there is already one Feature in the solution, let’s rename it from the default Feature1 to FeatureModel. This Feature is however targeting a whole Farm. The FeatureModel is therefore rather inappropriate and we have to descend one step in the SharePoint server model hierarchy and use a new Feature targeting the WebApplication (SPWebApplication) level.

Let’s do so and create one more Feature and make sure it truly targets WebApplication and rename it to FeatureConfig. This feature will not going to have any items content and it’s sole purpose will be to act during FeatureActivated and FeatureDeactivating.

Right mouse button FeatureConfig and click on “Add Event Receiver”. The solution at this stage shows Picture 2. It is important to note, that poking around configuration modifications could be a rather unfortunate enterprise and you as developer could face easily a broken SharePoint configuration state. The problem is, that as soon you call the SPWebConfigModification’s Update() method, all (even broken and wrong) modifications will go to the SharePoint configuration database. Subsequent calls of ApplyWebConfigModifications() will apply every record found in that store and except you know how to deal with the situation, you could find yourself in a desperate need of remedy. For this reason I’m providing with this recent solution a command-line cleanup utility as well (see more at the end of this article).

image
Picture 2

For the mentioned above reasons inserting and removing modifications should go at the very beginning synchronously. This makes sure all items added will be removed. Code 1 provides the FeatureActivated and Code 2 provides the FeatureDeactivating details.

public override void FeatureActivated(SPFeatureReceiverProperties properties)
{      
    SPWebApplication webApp = (SPWebApplication)properties.Feature.Parent;
    AddConnectionString(webApp,
        “sp_admin_sr”,
        “NorthwindEntities”,
        [Here goes your connection string],
        “System.Data.EntityClient”,
        0);
}
private static void AddConnectionString(
                   SPWebApplication webApp,
                   string Owner,
                   string connectionName,
                   string connectionString,
                   string provider,
                   uint Sequence)
{
            SPWebConfigModification addChild = new SPWebConfigModification();
            addChild.Name = “add”;
            addChild.Path = “configuration/connectionStrings”;
            addChild.Owner = Owner;
            addChild.Sequence = Sequence;
            addChild.Type = SPWebConfigModification.SPWebConfigModificationType.EnsureChildNode;
            addChild.Value = String.Format(“<add />”);
            webApp.WebConfigModifications.Add(addChild);

            SPWebConfigModification nameAttribute =
    new SPWebConfigModification(“name”, “configuration/connectionStrings/add”);
            nameAttribute.Name = “name”;
            nameAttribute.Owner = Owner;
            nameAttribute.Sequence = Sequence;
            nameAttribute.Type = SPWebConfigModification.SPWebConfigModificationType.EnsureAttribute;
            nameAttribute.Value = connectionName;
            webApp.WebConfigModifications.Add(nameAttribute);

            SPWebConfigModification connAtt =

new SPWebConfigModification(“connectionString”, “configuration/connectionStrings/add”);
            conAtt.Name = “connectionString”;
            conAtt.Owner = Owner;
            conAtt.Sequence = Sequence;
            conAtt.Type = SPWebConfigModification.SPWebConfigModificationType.EnsureAttribute;
            conAtt.Value = connectionString;
            webApp.WebConfigModifications.Add(conAtt);

            SPWebConfigModification providerAtt =

new SPWebConfigModification(“providerName”,“configuration/connectionStrings/add”);
            providerAtt.Name = “providerName”;
            providerAtt.Owner = Owner;
            providerAtt.Sequence = Sequence;
            providerAtt.Type = SPWebConfigModification.SPWebConfigModificationType.EnsureAttribute;
            providerAtt.Value = provider;
            webApp.WebConfigModifications.Add(providerAtt);
            webApp.Update();
            webApp.WebService.ApplyWebConfigModifications();
}
 

Code1

Please note (Code 1) that the connection string is constructed in four steps within the AddConnectionString() method. Firstly is created the <add> XML element, and in subsequent steps the element’s Attributes are constructed, namely the name, connectionString and provider. It is important to mention, that the method could be engineered alternatively to crate the connection string in one single step. Such solution is provided in my command-line utility and is described at msdn here How to: Add and Remove Web.config Settings Programmatically

Also note the connection string itself is not yet known (see placeholder Code 1) and we will fill this gap later while creating the conceptual model using ADO.NET Entity Framework.

public override void FeatureDeactivating (SPFeatureReceiverProperties properties)
{
     SPWebApplication webApp = (SPWebApplication)properties.Feature.Parent;
     RemoveRecentModification(webApp, “sp_admin_sr”);
}

private static void RemoveRecentModification (SPWebApplication webApplication, string owner)
{
    // This collection will be removed 
    Collection<SPWebConfigModification> configModFound =

                new Collection<SPWebConfigModification>();

    // This collection keeps all modifications

    Collection<SPWebConfigModification> modsCollection = webApplication.WebConfigModifications;

    // Find the most recent modification of a specified owner 
    int modsCount1 = modsCollection.Count;
    for (int i = modsCount1 – 1; i > –1; i–)
    {
         if (modsCollection[i].Owner == owner)
         {
             configModFound.Add(modsCollection[i]);
         }
    }
    // Remove it and save the change to the configuration database   
    foreach (SPWebConfigModification cfg in configModFound)
        modsCollection.Remove(cfg);
    webApplication.Update();

    // Reapply all the configuration modifications 
    webApplication.WebService.ApplyWebConfigModifications();
}

 

Code 2

Please note in the above code the importance of the modifications’ Owner, which is here assigned to “sp_admin_sr” string. This makes sure only those modifications will be removed, which belong to this dedicated owner. You can assign any string to the Owner, hence fore you are in perfect control of these happenings.

Furthermore in order to have a somewhat complete configuration scenario, let’s draw your attention on the fact, that the above described solution assumes the XML element <configurationStrings> representing a configuration section is present in the web.config file. If this prerequisite is not true, the deployment will fail during configuration’s modification. In order to remedy you could add at the top of the AddConnectionString() method one more extra modification step (Code 3). Note here the “EnsureSection” Type which makes sure, this top level element exists only once and it is not to be removed at all.

SPWebConfigModification addChild1 = new SPWebConfigModification();
addChild1.Name = “connectionStrings”;
addChild1.Path = “configuration”;
addChild1.Owner = Owner;
addChild1.Sequence = Sequence;
addChild1.Type = SPWebConfigModification.SPWebConfigModificationType.EnsureSection;
addChild1.Value = String.Format(“<connectionStrings>”);
webApp.WebConfigModifications.Add(addChild1);

 

Code 3

Let’s clarify the fact, that within our farm solution, the configuration modification is targeting the WebApplication scope. The modification however will be executed for each WebApplication within the farm. This seems to be appropriate, as we are going to deploy a farm solution. It is to be expected all underlying web applications’ configuration files will be altered.

Incorporating ADO.NET Entity Framework

Now switch the technology and add to the project a new item “ADO.NET Entity Data Model” and just leave the model’s name lake suggested Model1.edmx ( Picture 3).  The pity in this approach is, that due to SharePoint 2010 this model is based on the  .NET 3.5 SP1. Therefore all the new cool Entity Framework stuff introduced in .NET Framework 4.0 cannot be reached. This is the reason we have to use later a tricky workaround in order to get the solution smoothly running.

image

Picture 3

Right now just complete the wizard’s steps creating a new connection (if previously no such existed) and selecting the Customers table from the list of available tables in the Northwind database (Picture 4). Clicking on the finish button the conceptual model will appear in the designer (Picture 5) and an app.config file will be added to the recent solution.

At this solution development stage you could check the new configuration and fill in the appropriate string replacing the placeholder in the FeatureActivated method (Code 1). Special care however should be taken concerning to the character entity references found in the connection string, like the &quot; character entity. This represents a quotation mark and in the FeatureActivated method could be directly replaced by a simple quotation mark. Otherwise you risk that this &quot; entity character will be resolved to &amp;quot; which is wrong.

image

Picture 4

image

Picture 5

In order to proceed we are going to alter the LobSystem model (Picture 1). What we have got here by default is a BDC (or LOB?) Entity1. Let’s rename it to CustomerEntity. Rename the identifier to CustomerID and note the two methods ReadList and ReadItem. These methods are the default Finder and Specific Finder Methods in terms of the LobSystem (Picture 6) meaning, SharePoint is going to use them in order to build the list (Finder) and displaying one specific selected Entity (Specific Finder).

Next switch please to the BDC Explorer view and expand ReadItem and rename here the identifier1 to CustomerID. Expand furthermore returnParameter and select Entity1. Rename it to Customer and in the Properties window click Type Name’s value selector, which will display the Type-Selector dialog (Picture 7). Take from the available types the Customer class representing the class from the just added Entity Data Model.

image

Picture 6

image

Picture 7

Select now the Identifier1 child below Customer and rename it to CustomerID. As a somewhat tedious task to proceed is now to manually recreate the very same property structure in the BDC Explorer like in the original Customer ADO.NET Entity exists. For this reason you have to select the Customer in the BDC Explorer, right mouse button, and take “Add Type Descriptor” as many time as many properties the Customer ought to have; eleven in sum (Picture 8). Note that CustomerID is the only Identifier among the properties.

image

Picture 8

Let’s make further changes in the ReadList Finder method. Expand the returnParamter and rename the Entity1List to CustomersList. Delete the Entity1 child node below the CustmersList. Copy the previously created Customer structure from the ReadItem Specific Finder method and just paste it to the CustomersList. Select now the Customer’s Type Name’s value (EntityFrameworkBdcNw.Customer, BdcModel1) in the properties window and paste this string into the CustomersList’s Type Name like displayed below:

System.Collections.Generic.IEnumerable`1[[EntityFrameworkBdcNw.Customer, BdcModel1]]

Highlighted in bold is the part to change mandatory. The current development stage in BDC Explorer is displayed at Picture 9.

image

Picture 9

Switch right now to the BDC Method Details pane, placed normally at the bottom of the Visual Studio IDE. For this reason DoubleClick first the bdcm model in the solution explorer which will let display the BDC Method Details. Here scroll down to the very bottom and click on <Add a Method> which will popup the dialog box selector for all possible Methods (Picture 10). Go ahead and add one Creator Method, one Deleter Method and one Updater Method.

image

Picture 10

Your task after completing and extending the total number of LobSystem-Methods to 5, is finished here. Just switch once more back to the BDC Explorer View and review the configurations of the three lastly added methods. Everything should be here fine.

Let’s go code and open up the CustomerEntityService.cs file and use the ADO.NET Entity Framework’s context to read, update, create and delete the Customer entity (Code 4).

public static Customer ReadItem(string id)
{
    EntityConnection conn = new EntityConnection(“name=NorthwindEntities”);
    using (NorthwindEntities context = new NorthwindEntities(conn))
    {
        var c = (from x in context.Customers
                 where x.CustomerID == id
                 select x).FirstOrDefault();

        context.Detach(c);
        return c;
   }  

}
public static IEnumerable<Customer> ReadList()
{
     EntityConnection conn = new EntityConnection(“name=NorthwindEntities”);
     using (NorthwindEntities context = new NorthwindEntities(conn))
     {
         IEnumerable<Customer> clist = from x in context.Customers
                                              select x;

        foreach (Customer c in clist)
           context.Detach(c);

        return clist.ToList();
    }

}

public static Customer Create(Customer newCustomerEntity)
{
      EntityConnection conn = new EntityConnection(“name=NorthwindEntities”);
      using (NorthwindEntities context = new NorthwindEntities(conn))
      {
          context.AddToCustomers(newCustomerEntity);
          context.SaveChanges();

          var cust = context.Customers.FirstOrDefault (x => x.CustomerID == newCustomerEntity.CustomerID);
          return cust;
      }      

}

public static void Delete(string customerID)
{
     EntityConnection conn = new EntityConnection(“name=NorthwindEntities”);
     using (NorthwindEntities context = new NorthwindEntities(conn))
     {
         var c = (from x in context.Customers
                         where x.CustomerID == customerID
                         select x).FirstOrDefault();

          context.DeleteObject(c);
          context.SaveChanges();
     } 
}

public static void Update(Customer customerEntity)
{
     EntityConnection conn = new EntityConnection(“name=NorthwindEntities”);
     using (NorthwindEntities context = new NorthwindEntities(conn))
     {
          var c = (from x in context.Customers
                         where x.CustomerID == customerEntity.CustomerID
                         select x).FirstOrDefault();
          if (c != null)
          {
               c.Address = customerEntity.Address;
               c.City = customerEntity.City;
               c.CompanyName = customerEntity.CompanyName;
               c.ContactName = customerEntity.ContactName;
               c.ContactTitle = customerEntity.ContactTitle;
               c.Country = customerEntity.Country;
               c.Fax = customerEntity.Fax;
               c.Phone = customerEntity.Phone;
               c.PostalCode = customerEntity.PostalCode;
               c.Region = customerEntity.Region;
               context.SaveChanges();
         }
     }
}

 

Code 4

Completing all these steps you could deploy the solution and proceed as described here:

  1. Enable/Set appropriate permissions for the new external content type CustomerEntity at Central Administration / Manage Service Applications / Business Data Connectivity Services / Set Object Permissions
  2. Recycled the IIS Application Pool (using IIS Services Manager)
  3. Create a new List based on External List Template. Click on the “Create” button within the Dialog Box.
  4. Select the targeted External Content Type in the displayed Dialog Window using the yellowish database symbol at the bottom on right hand side . This takes you to the list of available external content types (Picture 11).

If you did everything correctly the list will be filled with data from the Northwind Database and you could even select to view one particular item. This proves the Finder and Specific Finder Methods are called and the methods ReadItem and ReadList work fine. Even you could use Delete functionality however due to referential integrity at the database level this operation could fail ā€“ which is normal behavior.

image

Picture 11

Introducing the POCO Workaround

To your surprise however neither Update nor Create will work. So what the hell is going on? The Exception will tell you something around problems while setting CustomerID property to null value, which is allegedly not allowed. Scrutinizing the displayed exception stack will not bring you any additional useful information and the only thing you could do is thinking. Think about a while what’s happening while you are going to create a new Item? SharePoint will create a new instance of the Customer class. Yea, that should be not a problem on the server side. It turns out it is a problem however on the client side, meaning within Web Browser.

In this above scenario the catch is in the fact, that the innocently looking Customer class is derived from an awful internal class DataClasses.EntityObject. This class prevents us instantiating any new Customer instance with a null CustomerID. That is the message’s interpretation you are facing while trying to create in SharePoint a new Customer or update the selected one. The client will try to create by default a blank new Customer entity and subsequently assign the properties. This attempt will immediately fail. So what to do? We could edit and alter the ADO.NET Entity Framework generated conceptual model however allowing CustomerID to be null, will introduce more awful problems, hence fore this path is not the way to go.

In .NET Framework 4.0 we could trick the system creating POCO classes (Plain Old CLR Objects) and instead of the originally generated Customer class use the simplistic POCO representation. Although in .NET Framework 3.5 SP1 we do not have the appropriate framework support for such enterprise, we could still use this approach here poking a little bit around manually. Here are the steps to do:

  1. Create manually you Customer POCO class (Code 5) and name it CustomerPoco
  2. Locate the Update and Create methods and whatever Customer class the client is going to send back to the server, alter those classes to CustomerPoco (Code 6). Change appropriately these methods’ contents reflecting this change (whenever needed).
  3. Switch to the BDC Explorer and pick up the appropriate CustomerPoco POCO class for both the Create and Update inbound parameters
  4. Recompile and Deploy. Every function should start working like expected. CRUD works fine. Hope it works for you as well Smile
public class CustomerPoco 
{
        public string CustomerID { get; set; }
        public string Address { get; set; }
        public string ContactName { get; set; }
        public string ContactTitle { get; set; }
        public string CompanyName { get; set; }
        public string City { get; set; }
        public string Region { get; set; }
        public string Country { get; set; }
        public string PostalCode { get; set; }
        public string Phone { get; set; }
        public string Fax { get; set; }
}
 

Code 5

public static Customer Create(CustomerPoco newCustomerEntity) 
{
      EntityConnection conn = new EntityConnection(“name=NorthwindEntities”);
      using (NorthwindEntities context = new NorthwindEntities(conn))
      {
           Customer c = new Customer();
           c.CustomerID = newCustomerEntity.CustomerID;
           c.Address = newCustomerEntity.Address;
           c.City = newCustomerEntity.City;
           c.CompanyName = newCustomerEntity.CompanyName;
           c.ContactName = newCustomerEntity.ContactName;
           c.ContactTitle = newCustomerEntity.ContactTitle;
           c.Country = newCustomerEntity.Country;
           c.Fax = newCustomerEntity.Fax;
           c.Phone = newCustomerEntity.Phone;
           c.PostalCode = newCustomerEntity.PostalCode;
           c.Region = newCustomerEntity.Region;
           context.AddToCustomers(c);
           context.SaveChanges();
           var cust = context.Customers.FirstOrDefault(x => x.CustomerID == newCustomerEntity.CustomerID);
           return cust;
      }
}
 
public static void Update(CustomerPoco customerEntity)
{
     EntityConnection conn = new EntityConnection(“name=NorthwindEntities”);
     using (NorthwindEntities context = new NorthwindEntities(conn))
     {
          var c = (from x in context.Customers
                         where x.CustomerID == customerEntity.CustomerID
                         select x).FirstOrDefault();
          if (c != null)
          {
              c.Address = customerEntity.Address;
              c.City = customerEntity.City;
              c.CompanyName = customerEntity.CompanyName;
              c.ContactName = customerEntity.ContactName;
              c.ContactTitle = customerEntity.ContactTitle;
              c.Country = customerEntity.Country;
              c.Fax = customerEntity.Fax;
              c.Phone = customerEntity.Phone;
              c.PostalCode = customerEntity.PostalCode;
              c.Region = customerEntity.Region;
              context.SaveChanges();
          }
     }
}
 

Code 6

Here the link to download both the SharePoint Solution and the Configuration Clean-Up command line utility mentioned above:

http://cid-8d365142bc4869ab.office.live.com/self.aspx/.Documents/EntityFrameworkBdcNw.zip

Advertisements
This entry was posted in SharePoint 2010. Bookmark the permalink.

5 Responses to Another walkthrough of enabling CRUD BCS for SP2010

  1. Jim says:

    Thanks for the link to the event receiver on MSDN. I followed your steps almost exactly and the model seems to deploy fine but I’m deploying to a host-named site collection and the BDC database is partitioned to allow for a multi-tenant setup. For some reason I can’t get the model or external content types to show up in the tenant administration site. Do you have any idea why they wouldn’t show up??

  2. Pinal says:

    Thanks

  3. Bjorn says:

    Great walkthrough, very good details!

    I am looking for a good way manage the delete function. Either to disable it if I find a reason to for a given object, or to give feedback to the user that he/she cannot delete for some reason.

    I know that I could simply not do the deletion in my code if this is the case, but then the user would be none the wiser as to why the deletion did not happen as they expected.

    Hope for an enlightening answer! šŸ˜€

    Regards
    -Bjorn

  4. Excellent post. Keep posting such kind of info
    on your page. Im really impressed by it.
    Hey there, You have performed a fantastic job.
    I will certainly digg it and in my view recommend to my friends.
    I am confident they’ll be benefited from this web site.

  5. Asma says:

    Wait I don’t understand how to do this for a Web.Config without the connectionStrings tag

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s