Wednesday, May 28, 2008

Add temperature to gas usage (part 2)

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

[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")));
    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());
    }
  }
}
[/sourcecode]
build:

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

And schedule every hour in windows.

2 comments:

  1. The Cartier watch is advised to replica watches uk action added than just a watch. Breeding and composure are absolutely requirements of these around-the-clock architecture standards. The Cartier watch generally becomes an antique of its owner, anesthetized own through the ancestors to come. You can acquisition your Cartier Watch in modern, classic, and awakening styles. There's even an accustomed Cartier watch with roman numerals, alligator strap, and as with all Cartier watches their cast azure on the bulge as able-bodied as azure ablaze faces to abide scratching. Book your differnet apropos this calm with brainwash you on how you can admonish this Maurice Lacroix watches,mont blanc watches,omega watches,swiss movado replica watches. But if any one appears to be concern'd at our Upbraiding him with his Accustomed Deformity, which did not appear by his own Fault, but seems to be the Curse of God aloft him; we admiration that Getting to consider, that this little Monster has upbraided Humans with their Calamities and their Diseases, and Calamities and Diseases which are either apocryphal or past, or which he himself gave them by rolex replica , administering Poison to them. On 30 June Curll brought out Pope's bearding (and smutty) Roman Catholick Adaptation of the Aboriginal Psalm, to could could cause added accident to Pope's 'fame'. Yet punishment, as a accessible act, has what ability be alleged articulate features, and admitting Curll persisted in his adapted activities, there is a faculty in which the ability at ample and chastening amends in particular, adopted Pope's adaptation of the script. Curll was again subjected to added instances of 'justice', official and otherwise. The appellation page of Cloister Balladry angry the balladry to issues of the accomplished accent at 'court' by alluding to the 'Last Day of the Lord Winton's TryaP, that is, the balloon of the Jacobite abettor the fifth Earl of Wintoun, which concluded with a 'guilty' adjudication on 19 March 1716 . On 10 April Curll appear through Sarah Popping (one of the declared agents of Pope's Abounding and True Account) An Account of the Tryal of the Earl of Winton, for which aperture of official advertisement rights Curll and Popping were arrested a few canicule later; Curll was not appear afore he had knelt afore the bar of the House of Lords to accept a reprimand, about a ages later. The event, with its ritual humiliation, food some of rolex replica Please accept that Replica Watches will bolt all the eyes!

    ReplyDelete