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

CodeFluent Entities: Initializing your application with data

March 26, 2012 Leave a comment

In our previous posts, we’ve seen that from a single platform independent model (see CodeFluent Entities is not an ORM) you can generate continuously (see Continuous Generation) complete databases, .NET classes, web services or UIs.

That’s great but when you’re developing your application, this will all be empty and just like an empty party, an empty app is no fun Smile

Well using CodeFluent Entities, from Visual Studio, you can specify “instances of data” in your model. What’s great about instances is that persistence producers such as the SQL Server producer, Oracle Database producer or SQL Azure producer, will translate those into a SQL script which will insert/update data in your app!

Here’s an example:

Select an entity and click on “Add Instance” to add instances one by one using the instance dialog.

You can also use the instance grid (accessible from the ribbon, in the “Batch Modeling” group) to multiple instances at a time in a grid:

 

For the newcomers, we published a while ago a 1 minute video illustrating how to generate code and data using CodeFluent Entities, plus using the generated code right away in the following post: How to generate with data (video)

Cheers,

Carl Anderson

CodeFluent Entities: Connection Strings

February 21, 2012 Leave a comment

Fairly recently we had a big thread of questions regarding connection strings on our forums, so I thought I’d do a recap’ of how connection strings are handled in CodeFluent Entities.

First it’s important to separate two distinct stages:

  • generation time: the connection string used by your producer to generate code,
  • run time: the connection string used by your generated application.

 

At Generation Time

As mentioned in previous posts (see Environment Variables Support and Default Persistence Server), CodeFluent Entities uses a default connection string which is as follows:

Application Name=[DefaultNamespace];server=[DefaultPersistenceServer];database=[DefaultNamespace];Integrated Security=true;

  • [DefaultNamespace]: your CodeFluent Entities default namespace,
  • [DefaultPersistenceServer]: value of the CF_DEFAULT_PERSISTENCE_SERVER environment variable or “127.0.0.1” if not found.

As a consequence, by setting the CF_DEFAULT_PERSISTENCE_SERVER environment variable (see Default Persistence Server for more info), you won’t even have to specify connection strings to build your applications on your development environment.

What’s even better, is that if you’re working as a team on the same application, if each member specified its own persistence server, they won’t even have to check-out and modify the model to build it in their respective environment.

This being said, in some situations, you might have to set a specific connection string to build somewhere else than on your default persistence server. To do so, you’ve got several options:

  • On your project, set the “Default Connection String” property to the default connection string to use (if none specified, the default one is used)
  • Or you can specify it producer by producer (if no connection string set, the one defined at the project level is used)

 

At Run Time

Now that you generated your application, it can run anywhere, and connect to any persistence server.

Once again, just like at generation time, the same connection string is used by default:

Application Name=[DefaultNamespace];server=[DefaultPersistenceServer];database=[DefaultNamespace];Integrated Security=true;

So likewise, if you specified the CF_DEFAULT_PERSISTENCE_SERVER environment variable on your machine, you won’t have to define a connection string.

If your application runs on a machine without this variable, or if you want it to use another persistence server explicitly, you’ll need to set its connection string in its application configuration file (app.config for a desktop app, or web.config for a web app). Here’s the corresponding documentation article illustrating how to do it: Application Configuration.

Another point of interest is that you can reuse connection strings defined in the standard .NET <connectionStrings> element.

Example:

<configuration>
  <configSections>
    <section name="Sample" type="CodeFluent.Runtime.CodeFluentConfigurationSectionHandler, CodeFluent.Runtime"/>
  </configSections>
  <connectionStrings>
    <add name="SqlServer" connectionString="server=MYSERVER;database=Sample;Integrated Security=true" />
    <add name="SqlServerExpress" connectionString="server=MYSERVER\SQLEXPRESS;database=Sample;Integrated Security=true" />
  </connectionStrings>
  <Sample connectionString="{SqlServerExpress}" />
</configuration>

The example above we’ll use the connection string named “SqlServerExpress”.

Another point of interest is that you can also use environment variables as the connection string name, so that based on an environment variable you can go pick one connection string or the other. For instance, depending on the local machine name, you could connect to one persistence server or another:

<configuration>
  <configSections>
    <section name="Sample" type="CodeFluent.Runtime.CodeFluentConfigurationSectionHandler, CodeFluent.Runtime"/>
  </configSections>
  <connectionStrings>
    <add name="Foo" connectionString="server=MyServer1;database=Sample;Integrated Security=true" />
    <add name="Bar" connectionString="server=MyServer2;database=Sample;Integrated Security=true" />
  </connectionStrings>
  <Sample connectionString="{%COMPUTERNAME%}" />
</configuration>

In the example above, if we’re on the “Foo” machine we’ll connect to “MyServer1” whereas when we’re on the “Bar” machine, we’ll connect to the server “MyServer2”.

 

Hope this helps,

Carl Anderson

CodeFluent Entities: Customizing the Method Body

December 19, 2011 Leave a comment

CodeFluent Entities provides “Methods” which allows developers to define custom data accessing methods (load, loadone, delete, count, search) which persistence producers will translate into stored procedures.

For instance, the following method:

image

Will generate the following stored procedure:

CREATE PROCEDURE [dbo].[Setting_LoadByName]
(
@Name [nvarchar] (256)
)
AS
SET NOCOUNT ON
SELECT DISTINCT [Setting].[Setting_Id], [Setting].[Setting_Name], [Setting].[Setting_Value] FROM [Setting]
    WHERE ([Setting].[Setting_Name] = @Name)

RETURN

You can also create what we call Raw methods which are platform specific code such as T-SQL or PL-SQL, but you can also mix both, that is to say create a CFQL method so upper layers benefit from the out-of-the-box features provided by such methods and just override the actual body of the stored procedure.

For instance say you wanted to create a topped method which the number of returned rows can be passed to the method. The “TOP” statement doesn’t exist in CFQL (see TOP in CFQL for more information) so a way to do this would be to mix CFQL and T-SQL for instance.

To do so, start by defining your CFQL body:

image

And then define the Raw Text property of the body (select your method, and click on the “Bodies” property in the property grid):

image

Once this is done, if you build your model, the stored procedure quoted hereunder will be generated, and in upper layers your method will look just as expected (returning a SettingCollection, mapping returned columns to properties, and taking an int maxCount parameter):

CREATE PROCEDURE [dbo].[Setting_BoundedLoadAll]
(
@maxCount [int]
)
AS
SET NOCOUNT ON
SELECT TOP(@maxCount) * from Setting
RETURN

Follow

Get every new post delivered to your Inbox.

Join 41 other followers