A group blog from members of the VB team
Creating my first database was a very interesting experience because I really didn't know where to begin. In a past internship I used Visual Studio for some web applications and managing data, but never had to actually create my own database. This summer as an MS Explorer I added an extension to a BlackJack Starter Kit App. My extension utilized a database to store game high scores and that was 'My First Database'. To begin I added a new database to my project, and along with that a dataset was created. After clicking around in the Server Explorer, I was able to Add a new Table that contained the players name, rank and score. From there I couldn't really figure out how to actually get my table to be used inside of my program until I pulled up the Data Souce view. In there I was able to add my new table as the data source for the dataset and a datatable adapter was created. The adapter was my link to the database.
After hooking everything up, it was just a matter of loading and storing data to the database. I already had a listview for displaying highscores, and the code behind the listview would adjust the list when new high scores were made. My plan was to use this list and actually populate it on form load and save it on form close. Populating the list wasn't that bad, I just had to initially set up the database to have a couple rows with some default and on form load each listview item/subitem would be set to the value of the cell in the database. I figured saving wouldn't be any more difficult, but I was wrong. I actually went through and changed each value within the database, but when I did my database update VS would produce an error. At the time I didn't really understand why this code didn't work, and I actually contemplated scrapping the whole database idea all together.
After talking to a couple of people (in particular Steve Lasker), I realized that my problem was that I didn't have a primary key. A primary key is basically a unique identifier for database data. Steve explained to me that my problem was that there was no Update method. You may be wondering, why wasn't the update function generated automatically when the datatable adapter was created. And the answer is, when a primary key isn't assigned, the the datatable adapter doesn't know specifically which row to update and therefore in order to prevent data loss does not generate this function. So in order to get an Update method I had to assign a primary key, delete my table from the dataset and re-drag it into the dataset view in order to have a generated Update method and then my program worked perfectly.
So after creating 'My First Database', I learned the importance of assigning a primary key and I also learned a lot of cool drag and drop features for databases in VS2005.
Primary Key Definition: http://databases.about.com/cs/administration/g/primarykey.htm
SQL Database Glossary: http://www.sqlstrings.com/Database-Glossary.htm