title: Fastest way to upload a Datatable to SQLite
body: <p>So, I was using SQL Server but there is a need for me to change to SQLite.
  I had the following code that worked very fast for 600.000 rows (about a minute).</p>
  
  <pre class="lang-vb prettyprint-override"><code>Dim SQLAdapter As New SqlDataAdapter
  Dim dtKinoDraws As New DataTable
  Dim SQLcmdBuilder As SqlCommandBuilder
  
  'Emptying the Database
  ExecuteSQLQuery(&lt;SQL&gt;
                      USE &lt;%= DatabaseName %&gt;
                      TRUNCATE TABLE [&lt;%= DatabaseName %&gt;].[dbo].&lt;%= KinoDrawsTableName %&gt;
                  &lt;/SQL&gt;.Value)
  
  'This is so we have the SCHEMA to use dtKinoDraws.NewRow later to add the 400.000 rows
  SQLAdapter = New SqlDataAdapter(&lt;SQL&gt;
                                       USE &lt;%= DatabaseName %&gt;
                                       Select * FROM [&lt;%= DatabaseName %&gt;].[dbo].&lt;%= KinoDrawsTableName %&gt;
                                  &lt;/SQL&gt;.Value, SQLConn)
  SQLcmdBuilder = New SqlCommandBuilder(SQLAdapter)
  SQLAdapter.Fill(dtKinoDraws)
  
  [Adding the 600.000+ rows to the datatable dtKinoDraws as described below]
  
  'Copying everything from the datatable back to SQL Server (takes about a minute)
  Using bulkCopy As New SqlBulkCopy(SQLConn)
      bulkCopy.DestinationTableName = KinoDrawsTableName
      bulkCopy.WriteToServer(item)
  End Using
  
  SQLAdapter.Update(dtKinoDraws)
  SQLAdapter.Dispose()
  SQLConn.Close()
  </code></pre>
  <p>Now I tried to do the same with SQLite but there is no SQLiteBulkCopy, modelling after my previous code and after searching stack overflow for a way, I wrote this extremely inefficient and snail-slow piece of code (takes literally HOURS):</p>
  <pre class="lang-vb prettyprint-override"><code>Dim SQLiteAdapter As New SQLiteDataAdapter
  Dim dtKinoDraws As New DataTable
  Dim SQLitecmdBuilder As SQLiteCommandBuilder
  
  'Emptying the Database
  ExecuteSQLQuery(&lt;SQL&gt;
                      DELETE FROM [&lt;%= KinoDrawsTableName %&gt;];
                      VACUUM;
                  &lt;/SQL&gt;.Value)
  
  'This is so we have the SCHEMA to use dtKinoDraws.NewRow later to add the 400.000 rows
  SQLiteAdapter = New SQLiteDataAdapter(&lt;SQL&gt;
                                            Select * FROM &lt;%= KinoDrawsTableName %&gt;
                                        &lt;/SQL&gt;.Value, SQLConnLite)
  SQLitecmdBuilder = New SQLiteCommandBuilder(SQLiteAdapter)
  
  SQLiteAdapter.Fill(dtKinoDraws)
  
  [Adding the 600.000+ rows to the datatable dtKinoDraws as described below]
  
  SQLiteAdapter.Update(dtKinoDraws)
  SQLiteAdapter.Dispose()
  SQLConnLite.Close()
  </code></pre>
  <p>Visual Studio's Output gives me a nearly infinite number of this message at least twice a second: &quot;SQLite notice (27): delayed 25ms for lock/sharing conflict at line 42982&quot;</p>
  <p>How can I improve it to be fast like the SQL Server one?
  My code is in VB, so I'd prefer VB code, but I can translate C# ones to VB so that's cool too :)</p>
  <p>===========================================================================
  Now, I didn't think that the way the records are added to the datatable plays a role as THAT only takes seconds, but as per @Plutonix request, here's the whole code:</p>
  <pre class="lang-vb prettyprint-override"><code>Private Async Function LoadKinoDrawsFromTextFiles(ByVal FromValue As Integer, ByVal ToValue As Integer, ByVal Files() As String, ByVal dtTable As DataTable) As Task(Of DataTable)
      Dim Result As DataTable = dtTable.Clone
  
      Await Task.Run(
          Sub()
              For i As Integer = FromValue To ToValue
                  DownloadingDrawsProgress += 1
                  Dim FileContent() As String = File.ReadAllLines(Files(i))
                  Dim CurLineKinoDrawNum As Integer = -1
  
                  For Each line In FileContent
                      If line &lt;&gt; &quot;&quot; AndAlso line.Length &gt; 10 Then
                          Try
                              Dim LineDecomposition As List(Of String) = line.Split(&quot; &quot;c).ToList
  
                              Dim NewDataRow As DataRow = Result.NewRow
                              CurLineKinoDrawNum = CInt(LineDecomposition(0))
                              NewDataRow(0) = CurLineKinoDrawNum
                              Try
                                  NewDataRow(1) = Date.ParseExact(LineDecomposition(1) &amp; &quot; &quot; &amp; LineDecomposition(2), KinoDateFormat, CultureInfo.InvariantCulture)
                              Catch ex As Exception
                                  Try
                                      NewDataRow(1) = Date.ParseExact(LineDecomposition(1) &amp; &quot; &quot; &amp; LineDecomposition(2), &quot;d/M/yyyy HH:mm&quot;, CultureInfo.InvariantCulture)
                                  Catch exc As Exception
                                      NewDataRow(1) = Date.Parse(LineDecomposition(1) &amp; &quot; &quot; &amp; LineDecomposition(2))
                                  End Try
                              End Try
                              NewDataRow(2) = ArrayBox(False, &quot; &quot;, 0UI, False, LineDecomposition.Skip(3), True, &quot;&quot;)
                              Result.Rows.Add(NewDataRow)
  
                          Catch ex As Exception
                              Try
                                  Dim Tries As Integer = 0
                                  Dim DownloadClient As New WebClient With {.Proxy = Nothing}
                                  Dim OnlineDrawJSONString As String
                                  Dim OnlineDrawJSON As JObject = Nothing
  
                                  Do Until Tries &gt;= 10
                                      Try
                                          OnlineDrawJSONString = DownloadClient.DownloadString(DrawByNumberLink.Replace(&quot;{NumIdentifier}&quot;, CurLineKinoDrawNum.ToString))
                                          OnlineDrawJSON = JObject.Parse(OnlineDrawJSONString)
                                          Tries = 10
                                      Catch exb As Exception
                                          Thread.Sleep(2000)
                                          Tries += 1
                                      End Try
                                  Loop
                                  Tries = 0
  
                                  If OnlineDrawJSON Is Nothing Then Throw New Exception(&quot;Unable to download the Draw!&quot;)
  
                                  Dim OnlineDrawNum As Integer = OnlineDrawJSON(DrawIdentifier)(DrawNumberIdentifier).Value(Of Integer)()
                                  Dim dateStr As String = CStr(OnlineDrawJSON(DrawIdentifier)(DrawDateIdentifier))
                                  dateStr = dateStr.Substring(0, dateStr.IndexOf(&quot;T&quot;)) &amp; &quot; &quot; &amp; dateStr.Substring(dateStr.IndexOf(&quot;T&quot;) + 1, 5) '5 because it'll always be 5. e.g. &quot;22:00&quot;.Length = 5
                                  Dim OnlineDrawDate As Date
                                  Try
                                      OnlineDrawDate = Date.ParseExact(dateStr, &quot;dd-MM-yyyy HH:mm&quot;, CultureInfo.InvariantCulture)
                                  Catch exn As Exception
                                      Try
                                          OnlineDrawDate = Date.ParseExact(dateStr, &quot;d-M-yyyy HH:mm&quot;, CultureInfo.InvariantCulture)
                                      Catch exc As Exception
                                          OnlineDrawDate = Date.Parse(dateStr)
                                      End Try
                                  End Try
  
                                  Dim OnlineResults As List(Of Integer) = OnlineDrawJSON(DrawIdentifier)(DrawResultsIdentifier).Values(Of Integer)().ToList
  
                                  Dim drRow As DataRow = Result.NewRow
                                  drRow(0) = OnlineDrawNum
                                  drRow(1) = OnlineDrawDate
                                  drRow(2) = ArrayBox(False, &quot; &quot;, 0UI, False, OnlineResults, True, &quot;&quot;)
                                  Result.Rows.Add(drRow)
  
                              Catch exc As Exception
                                  CreateCrashFile(exc, True,, MsgBoxStyle.Critical)
                                  Exit Sub
                              End Try
                          End Try
                      End If
                  Next
              Next
          End Sub)
  
      Return Result
  End Function
  
  Public Function ArrayBox(Of T)(ByVal doNumeriseItems As Boolean, ByVal DelimitationStr As String, ByVal SplitOnNum As UInteger, ByVal IgnoreNullValues As Boolean, ByVal IETVar As IEnumerable(Of T), Optional ByVal IgnoreDelimitSpace As Boolean = False, Optional ByVal DefaultSpace As String = &quot; &quot;, Optional ByVal AlwaysDelimitBeforeNewLine As Boolean = False, Optional ByVal PrefixString As String = &quot;&quot;, Optional ByVal SuffixString As String = &quot;&quot;, Optional DoNotPrefixIfValueIsNumeric As Boolean = False, Optional DoNotSuffixIfValueIsNumeric As Boolean = False, Optional ByVal AddTwoDoubleQuotesBeforeADoubleQuoteCharacter As Boolean = False, Optional ByVal StrInCaseOfNullValue As String = &quot; &quot;) As String
      Dim sbRet As New StringBuilder
  
      If IETVar IsNot Nothing Then
          For i = 0 To IETVar.Count - 1
              If (IETVar(i) IsNot Nothing AndAlso Not IsDBNull(IETVar(i))) OrElse Not IgnoreNullValues Then   '   If the IEVariable is something
                  Dim StringToBeAppended As String = StrInCaseOfNullValue
                  Try
                      StringToBeAppended = IETVar(i).ToString
                  Catch ex As Exception
                  End Try
  
                  If doNumeriseItems Then sbRet.Append(i + 1).Append(&quot;) &quot;) '      Numerise it if user asked it
  
                  If PrefixString &lt;&gt; &quot;&quot; AndAlso (Not IsNumeric(StringToBeAppended) OrElse Not DoNotPrefixIfValueIsNumeric) Then ' If there is a Prefix set, and value isnt numeric OR, is numeric but prefixing is allowed
                      sbRet.Append(PrefixString) '                                                                                then append it before the actual value
                  End If
  
                  If Not AddTwoDoubleQuotesBeforeADoubleQuoteCharacter Then '         If we are to proceed normally,
                      sbRet.Append(StringToBeAppended) '                              Print the Value of IETVar(i)
                  Else
                      sbRet.Append(StringToBeAppended.Replace(&quot;&quot;&quot;&quot;, &quot;&quot;&quot;&quot;&quot;&quot;&quot;&quot;)) '      Else, add 2 double-quotes before any double-quote character
                  End If
  
                  If SuffixString &lt;&gt; &quot;&quot; AndAlso (Not IsNumeric(StringToBeAppended) OrElse Not DoNotSuffixIfValueIsNumeric) Then 'If there is a Suffix set, and value isnt numeric OR, is numeric but suffixing is allowed
                      sbRet.Append(SuffixString) '                                                                                    then append it after the actual value
                  End If
  
                  If AlwaysDelimitBeforeNewLine AndAlso DelimitationStr &lt;&gt; &quot;&quot; AndAlso i &lt;&gt; IETVar.Count - 1 Then '       If Always Delimit And it isn't the last element of all
                      sbRet.Append(DelimitationStr) '                                                                 then Delimit it
                      If Not IgnoreDelimitSpace AndAlso (SplitOnNum = 0 OrElse (i + 1) Mod SplitOnNum &lt;&gt; 0) Then sbRet.Append(DefaultSpace) ' Put a space if a space should be put
  
                  ElseIf DelimitationStr &lt;&gt; &quot;&quot; AndAlso (SplitOnNum = 0 OrElse (i + 1) Mod SplitOnNum &lt;&gt; 0) AndAlso i &lt;&gt; IETVar.Count - 1 Then '  if it should be delimited And it isn't the last element of the line
                      sbRet.Append(DelimitationStr) '                                                                                         then Delimit it
                      If Not IgnoreDelimitSpace Then sbRet.Append(DefaultSpace) '                                                             Put a space if a space should be put
                  End If
  
                  If SplitOnNum &lt;&gt; 0 AndAlso (i + 1) Mod SplitOnNum = 0 AndAlso i &lt;&gt; IETVar.Count - 1 Then '  If you separate them with NewLines and it is the last element of the line
                      sbRet.AppendLine() '                                                                then append a NewLine
                  End If
              End If
          Next
  
      End If
  
      Return sbRet.ToString
  End Function
  
  Public Async Sub FillDatabaseWithDefaultDraws()
  Dim SQLiteAdapter As New SQLiteDataAdapter
  Dim dtKinoDraws As New DataTable
  Dim SQLitecmdBuilder As SQLiteCommandBuilder
  
  'Emptying the Database
  ExecuteSQLQuery(&lt;SQL&gt;
                      DELETE FROM [&lt;%= KinoDrawsTableName %&gt;];
                      VACUUM;
                  &lt;/SQL&gt;.Value)
  
  'This is so we have the SCHEMA to use dtKinoDraws.NewRow later to add the 400.000 rows
  SQLiteAdapter = New SQLiteDataAdapter(&lt;SQL&gt;
                                            Select * FROM &lt;%= KinoDrawsTableName %&gt;
                                        &lt;/SQL&gt;.Value, SQLConnLite)
  SQLitecmdBuilder = New SQLiteCommandBuilder(SQLiteAdapter)
  
  SQLiteAdapter.Fill(dtKinoDraws)
  Dim Files() As String = Directory.GetFiles(strKinoDrawsDirPath)
  
  Dim item = Await LoadKinoDrawsFromTextFiles(0, Files.Length - 1, Files, dtKinoDraws)
  dtKinoDraws.Merge(item)
  
  SQLiteAdapter.Update(dtKinoDraws)
  SQLiteAdapter.Dispose()
  SQLConnLite.Close()
  End Sub
  </code></pre>
  <p>And here's what each file in Files(i) looks like:</p>
  <blockquote>
  <p>304454    30/11/2011  21:00   7   65  31  62  21  50  34  70  37  40  16  59  10  41  42  76  78  71  74  47</p>
  <p>304453    30/11/2011  20:55   62  30  68  63  9   45  10  50  1   78  22  6   61  52  38  2   55  26  49  72</p>
  <p>304452    30/11/2011  20:50   45  65  42  1   78  14  16  4   43  70  28  19  67  23  77  40  49  71  22  21</p>
  <p>...</p>
  <p>300971    1/11/2011   10:00   52  48  1   76  13  38  24  18  16  20  53  59  4   40  65  68  42  9   2   51</p>
  </blockquote>