Linq to Oracle Autogenerated Key

We build on previous sample. In this example we add an autogenerated primary key. This is done by adding a sequence and an insert trigger in oracle:

SQL> create sequence degrees_seq
  2  start with 1
  3  increment by 1
  4  nomaxvalue;

Sequence created.

SQL>
SQL> create trigger degrees_trigger
  2  before insert on degrees
  3  for each row
  4  begin
  5  select degrees_seq.nextval into :new.degkey from dual;
  6  end;
  7  /

Trigger created.

SQL>

As we can see in linq.cs generated file the column DEGKey is attached with the IsDbGenerated = true attribute. This says that the .Net environment knows the column is updated from the database, which is what we want.

Now we get a new sample oracletest2.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("Insert temperature 1:");
     Degrees a = new Degrees();
     a.DEGKey = 1; // Must be set but doesn't matter because is database generated
     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 = 1; // Must be set but doesn't matter because is database generated
     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 = 1; // Must be set but doesn't matter because is database generated
     c.DEGDate = System.DateTime.Now;
     c.DEGCeLcIUs = 3;
     db.Degrees.InsertOnSubmit(c);
     Console.WriteLine("Key values before submit:");
     Console.WriteLine("a.DEGKey:"+a.DEGKey);
     Console.WriteLine("b.DEGKey:"+b.DEGKey);
     Console.WriteLine("c.DEGKey:"+c.DEGKey);
     db.SubmitChanges();
     Console.WriteLine("Key values after submit (Should be synchronized but are not)");
     Console.WriteLine("a.DEGKey:"+a.DEGKey);
     Console.WriteLine("b.DEGKey:"+b.DEGKey);
     Console.WriteLine("c.DEGKey:"+c.DEGKey);

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

     Console.WriteLine("Select all temperatures (now the keys are synced): ");
     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("------");
   }
 }
[/sourcecode]
Mind the comments in the code. A flaw in dblinq is that on the SubmitChanged the primarykey field should be updated which aren't. For the rest it works nice.

build:

csc /r:dblinq.dll,dblinq.oracle.dll linq.cs oracletest2.cs

and run:

oracletest2.exe

have fun..