One of the nice features of VSTS is data-driven testing. It’s as easy as hooking up a couple of attributes to the test classes, which then allows tests to be run for multiple sets of input data. The problem is when you want to include data with the test project.

The first thing that came to mind was SQLExpress. It’s free and it allows you to use MDF SQL Server files included in the project, which can be dynamically attached as needed. Unfortunately, there are a number of issues with this approach. The primary two are that you can’t reliably use relative paths or path substitutions without major workarounds, and the way SQLExpress manages users in the database file (primarily when you distribute the file/project to multiple machines with different users). Believe me when I say this option is more trouble than it’s worth. So what then? Use Excel or Access files? Excel has some nasty issues including size of data and data formatting problems (actually, the way the Excel OLEDB provider attempts to identify data types on columns). MDB files work, but can be a bit on the heavy side. More importantly, I’d like to stick to SQL Server syntax and features.


The answer came to me as a new product on the desktop – SQLce. SQL Server Compact Edition 2005 was recently released as a small light-weight file-based database based on SQL Server, which is now available on the desktop (not just mobile devices anymore).


Installation


First thing you need to do is download and install SQLce. You’ll need three components – the database runtime files, the software development kit (for the docs primarily), and the Visual Studio tools.


(runtime) http://www.microsoft.com/downloads/details.aspx?FamilyID=85e0c3ce-3fa1-453a-8ce9-af6ca20946c3&DisplayLang=en


(SDK) http://www.microsoft.com/downloads/details.aspx?familyid=E9AA3F8D-363D-49F3-AE89-64E1D149E09B&displaylang=en


(VS tools with SP1) http://www.microsoft.com/downloads/details.aspx?familyid=877C0ADC-0347-4A47-B842-58FB71D159AC&displaylang=en


If I remember correctly, you’ll have to poke around in the SDK directory to find another installer for the documentation.

Database Creation


SQLce databases are completely self-contained in an SDF file. You can use Server Explorer in VS2005 or SQL Management Studio to create the databases. Unfortunately, there’s no "Add SQLce File" in the VS "Add New Item" dialog, though you could add a template script yourself.


If you decide to use VS2005 Server Explorer, open your test project, and then in Server Explorer, click "Connect to Database".


Change the Data Source to "Microsoft SQL Server 2005 Compact Edition".


Click on the "Create..." button and select your project directory (or sub-directory thereof).


If you don’t include security-sensitive data, there’s no need for a password, and your testing teammates will thank you for not including one :-)


Make sure to change the name of the database file, since by default it’ll be something like "MyDatabase#1.sdf".


Right-click on the project in Project Explorer and select "Add->Existing Item". Select the SDF file you just created. This will include the database in your project.


Adding Tables and Data


Go back to server explorer and expand the entry for your SDF file. Right-click on "Tables" and select "Create Table". You’ll see a somewhat familiar dialog for creating tables.


When you’re done creating the necessary table(s), go back to Server Explorer and right-click on the new table entry, then select "Open". You’ll see a datagrid where you can enter and edit row values.


Wiring Up the Test DataSource(s)


For Unit Tests:
Go to the test method containing the TestMethod attribute.


Because of how the test software deploys the individual test runs, you’ll have to deploy the SDF file as well. Add the DeploymentItem attribute to your test method and specify the SDF file. You can also tweak the output directory on the DeploymentItem attribute if you really feel inclined to do so.


Now you need to also add the DataSource attribute to your test method. It should look as follows:


[DataSource("System.Data.OleDb", @"Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=YourDB.sdf;", "YourTestTableName", DataAccessMethod.Sequential)]


Note that the data source is a path to the file itself, but it can be a relative path, unlike SQLExpress MDF attached files.


For Web Tests:
Create a web test as usual and generate the code. Add a DataSource and your attribute should look like:


[DataSource("YourDataSourceName", "Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=YourDB.sdf;", DataBindingAccessMethod.Sequential, "YourTestTableName"]


Now, there are a few caveats with SQLce. First, your data file is limited to 4GB. That shouldn’t be a problem for most unit tests (one would hope!). Also, the SQLce doesn’t support stored procs (yet). This is the first desktop version but this limitation will be addressed soon from what I hear. You can use pretty standard dynamic T-SQL though. The testing framework simply selects the data from the specified table, so having no stored-procs doesn’t impact the unit or web tests. That’s just something you’ll have to think about should you use the database file for other purposes.


Enjoy.