Posts Tagged ‘BDC’

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

As many of you know, customizing a form for a SharePoint list isn’t very tricky to do – but when it comes to modifying the forms for an External List it becomes a bit more of a challenge. In this article I will walk you through how the BCS (Business Connectivity Services) model can enable you to modify the New- and Editforms by creating and utilizing your own Custom Field Controls and I will also talk about how you can change the behavior and rendering of your DisplayForm using jQuery.

Related articles about BCS that may be worthwhile:

Scenario

So let’s pose that our scenario is that we’ve got a a source of data coming in through BCS and is represented like it’s always represented out of the box with BCS. What we would like to do is to customize the New- and EditForms to allow custom logic and we would also like to change how the DispForm behaves.

In this sample we’ve got a very simplistic BCS model containing one entity with these properties:

  • Identifier (string)
  • Color (string)
  • Published (boolean)

    In SharePoint, our model is represented like this in the out of the box UI:

    Display Form New- and Edit Forms
    ScreenShot1530 image

    What we really want to achieve in this article is to manipulate the behavior of our Display- and New/Edit forms to look something like this:

    Display Form
    Published: Icon instead of Yes/No text.
    Color: Lit up with the selected color in the UI.
    New- and Edit Forms
    Color: Replaced with a custom field control
    image image

    As you can see in the last two images above, the result of our development is that we’ll be using a custom control (DropDown) instead of the standard TextBox for our Color-field, and we’ll change the way the Published-field looks in the Display Form. It should give you an idea of how you can customize and alter the behavior of your BCS External List Forms.

    So without further ado, let’s get started with modifying our forms!

    (I assume that you already have a BCS Model in place and will not iterate the steps for creating one here…)

    Modifying New/Edit forms: Custom Field Control to the rescue

    When it comes to the BCS Model, you should really always edit it through the XML and not through the Visual Studio UI. Start by right-clicking your BCS Model and selecting the "Open With…" alternative:
    image

    Then choose your favorite XML editor:
    image

    When you’re in this mode, you obviously have to be careful not to mistype or misspell anything as it can result in a broken BCS Model. Awesome :-)

    To cut it short, here’s an extract for the "Creating" Method, which references a Custom Field Control called ColorField:

                <Method Name="Create">
                  <Parameters>
                    <Parameter Name="returnEntity1" Direction="Return">
                      <TypeDescriptor Name="ReturnEntity1" TypeName="TOZIT.Samples.BCSExtensions.BdcModel1.Entity1, BdcModel1">
                        <TypeDescriptors>
                          <TypeDescriptor Name="Identifier1" IdentifierName="Identifier1" TypeName="System.String" />
                          <TypeDescriptor Name="Published" IsCollection="false" TypeName="System.Boolean" />
                          <TypeDescriptor Name="Color" TypeName="System.String">
                            <Properties>
                              <Property Name="SPCustomFieldType" Type="System.String">ColorField</Property>
                            </Properties>
                          </TypeDescriptor>
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </Parameter>
                    <Parameter Name="newEntity1" Direction="In">
                      <TypeDescriptor Name="NewEntity1" TypeName="TOZIT.Samples.BCSExtensions.BdcModel1.Entity1, BdcModel1">
                        <TypeDescriptors>
                          <TypeDescriptor Name="Identifier1" IdentifierName="Identifier1" TypeName="System.String" CreatorField="true" />
                          <TypeDescriptor Name="Published" IsCollection="false" TypeName="System.Boolean" CreatorField="true" />
                          <TypeDescriptor Name="Color" TypeName="System.String" CreatorField="true">
                            <Properties>
                              <Property Name="SPCustomFieldType" Type="System.String">ColorField</Property>
                            </Properties>
                          </TypeDescriptor>
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </Parameter>
                  </Parameters>
                  <MethodInstances>
                    <MethodInstance Name="Create" Type="Creator" ReturnParameterName="returnEntity1" ReturnTypeDescriptorPath="ReturnEntity1" />
                  </MethodInstances>
                </Method>

    As you can see above I’ve referenced a property called "SPCustomFieldType" in the code (Read more on BCS Custom Properties), which in turn is referencing something called "ColorField". The ColorField is my CustomField control that is simply a DropDown-box to be represented in the UI instead of the good’ol text box.

    Modifying Display Forms: jQuery to the rescue

    When it comes to modifying the DispForm of the External List, I’ve had several attempts with the documented RendererDefinition in the past and not one single time it worked for me nor any of my clients. So with that experience, I’ve resorted to using jQuery to modify the rendition of the Display Form. Obviously one could use jQuery for the New- and Edit Forms as well, if you want to.

    Short story, the jQuery looks like this:

    // Found this through google, but can't remember the source. 
    // Great script for string replacements!
    String.prototype.replaceAll = function (str1, str2, ignore) {
        return this.replace(new RegExp(str1.replace(/([\/\,\!\\\^\$\{\}\[\]\(\)\.\*\+\?\|\<\>\-\&])/g, "\\$&"), (ignore ? "gi" : "g")), (typeof (str2) == "string") ? str2.replace(/\$/g, "$$$$") : str2);
    };
    
    $(document).ready(function () 
    {
        // Render an icon instead of Yes/No text
        var publishedHtml = $('h3.ms-standardheader:contains("Published")').closest('tr').children(".ms-formbody").html().toString();
        publishedHtml = publishedHtml.replaceAll("Yes", "<img src='/_layouts/images/TOZIT.Samples.BCSExtensions/checked.png' />");
        publishedHtml = publishedHtml.replaceAll("No", "<img src='/_layouts/images/TOZIT.Samples.BCSExtensions/unchecked.png' />");
        $('h3.ms-standardheader:contains("Published")').closest('tr').children(".ms-formbody").html(publishedHtml);
    
        // Render the entire TD in the color that was chosen
        var colorHtml = $('h3.ms-standardheader:contains("Color")').closest('tr').children(".ms-formbody").html().toString();
        var colorHtmlTd = $('h3.ms-standardheader:contains("Color")').closest('tr').children(".ms-formbody").attr("style", "background-color:" + colorHtml + ";");
    });

    Essentially what this jQuery snippet does is that it will find the correct elements in the HTML markup that corresponds to my Fields (you see the :contains("Published") part? That’s where I find the Published-field in the markup). Then I simply alter the text that is sent to us from the server, replacing "Yes/No" with an image of a checked or unchecked checkbox. Pretty simple trick to light up the form a bit without the hassle of trying to get your RendererDefinition working…

    Tip: If you want to easily inject your jQuery to the External List Forms, I’d recommend using a DelegateControl override on the AdditionalPageHead. The code for my AdditionalPageHead DelegateControl looks something like this:

            protected void Page_Load(object sender, EventArgs e)
            {
                string httpRequestUrlString = HttpContext.Current.Request.Url.AbsoluteUri;
    
                // Note: You should create a smarter verification for when to load the scripts than to use a hardcoded value like I've done in this sample. 
                if ((httpRequestUrlString.Contains("/Lists/Sample Entity/") || httpRequestUrlString.Contains("/Lists/Sample%20Entity/")) && httpRequestUrlString.Contains("DispForm.aspx")) // Only render scripts if it's the Display Form on the Sample Entity list
                {
                    // Add a reference to jQuery if it isn't already loaded
                    ScriptLink.Register(this.Page, "/_layouts/TOZIT.Samples.BCSExtensions/Scripts/jquery-1.7.2.min.js", false);
                    ScriptLink.Register(this.Page, "/_layouts/TOZIT.Samples.BCSExtensions/Scripts/bcsDisplayFormScriptSample.js", false);
                }
            }


    The code snippet above simply checks the current request and determines whether it’s your specific list being loaded, and then also determine if it’s the DispForm.aspx file being served. As noted in the comments, you should modify this if-statement to suit your needs, should you decide to create a DelegateControl like this.

    Summary

    Working with BCS is both fun and challenging at the same time. Like most SharePoint projects your requirements always change, and there’s always a need for further investigating the possibilities for how we can deliver quality solutions. In this article I’ve talked about some tips for how you can modify the NewForm and EditForm of your BCS External List by simply using a Custom Field Control. I’ve also talked about how you can deliver customized forms using jQuery as in the DispForm sample above.

    As a final note I’d like to shout out to Scot Hillier who is a fellow SharePoint MVP and author of the Professional Business Connectivity Services in SharePoint 2010 book for valuable tips and awesome discussions regarding this topic.

  • Author: Tobias Zimmergren
    URL: http://www.zimmergren.net | http://www.tozit.com 

    Introduction

    In this article I will guide you through the very basics of getting started with Business Data Catalog, BDC:

    1. Install the AdventureWorks 2008 Sample Databases
      1. We will use this database as our example for retrieving data using the BDC.
    2. I will step you through the simple process of creating your ADF (Application Definition File)
      1. We will use this file as our import connection
    3. I will guide you through how we can import this ADF file and create our BDC Application
    4. Lastly, I will guide you through the process of creating a basic site and use some of the basic BDC Web Parts

    Install the AdventureWorks sample database

    • You’ll need to go and download the AdventureWorks sample databases
      image
    • Just finish the installation by clicking next a couple of times and let the installer do it’s normal Microsoft-installer magic.
    • You should now see a couple of new databases in your SQL Server Management Studio:
      image
      AdventureWorks, AdventureWorks2008, AdventureWorksDW, AdventureWorksDW2008, AdventureWorksLT, AdventureWorksLT2008

    Alright – We’ve got our databases, now we need to start thinking about how we will get data from our SQL server into SharePoint. This is done by creating/generating an Application Definition File (ADF) as you will see in the next section.

    Creating the ADF (Application Definition File)

    Alright, there’s a few different options to create your ADF (Application Definition File). I will show you how to get started with using the free tool called "Application Definition Editor" that comes with the latest SharePoint Server SDK.

    Note: See the bottom section in this article for a summary of links to all resources mentioned in the article.

    After you have installed the latest SDK, you can choose to install the "Microsoft BDCTool" located here by default: "C:Program Files2007 Office System Developer ResourcesToolsBDC Definition Editor". Which will give you the following item in your Start Menu:
    image

    Launch the BDC Application Definition Designer

    Click the application and launch the editor. You will see an interface like this:
    image

    Create or import your ADF file

    There’s basically two alternatives when it comes to editing an ADF (Application Definition File).
    One is to create a new one (which I will guide you through first), and the other is to import an existing one (which I will show you after the first alternative).

    Alt 1) Creating our own ADF file
    Now we’re going to connect to our newly created sample-databases and create an ADF file for use with those databases.
    • Click on ADD LOB System
    • Choose Connect To Database
    • You will see a nice popup-dialog where you will be able to enter the connection details to your desired database
      • Enter your connection details, example:
         image
    • You are presented with the "Designer Surface" that looks something like this:
      image
    • In our case, we’re going to use the table called "vEmployee" which exist in the AdventureWorks database in order to pull out some information about our employees.
      • Search for the table called vEmployee and drag it out to the Design Surface
      • Search for the table called vEmployeeDepartments and drag it out to the Design Surface
      • It should look something like this:
        image
      • Make any necessary changes, then click OK
      • You’ll see a view similar to this one after some tweaking:
        image 
      • After you’ve done the necessary changes to your configuration, making sure it’s a valid ADF with proper filters, enumerators and methods or whatever you need in your application then smile, because we’re done with that part!
    Alt 2) Importing an existing ADF file

    If you don’t want to do everything from scratch or you’ve already got an ADF file that you wish to modify, you can do so by importing an existing ADF file into the Definition Editor. Here’s how:

    • Open the BDC Definition Editor tool, then click the "Import" button in the menu:
      image
    • Browse to your existing ADF file and choose to import it. Simple as that.
      (I am importing a file called BDCAWDW.xml, which contains definitions for Product, Reseller, ProductSubcategory, ProductCategory as shown below)
    • You’ll see the imported ADF file’s structure immediately in the designer, under the prerequisite that your SQL connection string in the ADF file is valid:
      image 

    Note: I will not detail how you create filters, finders, methods etc. in this article. You can read more about that here:
    http://msdn.microsoft.com/en-us/library/ms145931(SQL.90).aspx

    I may cover the topic of ADF-functionality in another article later on.

    Generate the ADF file from the designer

    I really don’t need to tell you this, but there’s a button called "Export" which you use to export the definition you’ve created using the definition editor to an xml file:
    image

    Import the ADF file

    If we have gotten this far, we might as well get the last few bits in place.
    What we now need to do is to import our ADF file into SharePoint, since that’s where it should reside. Follow along with these few simple steps to make sure you’re properly importing your file into SharePoint.

    • Navigate to your Shared Services Provider Administration site (You can access your SSP through Central Administration)
    • You are presented with a section called "Business Data Catalog" where you’ll find a bunch of different alternatives.
    • Make sure you have the permissions to modify the BDC (See the link Business Data Catalog permissions)
    • Click "Import application definition"
      image  
    • Browse for your .xml file and click "OK":
      image
    • You’ll see a progress bar (You don’t see that a lot in SharePoint. I love it!), telling your how the import process is going:
      image
    • When it’s done, you’ll click "OK" and be presented with an overview of your imported BDC Application:
      image
    Configure permissions on the BDC Application Definition

    In order for all users to be able to select/read data from your BDC Application, you’ll need to make sure they’ve got the appropriate permissions to actually do so.

    Usually I do this setting on each of the imported entities, in case you want specific permissions on different entities – instead of on the entire application.

    • Select the DropDown list on your first entity and choose "Manage Permissions":
      image 
    • Choose "Add Users/Group":
      image
    • Enter "NT AUTHORITYAUTHENTICATED USERS" and choose "Select in Clients":
      image
    • Repeat these steps for the other entity as well.
    • You’re done.

    Now we have created or imported an ADF file with the Business Data Catalog Definition Editor tool, exported it to an .xml file, imported it into SharePoint, set basic permissions on the entities.

    Next, we should make sure that the application works in SharePoint by adding a Business Data Catalog-WebPart to a page.

    Use the basic built-in BDC Web Parts

    Awesome. Now that we have gotten this far by importing an ADF file into SharePoint and set appropriate permissions on the entities – We’re ready to actually use the ADF connection to view stuff in our database.

    Note: I have created a new blank site where I can easily show the built-in BDC Web Parts – so that’s where I am adding my Web Parts.

     

    • Add two Web Parts to your page called "Business Data List" and "Business Data Item":
      (Note that when you’ve configured a BDC application, you’ll see the Business Data web parts)
      image
    • Choose to edit the properties of the Business Data List Web Part:
      image
    • Click the Browse-icon to the right to pop up the BDC entity chooser:
      image
    • It will present you with the following interface (note, BDC applications will of course vary depending on what you have imported..):
      image 
    • Double click the "Employee" type, and then click "OK" in your Web Part property window.
    • Repeat this process for the "Business Data Item" Web Part, and select "Employee" in the BDC Type Picker as well.

    Now we’ve got one BDC List Web Part which will list all employees, and one BDC Item Web Part that will display details about the employee we select.

    In order for this to work we must connect the two Web Parts.

    • Edit menu of your Web Part -> Connections -> Send Selected item To -> Employee
      image

    Test our BDC Application out, and make sure it works!

    • Choose "LastName" then "contains" and enter "smith":
      image
    • Select one of the results by clicking the radiobutton to the left, and see that the result (details) about the Employee shows up in the connected Web Part:
      image 

    Resources and links

    Summary

    This article is a basic step-by-step guide to getting started with BDC in MOSS 2007. I’ve shown you every step from creating the databases required (in our case some sample databases) to creating the ADF file and to finally utilize the BDC connection from a site, using the BDC Web Parts.

    In an upcoming post I will talk about how you can create your own BDC Web Parts! Keep your eyes open!