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
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
Install and configure OpenLink ODBC Driver for Microsoft SQL Server Install and configure OpenLink Virtuoso Universal Server Linking Microsoft SQL Server tables into OpenLink Virtuoso Creating EDM in Visual Studio 2008 Using EDM to create Entity Framework based applications

8.13. Using Microsoft Entity Frameworks to Access Microsoft SQL Server Schema Objects with Virtuoso

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

Prerequisites

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

Microsoft SQL Server DBMS

A Microsoft SQL Server DBMS hosting the required Schema Objects needs to be available. In this document the Microsoft SQL Server Northwind sample database will be used to demonstrate the process.

ODBC Driver for Microsoft SQL Server

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

8.13.1. Install and configure OpenLink ODBC Driver for Microsoft SQL Server

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

Installation instructions for the OpenLink ODBC Driver for Microsoft SQL Server are available from:


8.13.2. Install and configure OpenLink Virtuoso Universal Server


8.13.3. Linking Microsoft SQL Server tables into OpenLink Virtuoso

  1. Start the Virtuoso Web User Interface
    Start
    Figure: 8.13.3.1. Start
  2. Select the "Conductor" link in the Left Frame and Login as the "dba" user.
    Conductor
    Figure: 8.13.3.1. Conductor
  3. Select the "Databases" -> "External Data Source" -> "Data Sources" tab menu items
    Databases
    Figure: 8.13.3.1. Databases
  4. Select the "Connect" button for the "sql2k5ma" Microsoft SQL Server DSN.
    Connect
    Figure: 8.13.3.1. Connect
  5. On successful connection Select the "Link Objects" button to obtain a list of available tables
    Link Objects
    Figure: 8.13.3.1. Link Objects
  6. Select all the tables that are part of the "Northwind" catalog.
    Select all tables
    Figure: 8.13.3.1. Select all tables
  7. Change the Catalog for each table to be "Northwind" using the "Set All" button.
    Catalog
    Figure: 8.13.3.1. Catalog
  8. All the catalog names are changed to be "NWIND"
    catalog names
    Figure: 8.13.3.1. catalog names
  9. Select the "Link" button to link the selected tables into Virtuoso
    "Link" button
    Figure: 8.13.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.13.3.1. Completion
  11. At this point, you can test the remotely linked tables by clicking on the link that accompanies each table, e.g. NWIND.NWIND.Customer. This will display the Interactive ISQL interface which will have been already populated with a suitable SQL statement.
    Querying
    Figure: 8.13.3.1. Querying
  12. Select Execute to see data from the remotely linked table.
    Execute
    Figure: 8.13.3.1. Execute
  13. The tables can also be viewed as part of the Virtuoso "SQL Schema Objects" under the "Northwind" catalog name.
    SQL Schema Objects
    Figure: 8.13.3.1. SQL Schema Objects

8.13.4. Creating EDM in Visual Studio 2008

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

  1. Launch the Visual Studio 2008 SP1 IDE.
    Visual Studio 2008 SP1 IDE
    Figure: 8.13.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.
  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.13.4.1. name for the project
  6. This will create a new project called VirtuosoDataService.
    create a new project
    Figure: 8.13.4.1. create a new project
  7. Right click on the VirtuosoDataService project name of the Solution Explorer pane, then select the Add -> New Item menu options.
    VirtuosoDataService
    Figure: 8.13.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.13.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.13.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.13.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.13.4.1. Choose Data Source
  12. In the Add Connection dialog, specify the hostname, portno, username and password for the target Virtuoso Server and click the Advanced button.
    Connection Properties
    Figure: 8.13.4.1. Connection Properties
  13. Use the Select Database From List dialog to select the NWIND catalog.
    Add connection
    Figure: 8.13.4.1. Add connection
  14. Press the Test Connection dialog to verify that the database is accessible. Click "OK" to persist the connection attributes, after a successful connection is verified.
    Test Connection
    Figure: 8.13.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.
    entity connect string
    Figure: 8.13.4.1. entity connect string
  16. In the Choose your Database Objects page select the Tables check box to select all tables in the NWIND catalog for addition to the Entity Data Model. Set the Model Namespace to VirtuosoModel and click Finish.
    Database Objects
    Figure: 8.13.4.1. Database Objects
  17. The Virtuoso.edmx EDM will be created with the tables and relationships displayed in the Visual Studio IDE
    Virtuoso.edmx
    Figure: 8.13.4.1. Virtuoso.edmx

Creation for the Entity Data Model for the Microsoft SQL Server Northwind database is now complete.


8.13.5. Using EDM to create Entity Framework based applications

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

8.13.5.1. Entity Frameworks based ADO.NET Data Service

An ADO.Net Data Service for the Microsoft SQL Server 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.13.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.13.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.13.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 Northwind database catalog.
    Data Service test
    Figure: 8.13.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').
    EMPLOYEES
    Figure: 8.13.5.1.1. EMPLOYEES

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);

}