Figure 11.5: The user can add and modify specialties. You never know when explosives and doilies will be needed on a mission.
Creating a Simple Database
It pays to start small. You’ll begin with a more modest version of the spy database. Although you can write programs to generate a database, it’s often easier to use a database management system to create the original database. Microsoft includes a very powerful subset of the SQL Server database program as part of the visual studio editor. If you have any experience with Microsoft access (or pretty much any other relational database package) you’ll find the tools for data design to be pretty standard.
Hint Not all installations of Visual Studio.NET include the SQL Server extensions. If you do not have this functionality, you can follow the same steps outlined in this chapter in Microsoft Access or nearly any other modern database program, and then you can use the steps outlined in the “Working with Other Databases” section later in this chapter to connect to the database. Nearly every modern data package will have some variation of the tools I describe in this section, although the exact techniques for accessing these tools varies.
Accessing the Data Server
The toolbar tab on the left side of the IDE has been hiding some important secrets from you. The Server Explorer tab at the bottom−left of the toolbar brings to focus an entirely new set of tools you might never have seen before. Being the international spymaster you are, you can master these new tools quickly. The server explorer enables you to examine many kinds of tools available on the server or servers your computer is currently connected to. The term server is used to describe a
321
computer that provides information, and also to the software on the computer that distributes information. You may be familiar with network servers, which provide a foundation for a local area network, or Web servers, which is where Web pages are stored and accessed. There are many other types of servers available as well, including data servers, which are used to manage databases. When you installed Visual Studio, a simplified version of SQL Server, Microsoft’s powerful data server, was also installed on your machine. You can use this relational database package within the Visual Studio IDE to develop your own databases, and integrate them into your programs.
Creating a New Database
If you look at the servers tag, you’ll see a list of servers you are connected to. As a default, only your own machine name is listed.
Hint When I installed Windows 2000 on my work machine, I went with the default machine name that Microsoft suggested. That was a really bad idea, because I can hardly even type that monster, let alone remember it. In the following code and screen shots, replace andy−mpecr6vc86 with your own machine’s name. If you haven’t yet installed Windows 2000 or XP, make sure you choose a machine name that’s easier to remember than mine.
My server is listed, so I clicked on the corresponding plus sign to see the various parts of the server that are available. SQL servers are near the bottom of the list. SQL (Structured Query Language) is the name of a powerful database manipulation language. You get a brief introduction to it in this chapter. If you expand the SQL Servers item, you see your machine name indicated again. Finally, you see a list of the actual databases registered to your machine. Even if you haven’t attached any databases yet, there will still be a few placed there as prototypes. Right−click on your machine name under SQL Servers and you are given an opportunity to create a new database. Do so, and name it “SimpleSpy.” When you are finished, the IDE adds the SimpleSpy database and several related elements to your server list. It should look like Figure 11.6.
322
Figure 11.6: The hierarchy tree on the left−hand side shows my machine with my machine listed as an SQL Server. The SimpleSpy database is listed as an element of my SQL Server.
Adding a Table
Databases use a construct called a table as the core entity for storing data. A table holds information about a specific element. Each table consists of a number of fields, which each have a value and a type. To make a new table for the simple spy database, right−click on Tables under simpleSpy in the server explorer tab, and choose Add New Table. The main panel of your editor changes so it looks like Figure 11.7.
Figure 11.7: A table editor reminiscent of Microsoft Access pops up.
If you have any experience with Microsoft Access (Microsoft’s very popular entry−level database) the table design screen probably looks very familiar to you. The table designer prompts you for field names and types. The Agents table is designed to describe a list of secret agents. Each agent (at least in this current simple form) has three characteristics. Figure 11.8 illustrates the first stage of table design for the Agents table.
323
Figure 11.8: Each agent has a code name, an assignment, and a specialty field.
For each field, you must determine a field type and length. The field types are much like the variable types you are already used to, but there are a few differences. Fortunately, all the legal field types are available in the drop−down list. Note that text values are listed as char rather than string. This is because data definition has a different tradition and nomenclature than programming. If a field is defined as a text field, you are required to list the length of the field in characters. The software determines where one record ends and another begins by counting the number of bytes in each record. Every record in a table must be the same length. If you define a field to be 10 characters long, any values longer than ten characters will be truncated, and trailing spaces will be added to any values shorter than ten characters. Field names should be single words, like variable names. (Many database packages allow multiple word field names, but they usually make working with the data much more complex.)
Trap The Simple Spy database has some serious problems in its design, as you’ll see. I’ll show you how to improve the database throughout the chapter, but first I want to explore attaching this simplistic database to a form and displaying it in a C# program.
Creating a Primary Key
Notice that the first field in the Agents table is called SpyID. This field is meant to provide an index for the Agents table. It is much easier to work with large and complicated data sets if each table has a special field identified as its primary key. You have already encountered primary keys many times. Every time you make a call to your insurance company, the bank, your credit card company, or any other bureaucracy, somebody asks you for an account number. They are rarely concerned about your name as long as they have that account number. An account number is usually a primary key to the data about you in their database. The primary key is used to find all the other information about you in the database. It would theoretically be possible to use your name as a primary key, but this doesn’t work very well, because of spelling and capitalization problems. There are also people with duplicate names. Account numbers—and primary keys—are designed to avoid these problems. A primary key should be unique—the key should be different for each record in the database. Primary keys should also be universal—each record must have one. Finally, primary keys should be
324
non−null. (This seems obvious, but in many databases, you have to specify that a field is non−null explicitly, so it’s worth mentioning). The best keys are integers, because you don’t have to worry about spelling or capitalization.
In most of my tables, I create a primary key as the first field in the table, and I usually end the field name with ID. To specify in Visual Studio that a field should be used as a primary key, select the entire field by clicking on the triangle to the left of the field, and click the key icon of the database toolbar at the top of the screen. A small key icon appears beside the field in the definition table, as shown in Figure 11.9. Notice that when you set a field as the primary key, the allow nulls check box is automatically deselected.
Figure 11.9: The AgentID field is now the primary key of the Agents table.
Hint It’s very smart to add a primary key to every table, even though you might not feel it’s necessary. When you start to build relational databases later in this chapter, you’ll see that primary keys are critical to building well−formed data structures. If in doubt, add an integer field, call it an ID field, and designate it as a primary key. It never hurts to have one.
To finalize your design of the table, close the window containing the table definition. A dialog pops up asking you the name of the table. Call the new table Agents.
Adding Agents to the Table
After you have created the Agents table, look again at the server explorer. You see that the Agents table is registered to your server, as illustrated in Figure 11.10.
325