Add temperature to gas usage (part 2)

After part 1 it is time to load the data in the database. We will use linq for it. In a previous article I I have shown how to get and build linq to oracle. With that in mind, we start creating the table. After this we create the linq proxy classes using dbmetal. And finally the code to degrees process.

Let’s start with the table:

drop table deg
/

create table deg
(
  degkey    number not null,
  degdat    number not null,
  degtim    number not null,
  degcelcius    number not null,
  constraint deg_pkey primary key (degkey),
  constraint degdat_fkey foreign key (degdat) references dat (datkey),
  constraint degtim_fkey foreign key (degtim) references tim (timkey)
)
/

drop sequence degkey_seq
/

create sequence degkey_seq
  start with 1
  increment by 1
  nomaxvalue
/

create or replace trigger degkey_trigger
  before insert on deg
  for each row
  begin
    select degkey_seq.nextval into :new.degkey from dual;
  end;
/

After this we use dbmetal to generate the helper classes:

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

The problem here is that the databasename is the same as a tablename in the schema. To solve this edit the gwe.cs file and change the DataContext name to GWE (all capitals). Also change the constructors to GWE.

Then the code to the degrees.cs:

using System;
using System.Web.Services.Protocols;
using System.Net;
using System.Xml;
using System.Text.RegularExpressions;

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

public class Degrees
{
  public static void Main (string [] args)
  {
    try {
    string degrees=”";
    string success=”";
    GlobalWeatherSoapClient w = new GlobalWeatherSoapClient();

    // We load the string in an xml reader to parse it and find the temperature
    XmlReader reader = XmlReader.Create(new System.IO.StringReader(w.GetWeather(“Groningen”,”Netherlands”)));
    reader.MoveToContent();
    // Parse the file and find the Temperature element its value
    while (reader.Read())
    {
      if (reader.NodeType==XmlNodeType.Element && reader.Name==”Temperature”)
      {
        // Do some regex to find the temperature in celcius
        reader.Read(); degrees = Regex.Replace(Regex.Replace(reader.Value,”^.*\\(“,”"),” C\\).*$”,”");
      }
      if (reader.NodeType==XmlNodeType.Element && reader.Name==”Status”)
      {
        reader.Read(); success = reader.Value.Trim();
      }
    }
    reader.Close();

    System.Console.WriteLine(degrees + success);

    if (success==”Success”) {
      System.DateTime d = System.DateTime.Now;

      string connStr = “User Id=gwe;Password=gwe;Data Source=XE”;

      GWE db = new GWE(new OracleConnection(connStr));
      // get datkey
      var q1 = (from p in db.Tims where p.TimHour==d.Hour select p).Single();
      var q2 = (from p in db.Dat where p.DatYYYYMmDd==(d.Year*10000)+(d.Month*100)+d.Day select p).Single();
      DEG deg = new DEG();
      deg.DEGKey=1; // dummy
      deg.DEGCeLcIUs=Convert.ToDecimal(degrees);
      deg.DEGdAt=q2.DatKey;
      deg.DEGTim=q1.TimKey;
      db.DEG.InsertOnSubmit(deg); 
      db.SubmitChanges();
    }
    }
    catch(Exception ex) // catch all exceptions so the batch continues
    {
      Console.WriteLine(ex.ToString());
    }
  }
}

build:

csc /r:dblinq.dll,dblinq.oracle.dll degrees.cs gwe.cs GlobalWeather.cs

And schedule every hour in windows.

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:

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(“——”);
   }
 }

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..

Linq for Oracle sample

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 bin\Debug

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:\dotnet\oracle) and copy the linq.cs, DbLinq.dll and DbLinq.Oracle.dll to this directory.

Create the test program oracletest.cs:

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(“——”);

}
}

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…