Keyword Search:
Book Home

Contents
Preface

Virtual Database Engine

Virtual Database (VDB) Engine
Using Microsoft Entity Frameworks to Access Oracle Schema Objects with Virtuoso
Using Microsoft Entity Frameworks to Access Progress Schema Objects with Virtuoso
Using Microsoft Entity Frameworks to Access Ingres Schema Objects with Virtuoso
Using Microsoft Entity Frameworks to Access Informix Schema Objects with Virtuoso
Using Microsoft Entity Frameworks to Access DB2 Schema Objects with Virtuoso
Using Microsoft Entity Frameworks to Access Sybase Schema Objects with Virtuoso
Using Microsoft Entity Frameworks to Access MySQL Schema Objects with Virtuoso
Install and configure OpenLink ODBC Driver for MySQL Install and configure OpenLink Virtuoso Universal Server Linking MySQL tables into OpenLink Virtuoso Creating EDM in Visual Studio 2008 Using EDM to create Entity Framework based applications
Using Microsoft Entity Frameworks to Access PostgreSQL Schema Objects with Virtuoso
Using Microsoft Entity Frameworks to Access ODBC to JDBC Bridge Schema Objects with Virtuoso
Using Microsoft Entity Frameworks to Access ODBC to ODBC Bridge Schema Objects with Virtuoso
Using Microsoft Entity Frameworks to Access Firebird Schema Objects with Virtuoso
Using Microsoft Entity Frameworks to Access Microsoft SQL Server Schema Objects with Virtuoso

8.8. Using Microsoft Entity Frameworks to Access MySQL Schema Objects with Virtuoso

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

Prerequisites

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

MySQL DBMS

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

ODBC Driver for MySQL

An MySQL ODBC Driver is required for Linking the MySQL Schema Objects into the Virtuoso Server. The OpenLink ODBC Driver for MySQL will be used in this section, for which a functional ODBC Data source name of "mysqlma" 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.

Tasks
Ensure MySQL 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 MySQL database schema before attempting to generate an EDM. In the case of the Northwind database all tables are not nullable, thus this should not be an issue in this case.

8.8.1. Install and configure OpenLink ODBC Driver for MySQL

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

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


8.8.2. Install and configure OpenLink Virtuoso Universal Server


8.8.3. Linking MySQL tables into OpenLink Virtuoso

  1. Start the Virtuoso Web User Interface
    Start
    Figure: 8.8.3.1. Start
  2. Select the "Conductor" link in the Left Frame and Login as the "dba" user.
    Conductor
    Figure: 8.8.3.1. Conductor
  3. Select the "Databases" -> "External Data Source" -> "Data Sources" tab menu items
    Databases
    Figure: 8.8.3.1. Databases
  4. Select the "Connect" button for the "mysqlma" MySQL DSN.
    Connect
    Figure: 8.8.3.1. Connect
  5. On successful connection Select the "Link Objects" button to obtain a list of available tables
    Link Objects
    Figure: 8.8.3.1. Link Objects
  6. Select all the tables that are part of the "mysql" catalog.
    Select tables
    Figure: 8.8.3.1. Select tables
  7. Ensure a primary key is assigned to all table that are to be used in the EDM generation phase.
    Catalog
    Figure: 8.8.3.1. Catalog
  8. Change the Catalog for each table to be "mysql" using the "Set All" button.
  9. Select the "Link" button to link the selected tables into Virtuoso
    "Link" button
    Figure: 8.8.3.1. "Link" button
  10. On completion of the Link process the tables will be displayed in the "External Linked Objects" tab.
    Completion
    Figure: 8.8.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 "mysql.mysqlma.artist" table to demonstrate this.
    Completion
    Figure: 8.8.3.1. Completion
  12. Then click the "Execute" button to run the query and retrieve the results from the remote table.
    Completion
    Figure: 8.8.3.1. Completion
  13. The tables can also be viewed as part of the Virtuoso "SQL Schema Objects" under the "mysql" catalog name.
    view tables
    Figure: 8.8.3.1. view tables

8.8.4. Creating EDM in Visual Studio 2008

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

  1. Launch the Visual Studio 2008 SP1 IDE.
    Visual Studio 2008 SP1 IDE
    Figure: 8.8.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.
    Web Application
    Figure: 8.8.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.
    name for the project
    Figure: 8.8.4.1. name for the project
  6. This will create a new project called VirtuosoDataService.
    create a new project
    Figure: 8.8.4.1. create a new project
  7. Select the Project -> Add New Item menu option.
    VirtuosoDataService
    Figure: 8.8.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.
    Add New Item
    Figure: 8.8.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.
    Choose Model Contents
    Figure: 8.8.4.1. Choose Model Contents
  10. In the Entity Data Model Wizard dialog Choose your Data Connection page, select the New Connection button
    Entity Data Model Wizard
    Figure: 8.8.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.
    Choose Data Source
    Figure: 8.8.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.
    Connection Properties
    Figure: 8.8.4.1. Connection Properties
  13. Select the Select Database From List radio button and choose the mysql database from the drop down list.
    Add connection
    Figure: 8.8.4.1. Add connection
  14. Press the Test Connection dialog to verify that the database is accessible.
    Test Connection
    Figure: 8.8.4.1. Test Connection
  15. Click OK to add the connection.
    Test Connection
    Figure: 8.8.4.1. Test Connection
  16. 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.
    entity connect string
    Figure: 8.8.4.1. entity connect string
  17. In the Choose your Database Objects page tick the Tables check box to select all tables in the mysql catalog for addition to the Entity Data Model. Set the Model Namespace to VirtuosoModel and click Finish.
    Database Objects
    Figure: 8.8.4.1. Database Objects
  18. The Virtuoso.edmx EDM will be created with the tables and relationships displayed in the Visual Studio IDE
    Virtuoso.edmx
    Figure: 8.8.4.1. Virtuoso.edmx

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


8.8.5. Using EDM to create Entity Framework based applications

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

8.8.5.1. Entity Frameworks based ADO.NET Data Service

An ADO.Net Data Service for the MySQL 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.
    VirtuosoDataService
    Figure: 8.8.5.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.
    Add New Item
    Figure: 8.8.5.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);
        }
    }
}
    Virtuoso.svc.cs
    Figure: 8.8.5.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 mysql Northwind database catalog.
    Data Service test
    Figure: 8.8.5.1.1. Data Service test
  6. To access a specific entity instance like the Customers table ALFKI record, use this convention: http://host/vdir/Virtuoso.svc/Customers('ALFKI') .
    Customers
    Figure: 8.8.5.1.1. Customers

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.8.5.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.
    Visual Studio 2008 SP1 IDE
    Figure: 8.8.5.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.
    Web Application
    Figure: 8.8.5.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.
    Web Application
    Figure: 8.8.5.2.1. Web Application
  6. In the Toolbox, expand Data Controls, and drag the DataGridView control onto the form.
    Toolbox
    Figure: 8.8.5.2.1. Toolbox
  7. Click on the little arrow in the top right of the DataGridView control. This loads the DataGridView Task menu.
    DataGridView Task
    Figure: 8.8.5.2.1. DataGridView Task
  8. Click on the Choose Data Source list box.
    Choose Data Source
    Figure: 8.8.5.2.1. Choose Data Source
  9. Click on the Add Project Data Source link to connect to a data source.
    Add Project Data Source
    Figure: 8.8.5.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.
    Data Source Type
    Figure: 8.8.5.2.1. Data Source Type
  11. In the Data Source Configuration Wizard dialog Choose your Data Connection page, select the New Connection button
    Data Source Configuration Wizard
    Figure: 8.8.5.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.
    Data Source
    Figure: 8.8.5.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.
    Connection Properties
    Figure: 8.8.5.2.1. Connection Properties
  14. Select the Select Database From List radio button and choose the mysql database from the drop down list.
    Add connection
    Figure: 8.8.5.2.1. Add connection
  15. Press the Test Connection dialog to verify that the database is accessible.
    Test Connection
    Figure: 8.8.5.2.1. Test Connection
  16. Click OK to add the connection.
    Test Connection
    Figure: 8.8.5.2.1. Test Connection
  17. Leave the default connect string namemysqlConnectionString and click Next.
    mysqlConnectionString
    Figure: 8.8.5.2.1. mysqlConnectionString
  18. From the list of available tables returned for the mysql database, select the Shippers table to be associated with the DataGridView control.
    mysql database
    Figure: 8.8.5.2.1. mysql database
  19. The columns names of the select table will be displayed in the DataGridView.
    DataGridView
    Figure: 8.8.5.2.1. DataGridView
  20. Resize the Form and DataGridView to allow all columns to be visible, if possible.
    Resize the Form and DataGridView
    Figure: 8.8.5.2.1. Resize the Form and DataGridView
  21. To test the application, simply hit Ctrl+F5 within Visual Studio or select Start Debugging from the Debug menu.
    Start Debugging
    Figure: 8.8.5.2.1. Start Debugging
  22. The data from the Shippers table will be displayed in the DataGrid.
    DataGrid
    Figure: 8.8.5.2.1. DataGrid

The task is now complete.