I have a confession to make.  I’m not a database guy.  I’ve done a lot of varied coding over my career, but somehow I’ve never been directly involved in the guts of database coding.  Whenever my code needed some sort of data-caching mechanism, I’ve tended to “roll my own,” as the saying goes.  Therefore, when we first started working on Linq for VS2008, I was quite excited as it was my chance to really learn something about the ins and outs of data programming.  As it happened, within a few weeks of scheduling that, I was promoted from Compiler Lead to Dev Manager, and so while I tried hard to stay on top of the technology, there just wasn’t time for me to really dig into the guts of the system.

Fortunately, SQL Server Compact Edition and Linq have made it easier for me to code a data app, even if I’m not involved with the code itself directly.  I decided to put this to the test this weekend to help me get a handle on my ever-growing book collection.  If I’ve got one bad addiction, it’s got to be the purchasing of way too many books.  From high-brow literature, philosophy, physics texts, and ancient classics all the way to dime-a-dozen science fiction books, I’m just not able to walk out of a bookstore without a full stack of any or all of them.  I’m not even able to avoid the bookstores anymore, thanks to online bookstores being omnipresent, and as a result books are very nearly furniture in my house.  (Sometimes I even manage to find time to read some of them.)

The biggest problem with having a large collection of books is that I’ll sometimes forget which books I already have. Back in the 80’s, I used to keep track of them with a Hypercard stack I’d created for that purpose, but my Macintosh Centris 650 has long since been recycled, and I’d never set anything equivalent up on my successive Windows machines.  So, armed with VS2008, I finally decided to give it a try this weekend.

Creating the Database

I’ve got to say that I really, really like SQL Compact Edition, which is installed by default with VS2008 and which can also be downloaded for free from this site.  It’s perfect for the sort of work that I do, as it stores everything in a file on the local machine and that data is easily portable to other machines.  Setting up a database is also really easy if you don’t have one ready-made, and you can even create it from within Visual Studio:

1.       Launch Visual Studio 2008.

2.       Choose “View” and then “Server Explorer.”

3.       In the resulting tool window, right-click the “Add Connection…” command.

4.       You’ll get a dialog which allows you to choose your data source. 

a.       Choose “SQL Server Compact 3.5 (.NET Framework)” and “My Computer.”

b.      In the “Connection Properties” area, click the “Create…” button to create a new database.

c.       Another dialog will come up, allowing you to name the database file (I named mine “GertzLibrary”), and you can also specify passwords here if you like.  (My database of books not being a private thing, I skipped that step.)  Once you’ve got this all taken care of, press “OK” and your database has been created!

d.      Back on the first dialog, you’ll need to add the password if, if relevant, before pressing “OK.”  Otherwise, just press “OK” and the connection to the database will be created.

5.       Your database will now appear in the Server Explorer tool window.  It’s an empty database, so let’s add a table:

a.       Expand your database and right-click on “Tables,” choosing “Create Table.”

b.      In the resulting dialog, give your table a name (mine is “Books”), and add the column information.  I chose:

                                                               i.       “ISBN” – nvarchar 16, primary key.  (Think of the primary key as the thing which uniquely identifies a record.  ISBN numbers are unique, whereas titles and author names certainly aren’t.)

                                                             ii.      Title – nvarchar 128.

                                                            iii.      Full Title – nvarchar 256.

                                                           iv.      Author – nvarchar 128.

                                                             v.      Publisher – nvarchar 128.

                                                           vi.      Owned – int, no nulls allowed.  This field will tell me whether I own the book or simply want it.  I’ll treat it as a Boolean in my code.

That’s it; the database is complete!  It doesn’t have any records yet, but we’ll get to that later.

Using the Database

The database is now “known” to Visual Studio, but we’re not actually using it yet.  To do that, let’s start by creating a Windows Application – I’ve called mine “VBLibrary.”  You’ll see a tool window near the Solution Explorer called “Data Sources.” (If you can’t find it, you can use the “Data”/”Show Data Sources” command to bring it up.)  In the middle of that tool window, you should see a link called “Add New Data Source” – go ahead and click it. (The command is also available from the “Data” menu.)

You’ll be taken to the “Add Data Source” wizard, which is super easy to use: 

1.       On the first page, make sure that the “Database” object is selected, and click “Next.” 

2.       On the second page, make sure your database is selected in the drop-down and press “Next” again.  You’ll be prompted to add the database file to the project if you like; I selected “No” since I don’t need to distribute my database with my project and prefer to just have one copy on my machine.

3.       Click “Finish” on the final page.

The Data Sources window should now show your table.  To represent the data on the form is really easy – just drag the table to the form!  But before you do that, you’ll want to make a few tweaks.  I prefer standard controls UI instead of using a datagrid (that latter is the default), so let’s select “Books,” click the drop-down that appears next to it, and choose “Details.”  After doing this, you’ll note that the icon has changed for “Books” to something more form-like and less grid-like.  Next, right-click “Books,” and choose “Edit in Data Designer.”  The Data Designer will appear with a representation of your table on it – it allows you to do some customization on how data is actually used in the application. 

1.       Select “Title” in the table, and in the property grid change “NullValue” to “(Empty)” – that is, treat the title as the empty string whenever no data exists for it, instead of throwing an exception.  Repeat this for “Full Title,” “Author,” and “Publisher.” I’m doing this because I don’t necessarily care if they are filled in.

2.       Select “Owned” in the table, and in the property grid make the following changes

a.       Change “DataType” to System.Boolean.  (Once you do this, you’ll see that “Owned” in the Data Sources window has changed to a check-box type.)

b.      Change “DefaultValue” to “True” – most of the entries you’ll make into the database will be books that you own, so that should be made the default.

c.       Change “AllowDBNull” to be “False” – no nulls allowed here, since you either own the book or you don’t.

Now, you can close the Data Designer.  From the Data Sources window, drag the “Books” table to your form, and voila! You’ve got all of your data controls set out for you, with the binding code generated automatically for each.  There’s even a navigation bar automatically generated for you, to allow you to navigate through the records you’ll be creating and accessing.

Cleaning Up the Form

You can drag the controls around and modify them just like any other control.  You can even delete ones you decide aren’t necessary.  For example, I deleted the label “Owned” in front of “Checkbox1” and just changed the text of that checkbox to “Owned” – I personally don’t use labels with checkboxes.

To make data entry easier for the user, you should click each field and, in the property grid, change its MaxLength property to match the length of the data values so that users don’t inadvertently type in more data than the field can hold in the database – our code generator does not do that automatically when creating the controls. 

I also like to keep users from trying to save bogus records.  In this case, a record is bogus if it doesn’t have an ISBN number (since we defined that to be always required when creating the table).  I therefore added the following code to the form (right-click the form, choose “View Code”):

    Private Sub ISBNTextBox_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) _

Handles ISBNTextBox.TextChanged, BindingNavigatorAddNewItem.MouseUp

        Dim enableButtons As Boolean = (ISBNTextBox.TextLength <> 0)

        Me.BindingNavigatorAddNewItem.Enabled = enableButtons

        Me.BindingNavigatorCountItem.Enabled = enableButtons

        Me.BindingNavigatorMoveFirstItem.Enabled = enableButtons

        Me.BindingNavigatorMoveNextItem.Enabled = enableButtons

        Me.BindingNavigatorMovePreviousItem.Enabled = enableButtons

        Me.BindingNavigatorPositionItem.Enabled = enableButtons

        Me.BooksBindingNavigatorSaveItem.Enabled = enableButtons

    End Sub

 

Basically, this just determines whether or not the ISBN value is empty, and disables all of the viewer controls except Delete if so – otherwise, it enables them all.  The Handles clause indicates that I check this whenever a new record (and, by default, blank) record is added or whenever the field changes values.  If I don’t disable the controls, the user might try to save or navigate away from an unfinished record, leading to an exception.  (I could just handle the exception, but I prefer to keep users from getting into trouble in the first place.)

Press “F5.”  Your application launches.  You can now add records (using the button that looks like a “plus” sign), save records, and move through records. Your database is updated when you press “Save” in the toolbar.  Cool stuff!

The Missing Linq

I promised there would be a little Linq code in this application, so I’m going to use Linq to generate a report for me.  The idea would be that I could create a list of books that I’m interested in but don’t own, and save that to a file to either view when I’m looking for books online to print to take to the bookstore with me.

Let’s add a button to the form and call it “Generate Report.”  Double-click the button to take us to the click handler for it.  The first thing we’ll need to do is query the database for the books we’re interested in:

Dim unownedBooks = From book In GertzLibraryDataSet.Books _

Where book.Owned = False _