(NET) NET (2018)

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=&quot;data source=C:\Users\khark\AppData\Local\Folex-1\FolexDB.sqlite&quot;" 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 ( )
<00>  <01>  <02>  <03>  <04>  <05>  <06>  <07>  <08>  <09>  <10>  <11>  <12>  <13>  <14>  <15>  <16>  <17>  <18>  <19>  <20>  <21>  <22>  <23> 
Link to this page: //www.vb-net.com/SQLite-Ef6-InTempDir/index.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>