July 2009
Volume 24 Number 07
Toolbox - Static Analysis Database Tools, Managing Remote Computers, And More
By Scott Mitchell | July 2009
All prices confirmed at press time and are subject to change. The opinions expressed in this column are solely those of the author and do not necessarily reflect the opinions at Microsoft.
Contents
Static Analysis for Your Database Design
.NET Podcasts
Manage Remote Computers from One Program
The Bookshelf
Static Analysis for Your Database Design
Software design standards are an important aspect of building reliable and maintainable applications. Most companies have some sort of coding standards, such as naming conventions and security guidelines. Static code analysis tools, such as FxCop and StyleCop, are useful for evaluating an application's intermediate code or source code to ensure that it conforms to the standards recommended by Microsoft or defined by your company. (FxCop and StyleCop were reviewed in the December 2008 issue, "Static Analysis Tools For .NET, Matt Berseth’s Blog.")
But what about database design and configuration? Many companies have naming conventions for tables and their columns, as well as guidelines for column data types, such as under what circumstances NULLable columns are permissible. The same applies for the use and cascade behavior of foreign key constraints and triggers. There may also be configuration standards that apply to login credentials, backup schedules, and other concerns.
ApexSQL Enforce (version 2008.04) is a static analysis tool for Microsoft SQL Server databases. When you first run ApexSQL Enforce, you are prompted to select the rule base to use. Each rule is associated with a database object type, such as the server, a table, a trigger, a column, or a primary key. The rules are classified further into categories such as Server Configuration, Database Modeling, Performance, and so on. Each rule is assigned one out of six possible severities, ranging from Info to Critical. A Best Practices rule base ships with ApexSQL Enforce and contains more than 80 rules, including ones that apply to database maintenance and administration. The Critical severity rule, for example, requires that the database be backed up at least every seven days. There are also data modeling rules, such as a High severity rule that ensures all tables have a primary key and a Tip rule that recommends against naming tables with a "tbl" prefix.
After selecting the rule base and the particular rules to apply, you choose the database to analyze. ApexSQL Enforce runs the selected rules against the specified database and displays any rule violations in a separate tab. Each rule violation includes a summary, noting the rule that was violated and the database object in violation; a description of the violated rule; and advice on how to remedy the violation. For some rules, ApexSQL Enforce can provide the T-SQL syntax to fix the violation.
ApexSQL Enforce offers a high degree of customization and flexibility. For instance, you can create your own rule bases or modify the built-in Best Practices rule base. Rules are defined using C# or Visual Basic code. Creating and modifying these rules is straightforward thanks to a built-in rule editor that offers syntax highlighting and IntelliSense support. ApexSQL Enforce can be run from its graphical user interface or from the command line, and violation reports can be exported to XML or rendered into an HTML report.
Static analysis is a useful and automated technique for ensuring that your software design adheres to prescribed standards. ApexSQL Enforce is a powerful and customizable tool for applying static analysis to your databases.
Price: $999
www.apexsql.com/sql_tools_enforce.asp
.gif)
ApexSQL Enforce
.NET Podcasts
Podcasts are audio or video productions that are available for download over the Internet and are typically played on portable MP3 players, such as the Apple iPod or the Microsoft Zune. Unlike streaming audio shows, podcasts are prerecorded and syndicated. You can configure your MP3 player's software to automatically download the latest shows of your favorite podcasts—they are great listening material for your daily commute.
There are a number of very interesting, educational, and well-produced podcasts specifically for Microsoft .NET developers. One of my favorite .NET-focused shows is Hanselminutes, a weekly audio podcast hosted by Scott Hansel man, a senior program manager at Microsoft. Most episodes are around 30 minutes in length, focus on a single topic, and include a guest who is a pioneer or an expert on the topic of discussion. What I like best about the show is the wide range of subject matter, with episodes on specific technologies—such as JavaScript, ADO.NET Data Services, and parallel programming in .NET—as well as shows that discuss software development models like Scrum, test-driven development, and the SOLID principles of object-oriented design.
What sets Hanselminutes apart from many other podcasts is its consistent schedule and level of quality. A new Hanselminutes episode has appeared nearly every single week since January 2006, with over 155 episodes so far. All past episodes (as well as each week's new episode) are available for download in several popular multimedia formats. And Scott does a great job interviewing his guests and keeping the dialogue interesting and on-topic.
Price: Free
Manage Remote Computers from One Program
Tools like Virtual Network Computing (VNC) and the Microsoft Remote Desktop Protocol (RDP) make it easy to log into and manage remote computers from your home or office and have long been used by system administrators to manage remote computer assets. If you are tasked with managing many different computers, or routinely find yourself with multiple remote connections open at the same time, check out Terminals (version 1.7e), an open-source project that consolidates managing and running remote connections. When a remote connection is launched from Terminals, it is displayed in a new tab within the Terminals user interface. This tabbed UI streamlines multiple, simultaneous remote connections into a single window. And Terminals works with a variety of protocols, including VNC, RDP, Virtual Machine Remote Control (VMRC), Remote Access Service (RAS), Telecommunication network (Telnet), and Secure Shell (SSH).
When Terminals is launched for the first time, it searches your computer for remote connection files and adds any discovered connections to the Favorites window. You can edit these automatically added entries, or you can manually add your own remote connections to Favorites. And with a few clicks of the mouse, you can customize these remote connections' display and behavior properties, supply connection credentials, or categorize the connections using tags. Double-clicking one of the remote connections in the Favorites window causes Terminals to connect to that computer in a new tab. There's also a History view that shows what connections were made today, yesterday, in the past week, and so on.
Terminals includes a number of helpful tools and utilities. There's a feature that makes taking screen captures of remote connections as easy as clicking a button, and a manager that catalogues and organizes your screen captures. Terminals also offers a suite of network-related utilities. For example, you'll find tools for performing a Whois or DNS lookup from within Terminals, along with tools for examining the shares on a local or remote computer. Other tools show information about the local computer's Network Interface Controllers (NICs), the open connections, and the packet traffic. There's also one-click access to common system administration and network maintenance configuration. The Terminals toolbar contains shortcut icons to programs like the Registry Editor (regedit.exe), the Computer Management Console, the Control Panel, and the local computer's Internet Properties, Network Connections, and Services Manager, among many others.
Terminals is a nifty application that consolidates working with remote connections into a single, simple interface. If you regularly connect to remote computers, give Terminals a try.
Price: Free, open-source
.gif)
Terminals
The Bookshelf
Most .NET developers are familiar with ADO.NET, the data access library that has been part of the .NET Framework since its inception. One challenge of using ADO.NET is that the developer must always keep the details of the underlying data store in mind. When querying data, the developer must be cognizant of the tables to query, their relationships, what JOINs are needed and whether they are INNER or OUTER JOINs. When retrieving data from a DataSet or a DataReader, the developer must recall the column names and their data types.
Over the past several years, Microsoft has been developing the ADO.NET Entity Framework, a new library for accessing data. When using the Entity Framework, you do not run queries against the database. Instead, you query the Entity Data Model, which is a set of classes in your application that model the database's structure in an object-oriented manner. In addition to the Entity Data Model, the Entity Framework maintains a logical model of the database and a map that indicates how the objects in the logical model correspond to the objects in the Entity Data Model. While the Entity Framework is not a replacement for ADO.NET, it is an important tool that Microsoft is investing in and is being used in technologies like ADO.NET Data Services and ASP.NET Dynamic Data.
One of the best ways to learn about the Entity Framework is to read Julia Lerman's book, Programming Entity Framework (O'Reilly, 2009). Julia's book starts with a solid overview of the design goals of the Entity Framework, its pros and cons, and where it fits into Microsoft's data access story. The reader is shown how to create an Entity Data Model using the Designer, how to query against this model, how to insert, update, and delete entities, and how to display and manage data via the Entity Framework in WinForms, Windows Presentation Foundation (WPF), and ASP.NET applications, as well as in Web Services and Windows Communication Foundation (WCF) services.
At nearly 800 pages, Programming Entity Framework is fairly hefty, but it offers a solid grounding in using the Entity Framework. The book assumes its readers are intermediate to advanced .NET developers who are already familiar with database concepts, ADO.NET, LINQ, and other core .NET features and spends no time introducing these topics. Instead, the book is packed with walkthroughs that illustrate the use of Entity Framework in various scenarios. It also does a great job pointing out what this first version of the Entity Framework can and cannot do and what use cases are difficult or tricky to implement, along with workarounds where appropriate.
Julia's use of real-world examples really helped me to understand and become familiar with using the Entity Framework. In the second chapter, Julia introduces a simple database with a handful of tables, views, and stored procedures. She uses this database over the next several chapters to highlight the features of the Designer and to demonstrate working with entities using both LINQ to Entities and Entity SQL queries. Later, in Chapter 7, Julia unveils a more complex, real-world database that contains many more tables and more types of relationships. She uses this new database throughout the remainder of the book to illustrate more advanced topics, such as customizing entities and the Entity Data Model, transaction processing, handling exceptions raised by the Entity Framework, and so on. These two databases, along with the complete sample code, are available for download from the book's Web site, LearnEntityFramework.com.
Price: $54.99, 792 pages
Programming Entity Framework
Send your questions and comments for Scott to toolsmm@microsoft.com.
Scott Mitchell, author of numerous books and founder of 4GuysFromRolla.com, is an MVP who has been working with Microsoft Web technologies since 1998. Scott is an independent consultant, trainer, and writer. Reach him at Mitchell@4guysfromrolla.com or via his blog at ScottOnWriting.NET.