8. Virtual Database Engine

8.3. Using Microsoft Entity Frameworks to Access Progress Schema Objects with Virtuoso

This section details the steps required to provide Microsoft Entity Framework access to Progress Schema Objects using the OpenLink Virtuoso Universal Server. This is achieved by Linking the required Progress Schema objects into Virtuoso using its built in Virtual Database engine, and then using the Virtuoso ADO.Net Entity Framework provider to query the remote Progress Schema objects linked into the Virtuoso Server.

Prerequisites

Progress OpenEdge 10

A Progress DBMS hosting the required Schema Objects needs to be available. In this section, the isports sample database will be used to demonstrate the process.

OpenLink ODBC Driver for Progress (SQL-92)

A Progress ODBC Driver is required to link the Progress Schema Objects into the Virtuoso Server. The OpenLink ODBC Driver for Progress (SQL-92) will be used in this section, for which a functional ODBC Data source name of "prs101c" will be assumed to exist on the machine hosting the Virtuoso Server.

OpenLink Virtuoso 5.10.x

An OpenLink Virtuoso Universal Server installation including the Virtuoso Universal Server and ADO.NET Entity Framework Provider is required. The Virtuoso components must be Release 5.10.x or above, this being the minimum version containing support for Microsoft Entity Frameworks.

Microsoft Visual Studio 2008 + SP1

Microsoft Visual Studio 2008 with Service Pack 1 is required, this being the only version containing the necessary Entity Framework support available at the time of writing.

Tasks

Ensure Progress Primary Keys (PKs) are not nullable

The Visual Studio 2008 Entity Data Model (EDM) requires that all primary keys are NOT Nullable, and will fail to generate an EDM if any are nullable. Thus ensure any tables to be used are defined as not nullable in the Progress database schema before attempting to generate an EDM.

It seems that, by default, several Primary Keys (PKs) in the isports database allows <NULL> values. It seems somewhat nonsensical - that a unique key used, specifically, to identify a row in a table can be allowed to be <NULL>.

This issue is best addressed directly in the database schema, by redefining those PKs that allow <NULL> so as not to allow <NULL>. Progress does not seem to talk in terms of <NULL>, instead using the term "Mandatory"

The following section will provide more specific details about how to ensure Progress PKs are not nullable:

Ensuring Progress PKs are not nullable

8.3.1. Install and configure OpenLink ODBC Driver for Progress (SQL-92)

The Virtuoso Virtual Database engine uses ODBC as the connectivity mechanism for linking remote database objects into its local schema. Thus a Progress ODBC Driver must be available with a suitably configured DSN for connecting to the target database. The OpenLink ODBC Drivers for Progress have been used in this section, although in theory any Progress ODBC Driver can be used.

Installation instructions for the OpenLink ODBC Driver for Oracle are available from:

Product Installation & Basic Configuration (ODBC)

8.3.2. Install and configure OpenLink Virtuoso Universal Server

Install and configure OpenLink Virtuoso Universal Server

8.3.3. Linking Progress tables into Virtuoso

Start the Virtuoso Web User Interface

Figure: 8.3.3.1. Start
Select the "Conductor" link in the Left Frame and Login as the "dba" user.

Figure: 8.3.3.1. Conductor
Select the "Databases" -> "External Data Source" -> "Data Sources" tab menu items

Figure: 8.3.3.1. Databases
Select the "Connect" button for the "prs10ma" Oracle DSN.

Figure: 8.3.3.1. Connect
On successful connection Select the "Link Objects" button to obtain a list of available tables

Figure: 8.3.3.1. Link Objects
Select all the tables that are part of the "isports" catalog.

Figure: 8.3.3.1. Select all tables
Change the Catalog for each table to be "isports" using the "Set All" button.

Figure: 8.3.3.1. Catalog
All the catalog names are changed to be "isports".

Figure: 8.3.3.1. Catalog names
Select the "Link" button to link the selected tables into Virtuoso

Figure: 8.3.3.1. "Link" button
On completion of the Link process the tables will be displayed in the "External Linked Objects" tab.

Figure: 8.3.3.1. Completion
At this point you can test the remotely linked tables by clicking on the link that accompanies each table. e.g. isports.prs101c.Customer.

This will display the Interactive ISQL interface which will have been already populated with a suitable SQL statement.

Figure: 8.3.3.1. Querying
Select Execute to see data from the remotely linked table.

Figure: 8.3.3.1. Execute
The tables can also be viewed as part of the Virtuoso "SQL Schema Objects" under the "isports" catalog name.

Figure: 8.3.3.1. SQL Schema Objects

8.3.4. Creating EDM in Visual Studio 2008

The following steps can be used to create an Entity Data Model (EDM) for the Progress isports database:

Launch the Visual Studio 2008 SP1 IDE.

Figure: 8.3.4.1. Visual Studio 2008 SP1 IDE
Create a Web Application project by going to the File menu in Visual Studio and choosing New Project.

Figure: 8.3.4.1. Web Application
When the New Project window appears, choose either Visual Basic or Visual C# as your programming language.
Within the language category, click on Web and select ASP.NET Web Application from the right-hand panel.
Choose a name for the project, for example isports, and click OK.

Figure: 8.3.4.1. Name for the project
This will create a new project called isports.

Figure: 8.3.4.1. create a new project
Select the Project -> Add New Item menu option.

Figure: 8.3.4.1. New Item
The Add New Item dialog will appear. Choose the ADO.NET Entity Data Model template. Give it the name Model1.edmx and click Add to start the creation of the ADO.Net Entity Data Model.

Figure: 8.3.4.1. Add New Item
In the Entity Data Model Wizard dialog Choose Model Contents page select the Generate from Database model type and click Next.

Figure: 8.3.4.1. Choose Model Contents
In the Entity Data Model Wizard dialog Choose your Data Connection page, select the New Connection button

Figure: 8.3.4.1. Entity Data Model Wizard
In the Choose Data Source dialog, select the OpenLink Virtuoso Data Source from the list and click Continue.

Figure: 8.3.4.1. Choose Data Source
In the Add Connection dialog, specify the hostname, portno, username and password for the target Virtuoso Server and check the Save Password check box.

Figure: 8.3.4.1. Connection Properties
Select the Select Database From List radio button and choose the isports database from the drop down list.

Figure: 8.3.4.1. Add connection
Press the Test Connection dialog to verify that the database is accessible.

Figure: 8.3.4.1. Test Connection
Set the entity connect string name to isportsEntities (note this name as it is required in the section on creating and ADO.Net Data Service) and click Next.

Figure: 8.3.4.1. entity connect string
In the Choose your Database Objects page select the Tables check box to select all tables in the isports catalog for addition to the Entity Data Model. Set the Model Namespace to isportsModel and click Finish.

Figure: 8.3.4.1. Database Objects
The Model1.edmx EDM will be created with the tables and relationships displayed in the Visual Studio IDE.

Figure: 8.3.4.1. Virtuoso.edmx

Creation for the Entity Data Model for the Progress isports database is now complete.

8.3.5. Manually creating EDM Associations (FKs) for the Progress isports database

The following steps will detail what is required to manually create "Associations" in your Entity Data Model.

You will need to determine where these associations exist and their multiplicity (one-to-one, one-to-many, etc.) before commencing with the following steps.

Note:These steps will need to be repeated for each association.

The first Association will deal with is the relationship between Customer and Invoice, identified by the presence of the scalar property Cust_Num in both entities. This is a one-to-many relationship, as a Customer may have any number of Invoices.

Figure: 8.3.5.1. Association
To add the Association, right click on the Customer entity then Add -> Association.

Figure: 8.3.5.1. add the Association
You will now see the Add Association dialog.

Figure: 8.3.5.1. Add Association dialog
For this association the only thing that needs changing from the default, is the name of the Navigation Property from Invoice to invoices on the Customer end of the association.
This better reflects the multiplicity of the association such that a Customer is associated with zero or many Invoices (plural).

Figure: 8.3.5.1. Navigation Property name

Typically, here is where you will:
- Select the entity at each end of the association
- Select the multiplicity of each end of the association, and
- Provide suitable names for the association and its navigation properties
Once you then hit OK the diagram is refreshed to include the newly created association.

Figure: 8.3.5.1. diagram
You now need to edit the mappings associated with the newly created association, so right-click the association on the diagram, and select Table Mapping to display the Mapping Details pane.

Figure: 8.3.5.1. Table Mapping
Click that line reading <Add a Table or View> to reveal a drop down list of all entities.

Figure: 8.3.5.1. Add a Table or View
Here you need to select the entity on the right/far side of the association (the entity where the foreign key exists). In this example, it is the Invoice entity.

Figure: 8.3.5.1. Entity
The Mapping Details pane now refreshes to display both ends of the association, requiring that you provide relevant target store data types in the Column column for the key fields, as depicted here.

Figure: 8.3.5.1. Mapping Details

Figure: 8.3.5.2. Mapping Details

Figure: 8.3.5.3. Mapping Details
Once the mapping is complete, you can build the project using Build -> Build Solution. NOTE: It is worthwhile building as each association is made, since the error messages can be a little confusing.

Figure: 8.3.5.1. Build the project
This should result in the following error:

Figure: 8.3.5.1. Error

This error indicates that there are two source columns - in this case, the Invoice entity's Scalar Property Cust_Num and Navigation Property Customer, which are both mapped to the same target column - the Progress column Invoice.Cust_Num - and this is not supported.

Figure: 8.3.5.2. Error
The solution is simple! Simply delete the mapping of the Scalar Property Invoice.Cust_Num, since its only purpose is to hold data representing a relationship/association (it is a Foreign Key) which has already been represented by the newly created association and resulting Navigation Property Customer.
Right click on Invoice.Cust_Num then Delete.

Figure: 8.3.5.1. delete the mapping
The model diagram will refresh to reflect this change.

Figure: 8.3.5.1. Model Diagram
Build the project, again, using Build -> Build Solution.

Figure: 8.3.5.1. Build project
The project should now be fine.

Figure: 8.3.5.1. Build Project

You will need to repeat these steps for each association until you have a completed Entity Data Model.

Figure: 8.3.5.1. Entity Data Model

8.3.6. Using EDM to create Entity Framework based applications

Now that a Microsoft Entity Data Model has been created for the Progress isports database, Entity Framework applications can be created to make use of it.

8.3.6.1. Entity Framework Data Service

An ADO.Net Data Service for the Progress tables can be created using the Entity Data Model created in the Creating EDM in Visual Studio 2008 section.

Open the VirtuosoDataService project created in the Creating EDM in Visual Studio 2008 section.
Select the Project -> Add New Item menu option.

Figure: 8.3.6.1.1. VirtuosoDataService
The Add New Item dialog will appear. Choose the ADO.NET Data Service template. Give it the name WebDataService.svc, and click Add to create the ADO.Net Data Service.

Figure: 8.3.6.1.1. Add New Item

In the WebDataService1.svc.cs Data Service file created add the data source class name of isportsEntities (note this is the name set in the Creating EDM in Visual Studio 2008 section) as the DataService name and enable the access to the Data Service by adding the entry config.SetEntitySetAccessRule("*", EntitySetRights.All); in the InitializeService method.

// C#

using System;
using System.Web;
using System.Collections.Generic;
using System.ServiceModel.Web;
using System.Linq;
using System.Data.Services;

namespace SimpleDataService
{
    public class Northwind : DataService<VirtuosoDemoEntities>
    {
        public static void InitializeService(IDataServiceConfiguration  config)
        {
            config.SetEntitySetAccessRule("*", EntitySetRights.All);
        }
    }
}

Figure: 8.3.6.1.1. WebDataService1.svc.cs

To test the Data Service, simply hit Ctrl+F5 within Visual Studio. This will start the development web server, run the Data Services server inside and load a Web browser page displaying the list of available tables/entities for the isports database catalog.

Figure: 8.3.6.1.1. Data Service test
To access a specific entity instance like the EMPLOYEES table employee number 100 record, use this convention http://host/vdir/Virtuoso.svc/EMPLOYEES(100) .

Figure: 8.3.6.1.1. EMPLOYEES

Notes:

Important - To view Atom (the default format returned by an ADO.NET Data Service) in Internet Explorer, you must first ensure that Feed Reading View is turned off. This can be done on the Content tab of Tools in Internet Options.
If a Data Services entity instance URI page fails to load you can turn Verbose errors on by adding config.UseVerboseErrors = true; in the virtuoso.svc.cs InitializeService method to obtain more detailed information from the server as to why the page failed to load:
```
public static void InitializeService(IDataServiceConfiguration config)

{

config.UseVerboseErrors = true;

config.SetEntitySetAccessRule("*", EntitySetRights.All);

}
```

8.3.6.2. Visual Studio Windows DataGrid Form Application

This section details the steps required to create a simple Visual Studio 2008 Windows Form application, with associated DataGridView control for displaying data in selected tables from the target database.

Launch the Visual Studio 2008 SP1 IDE.

Figure: 8.3.6.2.1. Visual Studio 2008 SP1 IDE
Create a Web Application project by going to the File menu in Visual Studio and choosing New Project.

Figure: 8.3.6.2.1. Web Application
When the New Project window appears, choose either Visual Basic or Visual C# as your programming language.
Within the language category, click on Windows and select Windows Form Application from the right-hand panel.
Choose a name for the project, for example VirtWindowsFormApplication, and click OK.

Figure: 8.3.6.2.1. Web Application
In the Toolbox, expand Data Controls, and drag the DataGridView control onto the form.

Figure: 8.3.6.2.1. Toolbox
Click on the little arrow in the top right of the DataGridView control. This loads the DataGridView Task menu.

Figure: 8.3.6.2.1. DataGridView Task
Click on the Choose Data Source list box.

Figure: 8.3.6.2.1. Choose Data Source
Click on the Add Project Data Source link to connect to a data source.

Figure: 8.3.6.2.1. Add Project Data Source
In the Data Source Configuration Wizard dialog Choose Data Source Type page select the Database data source type and click Next.

Figure: 8.3.6.2.1. Data Source Type
In the Data Source Configuration Wizard dialog Choose your Data Connection page, select the New Connection button

Figure: 8.3.6.2.1. Data Source Configuration Wizard
In the Choose Data Source dialog, select the OpenLink Virtuoso Data Source from the list and click Continue.

Figure: 8.3.6.2.1. Data Source
In the Add Connection dialog, specify the hostname, portno, username and password for the target Virtuoso Server and check the Save Password check box.

Figure: 8.3.6.2.1. Connection Properties
Select the Select Database From List radio button and choose isports from the drop down list.

Figure: 8.3.6.2.1. Add conneciton
Press the Test Connection dialog to verify that the database is accessible.

Figure: 8.3.6.2.1. Test Connection
Leave the default connect string isportsConnectionString and click Next

Figure: 8.3.6.2.1. isportsConnectionString
From the list of available tables returned for the isports database, select the JOBS table to be associated with the DataGridView control.

Figure: 8.3.6.2.1. isports database
The columns names of the select table will be displayed in the DataGridView.

Figure: 8.3.6.2.1. DataGridView
Resize the Form and DataGridView to allow all columns to be visible, if possible.

Figure: 8.3.6.2.1. Resize the Form and DataGridView
To test the application, simply hit Ctrl+F5 within Visual Studio or select Start Debugging from the Debug menu.

Figure: 8.3.6.2.1. Start Debugging
The data from the JOBS table will be displayed in the DataGrid.

Figure: 8.3.6.2.1. DataGrid
To make the DataGridView updateable, you will need to manually add some code to the project along with a suitable control to invoke the code. Drag a Button control onto the form.

Figure: 8.3.6.2.1. DataGrid
Right click on the Button and select Properties.

Figure: 8.3.6.2.1. DataGrid
In the Properties view, edit the buttons Text property to read Save Changes and its (Name) property to read saveChanges.

Figure: 8.3.6.2.1. DataGrid
The button will now update to reflect these changes.
NOTE: You will need to resize the button to make the new text visible.

Figure: 8.3.6.2.1. DataGrid
Double click the new button to generate the required event handler. It should take you directly to the area of code that will execute when the button is clicked.

Figure: 8.3.6.2.1. DataGrid

Edit the saveChanges_Click event handler code to include the following line.

private void saveChanges_Click(object sender, EventArgs e)
{
  this.msgsTableAdapter.Update(this.isportsDataSet.msgs);
}

Figure: 8.3.6.2.1. DataGrid

Now test the application again by hitting Ctrl+F5.
Scroll to the empty row, at the bottom, and enter data for a new row then select Save Changes which will write the new row back to the database.

Updates and deletes can be performed similarly.

Figure: 8.3.6.2.1. DataGrid
You can use Interactive ISQL to test that the changes that have been written. Interactive ISQL Interface is detailed in the Linking Progress tables section.

Figure: 8.3.6.2.1. DataGrid

The task is now complete.

Previous
Using Microsoft Entity Frameworks to Access Oracle Schema Objects with Virtuoso

Chapter Contents

Next
Using Microsoft Entity Frameworks to Access Ingres Schema Objects with Virtuoso