Creating Your First Database
In this lesson, you will learn how to create a database that you will use in subsequent lessons to create an address-book program.
In the previous lesson, you learned that a database can be used to store and retrieve data for your Visual Basic programs. First, you must have a database to access. Although you could use an existing database, in these lessons, you will learn how to create a new database by using the Visual Database Tools included in Visual Basic.
Prerequisites
To create and access a SQL Server Compact 3.5 database by using Visual Basic Express, you must first install SQL Server Compact 3.5. If you did not install it when you installed Visual Basic Express, you must do so before continuing. For more information, see Using SQL Server Compact 3.5 (Visual Studio).
Try It!
To create a database
On the File menu, click New Project.
In the New Project dialog box, in the Templates pane, click Windows Forms Application.
In the Name box, type FirstDatabase and then click OK.
A new Windows Forms project opens.
On the Project menu, click Add New Item.
In the Add New Item dialog box, click Local Database.
In the Name box, type FirstDatabase and then click Add.
The Data Source Configuration Wizard opens.
In the Data Source Configuration Wizard, click Cancel.
A new database, FirstDatabase.sdf, is added to the project and appears in Solution Explorer.
Adding a Table
As you can see, creating a database is easy. In its current state, the database is not useful because it contains no data. In the next procedure, you will add a table to the database—in this case, a table to store address information.
Try It!
To add a table to the database
On the View menu, click Database Explorer.
In the Database Explorer, expand FirstDatabase.sdf (click the plus sign) and then select Tables.
Right-click Tables and then click Create Table.
A New Table window opens.
In the Name box, type Addresses.
Select the empty field under Column Name and type FirstName.
In the Data Type field, click nvarchar in the drop-down list.
Set the value in the Length field to 50; the Allow Nulls column will automatically be set to Yes.
You have now defined the first column in your new table.
Repeat the previous three steps to add four more columns with the following values:
Column Name: LastName, Data Type: nvarchar, Length: 50
Column Name: StreetAddress, Data Type: nvarchar, Length: 50
Column Name: City, Data Type: nvarchar, Length: 50
Column Name: Phone, Data Type: nvarchar, Length: 50
Click OK to create the table and close the New Table window.
Adding Primary Keys
You now have a table in the database that you can use to store names, addresses, and telephone data for your address book. There is one more step—adding primary keys so that you do not have duplicate records.
A key column, also known as a primary key, designates a column in the table that always contains unique values. In this column, there can be only one row in the table that contains any given value; if you try to enter a second row that contains the same value, you will receive an error.
If you don't have one column that can serve as the primary key, you can use more than one column. In the case of the Addresses table, designate both the FirstName and LastName columns as your primary keys. Although you might know several people with the same first name or last name, it is less likely that you know two people with who share both a first and last name.
Try It!
To add primary keys to the table
In the Database Explorer, expand the Tables node.
Right-click the Addresses table, and then click Edit Table Schema.
The Edit Table widow opens.
In the FirstName row, change Allow Nulls to No and Primary Key to Yes.
In the LastName row, change Allow Nulls to No and Primary Key to Yes.
Note
Allow Nulls is set to No because any field that is designated as the primary key must contain a value.
Click OK to save these settings and close the Edit Table window.
Adding Data
You now have a database that contains a single table, Addresses. Of course, a database is not of much use unless it contains data. In the following procedure, you will add some data to the Addresses table. If you want, you can substitute the names and addresses of people that you know for those given in the example.
Try It!
To add data to the table
In the Database Explorer, expand the Tables node, select the Addresses node, and then, on the Data menu, click Show Table Data.
A data-table window opens.
In the data-table window, in the FirstName field, type Sandeep.
Note
Notice that when you first open the data-table window, the value NULL appears in each field—null is a database term meaning that the field is empty.
In the LastName field, type Kaliyath.
In the StreetAddress field, type 123 45th Ave. E.
In the City field, type Seattle.
In the Phone field, type 2065550100, and then press the TAB key.
You have now defined the first record in the Addresses table.
Repeat the steps 2 through 6 to add two more records that contain the following values:
FirstName: Michael, LastName: Alexander, StreetAddress: 789 W. Capital Way, City: Tacoma, Phone: 2065550101.
FirstName: Andrea, LastName: Dunker, StreetAddress: 234 Moss Bay Blvd, City: Kirkland, Phone: 2065550102.
On the File menu, click Save All to save the project and the database.
While you were entering the data, you may have noticed that small exclamation point icons appeared next to the data. When you used the TAB key to move to the next row, the icons disappeared. This icon means that the data has not been saved to the database. When you move away from the row in which you are entering data, the data for the whole row is saved to the database automatically.
Next Steps
In this lesson, you created a database, added a database table, and then added records to the table in the integrated development environment (IDE). In the next lesson, you will learn how to use a database in your program.
Next Lesson: Getting the Information You Need: Connecting to an Existing Database.