Encoding Kockerbeck

Applying WebInvoke without an Attribute

2012-02-17T23:37:00.000-08:00

Haven't posted in a long time but thought I'd post this bit of WCF extensibility I was working on at home. There is nothing on the Internet about how to do this & I felt I should figure something out.

Scenario: You have legacy WCF services that you want to switch to a WebHttpBinding w/ JSON but don't want to update 1,337 service contracts to include a WebInvoke attribute, UriTemplates and Request/Response Body Types

In this scenario I'll be assuming use of IIS and Windows Process Activation Services to host your service.

The WebInvokeAttribute implements IOperationBehavior. So what we need to do is somewhere in the WCF stack apply this operation behavior programmatically to our Endpoint's Contract.Operations. Let's start from the bottom up & take a look at an extension method that does this.


/// <summary>
/// Automatically applies the Web Invoke behavior to all operations in the collection
/// </summary>
/// <param name="operations"></param>
public static void ApplyWebInvoke(this System.ServiceModel.Description.OperationDescriptionCollection operations)
{
    foreach (var operation in operations)
    {
        var webInvoke = new System.ServiceModel.Web.WebInvokeAttribute
        {
            BodyStyle = System.ServiceModel.Web.WebMessageBodyStyle.Wrapped,
            Method = "POST",
            RequestFormat = System.ServiceModel.Web.WebMessageFormat.Json,
            ResponseFormat = System.ServiceModel.Web.WebMessageFormat.Json,
            UriTemplate = operation.Name
        };

        operation.Behaviors.Add(webInvoke);
    }
}

In the above extension method I'm forcing everything to be a POST, everything to use JSON and keeping the UriTemplates simply as the Operation name. Now where do we call this method? Personally, I'm a fan of having custom ServiceHosts + ServiceHostFactories rather than BehaviorExtensions. Chances are it's OK for me to touch the code & hosting portion of the application that I don't need to rely solely on configuration to extend WCF. If I have to extend a service that I don't have access to the ServiceHost -- behavior extensions are the only way. But given the choice -- custom ServiceHostFactory every time. The biggest reason is that I find other developers can follow the stack easier. It's less confusing. "Oh this SVC file has a custom factory? Oh this custom factory applies this behavior! "

Now take a look at the Factory and the custom Service Host:


public class WebInvokeServiceHostFactory : System.ServiceModel.Activation.ServiceHostFactory
{
    protected override System.ServiceModel.ServiceHost CreateServiceHost(Type serviceType, Uri[] baseAddresses)
    {
        return new WebInvokeServiceHost(serviceType, baseAddresses).ApplyWebInvoke();
    }
}

public class WebInvokeServiceHost : System.ServiceModel.Web.WebServiceHost
{
    public WebInvokeServiceHost(Type serviceType, params Uri[] baseAddresses) : base(serviceType, baseAddresses) { }
        
    internal WebInvokeServiceHost ApplyWebInvoke()
    {
        foreach(var endpoint in Description.Endpoints)
        {
            endpoint.Contract.Operations.ApplyWebInvoke();
        }

        return this;
    }
}

I'm applying the WebInvoke behavior after the ServiceHost has been instantiated. Now just update the .svc file to use the new Factory:


<%@ ServiceHost Service="MySite.Services.TestService" Factory="MySite.ServiceModel.WebInvokeServiceHostFactory" %>

As for the client...
Given our scenario of working with a legacy application, I'll start by assuming we aren't auto-generating Service References. So we just need to apply the same IOperationBehavior to the Client Endpoint's Contract.Operations just like the Service. Below is a sample client base.


public class SampleClientBase : ClientBase<ITestService>, ITestService
{
    public SampleClientBase(string config) : base(config) { }

    public SampleClientBase() : this("TestService")
    {
        ChannelFactory.Endpoint.Behaviors.Add(new WebHttpBehavior());

        ChannelFactory.Endpoint.Contract.Operations.ApplyWebInvoke();
    }

    public SomeObject BasicTest(SomeObject someObject)
    {
        return Channel.BasicTest(someObject);
    }
}

And that's all there is to it! Hopefully now you see why that loop is an extension method and just how easy it really is to switch over to WebHttp from NetTcp without having to update your contracts.

MapReduce in C# using Task Parallel Library

2010-09-27T09:47:00.000-07:00

Back in August I starting playing with a C# implementation of Google's MapReduce algorithm. The implementation was based on something Stephan Brenner did, although I completely refactored it.

Today I added a little bit of logic to split up the actual execution of Map & Reduce in this implementation using the Task Parallel Library in .NET 4.0. Check out the source code for MapReduce in C# on GitHub. Below is an excerpt from the Tests on how to implement the library.

Counting Words in Files


public static List<KeyValuePair<string, int>> Map(FileInfo document, string text)
{
    var items = text.Split('\n', ' ', '.', ',','\r');
    return items.Select(item => new KeyValuePair<string, int>(item, 1)).ToList();
}


public static List<int> Reduce(string word, List<int> wordCounts)
{
    if (wordCounts == null) return null;

    var result = new List<int> { 0 };
            
    foreach (var value in wordCounts)
    {
        result[0] += value;
    }

    return result;
}

public static void Main()
{
    var fileSearchData = new Dictionary<FileInfo, string>();
    di.GetFiles().ToList().ForEach(f => fileSearchData.Add(f, File.ReadAllText(f.FullName)));

    var output = MapReduce.Execute(Map, Reduce, fileSearchData);

    Console.WriteLine(output["needle"][0]);
}

Let me know if you have any questions or want to share any implementation concerns.

Require comment / message on all SVN commits for Visual SVN Server

2010-08-05T15:34:00.000-07:00

(I've been posting a lot the last few days!)

Something that drives me nuts is not having a comment or message when looking at SVN history. It's impossible to know what went on. Even a bad comment is better than no comment.

SVN hooks are a great way to solve this. If we make a pre-commit.bat file that checks the commit data for a comment, returns an error code of 1 if it wasn't found (with some text) or returns 0 if the comment is there.

Check out the pre-commit hook that I found online (thanks, Anuj Gakhar!) or check out the hook below.


@echo off
setlocal

rem Subversion sends through the path to the repository and transaction id
set REPOS=%1
set TXN=%2

rem check for an empty log message
svnlook log %REPOS% -t %TXN% | findstr . > nul
if %errorlevel% gtr 0 (goto err) else exit 0

:err
echo. 1>&2
echo A log message or comment is required to commit 1>&2
exit 1

This works perfectly except for 1 downfall -- you have to distribute it in every freaking repository! Below is the windows batch solution I came up with to solve that problem. Pretty straight forward, but thought I'd share for those less inclined to dig through archaic windows batch programming references.

Just create a batch file of the following & schedule it to run however often you want.


@ECHO OFF
:: ------ SVN Deploy Hooks ------
:: ----- by Mark Kockerbeck  ----

:: ------ Configuration ---------
SET HookDirectory=C:\CommonHooks
SET RepositoryDirectory=C:\Repositories
:: ------ /Configuration --------

pushd %HookDirectory%
for /r %%i in (*) do (
 for /D %%j in (%RepositoryDirectory%\*) do (
  echo Copying %%i to %%j\hooks\
  copy %%i %%j\hooks\
 )
)
popd

Using ref Keyword with Reference Types in C#

2010-08-05T08:30:00.001-07:00

One of our developers was using the ref keyword in a method that needed to replace the value of a property of the method's parameter.

In other words...


  public class Person
  {
    public int ID { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
  }

  public static class ReferenceExample
  {
    public static void Main()
    {
      var person = new Person
      {
        Name = "Mark",
        Email = "none@none.com",
      };

      Save(ref person);

      Console.WriteLine(person.ID);
      Console.ReadLine();
    }
    
    public static void Save(ref Person person)
    {
      person.ID = 1; // Pretend this saves to a database or something
    }
  }

The thinking is correct. Person needs to be passed by reference. So why not use the ref keyword, right? Well...Person is already a reference type. So if we are just replacing properties, we don't need the ref keyword. Removing it changes nothing with our code. The program still outputs 1.

This begs the question -- Why even have the ref keyword? The reason for it is to pass the entire parameter by reference. This means that we can redefine a newly instantiated person.

In other words...



  public static class ReferenceExample
  {
    public static void Main()
    {
      var person = new Person
      {
        Name = "Mark",
        Email = "none@none.com",
      };

      Save(ref person);

      Console.WriteLine(person.ID);
      Console.WriteLine(person.Name);
      Console.ReadLine();
    }
    
    public static void Save(ref Person person)
    {
      person = new Person
      {
        Name = "Bill",
        Email = "adventures@phonebooth.com",
        ID = 2,
      };
    }
  }

Try running the above with and without the ref keyword. If you use it with the ref keyword, the Person will be "Bill". If you use it without, the person will be "Mark".

Seems simple enough but I'm surprised how many developers don't (or didn't!) realize this difference.

Get the name of a property as a string in C#

2010-08-04T13:13:00.000-07:00

Another developer showed me something very useful with Expression Trees in C# (thanks, Pierre! Full credit goes to you). To show you the awesomeness of it all, let's start with a class called Person.


public class Person
{
  public string Name { get; set; }
  public string Email { get; set; }
}

Now let's say I have an instance of Person


var person = new Person
{
  Name = "mark kockerbeck",
  Email = "noneofyourbusiness@face.com",
};

Imagine creating a Dictionary<string,string> of this particular Person's properties (e.g. "Name" and "Email") and the corresponding property values.

We could easily do something like the following:


var dictionary = new Dictionary<string, string>
{
  { "Name", person.Name },
  { "Email", person.Email },
};

But there is unnecessary redundancy and repetition in this. We have to say the property twice (and as a messy string no less!).

Well, we could use reflection...


var dictionary = new Dictionary<string, string>();
typeof(Person).GetProperties().ToList().ForEach(p => dictionary.Add(p.Name, p.GetValue(person, null).ToString()));

...but a lot of developers seem scared by Reflection & especially a concise statement like the above.

Below is a way to get the Property name & value in one small statement while keeping a strong typed non-reflective code base.

We just need to create a new Method...


public static void AddField<T>(Dictionary<string,T> dictionary, Expression<Func<T>> expression)
{
  var name = ((MemberExpression)expression.Body).Member.Name;
  dictionary.Add(name, expression.Compile()());
}

And now just call each property as a Func<T>...


var dictionary = new Dictionary<string, string>();
AddField(dictionary, () => person.Name);
AddField(dictionary, () => person.Email);

Personally, I like the reflective way but I thought this was a very good use case for an Expression Tree

Service Cloud - dynamic calls to a cloud of WCF services

2010-08-03T13:18:00.000-07:00

For awhile, myself and another developer have been trying to build a working prototype of an idea he proposed for a Service Cloud. Essentially the Service Cloud is a collection of WCF services hosted on a number of servers and environments that can all communicate with one another via a common interface. An "application" would then be built by orchestrating these services together.

Instead of the application adding service references for each of the services it needs -- it would simply send a request to the "Gateway" service and it would figure out the rest. It would call the other services.

Finally, when the execution is complete and your request has bounced all over the cloud, you get a response that many services have built together.

I finally was able to create a working prototype (albeit completely contrived).

The Service Cloud prototype is available on GitHub, see: http://github.com/xeb/ServiceCloud.

Client
The client is simple. The client adds a WCF Service Reference to the Gateway service and calls the Gateway's service Execute method. The prototype requires that the full Endpoint Address of each service in the cloud be specified -- but this could easily be added to the Host (or better yet -- a service in the Host!).


// Start up the Cloud Gateway!
var cloud = new CloudServiceClient();

// Formulate our Request and get a Response from the Gateway
var response = cloud.Execute(new Request
{
 Argument = 15,
 Services = new[]
 {
  new ServiceCall { Name = "Decrementer", 
   Address = "http://localhost:8731/Design_Time_Addresses/ServiceCloud.Services/Decrementer/" },
  new ServiceCall { Name = "Incrementer", 
   Address = "http://localhost:8731/Design_Time_Addresses/ServiceCloud.Services/Incrementer/" },
  new ServiceCall { Name = "Incrementer", 
   Address = "http://localhost:8731/Design_Time_Addresses/ServiceCloud.Services/Incrementer/" },
 },
});

cloud.Close();

Now the Response we get back has a ReturnObject property that will be the result of our 3 service calls. Can you guess what the result will be?

Pretty straight forward huh? Check out the
Service Cloud Host at github for more details on how to make the magic happen.

Now imagine a Request to the Cloud with something like the following & I think you will see the potential.


// Formulate our Request and get a Response from the Gateway
var response = cloud.Execute(new Request
{
 Argument = new Person("Mark Kockerbeck") { Message = "I see you" },
 Services = new[]
 {
  new ServiceCall { Name = "FindAddress", 
   Address = "http://epicapp.com/findaddress" },

  new ServiceCall { Name = "FindEmail", 
   Address = "http://epicapp.com/findemail" },

  new ServiceCall { Name = "FindTwitter", 
   Address = "http://epicapp.com/findtwitter" },

  new ServiceCall { Name = "SendTwitterMessage", 
   Address = "http://epicapp.com/sendmessage/" },

  new ServiceCall { Name = "SendEmail", 
   Address = "http://epicapp.com/sendmessage/" },

  new ServiceCall { Name = "SendDirectMail", 
   Address = "http://epicapp.com/sendmessage/" },
 },
});

A scary example but the potential for many applications exists.

New & Fun vs. Tried & True

2010-07-14T17:30:00.000-07:00

I love new technology -- .NET 4.0 and the DLR are a dream come true. I cannot wait to convince other developers of the awesomeness of having IronPython for middleware supported by compiled C# on the backend.

But before I go upgrading existing client sites to 4.0, I need to be sure it will work. I just want to see it be successful from start to finish. I am skeptical by nature and want results before jumping in bed with any new tech. Not that I don't think Microsoft did an amazing job with the latest framework -- its just that I'm not as familiar with the "workarounds" that I need to learn for things. And yes, there will be workarounds -- I have yet to see perfection in software (only a few things come close).

From my experience it seems that developers fall into two general categories when faced with new technology decisions. Hopefully they change from one category to another. If you're like me, you change on a project by project basis.

New & Fun
Pros: You are constantly ahead of the curve; some problems can be solved with minimal effort. Faster than ever before. Chances are you have more job security & probably enjoy what you are doing everyday. Your mind is constantly expanding.
Cons: You need a new library, a new pattern, a new framework or even a new language all the time. If it isn't new, it's boring & therefore must be shit. You are constantly fighting with bugs and constantly discovering new things. Nothing ever feels stable. When it is stable -- it's time to move on or try something else.

Tried & True
Pros: You get to say "that's done, what else do you have for me?". You don't spend as much time debugging. You are great at estimating and meeting deadlines. Your days are predictable.
Cons: You're probably old. You may still write perl scripts (even COBOL or awk) when you need to. You're falling behind the curve and have a constant fear of being left behind. You may grow bitter as the world changes around you.

These categories are completely independent of skill level. I've seen Tried & True programmers who are brilliant and New & Fun programmers who can't get anything working.

The most important thing anyone of us can hope to learn is when do we use each technique. There are times when New & Fun is required. This project demands perfection & bleeding edge technology. Other times (usually tight deadlines, emergencies or low budgets), you must not deviate from the norm. Venturing into the unknown means suicide here.

What will really drive you insane is when a project or team's culture changes as time goes on. People quickly abandon New & Fun when the sky is falling (has fallen). Or what is the most frustrating: when something starts off tight, you go Tried & True only to find out that the client / owner / project manager demands new & flashy. "Can you add some cool new HTML 5 canvas animation to my Classic ASP site coded in VB"?

What I desire from any software I write is below. (Depending on the application 3 & 4 can switch).

1) Working

2) Stable

3) Readable

4) Fast

Anything else...is just prototyping.

TwoRingBinder - a Custom ASP.NET MVC Model Binder

2010-07-11T21:48:00.000-07:00

I haven't posted in a few months. Been busy at work and starting playing WoW (I know -- its awful but its fun). But a few weeks ago I made a Custom ModelBinder -- called TwoRingBinder -- for ASP.NET MVC. Ok, it's not an implementation but rather a class inheriting from DataAnnotationsModelBinder that allows for easier custom Binding methods. I put it on github but will explain how to use it below.

Download

or check out the meat of the code.

Essentially, TwoRingBinder allows you to write Binding Extensions instead of writing your own Custom Model Binder every time you want to do something tricky or different with Model Binding.

Example: We have a model called SignUp. SignUp has FirstName, LastName, Origin, IsFemale and FromRoot properties. Only the FirstName and LastName properties will be bound from a form via a ValueProvider; however, we need IsFemale to have a value (assume for a service of some type). Now, we don't want a checkbox on the site for people to select if they are female -- we want to just guess it based on their name (an arbitrary example to say the least). With TwoRingBinder we can do this two ways (get it?!?!).

Ring One: we can implement a method (or two) on our model -- the method(s) must be named OnBound or OnBinding.

Ring Two: we can implement a IBindModelExtension -- which is a generic of our Model, SignUp. The extension can have one or two methods, PreBindModel and PostBindModel.

Let's set a value for IsFemale automatically using TwoRingBinder, see:



private readonly string[] _girlsNames = new[] { "Elaine", "Stephanie", "Jennifer" };

public void PostBindModel(SignUp boundModel, ControllerContext controllerContext, 
                               ModelBindingContext bindingContext)
{
   boundModel.IsFemale = _girlsNames.Contains(boundModel.FirstName);
}

It's not that much code but I've found TwoRingBinder super useful for those types of situations where a Custom Model Binder isn't appropriate. Plus the IBindModelExtensions can be organized & clean -- separate from your Controllers and Models (which they are neither). Or if you so desire, the logic can be a part of the Model itself. I think where you place the OnBind / OnBound logic depends entirely upon what you are doing.

ASP.NET MVC and MSBuild via Command-Line

2010-05-18T21:32:00.000-07:00

I struggled with this one for a couple hours & wanted to post some help. I'm trying to move towards better deployment processes. I'd like to use TeamCity but until then, command-line will do. It's how I roll anyways.

When I first ran MSBuild on my ASP.NET MVC CSPROJ file I got the following error:


C:\Projects\MvcApplication.WebUI>msbuild /p:Configuration=Debug MvcApplication.csproj
Microsoft (R) Build Engine Version 2.0.50727.3053
[Microsoft .NET Framework, Version 2.0.50727.3603]
Copyright (C) Microsoft Corporation 2005. All rights reserved.

Build started 5/18/2010 9:27:29 PM.
__________________________________________________
Project "C:\Projects\MvcApplication.WebUI\MvcApplication.csproj" (default targets):

Target ResolveProjectReferences:
    C:\Projects\Assembly.csproj(200,11): error MSB4019: The imported project "C:\Microsoft
.CSharp.targets" was not found. Confirm that the path in the  declaration is correct, and that the file exists on disk.
Done building target "ResolveProjectReferences" in project "Spinsix.Lincs.WebUI.csproj" -- FAILED.

Done building project "MvcApplication.WebUI.csproj" -- FAILED.

Build FAILED.
C:\Projects\Assembly.csproj(200,11): error MSB4019: The imported project "C:\Microsoft.CSh
arp.targets" was not found. Confirm that the path in the  declaration is correct, and that the file exists on disk.
    0 Warning(s)
    1 Error(s)

Time Elapsed 00:00:00.12

The trick here is the Environment Variables that Visual Studio sets up for you. So you need to run the following in order to get them setup:


C:\Program Files\Microsoft Visual Studio 9.0\VC\vcvarsall.bat
or
C:\Program Files\Microsoft Visual Studio 10.0\VC\vcvarsall.bat

Or if you are writing a Build Script / Batch file, simply do:


cd  C:\Program Files\Microsoft Visual Studio 9.0\VC\
call vcvarsall.bat >NUL 
cd C:\Projects\MyProject\
msbuild /p:Configuration=Debug MvcApplication.csproj
xcopy /a /e (whatever other options) *.* x:\SomeDestination

Hope that helps someone!

Why TradingBlox is so Awesome

2010-03-17T23:09:00.000-07:00

TradingBlox is a relatively expensive piece of software that you've (probably) never heard of. You may never use it. But I still think you should know about it.

I've been passionate about the financial markets for years. Later I'll explain this month why you should at least know something about them. I had the pleasure of working at Carmichael and Company LLC and learned a great deal about trading from Ralph Carmichael. It excited a passion in me. This passion was fueled by books like: Market Wizards, Reminisces of a Stock Operator and The Way of the Turtle. Also this little PDF about the original Turtle trading rules really helped.

After trying to program my own recreation of the Turtle rules, I stumbled upon TradingBox. It did everything I wanted and more. It has an amazing community. It supports trading & optimizing multiple markets with the same system (which was a pain -- if not impossible -- using TradeStation).

TradingBlox lets you program trading rules to buy & sell Futures (or Forex / Stocks). Below is my own brief outline on the TradingBlox structure.

"Blox" are simply snippets of code that are broken into various categories, including:

Entry and Exit -- signals when to enter or exit
Entry Only -- signals when to enter
Exit Only -- signals when to exit
Entry, Exit and Money [Manager] -- signals when to enter, exit & how much to trade
Money Manager -- determines how much to trade
Portfolio Manager -- determines which markets you should trade
Risk Manager -- adjusts / monitors your overall risk of existing positions
Auxiliary -- for miscellaneous & reusable code

Right now, I want to just focus on an Entry & Exit Block. Let's take a look at a very simple one that uses 2 Moving Averages to determine when to buy & sell. We buy at the exact day that short moving average crosses over the long moving average.


VARIABLES: currentclose, stopPrice TYPE: Price

' Get the current close.
currentClose = instrument.close

' If we are not long and the faster moving averages are above the slower one.
IF instrument.position <> LONG AND
 shortMovingAverage > longMovingAverage THEN

  ' Enter a long on the open with no stop.
  broker.EnterLongOnOpen
ENDIF

' If we are not short and the faster moving averages are below the slower one.
IF instrument.position <> SHORT AND
 shortMovingAverage < longMovingAverage THEN

  ' Enter a short on the open with no stop.
  broker.EnterShortOnOpen
ENDIF

Makes sense, right? TradingBlox is extremely powerful & I'm going to be sharing more about Trading, Mechanical Systems & the Markets in general in the near future. It's what I really get excited about & its what I really love.

In the meantime, check out: TradingBlox

Spark - Optional Parameters in Partials

2010-02-05T19:14:00.000-08:00

While on the topic of the Spark View Engine, there is an easy way to make a parameter of a partial optional so you don't always need to pass a value when calling the partial.

Simply define the variable as part of the viewdata property.


<!-- samplePartial.spark -->
<viewdata optionalParameter="string" />

<a class="active?{optionalParameter == "home"}">Home</a>

That's it. Now you can call the partial by itself...


<samplePartial />

... or with the optionalParameter...


<samplePartial optionalParameter="home" />

A helpful trick that I couldn't find a lot about online.

Spark - Recursive rendering of partial files not possible (but it is!)

2010-02-04T15:06:00.001-08:00

We've been using the Spark View Engine a lot at work. I absolutely love it. Our front end developers love it (they don't need to re-learn something like we had to for NHaml) and if they don't want to use Spark's features, standard HTML works without problems.

During one project, I was making some contextual menus. I wanted to call a Spark partial recursively in order to not repeat myself & keep things flexible for the future. Unfortunately Spark specifically disallows this. If you try it, you will get an Exception that states "Recursive rendering of partial files not possible".

Well that just sucks. I understand the limitation in the View Engine itself but there is a way around it. Not super elegant but definitely functional without sacrificing anything other than style.

I posted the following example in the Spark project itself. The trick is using the HtmlHelper's RenderPartial method.

Let's say we have a partial called: navigation.spark.


< --- Contents of: navigation.spark --- >
<viewdata navItem="Models.NavigationItem" />

<li><a if="navItem != null" href="${navItem.Url}">${navItem.Name}</a></li>

<ul if="navItem.Children.Count > 0" >
  <for each="var child in navItem.Children" >
    #{Html.RenderPartial("_navigation.spark", new { child });}
  </for>
</ul>

And the page that you want to list navigation items, simply do:


<viewdata navigationItems="List<Models.Navigation>" />

<navigation for="var nav in navigationItems" navItem="nav" />

Now you can call the Partial recursively without issues.

How to Troubleshoot a Problem

2010-01-19T15:02:00.000-08:00

Building computers early on in life was an invaluable experience. Fighting with mobos & hunting for drivers taught me more about software development (& customer support) than hardware. I've been trying to summarize those lessons into an easy framework for debugging, troubleshooting and fighting most problems. Below is just what I have so far. Hopefully it's useful in some way.

1) Don't Panic - if this sounds familiar, congrats, you're a geek. You have to be calm & stop freaking out to be of any use. Someone going ape shit in your face is the fastest way to stop your brain from working. So take a moment, read Zen Habits and just chill for a second.

2) Identify the Problem - before doing anything, ANYTHING, you must figure out what's going wrong. Is the page not displaying? Is an error appearing? What's in the stack trace? What is happening that shouldn't be? 3/10 times you will stop here because nothing is wrong at all. Someone was just going crazy. If it's not one of those times, be thankful that you figured out what was going on & are closer to fixing it.

3) Stop the Bleeding - if your server is not responding, your website is down or a database is just hung, stop thinking about why its happening & get it back up & running! Seriously. Stop reading! Go do it! It's easier to explain what went wrong after things are working than trying to explain why the Apocalypse is inevitably happening RIGHT NOW.

4) Document the Symptoms - ok, you don't actually have to write anything down, but at least understand what is going on. Get out a whiteboard. List it out in front of your face. You may recognize something.

5) Diagnose the Disease with Peers - come up with some kind of diagnose and ask your buddies and/or co-workers. Start talking about it. Someone may say something that triggers a memory that helps you identify the root problem. Or someone may already know what's wrong but just hasn't shared it yet.

6) Start Treatment - make forward progress. Come up with a regiment of patched, upgraded, caching, mapreducin', memory-managed, leak-destroying updates. Throw them at the problem & see what happens.

TrustButVerify - simple jQuery validation powered by LiveValidation

2009-10-18T22:50:00.001-07:00

I really like the way LiveValidation runs; however, I REALLY LOVE jQuery selectors & chaining. Unfortunately the LiveValidation approach to validation doesn't feel right when looking at a lot of field-specific validators surrounded by super terse jQuery syntax.

So I decided to make a small 79-line wrapper-type of plugin that is powered by LiveValidation but comes with all the power of jQuery's selectors, chaining and... well, jQuery.

I called the plugin, TrustButVerify. Check it out & let me know your thoughts or if there is room for improvement (which there definitely is).

I have NOT exposed all of the LiveValidation methods; just the ones I found useful & necessary :-p

Here's a sample of some validation:


$(document).ready(function(){
  $('#form .required').required();
  $('#email').validEmail();
  $('#zip').matches(/^\d{5}$/);
  $('input.address').required().validate(someCustomFunction, 'your address is out of our area ');
});

Convert SQL types in destination to SQL type in source

2009-09-25T15:30:00.001-07:00

Another little script I needed for today. Feel free to expand on the CASE statement for your own types (which I will do should I need to run this again).



/*
 Will transform destination table's column types to match the source database's  
 (when column names match)
 codesnippet:83d07e82-3866-4877-849f-0ae06322b9fb
*/


declare @destination varchar(50)
declare @source varchar(50)

---- Configuration -----
set @destination = 'PlanImport'
set @source = 'Plan'
------------------------

declare @i int
declare @count int
declare @sql varchar(8000)
declare @columnName varchar(8000)
declare @columnType varchar(8000)
declare @columns table (id int identity(1,1), columnName varchar(50), columnType varchar(50) )

-- insert source columns that are in destination
insert into @columns
select source.[Name], 
 (CASE source_types.[Name]
 WHEN 'decimal' THEN
  source_types.[Name] + '(' + cast(source.[precision] as varchar(50)) + ',' + cast(source.[scale] as varchar(50)) + ')' 
 WHEN 'int' THEN
  source_types.[Name]
 WHEN 'varchar' THEN
  source_types.[Name] + '(' + cast(source.[max_length] as varchar(50)) + ')' 
 ELSE 
  source_types.[Name]
 END) as [Name]
from sys.columns as source
join sys.columns as destination on destination.[name] = source.[name]
join sys.types source_types on source_types.system_type_id = source.system_type_id
where source.object_id = object_id(@source) and destination.object_id = object_id(@destination) 

set @i = 1
set @count = (select max(id) from @columns)

while(@i <= @count)
begin
 set @columnName = (SELECT [columnName] FROM @columns WHERE [ID] = @i)
 set @columnType = (SELECT [columnType] FROM @columns WHERE [ID] = @i)
 
 --select @columnName
 set @sql = 'ALTER TABLE ' + @destination + ' ALTER COLUMN ' + @columnName + ' ' + @columnType

 execute(@sql)

 set @i = @i + 1
end

select 'Done!'

Convert literal SQL 'NULL' to real NULL

2009-09-25T14:56:00.000-07:00

Pretty straight forward. Had to do this today & thought I'd share the solution I came up with.


/*
 Will turn literal "NULL" into SQL NULL in any table (for all columns)
 codesnippet:25616582-ced6-4e59-ab4c-25d2128cc4b6
*/

declare @tableName varchar(50)

---- Configuration -----
set @tableName = 'PlanImport'
------------------------


declare @i int
declare @count int
declare @sql varchar(8000)
declare @columnName varchar(8000)
declare @columns table (id int identity(1,1), columnName varchar(50))

insert into @columns
select [Name] from sys.columns where object_id = object_id(@tableName) 
and system_type_id in (select system_type_id from sys.types where [name] like '%varchar%' or [name] like '%text%' )

set @i = 1
set @count = (select max(id) from @columns)

while(@i <= @count)
begin
 set @columnName = (SELECT [columnName] FROM @columns WHERE [ID] = @i)
 set @sql = 'UPDATE [' + @tableName + '] SET [' + @columnName + '] = NULL WHERE [' + @columnName + '] = ''NULL'''

 EXECUTE(@sql)

 set @i = @i + 1
end

Fluent NHibernate - Incorrect syntax near the keyword 'Group'

2009-09-16T20:21:00.000-07:00

I have to share this. I spent a good amount of time searching around Fluent NHibernate for a way to escape columns in the generated SQL while still using AutoMapping.

My initial configuration looked like:


Fluently.Configure()
.Database(MsSqlConfiguration.MsSql2005.ConnectionString(
  c => c.Is(ConfigurationManager.ConnectionStrings["Something"].ConnectionString)))
 .Mappings(x => x.AutoMappings.Add(AutoPersistenceModel.MapEntitiesFromAssemblyOf()
   .WithSetup(convention =>
     {
       convention.FindIdentity = p => p.Name == p.DeclaringType.Name + "Id";
       convention.GetComponentColumnPrefix = type => type.Name + "Id";
     } )
 .BuildSessionFactory()

This works great, but I had a property in my class (SomeType) that is called Group. This was causing the SqlException: Incorrect syntax near the keyword 'Group' (others would be like: Incorrect syntax near the keyword 'User' or Incorrect syntax near the keyword 'Table').

The solution I found was to use the Fluent NHibernate ConventionDiscovery + ConvensionBuilder to always add a column name possibility (to use in SQL) which would be escaped by [] for every property. Why not? This is SQL 2005 I'm working with, right?

So the final solution looked like:


Fluently.Configure()
.Database(MsSqlConfiguration.MsSql2005.ConnectionString(
  c => c.Is(ConfigurationManager.ConnectionStrings["Something"].ConnectionString)))
 .Mappings(x => x.AutoMappings.Add(AutoPersistenceModel.MapEntitiesFromAssemblyOf()
   .WithSetup(convention =>
     {
       convention.FindIdentity = p => p.Name == p.DeclaringType.Name + "Id";
       convention.GetComponentColumnPrefix = type => type.Name + "Id";
     } )
   .ConventionDiscovery.Setup(c => c.Add(ConventionBuilder.Property.Always(
      s => s.ColumnNames.Add("[" + s.Property.Name + "]"))))))
 .BuildSessionFactory()

Strongly typed AppSettings with defaults

2009-08-27T23:06:00.001-07:00

Very often I want configurable options for miscellaneous settings; but I hate to shift my focus from the code I'm working on to add a new AppSetting. Sometimes these settings are essential but more often than they are just options that I may need.

So the solution I like to use is to make default values for such AppSettings. If the service you are writing needs to retry a database or web service call a few times, why not make that option configurable but use a default of 5. The method below will grab an AppSetting & try to convert it. If it fails, it uses the default. Plus the generic T can be a value or reference type.


public static T GetAppSetting<T>(string key, T defaultValue)
{
 if (String.IsNullOrEmpty(ConfigurationManager.AppSettings[key])) return defaultValue;

 try
 { 
  return (T)Convert.ChangeType(ConfigurationManager.AppSettings[key], typeof(T));
 }
 catch
 {

 }

 return defaultValue;
}

// Sample Usage
public void DoSomething()
{
 if(GetAppSetting("ShouldRun", true))
 {
  for(int i = 0; i < GetAppSetting("NumberOfTries", 5); i++)
  {
   if(SomeMethodSucceeds()) break;
  }
 }
}

Stream was not readable

2009-08-14T08:50:00.000-07:00

I recently started writing some tests (in xUnit using Moq + StructureMap) for a service that calls a class -- let's call it LogWriter -- that simply writes formatted logging data to a Stream. After creating an interface for the logging class, ILogWriter, I wanted to write some tests on LogWriter to see if it was working as expected (before testing out my service which depends on ILogWriter).

After the ILogWriter was done, I used StreamReader to check out it's input. The Stream I told it to write to was a MemoryStream (this is unit testing).


 public class LogWriterTests
 {
  [Fact]
  public void BaseStream_Is_Not_Empty()
  {
   MemoryStream stream = new MemoryStream();
   LogWriter logWriter = new LogWriter(stream);
   logWriter.WriteLine(new[] { DateTime.Now.ToString("yyyy-MM-dd"), "Log data" });

   using (StreamReader sr = new StreamReader(stream))
   {
    Assert.NotEmpty(sr.ReadToEnd());
   }
  }
 }


// A basic example
public class LogWriter
{
 public LogWriter(Stream stream)
 {
  BaseStream = new StreamWriter(stream);
 }

 public StreamWriter BaseStream { get; set; }

 public void WriteLine(params object[] logObjects)
 {
  BaseStream.WriteLine(DoSomeFormatting(logObjects));
 }
}

The "BaseStream_Is_Not_Empty" test always failed. The Stream was always empty. Hmmm...how about if we try closing the Stream?


[Fact]
public void BaseStream_Is_Not_Empty()
{
 MemoryStream stream = new MemoryStream();
 LogWriter logWriter = new LogWriter(stream);
 logWriter.WriteLine(new[] { DateTime.Now.ToString("yyyy-MM-dd"), "Log data" });
 logWriter.BaseStream.Close();

 using (StreamReader sr = new StreamReader(stream))
 {
  Assert.NotEmpty(sr.ReadToEnd());
 }
}

Now we would expect to get: Stream was not readable. The Stream has been closed -- you can't read from it. The trick now is to reset the position of the Stream and set the BaseStream of LogWriter to AutoFlush.


[Fact]
public void BaseStream_Is_Not_Empty()
{
 MemoryStream stream = new MemoryStream();
 LogWriter logWriter = new LogWriter(stream);
 logWriter.WriteLine(new[] { DateTime.Now.ToString("yyyy-MM-dd"), "Log data" });
 logWriter.BaseStream.AutoFlush = true;
 stream.Seek(0, SeekOrigin.Begin);

 using (StreamReader sr = new StreamReader(stream))
 {
  Assert.NotEmpty(sr.ReadToEnd());
 }
}

And now our test passes. Moving on...

Fluent NHibernate + Encrypting Values

2009-08-03T17:07:00.000-07:00

I recently created two CLR User Defined Functions for SQL Server 2005 (written in C#) to decrypt & encrypt a given string (based on a hard-coded key).



select dbo.EncryptString('Testing')
-- Returns: Fjfds243qcm43fsdj2343==

select dbo.DecryptString('Fjfds243qcm43fsdj2343==')
-- Returns: Testing

Pretty simple stuff. This will allow the user with enough access to pull encrypted data out of the database and provide the ability to restrict decryption to other SQL users.

Now to implement this in code...

Because it's awesome, Fluent NHibernate has a method called FormulasIs that assists in building out values using SQL.



public class User
{
   public virtual int Id { get; set; }
   public virtual DateTime DateOfBirth { get; set; }
}

public sealed class UserMap : ClassMap
{
  public UserMap()
  {
    WithTable("dbo.[User]");
    Id(x => x.Id, "[ID]");
    Map(x => x.DateOfBirth, "DOB").FormulaIs("dbo.DecryptString(DOB)");
  }
}

This is perfect. Exactly what I needed...until I needed to save the value. Fluent NHibernate will not pass any value to the database for the DateOfBirth property -- it is being set as NULL.

A solution I came up with was to use 2 Properties in the entity. 1 for the developer to use, and 1 for NHibernate to use.



public class User
{
   // Get the default instance using StructureMap
   private readonly IEncryptionService _encryptionService =
                   ObjectFactory.GetInstance<IEncryptionService>();
   public virtual int Id { get; set; }
   public virtual DateTime? DateOfBirth
   {
     get
     {
       return _encryptionService.DecryptObject<DateTime?>(DateOfBirthEncrypted);
     }
     set
     {
       DateOfBirthEncrypted= _encryptionService.EncryptString(value.Value
                                   .ToString("yyyy-MM-dd HH:mm:ss"));
     }
   }
   [Obsolete("Use the 'DateOfBirth' property -- this property is only to be used by NHibernate")]
   public virtual string DateOfBirthEncrypted { get; set; }
}


public sealed class UserMap : ClassMap<User>
{
  public UserMap()
  {
    WithTable("dbo.[User]");
    Id(x => x.Id, "[ID]");
    Map(x => x.DateOfBirthEncrypted, "DOB");
  }
}

And a sample EncryptionService is below:


using System;
using System.IO;
using System.Security.Cryptography;
using System.Text;

namespace Services
{
 public class EncryptionService : IEncryptionService 
 {
  /// <summary>
  /// Decrypts a string 
  /// </summary>
  /// <param name="encryptedString"></param>
  /// <returns></returns>
  public String DecryptString(string encryptedString)
  {
   if (String.IsNullOrEmpty(encryptedString)) return String.Empty;

   try
   {
    using (TripleDESCryptoServiceProvider cypher = new TripleDESCryptoServiceProvider())
    {
     PasswordDeriveBytes pdb = new PasswordDeriveBytes("ENTERAKEYHERE", new byte[0]);
     cypher.Key = pdb.GetBytes(16);
     cypher.IV = pdb.GetBytes(8);

     using (MemoryStream ms = new MemoryStream())
     {
      using (CryptoStream cs = new CryptoStream(ms, cypher.CreateDecryptor(), CryptoStreamMode.Write))
      {
       byte[] data = Convert.FromBase64String(encryptedString);
       cs.Write(data, 0, data.Length);
       cs.Close();

       return Encoding.Unicode.GetString(ms.ToArray());
      }
     }
    }
   }
   catch
   {
    return String.Empty;
   }
  }

  /// <summary>
  /// Encrypts a string
  /// </summary>
  /// <param name="decryptedString"
  /// <returns></returns>
  public String EncryptString(string decryptedString)
  {
   if (String.IsNullOrEmpty(decryptedString)) return String.Empty;

   using (TripleDESCryptoServiceProvider cypher = new TripleDESCryptoServiceProvider())
   {
    PasswordDeriveBytes pdb = new PasswordDeriveBytes("ENTERAKEYHERE", new byte[0]);

    cypher.Key = pdb.GetBytes(16);
    cypher.IV = pdb.GetBytes(8);

    using (MemoryStream ms = new MemoryStream())
    {
     using (CryptoStream cs = new CryptoStream(ms, cypher.CreateEncryptor(), CryptoStreamMode.Write))
     {
      byte[] data = Encoding.Unicode.GetBytes(decryptedString);

      cs.Write(data, 0, data.Length);
      cs.Close();

      return Convert.ToBase64String(ms.ToArray());
     }
    }
   }
  }

  /// <summary>
  /// Decrypts a given value as type of T, if unsuccessful the defaultValue is used
  /// </summary>
  /// <typeparam name="T"></typeparam>
  /// <param name="value"></param>
  /// <param name="defaultValue"></param>
  /// <returns></returns>
  public T DecryptObject<T>(object value, T defaultValue)
  {
   if (value == null) return defaultValue;

   try
   {
     Type conversionType = typeof(T);

     // Some trickery for Nullable Types
     if (conversionType.IsGenericType && conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
     {
          conversionType = new NullableConverter(conversionType).UnderlyingType;
     }

     return (T)Convert.ChangeType(DecryptString(Convert.ToString(value)), conversionType);
   }
   catch 
   {
    // Do nothing
   }

   return defaultValue;
  }
 }
}

This will allow the developer to set the property as normal but NHibernate will only access the Encrytped property. The developer never has to worry about knowing which values are encrypted on the database. And better yet, our encryption can all be done in code & we can just delete those CLR UDFs if we don't need them.

Simple ASP.NET Localization

2009-07-31T14:52:00.000-07:00

I've seen a lot of solutions for localization in ASP.NET -- many of which use a database to store the localized content. The problem with this approach is that you have to recreate the tools that you are already using. Specifically: text editors (now you need an admin site to update the database content), source control (now you need revision history), content uploading (now you need an SSIS or custom Windows Service to deploy content). It's a bigger project than you want.

Generally, whenever HTML goes into a database (apart from a Forum), I question the need. The reason being that HTML is code and code belongs in Source Control. If Developers are the only ones updating the content (we're not talking about CMSs here) then why force them to use tools they aren't familiar with to do the same job they do all day long?

A solution that I've found that works well is to use an HTTP Module and a Localized directory of files. First, let's go over the HTTP Module.


/// 
/// Rewrites URLs to Custom Pages if they exist
/// 

public class LocalizationModule : IHttpModule
{
 public void Init(HttpApplication app)
 {
  app.BeginRequest += OnBeginRequest;
 }

 private void OnBeginRequest(object src, EventArgs e)
 {
  // Replace the below with a way to determine culture however you'd like
  string culture = SomeService.GetCulture();
  if (String.IsNullOrEmpty(culture)) return;
  
  HttpApplication app = src as HttpApplication;
  if (app == null) return;

  string fullPath = app.Request.Path;
  string possiblePath = app.Context.Server.MapPath("/" + ConfigurationManager.AppSettings["LocalizationDirectory"]) + "\\" + culture + fullPath.Replace("/", "\\");

  if (File.Exists(possiblePath.ToLower()))
  {
   string virtualPath = "/" + ConfigurationManager.AppSettings["LocalizationDirectory"] + "/" + culture + fullPath;

   app.Context.RewritePath(virtualPath);
  }
 
 }

 public void Dispose()
 {

 }
}

The "SomeService.GetCulture()" can be anything. I've made GetCulture return the culture code using Cookies (with a value of say "fr-FR"), the URL (i.e. "http://www.somesite.com/en-US/page.aspx") or even based on a drop down selection.

The content of your site can live within a specified folder, see:

The HTTP module will then serve up the corresponding page in the localization directory.

Pretty simple stuff. It's a LOT easier to manage than some database localization solutions. I highly recommend using flat files for content that is only going to be touched by developers -- after all -- we're used to it.

Unsvn - a way to un-checkout an svn repository

2009-07-27T10:20:00.000-07:00

Sometimes other developers push up .svn directories to production. This drives me nuts. Not only does it create clutter but the directory contains sensitive information about where your SVN repository is located and who the user is.

Removing these directories is a pain to do manually, so why bother? The C# code below will remove all .svn directories & all the files beneath them for any given path. See the source below or just download the Windows Binary.


using System;
using System.IO;

namespace UnSvn
{
 class Program
 {
  static void Main(string[] args)
  {
   if (args == null || args.Length != 1)
   {
    Usage();
    return;
   }

   DirectoryInfo root = new DirectoryInfo(args[0]);
   
   try
   {
    if(!Process(root))
    {
     Console.WriteLine("Nothing to do");
    }
   }
   catch (Exception ex)
   {
    Console.WriteLine(ex.Message);
    Console.WriteLine(ex.StackTrace);
   }
  }

  static void Usage()
  {
   Console.WriteLine("usage: unsvn [/path/to/directory]");
   Console.WriteLine("unsvn will remove all \".svn\" directories and files from the given path");
  }

  static bool Process(DirectoryInfo di)
  {
   bool removed = false;

   if (String.Equals(di.Name, ".svn", StringComparison.OrdinalIgnoreCase))
   {
    SetNormal(di);
    Console.WriteLine(String.Format("Removed: {0}", di.FullName));
    Directory.Delete(di.FullName, true);
    removed = true;
   }
   else
   {
    foreach (DirectoryInfo di2 in di.GetDirectories())
    {
     removed = removed | Process(di2);
    }
   }

   return removed;
  }

  static void SetNormal(DirectoryInfo di)
  {
   foreach (FileInfo fi in di.GetFiles())
   {
    File.SetAttributes(fi.FullName, FileAttributes.Normal);
   }

   foreach (DirectoryInfo di2 in di.GetDirectories())
   {
    SetNormal(di2);
   }
  }
 }
}

And for those on Macs, below is a Python implementation (that has not been fully tested, but works well enough for me).


#!/usr/bin/python
import os, sys

def main():
  if len(sys.argv) != 2: usage()
  elif unsvn(sys.argv[1]) == False:
    print "Nothing to do"
  print

def usage():
    print "usage: unsvn [/path/to/directory]"
    print "unsvn will remove all \".svn\" directories and files from the given path"

def unsvn(path):
  removed = False
  for file in os.listdir(path):
    subpath = os.path.join(path, file)
    if file == ".svn":
        removeDir(subpath)
        removed = True
        print "Removed: " + subpath
    elif os.path.isdir(subpath):
        removed = removed or unsvn(subpath)
  return removed

def removeDir(path):
  if os.path.isdir(path) == False:
    os.unlink(path)
    return
  for file in os.listdir(path):
    removeDir(os.path.join(path, file))
  os.rmdir(path)

main()

Syntax Highlighting in Blogger -- with jQuery & SyntaxHighlighter

2009-07-24T14:25:00.000-07:00

There are a few reasons I decided to go with Blogger over other blogging options. 1) I want to spend time writing not setting things up, 2) BlogEngine.NET didn't seem to look right no matter what theme I used and I didn't like the admin and 3) Wordpress is all MySQL.

That being said I wanted to get SyntaxHighlighter working. After reading Guogang Hu's post about how to get it working (& not likely a lot of code), I decided to do it with jQuery. The real issue with SyntaxHighligher and Blogger is the line breaks. Posts have line breaks replaced by <br /> tags -- which SyntaxHighlighter will faithful display.

Using the replaceWith method in jQuery makes this a snap. Check out the code below.


<link href='/syntaxhighlighter/styles/SyntaxHighlighter.css' rel='stylesheet' type='text/css'/>
<script src='http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js' type='text/javascript'></script>
<script src='/syntaxhighlighter/scripts/shCore.js' type='text/javascript'></script>
<script src='/syntaxhighlighter/scripts/shBrushCSharp.js' type='text/javascript'></script>
<script src='/syntaxhighlighter/scripts/shBrushXml.js' type='text/javascript'></script>
<script src='/syntaxhighlighter/scripts/shBrushJScript.js' type='text/javascript'></script>
<script src='/syntaxhighlighter/scripts/shBrushSql.js' type='text/javascript'></script>
<script type='text/javascript'>
$('pre br').replaceWith('\n');
dp.SyntaxHighlighter.HighlightAll("code");
</script>

Deleting Cookies (or Managing Cookie Domains) in ASP.NET

2009-07-22T12:47:00.000-07:00

I just spent a good few hours wrestling with writing authentication cookies in ASP.NET across different sub-domains. I've done enough SSO installations to know how it should work & what the hiccups are. Today I was reminded of a good lesson. I was writing a Cookie out via Response.Cookies.Add() in 2 sites. The problem was the second site was not overwriting the value from the first. So I tried to delete the cookie. I set the expiration to the past, I removed it from the collection, I tried using Klaus Hartl's jQuery plugin. Nothing was deleting this cookie.

Then I realized what I was doing wrong -- I was setting the Domain in the first instance but NOT in the second. ASP.NET was using different domains in the cookie. What I learned was that the more general, top-level domain was taking priority over the default domain.

Another issue to consider is the fact that Visual Studio 2008 and IIS will read the Cookie values different depending upon their Domain. Let's say we set 3 cookies (with the same name, but different values) with 3 different domains: the top-level domain, a subdomain and the default domain.

Or...



HttpCookie tldCookie = new HttpCookie("Name", "TLD Value")
{
 Domain = "topdomain.com"
};

HttpCookie subDomainCookie = new HttpCookie("Name", "Subdomain Value")
{
 Domain = "subdomain.topdomain.com"
};

HttpCookie defaultCookie = new HttpCookie("Name", "Default (No-Domain) Value");

Response.Cookies.Add(tldCookie);
Response.Cookies.Add(subDomainCookie);
Response.Cookies.Add(defaultCookie);

If we read the "Name" cookie on another page using the ASP.NET Development Server -- what should the value be?



HttpCookie cookie = Request.Cookies["Name"];
if (cookie == null) return;
Response.Write(cookie.Value);

...this will write Default (No-Domain) Value as the value while running locally. If you run the same page on subdomain.topdomain.com it will return TLD Value.

Top-Level Domains will take precedence over other domains. Moreover, the subdomain cookie will be there (if you use the WebDev toolbar + View Cookies); however, it is not readable within the Response.Cookies collection.

Dynamic SQL Pivot with no Aggregate

2009-04-04T15:53:00.000-07:00

Imagine a Survey database. The layout of the database is meant to be reusable, DRY-conforming and fast. Meaning it's not very user-friendly. A data dump would be a PITA for any user to interpret.

A database diagram is below.

One thing that I could not find out how to do online was how to write a PIVOT query without using an aggregate function. The reason is that you cannot. You have to aggregate the data. So the trick is to write your query with only one column of results being pivoted -- making the aggregate meaningless.

Another issue I have with PIVOTs is that you need to know something about your DataSet in order to pivot it. That is lame. So the following TSQL query will get a list of all users, their answers to the survey & the questions as columns. It does this by building the PIVOT statement in SQL and then executing it.


/*****************************************
*
* WARNING
* If you receive the following error: 
* Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '('.
* THEN your database is NOT configured with SQL Server 2005 
* Compatibility-Level and PIVOT is not recognized
* 
******************************************/

if object_id('tempdb..#pivotdata') is not null
begin
 drop table #pivotdata
end

declare @questions table( id int identity(1,1), question varchar(500) )
declare @i int
declare @cmd varchar(8000)

select
u.Reference, u.Email, FirstName, Question, Answer
into #pivotdata
from surveyresult r
join surveyuser u on u.[id] = r.surveyuserid
join surveyanswer a on a.[id] = r.answerid 
join surveyquestion q on q.[id] = a.questionid 
order by FirstName


insert into @questions
select distinct question from #pivotdata

set @i = 1
set @cmd = '
   select *
   from #pivotdata
   pivot ( MAX(answer) FOR question IN 
   ('

while @i < (select max(id) from @questions) + 1
begin
 set @cmd = @cmd + '[' + (select question from @questions where id = @i) + '],'
 set @i = @i + 1
end
set @cmd = substring(@cmd, 0, len(@cmd)) + ') ) as p order by email'

execute(@cmd)

if object_id('tempdb..#pivotdata') is not null
begin
 drop table #pivotdata
end

Keep in mind that in order for execute to read from your temp table it must be an on-disk temporary table. No in-memory tables (like @questions) will work.

The Wheel

2008-04-19T01:23:00.000-07:00

Why reinvent the wheel? There are so many tools out there that do things better than I can, so why not use them & spend my time on something original? So, I'm just going to redirect my domain to my new blog.

Also, thanks to 37signals for making Georgia the coolest font on the Internets. I'm going to be sure to change my default in Outlook when I get back to work on Monday.