لطفا منتظر باشید ...
Hack 74. Diagram and Modify a Database
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/vdbt7/html/dvconvisualdatabasetoolseditions.asp.
Figure 9-6. Server Explorerdatabase options
9.3.1. Database Diagrams
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.
Figure 9-7. Add Table dialog
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.
Figure 9-8. Database Diagrams interface
Using this interface, you can configure the tables, add relationships
or keys, add or edit columns, and more.
9.3.2. Tables
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.
Figure 9-9. Server Explorer Tables interface
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.
Figure 9-10. Table data view
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.
Figure 9-11. Table design window
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.
9.3.3. Views
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.
Figure 9-12. Design view
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.
9.3.4. Stored Procedures
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.
Figure 9-13. Server ExplorerStored Procedures node
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.
Figure 9-14. Run Stored Procedure dialog
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.
Figure 9-15. Stored procedure output
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.
