Toolbox
Synchronize Data, Review Runtime Errors, and More
Scott Mitchell
Synchronize SQL Server Data
For data-driven applications, developers frequently use multiple databases: one for development, perhaps one for staging, and one for production. For apps whose scope and feature sets continue to evolve, these databases start to differ from one another. For example, the production server's schema is designed from the feature set of the last stable release, whereas the development server's schema has been modified to incorporate the functionality currently being designed. Even if the schemas are identical, the production database has live, accurate data, but the testing server contains an approximation. Often, these databases need to be compared and synchronized.
Databases can differ from one another both in their schemas and in their underlying data, and tools typically focus on comparing and synchronizing either schema differences or data differences. The April 2006 Toolbox column introduced you to SQL Examiner, a product for comparing and synchronizing SQL Server™ database schemas. For comparing the actual data in two schema-identical databases, however, you'll need to use a different product, such as SQL Data Compare version 5 from Red Gate Software. (Red Gate Software also offers SQL Compare, a tool for comparing and synchronizing database schemas.)
SQL Data Compare prompts you to select the two schema-identical databases to compare, along with their related tables, columns, and records. The two databases are examined and SQL Data Compare provides the results in an intuitive, split-screen interface. The top pane lists a summary of the results—what tables were compared, whether the data in the tables are identical or different, and how many records are identical, different, missing, or additional. Clicking on a table in the top pane lists the details of the comparison in the bottom pane. For the differing records, you can see precisely which columns vary.
Quickly determining how the data differs is quite useful, but SQL Data Compare's real power shines in its synchronization capabilities. Let's say that the testing and production servers' schemas are identical, but using SQL Data Compare you've found that the production database has several new records, a handful of deleted records, and many column-level changes. To synchronize these two database tables by hand, you'd need to craft your own T-SQL script that had the correct combination of INSERT, UPDATE, and DELETE statements—a time-consuming effort, to be sure. With SQL Data Compare, simply click the Synchronize button and the T-SQL script is automatically generated. You can execute immediately against the database to synchronize, launch the script in SQL Server Management Studio, or save it as a text file.
Price: $295 for a single-user license.
Review Your Web App's Errors
In my day job I work as an independent consultant building ASP.NET Web applications for small- to medium-sized businesses. These applications are typically intranet-based and designed to move an existing paper-based business process to the Web. As such, when something goes awry I am quickly notified of the problem. Many times I am able to identify the problem and fix it before I actually receive their e-mails, which has convinced my clients that I am clairvoyant.
My little secret is a set of free, open-source HTTP modules and handlers designed by Atif Aziz (www.raboof.com) and referred to as Error Logging Modules and Handlers (ELMAH). Whenever a runtime error occurs in one of my Web applications, ELMAH logs the error and e-mails me its details. This provides me with a few precious minutes to fix the problem before the user has a chance to report it.
In a perfect world there would be no runtime errors. But we program in reality, where deadlines and never-ending feature requests are a given and where databases and Web servers can and do go offline. It's vital, then, that when such errors occur that the pertinent information be recorded and the developers of the project promptly notified. Without such error logging and notification you are at the mercy of your users to notify and provide you with the error's details; in short, you are making your users become testers.
When an unhandled exception is encountered in an ASP.NET application, the application's Error event is raised, which can be handled in the application's Global.asax file or by an HTTP module. ELMAH includes an HTTP module for recording the error's details in a database and another that provides notification via e-mail. For unhandled exceptions these details include the exception's message, stack trace, and request-specific details, such as the URL, query string, cookie values, post headers, and so on. In addition to its HTTP modules, ELMAH also includes HTTP handlers for displaying the error log in a Web page and as an RSS feed.
ELMAH is a cinch to set up and can be added to an existing Web site in under a minute without requiring any code changes or recompilation. Start by adding the ELMAH assembly to the application's /bin directory. Next, run the provided SQL scripts to create the database table and stored procedure used by the HTTP module for recording error details in the database. Finally, register the HTTP handlers and modules in web.config along with the ELMAH-specific settings. That's all there is to it!
Furthermore, ELMAH can be automatically deployed across all the sites on a Web server by adding the assembly to the Global Assembly Cache (GAC) and properly configuring machine.config.
ELMAH is a tool I could not live without; I use it religiously in all of my projects. The 30 seconds or so that it takes to set up ELMAH has saved me hours in debugging time and has greatly increased my turnaround time for identifying and resolving runtime errors. Just ask my clients, who are still baffled at how I can know the details about an error they just encountered before they tell me about it!
Price: Free. Includes source code.
Bring Windows Shell UI to Desktop Apps
Working with the file system is a task most computer users can handle regardless of their expertise level because of an unvarying user experience. Microsoft® programs use a consistent Windows® shell user interface for interacting with files and folders. Folders all have the same icon while files are represented by icons based on their file type. Moving files from one folder to another is as simple as dragging and dropping. To encourage the adoption of this UI, Microsoft provides Windows shell dialog boxes as part of the .NET Framework. The OpenFileDialog and SaveFileDialog controls display a dialog box for opening and saving files. The FolderBrowserDialog control provides a standard interface for selecting a folder from the file system.
While these Microsoft-supplied dialog box controls are helpful when designing Windows-based applications, there are times when their interfaces need to be incorporated directly within an existing form. Sure, you can use the TreeView control or ListView control for selecting a folder or file, but you're still on the hook for associating the correct icons with each folder or file type and adding the other standard Windows shell features.
Rather than building this UI from the ground up, consider using Shell MegaPack 7.1 controls from Sky Software. Shell MegaPack is a collection of four .NET-based controls designed to mimic all features of the Windows shell UI, including thumbnails, context menus, drag and drop, renaming, and so on. Want to add a tree of folders from which the user can select a particular one? Just drag the FolderView control onto your form and root the tree to a particular folder by setting the CurrentFolder property. You now have a folder selection UI that your users will be familiar with.
The Shell MegaPack also includes the FileView, ShComboBox, and ShellObjects controls. The FileView control provides a list of files, much like the file list in the OpenFileDialog and SaveFileDialog controls. If you need to list the folders in the file system in a dropdown list, use the ShComboBox control. The ShellObjects controls can be used to display Microsoft Office-style popups with slide and fade animations and system tray notifications. It also contains an API to create, enumerate, and dissect Shortcut (.lnk) and Internet Shortcut (.url) files.
Available both as ActiveX® controls and .NET libraries, Shell MegaPack 7.1 is an easy way for you to replicate the Windows shell user experience in your application's forms.
Price: $295 per developer license.
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 atScottOnWriting.NET.
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.