8. Virtual Database Engine

Keyword Search:

8.7. Using Microsoft Entity Frameworks to Access Sybase Schema Objects with Virtuoso

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

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

Sybase DBMS

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

ODBC Driver for Sybase

An Sybase ODBC Driver is required for Linking the Sybase Schema Objects into the Virtuoso Server. The OpenLink ODBC Driver for Sybase will be used in this section, for which a functional ODBC Data source name of "syb15ma" 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 Sybase 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 Sybase database schema before attempting to generate an EDM. In the case of the Sybase pubs2 database all tables are not nullable, thus this should not be an issue in this case.

8.7.1. Install and configure OpenLink ODBC Driver for Sybase

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

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

• Product Installation & Basic Configuration (ODBC)

8.7.2. Install and configure OpenLink Virtuoso Universal Server

• Install and configure OpenLink Virtuoso Universal Server

8.7.3. Linking Sybase tables into OpenLink Virtuoso

1. Start the Virtuoso Web User Interface

   Figure: 8.7.3.1. Start

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

   Figure: 8.7.3.1. Conductor

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

   Figure: 8.7.3.1. Databases

4. Select the "Connect" button for the "syb15ma" Sybase DSN.

   Figure: 8.7.3.1. Connect

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

   Figure: 8.7.3.1. Link Objects

6. Select all the tables that are part of the "pubs2" catalog.

   Figure: 8.7.3.1. Select tables

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

   Figure: 8.7.3.1. Catalog

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

   Figure: 8.7.3.1. catalog names

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

   Figure: 8.7.3.1. "Link" button

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

    Figure: 8.7.3.1. Completion

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

    Figure: 8.7.3.1. view tables

8.7.4. Creating EDM in Visual Studio 2008

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

1. Launch the Visual Studio 2008 SP1 IDE.

   Figure: 8.7.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.7.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.7.4.1. name for the project

6. This will create a new project called VirtuosoDataService.

   Figure: 8.7.4.1. create a new project

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

   Figure: 8.7.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.7.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.7.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.7.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.7.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.7.4.1. Connection Properties

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

    Figure: 8.7.4.1. Add connection

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

    Figure: 8.7.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.7.4.1. entity connect string

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

    Figure: 8.7.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.7.4.1. Virtuoso.edmx

Creation for the Entity Data Model for the Sybase pubs2 database is now complete.

8.7.5. Manually creating EDM Associations (FKs) for the Sybase pubs2 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.

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

   Figure: 8.7.5.1. Association

   This is a one to many relationship insomuch that publishers may have zero or many titles.

2. To add the Association, right click on the publishers entity then Add -> Association.

   Figure: 8.7.5.1. add the Association

3. You will now see the Add Association dialog.

   Figure: 8.7.5.1. Add Association dialog

4. For this association the only thing that needs changing is the name of the Navigation Property from publishers to publisher on the publishers end of the association.

   This more correctly reflects the multiplicity of the association such that a publisher is associated with zero or many titles (plural).

   Figure: 8.7.5.1. Navigation Property name

5. Once you then hit OK the diagram is refreshed to include the newly created association.

   Figure: 8.7.5.1. diagram

6. 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.7.5.1. Table Mapping

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

   Figure: 8.7.5.1. Add a Table or View

8. 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 titles entity.

   Figure: 8.7.5.1. Entity

9. 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.7.5.1. Mapping Details

10. 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.7.5.1. Build the project

11. This should result in the following error:

    Figure: 8.7.5.1. Error

    It seems that this error is attempting to say that there are two mappings which map to the same target source column.

    In this case it is the target column titles.pub_id.

    That is, the scalar property pub_id and the Navigation Property publishers in the Invoice entity, both map to the Sybase table column titles.pub_id - which is not supported

    Figure: 8.7.5.2. Error

12. The solution is simple! Simply delete the scalar property titles.pub_id since its purpose is only 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 publishers.

    Right click on titles.pub_id then Delete.

    Figure: 8.7.5.1. delete the mapping

13. The model diagram will refresh to reflect this change.

    Figure: 8.7.5.1. Model Diagram

14. Build the project, again, using Build -> Build Solution.

    Figure: 8.7.5.1. Build project

15. The project should now be fine.

    Figure: 8.7.5.1. Build Project

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

Figure: 8.7.5.1. Entity Data Model

8.7.6. Using EDM to create Entity Framework based applications

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

8.7.6.1. Entity Frameworks based ADO.NET Data Service

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

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

   Figure: 8.7.6.1.1. VirtuosoDataService

3. The Add New Item dialog will appear. Choose the ADO.NET Data Service template. Give it the name Virtuoso.svc, and click Add to create the ADO.Net Data Service.

   Figure: 8.7.6.1.1. Add New Item

4. In the newly created Virtuoso.svc.cs Data Service file, add the data source class name of VirtuosoEntities (note this is the name set in the Creating EDM in Visual Studio 2008 section) as the DataService name. 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.7.6.1.1. Virtuoso.svc.cs

5. 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 P2 database catalog.

   Figure: 8.7.6.1.1. Data Service test

6. To access a specific entity instance like the publisher table publisher number 0736 record, use this convention http://host/vdir/Virtuoso.svc/publishers("0736") .

   Figure: 8.7.6.1.1. publisher

Notes:

1. 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.
2. 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.7.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.

1. Launch the Visual Studio 2008 SP1 IDE.

   Figure: 8.7.6.2.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.7.6.2.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.7.6.2.1. Web Application

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

   Figure: 8.7.6.2.1. Toolbox

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

   Figure: 8.7.6.2.1. DataGridView Task

8. Click on the Choose Data Source list box.

   Figure: 8.7.6.2.1. Choose Data Source

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

   Figure: 8.7.6.2.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.7.6.2.1. Data Source Type

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

    Figure: 8.7.6.2.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.7.6.2.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.7.6.2.1. Connection Properties

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

    Figure: 8.7.6.2.1. Add connection

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

    Figure: 8.7.6.2.1. Test Connection

16. Leave the default connect string P2ConnectionString and click Next

    Figure: 8.7.6.2.1. P2ConnectionString

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

    Figure: 8.7.6.2.1. P2 database

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

    Figure: 8.7.6.2.1. DataGridView

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

    Figure: 8.7.6.2.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.7.6.2.1. Start Debugging

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

    Figure: 8.7.6.2.1. DataGrid

The task is now complete.

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

Chapter Contents

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