Tag Archives: sql

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
    If Global.Connection.State = ConnectionState.Open Then
    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.Move(strTmpDBName, strDBName)

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

   ‘ Cleanup

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

  End If
 End Function