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.
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:
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
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
Rightclick on your project name in the solution explorer and pick Properties. Click the Settings tab and add a new setting as follows:
Mine looks like this:
Controls on the form are as follows:
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 Dim strPathandFile As String = openFileDialog.FileName txtFile.Text = strPathandFile If txtFile.Text <> "" Then cmdOpen.Enabled = True cmdOpen.Select() My.Settings.lastpath = strPathandFile 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
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
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!