Sql Server Express edition packs something really cool – User Instances.
Let me elaborate. I have following requirements -
- A windows application
- A single user database
- Click-once deployment
I found out I can create a solution with Sql server User Instance and local database really easily. This is one way to do this -
Add Database to Visual Studio
Create a windows forms application solution in Visual Studio. Next create the database using SSMS. Detach the database and copy/add the mdf file to visual studio solution. Alternatively, you can add a local database in Visual Studio and edit the database model from VS.
Update Project
Update project properties by right clicking on project and selecting properties. Verify that in publish tab\Application files your mdf file is setup as data file and required. Also make sure, mdf file Build Action is set to Content and Copy.
Since we will use Sql Server Express 2008, you will need to install Visual Studio 2008 Express edition. This is because Visual Studio 2008 Standard came up before Sql Server 2008 and does not have the necessary Sql Server 2008 bootstrapper. When you install VS Express, make sure you select Sql Server Express to install as well.
Add Sql Server 2008 Express edition to your list of pre-requisites under Publish tab.
You may also add a update url if you choose.
Setup Connection
Create a database connection string in your config file as following -
<add name="MyConnectionString" connectionString="Server=.\SQLExpress;AttachDBFileName=|DataDirectory|\mydatabase.mdf;Database=mydatabase;Trusted_Connection=Yes;User Instance=True "
providerName="System.Data.SqlClient"/>
During click-once deployment, Installer will copy the mdf file to the data folder in your profile directory (directory address varies among flavors of windows – in Windows 7 it is here: C:\Users\<username>\AppData\Local\Apps\2.0\Data). During update, the mdf file will be left alone. You will need code to update your database.
Publish
Publishing itself is straightforward. Choose your deployment option (Web/CDROM etc) and hit deploy. When user runs the installer, Sql Server Express will auto-download and install. The database file will attach itself and stay attached. No hassle on your part!
Few things to remember:
You won’t be able to view your database if you connect to the Express instance. this is because, when you specify User Instance = true in your connection string, Sql Server runs a special instance of Sql Server that allows non-admin users to create/attach database. For troubleshooting use following Sql command to find your instance –
SELECT owning_principal_name, instance_pipe_name, heart_beat FROM sys.dm_os_child_instances
You can use the value for instance_pipe_name as your server name to connect to this instance. this instance will shut down after 60 minutes of inactivity.
Also, if you want to have the option to allow user to be able to use either Standard Sql Server or Express, you can modify the connection string at runtime. Just remove the “User Instance” key.
