T4 Slays Spaghetti Monster

Posted on by Tom Wayson

My client wanted a list of all of the tables and their respective columns in a given SQL server database.  My first instinct was to get Visio to create an ER diagram by reverse engineering the database. However, with dozens of tables in this database, the diagram that Visio spit out looked like spaghetti and meatballs, and would have printed across 36 8.5″x11″ pages. That said, Visio did a better job laying out the diagram than I would have by hand.

Then I thought, what if I had an automated way to create a document that listed the tables in alphabetical order, showing all of their columns? This list would be something that the client could cross-refrence against the spaghetti monster. Well, thanks to T4, I do have such an automated way. I spent my lunch break hacking away (it’s scary to see how much you rely on Intellisense once you don’t have it) at this simple template that will iterate through all the tables in a database and output all the columns for each as a text file. To get it work,follow these steps:

  1. Download the template file (all_table_columns.tt).
  2. Copy it to any Visual Studio 2005/2008 project.
  3. Open it, replace “[your connection string here]” with a valid SQL Server connection string (ideally to a database with a limited number of tables), and then save the file.

Visual Studio attempts to generate the output whenever you save the template file, so the new text file should already be generated.  To view it, go to the Solution Explorer window and expand the node for all_table_columns.tt and you will see a file named all_table_columns.txt. Open that text document and you should have a list of all tables and columns!

For more on code generation via T4, see Scott Hanselman’s link-fest of a post.

myjoglog.net – Iteration 1: Completing the Workout Life Cycle

Posted on by Tom Wayson

So, if you read my first post in this series, you know I’m creating a MVC web application to log and share workouts. Picking up where I left off, I’ve got to complete the life cycle of a workout (insert, select, update, and delete).

Completing the Life Cycle of a Workout

In the first post we covered adding a new workout, and selecting recent workouts. That leaves updating and deleting workouts. To save time we’ll just implement the delete functionality and leave updating for later. To enable deleting all I had to do was add a controller action that would perform the delete operation against the database and add forms to the default view that would post to this controller action.

The new controller action in WorkoutsController.vb:

Note that the action only accepts HTTP POST requests and that it expects the MVC framework to map an input value to a local integer variable called workoutId which it then uses to query and delete the workout record in the database.

The new forms (one for each row) in Index.aspx:

Delete Workout Form

This will write a form that contains a delete button and hidden input for the WorkoutId for each row in the table. Clicking on the Remove button on any row will delete the record and redirect the user back to the home page. You can try it out here:

http://myjoglog.net/workouts/

Wow, that was easy and I was able to implement it in about a dozen lines of code or so. I <3 MVC.

In my next iteration, I’ll create some user stories to see what direction I need to go from here. I know, I know, this is a little backwards. I probably should have started with pen and paper (and what of unit tests???) and then gotten into the code, but MVC is new to me, and I needed to write some code just to understand the life cycle of a page. Besides, better late than never, right?

Stay tuned…

My First ASP.Net MVC Application – http://myjoglog.net

Posted on by Tom Wayson

I’ve been very excited about ASP.Net MVC ever since I started hearing about its emergence in various blogs and podcasts. MVC basically addresses all of the issues I have with developing ASP.Net web sites (viewstate, postbacks, testing, etc), and I’ve eagerly awaiting a release its release.

Well, now that ASP.Net MVC is in it’s first beta, I thought it was time to jump in and get my hands dirty with some code. All I needed was an idea for a demo app. My brother-in-law was telling me that he needed an application to log his progress and help motivate him to train for an upcoming marathon. Specifically he said that there are lot of sites out there that allow you to log your own training, but he was really interested in one that would emphasize sharing your logs with your training partners as means to help keep each other motivated. So I started working on a small web application that enables users to log and share their workouts when training. You can follow along as I develop it at:

http://myjoglog.net

Iteration 0: Setting Up the Development Environment

To do create this application on my dev machine, I would need to install ASP.Net MCV Beta (as well as VS 2008 SP1).

I’d also need to find a hosting service that would allow me to host and MVC application (I went with reliablesite.net), and to that end, these threads were helpful:

  1. Step by step instructions for configuring your hosting environment: http://forums.asp.net/p/1239943/2294813.aspx
  2. Which assemblies you’ll need to include in the /bin folder: http://haacked.com/archive/2008/11/03/bin-deploy-aspnetmvc.aspx

Iteration .5: Putting Something Out There

By coincidence (I swear) Stephen Walther and Paul Litwin create a workout log application in their ASP.Net MVC video series. As a first iteration, I thought it would be a good idea to basically go through this series step by step and publish the output on my new hosted. After walking through the steps in the videos, I modified the pages slightly to use the site’s master page, which I also modified a bit to handle navigation for the newly added pages.

You can see the results at: http://myjoglog.net.

Coming Soon… Iteration 1: Completing the Workout Life Cycle

Creating an app that adds and displays data in just a couple of hours (thanks to the help of the template code and tutorials) isn’t too shabby, but there’s lot’s of work to be done. For starters, I want to be able to complete the life cycle of a workout (insert, select, update, and delete). So I’ll do that next.

After that it’s time to take a step back, and review the user stories to get a firm idea of what the app will need to be able to do – this is where my path will diverge from the good start set out by Walther and Litwin. With the big picture in mind, I’ll be able to layout the subsequent iterations that will allow me to get the app fully functional in time to help my brother-in-law train for his marathon.

Stay tuned…

The Missing LINQ (to Oracle)

Posted on by Tom Wayson

Since all the .Net applications I am currently working on use Oracle on the back end, I haven’t spent a lot of time looking into LINQ – LINQ to SQL only supports SQL Server. After attending the 2008 launch event and getting a chance to put my hands on some LINQ examples in the labs I didn’t get the feeling that I was missing much. I currently use SubSonic as a means of automating the creation of an abstraction layer between my UI code and the database, and I love it. I’ve been considering it a bridge that will last me at least until LINQ to Entities comes out later this year, at which point I’ll have to evaluate the pros and cons of each. Scott Hanselman’s recent interview with Mike Pizzo provides a great in-depth analysis of the differences between LINQ to Entities and LINQ to SQL. As with any abstraction layer, performance seems to be the question on everyone’s mind. It would be great to see benchmarks of the same queries of the same database for both Linq to SQL and Linq to Entities. Which brings me to my point, I would love to see a Linq to Oracle. I’m sure Oracle is on board to create a provider for the Entity Framework, and I know the EF provides added value (mapping entities to multiple tables, and vice versa), but I would also like to see Oracle provide an implementation of the IQueryable interface that is lean and mean for simple applications. In the mean time, I think I’ll check out the DbLinq Project, which claims to provide LINQ implementations for databases other than SQL Server. I was going to look into this when I first started using SubSonic, but I saw the Nov 07 update that said the Oracle Provider was no longer supported. Still, it’s probably worth downloading and comparing.

Google Calendar Sync for Outlook

Posted on by Tom Wayson

A couple of days ago Google released Google Calendar Sync for Outlook.  It’s free, so I downloaded it and gave it a test drive.  In summary, it’s not ready for prime time… yet.  It’s major limitation is that (unlike Google Sync for your Blackberry which allows you to choose which of your calendars to sync) it will only sync your primary calendar.  Also, unlike GSyncIt ($10), you cannot choose which Outlook calendar you want to sync with.  Once Google overcomes these limitations, I’m sure it will be a superior product to any competitor’s and of course, well worth the price.

script.aculo.us Slide Show

Posted on by Tom Wayson

My client, Terra Nova Pools, wanted to add a new page to their site that would display a looping slide show of before and after pictures of their clients’ pools. I decided to implement this page using this solution that relies on the script.aculo.us javascript library to fade a series of div tags in and out sequentially. The solution worked well with the exception of one small glitch – the screen seemed to jump between slides in FireFox (see comments on page above). I solved this by setting the position attribute for all slide divs to absolute with the following line of css:

div.slide { position: absolute; }

You can see the results at: http://www.terranovapools.com/clients/before-and-after/

I chose this solution because I knew I could rely on the script.aculo.us library to support a wide array of browsers as well as allow me to use web standards to ensure that the page would degrade nicely for users on mobile devices or older browsers. I had previously used Dynamic Drive’s ultimate fade in javascript slide show for the site’s home page, but that did not work at all in Safari and required defensive coding to be able to degrade nicely.

Blackberry Sync Part 1: Blackberry Calendar to Google Calendar

Posted on by Tom Wayson

UPDATE: I am no longer using the solutions outlined in this post. See my more recent post about Google Sync.

Like everyone else, I use Google calendar as my primary calendar. Having just bought a Blackberry Curve 8320, I wanted a way to sync the calendar on the device w/ my Google calendar. Fortunately (or unfortunately) there is no shortage of ways to do this. After trying several methods I ended up deciding to use CompanionLink for Google Calendar, and for me it’s worth every penny of the $29.95 price tag even though there are several free solutions out there.

Here’s what I was looking for

  • Direct desktop synchronization, OR
  • Synchronization via Outlook 2002 (paying for an upgrade of Outlook to 2003/2007 is out of the question)
  • Robust support for all day events, recurring events, and reminders
  • Automatic or periodic syncing whenever the device is connected to the PC

Companion link was the only solution that met all of those requirements. The only limitation that I have come across is that it will not sync multiple calendars, but that is not a deal killer for me. I should also say that I was not able to get it to work properly until I found this set up guide.
Here’s some of the alternatives that I didn’t choose and why.

Desktop Syncing Solutions
It seems like there are a lot of Outlook 2003/2007 plugins that will do what companion sync does for free or at least for less money. That’s great if you have Outlook 2003/2007, but I don’t so I couldn’t really say how well these work or don’t. Maybe they’ll work for you.

“Over the Air Syncing” Solutions
The appeal of over the air syncing is obvious since you don’t need to be tethered to a PC to sync. The reality, however, is a little different. Many limit the number of days ahead that can be synced (for data traffic reasons), and some require pretty heavy tweaking of your network connection settings – which can be problematic for other apps on your device.

  • GCalSync (free) – You get what you pay for. I was able to get this working after tweaking some connection settings, but the resulting calendar events on the hand held were off by several hours. Although there is a fix for the apparent timezone issues, I would still say that this is not yet ready for prime time since it does not support recurring events, reminders, and it even messes up all day events. Furthermore you must manually start the sync each time.
  • GooSync – This is really just a service that will provide a SyncML host for your Google calendar data. It requires a download of a separate SyncML client app for $19.95. I could not get the client app to work at all, so my money and time go elsewhere.

“No Sync” Solutions
You may even wonder why I would want to sync at all since I can access my Google calendar via the Blackberry web browser or Opera mini. Mainly it’s because even though my Blackberry’s screen may be tiny, I like to start out by looking at a whole month, not an agenda. And what happens when I want to see if I’m free some day 3 months from now? That being said, the mobile web version of the Google calendar does fill in the gap nicely when my Blackberry’s calendar may be out of sync, or if I need to see something on one of the other calendars (besides my primary calendar).

Hopefully, Google will soon release a mobile app for their calendar just like the ones for gmail and maps. Then we can put all this unpleasantness behind us.

Convert GIS Data Files to GeoRSS Format

Posted on by Tom Wayson

Since my last post on the topic of adding polygon data to your VirtualEarth map, VE has released not one, but two versions of the API. Starting with version 5 you are now able to add a layer containing polygon and line features to your map via a GeoRSS feed. Pretty cool.

Now the hard part becomes getting your GIS data in GeoRSS format, and that is where the GeoFeeder from BrightiSolutions comes in. It’s a desktop application that reads many types of GIS data (including ESRI Shapefile) and outputs the features in a GeoRSS file format. Check out the demo to see how you can customize the output.
Unfortunately it’s not free, but it could well be worth the $95 license if you don’t have the time to write your own code to converting your GIS data to a GeoRSS file.

Adding Polygon and Polyline Layers to a Virtual Earth Map

Posted on by Tom Wayson

We all know that the Virtual Earth (VE) API makes it easy to overlay a layer of your own georeferenced point data as a layer of pushpins on their maps. However, what if you need to add non-point georeferenced data such as zip codes or pipelines? The VE API only supports two types of layers: point and tiled image. The VE API does support adding both individual polygons and individual polylines to any map (and impressively these shapes will even be displayed in 3D mode), but there is no support for adding a layer of such features. In this post I will look at a couple of hacks for achieving this and the reasons why you might want or (most likely) not want to implement them. In Part 2, I’ll explore using tiled image layers to display non-point data in VE.

The first solution that I have found to this problem comes from http://www.blogthevote.net/veshapefilec.htm. This site displays a VML overlay containing the polygon or polyline features on top of the map. While this is an ingenious solution, I wondered why they did not take advantage of the VE support for adding polygons and polylines directly to the map instead of using a VML overlay. After all, this required them to not only write their own code to generate VML graphics, but also to handle rescaling and shifting the graphics when the user panned or zoomed the map. In addition to the burden of writing more code, there are issues with browser support for VML (though I will admit it is much better now than it was a few years ago). I suspected that the only reason to go to that much work would be to improve performance, and I would quickly confirm my own suspicions.

I created my own demo application (http://waysonweb.com/maps/ve/demos/polylayer/) by tweaking their code a bit to try adding the features as polygons or polylines to the map directly using the VE API instead of creating a VML overlay. Using the same Seattle zip code data for comparison I found that the initial load of the maps was slower, as were subsequent pan and zoom operations, but the difference in performance was barely perceptible. However, the performance went well into the “unusable” end of the spectrum when I added my own higher resolution data (with more vertices per polygon). Using my demo application, it took about 90 seconds for the initial map load and around 20 second for each subsequent pan or zoom operation. Further testing demonstrated that the bottleneck was not in the parsing of the coordinate data (usually < 10% of total processing time), but was indeed on the VE server’s end of the load operation.

Essentially, if you want to use the support for polygons and polylines built-in to the VE API to display a layer of GIS data, you will quickly find yourself limited by the number of vertices that make up the features you add to the map. It works great if you want to add a rectangle representing the state of Wyoming, but it is not ready for real world GIS data (imagine trying to add all the property parcels in Los Angeles County).

Another option for adding your own polygon and polyline data to a VE map is the tiled image layer. In Part 2, I’ll examine the pros and cons of this promising method, but in the mean time, you can view an example at http://showcase.sharpgis.net/ve/.