Find text in stored procedures on your SQL Server Database

tres handy..

select * 
from syscomments
where [text] like '%ASPStateTempApplications%'

 -- > SQL 2005
select * from sys.sql_modules
where definition like '%ASPStateTempApplications%'

No Comments

Powershell to write bunch of data from sql to the file system

I recently had need to write a bunch of xml we had stored in sql out to a bunch of files. So we have one xml file per row, and I wanted to out put these into a folder, each one in it’s own file. After some Googling I got this working;

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Data Source=localhost;Initial Catalog=testDB;User Id=sa;Password=thepassword;Persist Security Info=True”
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = “select top 500 id, [xml] from xmlLog order by id desc”
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
for ($i=0;$i -le $DataSet.Tables[0].Rows.Count – 1;$i++) { $DataSet.Tables[0].Rows[$i][1] >> (“c:\xml\file” + $i + “.xml”) }

This gave me a folder called xml in my c:\ containing 500 xml files!

No Comments

Executing long running process from a web site

I recently had to build a report which accepts one xls excel file, churns through a bunch the data looking stuff up on other systems and performing calculations, then spits out another xls excel sheet the other end. The whole process takes about 15 to 20 minutes to run and at present is a simple command line application which accepts a couple of parameters (input and output filenames) and I fire up manually- obviously a situation which is no good moving forward.

All our internal business systems are web forms/ mvc running from local IIS boxes so ideally I would like to add a screen where the user can submit their xls file, hit go, and then get the result back 15/ 20 minutes later- but I don’t want to tie up one of the threads in the aspnet worker thread pool for the entire duration. Some Googling revealed the best approach appears to be to write a windows service which hosts the long running process then get the web app to call across to that to kick off the task.

Implementation

This is to be a standard Windows Service which will run my long running process, hosting a WCF service which the website can use to call into it with. If you;ve not done this before, you can follow the steps on the MSDN ; msdn.microsoft.com/en-us/library/ms733069.aspx.

Create your WCF Service which will respond to requests from the website. In my instance I wanted to be able to start the operation, passing a byte[] containing the xls file, request status updates, then finally request the resulting xls file, again as a byte[]. For the sake of this example, the WCF service is the one which is doing all the work with regards spawning the worker thread and tracking updates from the actual worker class, and the worker class is a type called “Calculator”.

    using System.ServiceModel;
    [ServiceContract(Namespace = "http://Moneybarn.VIVS.FleetRevaluation")]
    public interface IMyWCFService
    {
        [OperationContract]
        string Start(byte[] excel_file, string user_name);

        [OperationContract]
        Progress GetProgress();

        [OperationContract]
        byte[] GetResult();
    }

    using System.Threading;
    public class MyWCFService: IMyWCFService
    {
        private static string CurrentUserInstance;
        private static Calculator MyCalculatorInstance;
        private static Progress LastUpdate;
        private static Thread WorkerThread;
        private static bool IsComplete;
        private static string LastError;
        private static byte[] FinishedFile;

        public string Start(byte[] excel_file, string user_name)
        {
            if (WorkerThread == null)
            {
                CurrentUserInstance = user_name;
                MyCalculatorInstance = new Calculator(excel_file);

                MyCalculatorInstance.OnProgress += new OnProgressEventHandler(MyCalculatorInstance_OnProgress);
                MyCalculatorInstance.OnComplete += new OnCompleteEventHandler(MyCalculatorInstance_OnComplete);
                MyCalculatorInstance.OnError += new OnErrorEventHandler(MyCalculatorInstance_OnError);

                WorkerThread = new Thread(new ThreadStart(MyCalculatorInstance.Start));
                WorkerThread.Start();

                return null; // no news is good news!
            }
            else
            {
                return "Instance already running for user " + CurrentUserInstance;
            }
        }

        public Progress GetProgress()
        {
            return LastUpdate;
        }

        public string GetError()
        {
            return LastError;
        }

        public byte[] GetResult()
        {
            if (IsComplete)
            {
                byte[] buff = FinishedFile;

                MyCalculatorInstance = null;
                WorkerThread = null;
                CurrentUserInstance = string.Empty;
                LastUpdate = null;
                FinishedFile = null;
                IsComplete = false;

                return buff;
            }
            else
                return null;
        }

        private void MyCalculatorInstance_OnError(string error)
        {
            LastError = error;
        }

        private void MyCalculatorInstance_OnComplete(byte[] the_file)
        {
            FinishedFile = the_file;
            IsComplete = true;
        }

        private void MyCalculatorInstance_OnProgress(int stage, float percent)
        {
            LastUpdate = new Progress() { 
                Stage = stage, 
                Percent = (int)(percent * 100) 
            };
        }
    }

Add a new class called ProjectInstaller- this will handle registering your service with Windows

    using System.ComponentModel;
    using System.Configuration.Install;
    using System.ServiceProcess;

    [RunInstaller(true)]
    public class ProjectInstaller : Installer
    {
        private ServiceProcessInstaller process;
        private ServiceInstaller service;

        public ProjectInstaller()
        {
            process = new ServiceProcessInstaller();
            process.Account = ServiceAccount.LocalSystem;
            service = new ServiceInstaller();
            service.ServiceName = "MyWindowsService";  // change this!
            Installers.Add(process);
            Installers.Add(service);
        }
    }

Add the actual service body; add a new “Windows Service” file to the project. This is the meat of the service, and contains no real logic- it’s only job is to fire up the ServiceHost for your WCF service.

    using System.ServiceModel;
    using System.ServiceProcess;

    public class MyWindowsService : ServiceBase
    {
        public ServiceHost serviceHost = null;

        public MyWindowsService()
        {
            // Name the Windows Service
            ServiceName = "MyWindowsService";
        }

        public static void Main()
        {
            ServiceBase.Run(new MyWindowsService());
        }

        // Start the Windows service.
        protected override void OnStart(string[] args)
        {
            if (serviceHost != null)
            {
                serviceHost.Close();
            }

            // The type of your WCF service
            serviceHost = new ServiceHost(typeof(MyWCFService));

            serviceHost.Open();
        }

        protected override void OnStop()
        {
            if (serviceHost != null)
            {
                serviceHost.Close();
                serviceHost = null;
            }
        }
    }

Finally there is a little bit of config to pop into the app.config for the WCF endpoints- I’ve created a custom binding here to allow me to receive larger files over the WCF call than the default limits allow (in this case, up to 10 meg);

  <system.serviceModel>
    <bindings>
      <wsHttpBinding>
        <binding name="bigWSHttpBinding" 
                 maxBufferPoolSize="10485760" maxReceivedMessageSize="10485760"> <!-- 10 MB limit -->
          <readerQuotas maxDepth="10485760" maxStringContentLength="10485760" maxArrayLength="10485760" maxBytesPerRead="10485760" />
        </binding>
      </wsHttpBinding>
    </bindings>
    <services>
      <service behaviorConfiguration="MyWCFServiceBehavior"
        name="Demo.MyWCFService">
        <endpoint address="" binding="wsHttpBinding" bindingConfiguration="bigWSHttpBinding"
          contract="Demo.IMyWCFService" />
        <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
        <host>
          <baseAddresses>
            <add baseAddress="http://localhost:8000/MyWCFService/service" />
          </baseAddresses>
        </host>
      </service>
    </services>
    <behaviors>
      <serviceBehaviors>
        <behavior name="MyWCFServiceBehavior">
          <serviceMetadata httpGetEnabled="true"/>
          <serviceDebug includeExceptionDetailInFaults="true"/>
        </behavior>
      </serviceBehaviors>
    </behaviors>
  </system.serviceModel>

I also created a couple of little batch files which I mark as “Copy to output directory” for installing and removing the service;

rem install.bat
c:\windows\microsoft.net\Framework\v4.0.30319\InstallUtil MyWindowsService.exe
net start MyWindowsService
rem remove.bat
net stop MyWindowsService
c:\windows\microsoft.net\Framework\v4.0.30319\InstallUtil MyWindowsService.exe /u

That’s the Windows service part built. You can now build, and jump to the bin output folder and run the install.bat file from the command line- with any luck (more likly with a little debugging!) you will be up and running. The second part is even easier;

Jump over to your web app, add a service reference to the url of the base address from your app config above then it’s just a matter of exposing those web service methods so that you can call them from your page with an ajax call- so in MVC I created a controller with Start, GetProgress & GetResult methods which returned JsonResults called straight from the client with jQuery.

2 Comments

My 8 Week Game Entry!

For the last 8 weeks I’ve been beavering away on a game to enter into the 8 week game competition! The last one (back in 2010) I won! (with Manic Spaceman, a Manic Miner clone, set in.. you guessed it… space)! So the pressure is on this time round with more competitors, some of which have experience in the games industry.

You can try out “The Distant Future (The Year 2000)” Here!

As last time, I had the choice of either XNA windows game, or a browser based html5/javascript/canvas effort- I went for the latter. I decided to make a game in the vein of the original classic Syndicate from the Amiga made by bullfrog. The competition states you have to do all the coding yourself, but other assets are up to you, which is great because I suck at trying to draw little people and animate them; so I managed to get the original sprites from Syndicate, along with some sound effects- for the levels I decided to use real cities, suing imagery from Bing Maps (which, to my surprise, were actually much better than the Google maps photography). I built a very basic level editor using jQuery, knockout and EaselJS allowing me to define walkable and non-walkable regions, and also define where buildings were so my game could make the levels pseudo-3d, where your characters would appear to walk behind buildings and trees etc and produce JSON data which could be dropped into the main game. (You can have a play with that here, there’s not a great deal you can do with it to be honest, but it was a great help during development).

The game’s main mechanic, from a technical standpoint, is taking the flat 2d Bing Map and making it into a 3d city your guys can walk around. This is achieved by taking the buildings as defined in the level editor, then snipping those pieces of the map out and placing them onto a separate layer, then it’s just a matter of figuring out what order to draw everything in- should each little guy be in front of or behind each building- I think it’s quite effective at giving the illusion of depth, though could do with a little refining, given more time!

As a base for the game, unlike my last 8 week game where everything was written from scratch, I used the CreateJS set of libraries to underpin everything. This is a great set of open source libraries which covers a whole bunch of stuff but the parts I used were EaselJS for the canvas itself, SoundJS to handle html5 audio, and PreloadJS which handles the preloading of various assets. This made it a bit more of a level playing field with the XNA guys who have a lot of this great stuff built into the framework. I found create js to be a great library, abstracting out lots of repetitive bits of code which are boring to write, but while still letting you manipulate the canvas directly when you need to. (EaselJS in particular has some great bitmap caching features in there too which let you dynamically build up textures as you go which I put to good use with the blood splatters and bullet holes!)

Getting the guys to find their way round the city proved quite complex- I used Dijkstra’s algorithm for this which used my array of non-walkable regions built in the level editor as the node graph and calculates the shortest region. I actually ported some code from C to JavaScript for this- the original being written by Darel Finley and had to make some adjustments to allow it to read from my data structures. This also proved to be quite an expensive calculation which would freeze the browser for a moment so I employed Web Workers, which are new to HTML5 and effectively bring threading to JavaScript for the first time, which solved this issue.

Everything else was pretty straight forward- the interface is all built with HTML5/ CSS/ jQuery and utilises a class I wrote called layout manager which reshuffles all the little grey boxes to fit together when they all change size. I also reused my Mini Map class to generate the level previews you see on the briefing screen.

The competition is judged by voting on the Facebook event page which is open to the general public next week- 11th Feb 2013 onwards. You can check out the competition details and the other entries on the Facebook page found at www.facebook.com/events/165946213543847

No Comments

How to fix CD / DVD Drive not showing up in Windows 8

Just installed Windows 8… which is a LOT faster than Windows 7 at pretty much everything- but I’m still not sold on the crazy new UI and there are a few things which seem quite mental. Noticed yesterday that, despite having installed from a DVD, my DVD drives just disappeared! Lucky full-windows8.com had blogged how to fix this with a weird registry hack;

They recommend you run this from the admin command prompt;

reg.exe add “HKLM\System\CurrentControlSet\Services\atapi\Controller0″ /f /v EnumDevice1 /t REG_DWORD /d 0×00000001

Repeat for additional drives, incrementing the very last number each time. Worked for me. Mental.

No Comments

Threading in JavaScript with Web Workers

I’m about 5 weeks into the second 8 week game competition now building my, as yet unnamed, (original) Syndicate clone.

My levels consist of real images from Bing Maps (which I was surprised to find had nicer imagery when compared with Google, despite Google maps generally being my go-to maps site), which I then outline the buildings on using a level editor I built for the task, then save a big ol JSON array. This is then used in game to tell the little guys where they are and aren’t allowed to walk, amongst other things. The windy streets of my first level (Brighton, on the south coast of the UK) make for some difficult navigation- so I had to employ an A* search algorithm which would let my little guys find their way around these defined polygons which represent the footprints of the buildings. This all worked fine, however I noticed a the browser would lock up for a second or so while it ran through checking all the polygons and building paths for all four of your guys. “Wouldn’t it be great if I could do this in another thread” I thought…

Web Workers to the rescue!

Web workers are new in HTML5 and let you fire up another piece of javascript code in another thread, totally separate from the UI (so it will stop the freezeing I was seeing when the Javascript had to think really hard). You only have one mechanism (correct me if I’m wrong here) to send messages between your “Worker” thread and back to the parent application; the postMessage() method and onmessage callback function.

On the application side you create your new worker like this;


// setup the worker- giving it the url for the js file with the code to execute
var worker = new Worker('mysource.js'); 

// define a call back to handle messages we get from the worker
worker.onmessage = function(msg) {  
  alert(msg);
}

// send a message to the worker
worker.postMessage('shaw');  

Then on the worker side you have the same methods at your disposal;

// this exists in mysource.js
// setup a call back to handle messages we receive from the application
self.onmessage = function(msg) {  
  // post a message back to the application
  self.postMessage('hello ' + msg);  
}

In case you’ve not figured it out, in the above example you should expect to get an alert box saying “hello shaw”.

It’s also work pointing out that “self” is the global scope inside one of these web workers, as you won’t have access to any of the usual browser window or document objects.

So this is all pretty straight forward, but I still had a problem with my route finding example; I’d written a class which has a constructor accepting the polygon array and then a method which works out the route and returns it– how does that work in this model?? How do you call methods and constructors when the only access you have to your thread is a simple postMessage method?

Calling methods from ‘the other side’
postMessage() lets you send JSON as well as just plain old strings, which makes it quite a bit more useful. I used this to build a basic messaging system between the app and the thread- I would pass something like this;

worker.postMessage({ method: 'Init', args : [my_array, null, 5]})

then in the worker I would have a call back which looked like this;

self.onmessage = function(msg) {
    // the if catches the 'Init' special case- this instanciates a new RouteFinder
    if (msg.method == 'Init') 
    {
        // create a new instance of our route finder class, using it's constructor and passing in args
        self.MyRouteFinder = RouteFinder.apply( null, msg.args );
    }
    else  // the else catches all the other method calls, whatever they may be
    {  
        // find the method from the prototype chain, and apply it against our globally scoped object, again passing the args
        // the result of this method call is passed straight back to the application
        self.postMessage(RouteFinder.prototype[msg.method].call(self.MyRouteFinder, msg.args));
    }
}

This worked well for me though there are lots of (often better!) variations of this pattern on-line.

What if I don’t want to put my worker code in another file?

Also not a problem as detailed in this article on html5rocks.com. It basically involves grabbing your code from it’s own script tab on the same page, then smashing it into a Blob, then passing this blob into the construtor of your web worker.

Wait a sec.. how do I debug these workers?

You may have fired up some code in Chrome and noticed it hasn’t worked, so gone to the dev tools (F12) then flicked around in the scripts tab and… wait a minute… the web worker code doesn’t show up in the scripts panel! Well don’t worry- if you’re up to date with your copy of Chrome go down to the bottom right hand corner of the scripts panel where you’ll see one of the collapsably panels is called “Workers” and lists the code executing in each of your workers- just click one to launch another debug window which you can set break points on in the usual way.

No Comments

Application timing out on live (under load) when making WCF Calls

Had an issue with a maintenance release of a system this week to UAT; It was a new WCF web service which supplies valuation data about vehicles- adapted an existing system to consume this service instead of it’s original local database- it worked fine in dev, but on UAT under load testing everything worked perfectly… for about 10 minutes!

As soon as the client system got under load from multiple connections we begun seeing timeout exceptions and the application just generally hanging, but only on pages which made WCF calls to the new service. After some perusal of the ELMAH error logs it became apparent that it was indeed our implementation of the WCF clients to blame- a quick peek into the code revealed that we were creating the WCF service proxy client, then executing the method we wanted, but then NOT calling the .Close method against the proxy! So the first 16 connections (the default number of connections allowed with the stock WCF config) were coming in and everything went fine, but after that all the connections were used up, just hanging there still open!

So… don’t forget to close you connections when you’re done- especially if you’ve come from using ASMX services where you didn’t have to worry about such things!

No Comments

Unexpected null reference exceptions when using NSubstitute

I recently got some unexpected null reference exceptions in a test I was writing using MSTest and NSubstitute; I was setting up my mock to return a new instance of an object with a bunch of parameters set- my code looked like this;

iv_client.GetValuation("G1|12345", 10000, 2008, "08").Returns(new WebServiceResponse()
{
    Result = false,
    ErrorEnum = IdResponseCode.NoDataFound
});

However when access the value of my GetValuation call in the code it was always null? The solution it seems is to define the result in it’s own variable and then return that, rather than define it inline- like so;

var valuation = new WebServiceResponse()
{
    Result = false,
    ErrorEnum = IdResponseCode.NoDataFound
};

iv_client.GetValuation("G1|12345", 10000, 2008, "08").Returns(valuation);

There’s probably a good reason for this… but I don’t know what it is! Anyone care to shed some light on this in the comments?

1 Comment

WCF Service with message based security validating against AspNet Membership Provider

This took me days of fiddling, but when I eventually sussed it was quite easy! WCF services have to be secured, and you have have a few options;

  • Message security
    The messages are encrypted
  • Transport security
    The connection is secured (SSL)
  • Message & Transport
    Both of the above

I want to use message based security (so no SSL) and I wanted AspNet Membership (as used in forms authentication by default) to manage the users allowed to use the service.

The key to message based security, is an X.509 certificate installed on the server. You can buy a cert from a trusted certificate authority (like Verisign) or you can make your own self signed cert. I went for the self signed certificate option, using the awesome SelfCert tool on the pluralsite blog as this is a system used internally- for an externally facing public service you’de probably want a proper certificate from a recognised certificate authority.

Drop the tool on your server, and fire it up- give it a common name (CN=) of whatever you want- for the sake of the example we’ll call it MyCert. I put the certificate in the “My” store.

Once that’s done you need to make sure the user account of whatever process hosts your WCF service has access to read the certificate. In this example, I’m running the WCF service in a web site hosted by IIS 7, in an app pool called “WCFDemo.shawson.co.uk”- so the identity is “IIS APPPool\wcfdemo.shawson.co.uk”. To assign permissions you can use another handy tool called winhttpcertcfg – once installed on your server, fire up the command line and run;

C:\Program Files (x86)\Windows Resource Kits\Tools>winhttpcertcfg -g -c LOCAL_MACHINE\My -s MyCert -a WCFDemo.shawson.co.uk

This will come back and tell you permissions have been assigned and everything is good. Ok so lets build the service.

I created a new MVC3 project (this doesn’t really matter) and added a WCF service to the project called TestService.svc. This had a single method called Hello which accepted a string “name” and returned a string. I setup the aspnet membership and added a single account called tester with a password of tester1.

Once thats all sorted, the critical bit is in the web config, which is where most of the WCF magic happens- so the server side config for the service looks like this;

 <system.serviceModel>
    
    <services>
      <!-- the service end point- this ties everything together for the service -->
      <service name="WCFCertificateTest.Services.TestService">
        <endpoint address="/"
                  binding="wsHttpBinding"
                  bindingConfiguration="MessageSecurity"
                  contract="WCFCertificateTest.Services.ITestService" />
      </service>
    </services>

    <bindings>
      <wsHttpBinding>
        <binding name="MessageSecurity" >

          <!-- specifies we cant message, not transport security -->
          <security mode="Message">
            <message clientCredentialType="UserName"/>
          </security>

        </binding>
      </wsHttpBinding>
    </bindings>
    
    <behaviors>
      <serviceBehaviors>
        
        <behavior name="">
          <serviceMetadata httpGetEnabled="true"/>
          <serviceCredentials>
            
            <!-- this is the bit which tells WCF to use the ASPNet sql membership -->
            <userNameAuthentication
              userNamePasswordValidationMode="MembershipProvider"
              membershipProviderName="AspNetSqlMembershipProvider" /> 
            
            <!-- this next bit tells the server which certificate we should be using the encrypt the messages -->
            <serviceCertificate
              findValue="MyCert"
              storeLocation="LocalMachine"
              storeName="My"
              x509FindType="FindBySubjectName"/> 
            
          </serviceCredentials>
        </behavior>
        
      </serviceBehaviors>
    </behaviors>
    
    <serviceHostingEnvironment multipleSiteBindingsEnabled="true"/>
  </system.serviceModel>

The server side is now ready. Make sure you run this on the same machine we created the certificate on earlier- fire it up!

Now for the client– this is the really easy bit.

For the purpose of this demo, I created a windows console app. Add the service reference.

Enter the url for your service, and select it, then enter a namespace for it (I chose TestService) then click ok. This will add the reference to your client app and write a bunch of stuff into the config. In my demo console app I got this;

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <system.serviceModel>
        <bindings>
            <wsHttpBinding>
                <binding name="WSHttpBinding_ITestService">
                    <security>
                        <message clientCredentialType="UserName" />
                    </security>
                </binding>
            </wsHttpBinding>
        </bindings>
        <client>
            <endpoint address="http://wcfdemo.shawson.co.uk/Services/TestService.svc"
                binding="wsHttpBinding" bindingConfiguration="WSHttpBinding_ITestService"
                contract="TestService.ITestService" name="WSHttpBinding_ITestService">
                <identity>
                    <certificate encodedValue="AwAAAAEAAAAUAAAAyb+3RrSzF0j7Lm7TONYkIpPPJosgAAAAAQAAAK4EAAAwggSqMIICkqADAgECAhAwLsfZzjx....QGqKDw4P1sScwBANFBjdRSrKzNyVR7b5gDU3geiPsXvS3an6kxZ" />
                </identity>
            </endpoint>
        </client>
    </system.serviceModel>
</configuration>

The critical part of this is the big string of jibberish in the identity certificate. When you add the reference it goes to the server and grabs this string, which is unique to the certificate we created earlier. If you are running this from local, and later deploy to a production server with a new certificate (even if it has the same common name), make sure you update the service reference, as this string will be different!

We can now make the call from the application- The asp net membership cerdentials are passed over using the ClientCredential.Username element (lines 3-4). Because we’re using a self signed certificate not for a trusted root authority we add a line (line 5 in the example below) to change how dot net validates the certificate- basically telling it not to worry about verifying the certificate.

            TestService.TestServiceClient client = new TestService.TestServiceClient();

            client.ClientCredentials.UserName.UserName = "tester";
            client.ClientCredentials.UserName.Password = "tester1";
            client.ClientCredentials.ServiceCertificate.Authentication.CertificateValidationMode = System.ServiceModel.Security.X509CertificateValidationMode.None;

            Console.WriteLine(client.Hello("Shaw"));
            Console.ReadLine();

And voila! A working WCF service using message based security. You can download the tools referenced above (just in case they become unavailable in the future) SelfCert & winhttpcertcfg

No Comments

Excellent free online book on Javascript Design Patterns

Excellent free online book on Javascript Design Patterns by Addy Osmani called “Learning JavaScript Design Patterns” – An essential read for any one doing any kind of serious Javascript development (and even if you’re not!).

No Comments