Special Edition Using
Microsoft Visual InterDev

Chapter 32

Database Development with Visual InterDev

Now that you have either created a new database or set up an existing one with Visual InterDev, it is time to develop the database. You have weathered the storm, and calmer seas are ahead. Once you are able to and understand how to create a database with Visual InterDev, you've won half the battle. Now that you have successfully completed the large container for your data, the database, it is time to break it into tables and columns. This chapter will discuss on a parallel basis a new database and an existing database. Therefore, all instructions in this chapter will first discuss the actions as if you were creating objects in the database that do not already exist. Then, as there are variations to this, we'll discuss modifying an existing table.

This chapter will mainly discuss databases using Microsoft SQL Server 6.5, which we will refer to as SQL Server. The query section is most applicable if you are accessing another type of database.

Database Interface

We are going to apply the knowledge of tables to Visual InterDev and its own variation on this theme.

NOTE: When you insert a table, you're creating a new table that did not exist before. The table creation occurs as soon as you save the table or diagram. When you add a table, you are using an existing table that currently exists in your database.



Once you initially connect to a database, you will likely be in File View. You can tell what view you are in if you look at the frame to the left, at the very bottom. There you will see three tabs named File View, Data View, and Info View. The tab on the top indicates your current view. It may be more difficult in higher resolutions to know the current view you are in.

In the File View, you will see the standard tree diagram. At the top, you see the name of the database project. We will call it PrjDB. After this tree expansion, you will see the name of the database, DevStudioDB. In parentheses, you will see the name of the server the database is stored. This particular view is best when working with the project as a whole or working with multiple databases.

If you click on the middle tab, you will see Data View. If you fully expand the tree as shown in Figure 32.1, you will see that the first two items are similar to the File View. The difference is trivial; DevStudioDB icon no longer has a cord. Below that, you will see three folders named Tables, Views, and Stored Procedures.

NOTE: For Visual InterDev to work with SQL Server as described in this chapter, you must install Microsoft SQL Server 6.5's Service Pack #1. You can locate this on Microsoft's site at http://www.microsoft.com in their patch downloads area.

If you are using an existing database, the Table folder contains the tables in your database. Each table contains a list of the columns and triggers in that particular table. The View folder contains the views for the database and the columns of that view. The Stored Procedure folder contains the stored procedures for the database with the appropriate return information.

Figure 32.1
Initial view of a connected SQL Server database.

Working with Tables

When you right-click the Table folder, you see the Tables Folder Shortcut Menu. If you right-click an existing table, you get the Table Shortcut Menu. Both menus allow you to create a new table. In addition, you can choose from the menu, under Insert, choose New Database Item to add a new table. Figure 32.2 shows how the new table screen appears in the Database Diagram.

Figure 32.2
Creating a New Table in the Database Diagram.

Each row in the Database Diagram represents a column in the table. Every column in the Database Diagram window represents a property of the new table's column. The following table discusses each of the column properties.
Column Properties
Column Description
Column Name Specifies the name of the column. It is recommended that you be descriptive of what data and data type this column will hold.
Data Type Specifies the type of data permitted in this column. Chapter 30 discusses the appropriate value to enter.
Length Specifies how long the values of this column will be. In some cases, you will not be able to change this value. For char and varchar data types it is recommended that you keep these as small as possible.
Precision Specifies how precise the column's values will be held. This only applies to numeric values. In short, any numeric value longer than specified in Precision will be only mostly accurate. Unless you deal with numbers with many decimal places, this property will not be very important.
Scale Specifies how many digits to the right of the decimal point are stored in the database.
Null Specifies whether or not this column will allow null values. Null values are values that have not been assigned. For example, if you don't know a customer's drivers license number and you have Null set to no, you would have to enter a value such as Unknown.
Default Value Specifies the value this column will have if no other value is used. This works in conjunction with the aforementioned Null property. For example, if you do not want null values in a column, you could set the default value to Unknown, None, etc.
Identity Specifies whether this column's value is generated by the system. For example, you could create unique product or employee identifiers. There are certain requirements for this property. These requirements are explained in the InfoView by looking under Visual DataTools, Database Designer, Working with Database Objects and Column Properties.
Identity Seed Specifies what the next record's (or first record's) identity will be based on. There is more detail on this in the next item.
Identity Increment Specifies the increment value the identities will have. For example, if you have a Product ID and the Identity Seed is set at 00001 and the increment at 2, then the next Product ID will be 00003.


After creating all the columns you would like to create, it is very important that you set a primary key or primary keys. To do this, simply click the row selector to the left of the column name and click the key in the table toolbar.

TIP: If you would like to create a composite key, you are not allowed to perform a conventional multiple item select. Instead, hold down the Control key and click the multiple items using the arrows and spacebar keys.



Now it is time to edit the table properties. To edit the table properties, right-click the table and select Properties. Take the time right now to change the name of the table. You can then select from the File menu and select Save.

CAUTION: It is very important that you modify the table's name during table design. Failure to do so will result in a table called Table1. There is no easy way to rename a table.



You may make further modifications to the table in this screen and save the changes to modify the existing table. You can also right-click another existing table and add, edit, or delete columns and properties.

Another useful feature is the ability to save the table modification script. This script can be used in stored procedures to make the development of stored procedures faster and easier. To save the modifications as a script, click the icon with the scroll and disk on the table toolbar. Afterwards, you will be prompted whether you would like to save the script. Unfortunately, you are not allowed to choose the name of the script. You are also not allowed to copy the text to the clipboard. It also takes some effort to find the script once it is saved.

Once you have created a table, you can right-click it and open the table. In actuality, you are querying the table for all the records. Once you have opened the table, you can view, edit, or delete any existing data if you have permission to do so. In addition, you öþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþ

Working with Referential Integrity

Visual InterDev's interface for working with keys and referential integrity is called the Database Diagram. Referential integrity and keys are fully discussed in Chapter 30. To create a Database Diagram, you can right-click the Database Diagram folder and choose New Database Diagram, or from the Insert menu, you can choose "New Database Object". Once you have a Database Diagram on the right side of your screen you can drag and drop single or multiple tables inside it.

To create a relationship between tables, drag a column from one table to a column in another table. This action brings up the Create Relationships window shown in Figure 32.3. The Create Relationships window provides three powerful options. The first option, Check Existing Data on Creation, determines whether the data already in the foreign-keyed table must comply with the new relationship. The second option, Enable Relationship for Insert and Update, allows you to choose if the relationship is active during inserts or updates of data. The last option, Enable Relationship for Replication, allows you to select whether the relationship is implemented during replication. You can also change the name of the relationship to one that is more meaningful to you. If you have an existing database and existing relationships, these relationships are automatically displayed on the Database Diagram. You can right-click these relationships, select Properties to view, and modify the relationship.

TIP: You can maneuver the lines around tables. If you are not careful, the lines start looking like a spider web. To make the diagram easier to read, arrange the tables and lines to minimize criss-crossing.

Figure 32.3
Creating a relationship.

Visual InterDev updates the database after you save the database diagram. If there are changes to the database itself, a screen like the one shown in Figure 32.4 asks you to confirm whether you would like those database items changed.

Figure 32.4
List of database items changed.

Working with Constraints

While in the Database Diagram, if you right-click a table and select Properties, you see the properties for that table. By clicköþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþöþowing:

((Author like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'))

The Transact-SQL Reference manual included with SQL Server has a reference section on correct constraint statements. You can also base a constraint on the value of another column. For example, to prevent StopDate from being before StartDate, you would enter a statement like the following:

StartDate <= StopDate

Any previously existing data constraints will be visible in this screen. The drop-down box allows you to select a constraint to view or modify.

Working with Triggers

Triggers are generated when data is inserted, updated, or deleted from a table. There are two ways insert a trigger into a database. From the menu, you can select from the Insert menu, "New Database Object" and choose Trigger. Alternatively, you can right-click a table that you would like the trigger to affect, and choose New Trigger. When you create a new trigger the screen looks like Figure 32.6. The code is color-coded according to the Format Options. The default is the standard programming colors: Keywords--Blue, Comments--Green, Other--Black. You can change the colors so your code can look like a bag of candy if you like. The benefit is that your code is easier to read because keywords are highlighted.

A trigger can be used to update a table dynamically based on a new record. For example, if your manager likes to have constant updates of the sales for a single day, a trigger could be created to update the day's sales figure after every order. Such a trigger would put considerable demands on a system, but it could be useful for some businesses. Triggers are most commonly used to cascade changes made on a primary key to foreign keys.

Figure 32.6
Creating a new trigger.

Any existing triggers do appear in this view. To modify an existing trigger, right-click the trigger and select Open.

Working with Queries

In this section, if you can connect a database to Visual InterDev, you can query the data. No doubt you will find the interfaces with which to design the query quite impressive.

To create a query, from the menu select Insert, New Database Item, select Query and click OK. The right side of the screen is divided into four sections (panes). The panes are named, from the top, Diagram Pane, Grid Pane, SQL Pane, and Results Pane. This is shown in Figure 32.7.

Figure 32.7
The four panes of the Query window.

Diagram Pane

The Diagram Pane shows the source of the data that you want to view. This source could be a table, a view, or another query. The Diagram Pane, as its name describes, is a general, graphical view of the query.

The most prominently displayed item in the Diagram Pane is the data source, shown as a window. To bring a data source into this pane, drag and drop a table, view, or query into this pane. Visual InterDev's Query Designer does not allow you to have data sources from two databases. The Query Designer does allow you to drag multiple data sources from a single database into this pane at one time. To do this, simply select multiple data sources. Then drag and drop them into the Diagram Pane. If the database already has relationships defined, this information is automatically displayed. A legend is provided in the help file to decipher the symbols.

To inform you of the source of the data there is an icon in the upper left side of the data source window. The help file has a legend of the meaning of the different icons. Under the title bar there are two or more items listed. The first item indicates all columns in a table. The remaining item(s) show the name of the column(s) in the table. To the left of each column is a box that indicates the use of a column in a query, and if so, how it is being used.

NOTE: If you do not have sufficient rights to view a column, the column name will not appear in the list of columns.



The box indicates whether the column is returning data, inserting data, changing data, or deleting data.

NOTE: If *(All Columns) is selected, then a mark will not appear in the box to the left of the items in the list.



To the right of an item, several icons may appear, depending on the query you are running. A special icon appears for columns used in sorting, grouping, criteria, and sums.

Grid Pane

If you are familiar with Microsoft Access, you will notice that the Grid Pane is like the bottom half of Access's Query-By-Form, except turned sideways. The concept of the Grid Pane is quite similar. If you are not familiar with Access, the Grid Pane is still easy. The Grid Pane is set up like a spreadsheet, and each row creates a column in the result. The name of the column appearing depends on the type of query.

Column The column shows the name of the data source's column supplying the data. This column appears for all query types. If the result is to be an expression, this column displays the expression. To create an expression based upon a column, simply type the name of the column. No special characters are required to designate it as a column of a table. In addition, depending on the type of database storing the data, you can use the functions of that particular database. For example, you can use Microsoft Access functions to evaluate data from an Access database.

Alias The Alias column allows you to rename the column in the result. This is especially useful for calculated columns. You would not want a column called Quantity * Price. You would want a column called Total. On the other hand, if a developer created this database, you could have column names like txt_EMP_NAM or txtStart_DT. Aliases such as Employee Name and Start Date will make things much easier for the user to understand. This column is also used in select, insert, and update queries.

Table This column displays the name of the data source of the column. If the column is an expression, the table column is blank. This column appears for select, insert, and update queries.

Output This column of check boxes indicates whether a column will appear in the result. For example, if you have a query of all customers in California, you may not need a column for the customers' state. Some data sources may not allow you to uncheck this box. Check the documentation of your product--most indicate indirectly whether they have this feature.

Sort Type This column informs you of the type of sorting, ascending or descending, if any, that is being done of the results. This column is available for select and insert queries.

Sort Order This column permits you to indicate the priority of the sort. For example, you could sort customers first by last name, then by first name, or by state, then city. You can use this column only for select and insert queries.

Group By This column specifies that grouping occurs on the values of this column. This column is displayed only if you have selected a group by function. You can do this by right-clicking the Diagram Pane and selecting Group By in a select or insert query.

Append This column specifies the name of the column of the data source to which you want the data of this item appended. For example, if you wanted to insert the data from an order table into a shipping table, you can specify that the order table's order date be entered into the shipping table's shipping date. To perform this action, set the append column to the shipping table's shipping date. This column appears only for insert queries.

New Value This column is used in update queries to specify the new value of the column.

Criteria and Or These are the most important columns for a query. These columns allow you to specify which records you want your query to return. Using an earlier example, if you wanted all customers in California, in the Criteria column you would enter 'CA' with single quotation marks. By entering expressions into the Criteria column for multiple items, you are indicating that the expressions are linked with the AND operator. For example, Figure 32.8 shows the Grid Pane of customers whose last name is Barr AND who live in California.

Figure 32.8
Grid Pane showing a query for customers whose last name is Barr and who live in California.

Using the Or column is the opposite of putting all criteria in the Criteria column. Figure 32.9 is an example of customers whose last name is Barr AND who live in California, OR whose last name is Barry AND who live in New York.

Figure 32.9
Grid Pane showing customers whose last name is Barr and who live in California or whose last name is Barry, living in New York.

TIP: If you cannot write a description of the data you want to return in one sentence, then you probably need to write two queries.



Working with the Grid Pane Along the top of the columns are the column headings. You can delete all the values of that column by pressing the Delete key. Be forewarned, it deletes ALL values of that column.

Along the left side of the Grid Pane are row selectors. If you click these, you are allowed to insert, delete, or move rows. To insert a row, click the row selector and press the Insert key. To delete a row, click the row selector and press the Delete key. To move a row, click the row selector once. When the pointer returns to its standard arrow, you can move a row between another two rows. The above instructions work if you select one or more adjacent rows.

SQL Pane

The SQL Pane shows your query as an SQL statement. This is a convenient way for people who know SQL to type their query and see it appear on the other panes. The Query Designer allows you either to return to the SQL Pane to correct the error or to revert the SQL statement to the last correct SQL statement.

TIP: When working in the SQL window, after you have an SQL statement that you feel will work, move to another pane then return. In this way, if you later make an error, the SQL window can revert to an earlier statement rather than a blank window.



The SQL Pane can be used for application-specific SQL statements. If you have used multiple database applications, you have likely noticed that a character in one application's SQL statement means something completely different in another application's SQL statement. If the Query Designer does not understand your application's particular implementation of SQL, then Query Designer will treat the SQL statement as an error. If you choose to remain in the SQL Pane, the Query Designer will dim the Diagram and Grid Panes to indicate that these panes are no longer graphical representations of the SQL statement.

TIP: Visual InterDev allows you to check your application-specific SQL statement against the data source. It is located on the Query toolbar as the button that says "SQL" with a check. In the menu, under Query you can choose Verify SQL Statement.



Like the other panes, changes made in the SQL Pane are reflected in the other panes. Any changes made in the other panes are reflected in the SQL Pane.

NOTE: If the SQL statement you enter is acceptable, Query Designer will reformat the statement for easier reading. The SQL statement will return the same results as the unformatted version.

Results Pane

The Results Pane shows the data returned by the most recently run query. This method is a convenient way to test your query and verify that the query returns the values you require. Only after you run the query is the Results Pane updated. If you make changes in one of the other panes, the Results Pane is dimmed. You are allowed, however to continue to view the existing data, but it no longer reflects the query displayed in the other panes.

When data is displayed in the Results Pane grid, there are a few conventions of which to take note. If a grid item has no value, a NULL appears in that cell. If a column being returned is a memo or binary data such as a picture or file attachment, the value displayed is BLOB. Rather than being a large, green, gelatinous mass, BLOB stands for Binary Large Object. If the column is exceptionally wide, only part of the values are displayed. You can adjust the width of the column or click on the large item. The control panel's Regional Settings state the format of the data. This applies only to date/time, numeric, and money values.

Working with Stored Procedures

As mentioned in Chapter 30, stored procedures offer a great way to perform routine tasks. Like other database objects, inserting a stored procedure can be done either from the menu or through the shortcut menu of the Stored Procedures folder. You will notice a striking similarity to the trigger screen. This is so because a trigger is a special type of stored procedure.

Figure 32.10
Stored procedure looking for a parameter.

You can open an existing stored procedure by double-clicking it. You can run stored procedures through the stored procedure's shortcut menu. When you run a stored procedure, depending on the type, you may be asked to enter a value to be passed to the stored procedure as shown in Figure 32.10. After the stored procedure is run, the results are sent to the Output Window under the Results Tag. Figure 32.11 shows what to expect from a stored procedure. To clear the Output Window, right-click inside it and select Clear.

Figure 32.11
Result window after running a stored procedure.

TIP: If you will be running many stored procedures, it may help to increase the size of the Result Window. This may seem obvious. Then, if you close that window and later run a stored procedure, the Result Window will restore to the former size you specified.

Debugging Functions within Visual InterDev

Visual InterDev is very helpful in pointing out errors within code. It tells you the line number and the area on that line where it found the error. An unfortunate side to this is that if you have 50 lines of code, you are not going to want to count to 35 to find the error. You can do one of two things. You can search for the word with which Visual InterDev had a problem. Alternatively, you can keep your stored procedures short. Figure 32.12 shows a typical error box. You will notice that it has scroll bars because descriptions can be long enough to need them.

Figure 32.12
Typical error box explaining errors within a stored procedure.

The debugging feature seems to be able to find multiple errors at once, on some occasions. Based on preliminary investigations, it appears that the debugger first looks at SQL statements, then general functions, and finally other returned values. It appears that it can list multiple problems with the other returned values.

Performance Tuning with Indexes

By looking at a table's properties from within the Database Diagram, you will find the Index tab. Here is where you can specify the indexes for a column to improve the speed of data retrieval. Figure 32.13 shows this screen.

Figure 32.13
Creating indexes within the table properties.

To create a new index, click on the New button. You can then name this index to your specifications. Following this, you can select the column or columns to index. You may wish to select one or more of the options displayed.

Unique

Select Unique if you would like each value in this column to be different from all the others. This means if the name Reeves appears once in this column, it cannot appear again in this column. This can be implemented in two ways. Both ways prevent duplicate data from being input for a column in a table. Constraint creates a constraint that will prevent any duplicate data from being input into this column. Index creates an index and prevents duplicate data.

WARNING:
The Ignore Duplicate Key should be used very cautiously. It appears harmless enough--in fact, it looks friendly, but beware, this feature can delete data. Normally, when SQL Server is faced with duplicate data when the data should be unique, an error is generated. This option causes SQL Server not to generate an error and to disallow the data from being placed in the table. In addition, if data being updated causes duplicate values in that column, the original record will be deleted, the new record will not be inserted, and no error will be generated.

Clustering

As discussed in Chapter 30, clustering causes the data in a table to be stored in ascending order. Only one index per table is allowed to be clustered. The sort side has three options, which are discussed in the table below.
Sorting Options
Option Description
Sort Data Choose this option if the column has not been indexed before.
Data Already Sorted Choose this option if you know the data is already sorted by this column. If SQL Sever detects any unsorted data, an error occurs and the index is not created.
Reorganize Sorted Data Choose this option for every other situation. This method is usually faster than choosing the first option.


The Duplicate Handling Options is how SQL Server handles duplicate data. The following table compares these three options.

Duplicate Handling Options
Option Description
Disallow Duplicate Rows Choose this option if the column will not contain duplicate data. This option creates the fastest index, but is also the most restrictive.
Ignore Duplicate Rows WARNING: This option is similar to the aforementioned Ignore Duplicate Key. If there is preexisting duplicate data, the index creation will fail. If duplicate data is inserted later, SQL Server will not allow it. However, this process does generate an error message.
Allow Duplicate Rows Choose this option to allow duplicate data in this column.


With all the problems that can be caused by duplicate data in an index, it becomes clear why primary keys are good sources for indexes.

Fill Factor

We recommend that you simply ignore this value. This feature is for fine-tuning only, and the default value causes acceptable performance. Its application is beyond the scope of this book.

From Here…

Other chapters that follow a similar subject line include the following: