Keeping Track of Books Using VB (Matt Gertz)
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 _
Select book.Author, book.Title
In this line, two interesting things are going on. First, VB will automatically create (on the fly) a type of object (known as an “anonymous type”) which can hold two strings (“Author” and “Title,” with type information identical to that of the corresponding database values). Second, it will create an enumerable set of those using only the records that have “Owned” = “False.”
I can then enumerate over these objects and create something human-readable:
Dim reportResults As New List(Of String)
Dim reportLine As String
For Each unownedBook In unownedBooks
reportLine = unownedBook.Author & ", """ & unownedBook.Title & """"
reportResults.Add(reportLine)
Next
I’ll want to save this report information to a file, and so I’ll need a file dialog to help with that. From the toolbox, drag a “SaveFileDialog” object to the form – it will appear in the tray at the bottom with the other windowless controls already there. Select it and in the property grid make the following changes:
· NameàSaveReportDlg
· DefaultExtàtxt (this will automatically add a “.txt” extension to the filename if necessary)
· FileNameàMyReport (the default filename to create; the user can change this)
· Filterà Text files (*.txt)|*.txt (populates the file type drop-down in the dialog)
· TitleàSave Report (appears in the title bar of the dialog)
Now, back in the button handler I’ll then add the following code:
If Me.SaveReportDlg.ShowDialog = Windows.Forms.DialogResult.OK Then
My.Computer.FileSystem.WriteAllText(Me.SaveReportDlg.FileName, _
“Books to Buy” & vbCrLf, False)
My.Computer.FileSystem.WriteAllText(Me.SaveReportDlg.FileName, _
"------------------" & vbCrLf, True)
For Each unownedBook In unownedBooks
My.Computer.FileSystem.WriteAllText(Me.SaveReportDlg.FileName, _
unownedBook.Author & ", """ & unownedBook.Title & """" & vbCrLf
Next
End If
And I’m done! If I click F5 and press the report button, I’ll get a report saved to disk which looks something like the following (assuming I’ve added such books to the database):
Books to Buy
------------------------------------------
Paul Vick, “The Visual Basic .NET Programming Language”
The Unicode Consortium, “The Unicode Standard, Version 5.0”
Matt Gertz, “The Novel I Will Really, Truly, Honestly Write One of These Days (I Mean It)”
Other Ideas
Since starting this hobby project, I’ve learned that there are web services which can return you all of the book data you need, given an ISBN number. This would make data entry super easy, since you could then just enter the ISBN number from the back of the book and click some button to populate the rest. I’ve going to give that a try next, and if I can find time to get it working, I’ll report back with the details.
‘Til next time…
--Matt--*
