Tag Archives: XML

Wunderground API via .Net

Intro

For those using WunderGround for weather, you probably are pulling your hair out. Not because it is a terrible service. But more likely because there is so little documentation. It is definitely one service that could use a technical writer to organise and tidy things up. For example, there is a  complete lack of documentation on errors. And there is no sample to show how to access WunderGround from .Net

The Sample

I was doing some work for a client and they had this need to download historic code from Wunderground for the town their clients have operations in. The weather relates to retail sales and footfall and can obviously be used to determine is a slow day was caused by weather.

On the WunderGround API website there are a few samples. One in Ruby, and even in (gasp) ColdFusion. But nothing in .Net. Seemed a bit odd given the popularity of .Net.

After a bit of rooting around I managed to bolt together this code – excuse the VB.Net my client prefers that I use VB.

Imports System.Xml.Linq
Imports System.Xml

Dim sWunderGroundURL As String = "http://api.wunderground.com/api/{0}/history_{1:yyyyMMdd}/q/{2}.xml"
Dim MyApiKey as String = "anAPIKeyHere"
Dim MyDate as DateTime = #8/1/2012#
Dim MyTown as String = "UK/London"

Dim root As XElement = XElement.Load(String.Format(sWunderGroundURL, MyApiKey, MyDate, MyTown))

'Check for an error
Dim errors As IEnumerable(Of XElement) = _
 root...<error>

If errors.Count > 0 Then
 nErrorCount += 1
Else
'Do your processing here
End If

The code is fairly straight forward but note that I am using the xml interface and not JSON. I like to use Linq to XML so rather than use xmlDocument, you use XElement. You load the URI into the XML then check for an error element. If there is one I log or print out an error otherwise do my further processing.

You could use the JSON interface if you wished. You would have the serialize in the JSON and then process it. And extra step I didn’t feel was worth it.

Conclusion

There you have it a very simple sample on connecting to WunderGround using .Net. I may submit this to the nice fellows at WunderGround – but as I like hits to this site..maybe not.

 

Parameterise a IN clause in T-SQL

Parameters are a great feature in any SQL implementation. You can protect yourself – and more likely – your clients from SQL injection by always using parameterised queries for every call to the database.

I’ve seen some implementations in e-commerce packages where SQL injection would be a breeze.

The Scenario

For a recent client I had a .Net app which was to query records but exclude a set from a specific set of criteria. Normally this is either done with a join or an IN clause. In this instance as I am passing the criteria by parameter I needed a IN Clause.

A IN clause is used to either include or exclude records from a result. An example would be:

Select * from TableA where TableA.name IN ('John','Frank','Dave')

This would include only those rows where column name matched one of ‘John’ or ‘Frank’ or ‘Dave’.

The Problem

Now there is no current way to send a array of items through a parameter. So your choices are:

  • Invoke the query once for each criteria item, or
  • Use dynamic SQL

Both of these are highly inefficient, to say the least.

The Solution

Now before you start pulling your hair out, there is a simple and elegant solution. In T-SQL you can use XML, yes I did write XML that is not a typo.

Here’s how

In T-SQL you can have script which looks like:

Declare @x xml
@x = '<L>
        <I>John</I>
        <I>Frank</I>
        <I>Dave</I>
     </L>'

Select * from TableA where TableA.name in (
     Select * from ( select T.c.value('.','uniqueidentifier') as source
     from @x.nodes ('/L/I') T(c) ) Idss )

The inner query converts the XML into a result set by breaking out the individual <I> nodes within the <L> node into logical rows. The ‘T.c.value’ then accesses the logical row and extracts the text within. Finally this is presented as a subquery result named Idss (apparently that part is important otherwise you’ll get an error).

Now all you would need to do is to place your XML in the @x parameter. I chose a parameter type of string as that was simpler to handle.

Conclusion

The XML solution is quite elegant requiring minimal effort on the client side to pass the parameters to the server. This is much faster than dynamic SQL, as the server store the plan away and will save you recompiles on the server – always a performance killer.

You’ve seen how simple it is to specific a list of values for an IN clause as a parameter. A lot simpler than either of thought!