SQL CE Compacting usingĀ .NET

Private Function CompactDatabase()
  Dim strDBName As String = txtDatabase.Text
  Dim strTmpDBName As String = “/Temp/tmpCompactedDatbase.sdf”
  Dim objSqlCeEngine As SqlCeEngine
  Dim success As Boolean = False
  ‘ Check to see if the local DB exists
  If System.IO.File.Exists(strDBName) Then
   Try
    If Global.Connection.State = ConnectionState.Open Then
     Global.Connection.Close()
    End If

    ‘ Compact the database to the new temporary location
    objSqlCeEngine = New SqlCeEngine(“Data Source=” & strDBName)
    objSqlCeEngine.Compact(“Data Source=” & strTmpDBName)     ‘//The actual code for compaction
    ‘ Now delete the old database and move the new one back to the original location
    System.IO.File.Delete(strDBName)
    System.IO.File.Move(strTmpDBName, strDBName)

    success = True
   Catch ex As SqlCeException
    success = False
    MessageBox.Show(ex.Message + ControlChars.CrLf + ex.InnerException.ToString())
   End Try

   ‘ Cleanup
   objSqlCeEngine.Dispose()

   if success
    MessageBox.Show(“Database has been compacted”)
   else
    MessageBox.Show(“Could not compact database. Correct error manually.”)
   End If

   Global.Connection.Open()
  End If
 End Function