December 2009
Volume 24 Number 12
Toolbox - Database and OR/M Tools, Oren Eini and Custom Visual Studio Tabs
By Scott Mitchell | December 2009
Enhancing the LINQ to SQL and ADO.NET Entity Framework Designers
LINQ to SQL and the ADO.NET Entity Framework are two object-relational mapping (OR/M) implementations from Microsoft. Using these OR/Ms entails creating a .dbml or .edmx file that contains a mapping between the relational model and the object model. Under the covers, this mapping is defined via XML, but the Visual Studio designer makes creating such mappings as easy as drag and drop.
If you use LINQ to SQL or Entity Framework in your applications and are in charge of maintaining the mapping files, check out Huagati DBML/EDMX Tools (version 1.76), which adds a number of features to the Visual Studio LINQ to SQL and Entity Framework designers.
Observing prescribed naming conventions in the object model is a common challenge with drag and drop OR/M tools. Database object names often include prefixes like “tbl” or “usp_”, or may be entirely capitalized or lowercase. When adding a database object to the designer, Visual Studio creates the corresponding class using the same prefixes and casing, which may run counter to your team’s naming conventions. With Huagati DBML/EDMX Tools, you’re just a couple of clicks away from renaming all of the classes and members in the object model. You can add or remove prefixes or suffixes, force proper casing, remove underscores and more.
One shortcoming of the Visual Studio LINQ to SQL designer is that there is no mechanism to update the object model to reflect the latest database changes. For example, when you first drag a database table onto the designer, an object is created with properties that correspond to the table’s columns. If three new columns are added to the same table a few weeks later, you must return to the LINQ to SQL designer and either remove and then re-add the table onto the designer, or manually add three new properties to the corresponding class. This shortcoming is a non-issue with the Huagati DBML/EDMX Tools, which can report the differences between the relational model and the object model and automatically sync the object model with the relational model. The Huagati DBML/EDMX Tools can also generate the SQL statements needed to modify the relational model so that it mirrors the object model.
Another handy feature is the documentation tool, which retrieves the table and column descriptions from the database and includes them in the XML documentation of the object model.
The Huagati DBML/EDMX Tools includes a free 45-day trial version, along with Standard and Professional versions. The Standard version costs $50 per user license and supports up to 80 tables per model. The Professional version costs $120 per user license and imposes no limits.
Price: $50 to $120 per user license
huagati.com/dbmltools
Blogs of Note
One of my favorite .NET bloggers is Oren Eini, a prolific blogger and respected software developer who posts under the pseudonym Ayende Rahien. Eini is perhaps best known for his contributions to .NET open source projects. He’s a contributor to NHibernate, a popular OR/M framework reviewed in the October 2006 issue of MSDN Magazine (msdn.microsoft.com/magazine/cc163540), and is the creator of Rhino Mocks, a .NET mocking framework covered in the January 2008 issue (msdn.microsoft.com/magazine/cc135981).
.jpg)
Oren Eini’s blog
Eini’s blog posts explore a spectrum of topics. Some entries examine a feature or pitfall of a particular framework, such as NHibernate or the Microsoft Entity Framework. Most are more general, imparting advice from the trenches regarding database design, OR/M usage, testing and software architecture and design. Posts often include screenshots, diagrams and code snippets.
You’ll find, for example, a series of posts on OR/M implementation challenges such as mapping between the relational and object models, hydrating entities and so on. And be sure to read Solving the Select N+1 Problem, which explains how iterating over parent-child data can unwittingly lead to serious performance issues.
Eini’s blog also contains types of posts you don’t typically find in developer-focused blogs. For instance, there are a number of entries that are only a few sentences long, yet still manage to convey an important idea and get the reader thinking. Other posts contain a lengthy code snippet with a single statement like, “Find the bug.” With these types of posts, along with his impressive output, it’s not uncommon for there to be three or more posts per day. Oren’s blog is a must-read for .NET developers and architects.
Run Simultaneous Queries Against Multiple Databases
Over the course of my career, I’ve helped build a number of multi-tenant applications—applications that have a single instance running on a hosted web server but are used by multiple organizations. A multi-tenant application must ensure that a user can view and manage only the data that belongs to her organization. To this end, multi-tenant applications that work with sensitive information often store each organization’s data in a separate database to fully isolate the data (among other reasons).
Viewing data aggregated across multiple organizations can be a real challenge when each organization’s data is stored in a separate database. Imagine that a developer finds a bug that has corrupted data for a particular organization. To determine if there’s similar corruption for the other customers, the developer must run a query on every single database, emitting a separate resultset for each. As you can imagine, that gets tedious. Ideally, the results from each database would be aggregated into a single, unified output, which could then be sorted, filtered and so forth.
Over the years, I’ve queried multiple databases using a variety of techniques, including the undocumented sp_MsForEachDb stored procedure, writing batch files and building custom tools. Recently, a colleague introduced me to Red Gate Software’s SQL Multi Script (version 1.1), and I haven’t looked back. SQL Multi Script works much like you’d expect—enter the SQL statements to execute and select the databases to query against. SQL Multi Script then fires off the SQL statements to the specified databases and aggregates and displays the results.
.jpg)
SQL Multi Script
By default, SQL Multi Script sends the SQL statements to the databases in parallel. This can greatly reduce the time it takes to get back the results, especially when the databases reside on different servers. Alternatively, you can instruct SQL Multi Script to issue the statements serially, which is useful if you want to stop executing the script in the face of an error.
If you do get an error, SQL Multi Script offers four error handling options: continue executing the script (the default behavior); stop executing the current statement on the database, but continue with the other SQL statements; stop executing all statements on this database and move on to the next database in the list; or stop executing all statements on all databases.
SQL Multi Script’s Results pane provides an aggregated view of the messages and data returned by the databases, along with a history of the scripts executed against the databases during the current session. The Results pane also includes a checkbox list of the databases that were queried; check or uncheck a database to show or remove its results from the aggregate. You can also click the Save button to save the aggregated results to a .CSV or .TXT file.
Price: $195
red-gate.com
Improve the Visual Studio Tabs
When working on a large project, it is not uncommon to have dozens of files open within Visual Studio. Unfortunately, the Visual Studio user interface leaves a bit to be desired when there are many open documents. By default, Visual Studio uses a Single Document Interface (SDI) with a series of tabs that show which documents are open. However, these tabs are laid out horizontally, which limits how many can be displayed on the screen. The Visual Studio Multi Document Interface (MDI) does not show tabs, but instead requires the user to go to the Window menu to view and switch among the open documents.
Tabs Studio (version 1.6), by Sergey Vlasov, is an add-in that replaces the built-in Visual Studio tabs with an improved and customizable set of tabs. Unlike Visual Studio, Tabs Studio displays the tab for every open document in both the SDI and MDI configurations. If there is not enough horizontal space to display every tab, Tabs Studio stacks them vertically.
.jpg)
Tabs Studio
Many types of components created in Visual Studio are implemented using multiple files. For example, creating an ASP.NET page named Default.aspx actually creates two files, Default.aspx and Default.aspx.cs (or Default.aspx.vb). Tabs Studio adds features that make it easier to work with such files.
Say you’re working with Default.aspx and need to open Default.aspx.cs. Right-click on the Default.aspx tab and the context menu includes an Open Default.aspx.cs option. What’s more, Tabs Studio groups related documents into a single tab. When both Default.aspx and Default.aspx.cs are opened, Tabs Studio will display a single tab that lists the file name without the extension (Default) along with the two extensions (.aspx and .aspx.cs). Click the .aspx extension in the tab to bring up Default.aspx, or click .aspx.cs to display Default.aspx.cs.
Tabs Studio also has a variety of configuration options. For instance, Tabs Studio allows the tabs’ styles to be customized via XAML. You can configure the tabs’ fonts, colors, shapes and more. And because the settings are defined using XAML, you can include conditional statements and other programmatic logic, making it possible to do things like specify the styling for the currently selected tab, or make the previously selected tab a different color from the other non-selected tabs. Tabs Studio can be further customized using add-ins. You can write your own or download any of the free add-ins available from the Tabs Studio Web site.
Tabs Studio is available for the non-Express Editions of Visual Studio 2005, 2008 and 2010.
Price: $34
tabsstudio.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. Mitchell is an independent consultant, trainer and writer. Reach him at Mitchell@4guysfromrolla.com or via his blog at ScottOnWriting.NET.
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.
Send your questions and comments for Scott to toolsmm@microsoft.com.