Migrating database schema from Microsoft SQL Server to Oracle using Entity Framework and Devart dotConnect

This walkthrough covers the migration of tables and primary/foreign keys that is a reasonable assumption when accessing data using ORM.

Prerequisites

  • Visual Studio 2010 (should also work with Visual Studio 2008 with .NET 3.5 SP1 applied).
  • Devart dotConnect for Oracle (formely known as OraDirect.NET) that supports Entity Framework capabilities.

Steps to migrate data schema

1. Create a C# class library project using Visual Studio 2010.

Picture01

2. Delete a class created in a projects (“Class1”) and add a new item using “ADO.NET Entity Data Model” template.

Picture02

3. Select “Generate from database” and choose SQL Server database that contains the schema to be migrated.

Picture03

4. Select “Tables” in the next screen (“Choose Your Database Objects”), and Visual Studio will generate a model based on the selected database schema.

Picture04

Now if you right-click on a model diagram, Visual Studio will display a menu with an option “Generate Database from Model…”

Picture05

If you choose database generation, Entity Designer will generate a script for Microsoft SQL Server.

Picture06

4. Assuming Devart dotConnect is installed, it should be possible to change a template that is used to generate database script. Open “DDL Generation Template” combo box in Entity Designer Properties window and select “Devart SSDLToOracle.tt (VS)”.

Picture07

5. After the change of DDL generation template execute “Generate Database from Model” and you should get a script for Oracle data schema:

Picture08

Voila!

One thought on “Migrating database schema from Microsoft SQL Server to Oracle using Entity Framework and Devart dotConnect”

  1. There is no support for Model First in VS 2008, it was introduced in VS 2010 only.
    After the third step it will be useful to change the schema name from the default “dbo” to your Oracle schema name.
    However, the latest builds of dotConnect for Oracle do not add schema name to a script by default.
    This and some other settings can be easily adjusted in the
    %Program Files%Microsoft Visual Studio 10.0Common7IDEExtensionsMicrosoftEntity Framework ToolsDBGendevart ssdltooracle.tt template file.
    Please note that we have added DROP statements before the CREATE script.
    And after the fourth step it is necessary to change the connection string to the one pointing to Devart Oracle connection. It is a limitation of the VS Generate Database From Model wizard, it applies the current DDL Generation Template incorrectly in case the connection was left unchanged.
    To achieve the correct results comment out the context connection string in the application configuration file (App.Config/Web.Config) and run the Generate Database from Model wizard. Provide a correct Oracle connection on the first step of the wizard, and the DDL script will be generated successfully.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>