| We're ready to explore the Server Explorer a bit deeper, so let's talk about each of the object lists with which you can work: Database Diagrams: This feature (which was going to be dropped from Visual Studio) permits you to view and manage the database tables and relationships. That is, you'll be able to set up as many database diagrams as needed with whatever tables you deem appropriate. The relationships between these tables can be shown as well as edited. Let's walk through the process of creating a new Database DiagramI know you'll find this a useful side trip.
The first time you reference Database Diagrams for a specific database, Visual Studio exposes a dialog complaining that the objects (tables and stored procedures) needed to support diagrams are not present. If you want to support diagrams in your database, you'll need to let SQL Server create these objects. In SQL Server 2005, you won't (necessarily) see these objects, as they are added as system stored procedures and tables.
1. | Right-click on the Database Diagrams icon and choose "Add New Diagram". This opens a "blank slate" diagram pane on which you can add database tables for your diagram (as shown in Figure 4.41). I'm going to select a few related tables for the diagram. Later I'll add a new database table. Note that I switched back to the more familiar "Object" (or Default) view.
Figure 4.41. Choosing "Add New Diagram" opens the Add Table dialog on a new diagram pane. 
| | | 2. | Select as many tables as you want to appear in the diagram from the (multi-select) list and click "Add". When you're done adding tables, click "Close". The selected tables are then drawn on your new diagram along with any existing primary key/foreign key relationships. On closer inspection, you can see the columns designated as primary key (the small key) and the one-to-many relationships between tablesthe "one" side is a key and the "many" side is shown as an infinity ( ) sign. You can zoom in or out on the diagram and align the tables anywhere you choose.
When you create new database objects (tables, stored procedures, views, etc.), the Visual Studio (and SQL Server) tools don't always recognize them without refreshing the list of objects. Right-click the list and choose "Refresh" to get a current list.
| 3. | Each of these tables (and relationships) is editable. If you right-click in the whitespace below the column list, a dialog (shown in Figure 4.43) opens that permits you to edit the data table, set or remove a primary key, add or remove columns, or manage the diagram's relationships.
| | | 4. | You can also use this menu to manage the indexes created for the selected database table or manage the check constraints. If you choose "Indexes/Keys", the dialog shown in Figure 4.44 is exposed. Here, you can see existing indexes and add or delete indexes as needed.
Figure 4.42. Tables added to diagram are shown with existing relationships. 
Figure 4.43. Right-click to edit the data table or relationships. 
Figure 4.44. Clicking on "Indexes/Keys..." opens this dialog. 
| | | 5. | In a similar way, you can edit the relationships established for the database tables shown in the diagram. Right-clicking one of the relationship lines (as shown in 4.43) permits you to delete the relationship or view its properties in the Visual Studio properties dialog.
Note that none of the changes you make to the diagram is persisted to the database until you save the diagram.
| 6. | Let's add a new table to the database (and the diagram) and add a relationship to the existing tables. Right-click on the database diagram (in the whitespace) and choose "New Table..." to create a new table (as shown in Figure 4.45).
Figure 4.45. Create a new table or select from the list of existing tables. 
| 7. | To add this new database table (which is what you're going to want to do when creating a new database), enter a name for the new table in the "Choose Name" dialog. Note that this table is not immediately added to the databaseit won't be until you save the database diagram. I'm naming this table "AuthorDetails". I generally make table names plural, as they are collections of rows of like kind.
IMHO I assume that you have rights on the database to add new tablesyour DBA might not (should not) have granted these rights. | | | 8. | The new database table dialog created when you choose "Add New Table" permits you to fill in the column details for the database table you're creating. The most important properties of the column must be set in the Visual Studio Properties dialogall you get to provide in the new table dialog is the column name, data type, and allow nulls. Without going into a long spiel about suitable data types, just consider that while disk space is much cheaper nowadays, the more you put in memory (the larger the datatype), the fewer the rows you can cache. I also recommend staying away from fixed-types (Char and Nchar) except for cases when the data is always (as in, "always") the same size (as in a 2-byte State code). Be sure to mark fields as permitting NULL if you know there are going to be cases where the data is not yet available (as in DateRetired). I discuss how to build data tables in Chapter 2, "How Does SQL Server Work?".
Take a look at Figure 4.46. Note that I've defined a number of columns and set the datatype and nullability for each. No, I didn't have to remember any datatype namesthese were provided in a drop-down list as I typed. Note that the column names have no spaces. While this is permitted in SQL Server syntax, it's not something you want to dotrust us.
Figure 4.46. Add New Table dialog. 
| | | 9. | We're not done yet defining the database table. We still need to set specific properties on the Au_ID column. In this case, I need to use the Visual Studio Properties dialog (shown in Figure 4.47) once I click on a specific column. Select the Au_ID column and note that the Properties dialog shows the column properties. I've set several properties on this and the other columns in the new database table. These properties include:
- Description: This can be used behind the scenes as a column heading. In any case, I set it to document the purpose of the column.
- Identity Specification: These settings determine that this column is an "Identity" column of some kind. No, one should not set the AuthorDetails..Au_Id column as an Identity because it's the "many" (the Foreign key) side of the relationship. That is, the Au_ID column must be set to an existing (valid) Au_ID value in the Authors table to reference an existing author. This means the AuthorDetails table is a "child" tablethe Authors table is a "Parent".
If you're creating a "Parent" table, it often makes sense to configure the primary key to be auto-populated as an Identity. Once you change "(Is Identity)" to Yes, the Identity Increment and Identify Seed are set to 1. This means that SQL Server writes a guaranteed unique integer to this column when new rows are added to the database. Depending on the size of the table you expect to build, you might consider changing the datatype from int to bigint or smallint. As I discuss in Chapter 12, "Managing Updates", where I talk about concurrency, you can use other methods to uniquely identify each row, but setting up an Identity is easiest. As an alternative, you can to set your column datatype to "Uniqueidentifier" if you wish to generate a GUID to make each row unique, or you can create a custom primary key value that does the job.
- Default Value or Binding: While I don't want to set a default value for the primary key, I would generally set a default value on any columns in the table (except the TimeStamp) that should have a non-null value.
Figure 4.47. The Visual Studio Properties dialog when focused on a specific table column. 
| 10. | The next step is to set a primary key (PK) for the new database table. Remember, the PK is really a requirement for any relational table. While not absolutely necessary, without a PK, ADO.NET won't be able to identify specific rows to updateand neither will you (at least, not easily). A PK can consist of one or several database table columns taken together to return a unique value. For example, you could use a customer's name as a unique identifier if you have only a few people to trackthat is, until you get another customer with the same name. People have tried to use Social Security Account Numbers (SSAN), but these are not uniquethere are too many "duplicates" out there.
Any column(s) you designate as PK must contain a unique (non-NULL) value. This means as you add rows to the table, the new PK value can't exist in the table beforehand.
Once you decide which column(s) constitutes the PK, left-click to select the column and right-click to open the column operations menu, as shown in Figure 4.43. Choose "Set Primary Key" or "Remove Primary Key" to change the PK column.
| | | 11. | To permit SQL Server to easily locate rows in this table and improve query performance, you'll want to create one or more indexes for your table. This is fairly easysimply right-click on the database table and choose "Indexes/Keys". When you designate a PK, Visual Studio automatically generates an index based on the PK columns, as shown in Figure 4.48.
Figure 4.48. Creating an Index for your database table. 
If necessary, click "Add" to create a new indexyou'll need to give it a unique nameor "Delete" to remove an existing index. Sure, you might find it necessary to create several indexes (especially for large tables that are searched in a variety of ways). For example, you might want to regularly locate author details on city or on retirement date. In this case, it might (just might) make sense to add indexes on these columns.
Consider that each index slows down the INSERT and UPDATE operations, as they must be updated along with the data rows. Indexes also take more space in the RAM cache and on disk. Use the tools in SQL Enterprise Studio to analyze and optimize performance against your table and queries once they are populated with data.
| | | 12. | No, we're not quite done. The new AuthorDetails table has not been related to the existing Authors table. This means that if I add new rows to the AuthorDetails table, they might not have valid Au_ID values. Adding relationships is also easy. You have a couple of options here:
- Use the drag-and-drop technique: In this case, simply select the primary key column (Au_ID) in the primary key (parent) table (Authors, in this case), drag the arrow to the foreign key (child) table (AuthorDetails, in this case), and drop on the PK of the child table (AU_Id). This opens the Tables and Columns dialog, as shown in Figure 4.49.
Figure 4.49. Creating a PK/FK relationship. 
When you click OK, the Foreign Key Relationship dialog is exposed (see Figure 4.50). This dialog names the new foreign key and permits you to make whatever configuration settings you feel are necessary. Here you specify:
- How foreign key table rows are handled if rows from the parent are deleted. For example, you can have SQL Server make sure that if a parent row is dropped, all related child rows are dropped as wellthe default is "No action" on both deletes and updates.
- These options also permit SQL Server to ensure that no row is added to this foreign key (child) table if a valid parent row is not already in the database.
Figure 4.50. Setting relationship options in the Foreign Key Relationship dialog. 
| | | 13. | At this point, nothing has been committed to the database, so if you close the Database Diagram tab, you'll be prompted to save changes to the Diagram (which will save the changes to the database). But before you do that, you need to save your work in another way. One of the most powerful features of this tool is the ability to "script" the changes you're about to make to the databaseat least, as far as adding and changing tables, indexes, and relationships are concerned.
Right-click on the new database table we've been working on in the Database Diagram and choose "Generate change script...". This exposes yet another dialog (see Figure 4.51) that shows the T-SQL about to be executed to carry out the change you've requested. You might find it useful to save this to a file and review it with your DBA.
Figure 4.51. The change script generated by Visual Studio. 
| | | 14. | Okay, we're now ready to save (and commit) our Database Diagram. Click File | Save. At this point, you'll be given an opportunity to name your new Database Diagram. Once you provide a name, you're prompted (see Figure 4.52) to confirm that you're ready to accept changes to all affected database tables. Ah, if you click "Save Text File", all you'll get is a file that contains the names of the database tables to be changednot particularly useful.
Figure 4.52. Confirm that you want to change the indicated tables. 
| | | 15. | Once you click "Yes", the change script is executed and the new objects (including the Database Diagram) are stored in the database for everyone to seeat least, everyone with rights to the database. Upon inspection of the Server Explorer, you'll see that our new table "AuthorDetails" is now listed among the other tables (see Figure 4.53). If it's not, you might have to right-click on the Tables icon and choose "Refresh". Note that by clicking the "+", you can drill down to the list of columns. As you select a table or column, the Visual Studio properties dialog is populated with the property settings for the selected item. This is a good way to determine the datatype for a selected column or simply determine the approximate number of rows in the database table.
Figure 4.53. The new AuthorDetails table is now listed under "Tables" in the Server Explorer. 
|
Changing Existing Objects Once your database tables, relationships, and indexes are built, you're going to want to edit these items from time to time. This is not at all difficult, as the Server Explorer is fairly adept at these operations. Managing Database Tables To change an existing database table, right-click on the table and choose "Open Table Definition". This opens a new Visual Studio tab (see Figure 4.54) that includes two dialogssimilar to those used when I created tables with the Database Designer. Figure 4.54. The Open Table Definition dialog. 
Adding or changing data in an existing table is also possible with the Server Explorer. The Visual Studio 2005 implementation is rich with features that should make this easier (and safer) than ever. Let's walk through the process of adding a few rows to the AuthorDetails table. | | 1. | Right-click on the AuthorDetails table in the Server Explorer and choose "Show Table Data". You should see a dialog similar to Figure 4.55.
Figure 4.55. The "Show Table Data" dialog ready to accept new rows. 
Sadly, the UI here in Visual Studio 2005 is different than that used in the "companion" SQL Server Management Studio. The SQL Server tools support all of these same operations, but they use different names for the tasksit's almost like the two groups weren't in the same building working for the same boss.
| | | 2. | At this point, you need to enter data in each of the columns (that require an entry). Remember that the AuthorDetails Au_ID column is a foreign key that's tied to a valid (existing) author, so you'll need to enter an appropriate Au_ID value here. As you enter data, the dialog prompts you to indicate that individual columns are being changed. New popup dialogs (see Figure 4.56) show that the data has changed, that it has not been committed, and the original value (before the change).
Figure 4.56. As data cells are changed, the error provider shows additional information. 
| 3. | When you finish filling in the (required) columns and navigate to another row or click File | Save[10], the row is committed to the databaseas long as the PK/FK and other constraints are not violated.
[10] Or click the "File Save" icon.
|
Unlike Visual Studio 2003, you won't be able to drag Tables, Views, or stored procedures from the Server Explorer IDE to a Windows Form. That functionality has been moved to the new Data Source paradigm, as I discuss in Chapter 7, "The Visual Studio 2005 Data Tools". However, if you're building an ASP.NET page, the Server Explorer is still the only way to drag and drop data elements to the design surface.
|
