1. Technology

Use a database instead of a sequential file

Creating a SQL Server Compact 3.5 Database


By this time, you should have installed Microsoft's free SQL Server Express. Use Microsoft's search box at Microsoft.com with the key "Download Express" to find all of the free 'Express Editions' of tools.

The first thing to understand is that SQL Server Express is a Windows service running in the background rather than an application. This means that it's there, waiting for work to do. There's no IDE (Integrated Development Environment) or GUI (Graphical User Interface) like VB.NET. That's why it can be called up from a menu selection as though it was part of VB.NET. You can also install multiple instances of SQL Server Express.

Click Here to display the illustration
Click the Back button on your browser to return

When you "design a database" it means that you decide what the tables are, what the columns are in those tables, and how they relate to each other. You might also see the word "normalize." That's a process of optomizing that design so the database is as fast and efficient as possible. Database design is a big subject and it's way out of the scope of this tutorial so we won't go into it here.

The database we will create and use will be about as simple as it can get - just one table. To give you a head start in more complex databases, I'll show a simple relationship later.

Since our database is part of a VB.NET application, first start a new Windows Forms Application project. On the Project menu, click Add New Item. In the Add New Item dialog box, click Local Database. Name the database SingleTableDB. When the Data Source Configuration Wizard opens, click Cancel. This wizard is used for configuring databases that already exist and in most 'production' environments, they will. But we're going to create a very simple one from the beginning. But notice that you can now see your new database in Solution Explorer.

Click Here to display the illustration
Click the Back button on your browser to return

Local databases are a feature added with Framework 3.5 and VB.NET Express 2008. This new database format lets you create compact databases that can be deployed on desktop computers, smart devices, and Tablet PCs.

The next step is to add the database table. Nearly all databases consist of a lot of different tables, but we're simulating a sequential file, so we're just going to add one. As it's just before New Years as I write this, I think I'll create a table to keep track of all the fine wines in my wine cellar. (I wish!)

On the View menu, click Database Explorer. In Database Explorer, expand SingleTableDB.sdf by clicking the plus sign and select Tables. Right-click Tables and click Create Table. When the New Table window opens, give it a Name of WineList.

The next task is to create columns in the database. Let's start by creating a column with the name of the wine.

Select the empty field under Column Name and type WineName. Click the nvarchar Data Type field in the drop-down list. You might notice the other things that are available as data types; and also notice that they're not even close to being the same as the data types in VB.NET. One of the unfortunate facts of life is that SQL databases and .NET simply have different selections of data types. But .NET gives you a lot of help here. Set the value in the Length field to 50; the Allow Nulls column will automatically be set to Yes, Unique is No and Primary Key is No. When you tab to the next row, the column is created.

You can now add more columns with different data. I added these:

WineType nvarchar 50 Yes No No
NumBottles int 4 Yes No No

   (Note: You can't change the length because an int always
   has the same length. But you can pick two other types of integer.)
WineGrade nvarchar 50 Yes No No

Click OK to create the table.

Your table will also need one or more primary key columns that will be used to access the data in the database. The primary key also makes sure that all of the rows in the table are unique (because the key itself is unique). You can also select several columns as the key if one column isn't always unique.

In this case, I'm picking both Winename and WineType as primary keys. We could have done this when we defined the table, but this way, we get to see how to update the table too.

Right-click the WineList table, and then click Edit Table Schema. Change Primary Key to Yes for both columns. Note that Allow Nulls is set to No automatically because a column that is a primary key must contain a value. Click OK to save the new settings. Close the Edit Table window.

The final task is to add some data. Usually, your application takes care of this but there are cases, such as testing the database while you're coding, where you will want to do it immediately.

To add some data in design mode, click Show Table Data on the Data menu in Database Explorer or right-click the table name and click Show Table Data. This opens a spreasheet like form that you can use to add data. A row isn't added to the database until you move away from it.

Click Here to display the illustration
Click the Back button on your browser to return

It would be nice if we could open the database as start reading it into program variables, but there's still another step to go: making your database a data source. We see how to do that on the next page.

  1. About.com
  2. Technology
  3. Visual Basic
  4. Using VB.NET
  5. Simple Databases with VB.NET Express & Framework 3.5

©2014 About.com. All rights reserved.