GenXJamerican

I saw this insightful article on the subject in a “most popular articles” list. The author, Douglas Reilly, acknowledges at the beginning of the article that he uses stored procedures virtually all the time, but still does a great job of describing the pros and cons of each approach.

The most useful thing the article taught me was that as of version 7, SQL Server keeps execution plans for all statements, not just stored procedures. This reduces the advantage of stored procedures for basic CRUD (create, read, update, delete) operations substantially. I’ve certainly touted the superior performance argument in comparing stored procedures to ad-hoc SQL before, so I’m glad I learned the real deal now. That said, I still hold a bias in favor of stored procedures. Once upon a time, I worked for Sybase Professional Services as a DBA, and they were definitely pro-stored procedure. Nearly every shop I’ve worked in since then has been similarly inclined. Anytime an employer has used ad-hoc SQL, it’s been absolute murder to maintain. In my experience, the maintenance advantage of stored procedures over ad-hoc SQL is substantial, not slight (as Reilly concludes).

One issue the article doesn’t bring up that might be interesting is source code control for stored procedures and other database elements (views, user-defined functions, etc). While SQL Server does have backup and restore, it’s not nearly as granular or convenient as the check-in/check-out model of today’s source code control systems. On my projects, I tend to generate a script with just the stored procedures in it and store that in SourceSafe. I’d certainly like a better solution, but haven’t found one yet. I’m curious to see what SQL Server 2005 is like, since it will allow you to write stored procedures in C# or VB.NET.

I’ve got a small project at work that required me to use the date, but not the time in a datetime field for retrieval of certain rows from a table in SQL Server 2000. A bit of Googling revealed a document titled “Remove seconds from datetime SQL”. The title isn’t quite right, as the author’s query removes hours and minutes too, but the result turned out to be just what I wanted.

The query: SELECT Cast(Convert(varchar, GetDate(),105) as datetime) I ended up using code 101 instead of 105 to get my query working, but that was it.

Writing code for work on a Sunday is certainly not my idea of a good time. Especially when our approaching deadline is inspiring panic and all manner of apocalyptic pronouncements about bits of code that aren’t working.

The latest panic compelled me to do a bit of research (i.e. Googling) to find out what constituted a valid Social Security number. Google kindly coughed up this Wikipedia entry. A quick scan through the article yielded this page from the Social Security Administration. Titled Social Security Number Allocations, it lists the valid code ranges for all 50 states, DC, and U.S. territories.

The regular expression you get from Microsoft’s web user control for SSN validation is relatively simple: d{3}-d{2}-d{4}. One of the developers changed things to use this: ^(?!000)([0-6]d{2}|7([0-6]d|7[012]))([ -]?)(?!00)dd3(?!0000)d{4}$

Sure, it’s gold plating. But it works. It would be more trouble to take out than it was worth anyway.

VS.NET really annoyed me today. When I changed my custom page base class to be abstract, the web forms that inherit from it could only be seen in HTML mode. Their HTML editor leaves a lot to be desired. Maybe I’ll have to figure out how to switch to SciTE automatically when the visual designer croaks.

On the other hand, not using the visual designer does get rid of VS.NET’s annoying habit of trying to create new declarations of web user controls in the web form code-behind when you’ve already got them declared in a custom base page class.

Open source on the .NET platform is a topic I’ve been thinking about for awhile. My current boss has made it a point to try and use open source applications built with .NET whenever possible. This is the first in an occasional series of posts on the topic.

A good definition of the term “open source” comes from Vaskin Kissoyan of Lokion, Inc.. Here’s the software developer aspect of the full definition:

"Open Source Software allows a developer to contribute to an already existing product or application, it also provides a rich set of library code (basic functionality) effectively giving you an almost unlimited amount of blocks upon which to build your applications. The OS community fosters reuse so there is no need to re-invent the wheel - a major problem for all developers before the dawn of Open Source."

How well does this definition apply to some of the software built using .NET since the year 2000? In some respects, it fits quite well. When it comes to library code, Microsoft has provided Application Blocks, then the more robust Enterprise Library. Microsoft has also done a good job of providing reference applications as starting points for future development. This is a practice I first took advantage of during the Windows DNA days when I used the Fitch & Mather Stocks application as the basis for an online recruitment tool I built for Ciena in 2001. Since that time, sample applications like Duwamish Books and a myriad of starter kits have been the basis for many custom development efforts.

The Issue Tracker starter kit was the first open source .NET application our group at Aspen Systems (now Lockheed Martin IT) tried to use. The group lacked a formal, centralized way of tracking bugs in the software we developed, so my manager saw it as a no-cost way to build our skill with C# (it’s also available in VB.NET) and .NET and improve our development process.

Looking back on the experience, we should have answered the following questions before we moved forward with the IssueTracker starter kit:

1. Is open source the best choice for this application (buy versus build/extend)?
2. What platform are your developers most skilled at building for?
3. Is the best open source application on the .NET platform?
4. Is it our goal to re-sell an application we've extended?

At the time, I could have gone either way on whether an open source application was the best choice for issue/bug tracking. There are tons of commercial, closed-source options with plenty of functionality. One such application is FogBugz. The latest version has great e-mail integration, discussion groups, source code control integration, support for release notes, RSS and more. It can get expensive, but selling custom software for a living should mean that we recoup those costs.

Even if there was no willingness to spend money (and there wasn’t) and open-source was the direction chosen, Bugzilla is used by enough companies and organizations (including Id Software, NASA, and Akamai) that we should have considered it far more strongly than we did.

We were (and still are) primarily a Microsoft shop, so the developers were most familiar with that platform. Unfortunately, too many of them were new enough to ASP.NET that they wrote applications the same way they would have in classic ASP. This was probably why boss felt the application would be a good learning tool. When it comes to open source, the odds that the best solution will be written in .NET are low. None of the open source bug/issue tracking solutions written in .NET that I was able to find came anywhere close to Bugzilla or FogBugz in user interface, functionality, or documentation.

We weren’t going through the trouble of extending Issue Tracker because we wanted to re-sell it. It was (and is) strictly for internal use. From my perspective, the only reason to assign people from revenue-generating projects to extending this app was if we planned to re-sell the resulting application.

At the time, I believed we made the wrong choice in trying to turn Issue Tracker into a production system. The year that has past has only strengthened that belief. The resulting application is regularly changed and updated to accomodate requests from any number of people. It takes development resources we really need for projects that make the company money. It still lacks useful features that can be found in more mature products (both open source and COTS) .

In my next post on this subject, I’ll examine how these four questions apply to a more robust open source .NET application: Community Server.

In the nearly 11 years I’ve been writing software for real money, I haven’t had any internationalization projects until now. The project I’ve been assigned most recently is an electronic forms application that will allow the spouses of police, fire, and other public safety officers injured or killed on duty to apply for benefits. It certainly isn’t the most cheerful app I’ve ever helped write, but it’s definitely one of the more important ones.

Dealing with resource files has been the biggest pain of the entire project. We lack consistent naming conventions for variable names. We also lack consistent locations for the variable names. This results in the need to pull strings for one form from multiple resource files. Sometimes, variable names and locations have been changed arbitrarily and without warning. This meant that code I’d written one day that displayed the correct text, displayed nothing the next day. A few of those changes cost me half a day of rework (and every bit counts when your deadline won’t move).

When I finally had a chance to look for information on internationalization in .NET, I came across an excellent article at DevX. It’s the first in a 3-part series of articles that ends with a decent checklist of issues to consider in an internationalization project. The sample application is more complex than my assignment because they’re internationalizing database content as well as static content.

So far, I think we would have helped ourselves a lot if we’d thought of the resource files as classes. I’m not sure what the performance implications of more resource files with fewer strings would be versus fewer resource files with more strings, but I think it would improve our speed of development.