Connecting to VistaDB in ASP.NET

I have been looking at the ASP.NET MVC extensions recently, and as a database for my test project, I decided to check out VistaDB. VistaDB is a fully managed lightweight alternative to SQL Server, and is available in a free express edition.

The benefits of VistaDB are that it the SQL is compatible with SQL-Server, and you can deploy your database as a single file in your App_Data folder. This is a big benefit for me, as it makes backing up a site trivial. It comes with a reasonably well featured utility for you to explore and modify your vdb3 files.

The only slight disappointment was the documentation was lacking a quickstart for absolute beginners, so it required a few google searches to get me up and running.

To use VistaDB with your website:

1. Add references to VistaDB.NET20 and VistaDB.Web (I also copied them to my bin folder, but I think VS would have done this automatically)
2. Create a vdb3 file and put it in your App_Data folder
3. Now you can open a connection and query it using code similar to the following:

string connectionString = "Data Source=|DataDirectory|\\Database.vdb3;Open Mode = NonexclusiveReadWrite;";

using (VistaDBConnection connection = new VistaDBConnection(connectionString))
{
    connection.Open();
    VistaDBCommand command = new VistaDBCommand("SELECT * FROM Posts", connection);
    VistaDBDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        Post post = new Post
        {
            Body = (string)reader["Body"],
            Author = (string)reader["AuthorName"],
            Published = (DateTime)reader["PublishedDate"],
            Status = (PublishStatus)reader["PublishState"],
            Title = (string)reader["Title"]
        };
        yield return post;
    }
}

And that was all there was to it. My next task is to work out how to use the VistaDB MembershipProvider, which is another area that is somewhat lacking in documentation. I'll post a how-to here once I have got things up and running.