Use OData with IBM DB2 and Informix
A step-by-step guide
OData (see the Related topics section for more information) is a specification for a Web API for data access to enable resources such as tables in databases to be accessed from Web browsers and mobile devices. OData specifies create, delete, update, delete (CRUD), and query over HTTP on resources (data or applications). It also specifies the way the results are formatted in ATOM (XML) and JSON. OData is like a mini-ODBC or JDBC for the Web. More precisely, OData allows clients to construct URIs that name an entity set, filter among the entities it contains, and traverse relationships to related entities and collections of entities.
Figure 1 shows how DB2 or Informix can be exposed on the Web through their ADO.Net enablement. Microsoft Visual Studio provides the tooling to enable the database data to be exposed via HTTP on the Web. The database data can be created, updated, deleted, and queried via the OData syntax from Web Browsers and other OData consumers (see the Related topics section for more information).
Figure 1. OData overview
Figure 2 shows CSDL (Conceptual Schema Definition Language), which is an XML notation that describes underlying resources using an entity relationship model that can be accessed through OData.
Figure 2. CSDL (Conceptual Schema Definition Language)
CSDL is often used at development time, for example in tools or model mappers. CSDL is optional and is generated by Visual Studio to help consumer applications understand the structure of the data being exposed. CSDL is like metadata in JDBC and ODBC, helping client applications understand what they are accessing.
Exposing tables in the sample database on the web using OData
The next sections describe in detail how you can do the following.
- Perform initial setup, such as defining the connection to the database.
- Create an ADO.Net entity model.
- Select the DB2 tables that will be exposed via OData.
- Create an OData service (WCF service) for the selected tables.
- Test the OData service.
The description uses the OData runtime that is incorporated into Microsoft Visual Studio. However, once the support has been tested in Visual Studio, other OData runtimes can be used. See the Related topics section for more information.
Creating a new web site
- Start Visual Studio. From the File menu, click New Web Site. The New Web Site dialog box is displayed.
- In the left pane under Installed Templates, select Visual C#.
- In the center pane, select ASP.NET Dynamic Entities Web Site.
- In the Web location box, select File System and then enter the name of the folder where you want to store the pages of the web site. For example, type the folder name C:\WebSites\DB2OData and click OK. Visual Studio creates the web site, as shown in Figure 3.
Figure 3. A new web site
Adding a data connection to Server Explorer
- From the Tools menu, select Connect to Database. The Add Connection dialog box is displayed.
- Click IBM DB2 and IDS Servers, as shown in Figure 4,
and then click Continue.
Figure 4. Adding data connection to Server Explorer
- As shown in Figure 5, in the Select or enter server name box, enter
127.0.0.1/localhost, or the hostname. In the User ID box, type
db2admin. In the Password box, enter your
password. Select the Save my password check box.
Figure 5. Connection information
- In the Select or enter a Database Name box, type a name for the database, such as SAMPLE.
- Click Test Connection, and then click OK.
- As shown in Figure 6, in Server Explorer, you can optionally expand
Data Connections and view the database tables.
Figure 6. Database tables
Adding data to the web site and creating a new ADO.NET entity data model
An entity data model is required to expose DB2 data using WCF data services. Perform the following steps to add data to the site and create the ADO.NET entity data model.
- In Solution Explorer, right-click the project and then click New Item. The Add New Item dialog box is displayed.
- In the left pane under Installed Templates, select Visual C#. In the center pane, select ADO.NET Entity Data Model.
- In the Name box, type a name for the database model.
For example, enter the name DB2.edmx, and then click
Add, as shown in Figure 7.
Figure 7. Create entity data model
- When prompted, click Yes.
- From the Entity Data Model Wizard, select Generate from
database, and then click Next, as shown
in Figure 8.
Figure 8. Entity data model wizard
The Choose Your Data Connection dialog box is displayed.
- In the drop-down list, select the connection that you configured
previously. For example, db2admin@SAMPLE. Click
Yes to include the sensitive data (user name and
password) in the connection string, and then click
Next, as shown in Figure 9.
Figure 9. Choose your data connection
The Choose Your Database Objects dialog box appears.
- Click the triangle to expand the Tables node. Select the check box for
DEPARTMENT and EMPLOYEE tables,
and then click Finish, as shown in Figure 10.
Figure 10. Choose your data objects
- The DB2.edmx page will appear with the new Entity Data Model, as shown
in Figure 11.
Figure 11. DB2.edmx page
Registering the data content
- As shown in Figures 12 and 13, from the Solution Explorer, open the Global.asax file.
- Uncomment the line that contains the DefaultModel.RegisterContext method.
- Set the context type to SAMPLEModel.SAMPLEEntities,
and set the variable ScaffoldAllTables to True.
Figure 12. Global.asax (DefaultModel.RegisterContext method - commented)
Figure 13. Global.asax (DefaultModel.RegisterContext method - uncommented)
- Save the Global.asax file.
Adding WCF data service
- From the Solution Explorer, right-click the project name DB2OData. The Add New Item dialog box is displayed.
- Under Installed Templates, in the left pane, select Visual C# and in the center pane, and then click WCF Data Service.
- In the Name box, enter a name for the data service, such as
WcfDataService.svc, and then click
Add, as shown in Figure 14.
Figure 14. Adding WCF Data Service
Configure the WCF data service
- As shown in Figure 15, in the WcfDataService.cs file, replace the code
comments /* TODO: put your data source class name here
*/ with SAMPLEModel.SAMPLEEntities.
Figure 15. Data source class name and MyEntitySet
- Uncomment the code containing the config.SetEntitySetAccessRule.
Replace MyEntitySet with an asterisk "*", as shown in Figure 16.
Figure 16. Change code for WcfDataService.cs
Testing the WCF data service
- To run the application, from the Debug menu, click Start Debugging. If prompted to enable debugging, click OK.
- In the web browser, enter a URI to return all of the records from a DB2 table through the data service.
- Enter http://localhost:15452/DB2OData/WcfDataService.svc/ to
view the Entities that have been included, as shown in Figure 17.
Figure 17. WcfDataService.svc
To see the text of this file, view the WcfDataService.svc.txt file in the download with this article.
to view the Department table, as shown in Figure 18.
Figure 18. Department table
(View a larger version of Figure 18.)See the download for the text of this figure.
- To search for Employee ID = 000010, paste
in the URL, as shown in Figure 19.
Figure 19. Employee table
(View a larger version of Figure 19.)See the download for the text of this figure.
- You can use Add-Ons (like Bamboo for Firefox) to view the formatted
XML data, as shown in Figures 20 and 21.
Figure 20. Formatted employee table
Figure 21. Formatted department Table
This article showed you how to expose DB2 data over HTTP via OData using Microsoft Visual Studio, making it possible to access DB2 from mobile devices and Web browsers. OData libraries exist for a number of mobile devices (see the Related topics section for more information). Similar OData support is available for Informix and DB2 on z/OS.
Currently, temporal and XML data are not supported through Visual Studio.
- Learn more about DB2 and .Net.
- Visit the Data Developer Center to learn more about OData.
- Learn more about Susan Malaika and her publications by visiting her blog.
- Read the "OData" developerWorks article to learn more about OData.
- Learn more about OData Specifications.
- Learn more about OData Consumers.
- Learn more about Deploying WCF Services.
- Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.