(NET) NET (2018)

Template to save SQLiteDB (EF6) to Temporary Location.

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 this my useful pattern.



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
Link to this page: http://www.vb-net.com/SQLite-Ef6-InTempDir/index.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <MAIL ME>  <ABOUT ME>  < THANKS ME>