Archive

Archive for the ‘Oracle Database’ Category

Goodbye SQL-DMO!

September 10, 2012 1 comment

Starting with build 671, SQL-DMO is not longer a requirement for the CodeFluent Entities SQL Server Producer. That’s good news!

We have been relying on SQL-DMO for almost 7 years, and it has served us well. It has been marked as obsolete for something like 5 years or so, but the producer was still relying on it. One of the reasons we kept it for so long was to maintain  compatibility with SQL Server 2000. Now, we don’t support that database either (june 2012).

We have replaced SQL-DMO by a similar technology without any dependency on the newer SQL-SMO (replacement for SQL-DMO) either. This database abstraction technology – our own SMO-like layer – works not only for SQL Server but also for all our supported importers and producers supported databases and modeling systems, namely: SQL Server, Oracle, PostgreSQL, MySQL, SqlLite, Xmi, Enterprise Architect, Access and SqlServerCe. It’s mainly located in the CodeFluent.Runtime.Database.dll assembly that ships with the product and in satellite ones for specific targeted systems (CodeFluent.Runtime.PostgreSQL.dll for PostgreSQL, CodeFluent.Runtime.MySQL.dll for MySQL, etc.).

Cheers,

The R&D team.

Setting-up Oracle Database for .NET development

July 13, 2012 Leave a comment

I recently had to set-up a new development environment for a .NET application consuming an Oracle Database and found out that it wasn’t that easy, so I thought I’d share how I did it.

 

Warranty

I’m just sharing my config and the way I did it.

SoftFluent cannot be held responsible if something goes wrong or does not work, and no support is provided. It’s neither official documentation nor the only way to do it, I’m just sharing my recipe Smile

 

Prerequisites

I downloaded and installed a free Oracle Database Express Edition.

The 11g Release 2 is available here: http://www.oracle.com/technetwork/products/express-edition/downloads/index.html

Yet, in this case I had to install a 10g in 32-bit which can’t be found on OTN anymore…..except on a Japanese OTN page?!!

If you need the Oracle Database 10g Express Edition (x86) as I did here’s the link: http://www.oracle.com/technetwork/jp/database/express-edition/downloads/102xewinsoft-089442-ja.html

 

Installing the Server

Nothing special here: install OracleXE.exe, keep everything by default:

  • installation directory: C:\oraclexe
  • default port: 1521
  • configure the SYS or SYSTEM account

Once installed, an administration web page should start. Check that everything runs fine, if so, you’re done, now let’s prepare it for the .NET world!

 

Installing OCI & ODP.NET

Oracle Call Interface (OCI) is the basic and compulsory method to access Oracle using a client. On top of OCI, we’ll need the Oracle Data Access Components (ODAC). Both OCI and ODAC are a set of dlls and that are shipped as a ZIP, available on Oracle’s web site (http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html).

In my case I specifically needed the ODAC1110621Xcopy.zip, in your case just be careful to use an ODAC version matching your Oracle Database version and bitness.

Then here’s how I configured my environment:

  • I created a “C:\oracle” just like OracleXE did,
  • I extracted the content of my ODAC1110621Xcopy.zip archive in this directory,
  • I removed the path C:\oraclexe\app\oracle\product\10.2.0\server\bin” from my PATH environment variable…
  • …and added the “C:\oracle\instantclient_11_1” in the PATH so my OCI dlls are used,
  • I copied the Oracle.DataAccess.dll that is in “C:\oracle\odp.net20\odp.net\bin\2.x\” in “C:\oracle\instantclient_11_1”,
  • I removed all Oracle.DataAccess.dll from the GAC so I’ll be sure my .NET apps we’ll always use the Oracle.DataAccess.dll they reference,
  • I copied the OraOps11w.dll contained in the “C:\oracle\bin” in “C:\oracle\instantclient_11_1”

 

Installing SQL Developer

image

 

Setting-up CodeFluent Entities

Since we removed Oracle.DataAccess.dll from the GAC, we need to place it in CodeFluent Entities directory so the Oracle Database Producer can access it.

 

Hope this helps,

Carl Anderson

CodeFluent Entities: How to reuse existing stored procedures

July 13, 2012 Leave a comment

In this post I’ll start from the NerdDinner application, which you can get from CodePlex (http://nerddinner.codeplex.com/). In the NerdDinner database I added the following sample stored procedure named “sp_SelectDinnersByCountry” that takes a county name as a parameter:

CREATE PROCEDURE [dbo].[sp_SelectDinnersByCountry] 
(
     @Country [nvarchar] (50)
)
AS
SET NOCOUNT ON
SELECT DISTINCT * 
    FROM Dinners 
    WHERE Country = @Country
RETURN

From there, I then imported the NerdDinner database into a brand new CodeFluent Entities model (as detailed on CodeFluent Entities’ Get Started page) and now what I want to do is to reuse my existing stored procedure, instead of creating a new one using CFQL.

Select the “Dinner” entity and add the following new method:

image

Now that our method is created we need to specify:

  • it’s name in the persistence layer as my stored procedure has a different name than the one in my model,

image

 

  • it’s return type (by default raw methods are void),

image

 

  • mark my method so it’s not generated by the SQL Server Producer,

image

 

As a consequence the Business Object Model Producer will generate a SelectByCountry method, which calls the sp_SelectDinnersByCountry stored procedure, and returns a DataSet, so now in my app I can do something like this:

System.Data.DataSet ds = Dinner.SelectByCountry("FRANCE");

 

Hope this helps,

Carl

Designing Databases with CodeFluent Entities: Inheritance

April 20, 2012 Leave a comment

CodeFluent Entities tries to reduce the impedance mismatch between relational worlds and object-oriented worlds as much as possible and a good example of this I guess is how CodeFluent Entities supports inheritance across layers of your application.

Using CodeFluent Entities, an entity hierarchy defined in your model will be translated following the Table-Per-Type pattern in the persistence layer, when, in the generated business object model, this will be a regular class inheritance.

For instance, say we have the following hierarchy:

Here’s the result in the database:

Furthermore, data access methods will be modified accordingly to load the objects as expected in .NET. For instance, here’s the “Load” stored procedure that is automatically generated for the DiscountProduct entity:

CREATE PROCEDURE [dbo].[DiscountProduct_Load]
(
@Id [uniqueidentifier]
)
AS
SET NOCOUNT ON
SELECT DISTINCT [DiscountProduct].*, Product].* 
  FROM [DiscountProduct]
   INNER JOIN [Product] ON ([DiscountProduct].[Product_Id] = [Product].[Product_Id])
  WHERE ([DiscountProduct].[Product_Id] = @Id)

RETURN

Cheers,

Carl Anderson

Designing Databases with CodeFluent Entities: Names

April 19, 2012 Leave a comment

As explained in previous posts, CodeFluent Entities is way more that an ORM: you’re not just mapping classes to database objects, instead you’re creating a model which will be translated into stuff. Therefore if you add persistence producers such as SQL Server, Oracle Database or SQL Azure a database will be created or updated from it (see continuous generation from more info).

Creating a database implies naming a lot of stuff: tables, columns, constraints, stored procedures, etc., so I thought I’d give you a little details on how it works and how you can control this to create your own database.

First, CodeFluent Entities deduces names automatically from your model:

  • Table names are identical to entity names,
  • Columns are named “<EntityName>_<PropertyName>”,
  • Procedures are named “<EntityName>_<MethodName>”

Users can override this logic by:

  • defining a “Persistence Name” in the Property Grid on the object you want to change (entity, property, method, view, ..)
  • setting a “Persistence Property Name Format” at the project level to customize all property names of your project (e.g. replace the default “{0}_{1}” by “{1}” to remove the entity name prefix from all column names),
  • changing the naming convention

In practice a naming convention is a class implementing a naming convention rule which CodeFluent Entities will use to name persistence objects. Consequently changing the naming convention used will change the way all objects are named.

Several naming conventions are provided out-of-the-box and they all derive from one another, hence they benefit from the features of their parents. The hierarchy is as follows:

  • BaseNamingConvention (default)
    • FormatNamingConvention: modifies the name format of tables, columns, procedures, parameters, constraints.
      • LowerCaseNamingConvention: sets all database object names in lower case.
      • UpperCaseNamingConvention: sets all database object names in upper case.
      • DecamelizeNamingConvention: decamelizes all names (FirstName –> First_Name)
        • DecamelizeLowerCaseNamingConvention: all in lower case and decamelized (FirstName –> first_name)
        • DecamelizeUpperCaseNamingConvention: all in upper case and decamelized (FirstName –> FIRST_NAME)

Furthermore you can create your own custom naming convention by implementing the INamingConvention or by deriving from existing ones.

 

Cheers,

Carl Anderson

CodeFluent Entities Supports Multiple Database Systems

April 3, 2012 Leave a comment

Persistence producers translate your platform independent model into scripts and deploy them on the specified database to create your persistence layer.

Furthermore the persistence layer used is independent from the upper layers (.NET classes, services, user interfaces): switching your persistence layer does not change upper layers.

Therefore, using CodeFluent Entities allows you to create very flexible apps where switching or supporting a new database system comes down to switch or add producers:

 

Cheers,

Carl Anderson

What’s CodeFluent Query Language?

March 30, 2012 Leave a comment

CodeFluent Query Language (a.k.a. CFQL) allows you to write platform independent data accessing queries which producers (producer = code generator) will translate into optimized stored procedures.

For instance you can define the following CFQL query in your model:

<cf:method name="LoadByName" body="load(Name) where Name startswith @Name"/> 

The SQL Server Producer will generate the following stored procedure:

CREATE PROCEDURE [dbo].[Test_LoadByName] 
( 
@Name [nvarchar] (256) 
) 
AS 
SET NOCOUNT ON 
SELECT DISTINCT [Test].[Test_Id], [Test].[Test_Name] 
    FROM [Test] 
    WHERE ([Test].[Test_Name] LIKE (@Name + '%'))

RETURN

And the Oracle Database Producer will generate the following equivalent:

PROCEDURE LoadByName(CF_CURSOR OUT "Sample"."CF_#Runtime".CF_CURSOR_TYPE, 
        "#Name" NVARCHAR2) AS 
    V_CF_CURSOR "Sample"."CF_#Runtime".CF_CURSOR_TYPE; 
    BEGIN 
        OPEN V_CF_CURSOR FOR 
        SELECT "Test"."Test_Id", "Test"."Test_Name" 
            FROM "Sample"."Test" 
            WHERE ("Test"."Test_Name" LIKE ("#Name" || N'%')); 
    CF_CURSOR := V_CF_CURSOR; 
    END;

As you can see data accessing queries are not built in-memory dynamically at run time but instead a classic stored procedure is generated at development time.

As a consequence developers can:

  • see what’s going to happen at run time and avoid bad surprises (like this one when using EF for instance),
  • debug it easily if they need to,
  • change the code if the need to.

 

Cheers,
Carl Anderson

Follow

Get every new post delivered to your Inbox.

Join 49 other followers