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.

Add temperature to gas usage (part 1)

Wouldn’t it be nice if we can extend the previous ‘datawarehouse’ with the temperatures? so we can compare the gas (heating) or electricity (airco) usage with the current or average day temperature.

Let’s create a .net application which gets the current temperature from a webservice every hour and adds them to a new fact table. We gonna use Windows Communication Foundation and Linq in this example.

In this part we gonna create a webservice client to the weatherservice. In the next part we gonna add the linq part to load the temperature in the oracle database.

I found a pretty nice webservice that is free and also has the european weather:

http://www.webservicex.net/globalweather.asmx

First thing is to create a stub for the webservice:

svcutil.exe http://www.webservicex.net/globalweather.asmx?wsdl

This creates a GlobalWeather.cs stub and an output.config file which contains the bindings.

Ignore the errors, this is because there are other than soap ports in the wsdl.

rename the output.config to degrees.exe.config

create the client degrees.cs:

using System;
using System.Web.Services.Protocols;
using System.Net;
using System.Xml;
using System.Text.RegularExpressions;
public class SetCoverImage
{
public static void Main (string [] args)
{
GlobalWeatherSoapClient w = new GlobalWeatherSoapClient();

// The service returns a plain string
Console.WriteLine(w.GetWeather(“Groningen”,”Netherlands”));

// 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(); Console.WriteLine(Regex.Replace(Regex.Replace(reader.Value,”^.*\\(“,”"),” C\\).*$”,”"));
}
if (reader.NodeType==XmlNodeType.Element && reader.Name==”Status”)
{
reader.Read(); Console.WriteLine(reader.Value.Trim());
}
}
reader.Close();
}
}

csc degrees.cs GlobalWeather.cs

degrees.exe

<?xml version="1.0" encoding="utf-16"?>
<CurrentWeather>
  <Location>Groningen Airport Eelde, Netherlands (EHGG) 53-08N 006-35E 4M</Location>
  <Time>May 23, 2008 - 07:25 AM EDT / 2008.05.23 1125 UTC</Time>
  <Wind> from the NE (050 degrees) at 8 MPH (7 KT) (direction variable):0</Wind>
  <Temperature> 69 F (21 C)</Temperature>
  <DewPoint> 39 F (4 C)</DewPoint>
  <RelativeHumidity> 32%</RelativeHumidity>
  <Pressure> 30.00 in. Hg (1016 hPa)</Pressure>
  <Status>Success</Status>
</CurrentWeather>
21
Success

The service returns the temperature in my region.

Next step is to load it into the database. (see part 2)