Wednesday, May 28, 2008

Add temperature to gas usage (part 2)

Wednesday, May 28, 2008 Posted by Andre Broers , , , , , ,
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

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

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:

[sourcecode language="c#"]

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")));
    // 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();

    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
    catch(Exception ex) // catch all exceptions so the batch continues

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

And schedule every hour in windows.