Skip navigation
artist paint and paint brush

Visual Studio 2010 for Database Professionals

Do more with the .NET Framework 4.0

As a developer, you use Visual Studio (VS) to build many flavors of applications for the .NET Framework. Typically, a new release of VS comes with a brand-new version of the .NET Framework, and VS 2010 is no exception—it ships with the .NET Framework 4.0. However, you can use VS 2010 to build applications for any .NET platform, including .NET 3.5 and .NET 2.0.

VS 2010 also includes an improved set of design-time facilities such as IntelliSense, refactoring, code navigation, new designers for workflows, Entity Framework–based applications, and WPF applications. Let’s take a look at how these features help database developers.

Multimonitor Support

VS owes a large share of its popularity to its integrated development environment (IDE), which is made up of language- and feature-specific code editors, visual designers, IntelliSense, auto-completion, snippets, wizards, controls, and more. This IDE is extended in VS 2010 to host the much-requested multimonitor support. Writing code with the .NET Framework requires you to mix designer windows with code windows while keeping an eye on things such as a database profiler, an HTTP watcher, a specification document, or an entity-relationship model. However, doing so requires monitor real estate, and reducing the size of the fonts employed is no longer an option. Having multiple monitors is a viable option because monitors aren’t very expensive and are easy to install. Many IT organizations are using dual monitors as a way to increase productivity and save time and resources.

Microsoft, in fact, has been supporting the use of multiple monitors at the OS level since Windows XP. The real pain was getting VS to detect multiple monitors and allow all of its windows, including code editors, designers, and various dialog boxes, to be dragged around outside the border of the IDE’s parent window. Web Figure 1 shows the Float option, which enables full multimonitor support in VS 2010. Once you select this option, you can move the window around the entire screen, and even move it to another screen.

A New Code-Writing Experience

Honestly, very few editing features in VS ever exceed developers’ expectations. In fact, to get an optimal experience, you probably want to use VS in conjunction with some third-party tools. However, what makes VS really great is the huge number of code-editing features it offers, and this number grows with each new release. VS still leaves room for third party products, which offer a lot of additional features, but VS offers a good-enough coding experience out of the box. And the quality of your out-of-the-box coding experience increases significantly with each new release.

In VS 2010, IntelliSense includes auto-filtering, which gives you the ability to display a context-sensitive list of suggestions. In this version, the list isn’t limited to an alphabetical sequence or to all names starting with the typed sequence. IntelliSense attempts to guess the context in which you’re operating and shows related suggestions, and it even understands abbreviations. For example, if you type WL in an IntelliSense window, it will match member names, such as WriteLine.

Refactoring is an aspect of development that has gained of lot of attention in the past few years. Refactoring is the process of rewriting the source code in a better way (i.e., adding testability, separation of concerns, extensibility) without altering the actual behavior. Originally associated with agile practices, refactoring is now a common, everyday practice for almost every developer. Because refactoring doesn’t add any new behavior to the code, it’s often perceived to be a waste of time and is neglected. In the long run, however, a lack of systematic refactoring leads to low-quality software or even project failures. VS 2010’s refactoring tools are an excellent way to speed up the refactoring process, making it affordable for nearly any development team.

Previous versions of VS offer a Refactor menu, but VS 2010’s refactoring menu is richer than ever. As Figure 1 shows, it comes with more refactoring options than earlier versions, and it offers a more powerful Find tool to help you resolve missing types, namespaces, and assemblies.

When you select an object in the code editor, it will instantly highlight all the references to that particular object, and it can also display the call hierarchy for an entity or a method.

With VS 2010, you can seriously consider not using a third-party tool to help you with your coding chores. However, VS isn’t the only product to improve in the number and quality of editing and refactoring facilities. In fact, it remains inferior to existing versions of commercial products. But if you’re never used a third-party refactoring tool, you’ll feel even less of a need for one in VS 2010. If you’ve accustomed to working with a refactoring tool, dropping it because of the new features in VS 2010 probably isn’t ideal—you might still want to use or upgrade your third-party tool.

Database Development

As Figure 2 shows, VS 2010 comes with several database-specific projects that incorporate some of the features previously available in VS Team System 2008 Database Edition. You can create a database for SQL Server 2008 and SQL Server 2005, as well as .NET assemblies (i.e., CLR database objects) to run inside of SQL Server. You use the familiar VS interface to add tables, foreign keys, and functions to the database.

But is creating a database in VS beneficial? There are various benefits of creating a database project in VS. First, you’ll have all the database objects defined in the same place and alongside the code (e.g., a web or Windows application) that’s using the database. Your schema is captured by a regular project and can be added to the code repository and shared and versioned the same way as any other aspect of the project. Figure 3 shows a database project in action.

In the Properties section, you’ll find settings for SQL command variables and permissions, and you can add references to a VS database project. Finally, in the Scripts section, you’ll find two project folders named Pre-Deployment and Post-Deployment, which contain SQL scripts to run before and after the database deployment.

It’s worth mentioning VS 2010’s data-comparison capabilities. This release lets you compare the data in a source database with the content stored on a target database for a specific pair of tables. Figure 4 shows the New Data Comparison dialog box.

After setting up a data comparison, you can review the data and decide whether you want to synchronize the tables and persist the changes. An alternative to updating the target table is that you can export an UPDATE T-SQL script to a file and run it at a later time.

There are many ways to take advantage of VS 2010’s data-comparison capabilities. The most obvious usage is to update a development server with a copy of the tables you have in your production environment. Another common scenario is to use the tool to copy data across two or more tables in the same or different databases. Finally, you might consider using the Data Comparison wizard to compare data as it appears in a table before and after you run tests as a way to assert the behavior of a piece of code. In fact, it should be noted that both refactoring and testing are project-wide features in VS that aren’t limited to C# or Visual Basic .NET projects. You can have a few refactoring features (i.e., Rename) and testing assertions available for database-related projects, too.

In addition to comparing data in tables, you can also compare the schema of two databases. A schema comparison operation is aimed at finding structural differences between two databases. The detected delta is then summarized to a DDL script that you can run at a later time to make the databases equal.

Finally, note that the database tools aren’t available in just any version of VS 2010. The features I discussed here are included in only the higher-end versions of VS 2010, such as Premium Edition and Ultimate Edition. In particular, no database tools are available in VS 2010 Professional Edition and Express Edition.

LINQ-to-SQL Refined

When LINQ-to-SQL was introduced in VS 2008, it was considered to be a dead end after only a few months on the market. However, LINQ-to-SQL is available in VS 2010, and it includes a long list of bug fixes and some minor adjustments, but you shouldn’t expect smart, creative enhancements. Primarily created for web developers and websites, LINQ-to-SQL is just right for small-to-midsized businesses (SMBs) that don’t always need the abstraction and power of a true object-relational mapping (O/RM) framework, such as the Entity Framework. Many companies prefer to use LINQ-to-SQL because it’s smaller, faster, and simpler than the Entity Framework, but it goes without saying that LINQ-to-SQL isn’t as powerful in terms of entity-relationship design database support, which is limited to SQL Server.

Some changes to LINQ-to-SQL were made in VS 2010. First, when a foreign key undergoes changes in the database schema, simply re-dragging the table-based entity into the designer will refresh the model. Second, the new version of LINQ-to-SQL will produce T-SQL queries that are easier to cache. SQL Server makes extensive use of query plans to optimize the execution of queries. A query execution plan is reused only if the next query exactly matches the previous one that was prepared earlier and cached. Before VS 2010 and the .NET Framework 4.0, the LINQ-to-SQL engine produced queries in which the length of variable type parameters, such as varchar, nvarchar, and text, wasn’t set. Subsequently, the SQL Server client set the length of those fields to the length of the actual content. As a result, very few query plans were actually reused, creating some performance concerns for DBAs. In the LINQ-to-SQL that comes with .NET 4 and VS 2010, text parameters are bound to queries as nvarchar(4000), or nvarchar(MAX) if the actual length is greater than 4000. The following is the resulting query that hits SQL Server when you target .NET 3.5 in VS 2010:

exec sp_executesql 
N'SELECT TOP (1) \\[t0\\].\\[CustomerID\\] 
FROM \\[dbo\\].\\[Customers\\] 
AS \\[t0\\] 
WHERE \\[t0\\].\\[City\\] = @p0',N'@p0 nvarchar(6)',@p0=N'London'

And here’s the resulting query when you target the .NET Framework 4.0 in VS 2010:

exec sp_executesql N'SELECT TOP 
(1) \\[t0\\].\\[CustomerID\\] 
FROM \\[dbo\\].\\[Customers\\] AS \\[t0\\]
WHERE \\[t0\\].\\[City\\] = @p0',N'@p0 nvarchar(4000)',@p0=N'London'

Also, VS 2010’s LINQ-to-SQL supports server-generated columns and multiple active result sets.

LINQ-to-SQL still doesn’t support complex types or any mapping that’s more sophisticated than a property-to-column association. In addition, it doesn’t support automatic updates of the model when the underlying schema changes. And although LINQ-to-SQL works only with SQL Server, it still doesn’t recognize any of the new specific data types introduced in SQL Server 2008, such as spatial data.

The Entity Framework 4.0

LINQ-to-SQL is the low-end framework for data-driven applications and, in a way, it’s the simplest object-oriented replacement for ADO.NET. ADO.NET is still an available framework, but it mostly serves as the underlying API for more abstract and object-oriented APIs such as LINQ-to-SQL and its big brother—the Entity Framework. Positioned as the comprehensive framework to fulfill the expectations of enterprise architects and developers, the Entity Framework enables serious domain model design in the .NET space.

In VS 2010, the Entity Framework offers two distinct programming models—data-first and model-first. With the data-first model, you pick up an existing database connection and ask the designer to build an object model based on that connection. The model-first programming model lets you create an abstract entity-relationship model within the designer and offers to convert the DDL to physical tables.

This version of the Entity Framework includes several external VS 2010 extensions to customize the database generation and update process. In particular, I recommend checking out the Entity Designer Database Generation Power Pack, which lets you customize the workflow for generating the database from the model. The Database Generation Power Pack is a useful tool that meets the needs of developers who are willing to work at a higher level of abstraction with objects and object-oriented tools, and DBAs who are concerned about the stability, structure, and performance of the database. This new tool gives you control over the logic that maps an abstract model to a concrete list of tables, views, and indexes. The default wizard in VS 2010 uses a fixed strategy—one table per entity. You can choose from six predefined workflows or create a custom workflow using either the T4 template-based language of VS or Windows Workflow. Web Figure 2 shows the list of predefined workflows.

The Entity Framework 4.0 introduces a significant improvement in the process that generates C# (or Visual Basic) code for the abstract model. The default generation engine produces a class hierarchy rooted in the Entity Framework library classes. In addition, the Entity Framework includes two more generation engines: the Plain Old CLR Object (POCO) generator and the self-tracking entity generator. The first engine generates classes that are standalone with no dependencies outside the library itself. The self-tracking entity engine generates POCO classes that contain extra code so that each instance of the entity classes can track their own programmatic changes.

When the first version of the Entity Framework came out in the fall of 2008, some prominent members of the industry signed a document to express their lack of confidence with the product. The new version of Entity Framework fixes all the points mentioned in that document, and it includes even more features. If you passed up the first version of the Entity Framework because you didn’t think it was adequate for your needs, you might want to give it a second chance with VS 2010.

Future Enhancements

VS 2010 contains so many features and frameworks that it’s hard to summarize the product in the space of an article. The new version offers new database development testing and editing features, as well as new UI tools around the Entity Framework.

So what you can expect after the release of VS 2010? VS is the central repository of development tools, frameworks, and libraries, and it’s pluggable and extensible. Therefore, you should expect Microsoft to provide new designers and wizards to fix incomplete tools or to add cool new features that just weren’t ready in time for the VS 2010 launch. Developing for .NET is a continuous process in constant evolution.

SIDEBAR: VS 2010 Flavors

Visual Studio 2010 is available in four distinct flavors, each offering a growing number of features. The entry point is represented by the free Visual Studio Express Editions: Web, Visual Basic, C#, and C++. Next is the base product, Visual Studio 2010 Professional Edition, which supplies not only standard development tools but also source control integration, bug tracking, build automation, and more. Visual Studio 2010 Premium Edition offers everything found in the Professional Edition, plus more advanced development tools such as code coverage, code analysis and testing prioritization, richer profiling and debugging tools, advanced database support, and UI testing. Visual Studio 2010 Ultimate Edition is the cream of the crop because it offers everything that the Premium Edition does, plus features such as IntelliTrace and the new architecture tools for Unified Modeling Language (UML)-based modeling. All VS editions come with MSDN support and a certain number of hours per month of Azure cloud computing.

Note that VS 2010 can run side-by-side with VS 2008; however, it can’t directly open an existing VS 2008 solution (.sln) file. If you try to do so, VS 2010 offers to migrate the project to the new format. At that point, you’ll no longer able to reopen the same solution with VS 2008. This could be an issue if your team is working on a current .NET 3.5 project but not all your developers are using VS 2010. In this case, I recommend you make a copy of the VS 2008 solution file before you proceed with the VS 2010 migration, and then keep both solution files in the folder so that the same project can be opened from both versions of VS.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.