MOSS BDC – Custom WebPart using 2 LOB Systems

By admin Uncategorised 8th July 2012

We are going to give you a walkthrough of how to write a custom Web Part to connect two different Line of Business (LOB) Systems. The complete solution is attached to this WIKI to download, you will need to create your own Application Definition Files (ADF) and import them into SharePoint, you can use the free Developer version of BDC Meta Man to create your ADF.

In the example we are going to be connecting an Employee Table which sits in an Oracle Database to a Department table from an SQL Server Database.

The Columns in each are:

Oracle | Employees

(PK) EMPLOYEE_ID
Decimal

FIRST_NAME
String

LAST_NAME
String

EMAIL
String

PHONE_NUMBER
String

HIRE_DATE
String

JOB_ID
String

SALARY
Decimal

COMMISSION_PCT
Decimal

MANAGER_ID
Decimal

DEPARTMENT_ID
Decimal

SQL | Departments

(PK )dept_id
Int

dept_description
String

Our association will be between ‘DEPARTMENT_ID’ and ‘dept_id’

We have created our 2 application definition files using BDC Meta Man and have imported them into our SharePoint server.

Our Oracle LOB System Instance is called OracleInstance and the SQL LOB System Instance is SQLInstance

We have also installed the Visual Studio Extensions for Windows SharePoint Services (or VSeWSS for short) available from Here

OK Lets Go…..

1. Open Visual Studio 2005

2. Create a new project called ‘OracleAndSQLBDCWebPart’
We are going to be using the Web Part Template
Visual C# > SharePoint > Web Part

3. This template sets up the class ready for you

4. The first thing we will do is to rename our class, web part and xml

5. You can start by just renaming the files from the solution explorer to:

6. Rename your class and constructor to DepartmentInfo

7. Double click on DepartmentInfo.webpart

8. Edit the contents to

<?xml version=”1.0″ encoding=”utf-8″?>
<webParts>
<webPart xmlns=”http://schemas.microsoft.com/WebPart/v3″>
<metaData>
<!–
The following Guid is used as a reference to the web part class,
and it will be automatically replaced with actual type name at deployment time.
–>
<type name=”[YOUR OWN GUID]” />
<importErrorMessage>
Cannot import the Department Information Web Part.
</importErrorMessage>
</metaData>
<data>
<properties>
<property name=”Title” type=”string”>
Department Information Web Part
</property>
<property name=”Description” type=”string”>
Department Information Web Part which retrieves Departments from SQL and Employees from Oracle
</property>
</properties>
</data>
</webPart>
</webParts>

9. Double click on DepartmentInfo.xml

10. Change the contents to:

<?xml version=”1.0″ encoding=”utf-8″?>
<Elements Id=”9139d32f-3598-4f77-b2d9-c68f0527aa2a” xmlns=”http://schemas.microsoft.com/sharepoint/” >
<Module Name=”WebParts” List=”113″ Url=”_catalogs/wp”>
<File Path=”DepartmentInfo.webpart” Url=”DepartmentInfo.webpart” Type=”GhostableInLibrary” />
</Module>
</Elements>

11. Finally we will add the following references

System.Data
Microsoft.SharePoint.Portal

and add the following to the using section:

using System.Data;
using Microsoft.Office.Server.ApplicationRegistry.MetadataModel;
using Microsoft.Office.Server.ApplicationRegistry.Runtime;
using Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db;

12. OK, that is the initial set up done.

13. Back to our web part class

14. Delete the commented lines (25 – 28)

15. Next we will declare our Web Parts Properties, add these 5 controls and 2 classes

//Classes to hold our business data
private DataTable dtDepartments;
private DataView dvEmployees;
//Controls to display our data
private Label lblDepartments;
private DropDownList ddlDepartments;
private SPGridView gvEmployees;
private Button btnRefresh;
private LiteralControl htmlBR;

16. We will add our fields now that hold our BDC LOB details, add this just above where you have added the controls

// Web Part strings for our LOB
private string OracleLOBSystemInstance = “OracleInstance”;
// The name of our Employee Table from our Oracle Database
private string OracleEntityName = “EMPLOYEES”;
private string SQLServerLOBSystemInstance = “SQLInstance”;
// The name of our Department Table from our SQL Server Database
private string SQLServerEntityName = “dbo.tblDepartment”;
//BDC Methods used to return Department Data
private static string SQLEntityMethodName = “Getdbo.[tblDepartment]”;
private static string SQLEntityMethodInstanceName = “dbo.[tblDepartment]Finder”;

17. We will now add our controls to the CreateChildControls() method

protected override void CreateChildControls()
{
base.CreateChildControls();
// Instructional label to make sure people choose a department
lblDepartments = new Label();
lblDepartments.Text = “Please choose a department: “;
//Instantiate our Drop down list for Departments
ddlDepartments = new DropDownList();
// Instantiate a button to refresh the employees based on the department selection
btnRefresh = new Button();
btnRefresh.Text = “Refresh”;
// Register our event handler to bring back employee data
btnRefresh.Click += new EventHandler(btnRefresh_Click);
//Instantiate our SPGridview to hold the employee data
//We need to set AutoGenerateColumns to false
gvEmployees = new SPGridView();
gvEmployees.AutoGenerateColumns = false;
// Instantiate a LiteralControl to help format our Web Part
htmlBR = new LiteralControl(“<br>”);
//Add our controls to the Web Parts Control collection
this.Controls.Add(lblDepartments);
this.Controls.Add(ddlDepartments);
this.Controls.Add(htmlBR);
this.Controls.Add(btnRefresh);
this.Controls.Add(gvEmployees);
//Calls our method to populate the drop down list
getDepartments();
}

18. Add a getEmployees() method call to our btnRefresh_Click method, click to generate Method Stub

19. Next add a getDepartments() method to the bottom of our CreateChildControls() method and create a Method Stub for that too.

20. Add the following method to your Web Part Class to retrieve our Departments DataTable from our SQL LOB system

public static DataTable GetLOBDepartments(string lobSystemInstance, string entityName)
{
DataTable dtDepartments = null;
NamedLobSystemInstanceDictionary sysInstances = ApplicationRegistry.GetLobSystemInstances();
LobSystemInstance instance = sysInstances[lobSystemInstance];
Entity entity = instance.GetEntities()[entityName];
Method method = entity.GetMethods()[SQLEntityMethodName];
MethodInstance methodInstance = method.GetMethodInstances()[SQLEntityMethodInstanceName];
DbEntityInstanceEnumerator Departments = (DbEntityInstanceEnumerator)entity.Execute(methodInstance, instance);
while (Departments.MoveNext())
{
DbEntityInstance department = (DbEntityInstance)Departments.Current;
if (dtDepartments == null)
dtDepartments = department.EntityAsDataTable;
else
department.EntityAsDataRow(dtDepartments);
}
return dtDepartments;
}

21. Next create a new method with the following to retrieve the filtered Employees DataTable from our Oracle LOB System

public static DataTable GetLOBEmployeesByDepartmentID(string lobSystemInstance, string entityName, string DeptID)
{
DataTable dtEmployees = null;
NamedLobSystemInstanceDictionary sysInstances = ApplicationRegistry.GetLobSystemInstances();
LobSystemInstance instance = sysInstances[lobSystemInstance];
Entity entity = instance.GetEntities()[entityName];
// Get the Filters which we have set up in our ADF
FilterCollection filterCollection = entity.GetFinderFilters();
// Set the DeptID as the value to filter by
(filterCollection[0] as ComparisonFilter).Value = Convert.ToDecimal(DeptID);
IEntityInstanceEnumerator IEntityEnumerator = entity.FindFiltered(filterCollection, instance);
while (IEntityEnumerator.MoveNext())
{
if (dtEmployees == null)
dtEmployees = IEntityEnumerator.Current.EntityAsDataTable;
else
IEntityEnumerator.Current.EntityAsDataRow(dtEmployees);
}
return dtEmployees;
}

22. OK, so now we have set up our two methods to return our LOB Data, now we need to set up our methods to be able to display this information in our Controls.

23. First we will bind our Departments to our Drop Down List

protected void getDepartments()
{
dtDepartments = GetLOBDepartments(SQLServerLOBSystemInstance, SQLServerEntityName);
ddlDepartments.DataSource = dtDepartments;
ddlDepartments.DataTextField = dtDepartments.Columns[“dept_description”].ColumnName;
ddlDepartments.DataValueField = dtDepartments.Columns[“dept_id”].ColumnName;
ddlDepartments.DataBind();
}

24. And now we will configure our getEmployees() method to display the Employees into our SPGridView

protected void getEmployees()
{
//Calls the GetLOBEmployeesByDepartmentID passing in the Oracle LOB System, Our Employee entity and the select departments ID
DataTable _BDCDT = GetLOBEmployeesByDepartmentID(OracleLOBSystemInstance, OracleEntityName, ddlDepartments.SelectedValue);
if (_BDCDT != null)
{
dvEmployees = new DataView(_BDCDT);
gvEmployees.Columns.Clear();
gvEmployees.DataSource = dvEmployees;
gvEmployees.AutoGenerateColumns = false;
SPBoundField _spBoundField = null;
foreach (DataColumn _bdcColumn in _BDCDT.Columns)
{
_spBoundField = new SPBoundField();
_spBoundField.HeaderText = _bdcColumn.ColumnName;
_spBoundField.DataField = _bdcColumn.ColumnName;
_spBoundField.SortExpression = _bdcColumn.ColumnName;
gvEmployees.Columns.Add(_spBoundField);
}
}
else
{
gvEmployees.DataSource = null;
}
gvEmployees.DataBind();
}

25. Click Build to make sure the project Builds

26. Click the Play button or press F5

27. As this is a SharePoint template it will build the solution for you and deploy it to your SharePoint Environment

28. Navigate to the SharePoint where you want to use this Web Part

29. Go to Site Actions > Site Settings > Site Collection Features*
* if you are not at the Root Site Collection you will need to click on ‘Go to top level site settings ‘ first

30. Click to Activate our DepartmentInfo feature