Data Storage

You can most easily extend your application to handle runtime-created data elements by using the Data Definition Language (DDL) commands of your RDBMS. Before you continue any further, though, you need to be aware of one caveat right away: after you release your application, you can't control the permissions that the application has when it connects to the database—and DDL commands require an administrator privilege. If the application connects to the RDBMS through a user ID that doesn't have administrator privileges, these commands will fail. Because corporate security policies are understandably very rigid, you can't expect to insist that your customers grant administrator rights to the user ID for your application just so it can execute the occasional DDL command.

As I said, the easiest way to store new column and table structures is by creating new columns and tables using DDL. Although this may seem obvious, in the next section you look at an alternative approach. Using the personnel-management system example I've been beating to death all along in the book, if you have a table that stores LastName, FirstName, and DateOfBirth, you can add a new column defined by the user by executing this command:

ALTER TABLE Employees ADD FavoriteBeer varchar(100)

Previous chapters covered how to instantiate the form control objects to edit this new column. By tying this database column to a TextBox control, you can now track the favorite brand of beer for all your employees.

Most likely, though, you want to select such a series of options from a dictionary so users don't need to type in their favorite brand of beer free-form, leaving you to deal with all the various misspellings of Michelob and Budweiser. In this case, you can maintain a dictionary table to standardize the options. When the administrator decides to store this data, they can require that the end users select it from a dictionary, thereby requiring the use of a ComboxBox rather than a TextBox. They need to define Beer Brands as a dictionary type and can store this new type in the DictionaryType table:

DictionaryTypeID int Description varchar(100)

Each type of entry in DictionaryType corresponds to one or more entries in the Dictionary table, which has the following structure:

DictionaryID int Description varchar(100) DictionaryType int

When you define the Favorite Beer entry, you can insert its title into the DictionaryType table. The unique ID—DictionaryTypeID, which in the following example has a value of 3—is then used as the foreign key in the Dictionary table to determine that all entries with this designation belong to the beer list. The table data looks something like Tables 8-1 and 8-2.

Table 8-1. DictionaryType Table

DictionaryTypeID

Description

1

State

2

Department

3

Favorite Beer

Table 8-2. Dictionary Table

DictionaryID Description DictionaryTypeID

Table 8-2. Dictionary Table

1

New Jersey

1

2

California

1

3

Texas

1

4

Finance

2

5

Sales

2

6

IT

2

7

Michelob

3

8

Budweiser

3

Now you can present the user with a combo box that is populated by one routine that pulls its data using the DictionaryTypeID value as a parameter:

SELECT DictionaryID, Description

FROM Dictionary

WHERE DictionaryTypeID = @DictionaryType ORDER BY Description

■ Note Even if you're not building a data-driven application, using such a storage mechanism for simple dictionary entries makes sense. You only need to maintain one table and one set of select, insert, update, and delete stored procedures. I once had to maintain an application that had 76 tables of the same structure as the one you just saw—a full one-fourth of all the tables in the application.

Building a front end to allow a user the ability to create and configure their own data columns is easy. Look at the screen in Figure 8-1.

Figure 8-1. User interface to create custom columns and foreign key relationships

the source and foreign table combo boxes with the following SQL, which extracts the metadata from the INFORMATION_SCHEMA view:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE NAME

After a table is selected, its associated key combo box is populated with this SQL, which uses the table name as a parameter:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = <tablename> AND DATA_TYPE = 'int' ORDER BY ORDINAL_POSITION

The assumption here is that only integer type columns are used in primary and foreign key relationships.

You can determine the available data types in your SQL Server installation by extracting them from the sys.types table as shown here:

SELECT name FROM sys.types ORDER BY name

Each version of the RDBMS has slightly different data types. SQL Server 2008, for example, introduced a date and a time data type. Therefore, a hard-coded list of options won't work well.

Ultimately, the goal is to generate the text of the necessary DDL commands, as shown in Listing 8-1.

Listing 8-1. DDL Commands to Add a Column and Create a Foreign Key Relationship ALTER TABLE Employees ADD BeerID int ALTER TABLE Employees

ADD CONSTRAINT FK_Employees_FavoriteBeerID FOREIGN KEY (FavoriteBeerID) REFERENCES Dictionary (DictionaryID)

The code to generate this DDL is shown in Listing 8-2.

Listing 8-2. Generating DDL Commands string szSQL;

string szSourceTable = cmbSourceTable.Text; string szSourceTableColumn = txtColumnName.Text; string szForeignKeyTable = cmbForeignKeyTable.Text; string szForeignKeyColumn = cmbForeignKey.Text; string szDataType = cmbDataType.Text;

szSQL = "ALTER TABLE " + szSourceTable + " ADD " + szSourceTableColumn + " " + szDataType;

ApplySQL(szSQL);

szSQL = "ALTER TABLE " + szSourceTable +

" ADD CONSTRAINT FK_" + szSourceTable + "_" + szSourceTableColumn + " FOREIGN KEY (" + szSourceTableColumn + ") " +

" REFERENCES " + szForeignKeyTable + " (" + szForeignKeyColumn + ")"; ApplySOL(szSOL);

■ Note You can distribute your applications with a series of additional columns in every table to handle future custom data elements created by users. I've seen applications in which each table has columns named

FieldlVarchar.FieldnVarchar, Fieldllnt...FieldnInt, FieldlDateTime...FieldnDateTime, and so on. The additional step with this technique is the need for a special table to associate a label with each data element defined by the user. Programmatically, this approach is very easy to implement and allows you to keep a normalized structure while avoiding DDL commands. However, it does present a cumbersome database design, and you need to limit the number of additional data elements the users may add.

0 0

Post a comment