Why leave Visual Studio to administer your database? Use the Server Explorer to create tables, views, stored procedures, and more.
We all love to hate SQL Server Enterprise Manager and its seemingly endless number of modal dialogs and nonsizable forms, but the Server Explorer allows you to work with your database from a development perspective. It doesn't include all the features that Enterprise Manager does, but it provides the basics needed to work with your database. Figure 9-6 shows the basic database interface included with the Server Explorer.
The capabilities of the data tools in Visual Studio vary based on the version of Visual Studio you own. For a complete list of capabilities, please refer to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbt7l/dvconvisualdatabasetoolseditions.asp.
Database Diagrams can be used to design your database and create relationships between your tables. Using the Server Explorer, you can edit and create new database diagrams. To create a new database diagram, you simply need to right-click on the Database Diagrams node and select New Diagram. You will then see the Add Table dialog shown in Figure 9-7.
From this dialog, you can select the tables you want to work with. When you click Add, the table will be added to your diagram. If you add multiple tables, Visual Studio will automatically add primary/foreign key relationships to the diagram. Figure 9-8 shows the Database Diagrams interface.
Using this interface, you can configure the tables, add relationships or keys, add or edit columns, and more.
Using the Server Explorer interface, you can work directly with the tables in your database. By clicking on the Tables node in the Server Explorer, you will see a list of all the tables in your database, as shown in Figure 9-9.
Use this list to perform various table functions. If you right-click on a table and click on Retrieve Data from Table, you will see a portion of the data from your table in an easy-to-view format, as seen in Figure 9-10.
You can also edit a table by right-clicking on it and choosing Design Table from the context menu. You will then see the window shown in Figure 9-11.
From the table design window, you can change and configure any of the table settings that you can change directly through Enterprise Manager. In fact, you have probably noticed that the interface is pretty much identical to the interface in Enterprise Manager. You can also create triggers for your tables by right-clicking on a table and choosing New Trigger.
Using the Server Explorer, you can create and edit views much like working with tables. The Views node shows a list of all of the views in your database. Just as with tables, you can design a view by right-clicking on it and choosing Design View. The design view is shown in Figure 9-12.
Much like the table designer, the view designer is very similar to the one in Enterprise Manager. You can also add triggers to the view by right-clicking on the view and clicking Add Trigger.
Support for stored procedures is one area in which the Server Explorer excels. The Server Explorer displays not only the name of the stored procedure, but also includes a list of the parameters and return columns from the stored procedure. This node is shown in Figure 9-13.
From the Server Explorer, you can create new stored procedures, edit stored procedures, and even run them. By right-clicking on the stored procedure and choosing Run Stored Procedure, you will then see the parameters dialog shown in Figure 9-14.
After specifying all the required parameters, you can click the OK button and you will see the results of the stored procedure shown in the Output window, as shown in Figure 9-15.
You can also step into and debug the stored procedure [Hack #40] .
The Server Explorer lets you create inline, table-valued, and scalar-valued functions. After creating a function, you can then edit, run, or step into the functions in much the same way as stored procedures.
Using the Server Explorer can be a much more pleasant experience than working with the Enterprise Manager or SQL Query Analyzer.