Bilbro Bloggins

This is the first part of a three part blog series. 

1. Part One - Using the Microsoft Business Data Definition Editor to add a table and display the data in a Web Part.
2. Part Two - How to use the GenericInvoker to write data back through the BDC.
3. Part Three – Download of all project source files.

SharePoint's Business Data Catalog (BDC) has really peaked my interest.   Microsoft touts it as a way to expose external data in SharePoint.  The BDC does that and does it very well.  Architecturally, I'm looking to try and decide how and when we should apply the BDC in our implementation of SharePoint.  Through my research of the BDC, every now and then you run across a statement that goes "The BDC is read only".

I think some confusion arises because SharePoint, by default, only ships with Web Parts that read data from the BDC.  Although, the BDC is probably not the best technology to write back to database systems, you may find yourself in a situation where you are a SharePoint developer and the SharePoint Czars and/or DBAs won’t give you direct access to the database but will provide you with a BDC metadata definition for the interactions you need.

If you breakdown the BDC for what it is, you find out it can do more than just read data.   It can also send inserts, updates, call stored procedures and make web service requests that all have the affect of writing back to the data store.  The BDC is purely a meta definition layer to an underlying data repository.   So, as long as the underlining data repository supports updates, you can write back through the BDC.

In the above diagram, when you interact with SQL Server or Oracle, you are really using ADO.NET to access those databases.   And we know you can write data with ADO.NET.   The BDC is purely a definition layer that describes how you interact with the external source.  The BDC doesn’t excess the external source directly.  All it does is hand off the communication to the underling technology designed to interact with that data store.

One of the advantages of the BDC is you can expose external data stores in SharePoint in a common way.   Once you develop a web part that communicates with the BDC, you could switch the underling data technology and you might *not* have to change your web part (if the definition of the data store itself changes then you probably will need to make changes to your web part). 

In this blog, I’ll show you a quick example of how you can write back data using just the BDC.  To keep it simple, will start with the Adventure Works BDC samples that Microsoft provides.

We’ll update the BDC definition of Adventure Works database to include the Currency table.  This sample will add the Serbian Dinar to the currency table going through the BDC.

To get up-to-speed on the BDC and the Adventure Works database, this MSDN article is a good starting point:

http://msdn.microsoft.com/en-us/library/bb736296.aspx

We’ll also use the Microsoft Business Data Catalog Definition Editor.   This handy tool can be found in the Microsoft Office SharePoint Server 2007 SDK 1.2 download (released August 2007).

Step 1 – Open the Adventure Works BDC definition metadata xml file with the Business Data Catalog Definition Editor.  You can find the SQL Server 2005/2008 version of the metadata file here:

http://msdn.microsoft.com/en-us/library/ms494876.aspx

Step 2 – Set the connection information to your copy of the AdventureWorks database.

Step 3 – Add the Currency table.

Select Add Entity

Drag the DimCurrency table to the Design Surface and select OK.

Step 4 – Let’s go ahead and verify our BDC file with SharePoint.   Although, the BDC Editor has it’s own validation and testing, for the purpose of this article, I want to show you where we are with just adding the Currency table to the metadata file.

a. Export your metadata definition.  When I name the file, I like to add the version number of the BDC definition to the filename.

b. Open your Shared Services.  In the Business Data Catalog section, select Import application definition.

Browse to the file you just exported and then select Import.

Hopefully, everything went okay and you see the Application Definition Import Successful screen.

Step 5 – Add a BDC Web Editor to a page and see if you can read data from the DimCurrency Table.

a. Add the BDC Data List web part.

b. If you browse the BDC catalog by selecting the browse button in the Type property of the Web Part Property Editor, you’ll notice that the DimCurrency data type doesn’t show up.   That’s a bummer.

The reason is because when you add the DimCurrency table, the BDC Editor only adds the SpecificFinder and IdEnumerator method instance types.  To use the BDC Data List, your entity needs a Finder method instance type.  The Finder method returns multiple entity instances whereas the SpecificFinder only returns one instance.  So, it makes sense why the BDC Data List web part doesn’t show any Entities that do not have the Finder method.   I find it strange that the BDC Editor doesn’t add a Finder method by default though.  On a side note, Lightning Tool’s BDC Meta Man adds all three method instances by default.

http://www.lightningtools.com/bdc-meta-man/default.aspx

Step 6 – Add a Finder method instance to the DimCurrency entity.

a. Go back to the BDC Editor and select Add Method when you have the DimCurrency entity highlighted.

b. Set Name and DefaultDisplayName to GetAll_DimCurrency.  Set the RdbCommandText as the select statement from the table as follows:

Select CurrencyKey
,CurrencyAlternateKey
,CurrencyName
FROM DimCurrency
ORDER BY CurrencyKey Desc

For the sake of this blog, I set the ORDER BY to be CurrencyKey descending only for the purpose of rapidly seeing the newly inserted item we will do later on in the blog.

Step 7 – Add the return parameter to the GetAll_DimCurrency method.

From time-to-time, you find it useful to cut-n-paste existing xml data from one place or another into your metadata definition.   Now will be one of those such instances.  The return parameters for the GetAll_DimCurrency method will be exactly the same as the Find_Currency method.   The biggest difference between those two methods (beside one being the Finder and one being the SpecificFinder) is that the Find_Currency requires the CurrencyKey as a parameter and GetAll_DimCurrency doesn’t require any parameters.

a.  Export the metadata.  In my case, I’m going to give it a new version number in the filename.  This will be important because to re-import the file, the BDC Editor will only import if the version number is higher than what it currently has loaded.  Now, the filename itself doesn’t matter but when we edit the file we will manually change the version number so that we can import it (so, I like my filename to reflect the actual version).  As a side note, if you don’t like mucking with the versions, then you will need to delete the LobSystem before you import your updated file (I just don’t like the habit of deleting stuff).

b. Open the metadata file in your favorite text editor (notepad, Visual Studio, etc..).

c. Locate the Find_Currency method definition in your file.  It should look something like this:

<Method Name="Find_DimCurrency">
  <Properties>
    <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
    <Property Name="RdbCommandText" Type="System.String">Select "CurrencyKey","CurrencyAlternateKey","CurrencyName" from  DimCurrency where CurrencyKey=@CurrencyKey</Property>
  </Properties>
  <Parameters>
    <Parameter Direction="In" Name="@CurrencyKey">
      <TypeDescriptor TypeName="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IdentifierName="CurrencyKey" Name="CurrencyKey" />
    </Parameter>
    <Parameter Direction="Return" Name="@DimCurrency">
      <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="Reader">
        <TypeDescriptors>
          <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="Record">
            <TypeDescriptors>
              <TypeDescriptor TypeName="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IdentifierName="CurrencyKey" Name="CurrencyKey" />
              <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CurrencyAlternateKey" />
              <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CurrencyName" />
            </TypeDescriptors>
          </TypeDescriptor>
        </TypeDescriptors>
      </TypeDescriptor>
    </Parameter>
  </Parameters>
  <MethodInstances>
    <MethodInstance Type="SpecificFinder" ReturnParameterName="@DimCurrency" ReturnTypeDescriptorName="Reader" ReturnTypeDescriptorLevel="0" Name="SpecificFinder_DimCurrency_Instance" />
  </MethodInstances>
</Method>

d. Copy the entire <Parameters> node.

<Parameters>
  <Parameter Direction="In" Name="@CurrencyKey">
    <TypeDescriptor TypeName="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IdentifierName="CurrencyKey" Name="CurrencyKey" />
  </Parameter>
  <Parameter Direction="Return" Name="@DimCurrency">
    <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="Reader">
      <TypeDescriptors>
        <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="Record">
          <TypeDescriptors>
            <TypeDescriptor TypeName="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IdentifierName="CurrencyKey" Name="CurrencyKey" />
            <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CurrencyAlternateKey" />
            <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CurrencyName" />
          </TypeDescriptors>
        </TypeDescriptor>
      </TypeDescriptors>
    </TypeDescriptor>
  </Parameter>
</Parameters>

e. Locate the GetAll_DimCurrency method node.

f. Paste the <Parameters> node underneath the <Properties> node of your GetAll_DimCurrency method node.

g. Delete the @CurrencyKey <parameter> node.   The GetAll_DimCurrency doesn’t require any IN parameters and only needs the one RETURN parameter.

Your GetAll_DimCurrency method node should now look like this:

        <Method Name="GetAll_DimCurrency">
          <Properties>
            <Property Name="RdbCommandText" Type="System.String">Select CurrencyKey
,CurrencyAlternateKey
,CurrencyName
FROM DimCurrency
ORDER BY CurrencyKey Desc</Property>
            <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
          </Properties>
          <Parameters>
            <Parameter Direction="Return" Name="@DimCurrency">
              <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="Reader">
                <TypeDescriptors>
                  <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="Record">
                    <TypeDescriptors>
                      <TypeDescriptor TypeName="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IdentifierName="CurrencyKey" Name="CurrencyKey" />
                      <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CurrencyAlternateKey" />
                      <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CurrencyName" />
                    </TypeDescriptors>
                  </TypeDescriptor>
                </TypeDescriptors>
              </TypeDescriptor>
            </Parameter>
          </Parameters>
        </Method>
      </Methods>

h.  At the top of the xml file, locate the LobSystem node.  Update the version attribute to 1.0.1.0.

Version="1.0.1.0"

i. Go back to the BDC Editor and import your new file.  If you made no mistakes and updated the version, it should load fine.

Step 8 – Add the Finder method instance.

a. On the Instances node of the DimCurrency method select Add Method Instance.  Be sure to select the Finder radio option in the Method Instance Type section.  Select the Ok button.

b.  Set the Name and DefaultDisplayName of the newly added instance to Finder_DimCurrency_Instance.

Step 9 – Import the metadata definition file into SharePoint and modify the BDC Data List web part.

Because you already updated the version number of the metadata definition file, you can proceed with importing the latest version of the file.  If you didn’t increase the version number then you would get an import error from SharePoint and you would have to delete the existing import definition first.  Again, as a Best Practice, I recommended versioning your files.

Repeat Steps 4 and 5 above.  If you still have the BDC Data List web part on your page, then you can proceed to selecting the browse button of the Type property of the Web Part Property Editor.  This time now, you should see the DimCurrency entity.  Yeah!

Highlight DimCurrency and select the OK button on the Entity Browser popup and OK on the Web Part property editor.    If all went well, you should see the items from the DimCurrency table displayed.

This concludes the first part of this series.  Proceed to the second part to see how we can use the GenericInvoker to write data back to our DimCurrency entity.