In most cases you’ll want to create your database and it’s schema as part of your applications first deployment. Depending on the scenario, you may create it on the client, or create it on the server, do the initial sync, then stream the file down to the client pre-populated.
Why would you create the database on the fly, rather than ship the initial database with your app?
While you could create the empty shell, and deploy it, you’ll almost never want to start with a database that has data? Why? While in development, you’ll have some set of data. Months later, when another user first installs your app that data may no longer be valid. Since most sync systems don’t track deletes forever, you could wind up with data that is very stale, and now way to effectively update it.
Data Files and Click Once
If you’re using ClickOnce, the other problem is how ClickOnce manages data files. If you initially ship your app with a datafile that your customers edit you could loose those changes. If the file timestamp on your data file changes, ClickOnce will try to “update it” and replace the data file in your ClickOnce data directory. It does move the previous version to a .pre folder, but only once. If you realized you made this mistake, and send another update to โfix it”, you could again move the data file to the .pre folder, and the original one that had the data you were trying to save will be permanently lost.
For this post, I’m going to focus on how to create the database and schema. You’ll see that with a combination of some SQLServerCe APIs, and the use of Resources, it’s not very difficult to create, test within SQL Server Management Studio and deploy these updates to your client. This same procedure could be used for the initial deployment, as well as app updates where you may need to add new tables/columns, or add new relationships, constraints or indexes.
When an app starts up, I like to validate a few things like whether this is the first time the app was installed, if it’s the appropriate version, if all the necessary files still exist, as well as online/offline status.
Does the database exist?
I’ll first create a class called DatabaseHealthCheck() I’ll first check if the database even exists. Since with SQL Server Compact a database is a file, and a file is a database, it’s pretty straight forward. The file either exists, or it doesn’t. The connection string for a SQLce database contains a few name/value pairs depending on the options.
Data Source =”|DataDirectory|Another.sdf”; Password =”SomePassword”;
Rather than parse the string, or build it up manually, the SqlCeConnection object there’s a Database property that many don’t even think to look for. Since the database is a file…, all you need to do is create a connection object, and ask for the Database property. No need to open the connection, which wouldn’t work if the database/file didn’t exist.
Dim localConnection As New SqlCeConnection(My.Settings.LocalConnectionString)
If Not System.IO.File.Exists(localConnection.Database)
Nope, doesn’t exist, let’s create it
Next, if the database didn’t exist, we’ll need to create it. This is where it gets interesting. Since SQLce doesn’t run as a service, you can’t open a connection to the Master database and issue a Create Database command. No problem, SQLce has just the API for you. How about these two lines of code:
Dim sqlCeEngine As New SqlCeEngine(My.Settings.LocalConnectionString)
sqlCeEngine.CreateDatabase()
Pretty simple, huh. For those of you that were wondering why you would use SQL Server Compact Edition as your local database, now you’re probably starting to see how SQLce can be used to reduce the complexity really needed in a local/embedded database.
Creating the initial schema
It would be nice if we had a simple CreateTable API on the SqlCeEngine object, but we’re not there yet. But we can leverage T-SQL that we’d write in SQL Server Management Studio, at least the appropriate subset. We’re all familiar, although it’s likely not our favorite, with the ability to create scripts, separated by the GO command. Using this model we can test scripts, save them and re-execute them.
CREATE TABLE Suppliers(
SupplierID nVarChar(10) NOT NULL CONSTRAINT Suppliers_PK PRIMARY KEY,
CompanyName nvarchar(40) NOT NULL,
Description nvarchar(100) NULL,
SortOrder int NOT NULL DEFAULT(50),
Active bit NOT NULL DEFAULT (1)
)
GO
CREATE TABLE OrderStatus(
OrderStatus nChar(10) NOT NULL CONSTRAINT OrderStatus_PK PRIMARY KEY,
Status nvarchar(40) NOT NULL,
Description nvarchar(100) NULL,
SortOrder int NOT NULL DEFAULT(50),
Active bit NOT NULL DEFAULT (1)
)
GO
The problem is how do we get this in our code? We could embed the whole thing in quotes in our code:
Dim commandTest As String
commandTest = “CREATE TABLE OrderStatus(“ + _
” OrderStatus nChar(10) NOT NULL CONSTRAINT OrderStatus_PK PRIMARY KEY,” + _
“ Status nvarchar(40) NOT NULL,”
But we don’t have to get past line 2 before you realize, that sucks. And, if you ever want to make a change, you’ll have to decrypt this to put it back inside a query window. No problem, this is where we can leverage the handy dandy resource features.
Adding a SQL Script Resource
We’ll start by creating a script in SQL Server Management Studio. You can use the above CREATE TABLE scripts above to get things started. Within Management Studio, choose to save the script. It doesn’t really matter where, because VS will pull a copy into your project in the next step.
Name the script something with a .sql extension. If you do this, when you open the file within Visual Studio, you’ll get T-SQL syntax coloring.
Within Visual Studio, open the Resource designer. For VB, double click My Project from within your Solution Explorer. For C#, double click Properties. Click on the Resources tab on the left. From the toolbar select Add Resource ร Add Existing Resource. Select the file you just created, and voila.
Now you might ask why I didn’t just create the file here using the Add Resource ร Add New Text File. If I went that path, and I named the file Script.sql, Visual Studio would have added .txt to it so it would wind up as Script.sql.txt. If I then renamed the file in Solution Explorer, the link in the resources would be broken, and I would have to delete the resource reference, and then drag the renamed file back to the resource designer. Either works, this second way just highlights a less optimized scenarios. But, I guess I just did that. …either way…
Double click the script file you added, and voila, T-SQL syntax coloring. You may get a warning dialog, but just ignore it. The project template doesn’t understand this extension, yet Visual Studio does. You could just uncheck the checkbox so you won’t get nagged anymore.
Now, it would be nice if you could just execute the script, but no, this isn’t enabled in non-database projects.
Executing the script
We now have a script that we can copy/paste back and forth between Visual Studio and SQL Server Management Studio. But how do I execute this with my app? This is where we start to see the sum of the parts come together. We can use the My.Resources api, or in C#, the Resources.Default api to get a strongly typed experience to the resources in your project. We’ll then simply use the String.Split api to parse the commands on the word GO.
Dim commands() As String
commands = My.Resources.DatabaseCreation.Split( _
New String() {“GO”}, _
StringSplitOptions.RemoveEmptyEntries)
Putting it all together
That’s about it, we just need to put this all together now.
Public Class DatabasHealthCheck
Public Sub VerifyDatabaseExists(ByVal connectionString As String)
‘ we’ll use the SqlServerCe connection object to get the database file path
Using localConnection As New SqlCeConnection(My.Settings.LocalConnectionString)
‘ The SqlCeConnection.Database contains the file parth portion
‘ of the database from the full connectionstring
If Not System.IO.File.Exists(localConnection.Database) Then
‘ No file, no database
Using sqlCeEngine As New SqlCeEngine(connectionString)
sqlCeEngine.CreateDatabase()
CreateInitialDatabaseObjects(connectionString)
End Using
End If
End Using
End Sub
”’ <summary>
”’ When the database is created, we need to initialize it with new tables, relations and possibly data
”’ </summary>
Public Sub CreateInitialDatabaseObjects(ByVal connectionString As String)
‘ Grab a reference to the connection on the client provider
Using connection As New SqlCeConnection(connectionString)
‘ Using the SQL Management Studio convention of using GO to identify individual commands
‘ Create a list of commands to execute
Dim commands() As String
‘ To simplify editing and testing TSQL statements,
‘ the commands are placed in a managed resource of the dll.
commands = My.Resources.DatabaseCreation.Split( _
New String() {“GO”}, _
StringSplitOptions.RemoveEmptyEntries)
Dim cmd As New SqlCeCommand()
‘ make sure we put the connection back to its previous state
cmd.Connection = connection
connection.Open()
For Each command As String In commands
cmd.CommandText = command
cmd.ExecuteNonQuery()
Next
End Using
End Sub
End Class
In your application startup, simply execute the following code:
Dim databaseHealthCheck As New DatabasHealthCheck()
databaseHealthCheck.VerifyDatabaseExists(My.Settings.LocalConnectionString)
So, now you’ve got an easy way to create your initial database, or alter an existing database as part of your app. As always, I love to get your feedback and thoughts,
Steve
Steve Lasker 