Thursday, May 22, 2008

Linq for Oracle sample

Thursday, May 22, 2008 Posted by Andre Broers , , , 13 comments
Start with a database in my situation XE on localhost.
C:>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> create user linq identified by linq;
User created.
SQL> grant connect,resource to linq;
Grant succeeded.
SQL> connect linq/linq
Connected.
SQL> create table degrees
2 (
3 degkey number primary key
4 ,degdate date
5 ,degcelcius number
6 )
7 /
Table created.
SQL>

Download the dblinq library from : http://code2code.net/DB_Linq/

or get the latest via svn which has VisualMetal:
svn checkout http://dblinq2007.googlecode.com/svn/trunk/


Goto the directory and do the following to build the oracle libraries:

cd DbLinq.Oracle
msbuild

This will build the DbLinq.dll and DbLinq.Oracle.dll in binDebug

cd DbMetal
msbuild

This will build the DbMetal tool which creates the database to objects mapper classes. Ignore the copy errors at the end, it works without the copying because the oracle assemblies are in the GAC. DbMetal is created in the bin directory.

Run the command to create the mapper classes.

dbmetal /provider:oracle /conn:"Data Source=XE;User Id=linq;Password=linq;" /database=linq /code:linq.cs

create a new working directory (c:dotnetoracle) and copy the linq.cs, DbLinq.dll and DbLinq.Oracle.dll to this directory.

Create the test program oracletest.cs:

[sourcecode language="C#"]

using System;
using System.Collections.Generic;
using System.Linq;
using DbLinq.Linq.Mapping;
using System.Data.OracleClient;

class OracleTest
{
static void Main(string[] args)
{
string connStr = "User Id=linq;Password=linq;Data Source=XE";

Linq db = new Linq(new OracleConnection(connStr)); // Linq is the mapperclass created with DbMetal

Console.WriteLine("Select and delete all temperatures:");
var q1 = from p in db.Degrees select p;
//db.Degrees.RemoveAll(q1); // Not implemented :-(
Console.WriteLine("Fired sql:");
Console.WriteLine(db.GetQueryText(q1));
Console.WriteLine("Result:");
foreach (var v in q1)
{
Console.WriteLine(v.DEGKey + " - " + v.DEGDate + " - " + v.DEGCeLcIUs); // For some reason DbMetal makes a very cryptic property name
db.Degrees.DeleteOnSubmit(v);
}
db.SubmitChanges();
Console.WriteLine("------");

Console.WriteLine("Select temperatures (none):");
var q2 = from p in db.Degrees select p;
Console.WriteLine("Fired sql:");
Console.WriteLine(db.GetQueryText(q2));
Console.WriteLine("Result:");
Console.WriteLine("DEGKEY - DEGDATE - DEGCELCIUS");
foreach (var v in q2)
{
Console.WriteLine(v.DEGKey + " - " + v.DEGDate + " - " + v.DEGCeLcIUs); // For some reason DbMetal makes a very cryptic property name
}
Console.WriteLine("------");

Console.WriteLine("Insert temperature 1:");
Degrees a = new Degrees();
a.DEGKey = 1;
a.DEGDate = System.DateTime.Now;
a.DEGCeLcIUs = 1;
db.Degrees.InsertOnSubmit(a);
System.Threading.Thread.Sleep(5000);
Console.WriteLine("Insert temperature 2:");
Degrees b = new Degrees();
b.DEGKey = 2;
b.DEGDate = System.DateTime.Now;
b.DEGCeLcIUs = 2;
db.Degrees.InsertOnSubmit(b);
System.Threading.Thread.Sleep(5000);
Console.WriteLine("Insert temperature 3:");
Degrees c = new Degrees();
c.DEGKey = 3;
c.DEGDate = System.DateTime.Now;
c.DEGCeLcIUs = 3;
db.Degrees.InsertOnSubmit(c);

db.SubmitChanges();
Console.WriteLine("------");

Console.WriteLine("Select all temperatures:");
var q3 = from p in db.Degrees select p;
Console.WriteLine("Fired sql:");
Console.WriteLine(db.GetQueryText(q3));
Console.WriteLine("Result:");
Console.WriteLine("DEGKEY - DEGDATE - DEGCELCIUS");
foreach (var v in q3)
{
Console.WriteLine(v.DEGKey + " - " + v.DEGDate + " - " + v.DEGCeLcIUs); // For some reason DbMetal makes a very cryptic property name
}
Console.WriteLine("------");

Console.WriteLine("Select all temperatures higher than 1 and add 5 to these:");
var q4 = from p in db.Degrees where p.DEGCeLcIUs>1 select p;
Console.WriteLine("Fired sql:");
Console.WriteLine(db.GetQueryText(q4));
Console.WriteLine("Result:");
Console.WriteLine("DEGKEY - DEGDATE - DEGCELCIUS");
foreach (var v in q4)
{
Console.WriteLine(v.DEGKey + " - " + v.DEGDate + " - " + v.DEGCeLcIUs); // For some reason DbMetal makes a very cryptic property name
}
foreach (var v in q4)
{
v.DEGCeLcIUs+=5;
}
db.SubmitChanges();
Console.WriteLine("------");

Console.WriteLine("Select all temperatures:");
var q5 = from p in db.Degrees select p;
Console.WriteLine("Fired sql:");
Console.WriteLine(db.GetQueryText(q5));
Console.WriteLine("Result:");
Console.WriteLine("DEGKEY - DEGDATE - DEGCELCIUS");
foreach (var v in q5)
{
Console.WriteLine(v.DEGKey + " - " + v.DEGDate + " - " + v.DEGCeLcIUs); // For some reason DbMetal makes a very cryptic property name
}
Console.WriteLine("------");

}
}

[/sourcecode]

Compile and run:

csc /r:dblinq.dll,dblinq.oracle.dll *.cs

oracletest:
Select and delete all temperatures:
Fired sql:
SELECT p$.DEGCELCIUS, p$.DEGDATE, p$.DEGKEY
 FROM LINQ.DEGREES p$
Result:
1 - 5/22/2008 7:07:48 PM - 1
2 - 5/22/2008 7:07:53 PM - 2
3 - 5/22/2008 7:07:58 PM - 3
------
Select temperatures (none):
Fired sql:
SELECT p$.DEGCELCIUS, p$.DEGDATE, p$.DEGKEY
 FROM LINQ.DEGREES p$
Result:
DEGKEY - DEGDATE - DEGCELCIUS
------
Insert temperature 1:
Insert temperature 2:
Insert temperature 3:
------
Select all temperatures:
Fired sql:
SELECT p$.DEGCELCIUS, p$.DEGDATE, p$.DEGKEY
 FROM LINQ.DEGREES p$
Result:
DEGKEY - DEGDATE - DEGCELCIUS
1 - 5/22/2008 7:10:14 PM - 1
2 - 5/22/2008 7:10:19 PM - 2
3 - 5/22/2008 7:10:24 PM - 3
------
Select all temperatures higher than 1 and add 5 to these:
Fired sql:
SELECT p$.DEGCELCIUS, p$.DEGDATE, p$.DEGKEY
 FROM LINQ.DEGREES p$ WHERE p$.DEGCELCIUS > 1
Result:
DEGKEY - DEGDATE - DEGCELCIUS
2 - 5/22/2008 7:10:19 PM - 2
3 - 5/22/2008 7:10:24 PM - 3
------
Select all temperatures:
Fired sql:
SELECT p$.DEGCELCIUS, p$.DEGDATE, p$.DEGKEY
 FROM LINQ.DEGREES p$
Result:
DEGKEY - DEGDATE - DEGCELCIUS
1 - 5/22/2008 7:10:14 PM - 1
2 - 5/22/2008 7:10:19 PM - 7
3 - 5/22/2008 7:10:24 PM - 8
------

Have fun...

13 comments:

  1. Your code view is nice, how you do it?

    ReplyDelete
  2. I tag the code with the following tag (the tag is surrounded by square brackets but I can't type them because then it get interpreted:):

    sourcecode language="C#"

    and

    /sourcecode

    ReplyDelete
  3. [...] Linq for Oracle: ---------------- http://broersa.wordpress.com/2008/05/22/linq-for-oracle-sample/ [...]

    ReplyDelete
  4. Hello Andre,

    One question: Did you try DBLinq DBMetal especially with oracle enterprise server instead of express edition?

    I have been trying that for a while now. but I keep getting an error.

    ReplyDelete
  5. Thank you, just what I was looking for!

    -Joe Marinaccio

    ReplyDelete
  6. I get errors too on enterprise, did you ever fix it?

    ReplyDelete
  7. hi,
    I am using Oracle 10g enterprise edition for create linq.cs file from command prompt but it will given an error when i run dnMetal.exe.
    Error:

    DbMetal failed:System.IO.FileNotFoundException: Could not find file 'D:TestProj
    ectDbLinq-0.18buildSource=ora123;User'.
    File name: 'D:TestProjectDbLinq-0.18buildSource=ora123;User'
    at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
    at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, I
    nt32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions o
    ptions, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
    at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access,
    FileShare share)
    at DbMetal.Generator.Implementation.Processor.ReadSchema(Parameters parameter
    s, String filename) in D:TestProjectDbLinq-0.18srcDbMetalGeneratorImplemen
    tationProcessor.cs:line 228
    at DbMetal.Generator.Implementation.Processor.ReadSchema(Parameters parameter
    s, ISchemaLoader& schemaLoader) in D:TestProjectDbLinq-0.18srcDbMetalGenera
    torImplementationProcessor.cs:line 197
    at DbMetal.Generator.Implementation.Processor.ProcessSchema(Parameters parame
    ters) in D:TestProjectDbLinq-0.18srcDbMetalGeneratorImplementationProcess
    or.cs:line 100

    please help me to overcome this error.
    Thanks in advance.
    Chandan

    ReplyDelete
  8. Hi Chandan,
    I have similar error message. with oracle 11g. Did you find the solution? If you have please help me.

    Thanks
    Hguled

    ReplyDelete