Extension Method? Visitor?

I was reading this really cool article from Scott Hanselman about a newer, smarter way to format objects into a string.

The gist of it is this: you have an extension method called "ToString(..)", with a signature like this:

public static class FormattableObject
{
...
public static string ToString(this object anObject, string aFormat, IFormatProvider formatProvider)
...
}

So what this offer us is a really cool way via reflection to format objects based on their property/field names. For example, consider the basic Person class:

public class Person
{
...
public string FirstName{ get; set;}
public decimal Money{get; set;}
}

and then you can make a call like:

Person p = new Person(3.43M, "Joe");
Console.WriteLine(p.ToString("{Money:C}, {LastName}"));

and the output would look like

3.43, Joe

^^Now thats cool! This is a really simple and powerful piece of formatting code!
But then I paused for a minute. I like the reflection and formatting, but what benefit does the extension method bring? Its a parlor trick with a slight syntactic advantage, but it also seems to unnecessarily obfuscate things. To prove my point, I extracted the meat of his code into a simple (read: "less-cool") static method.

In essense, consider the following two lines:

1. Console.WriteLine(p.ToString("{Money:C}, {LastName}"));
2. Console.WriteLine(StringFormattingVisitor.ConvertToString(p, "{Money:C}, {LastName}"));

Now the following two lines of code are functionally equivalent. Call me an old fart, but I like line #2 better, because if theres ever an issue related to the cool string formatting functionality, I can read it and see exactly where the method is defined, the first one abstracts this information away from me.
Basically, what I am seeing is that extension methods are a way to implement object visitors, but the actual identity of the visitor is hidden, such that it looks like a native method. I'm still struggling to find out why this is better than writing a plain old explicit visitor. Any opinions?

How to Export from Sql Server 2005 to a well-formed XML File

Its a shame I have to write this one. It started out innocent enough. I need some data dumped out of a query and into a well-formatted xml file for further xslt transformations.

Piece of cake, just run a "FOR XML" query on a SQL server, output the results to file, and you will have an easy-to-translate XML file, right? Not exactly...

Say this is Northwind and I want the categories joined with the products:

DECLARE @output xml;
SET     @output =
(
SELECT
categories.categoryid AS '@categoryID',
 (
SELECT  
products.productid AS '@productID'
FROM       
Products
WHERE   
products.categoryid = categories.categoryid
FOR xml path('product'), type, root('products')
)
FROM       
Categories
FOR xml path('category'), TYPE, root('categories')
);

SELECT     @output;

Notice that I need something more powerful than RAW or AUTO mode, I want to define my own custom xml schema using the PATH syntax.
Then, I can see in the sql output window something like this:

<categories>
<category categoryID="1">
 <products>
   <product productID="1" />
   <product productID="2" />
   <product productID="24" />
   <product productID="34" />
   <product productID="35" />
   <product productID="38" />
   <product productID="39" />
   <product productID="43" />
   <product productID="67" />
   <product productID="70" />
   <product productID="75" />
   <product productID="76" />
 </products>
</category>
<category categoryID="2">
 <products>
   <product productID="3" />
   <product productID="4" />
   <product productID="5" />
   <product productID="6" />
   <product productID="8" />
   <product productID="15" />
   <product productID="44" />
   <product productID="61" />
   <product productID="63" />
   <product productID="65" />
   <product productID="66" />
   <product productID="77" />
 </products>
</category>
<category categoryID="3">
 <products>
   <product productID="16" />
   <product productID="19" />

..you get the idea. Now I have the format and results I want, but this needs to programmatically be exported to a file. it time to switch my output from grid/text to a file... only to get this garbage in my file:

------------------------------------------------------------------------
<categories><category categoryID="1"><products><product productID="1" /><product productID="2" /><product productID="24" /><product productID="34" /><product productID="35" /><product productID="38" /><product productID="39" /><product productID="43" /><product productID="67" /><product productID="70" /><product productID="75" /><product productID="76" /></products></category><category categoryID="2"><products><product productID="3" /><product productID="4" /><product productID="5" /><product productID="6" /><product productID="8" /><product productID="15" /><product productID="44" /><product productID="61" /><product productID="63" /><product productID="65" /><prod
(1 row(s) affected)

And that is the result of a sql query for xml exported to a file. Obviously, we can't pass this to an xml parser and expect anything to work. What to do? I spent an extra 1/2 hour and ended up making a little ADO.NET program that would:

1. Open a sql query file
2. Connect to a sql server database
3. Execute the query, read the full result set as an xml stream, and save a well-formatted xml document at the specified destination.
   

Here is the usage:

XmlExtract.exe [sql query file name] [connection string] [output file destination]
For example:
XmlExtract.exe query.sql "data source=pweissbrod0308\\sql2k5;integrated security=true;initial catalog=northwind;" "C:\output.xml"


And finally, here is the source code for my program:

using System;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Xml;

namespace XmlExtract
{
  public class Program
  {
      private static string connectionString;

      private static string outputFileName;
      private static string sqlQueryFilename;

      private static void Main(string[] args)
      {
          sqlQueryFilename = args[0];
          connectionString = args[1];
          outputFileName = args[2];

          TextReader textReader = new StreamReader(sqlQueryFilename);
          string query = textReader.ReadToEnd();
          textReader.Close();
          Console.WriteLine(query);
          var stringBuilder = new StringBuilder();

          SqlDataReader sqlDataReader;
          using (var sqlConnection = new SqlConnection(connectionString))
          {
              var sqlCommand = new SqlCommand(query, sqlConnection);

              sqlConnection.Open();
              sqlDataReader = sqlCommand.ExecuteReader();

              while (sqlDataReader.Read())
              {
                  stringBuilder.Append(sqlDataReader.GetString(0));
              }
              sqlDataReader.Close();

              var xmlDocument = new XmlDocument();
              xmlDocument.LoadXml(stringBuilder.ToString());

              if (File.Exists(outputFileName))
                  File.Delete(outputFileName);

              var xmlTextWriter = new XmlTextWriter(outputFileName, Encoding.UTF8);
              xmlTextWriter.Formatting = Formatting.Indented;
              xmlTextWriter.WriteStartDocument();
              xmlDocument.WriteTo(xmlTextWriter);
              xmlTextWriter.Flush();
              xmlTextWriter.Close();
          }
      }
  }
}

I hope this helps anyone with similar endeavors, feel free to paste this into snippet compiler to get a quick app up and running, or patch it into your current project for something more custom.

A quick snippet to delete all data in sql server 2005+

Note: this is a piece of code I've lost and rewitten a few times now, maybe you'll find it handy someday.

sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all';
go
sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER  all';
go
sp_MSForEachTable 'delete from ?', '?', null, null, 'and o.name not like (''system_%'')';
go
sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all';
go
sp_msforeachtable 'ALTER TABLE ? Enable TRIGGER  all';
go

What it does is disable all the relational constraints and triggers in the database, then wipes the tables clean, and re-enables the constraints/triggers at the end. Very fast, even compared with the TRUNCATE command.
For some reason, every command needs to be in a batch, hence the 'go' statements.

More thoughts on presence: Leveraging Virtualization

All too often, I run into security problems when working remotely for a client. I need to access a network resource, but since I am remote, I must wait until a sysadmin can help me or I find a less than ideal workaround.

This morning, I thought of a workaround:
Basically, I can prepare my own VM image with visual studio and all of my other development toys. I ask the sysadmin of the client's network to host my VM somewhere in his internal network. This way, I can VPN in to the client's network, access the VM via remote-desktop and viola! I have my own computer in their office.

This is a nice for me, because I have as much access as anyone else in the office. It is nice for the client (and the syadmin) because they don't need to loosen any of their security policies on my behalf.

Focused, yet, somewhere else...

Today has been one of the easier days for the past few weeks, its time for an update.

While its really cool to have regular subscribers asking me to post some more content, some more new ideas, I wanted to take a moment to discuss with you the skill of coping with heavy workload from multiple projects.

The picture of that disaster area is my personal office in Maine. Each screen represents a different project I'm currently working. I should have taken the picture last week when I had three laptops to strengthen my example!

Every single one of these projects is a very important obligation to me. Like any other project, there are naturally holds, emergencies, workarounds and communication overhead. This means that each project can be a bit of a hurry-and-wait scenario.

When you have multiple hurry-and-wait scenarios, you have the opportunity to context switch from your "hold" project to something else where the path is clear, to remain as productive as possible at all times. In theory, this works. In practice, CPUs will always be better at context switching than human beings, and too much switching ends up with a net result of... well.. nothing done.

People say that its essential to focus, to master the skill of doing one thing at a time, but I lack that tunnel vision; I find it very hard to cast a blind eye to any of my peripherals, because they are all important.

So I'm trying to work on a sweet spot in the balance between keeping a presence in multiple places, yet focusing my energy in one place at a time.

• A blackberry really helps to stay attuned to multiple clients
  
• Its nice to strictly enforce certain time slots to a specific project when possible
• Better managed and longer-term projects mean less context switching on my behalf

Any tricks work for you?