Converting *.mdf database to SQL Compact *.sdf file.

I created some app that store a small amount of data in the database. Database was created as *.mdf file and the requirement of installing on each machine SQL Express engine is unpleasant thing.

I wanted quickly convert my database to SQL Compact format and database had to work with Linq2sql mapping. Exception by exception the goal was achieved.

Step 1. T-SQL database script and the new database

Following that article I downloaded:

  • Export2SqlCe,
  • ExportSqlCE_Addin,
  • SqlCeCmd40

and from the command line used:

  • Export2sqlce “Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True” AW.sqlce
  • sqlcecmd40 -d “Data Source=Database.sdf” -e create
  • sqlcecmd40 -d “Data Source=Database.sdf” -i aw.sqlce > log.txt

 Step 2. New mapping.

 From VS console created manually *.dbml file:

  • sqlmetal /dbml:Database.dbml Database.mdf

The main difference between the old *.dbml file and the newly created is constructor that now can’t be parameterless.

Database dc = new Database("Data Source=|DataDirectory|\\Database\\Database.sdf");

 At this moment I had a exception, something like: “System.Data.SqlServerCe.3.5′ not installed.”.

With some search, I edited app.config and machine.config. At first added support for 3.5, but finally  I stayed with CE 4.0 and the line below:

<system.data>
<DbProviderFactories>
<remove invariant="System.Data.SqlServerCe.4.0" />
<add name="Microsoft SQL Server Compact Data Provider 4.0"
invariant="System.Data.SqlServerCe.4.0"
description=".NET Framework Data Provider for Microsoft SQL Server Compact"
type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
</DbProviderFactories>
</system.data>

3. Create database one more time

That was time for another exception:

“Incompatible Database Version. If this was a compatible file, run repair. For other cases refer to documentation. [ Db version = 4000000,Requested version = 3505053,File name = \\\\?\\D:\\Calc\\Calc\\bin\\Debug\\Database\\Database.sdf ]”

So the simplest solution was create database one more time in the right version, with help of SQL Management Studio  and previously created script.

But I was faced with one more problem (really strange, ne?)

4. Edit *.dbml file

“The table name is not valid. [ Token line number (if known) = 2,Token line offset (if known) = 14,Table name = CalcConst ]”

Solution: Remove from *.dbml file all occurences of “dbo.” phrase.

That’s all. LINQ works with SQL Server CE.

Advertisements

0 Responses to “Converting *.mdf database to SQL Compact *.sdf file.”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s





%d bloggers like this: