Connecting to Microsoft SQL Server 2008 from Microsoft Visual Studio 2005 and 2008

Jackie Goldstein

March 2008 (Updated October 2008)

Summary

Microsoft SQL Server 2008 offers developers an advanced and powerful database engine and comprehensive programming framework with which to create data centric solutions. Since Visual Studio 2008 and 2005 were released before SQL Server 2008, their respective tools and data designers do not automatically support SQL Server 2008 and its new data types and features. Microsoft is providing SQL Server 2008 support updates for Visual Studio in order to allow the designers to communicate with SQL Server 2008 (CTP5 and later) and take advantage of many of these features. (5 printed pages)

Applies to:

   SQL Server 2008 CTP5 (November 2007) and later

   Visual Studio 2005 and Visual Studio 2008

For the latest information about SQL Server 2008, see https://www.microsoft.com/sql.

For the latest information about Visual Studio, see https://www.microsoft.com/VisualStudio.

Introduction

Connecting Existing Applications to SQL Server 2008

Visual Studio 2005

Visual Studio 2008

Visual Studio Express Editions

Conclusion

Introduction

Microsoft SQL Server 2008 and development technologies from the Microsoft Data Platform provide many benefits to database application developers. Visual Studio 2008 and Visual Studio 2005 provide many tools to allow developers to more easily take advantage of many of these features. However, since these versions of Visual Studio were released before SQL Server 2008, the design-time tools cannot interact with SQL Server 2008 - even though it is possible to programmatically access the data in a SQL Server 2008 database from applications written with Visual Studio 2008 or 2005. Microsoft will be releasing a set of updates for these versions of Visual Studio in order to enable the Visual Studio tools to connect and interact with SQL Server 2008 (CTP5 – November 2007 – and later). This article discusses these updates and describes the different levels of functionality that they provide.

Connecting Existing Applications to SQL Server 2008

Before describing the updates to Visual Studio 2005 and Visual Studio 2008, and the functionality that they enable, it should be emphasized that existing ADO.NET applications that work against SQL Server 2005 can easily connect to SQL Server 2008, without the need for any updates. No changes need to be made to the connection string – all you need to do is to change the server name to the name of your SQL Server 2008 database server.

Visual Studio 2005

Even before applying the SQL Server 2008 support update, applications written with Visual Studio 2005 can programmatically connect to and access a SQL Server 2008 database. However, while this is an important element of a strategy to migrate an application from SQL Server 2005 to SQL Server 2008, such applications cannot take advantage of any of the new SQL Server 2008 data types from within the Visual Studio 2005 design-time tools. Once the SQL Server 2008 support CTP update has been applied to Visual Studio 2005, the Visual Studio Server Explorer, DataSet Designer, and other design-time tools can view and edit SQL Server 2008 database objects. However, the viewing, creation, and/or editing of table schemas will not be supported. It is recommended that developers use SQL Server Management Studio 2008 to create or modify the schema of tables in their SQL Server 2008 database(s).

After the SQL Server 2008 support update has been applied to Visual Studio 2005, the following features are also available for SQL Server 2008 databases:

• Data binding for WinForms and WebForms projects
• Deployment of SQL CLR projects
• T-SQL SQL CLR debugging

The SQL Server 2008 support update CTP for Visual Studio 2005 can be downloaded from:https://www.microsoft.com/downloads/details.aspx?FamilyID=e1109aef-1aa2-408d-aa0f-9df094f993bf.

Visual Studio 2008

Once the Visual Studio 2008 version of the SQL Server 2008 support CTP update has been applied to Visual Studio 2008, it will support the same features as described above for Visual Studio 2005 with its update applied This means that Visual Studio 2008 with the SQL Server 2008 support CTP update applied includes the following features:

• Programmatic consumption of new SQL Server 2008 data types
• Design-Time consumption of new SQL Server 2008 data types
• Server Explorer view & edit SQL Server 2008 database objects
• Server Explorer view SQL Server 2008 table schemas
• Data binding for WinForms and WebForms projects
• Deploy SQL CLR projects that target SQL Server 2008
• T-SQL SQL CLR debugging for SQL Server 2008
• Connect to SQL Server 2008 using LINQ to SQL and the LINQ to SQL Designer. Note you will not be able to consume new SQL Server 2008 Data Types (see below for SP1 information).

If you have also installed the ADO.NET Entity Framework and ADO.NET Entity Designer, you will be able to connect to SQL Server 2008 once the current SQL Server 2008 support CTP update has been applied but you will not be able to consume new SQL Server Data Types using the ADO.NET Entity Framework and the Entity Designer.

The released versions of this update, will be available in the coming months as part of the Visual Studio 2008 Service Pack 1 (SP1) beginning with the Visual Studio 2008 SP1 Beta 1.

With the release and installation of Visual Studio 2008 SP1 Beta 1 you will gain the following support:

• Connect and consume SQL Server 2008 Data Types (Date, Time, DateTime2, DateTimeOffset and Filestream) with LINQ to SQL and the LINQ to SQL Designer
• Connect and consume SQL Server 2008 Data Types (Date, Time, DateTime2, DateTimeOffset and Filestream) with the Entity Framework and the Entity Designer

Visual Studio Express Editions

The express editions of Visual Studio offer developers and hobbyists a working version of Visual Studio, albeit with various limitations as compared to the other versions of Visual Studio. Regarding the ability of the Visual Studio Express Editions to support SQL Server 2008 and its new data types, note the following:

Visual Studio 2005 ExpressEditions – There will not be an SQL Server 2008 support update offered, so the design-time tools of this edition will not be able to support new SQL Server 2008 Data Types.

Visual Studio 2008 Express Editions – There will not be a standalone SQL Server 2008 support update offered; however, when the Service Pack 1 (SP1) version of the Express Editions become available it will include this update and you can then download the new version.

Summary of Updates

The additional functionality offered by these different updates is summarized in the table below.

1. The SQL Server 2008 Management Studio can be used to view, create and edit table schemas.
2. Prior to the release of Visual Studio 2008 SP1 you must install the ADO.NET Entity Framework and ADO.NET Entity Designer separately from https://msdn.microsoft.com/data.
3. SQL Server Data Types supported in LINQ to SQL and the ADO.NET Entity Framework as of Visual Studio 2008 SP1 include Date, Time, DateTime2, DateTimeOffset and Filestream

Conclusion

Even though it is possible to programmatically access the data in a SQL Server 2008 database from applications written with Visual Studio 2008 or 2005, many of the design-time tools cannot connect and access the SQL Server 2008 database "out of the box". To correct this, Microsoft is releasing a set of SQL Server 2008 support updates for Visual Studio 2005 and 2008 (to later be incorporated into VS 2008 SP1) in order to address these limitations. CTP versions for both Visual Studio 2005 and 2008 are currently available. The official released versions of these updates, will be available in the coming months (for Visual Studio 2008, it will be incorporated into SP1 for Visual Studio 2008).