This week I’m kicking off a series of posts dedicated to learning programmers, enthusiasts and professionals who want to adventure in the land of local data using Visual Basic and SQL Express.  My goal is to explain new concepts in local data and to pass on tips and tricks along the way.  There are many new questions in this space, so please keep the feedback coming, and be patient for upcoming answers. 

You might be wondering why these adventures start on episode III. To be honest, I’m trying a psychological experiment to see if people searching for a certain hot new movie will instead find my features and begin a lifelong love of programming.  You’ll just have to tell me if that worked or not on you.   


Start By Telling Me the Basics

You are probably familiar with using databases in general.  That is, you have a machine that stores information in a structured way, and this is used by your applications to read and write to that storage.  Oftentimes these databases live on a machine, and your application makes an occasional connection to fetch data, and possible writes back some changes.

Local data refers to the same concept, but in this case it only refers to a database that resides on the same machine as the application.  Local data and local databases are everywhere busily managing your data behind the scenes.  Your contacts and messages are being saved to disk by your e-mail and IM software.  Your favorites are being saved by your Web browser.  Your songs list is saved by Media Player.  And even your phone is storing phone numbers.  You can have a collection of anything: books, movies, recipes, magazines, er, well anything.  More sophisticated applications like a cash register use both local and remote databases; local data is just used to save or “cache” data locally until the data can be safely transmitted to a server. 

      

Figure 1: local data is found in popular smart applications - media collections, media players, and smart phones

The challenge with writing an application that uses local data is that your users have high expectations for it to keep working in many situations!   It’s common nowadays to have an application that stores data, and then to move the application freely to other folders or even other machines.  For example, you want to simply share your collection of movies with your friends, so you toss files in a .zip and share it.  There is also now the ability to do a zero-impact installation of a program using ClickOnce.  In each case, users expect the local data to travel seamlessly with the application.  Here’s where local data sweetens the deal …


Local Databases are Just Files

Yes that’s the secret ingredient.  Your local database is a file at the end of the day, just like your word processing documents and your MP3s (you know, the ones you paid for).  This means you can move and copy databases to your heart’s content. 

The connection string is the glue that sticks application .EXE’s and database files together.  When the application tries to connect to database, it creates a connection using the string.  Here is a simple example. 

Connection string

Data Source=".\SQLExpress";AttachDbFilename="|DataDirectory|\DVDCollectionDatabase.mdf";Integrated Security=True;User Instance=True

File paths on disk

\My Documents\Visual Studio 2005\Projects\
 \MovieCollection1\
  [source code files]
  […]
  \Bin\
   MovieCollection1.exe  <-- application file
   DVDCollectionDatabase.mdf  <-- local database file
   DVDCollectionDatabase.ldf <-- database log file

This is easy to understand because a connection is simply made to a file via a file path.  The file path does look a little funky because it has the |DataDirectory| special sauce mixed in.  Think of |DataDirectory| as a variable that stores the actual value of the path where the database file lives.  In a simple Windows application this folder will be the same folder as the .EXE.  In a ClickOnce application this value will be the Application Data folder under Documents and Settings.  And in a Web application, this is the \Data\ subfolder in your Website.  You can thank this kind little |DataDirectory| variable for allowing your program to continue connecting to your database file dynamically no matter where you copy the folder. 

Black belt note – you can actually connect to any SQL server .MDF file using a connection string like the above, even if you treated the MDF file as a server database before.  Simply copy the file to your data directory, and change the connection string.  Now you’re going local, loco! 


SQL Express Takes it to the Next Level

One last point to make is that an engine is required handle database connections and processing the loading and saving of data (oftentimes called queries).  The best engine for local data in VB 2005 is SQL Server Express ("SSE" for short).  This is an evolved version of MSDE that has the features and benefits of SQL Server, combined with the lightweight features of engines like Access/Jet.  Not to mention, SQL Express has dramatically better setup and tools support than MSDE.  VB 2005 has excellent built-in support for SQL Express and installs it as a part of the product.  SSE is also completely free and can be installed standalone. 


Your Local Data App in 30 Seconds

I’ve been talking a lot, and now it’s time to get coding.  To get running in 30 seconds you will use the Movie Collection Starter Kit.  This is a fully working application for your collection of movies, complete with source code and tutorials.  It is included in every edition of Visual Basic and Visual Studio 2005.  You can download a much cooler Amazon-Enabled Movie Collection Starter Kit from our downloads page on the Visual Basic dev center:  http://msdn.microsoft.com/vbasic/downloads/2005/

To use it the starter kit:
1) Load Visual Basic or Visual Studio 2005
2) Start Page -> Create Project
3) Select Starter Kits in the left-hand-side tree (for Standard+ Editions only)
4) Click on Movie Collection Starter Kit and select OK
- your application source code will be loaded in a new project, and a tutorial will pop up –
5) Press F5 to run the application per the tutorial
6) Press the Add button in the Movie Collection and enter the details of your favorite movie. 
7) Close the application. 
8) File -> SaveAll to save your application to a folder (keep track of where you save it) 

That’s it - you have created your own application that stores your favorite movies. Movies are actually being stored to the DVDCollectionDatabase.mdf file, and can be seen every time you run your application. 

Now you can have some fun exploring how this works.  For example, open up the project folder for your application in Windows explorer.  Copy the application to your desktop.   The application continues to work and load your movies when you copy the application folder (under \Bin\) anywhere else, even to another machine. 


‘Til the Next Episode

We will explore the VB local data features in depth using the Movie Collection Starter Kit as our guide. 

If you want to get a jumpstart on the starter kit, check out the WebCast I did a few weeks ago on the Movie Collection Starter Kit.  All Webcasts are available here:  http://lab.msdn.microsoft.com/express/webcasts/default.aspx 

Until then, enjoy!

Paul