Hands on: Data Basic

Microsoft has come up with a seemingly endless stream of database libraries over the years and, although some are of high quality, particularly the recent work on Linq (Language Integrated Query), they can leave developers bemused over how to write simple database applications, a subject about which I receive frequent queries.

However it’s dressed up, the core of a database application is Crud: Create data, Retrieve data, Update data and Delete data. How do you do Crud in Visual Basic today? Using the example of a catalogue of images, this month’s column shows a couple of approaches, using the free Visual Basic 2008 Express.

Choosing a format
It all starts with a database. Unfortunately, most database engine choices involve some compromise. One of the problems is that Microsoft Access, part of Office, is increasingly out of sync with the rest of the company’s database technology. Access is based on native code and Com, whereas most Microsoft development tools, including the ASP.Net web platform, are based on the .Net Framework.

Access is marooned with its own database engine, called Jet, its own form designer and the old Visual Basic for Applications programming language. The consequence is that if you begin a database application in Access, it’s hard to migrate it anywhere else except by exporting the data and starting again. Although Access has a great user interface for reporting and working interactively with data, it’s best avoided for custom databases.

If you follow Microsoft, there are two database engines worth looking at. The best fit for a single-user, standalone database application is SQL Server Compact Edition (SSCE). The engine is a set of DLLs that runs in the same process as your application, and a database is just a file. Beyond that, there’s the full SQL Server, which is the best supported database engine on Microsoft’s platform and includes single-user desktop applications right up to Enterprise deployments. SQL Server Express is free, as is SSCE.

Unfortunately, both are compromised. SCCE is hard to access from other applications, since there’s no ODBC driver, only OLE DB, and .Net. SQL Server Express is great for features and allows use of Microsoft’s trendy Linq to SQL extensions, but it is more complex to deploy and manage, since it is a server database that happens to run on a desktop. It also consumes more resources than SSCE. The example that follows uses SCCE, which is installed with Visual Basic 2008 Express.

Connecting to data
The application starts with a new Windows Forms project in Visual Basic 2008 Express. Choose Add New Item from the Project menu and select Local Database. I called it pcwdata.sdf. In the next dialogue, choose Your Database Objects and click Finish.

From the View menu, choose Database Explorer. Expand pcwdata.sdf, right-click Tables and choose Create Table. In the New Table dialogue, name the table PCWImages and add:

ID int, Identity, No nulls, Unique, Primary Key
Subject nvarchar, length 100, No nulls
Date datetime, nulls allowed
Notes ntext, no nulls

Null means ‘value unknown’. It is pointless for a Notes field, but could be useful for a date.

Show me the SQL
One way to do Crud is with straight SQL, which has INSERT, SELECT, UPDATE and DELETE statements. This is a code-centric approach. By way of preparation, add the following line to the code behind your form:

Imports System.Data.SqlServerCe

Next, figure out the connection string, which includes the database’s path and filename. You can copy this from the properties of the connection in the Data Explorer, making sure you save the project first. Add the following as the first line in the form class:

Private connectionString = “Data Source=[YOUR PATH]\pcwdata.sdf”

Here is how to insert a new record using SQL. Place a button on the application’s main form, double-click and write the following code for the event handler:

Using conn = New SqlCeConnection(Me. connectionString)
Using cmd = New SqlCeCommand
cmd.Connection = conn
cmd.CommandText = “INSERT INTOPCWImages(Subject, DateTaken,Notes) VALUES (@subject,@DateTaken,@Notes)”
With cmd.Parameters
.AddWithValue(“Subject”, “Surfing at Newquay”)
.AddWithValue(“DateTaken”, New Date(2008, 8, 1))
.AddWithValue(“Notes”, “That’s me”)
End With
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using