8. Virtual Database Engine

Keyword Search:

8.9. Using Microsoft Entity Frameworks to Access PostgreSQL Schema Objects with Virtuoso

This document details the steps required to provide Microsoft Entity Framework access to PostgreSQL Schema Objects using the OpenLink Virtuoso Universal Server. This is achieved by linking the required PostgreSQL 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 PostgreSQL Schema objects linked into the Virtuoso Server.

The following prerequisites must be in place for this solution to be possible.

PostgreSQL DBMS

An PostgreSQL DBMS hosting the required Schema Objects needs to be available. In this section the PostgreSQL sample database will be used to demonstrate the process.

ODBC Driver for PostgreSQL

An PostgreSQL ODBC Driver is required for Linking the PostgreSQL Schema Objects into the Virtuoso Server. The OpenLink ODBC Driver for PostgreSQL will be used in this section, for which a functional ODBC Data source name of "ora10ma" will be assumed to exist on the machine hosting the Virtuoso Server.

Virtuoso Universal Server

An Virtuoso installation including the Virtuoso Universal Server and ADO.NET Entity Framework Provider is required. The Virtuoso components used 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 Service Pack 1 is required, this being the only version containing the necessary Entity Framework support available at the time of writing.

Ensure PostgreSQL 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. Thus ensure any tables to be used are defined as not nullable in the PostgreSQL database schema before attempting to generate an EDM. In the case of the PostgreSQL database all tables are not nullable, thus this should not be an issue in this case.

8.9.1. Install and configure OpenLink ODBC Driver for PostgreSQL

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

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

• Product Installation & Basic Configuration (ODBC)

8.9.2. Install and configure OpenLink Virtuoso Universal Server

• Install and configure OpenLink Virtuoso Universal Server

8.9.3. Linking PostgreSQL tables into OpenLink Virtuoso

1. Start the Virtuoso Web User Interface

   Figure: 8.9.3.1. Start

2. Select the "Conductor" link in the Left Frame and Login as the "dba" user.

   Figure: 8.9.3.1. Conductor

3. Select the "Databases" -> "External Data Source" -> "Data Sources" tab menu items

   Figure: 8.9.3.1. Databases

4. Select the "Connect" button for the "pg7ma" PostgreSQL DSN.

   Figure: 8.9.3.1. Connect

5. On successful connection Select the "Link Objects" button to obtain a list of available tables

   Figure: 8.9.3.1. Link Objects

6. Select all the tables that you would like to link.

   Figure: 8.9.3.1. Select all tables

7. Change the Catalog for each table to be "Northwind" using the "Set All" button.

   Figure: 8.9.3.1. Catalog

8. All the catalog names are changed to be "Northwind".

   Figure: 8.9.3.1. Catalog

9. Select the "Link" button to link the selected tables into Virtuoso

   Figure: 8.9.3.1. "Link" button

10. On completion of the Link process the tables will be displayed in the "External Linked Objects" tab.

    Figure: 8.9.3.1. Completion

11. The linked tables can be queried by clicking on the hyperlink in the "Local Name" column of the "External Linked Objects" tab above, which loads the Virtuoso "Interactive SQL" interface with the required SQL "Select" for retrieving the remote table data . We shall use the "Northwind.pg7ma.Customers" table to demonstrate this.

    Figure: 8.9.3.1. Querying

12. Then click the "Execute" button to run the query and retrieve the results from the remote table.

    Figure: 8.9.3.1. Execute

13. The tables can also be viewed as part of the Virtuoso "SQL Schema Objects" under the "Northwind" catalog name.

    Figure: 8.9.3.1. SQL Schema Objects

8.9.4. Creating EDM in Visual Studio 2008

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

1. Launch the Visual Studio 2008 SP1 IDE.

   Figure: 8.9.4.1. Visual Studio 2008 SP1 IDE

2. Create a Web Application project by going to the File menu in Visual Studio and choosing New Project.

   Figure: 8.9.4.1. Web Application

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

   Figure: 8.9.4.1. name for the project

6. This will create a new project called VirtuosoDataService.

   Figure: 8.9.4.1. create a new project

7. Select the Project -> Add New Item menu option.

   Figure: 8.9.4.1. VirtuosoDataService

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

   Figure: 8.9.4.1. Add New Item

9. In the Entity Data Model Wizard dialog Choose Model Contents page select the Generate from Database model type and click Next.

   Figure: 8.9.4.1. Choose Model Contents

10. In the Entity Data Model Wizard dialog Choose your Data Connection page, select the New Connection button

    Figure: 8.9.4.1. Entity Data Model Wizard

11. In the Choose Data Source dialog, select the OpenLink Virtuoso Data Source from the list and click Continue.

    Figure: 8.9.4.1. Choose Data Source

12. 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.9.4.1. Connection Properties

13. Select the Select Database From List radio button and choose the Northwind database from the drop down list.

    Figure: 8.9.4.1. Add connection

14. Press the Test Connection dialog to verify that the database is accessible.

    Figure: 8.9.4.1. Test Connection

15. Set the entity connect string name to VirtuosoEntities (note this name as it is required in the section on creating and ADO.Net Data Service) and click Next.

    Figure: 8.9.4.1. entity connect string

16. In the Choose your Database Objects page select the Tables check box to select all tables in the Northwind catalog for addition to the Entity Data Model. Set the Model Namespace to VirtuosoModel and click Finish.

    Figure: 8.9.4.1. Database Objects

17. The Virtuoso.edmx EDM will be created with the tables and relationships displayed in the Visual Studio IDE

    Figure: 8.9.4.1. Virtuoso.edmx

Creation for the Entity Data Model for the PostgreSQL Northwind database is now complete.

8.9.5. Using EDM to create Entity Framework based applications

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

8.9.5.1. 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.

1. Launch the Visual Studio 2008 SP1 IDE.

   Figure: 8.9.5.1.1. Visual Studio 2008 SP1 IDE

2. Create a Web Application project by going to the File menu in Visual Studio and choosing New Project.

   Figure: 8.9.5.1.1. Web Application

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

   Figure: 8.9.5.1.1. Web Application

6. In the Toolbox, expand Data Controls, and drag the DataGridView control onto the form.

   Figure: 8.9.5.1.1. Toolbox

7. Click on the little arrow in the top right of the DataGridView control. This loads the DataGridView Task menu.

   Figure: 8.9.5.1.1. DataGridView Task

8. Click on the Choose Data Source list box.

   Figure: 8.9.5.1.1. Choose Data Source

9. Click on the Add Project Data Source link to connect to a data source.

   Figure: 8.9.5.1.1. Add Project Data Source

10. In the Data Source Configuration Wizard dialog Choose Data Source Type page select the Database data source type and click Next.

    Figure: 8.9.5.1.1. Data Source Type

11. In the Data Source Configuration Wizard dialog Choose your Data Connection page, select the New Connection button

    Figure: 8.9.5.1.1. Data Source Configuration Wizard

12. In the Choose Data Source dialog, select the OpenLink Virtuoso Data Source from the list and click Continue.

    Figure: 8.9.5.1.1. Data Source

13. 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.9.5.1.1. Connection Properties

14. Select the Select Database From List radio button and choose the Northwind database from the drop down list.

    Figure: 8.9.5.1.1. Add connection

15. Press the Test Connection dialog to verify that the database is accessible.

    Figure: 8.9.5.1.1. Test Connection

16. Leave the default connect string NorthwindConnectionString and click Next

    Figure: 8.9.5.1.1. NorthwindConnectionString

17. From the list of available tables returned for the Northwind database, select the Shippers table to be associated with the DataGridView control.

    Figure: 8.9.5.1.1. Northwind database

18. The columns names of the select table will be displayed in the DataGridView.

    Figure: 8.9.5.1.1. DataGridView

19. Resize the Form and DataGridView to allow all columns to be visible, if possible.

    Figure: 8.9.5.1.1. Resize the Form and DataGridView

20. To test the application, simply hit Ctrl+F5 within Visual Studio or select Start Debugging from the Debug menu.

    Figure: 8.9.5.1.1. Start Debugging

21. The data from the Shippers table will be displayed in the DataGrid.

    Figure: 8.9.5.1.1. DataGrid

The task is now complete.

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

Chapter Contents

Next Using Microsoft Entity Frameworks to Access ODBC to JDBC Bridge Schema Objects with Virtuoso