Using Business Data Catalog Definition Editor
Category: SharePoint
Level: Beginner
Business Data Catalog is a Microsoft Office SharePoint Server feature that allows users to access external data from within SharePoint. SharePoint, as we know, is an excellent collaboration portal that can be used to create robust collaboration applications using Office products. Good thing about the new SharePoint portal is that it also allows you to create applications that involve non-MS products and allows you to create these applications as easily as one would create applications involving MS products. With BDC (Business Data Catalog) users can access data from applications like SAP, Oracle, MySQL, etc with ease. People who have been using SharePoint Server 2007 may also have worked with BDC before. BDC is not new but Business Data Catalog Definition Editor tool is a new edition. If you are entirely new and have never heard about the BDC before, then go through the following article, it will give you an overview of what BDC is and what it does.
Business Data Catalog Overview
In this article, I will show you how you can use the new Business Data Catalog Definition Editor to create definition files that are required to connect SharePoint with external applications, also known as Line Of Business (LOB) applications. BDC definition editor tool is available in the latest SharePoint SDK that you can download from the following location:
Download SharePoint SDK
After installing the SDK on your machine, you can install the BDC tool by running the setup.exe from the following location:
LocalDrive:\Program Files\2007 Office System Developer Resources\Tools\BDC Definition Editor
Once installed, you can use the tool to create the definition files. This tool allows you to use the following connection types:
1. SqlServer
2. Oracle
3. Oledb
4. Odbc
Because this is an introductory level article, we will use an Access database to create a definition file and using that file, we will display Access data in a SharePoint list (This will be demonstrated in the second part of this article). By the way, working with Access database is more tricky than working with the SQL database. Importing an SQL database table in to the Business Data Catalog Definition Editor is easy and straight forward but importing a table from an Access database requires some extra steps.
1. Create a DSN for your Access database. Go to Start > All Programs > Administrative Tools and select "Data Sources (ODBC)".
Figure 1: Add DSN
2. Select "System DSN" tab and click "Add" button.
Figure 2: Select Access driver from the list
3. Select "Microsoft Access Driver [*.mdb]" from the list of drivers. Remember, this is the driver for the Access 2003 database. If you want to connect to a Access 2007 database, then select "Microsoft Access Driver [*.mdb, *.accdb]" from the list. Click "Finish".
4. Enter "Data Source Name" and "Description" and click "Select" to select the database. Select your database and click "OK".
5. Now your DSN is ready. Open "Microsoft Business Data Catalog Definition Editor" from the Programs Menu.
Figure 3: Microsoft Business Data Catalog Definition Editor
6. Click "Add LOB System".
7. Click "Connect to Database".
8. Select "Odbc" from the "Connection Type" drop down.
Figure 4: Select a connection type
9. In the "Connection String" box, write "DSN=YourDSNname" and click "Connect".
10. Click "Add Table" button from the right side bar.
11. All the tables in your database will be displayed. Select the table from which you want to get the data. Drag and drop that table into the main design area.
Figure 5: Add Table
12. Click "OK". Enter a name for the LOB System and click "OK".
BDC Definition Editor will create an LOB System for you. If there was no primary key defined in the table that you used, then the editor will generate an LOB system but that will be of no use to you because the method required to get data from the database will not be created and you will have to create it manually but then what's the point in using the Definition Editor tool if you'd to create the method yourself. Definition file is nothing but an XML file that you can also create manually using any editor including Notepad. This is a complicated file and takes time to create if created manually. The Definition Editor tool gives you an advantage that you can execute the method and check if everything is working OK before you export the settings to an XML file. So to save the time and the effort, I would suggest that you create a primary key in the table but I know, in real world situation, that might not be possible for you to do as you might be working on a client's application. Of course, changing the client's database without his permission could be a problem or he may not allow you to do so. In that case, you will be left with two options. Either to edit the definition file manually and add the required settings or continue using the Definition Editor tool to add the missing methods. Well, let's complete this example first and then I will show you how to add the missing methods using the Definition Editor tool. For now, I am assuming that your table contained the primary key.
Some people argue that BDC Meta Man is a much better tool than the Microsoft Business Data Catalog Definition Editor and I am one of those people. Microsoft's tool is new and has issues. Right now, BDC Meta Man offers some advantages over the Microsoft tool and one of those advantages is that it adds the required methods even if there is no primary key in the table unlike the Microsoft tool that does not give you any warning or error but also does not add the methods that are required in the LOB System instance. "SpecificFinder" method is a method that is required for several reasons. One of the reasons is it is required if you want to use a field from the BDC as a "Business Data" column in your SharePoint library. Without the primary key in the table, this method is also skipped alongwith the other methods. BDC Meta Man allows you to add this method even if there is no primary key. It asks the users if they want to select the identifiers and it then shows them the fields to select an identifier from. This is a great feature.
Figure 6: LOB System - Parameters and Methods
Definition Editor will generate two methods for you. One method will return records against a value entered in the input parameter. If you look at the Figure 6 above, you will see a method "Find_Table1" where "Table1" is the name of the table. This method has two types of parameters. @ID is the input parameter and @Table1 is the output parameter. This output parameter consists of a "DataRecord" (Type: System.Data.IDataReader). Editor will also generate an instance that you can run and test the output. There will be another method created by the Editor, Figure 6 also shows "FindAll_Table1" method. This method returns all the records and does not take an input parameter. A separate instance is created for this method. Go ahead and test any of the instances created by the Editor. For example, let's test the "Find_Table1_Instance". Right click the instance name and select "Execute". This will open a new interface, you will be required to enter a value for the identifier "ID". Enter a value that you know exists in the database and click the "Execute" button. If that ID existed in the database, a relevant record will be returned. Go ahead and click the "Execute" button. What happened? Did it return the record? No? Instead you get an error message. See Figure 7 below.
Figure 7: Error connecting to Access Database
The error is: "The LobSystemInstance Property 'AuthenticationMode' has a value that requires 'Trusted_Connection' in the connection string"
This is because the application fails to connect with the Access database. It seems to be a bug in the Definition Editor. The solution is that one has to edit the definition file manually and make some changes. We can make those changes through the UI too but UI does not retain the changes so we have to manually edit the definition file. So first thing you got to do is export the settings to a definition file. Click the main node (LOB System) in the tree and then click "Export" from the menu. Provide a name for the definition file and save it. Open the saved file in any XML compliant editor. You can even open it in Notepad. Locate the following line:
<Property Name="rdbconnection Dsn" Type="System.String">YOUR DSN NAME WILL BE HERE</Property>
Figure 8: Change the DSN property
Replace this line with the following line:
<Property Name="rdbconnection data source" Type="System.String">"Driver={Microsoft Access Driver
(*.mdb)};DSN=YOUR DSN NAME WILL BE HERE;Uid=;Pwd=;Trusted_Connection=True;"</Property>
Save the file and go back to the Definition Editor tool. Don't forget to change the DSN name before saving the file. Click "Import" to import the definition file back in the tool. Now, select the method instance and select "Execute" from the context menu. Enter an ID and click the "Execute" button. You will get the result this time. You can also test the other method, the one that does not take any input parameters, and see if it returned all the records.
The next step is to import the definition file in the SharePoint. That will be the next article. It will show you how to import the definition file into the SharePoint and how to display the BDC data in a SharePoint list.
Read Next Article: Importing BDC Data into SharePoint