Bilbro Bloggins

This is the second 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.

For background information on this blog, I recommend reading the first part of this series if you have not done so already.  Part Two starts off with the BDC metadata file created in part one.  You will need that file to continue on.

Step 1 – Add a GenericInvoker method to perform inserts into the DimCurrency entity.

a. Open your BDC Editor with the AdventureWorks metadata definition file as you left it in part one.

b. Select the Add Method button with the DimCurrency method node selected.

c. Set the Name and DefaultDisplayName to Insert_DimCurrency.

d. Add the following INSERT statement to the RdbCommandType property.

INSERT INTO DimCurrency (CurrencyAlternateKey, CurrencyName) VALUES (@CurrencyAlternateKey, @CurrencyName)

Your Insert_DimCurrency method should look something like this:

Step 2 – Add the input parameters for CurrencyAlternateKey and CurrencyName.

a. On the Insert_DimCurrency node, select the Add Parameter button.

b. Select In for the Parameter Type dialog and Ok button.

c. Set the Name and DefaultDisplayName to @CurrencyAlternateKey.

d. On the @CurrencyAlternateKey node select Create Root TypeDescriptor button.

e. Set the Name and DefaultDisplayName to CurrencyAlternateKey.

f. On the Insert_DimCurrency node, select the Add Parameter button.

g. Select In for the Parameter Type dialog and Ok button.

h. Set the Name and DefaultDisplayName to @CurrencyName.

i. On the @CurrencyName node select Create Root TypeDescriptor button.

j. Set the Name and DefaultDisplayName to CurrencyName.

This is about as far as the BDC Editor can take us.  One of the quirks of the BDC is that a return parameter is required for a method instance.   So, we are going to step out of the BDC Editor and use a text editor to add our final XML to the metadata definition file.

Step 3 – Add the Dummy return parameter and the method instance.

a.  Export the metadata file.   As before, I’m going to give it a new version on the filename.

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

c. Locate the Insert_DimCurrency method node.  It should look like this:

<Method Name="Insert_DimCurrency">
  <Properties>
    <Property Name="RdbCommandText" Type="System.String">INSERT INTO DimCurrency (CurrencyAlternateKey, CurrencyName) VALUES (@CurrencyAlternateKey, @CurrencyName)</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="In" Name="@CurrencyAlternateKey">
      <TypeDescriptor TypeName="System.String" Name="CurrencyAlternateKey" />
    </Parameter>
    <Parameter Direction="In" Name="@CurrencyName">
      <TypeDescriptor TypeName="System.String" Name="CurrencyName" />
    </Parameter>
  </Parameters>
</Method>

d. Add the following dummy return <parameter> in the <Parameters> node.

<Parameter Direction="Return" Name="DimCurrency_DummyReturnParam">
  <TypeDescriptor TypeName="System.Int32" Name="DimCurrency_DummyReturnParam" />
</Parameter>

e. Add the following GenericInvoker method instance below the <Parameters> node.

<MethodInstances>
  <MethodInstance Type="GenericInvoker" 
                  ReturnParameterName="DimCurrency_DummyReturnParam" 
                  ReturnTypeDescriptorName="DimCurrency_DummyReturnParam" 
                  ReturnTypeDescriptorLevel="0" 
                   Name="Insert_DimCurrency_Instance" />
</MethodInstances>

Your final Insert_DimCurrency method node should look like this:

<Method Name="Insert_DimCurrency">
  <Properties>
    <Property Name="RdbCommandText" Type="System.String">INSERT INTO DimCurrency (CurrencyAlternateKey, CurrencyName) VALUES (@CurrencyAlternateKey, @CurrencyName)</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="In" Name="@CurrencyAlternateKey">
      <TypeDescriptor TypeName="System.String" Name="CurrencyAlternateKey" />
    </Parameter>
    <Parameter Direction="In" Name="@CurrencyName">
      <TypeDescriptor TypeName="System.String" Name="CurrencyName" />
    </Parameter>
    <Parameter Direction="Return" Name="DimCurrency_DummyReturnParam">
      <TypeDescriptor TypeName="System.Int32" Name="DimCurrency_DummyReturnParam" />
    </Parameter>
  </Parameters>
  <MethodInstances>
    <MethodInstance Type="GenericInvoker"
                    ReturnParameterName="DimCurrency_DummyReturnParam"
                    ReturnTypeDescriptorName="DimCurrency_DummyReturnParam"
                    ReturnTypeDescriptorLevel="0"
                     Name="Insert_DimCurrency_Instance" />
  </MethodInstances>
</Method>

Step 4 – Update the version of the BDC metadata file.  Locate the <LobSystem> node at the top of the file.  Change the version attribute to 1.0.2.0.

Version="1.0.2.0"

Step 5 – Verify your file in BDC Editor.  Import your file into the BDC Editor.

If successful, it should look something like this:

Step 6 – Update the metadata definition file in SharePoint.

a. Export your metadata definition.

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 7 – Use the BDC API to call your GenericInvoker method to insert data into the DimCurrency table.

a. Open Visual Studio and create a new Console Application project.

b.  In the main method add some variables that will be used to access the Insert GenericInvoker method.

string sspName = "SharedServices1";
string lobSystemName = "AdventureWorksDW";
string lobSystemInstanceName = "AdventureWorksDWInstance";
string entityName = "DimCurrency";
string methodInstanceName = "Insert_DimCurrency_Instance";

• sspName – The name of your Shared Service provider
• lobSystemName – The LOB System name of the Adventure Works BDC definition.  If you have been following along step-by-step with this blog this value specified above should be correct.
• lobSystemInstanceName – The LOB System Instance name of the Adventure Works BDC definition.  If you have been following along step-by-step with this blog this value specified above should be correct.
• entityName – The Entity Name of the Currency table.  If you have been following along step-by-step with this blog this value specified above should be correct.
• methodInstanceName– The instance name of the GenericInvoker for the Insert method.  If you have been following along step-by-step with this blog this value specified above should be correct.

b.  Next, we’ll add some code to locate the AdventureWorks BDC instance.

// Connect to the BDC Metadata Instance
SqlSessionProvider.Instance().SetSharedResourceProviderToUse(sspName);
NamedLobSystemDictionary lobSystems = ApplicationRegistry.GetLobSystems();
LobSystem lobSystem = lobSystems[lobSystemName];
NamedLobSystemInstanceDictionary lobInstances = lobSystem.GetLobSystemInstances();
LobSystemInstance lobInstance = lobInstances[lobSystemInstanceName];

c. Find the specific method instance we are looking for:

// Locate the method desired
Entity entityObject = lobInstance.GetEntities()[entityName];
NamedMethodInstanceDictionary methodInstances = entityObject.GetMethodInstances();
MethodInstance methInstance = methodInstances[methodInstanceName];

d. Get the parameters of the method instance:

// Get the parameters to the method
Object[] methodArgs = methInstance.GetMethod().CreateDefaultParameterInstances(methInstance);

e.  Pass in the parameters values.  In this case, I’ve hard-coded the Serbian Dinar but in the more realistic scenario you would probably be reading the input values from a “New Currency” form.

// RSD, Serbian Dinar
methodArgs[0] = "RSD";
methodArgs[1] = "Serbian Dinar";

f. And finally, execute the method.

// Execute the method
entityObject.Execute(methInstance, lobInstance, ref methodArgs);
Console.WriteLine(String.Format("Method {0} Executed",methodInstanceName));

g. To complete this sample, we’ll need to add a couple of project references

• Microsoft.Office.Server - C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\Microsoft.Office.Server.dll
• microsoft.sharepoint.portal - C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\microsoft.sharepoint.portal.dll

Your paths may vary but those are the specific DLLs you will need to reference.

and then add the following to your using statements:

using Microsoft.Office.Server.ApplicationRegistry.MetadataModel;
using Microsoft.Office.Server.ApplicationRegistry.Infrastructure;
using Microsoft.Office.Server.ApplicationRegistry.Runtime;
using Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db;

Step 8 – Compile and run this console app on your SharePoint server where you loaded the BDC metadata definition file.

(note: if you run this application more than once, you’ll get a SQL insert constraint error on the CurrencyAlternateKey.  The CurrencyAlternativeKey doesn't allow duplicate entries.)

Step 9 – If you view the BDC Data List web part you added in part one, you should see the newly added Serbian Dinar.

This is just a simple example of writing data back through the BDC.   The same techniques could be applied to UPDATE and DELETE statements.    However, it would probably be best to utilize stored procedures that has return values (success/fail results or for INSERTs, the newly added key).   Looking at the big picture you could write complete CRUD pages against your BDC data store following these guidelines:

• Display a list of results from the BDC (as the BDC web part does today).
• Add three custom action to have a selected item go your your custom edit form, delete form or new form.
• The edit form brings up the item in a form edit mode.
• The new form brings up an empty form.
• The delete form is probably just a confirm dialog.
• Use the GenericInvoker to perform UPDATE (edit) and INSERT (new) and DELETE statements (or Stored Procedure calls). 

It’s possible in the future I’ll blog with a specific example of the above process.

The console application and completed BDC metadata definition file can be downloaded from Part Three of this blog series.