Wednesday, March 04, 2015

SQLite Administration

SQLite is a fast and compact relational database supported across many platforms (including tablets).

The SQLite web page at says:

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.

Which says it all.

In many ways it is the first choice for a database in any circumstance where professional management is not required and where network latency is unlikely to become an issue. One of the benefits of using a relational database as an application file format (and there are more than several) is that you can access the data easily from a generic tool. Which brings me to SQLite administration tools.

I have used the excellent SQLiteAdmin tool for a long time. However I just hit a snag – this tool does not seem to support Unicode – while SQLite does (of course). I needed to add some Greek and Bulgarian characters to a data set and I could nor persuade SQLiteAdmin to import a Unicode encoded CSV file with them included.

The SQLiteAdmin web page is dated 2006 although the download zip file content is dated October 2007. I did look at the program source location ( mentioned on the web page but could not locate the code – so tweaking things to support Unicode (and Unicode filestreams) looked like a non-starter. The English language support forum link is also dead.

I turned up an alternative tool that seems to be delivering what I need however. SQLite Browser on github is an active project . So far, this tool seems to deliver all I require. Recommended and available for Windows, MacOSX, Linux and FreeBSD.

Look Mum – no Office…

I was in the process of porting and updating some venerable VB coded software components to C# the other day. There was a multi-page print routine, a multi-column list sorter and a widget to export data from a ListView or DataGrid to Excel or a CSV file. I took a quick look to see if the more recent versions of .NET had anything to add to the process when I came across mention of the Microsoft Open XML SDK supplied through the DocumentFormat.OpenXml.dll.

You can use the Package Manager in Visual Studio to get the dll via nugget

PM> Install-Package DocumentFormat.OpenXml

Which is cool but I would advise looking for the relevant page in the Microsoft Internet Download Centre and grab a copy of the tools installer (OpenXMLSDKTool.msi). This installs some (pretty poor) documentation but can also come in very useful for analysing existing documents.

As I was looking at exporting an Excel file first of all, I started there. Picking up a few clues on the way from There you can also download a fully coded solution rather cleverly based upon .NET DataSets – which would allow the almost direct export of data returned from a database to Excel. In any case the post is recommended as the techniques are readily adaptable to alternate data sources. One thing I might suggest is changing the file encoding to Encoding.Unicode from Encoding.ASCII as a default.

Implementing an export to Excel routine that did not require the presence of a licensed copy of office on the relevant machine was a win as far as I was concerned. Now it also happened that I was looking at outputting some documents from a code base. Creating a PDF is pretty steady and covers a lot of the requirement but I wondered if outputting a Word .docx file could be a bonus alternative.

A first look at the documentation at (almost bound to be a failed link within days but what can you do) did not thrill me. It looked complex with a vast number of objects to be created and manipulated. This is where the Open XML tool comes into play. If you create and save a Word document using Office you can open the file created using this tool. If you then click the “Reflect Code” menu strip option you can view the code required to produce the document – or any sub component of the document.  If you copy the code and paste it into your project it will run and can re-produce your original Word document perfectly. You may have to resolve some Object naming clashes ( Font and Color) if you paste the code into a Windows form class but you have a ready-made solution ready to run.

You will probably be initially horrified at the repetitive code – just how many times does the same font (and indeed the same colours) need to be defined? [Some may recall the days when folks occasionally used Word to generate a web page’s HTML – the effect is similar.] What you are seeing is of course a strategy that copes well with the user controlled document design process within Word and one that ensures precision when a document comes to be rendered – OK, with some redundancy.

And yes – it will offend any programmer’s soul where the context is the preparation of a particular document layout. So prune away.

The key thing to take away here is that Microsoft have provided another open tool that can be used to interact with the MS ecosystem – and in this instance – with the de facto standard of the Office suite of programs.

Friday, December 05, 2014

A world of change

The MSDN blog post on the introduction of .NET Core is very interesting and lays out quite a bit of the future .NET strategy.

I did find one section particularly entertaining though:

Although it doesn’t use .NET Native one of the goals of the new ASP.NET 5 web stack was to provide an XCOPY deployable stack so that web developers don’t have coordinate with their IT department in order to take dependencies on later versions."

Says so much about the conflicts and arcane processes of so many corporate IT departments.

Friday, November 14, 2014

Updating Skills

It was past time to run an update on Android skills. The last serious app I built was back when Android 2.2 (API version 8) was current and Google have just shipped Android 5 Lollipop and my test devices are running 4.4 Kitkat (API 19) in the main. There has been quite a lot of change and platform development over that time lapse.

Like any platform that has endured rapid development over a (relatively) short timescale things have sort of built up in layers. There are new approaches (things like fragments) overlaid on top of the old with new techniques to be applied to take advantage of the performance and functionality benefits accruing from each development. You also see a lot of yellow underlined code appearing in the Eclipse IDE reflecting deprecated classes – some just a bit mystifying, particularly where something simple looks to have been made unnecessarily complex *.

Having actively worked with .NET since the first usable version (1.1) and seen a lot of similar evolution in the Windows desktop and web application spaces I have to say the Microsoft managed this better overall. I suppose MS started with the advantage of building a fresh foundation rather than inheriting so much from Java.

On the bright side, the Eclipse IDE has improved** and gets excellent support for Android development – Android lint is pretty good and the “intellisense” mostly helpful – although at the occasional risk of assuming the “wrong” module to include (e.g. Android.Graphics.Camera and Android. Hardware.Camera which are not the same at all and we also haveAndroid.hardware.camera2 now available). Eclipse is still a long way from Visual Studio (the touchstone of IDEs?) and I did have a bash at getting Android Studio to work but failed to get anything actually running. Android Studio gets nice press but is still at a “point” release. Based upon JetBrains’ IntelliJ IDE one might have expected better progress since the May 2013 preview launch but to be fair targeting simultaneous implementations on Windows, Mac and Linux is quite a challenge. In the meantime Eclipse is quite good enough.

What I have found interesting about the Android skills revamp is how little attention I need to give to the Java language syntax. After a longish period working in C# and JavaScript there is almost no mental dissonance when switching between the languages themselves – although the environments do of course have a significant impact on what gets written in code. Which sort of brings me to Apple’s Swift.

When Swift was announced by Apple (last May I think) I grabbed a copy of the launch eBook and took the time to read the guide to the beta version. I understand that there have since been some language revisions and improvements but I was immediately taken with the overall approach. This was clearly a vast improvement on Objective C.

Here at Adit we established and nurtured iOS development skills more than three years ago. Big question I suppose we have to face now is: should we switch languages from Objective C to Swift? Given that the iOS platform represents the dominant slice of development focus this should be no more complex that switching between (say) Visual Basic and C# on .NET. There is also the potential to further reduce dissonance between the programming languages in use – although there are a few caveats. Apple is apple and their language development team seem on the face of it to have decided against certain de facto standards to ensure that their new baby has that certain Cupertino style.

The Swift class constructor is a method named init – rather than following the convention of a public method with the same name as the class. The keyword “self” is used where other languages use “this” to refer to the current instance. [I know that JavaScript newbies get in a mess with “this” but changing the name hardly introduces clarity.]

Include values in strings with a backslash – frankly an improvement over the convention but different…

Declaring a function:

func isPositive(number : Int)  Bool {}

as Swift does is just as comprehendible as

Bool isPositive(Int number) {}

which is the “convention” and one would have thought just as parsable (is that a word?) – so why be different? Given the way variables are declared in Swift even:

Bool isPositive(number : Int) {}

would have been good.

But this is cool:

func returnPrevAndNext(x: Int) -> (Int, Int) {
    return (x - 1, x + 1)
} and has the potential to avoid the overhead of clumsy return structures.

I quite like the Swift idea that variable types should be inferred although I suspect that I would get into the habit of being explicit (just as I tend to be with JavaScript these days when passing variables to functions or in concatenation). You use “var” for mutable variables and “let” for immutable variables – although immutable objects have mutable “content” (class properties and array element content for instance).

More positive point, Swift generic classes are nicely implemented and overall issues such as those highlighted are just minor carping – I just wonder though, that Apple may have made context switching between platforms just a tiny bit more complex than was necessary.

     *Example: many methods on the Date() object are now deprecated. So mDate.getYear() which returned an integer in an idiot proof and self-documenting manner now requires something like

cdate = Calendar.getInstance();
return cdate.get(Calendar.YEAR);

And OK, you would not actually write this code and we are probably supposed to be using Calendar not Date but…

     ** That understates it – Android development support in Eclipse has improved enormously. Someone has done a brilliant job and I notice that the odd couple of things that Eclipse does better look to have been incorporated into the Visual Studio 2015 preview – I found myself saying “Oh, Eclipse does that.”

Wednesday, September 24, 2014

ASP.NET Web Forms Routing for web services

I have written up a few notes on this as I hit a few issues with routing and I can see on sites like StackOverflow others have had issues as well.

Let’s start with the requirement:

You want to provide a web site that allows you to provide URLs that are meaningful to the end user and that can be resolved and routed to one or more selected pages. Your user might have a URL like

As the owner of, you want to route that request to (say) an aspx page called customer.aspx and pass the string “particularvariant” to that page to use in deciding on the content to display or services to offer. This part of the requirement is well documented.

Using Visual Studio you can add a “Global Application Class” Global.asax if there is not one already in your project. Then you just need to add the following code to the Global.asax.cs code file in the ApplicationStart method:

protected void Application_Start(object sender, EventArgs e)

Then add the RegisterRoutes method to the same file. It should contain the line required to add the routing like the one below.
        void RegisterRoutes(RouteCollection routes)
            routes.MapPageRoute("", "specialfunction/{userneed}", "~/customer.aspx");

A check on the MSDN documentation and/or a quick Google will supply plenty of alternate patterns for registering more complex URLs with routes to specific pages and with varying numbers of parameters but this example will suffice for this post. A request for the URL we wanted will now be routed to customer.aspx and the code page for that page can access the second element of the request (particularvariant) in the Page_Load method by interrogating the Page.RouteData.Values dictionary using the key(s) specified in the MapPageRoute method thus:

string userWants = (string)Page.RouteData.Values["userneed"];

That works fine – the required page will be served to the browser in exchange for our example request URL. The first snag you will probably hit will be that when the customer.aspx page loads in the browser any specified JavaScript and CSS files will have failed to load. If you use the browser developer tools (in Chrome maybe) you will see that the browser attempted to load these resources from the “wrong” URL. Maybe looking for a JavaScript file at ~/specialfunction/jscript/ rather than just ~/jscript/ or a css file at specialfunction/css/ rather than in the css subfolder from the root.
The css file is the simplest to solve – just specify the whole path. Something like:

<link href="~/css/bootstrap.min.css" rel="stylesheet" />

And it will be correctly resolved. This does not seem to work with JavaScript files and the best approach here is to add a scriptmanager <tag> to the web page (inside the <form> tags) and add the required scripts like:

    <asp:scriptmanager runat="server">
            <asp:ScriptReference Path="jscript/jquery-2.1.1.min.js"/>
            <asp:ScriptReference Path="jscript/standard.js"/>
The JavaScript files will then be correctly loaded into the browser.

Now we get to the fun bit. Suppose you have web services served from an asmx object and you want to call web services using the jQuery $.ajax() method. You might want to make a call to a notional GetCust(Int32 CustID) web method served by OurAPI.asmx with a call like this:

        type: 'POST',
        url: 'OurAPI.asmx/GetCust',
        data: '{CustID: 1}',
        contentType: 'application/json; charset=utf-8',
        dataType: 'json',
        processData: true,
        success: getCustBack,
        error: onError

As you will probably new expect, you will get an error as the “/specialfunction/OurAPI.asmx” service can’t be found. So you pop back to the Global.asax.cs file to add a new routing.

But while the routes class has a MapPageRoute() method it hast no MapServiceRoute method – which is surprising in a mature environment like .NET 4.5. However there is an Add() method but we need to create something to be added. To do this we need to create a new class that inherits from the .NET IRouteHandler class. I based mine upon a StackOverflow post ( and that goes like this:

public class ServiceRouteHandler : IRouteHandler
        private readonly string _virtualPath;
        private readonly WebServiceHandlerFactory _handlerFactory = new WebServiceHandlerFactory();

        public ServiceRouteHandler(string virtualPath)
            _virtualPath = virtualPath;

        public IHttpHandler GetHttpHandler(RequestContext requestContext)
            return _handlerFactory.GetHandler(HttpContext.Current, requestContext.HttpContext.Request.HttpMethod, _virtualPath, requestContext.HttpContext.Server.MapPath(_virtualPath));

Although you might want to add some guard code for the virtualPath parameter.

Now you can add an additional line to the RegisterRoutes method. Something like:

void RegisterRoutes(RouteCollection routes)
            routes.Add("reroutapi", new Route("specialfunction/OurAPI.asmx", new RouteValueDictionary() { { "controller", null }, { "action", null } }, new ServiceRouteHandler("~/OurAPI.asmx")));
            routes.MapPageRoute("", "specialfunction/{userneed}", "~/customer.aspx");

And feeling justly proud you will give your code a spin to call the web service method and it still will not work. The fix is simple but subtle and took me far too long to figure out (read, make random changes until I got it).

Make a small change to the url parameter in the object passed to $.ajax(). A forward slash in front of OurAPI.asmx thus:

        type: 'POST',
        url: '/OurAPI.asmx/GetCust',

Then the routing will work and the call will be serviced.

So that is how you get asmx web service routing to work from jQuery $.ajax() with Web Forms in ASP.NET


Thought I might revive this blog (after a gap of some 7 years) to post a few items of a more technical bent. The old stuff has been flushed as it had little relevance in 2014 and the "opinion" stuff did not really catch an audience.