Navigating the SQL Server Application Development Maze

Selecting the right set of technologies to develop an application is no simple matter. If you’re in the process of planning and developing new SQL Server applications, you need to make several important choices, all of which will have maintenance and support ramifications for years to come. There are multiple choices for the coding language, more choices for the data access layer, and even more choices for the development tools.

Related: Visual Studio Express 2012 Editions

Choosing a Code Language

When it comes to choosing a code language, the primary Microsoft-oriented choices are Visual Basic (VB) and C#. C++ is also still around, and PHP and Java are also considerations for organizations that are using open source and SQL Server. If you’re building business applications, C# or VB are the best choices. The Microsoft .NET Framework and automated garbage collection make them natural choices. Nowadays, C++, with its more difficult coding and memory management requirements, is typically reserved for special-purpose applications. Shops that might lean toward Java and PHP will do so because of a commitment to open source. These organizations will usually choose the open-source MySQL database rather than SQL Server. However, Microsoft provides SQL Server drivers for both languages.

Data Access Technology Options

Choosing a data access technology is more difficult. Standard ADO.NET is certainly one possibility, but modern application development has moved from segregated data access toward object-oriented data access technologies. Microsoft’s first technology in this area was LINQ to SQL. However, that has been superseded by the Entity Framework (EF). In addition, there are several other possibilities, including vendor-specific ORM tools as well as open-source ORM frameworks such as NHibernate. If you’re maintaining applications, you’ll probably be using straight ADO.NET for some time to come. It doesn’t make sense to mix in the new ORM technologies with older applications—you just end up with a hodgepodge that’s difficult to support. For new applications, the best choice would probably be EF. I tend to avoid other ORM technologies unless there’s a specific use case because of the added complexity, development, and support problems that can make the development process more difficult.

Deciding on a Development Tool

Choosing a development tool is no simple matter, either—even among Microsoft’s own tools. Even after you’ve chosen Visual Studio, the decision isn’t simple because there are many editions, including free Visual Studio Express versions. Additionally, you could choose the new Web Matrix tool or you might consider Visual Studio LightSwitch. The choice of tools partially depends on the code language you choose to use. Open-source developers might gravitate toward Eclipse, but the vast majority of developers will opt for one of Microsoft’s development tools.

Let’s take a quick inventory of Microsoft’s development tools. Web Matrix is an anomaly—it’s not really designed to create business applications. It’s aimed at hobbyists who are looking for quick support for open-source web frameworks. However, its reliance on Windows web servers will be a showstopper for that audience. LightSwitch will be very interesting as time goes on. It’s not a tool for end users, as I pointed out in the article “ Visual Studio LightSwitch’s True Target Audience.” However, it could be a great option for developers who are looking for a quick solution to build simple business applications.

The Visual Studio Express line of tools is good for small businesses or for beginning developers, but their limited database and debugging support makes these tools far less useful then the paid versions of Visual Studio. Visual Studio 2010 Professional Edition is the most cost-effective choice for medium and large projects. To get the additional database tools, such as data and schema comparison, you need to use Visual Studio 2010 Premium Edition or higher.

Related: Visual Studio 2010