Programmatically create a SQL Server Compact database on the client machines

Question

I have recently started experimenting with SQL Server Compact and EF6. I am currently using the model first approach and generating my classes and tables from it. I am curious though, about one thing. How can I get my program to dynamically create the database. It exists on my machine as I created it with the SQL Server Compact/SQLite toolkit, but when the program is deployed to client computers, the database will need to be created.

I would want to prompt them on first run for a good location, then create the entire DB schema and just use that in the future. I looked into this guide but vs started complaining about it not working because I didn't use a code-first approach.

If you need more info let me know! Thanks.

Answer 1

I have a little app I'm working on using SQL CE and EF and I deploy the template database when the app is installed (clickonce). Then I use a spash screen on application start to either load a previously created database or let the user create a new one.

When they create a new db I simply prompt them for a location and copy the template database out to their desired location with their desired name. I've also set it up to use the deployed database without giving them the opportunity have multiple database files.

We are dealing with a few pieces here as follows:

1. SQL CE database file

My stock/template .sdf file sits in my project folder and is included in the project within Visual Studio (I'm using 2015). Rightclick on the file in the Solution Explorer and select properties you want to set the following:

Build Action - Content
Copy to Output Directory - Copy always

2. Create global variable

Either use an existing module file or create a new one that looks like this:

Public Module Globals

  Friend g_recipeData As RecipeEntities

End Module

3. Create setting for last file path

Rightclick on your project name in the solution explorer and pick Properties. Click the Settings tab and add a new setting as follows:

Name: lastpath
Type: String
Scope: User
Value:

4. Create splash screen form (frmSplash)

Mine looks like this:

Controls on the form are as follows:

txtFile
cmdSelectDatabase
cmdNew
cmdOpen
cmdExit

5. Splash Screen (frmSplash) Code

Region "Form Methods"

Private Sub OnFormLoad() Handles Me.Load

    txtFile.Text = My.Settings.lastpath

    If txtFile.Text <> "" Then
        cmdOpen.Enabled = True
        cmdOpen.Select()
    Else
        cmdNew.Select()
    End If

End Sub

Private Sub FileSelect()

    Try

        Dim openFileDialog As New OpenFileDialog()

        openFileDialog.Filter = "sdf files (*.sdf)|*.sdf|All files (*.*)|*.*"
        openFileDialog.FilterIndex = 1
        openFileDialog.RestoreDirectory = True

        Dim result As DialogResult = openFileDialog.ShowDialog(Me)

        If result = DialogResult.Cancel Then
            cmdSelectDatabase.Select()
            Exit Sub
        End If

        txtFile.Text = openFileDialog.FileName

        If txtFile.Text <> "" Then
            cmdOpen.Enabled = True
            cmdOpen.Select()
            My.Settings.lastpath = openFileDialog.FileName
            My.Settings.Save()
        Else
            cmdOpen.Enabled = False
            cmdSelectDatabase.Select()
        End If

    Catch ex As Exception

        MessageBox.Show(ex.Message.ToString, "Application Error")
        Application.Exit()

    Finally

    End Try

End Sub 

Private Sub SetConnectionString()

    Try

        Dim providerName As String = "System.Data.SqlServerCe.4.0"
        Dim datasource As String = txtFile.Text

        Dim sqlCeBuilder As New SqlCeConnectionStringBuilder

        sqlCeBuilder.DataSource = datasource
        sqlCeBuilder.PersistSecurityInfo = True

        g_SQLCeConnectionString = sqlCeBuilder.ConnectionString

        Dim providerString As String = sqlCeBuilder.ToString()

        Dim entityBuilder As New EntityConnectionStringBuilder()

        entityBuilder.Provider = providerName

        entityBuilder.ProviderConnectionString = providerString

        entityBuilder.Metadata = "res://*/RecipeModel.csdl|res://*/RecipeModel.ssdl|res://*/RecipeModel.msl"

        Dim c As System.Configuration.Configuration = ConfigurationManager.OpenExeConfiguration(System.Reflection.Assembly.GetExecutingAssembly().Location)
        Dim section As ConnectionStringsSection = DirectCast(c.GetSection("connectionStrings"), ConnectionStringsSection)

        g_EntityConnectionString = entityBuilder.ConnectionString

        section.ConnectionStrings("RecipeEntities").ConnectionString = g_EntityConnectionString
        c.Save(ConfigurationSaveMode.Modified)
        ConfigurationManager.RefreshSection("connectionStrings")

    Catch ex As Exception

        MessageBox.Show(ex.Message.ToString, "Application Error")
        Application.Exit()
    End Try

End Sub 

Private Sub CreateDatabase()

    Try
        Dim saveFileDialog As New SaveFileDialog()
        saveFileDialog.Filter = "sdf files (*.sdf)|*.sdf"
        saveFileDialog.Title = "Create Database"
        saveFileDialog.FilterIndex = 1

        If saveFileDialog.ShowDialog() = DialogResult.OK Then

            File.Copy(Path.Combine(ApplicationDeployment.CurrentDeployment.DataDirectory, "rw.sdf"), saveFileDialog.FileName, True)

            Dim strPathandFile As String = saveFileDialog.FileName

            txtFile.Text = strPathandFile
            My.Settings.lastpath = strPathandFile
            My.Settings.Save()

            cmdOpen.Enabled = True

        End If

    Catch ex As Exception

        MessageBox.Show(ex.Message.ToString, "Application Error")
        Application.Exit()
    End Try

End Sub

Private Sub LoadMainApplication()

    Try
        Dim objNewForm As New FrmMain
        objNewForm.Show()
        Me.Close()

    Catch ex As Exception

        MessageBox.Show(ex.Message.ToString, "Application Error")
        Application.Exit()
    End Try

End Sub

End Region

Region "Event Handlers"

Private Sub cmdSelectDatabase_Click(sender As Object,
                                    e As EventArgs) Handles cmdSelectDatabase.Click

    FileSelect()
    cmdOpen.Select()

End Sub


Private Sub cmdCancel_Click(sender As Object, e As EventArgs) Handles cmdExit.Click
    Me.Close()
End Sub


Private Sub cmdOk_Click(sender As Object, e As EventArgs) Handles cmdOpen.Click

    Me.Cursor = Cursors.WaitCursor

    SetConnectionString()
    LoadMainApplication()

    Me.Cursor = Cursors.Default

End Sub


Private Sub txtFile_Validated(sender As Object, e As EventArgs) Handles txtFile.Validated
    If txtFile.Text.Length = 0 Then
        cmdOpen.Enabled = False
    Else
        cmdOpen.Enabled = True
    End If
End Sub

Private Sub cmdNew_Click(sender As Object,
                         e As EventArgs) Handles cmdNew.Click
    CreateDatabase()
    SetConnectionString()
    LoadMainApplication()

End Sub

Private Sub CatchEnterKey(ByVal sender As Object,
    ByVal e As System.Windows.Forms.KeyPressEventArgs) _
        Handles txtFile.KeyPress, txtPassword.KeyPress


    If e.KeyChar = ChrW(Keys.Enter) Then
        cmdOk_Click(sender, e)
        e.Handled = True
        Exit Sub
    End If

End Sub

6. Set global variable value

On the form of your main application (frmMain above) add the following to the constructor:

Public Sub New()

    InitializeComponent()
    g_recipeData = New RecipeEntities

End Sub

If you perform the above action when you create the variable in the module file then the connection string for the entity is set and you can't change it. You must set the connection string in app.config (using the above code) first and then the entity will use desired connection string when instantiated.

I think that's the basics for now. I highly recommend you reading through it again now that I'm done. I made some corrections and even added a step for the lastpath setting. Just hit me up if something isn't working or is confusing and I'll do my best to help. Good luck!