Template to save SQLiteDB (EF6) to Temporary Location (automatically and user selected).
If you use SQLite in desktop application (How to use SQLite with EF6), you can save it ti temporary dir. Similar pattern, but for simple text file I have described a couple years ago (DataGridView-редактор параметрів програми), but in SQLite with EF6 there are something another way, especially prototype of DB is possible to store in Resource. This is an alternative way to produce DB from EF6 Code First.
In this page I describe two my useful pattern.In both case I use a template of empty database from resource (see first screen below).
1. Static pattern for set database in temporary location.
In this case project develop as Database first, with support of SQLite studio.
App.Config file in this case as usual for any SQLite development.
1: <?xml version="1.0" encoding="utf-8"?>
2: <configuration>
3: <configSections>
4: <sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
5: <section name="Folex_1.My.MySettings" type="System.Configuration.ClientSettingsSection, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
6: </sectionGroup>
7: <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
8: <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
9: <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
10: <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
11: </configSections>
12: <startup>
13: <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
14: </startup>
15: <runtime>
16: <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
17: <dependentAssembly>
18: <assemblyIdentity name="Newtonsoft.Json" publicKeyToken="30ad4fe6b2a6aeed" culture="neutral" />
19: <bindingRedirect oldVersion="0.0.0.0-12.0.0.0" newVersion="12.0.0.0" />
20: </dependentAssembly>
21: <dependentAssembly>
22: <assemblyIdentity name="System.Runtime.CompilerServices.Unsafe" publicKeyToken="b03f5f7f11d50a3a" culture="neutral" />
23: <bindingRedirect oldVersion="0.0.0.0-4.0.6.0" newVersion="4.0.6.0" />
24: </dependentAssembly>
25: <dependentAssembly>
26: <assemblyIdentity name="System.Data.SQLite" publicKeyToken="db937bc2d44ff139" culture="neutral" />
27: <bindingRedirect oldVersion="0.0.0.0-1.0.112.0" newVersion="1.0.111.0" />
28: </dependentAssembly>
29: <dependentAssembly>
30: <assemblyIdentity name="System.Data.SQLite.EF6" publicKeyToken="db937bc2d44ff139" culture="neutral" />
31: <bindingRedirect oldVersion="0.0.0.0-1.0.112.0" newVersion="1.0.111.0" />
32: </dependentAssembly>
33: </assemblyBinding>
34: </runtime>
35: <applicationSettings>
36: <Folex_1.My.MySettings>
37: <setting name="BaseApiURL" serializeAs="String">
38: <value>https://testsystemapi.folex.io</value>
39: </setting>
40: </Folex_1.My.MySettings>
41: </applicationSettings>
42: <entityFramework>
43: <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
44: <providers>
45: <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
46: <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
47: <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
48: </providers>
49: </entityFramework>
50: <system.data>
51: <DbProviderFactories>
52: <remove invariant="System.Data.SQLite.EF6" />
53: <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
54: <remove invariant="System.Data.SQLite" /><add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" /></DbProviderFactories>
55: </system.data>
56: <connectionStrings>
57: <add name="FolexDb" connectionString="metadata=res://*/FolexDB.csdl|res://*/FolexDB.ssdl|res://*/FolexDB.msl;provider=System.Data.SQLite.EF6;provider connection string="data source=C:\Users\khark\AppData\Local\Folex-1\FolexDB.sqlite"" providerName="System.Data.EntityClient" />
58: </connectionStrings>
59: </configuration>
In form load we can insert this code fragment.
8: Imports System.Configuration
...
10: Public Class StartForm
11:
12: Dim IsFormLoaded As Boolean = False
13: Public WithEvents LoginTimer1 As New Timer
14: Dim db1 As FolexDb
15: Dim Reg As Registry
...
145: Reg = New Registry("FolexBot")
146: '
147: Try
148: Dim FolexDbLocation As String = Reg.GetValue(Of String)("Database")
149: Dim EmptyDB As Byte() = My.Resources.FolexDB
150: Dim TempDir As String = GetTempDirectory()
151: If FolexDbLocation Is Nothing Then
152: If Not My.Computer.FileSystem.DirectoryExists(TempDir) Then My.Computer.FileSystem.CreateDirectory(TempDir)
153: My.Computer.FileSystem.WriteAllBytes(IO.Path.Combine(TempDir, "FolexDB.sqlite"), EmptyDB, False)
154: Reg.SetValue("Database", IO.Path.Combine(TempDir, "FolexDB.sqlite"))
155: FolexDbLocation = Reg.GetValue(Of String)("Database")
156: Else
157: If Not My.Computer.FileSystem.FileExists(FolexDbLocation) Then
158: If Not My.Computer.FileSystem.DirectoryExists(TempDir) Then My.Computer.FileSystem.CreateDirectory(TempDir)
159: My.Computer.FileSystem.WriteAllBytes(IO.Path.Combine(TempDir, "FolexDB.sqlite"), EmptyDB, False)
160: End If
161: End If
162: Dim OldConfig = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
163: Dim CurConnectionString As String = OldConfig.ConnectionStrings.ConnectionStrings("FolexDb").ConnectionString
164: Using AppConfig.Change(IO.Path.Combine(TempDir, "App.config"))
165: Dim NewConfig = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
166: NewConfig = OldConfig
167: NewConfig.ConnectionStrings.ConnectionStrings.Remove("FolexDb")
168: NewConfig.ConnectionStrings.ConnectionStrings.Add(New ConnectionStringSettings With {
169: .Name = "FolexDb",
170: .ProviderName = "System.Data.EntityClient",
171: .ConnectionString = CurConnectionString.Replace("C:\Users\khark\AppData\Local\Folex-1\FolexDB.sqlite", FolexDbLocation)})
172: NewConfig.SaveAs(IO.Path.Combine(TempDir, "App.config"), ConfigurationSaveMode.Modified)
173: ConfigurationManager.RefreshSection("connectionStrings")
174: db1 = New FolexDb
175: End Using
176: Catch ex As Exception
177: If ex.InnerException IsNot Nothing Then
178: MsgBox(ex.Message.ToString & vbCrLf & ex.InnerException.ToString)
179: Else
180: MsgBox(ex.Message.ToString)
181: End If
182: End
183: End Try
...
In this case Reg is my standard old class for working with registry - Простий сервіс реестра для зберігання вводу користувача десктопних прог., as AppConfig class I also use my old class Dispose unmanaged resources (configuration, fonts). GC.SuppressFinalize and GetTempDirectory is a ordinary function:
35: Function GetTempDirectory() As String
36: Dim LocalTmpPath As String = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData)
37: Dim TempAppDirectory As String = IO.Path.Combine(LocalTmpPath, Application.ProductName)
38: If Not My.Computer.FileSystem.DirectoryExists(TempAppDirectory) Then
39: My.Computer.FileSystem.CreateDirectory(TempAppDirectory)
40: End If
41: Return TempAppDirectory & "\"
42: End Function
2. Dynamic pattern. User select arbitrarily database location.
This pattern is different, in this case program is locate inunprotected place (not in C:\Program Files\), therefore not need a create additional virtual .NET subdomain to start .Net program, app.config is changed in place.
In this case all have been start from empty DB in resource.
Current location we will track in Registry.
Firstly I have a EF6 config to connect to SQLiteDB, it has been published in page How to use SQLite with EF6
In main form I have a definition.
27: Public Class StartForm
28:
29: Public Shared Property ConnectionString As String
31: Public Shared db1 As Url_DB
32: Public Shared Rows As List(Of Site)
When application started, it read from registry location of SQLiteDB and save it in variables in StartForm.
40: StartFormInstance = New StartForm
41: Dim OldConnectionString = ConfigurationManager.ConnectionStrings("Url_DB").ConnectionString
42: Dim NewConnectionString As String = System.Text.RegularExpressions.Regex.Replace(OldConnectionString, ".data source=.*", """" & MyRegistry.GetValue(Of String)("Database") & """")
43: StartFormInstance.ConnectionString = NewConnectionString
But if application is start first time, it execute master to tuning program, including select location of new workable SQLite database. If this master is execute by first start of program, it simple store to registry location from config.
1: Imports System.ComponentModel
2: Imports System.Configuration
3: Imports System.Reflection
5:
6: Public Class Setting
...
25: LoginForm.MyRegistry.SetValue("Database", ConfigurationManager.ConnectionStrings("Url_DB").ConnectionString)
...
35: CurrenDbLocLabel.Text = StartForm.db1.Database.Connection.ConnectionString.Replace("data source=", "")
But maybe location from config after installation is readonly, therefore user is invited to select different location. This is name of variables in this tab.
And this is four function to support current operation to select new DB location (with old existing DB) and produce new empty DB from resource. After new location is selected, it save to Registry and variables on StartFrom.
69: Private Sub SetNewDBLocationButton_Click(sender As Object, e As EventArgs) Handles SetNewDBLocationButton.Click
70: If NewEmptyDBCheckBox.Checked Then
71: Dim X As New FolderBrowserDialog
72: X.Description = "Select place to database"
73: X.ShowNewFolderButton = True
74: If DialogResult.OK = X.ShowDialog Then
75: Dim NewPath = IO.Path.Combine(X.SelectedPath, "ResumeDB.db")
76: 'IO.File.Create(NewPath)
77: My.Computer.FileSystem.WriteAllBytes(NewPath, My.Resources.ResumeDB, False)
78: DbNameRefresh(NewPath)
79: End If
80: Else
81: Dim X As New OpenFileDialog
82: X.Title = "Browse SQLite database"
83: X.Filter = "SQLite database (*.db)|*.db|All files (*.*)|*.*"
84: X.FilterIndex = 1
85: X.CheckFileExists = True
86: X.CheckPathExists = True
87: If DialogResult.OK = X.ShowDialog Then
88: DbNameRefresh(X.FileName)
89: End If
90: End If
91:
92: End Sub
93:
94: Sub DbNameRefresh(NewPath As String)
95: CurrenDbLocLabel.Text = NewPath
96: Dim NewConnectionString As String
97: LoginForm.MyRegistry.SetValue("Database", "data source=" & NewPath)
98: NewConnectionString = GetNewConnectionString(NewPath)
99: If LoginForm.StartFormInstance IsNot Nothing Then
100: CurrenDbLocLabel.Text = StartForm.db1.Database.Connection.ConnectionString.Replace("data source=", "")
101: DatabaseTabClose.Visible = True
102: StartForm.ConnectionString = NewConnectionString
103: LoginForm.StartFormInstance.DataGridView1_RowFefresh()
104: End If
105: End Sub
106:
107: Public Function ReadResource(ResourceName As String) As Byte()
108: Dim Asm1 As Assembly = Assembly.GetExecutingAssembly()
109: Dim Stream1 As IO.Stream = Asm1.GetManifestResourceStream(ResourceName)
110: Return Stream1.ToByteArray
111: End Function
112:
113: Function GetNewConnectionString(NewDB As String) As String
114: Dim OldConnectionString = ConfigurationManager.ConnectionStrings("Url_DB").ConnectionString
115: Return System.Text.RegularExpressions.Regex.Replace(OldConnectionString, ".data source=.*", """" & "data source=" & NewDB & """")
116: End Function
Comments (
)
)
Link to this page:
//www.vb-net.com/SQLite-Ef6-InTempDir/index.htm
|
|