Hands on: Data Basic

Run the code and click the button. Close it, right-click PCWImages in the Database Explorer and choose Show Table Data. All being well, it will have a row of data matching your insert.

In a real application, you would typically take the values for the new record from controls on a form. You’d likely not include this code in the form itself, but in a separate data management class. Note the use of parameters rather than literal values in the SQL: this is a good protection against SQL injection attacks if the code ends up in ASP.Net, as well as being tidier.

How about retrieving data? You can do this with the SELECT command. Add a listbox to the form, add a second button and write this code:

Me.ListBox1.Items.Clear()

Using conn = New SqlCeConnection(Me. ConnectionString)
Using cmd = New SqlCeCommand
cmd.Connection = conn
cmd.CommandText = “SELECT Subject FROM PCWImages ORDER BY Subject”
conn.Open()
Using dr = cmd.ExecuteReader
While dr.Read
ListBox1.Items.Add(dr. GetString(0))
End While
End Using
End Using
End Using

By now, you’ll get the idea. UPDATE is similar to INSERT:

“UPDATE PCWImages SET Subject = @Subject WHERE ID=@ID”

DELETE is the simplest SQL of all:

“DELETE FROM PCWImages WHERE ID=@ID”

The SQL approach has advantages. It offers good performance, with the minimum of clutter between your code and the database, and standard SQL will work with most database engines. Best of all, you can see what instructions are being sent to the database, which assists debugging.

If you need maximum flexibility, and want to work with the data more in code and less through standard visual controls, the SQL approach is great. The downside is that you have to write a lot of code. It doesn’t look too bad in the bare-bones example above, but when there are multiple tables with dozens of fields, it soon gets tedious. The code above also skates over the issue of hanging on to the ID for each row you retrieve. It’s not too difficult, but it is more work.

The alternative is to use the Rad data tools in Visual Basic. Scrub all that SQL or start a new project and try that.

Crud the quick way
What follows presumes you have the same database as above. When it was added to the project, Visual Basic also created a dataset, called by default pcwdataDataSet.xsd. Double-click this in the Solution Explorer to open the DataSet Designer.

This is really an XML schema, but the designer hides it from you. Open the Database Explorer and drag the PCWImages table onto the designer. Save it, then open the form designer and drag the PCWImages table from the Data Sources tab onto the form. VB does some magic, adding a DataGridView and a navigator to the form. Reposition the items, then run the project. This really is an instant database application. You can add rows by clicking in a blank row and typing, amend them by typing in the grid or delete by clicking on ‘X’. Remember that changes you make are not saved unless you click Save before closing.

There is a fair amount of learning to do to get the best from components like the TableAdapterManager. The generated application is a good start, but could be a disaster in some circumstances. Here’s how to improve the application a little. There’s no need to display the ID field, and the notes would look better in a separate control. Right-click the DataGridView, choose Edit Columns and remove ID and Notes. Next, add a TextBox to the form and arrange the components so that it sits to the right of the grid, with a vertical scrollbar.

Currently, the Notes box is not linked to the data. To fix this, edit its properties and expand DataBindings. Select the Text property and bind it to the Notes field in PCWImagesBindingSource. See next month’s issue for more VB database tips.

Resources
Download the code for this sample from www.itwriting.com/pcw
Visual Basic Express is available from www.microsoft.com/express/vb