Using the Server Explorer

The Server Explorer is new to VS .NET; it enables you to manage your database servers and connections. You can use the Server Explorer to add a new server or a data connection to your list. The Server Explorer allows you to view, add, edit, and delete data in a tabular grid. The Server Explorer also allows you to create and delete database objects such as tables, stored procedures, views, and triggers.

Note

If you've ever used Open Database Connectivity (ODBC) data source names (DSNs) in your applications, then you might be familiar with the traditional Windows ODBC Administration. With ODBC Administration, you created DSNs using ODBC drivers for a data source and then connected your application using this DSN. Now, you'll hardly use ODBC Administration at all because the Server Explorer provides connection management services.

OK, let's see how to use the Server Explorer. You can open the Server Explorer by clicking the View Server Explorer menu item.

Tip

The use of the Server Explorer isn't limited to SQL Server databases. You can add any type of database, including MySQL and Access.

When you open the Server Explorer, you'll see two root nodes: Data Connections and Servers. Initially, the Data Connections node has no items listed under it, but the Servers node displays all the available servers. By expanding the Servers node, you can see all the servers and services running on your machine. The SQL Servers node of the Server Explorer lists all the available SQL Servers running on that machine.

If you don't have any servers listed in your Servers list, you can right-click the Servers node and select the Add Server menu option. This option allows you to add new servers to the list. You can also unregister a server by right-clicking it and selecting the "Unregister Server" menu option.

You can also manage services running on your server. If you expand the Services node, you'll see a list of services running on your system. By right-clicking a service, you can even start and stop a service using the Server Explorer.

To view and managed database objects such as tables, views, or stored procedures, you can simply expand the database server name listed under the Data Connections node. After that you can expand Tables, Views, and Stored Procedures nodes. To view, delete, and update a database table, you right-click the table and use the available options.

Adding a New Connection

Adding a new connection is the next step after adding a server (if you're using a server) to the Server Explorer. You add a new connection to your list by right-clicking the Data Connections node and choosing the Add Connection option. This brings up a Data Link Properties Wizard. By default, the Connection tab is active, but you may want to click the Provider tab. The first tab of this wizard, Provider, displays all the data source providers installed on your machine; this is where you select your database provider. The list could contain any OLE-DB provider, Jet OLE-DB, or other data driver available on your computer.

The second tab of this wizard, Connection, lets you pick your server and corresponding data source. The drop-down list displays all the available servers. Our server is a SQL Server with the default name localhost. After selecting a server, the Database drop-down list displays all the available databases on the server. Select the Northwind database in this example. You can also pick what security mode you want to use for your connection on this tab. You can use the user ID and password of a SQL Server or use the Windows NT Integrated security option. By clicking the Test Connection button, you can make sure your database connection is working. If you've provided an incorrect user ID or password, the test will throw an error.

The third tab, Advanced, is for setting connection timeout and access permissions. You can give this connection read, write, or other permissions using the Advanced tab. The last tab, All, shows you all properties of the connection in a list, such as server name, connection timeout, data source, initial catalog (database name), security, and other network options. You can also edit this data from this tab by just double-clicking a particular property and editing the value of it.

After adding a data connection, you'll see that the wizard adds a node (Servername.DatabaseName.dbo) to the Data Connections root node. If you expand this node, you can see the database objects such as tables, views, stored procedures, and functions.

Managing and Viewing Data

As mentioned earlier, the Server Explorer not only lets you add server and database connections, but it also lets you manage and view data. You can add, update, and delete data from a database. Of course, you must have read and write permissions on the server to read and write data. The Server Explorer also provides options for creating new databases and objects, including tables, views, stored procedures, and so on. You can do this from both root nodes (Data Connections or Servers).

The Server Explorer shows database objects in a tree structure. Each database is a tree node of the server. As you expand the Northwind database node, for example, you can see its children listed as tables, stored procedures, and views.

If you expand this connection by double-clicking it, you'll notice it shows tables, views, and stored procedures. You can further expand these items to see them in more detail.

Note

We discuss stored procedures and views in more detail in Chapter 11.

Besides showing a list of database objects such as tables, views, stored procedures, and functions, the Server Explorer also lets you retrieve, add, edit, and delete data from a data source. You can always double-click to view the data of a table. Alternatively, right-clicking a database object (such as a table) provides you menu options based on the selection. For example, you can right-click and then select the Retrieve Data from Table menu option to retrieve data from that table.

Now, if you retrieve data from the Employees table by double-clicking or using the Retrieve Data from Table option, data will be displayed in an editable grid. You can edit this data at any time—again, assuming you have the correct permissions. For example, to delete a row or a collection of rows, select the rows and hit Delete or right-click the selected rows and then hit the Delete option. The right-click option of the grid also provides you options to move to the grid's first, next, previous, and last records.