Toolbox
SQL Code Completion, Subversion Tools, Agile Development, and more
James Avery
Contents
Get Help Writing SQL
As a developer, it can be tough to switch languages. For instance, if you are using Visual Basic® .NET and need to write some C#, it can take time to remember the equivalent C# syntax. Happily, it's rare that as developers using the Microsoft®.NET Framework we need to write both Visual Basic .NET and C# on a routine basis. There is another language we write in quite a bit—SQL. But unlike Visual Basic .NET and C#, both of which provide IntelliSense® to help us remember language keywords, Visual Studio® 2005 and SQL Server® Management Studio don't provide any clues at all.
This is where SqlAssist comes to the rescue. There are two editions of SqlAssist that provide IntelliSense-like code completion: one for Visual Studio 2005 and another for SQL Server Management Studio.
The code completion in SqlAssist starts with basic SQL commands. For instance, instead of typing FROM you can simply type "f" and then press the Tab key. It also includes support for database tables and columns (even when they are aliased). In addition, SqlAssist includes support for local variables and displays parameter information when calling stored procedures or functions.
But perhaps the most interesting code-completion feature occurs when you are writing a SQL statement and you start to write a join clause: you will see a list of the foreign keys for the table you are querying. Select a foreign key, and the join clause will be automatically added.
SqlAssist also provides some valuable text editor support. When you type a table or column name, it will automatically ensure that the name is properly cased. For instance, vendoraddress will automatically be changed to VendorAddress. Furthermore, when opening SQL files, it is not uncommon for them to be formatted in strange ways (strange being any way other than how you like it); SqlAssist provides a SQL formatting functionality that will automatically reformat the file based on configurable options.
SqlAssist also provides a mechanism to create and store SQL code snippets, called SQL templates. You can easily create templates for commonly used SQL or for headers, and then you can drag them into your query when needed.
The Visual Studio add-in also provides excellent support for working with SQL files in Visual Studio. Simply open a SQL file and you will be able to connect to your database, get the support of SqlAssist's code completion, and execute your script files. Since installing SqlAssist, I haven't found myself opening SQL Server Management Studio except for true administration tasks like attaching or detaching databases.
Price: $49 per edition, $75 for both.
.gif)
SqlAssist Provides Code Completion (Click the image for a larger view)
Integrating Visual Studio and Subversion
Subversion is an open-source source-control system that has grown in popularity over the past couple of years. It takes a simple and user-friendly approach to handling many of the common source-control features like branching, tagging, and merging. Subversion's popularity is due to a number of factors: it is free and open source, it is easy to install and use, and it has excellent tools like TortoiseSVN. A Windows® Explorer extension, TortoiseSVN gives you the ability to perform all of your source-control functions right in the standard Explorer window instead of relying on a separate tool.
SourceForge has started providing Subversion hosting, and CodePlex even provides Subversion emulation so TortoiseSVN can be used with the site. With some .NET shops moving to Subversion, one of the features most often missed is the close integration with Visual Studio available through other popular source-control systems.
Enter VisualSVN, a Visual Studio add-in that provides excellent integration between Visual Studio and Subversion. When you use VisualSVN, you will see easy-to-understand markers next to each of your files in the Solution Explorer provided by Visual Studio (note that your solution must be stored in a Subversion repository). The markers show when a file is modified, unmodified, or conflicted. You can right-click on the file and show changes, revert changes, and update or commit.
Using the VisualSVN menu, you can manage changes for the entire project. The VisualSVN menu also contains shortcuts to common TortoiseSVN functions like the repo-browser, creating and applying patches, showing a subversion log, as well as branching, merging, and switching repositories.
One of the most important agile concepts is checking in early and often. By integrating as quickly as possible, you discover any potential merge issues, and if you implement continuous integration, all of your code will be built and your tests will be run. VisualSVN makes it easier to check in early and often because of the close integration with Visual Studio—you will constantly be reminded that you have modified changes that you haven't yet checked in. There's no reason to procrastinate since you can check those changes in directly from within Visual Studio.
Price: $49.
.gif)
VisualSVN Provides Function Shortcuts (Click the image for a larger view)
Rapid App Development
This past fall, Microsoft announced the ASP.NET MVC Framework, which enables developers to write Model View Controller-based Web applications. Many ASP.NET developers have been clamoring for this as an alternative to the standard ASP.NET page model.
Don't worry—the ASP.NET page model isn't going away. This just means that now we have another option. The
ASP.NET MVC Framework will make it easier to develop Web applications with a clear separation of concerns. The page logic will be stored in your controller, the HTML will be in the view, and the application logic will be in your model.
The ASP.NET MVC Framework is the first piece of the puzzle for Microsoft. A month or so after the MVC Framework was announced, the second piece of the puzzle fell into place: Microsoft hired Rob Conery to work full-time on his project, SubSonic.
SubSonic is an application toolset that is centered on its ability to completely generate your data access layer. Unlike some other Object Relation Mapping (ORM) frameworks, SubSonic takes the approach of generating and compiling your data access layer as opposed to performing a reflection-based mapping at run time (and for this reason, some would call it a code generation tool rather than an ORM).
SubSonic is different from other ORMs in how it maps your tables to your domain model. NHibernate, another ORM for .NET, takes the approach of specifying your model (or business entities) and then mapping those entities to your database through either XML or .NET attributes. SubSonic switches that around and creates a model based on your database schema. Both approaches have their advantages and disadvantages, but usually I find it comes down to personal preference.
To get a complete model of your database, install SubSonic and configure it in your app by way of a few settings in your web.config file. The model consists of simple classes with the same names as your tables, which contain methods for all of the common database actions. For instance, a Product table would translate to a Product class and have methods like Save, Destroy, and Where, the latter being used to query the database. A separate Query object is available for complicated queries.
Unlike most ORM tools, SubSonic provides a graceful way to handle stored procedures. Stored procedures can be wrapped in a single class and are called the same way you would call a method. Accessing views is also supported.
While the ORM portion of SubSonic is its largest feature, SubSonic also includes numerous other helper functions and controls. One of the more useful controls, Scaffold, makes it easy to get a quick application up and running—and while you might not use it for your production code, it's perfect for building a prototype. Some of the more useful helper methods center on validations with a host of different validation methods available for just about every scenario (numeric, alpha-only, ZIP codes, every major credit card, and so on)
SubSonic on its own is a very powerful application toolset; when paired with ASP.NET MVC, it becomes a valuable application framework that will be a compelling competitor to other rapid development application frameworks available in .NET and other languages.
Price: Free, source code available.
The Bookshelf
When Agile methodologies first started getting popular, they were largely disconnected from the modeling and project management software at the time; unified modeling language-heavy design phases and gigantic Gannt charts were counter to everything Agile was championing. As Agile methodologies have become more mainstream, tools have been created to cater to Agile projects. In fact, the premier application lifecycle management tool from Microsoft, Visual Studio Team System (VSTS), was built to work well with Agile (although you don't have to use Agile to use VSTS).
Visual Studio Team System: Better Software Development for Agile Teams by Will Stott and James W. Newkirk (Addison-Wesley Professional, 2007) is an excellent introduction to both the Team System tool and to working on an Agile project. The book introduces the pieces of Visual Studio Team System and explains how they fit together.
Following this is a brief lesson on Extreme Programming. You're better off reading a separate book dedicated to Extreme Programming, however, if you want to use it on your project. The book then goes into more detail on the Microsoft Solutions Framework for Agile processes and how to go about changing your current process.
Then the book starts digging into the details. It walks through source control, continuous integration, and test-driven development, highlighting how Team System provides functionality that lines up well with these core Agile practices. Each section not only includes good practical advice but includes step-by-step Team System instructions and screenshots.
The book goes on to cover an Agile-friendly method of modeling with Class Responsibility Collaborator (CRC)card-inspired, lightweight modeling. I was pleasantly surprised to see a section dedicated to the Framework for Integrated Test (FIT). FIT is an open-source testing tool that can enable your users or business analysts to drive the testing of your app.
The book is based on Visual Studio Team System 2005, but Visual Studio Team System 2008 will probably have shipped by the time this column is published. The general guidance will still be relevant, but the version-specific instructions and screenshots may become out of date.
There are better books on Agile, and there are others that take a more methodology-independent approach to learning Team System. However, if you are in that perfect cross-section of starting an Agile project using Team System, this book is the ideal place to start.
Price: $49.99.
.gif)
(Click the image for a larger view)
Send your questions and comments to toolsmm@microsoft.com.
James Avery runs his own .NET consulting practice, Infozerk Inc. His most recent book is
Windows Developer Power Tools (O'Reilly, 2006). You can e-mail him at
javery@infozerk.com and read his weblog at
www.dotavery.com/blog.