This post originated from an RSS feed registered with .NET Buzz
by Doug Thews.
Original Post: Compacting Your SQL CE Database
Feed Title: IlluminatiLand
Feed URL: http://apps5.oingo.com/apps/domainpark/domainpark.cgi?client=netw8744&s=JETBRAINS.COM
Feed Description: A technology blog for people enlightened enough to think for themselves
SQL CE has an annoying property in that it does not reclaim space within databases as records are inserted
and deleted. This causes a SQL CE database (.SDF file) to grow unchecked. As space on a handheld device is at a
premium, we must take care of this within our CF.NET applications.
I've written a simple function in CF.NET to show how easy it is to compact your SQL CE database.
Private Function CompactDatabase()
Dim strDBName as string = "/My Documents/MyDatabase.sdf"
Dim strTmpDBName As String = "/MyDocuments/tmpCompactedDatbase.sdf"
Dim objSqlCeEngine As SqlCeEngine
' Check to see if the local DB exists
If File.Exists(objDBConfig.LocalDBLocation & objDBConfig.LocalDBName) Then
Try
' Compact the database to the new temporary location
objSqlCeEngine = New SqlCeEngine("Data Source=" & strDBName)
objSqlCeEngine.Compact("Data Source=" & strTmpDBName)
' Now delete the old database and move the new one back to the original location
File.Delete(strDBName)
File.Move(strTmpDBName, strDBName)
Catch ex As SqlCeException
' Put your error handling code here
End Try
' Cleanup
objSqlCeEngine.Dispose()
End If
As you can see, it's pretty easy to do. You just call the Compact() method of the SQLCeEngine
object, which will compact the database and place it in a new location. If successful, we
delete the old database and move the compacted version to the original area.
Since SQL CE is a single-user database, this function will fail if the SQL CE database is currently open
(SQL CE Query Analyzer or by an application). This leads to the simplicity
of the solution since we can always be guaranteed that if a connection is made, it is safe to
do the move & delete operation.
My recommendation is to run this code at the startup of your application. This will ensure
that the database is compacted on a routine basis.
Previous Topic
