A group blog from members of the VB team
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.
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.
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.
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
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!
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 & """"
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:
· 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
“Books to Buy” & vbCrLf, False)
"------------------" & vbCrLf, True)
unownedBook.Author & ", """ & unownedBook.Title & """" & vbCrLf
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)”
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…
This sounds almost word for word like the sampes in LINQ in Action. Funny even the queries look the same.
Brad beat me to it. The running samples throughout LINQ In Action are all about working with books. We even include ways to interact with the Amazon web services. The book is primarily in C#, but we do include VB translations of all samples, plus coverage in areas where VB is different than C#.
Well now I'm embarassed... I suppose it is an obvious application to make. The problem when when you write the tools is that you don't get motivated to read the books about them... :-)
I'm sure you'd still learn from the book. We cover a lot of ground in it :-)
hi my name is saleem can you answer my question please
i made a simple media player with vb.net 2005
it is working well but i had these problems
1-how can i get all of file names i selected and pressed enter to play them with my application
i tried startupnext instance event and i got all file names but this was a very slow way and doesn't work well when you have alot of files
i mean i have to wait every file name to be sent to an instance of my application
please can you tell me what the way is to do that(
maybe the way that is used in windows media player)
2- how can i next the song by pressing nextmedia button while my application is minimized and i am working on another program like a word ...
3- i can drag and drop files from windows explorer
to my playlist
but i cant drag files from openfiledialoge to my play list
Ok, so once i had the database on the form, no extra code, i ran the program. i got this error message:
SqlCeException was unhandled
The specified password does not match the database password. [
Data Source = C:\Documents and Settings\SWAASDD-HI-0243-3459\My Documents\Contacts
and the line of code that seemed to be incorrect was this:
P.S. If it helps, i called my project Contacts Database, and i put in Contacts instead of Books.
Names don't matter, as you probably guessed, provided that they are consistent. Per the error, this is a credentials problem. My knowledge of databases is weak at best (as I note in the first paragraph), so what I'm going to do is forward this on to a colleague of mine who's more knowledgable in this area.
P.S. I'm going on vacation this evening for a few days, but I'll peek in on Friday when I get back to see what's up.
I talked to Young Joo on the Data team, and here's what he had to say:
"Looks like the customer specified the password when creating a SQL CE database file and the connection string either does not contain the password or mismatched one (or other way around where the DB doesn’t really have a password but the connection string does).
"Assuming that he was able to create a dataset without problem (which implies that the connection string was correct during that time), customer might have changed either the DB setting or connection string. Perhaps, he’s pointing to a wrong DB file?
"Have him try creating the database again, this time leaving the password blank."
Like I say above, I'll check back on Friday to see how you're doing.
Ok, I'll go do that now. And Matt, truly bless your heart mate, you are so helpful!
P.S. Ok, so i recreated the database and everything. I've solved that first problem, but apparently today is not my lucky day :(. Whenever i run my project, it loads normally. i click add, and add a record. once i enter it, then click on another place on the form, the record disappears. Just my luck. :(
Hmm, that's not something I've seen before. I'll take a peek when I get home.
Just a clarification -- are you clicking the "Save Item" button first after entering the record?
don't worry, i firgured it out :) on the two booleans i have, i had AllowDBNull to false, so if i didnt check them both, the record was deleted hehe. but i have a bit of a problem on a different subject here (sorry to go off topic, but you seem to be the best one to go to, seeing as you're so good at VB!) anyway, it has to do with my webbrowser...
I am currently re-constructing my vb2008 webbrowser, and i need unlimited bookmarks. i am using a combobox as the container for the bookmarks. lets say there is an item in there called youtube.com. when you click the down arrow and select it, the webbrowser navigates to it! cool, right? ok, so i tried saving it as a string. Lets say i add a bookmark called youtube.com . when i do that, it also adds it to the string i set up in my.settings, and it saves the settings. in the form that adds the bookmark, you type the website URL into the textbox and click the Ok button. The code for the ok button is this:
My.Settings.BookMarks = My.Settings.BookMarks & vbnewline & textbox1.text
ok, so it saves allright. the vbnewline is to make it a new item. ok, then i add another bookmark called google.com
ok, so i exit the webbrowser. in form1_load, i have this code to reload the bookmarks:
ok, so i reload my webbrowser, and it loads my bookmarks. but when i click the down arrow on the combobox to view my bookmarks, they aren't separate. there is only one single bookmark, called youtube.comgoogle.com . i need to know how to add each one one at a time, or some other solution. any help for me, matt? :)
Yep, I think I can get you going here. The thing is, there's nothing magic about vbnewline (or vbCrLf, or vbCr) -- as far as the combo box is concerned, these are just characters. The combo box won't know (and has no code) to break up a string into smaller strings (and a newline character also has no visual representation). Thus, you need to do this yourself. Here's some code to help you. Note that I'm using a semi-colon as a delimiter instead of vbnewline -- this is better since it's a single character, easy to see if for some reason you need to inspect the settings file, and is also traditionally used as a delimiting character for data):
Dim sArray() As String
sArray = My.Settings.BookMarks.Split(";")
For Each s As String in sArray
This code will take the bookmarks setting, split it into separate strings wherever a semi-colon occurs, and put the results into an array of string. Now, you just iterate over the array and add each value to the combo box. (Don't worry, none of the substrings will have a semicolon leftover.)
Hope this helps!