Getting Started: Adding a Database to the Project

When you add a new SQL Database item to your project, Visual Studio asks the local instance of SQL Server Express (which must be present) to create the new .MDF file. It then (for some reason) prompts you for the tables you want to use in your strongly typed Dataset. Ah, what tables? A new database does not (generally) have tables—unless you’ve populated the model database beforehand. Let’s ignore that issue for now.

Of course, the easiest way to create a database is to steal (er, “leverage”) it. That is, take an existing database and extract the schema to a script and run the script against the new database. One can also backup an existing database and restore it to another or use the detach/attach approach. I discuss these (more difficult) techniques in my new book “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”.

It turns out that Visual Studio can make this a lot easier. If you already have a database file (for example .MDF for SQL Server), you can simply add an “existing” item to your project and point to the .MDF file. Again, the Visual Studio IDE prompts you for table(s), views, stored procedures or functions from which to build a DataSet. Now this makes (more) sense.

Populating the Database Schema

We’re ready to take the next step—adding tables and whatever views, stored procedures and data to your new database. In your Visual Studio Database or Server Explorer search for the newly added .MDF Data Connection. The explorer can be used to define the tables and most of the other SQL Server schema you’ll need. I won’t walk you through that as it’s discussed elsewhere (and in the book). For this exercise I added a single table by selecting the Table icon, right-clicking and selecting Add New Table. This opens a (very lightweight) table designer that depends on a companion property page dialog to set additional column attributes. Click the “save” ICON to commit the table to the database. Your database is now set to the minimum size so don’t measure the performance or disk footprint of the deployed application by this value. My book’s example Biblio database is about 500mb which (frankly) is not that big as typical business databases.

Adding Data to the Database

Create a Data Source for the project that addresses the newly created table and drag it to the form. This builds a simple UI that permits you to test the database and add data thereto. Start a debug session (click on the “run” button), enter some data in your new table, click “Save” in the tool strip (the disc icon) and end the debug session. Start another debug session and look for your data—ah, it’s missing. That can be very frustrating if you spent considerable time loading data into your database.

The problem here is that you weren’t working with your project database (the first instance of the .MDF file), but with the copy of the .MDF file that was created when you started the debug session. The 2^nd instance is written to the project\bin\debug directory. If you create a “production” build (and you will eventually), that creates yet another copy of the original (1^st instance) database. These “base” .MDF files are only about 2.2mb in size so this won’t exactly fill up your 80GB hard drive but a “real” database can make each test run take longer as the .MDF is copied to the target directory. But we’re not done yet.

Publish the Database-Laden Project

Right-click on the Visual Studio project and step through the Publish wizard. For this exercise I published the project to a directory on my local hard drive. When the wizard’s done you have (you guessed it) another copy—we’re up to the 3^rd instance of the .MDF file (ExampleProjectDatabase.mdf.deploy).

Setup and Test the Project

Click Setup in the deployed application directory. Since Setup installs (decompresses the setup deployment cabs) and runs the application, two more copies of the database are created in:

C:\Documents and Settings\billva\Local Settings\Apps\2.0\Data\VP34KLZ1.C42\G7V79PAG.JB1\coun..tion_a936baec72ccee19_0001.0000_7fa3d49e679e09d3\Data

And in

C:\Documents and Settings\billva\Local Settings\Apps\2.0\X7KAKD1X.A7H\2CM1EY17.WTM\coun...exe_a936baec72ccee19_0001.0000_none_8ca8a7b93cbb0b16

The approach we’re taking has created the 5^th and 6^th instances. Fortunately, the data saved in the last instance is not overlaid when the application is restarted. That would be very bad. Consider that the default behavior for Visual Studio is to use the new (and unproven) “User Instance” paradigm that makes an additional copy of the database and places it in the user’s disk space. You saw evidence of this above.

A Better Solution

Actually, there are several other approaches that you can take to avoid the proliferation of (useless) copies of the .MDF files on your and your customer’s system. You can see how if the database contained proprietary data it’s important that loose copies not be created at every step in the development, testing, deployment and execution process.

Go back to your original project. Here we can make a simple change (from the default behavior). If you insist on adding a MDF database to your project, you can at least set the Copy to Output Directory property to “Do not copy”. This prevents the database from being overlaid each time you start a debug session. Nope, this won’t work if you don’t manually copy the .MDF file from the project directory to the bin\debug directory. Remember to copy the Debug version of the database back to the project directory before deploying as that’s the database instance that’s used to build the deployment CABs.

This approach helps when you need to pre-populate the database with data before deploying it but it also means you’re still creating additional databases all along the way.

The Best Solution

Frankly, I’m not a fan of imbedding the database file in a project. I think SQL Server databases should be attached to the appropriate instance of SQL Server during the development process, detached to a standalone MDB file, shipped with the application and attached to the local instance of SQL Server at runtime with your own code. Consider that this approach means you have at most two copies of the database file to keep safe.

As I said, this topic is beaten into submission in my new book due on the shelves later this year… Questions? Just ask.