Category Archives: .Net

Seeking a New Contract

I’ve just finished a highly-challenging and intense contract and I’m now looking for a new short contract.

I’ve got a lot of experience and mainly on the Microsoft stack of .Net, ASP.Net, C# & SQL Server.

I’m flexible on time from 1 day per week and at reasonable rates.

I do remote working but the occasional over-night stay will be fine. Prefer contracts in the Greater Manchester area.

If you’re needing some help on a project then do get in contact. I am on LinkedIn.

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.

 

Export a Gridview in iTextSharp

PDF reports are a wonderful way of delivering content to users. And there are a lot of great tools out there to create them. The tool I use the most is iTextSharp, the .Net port of the Java iText PDF creation and manipulation tool.

One the most popular controls in ASP.Net to display data is the GridView. In this entry I will discuss one method of converting a Gridview to PDF in one quite simple step. I will even discuss how to take it one step further.

Continue reading Export a Gridview in iTextSharp

First go at AJAX and ASP.Net

There are two way to do dynamic page update without a post-back in ASP.Net. There is the much vaunted UpdatePanel with all its overhead. No , it works just fine within your own application. Though make ultimate use it would require several UpdatePanels on a page to make a fantastic application. The other way is to use the AJAX calls within jQuery to either your own webservice or a 3rd party webservice. Certainly when building your own application the UpdatePanel is the way to go. But AJAX can still be used though you need to take care not to page refresh when using it. Its certainly a useful option when dealing with 3rd party webservices to display information. So how is this done? Continue reading First go at AJAX and ASP.Net

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!