Configuring Server (or Database) Explorers

Basically, the Visual Studio Server Explorer acts as a system-management console, permitting you to log on to servers (in the Servers list) and connect to data sources where you can manage databases and their components. In Standard, Professional, or Team editions, the Visual Studio 2005 Server Explorer has evolved quite a bit from the previous versions and is strikingly different than the Server explorer used in Visual Basic 6.0. Ah, before I forget to mention it, you'll have trouble finding the Server Explorer in the Express editions of Visual Studioyou'll find the Database Explorer appears in its place. As you'll also discover, the Database Explorer supports a subset of the Server Explorer features discussed in this chapter, so muddle along the best you can with this free Express versionI'll try to point out the features that are not implemented. However, remember that much of what the Server Explorer can do might still be possible by entering equivalent T-SQL into a query window in Visual Studio or by executing queries with the SQLCMD, SQL Enterprise Manager, or SQL Express Manager applications. A significant difference with the Express edition is the lack of .NET Data Providers. Out of the (free) box, it supports access to only a local instance of SQL Server Express and then only through an attached SQL Server database .MDF filebut the database file is not included; you'll have to create this yourself (I show you how in the next section). There is no facility to point to an external SQL Server instance in the Express edition from Visual Studio, but you can write applications to do so.

The Server Explorer permits you to browse the structure of a selected data source and create new objects on a selected data source. Its features are roughly the same for each backend to which you can connect. I'm going to focus on how it works with SQL Server, but you can still browse tables and create views and other elements against JET/Access, Oracle, DB2, or any other data source.

Creating a New Database with the Server Explorer

I assume that you're going to access one or more databases in the course of creating your applications. These can exist as stand-alone SQL Server database (.MDF) files, installed SQL Server databases on the local system, or on remote SQL Server(s) visible on your LAN or other data sources.

If necessary, the Standard, Pro, and Team versions of Visual Studio 2005 permit you to create new SQL Server (and just SQL Server) databases interactively with a number of techniques. While I prefer to use SQL Server Management Studio to create and administer databases, you can do at least part of the work from Visual Studio 2005. Note that SQL Server Express edition has its own version of the tool: SQL Server Management Studio Express. I discuss how to use both of these tools (at least to some extent) here and in Chapter 7.

The easiest way is to use the Visual Studio Server Explorer^[6]. Open the Server Explorer (View | Server Explorer), right-click on the Data Connections icon, and choose "Create New SQL Server Database". This exposes the dialog shown in Figure 4.27.

^[6] The Express edition assumes you're going to attach a preconfigured SQL Server database file.

Figure 4.27. Creating a new SQL Server database.

In this example, I point to the SQL Server located on the BETAV9 system and the SS2K5 SQL Server instance running on that machine. The new database is to be called "NewDatabase". Once the database is created, you can use the Server Explorer Database Diagram tools to create tables, relationships, and indexes.

Another approach (as suggested by the MSDN documentation) has you create a new "item" in a Visual Studio project. Let's walk through that approach, as it's the only way to create a new database in the Express Edition.

1.	Create a new Visual Studio project (Visual Basic .NET, C#, or Web project). I called the project "CreateNewDatabase".
2.	In the Solution Explorer, right-click the Project "CreateNewDatabase" and choose Add... | New Item and then SQL Database, as shown in Figure 4.28. Figure 4.28. Use Add New Item | SQL Database to open create database dialog.
3.	This dialog creates a new SQL Server .MDF database file and a Server Explorer (or Database Explorer) connection to access it. Note that this file is not attached to a SQL Server instance until it is opened. When the database is opened, it's attached (temporarily and invisibly) to the local SQL Express instance. This type of database is not seen by SQL Server Management Studio or other tools because ADO.NET and SQL Server create a user-unique instance to manage it. If you inspect the properties of this new database connection (see Figure 4.29), you'll see that the ConnectionString uses the "User Instance=True" option. Note that the database file is (currently) located in the application development directory. When deployed, this database file is copied to the user directory on the target system. I discuss this technique of database management in Chapter 9. Figure 4.29. The ConnectionString generated by the Add New Item | SQL Database dialog.
4.	Visual Studio next opens the Data Source configuration wizard that would normally permit you to select the database objects to include in your application dataset. However, since this new database has no user Tables or stored procedures, it does not make sense to continue past this dialog. I discuss the Data Source wizard in detail in Chapter 6. This approach adds the .mdf file to your project.

Creating a New Database in Code

Creating a new database is pretty simple to do in code^[7], so Express Edition users can use this approach to create a new database. For example, if you execute the following T-SQL batch, SQL Server creates a new "mytest" database (see Figure 4.30).

^[7] See SQL Server Books Online or online SQL Server help and search for "CREATE DATABASE".

Figure 4.30. Creating a new "mytest" database.

This T-SQL can also be executed directly in the Standard, Professional, and Team versions by using the Run Selection option of the stored procedure editor. I discuss this technique later in this chapter.