Wednesday, 15 April 2009

Switching to SQL Compact for Revit 64 bit

I recently posted that with the advent of Revit 64 some of us might be faced with having to update old systems and code for compatibility. Specifically for me this meant having to leave behind the old but reasonably faithful OLE DB provider for Microsoft Jet (no 64-bit version was ever provided for this) and adopt something new.

Microsoft's recommended database solution for single-user desktop (and mobile) applications is the free to use and distribute SQL Server Compact Edition (CE). With an approximately 5 MB memory footprint and a less than 2 MB disk footprint, SQL Server Compact Edition can run concurrently with other applications, making it effectively invisible to the user of the application. The app I'm working with extracts data from a Revit model, stores it in a local database, and compares it with data in another local database. For this, and I'd imagine lots of Revit plugins, SQL Server CE is ideal.

Of course, if you were starting a new project, or your app was small, you might considering using SQL CE along with newer technology like LINQ, which means you don't have to get your hands dirty with SQL stuff. But in the Real World some of us have big projects and little time, and need to adapt the code we have rather than re-write.

These are some of the things I had to consider when adapting our applications, which have plenty of different coding techniques from the hands of different developers:

Converting MS Access (.mdb) files to SQL Server CE (.sdf)

I used Primeworks's Data Port Wizard. It's intuitive and easy to use, and it did the job without a hitch.

When this is done, to open and manage your sdf files you can use Microsoft SQL Server Management Studio (SSMS) 2008 (but not 2005). To get this just download the full free trial of SQL Server 2008 and in the install process just go for the management tools only.

Sorting out SQL syntax and data types

Some of your SQL queries that did work won't work any more. Here's an example:

Select count(*) from TableName where myfield='hello'

Needs to be smartened up a little:

Select count(*) from [TableName] where [myfield]='hello'

And you may be using data types that aren't recognised in SQL CE. 'text' is one of them, for which I chose to substitute 'nvarchar'. But I'm no database expert - there may have been better options.

Changing connection strings

Typically I was changing my connection strings from something like this:

Private MasterFileConnection As OleDb.OleDbConnection

Dim connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myDatabase.mdb;Jet OLEDB:Database Password=letmein;"

MasterFileConnection = New OleDbConnection(connstring)

to something like this:

Private MasterFileConnection As SqlCeConnection

MasterFileConnection = New SqlCeConnection

MasterFileConnection.ConnectionString = "Data Source=myDatabase.sdf;Persist Security Info=False;Password=letmein;"


Handling data

In this example we were using an ADODB recordset to append new rows to a table:

Dim rsRecord As ADODB.Recordset
rsRecord = New ADODB.Recordset

rsRecord.Open(TableName, DB, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdTable)

rsRecord.Fields("Fieldname").Value = newValue


and it needed to be changed to this:

Dim rsRecord As SqlCeResultSet
Dim cmd As SqlCeCommand = DB.CreateCommand()

cmd.CommandText = "select * from [TableName]"
rsRecord = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)

newRow = rsRecord.CreateRecord

newRow.SetValue(rsRecord.GetOrdinal("Fieldname"), newValue)


and elsewhere using an OLEDB datareader we had to move from this:

Private command As OleDbCommand
Private drawingOptions As OleDbDataReader

command = New OleDbCommand("SELECT * FROM Tablename", MasterFileConnection)

If command.Connection.State = 0 Then command.Connection.Open()
drawingOptions = command.ExecuteReader(CommandBehavior.CloseConnection)

Do While drawingOptions.Read

'do something


to a SQL CE datareader, like this:

Private command As SqlCeCommand
Private drawingOptions As SqlCeDataReader

command = New SqlCeCommand("SELECT * FROM [Tablename]", MasterFileConnection)

If command.Connection.State = 0 Then command.Connection.Open()
drawingOptions = command.ExecuteReader()

Do While drawingOptions.Read

'do something



To do the coding shown above you already need to have referenced in the System.Data.SQLServerCE.dll to your project.

To get things to work on the end-user's machine you can either add SQL CE as a pre-requisite to your setup file, which will identify if a machine has it installed or not and send the user to download it from Microsoft accordingly.

Alternatively you can create a 'private installation', which by carrying all the necessary files (sqlceer35EN.dll, sqlcese35.dll, sqlceme35.dll, sqlceqp35.dll, System.Data.SqlServerCe.dll) in your package avoids the need for bootstrapping and a possibly contentious separate download. A lot neater.

1 comment:

  1. I want to use SQL CE to export ODBC data from Revit. How do you do that? In Revit there is no option for CE in the Export Odbc function.


Comments are moderated, so you'll have to wait a little bit before they appear!