Windows Phone – Max Database Size Problem in SQL CE

If you have encounters an SQLCeException error during a LINQ operation for example Union on a very large data set, check the error message. If the error message shows something like “The database file is larger than the configured maximum database size. This setting takes effect on the first concurrent database connection only.“, then congratulation to you, because you just hit the limit.

The Windows Phone team think any application in phone should not use that much memory, but still some of us might hit this limit. For example like me which required to search through 20,000+ data sets with complicated search conditions. The solution to this problem is pretty simple, add a max database size in the connection string to SQL CE. You will need a complete connection string this time instead of just defind the location of your SQL CE database file.

For example, my connection string before this is as follow:

@"isostore:/CardsDatabase.sdf"

Then after adding the max database size parameter and convert it to a complete connection string, it will become like this:

@"Data Source='isostore:/CardsDatabase.sdf';Max Database Size=64;"

The 64 in the connection string mean I wants to increase the max database size to 64 MB instead of the default value. Hope this little information can help.

by Ooi Keng Siang via Ooiks’s Blog

Windows Phone: Preload Existing Data to SQL CE Database

Ever since Windows Phone 7 Mango update, you can run a small database call SQL CE in your Windows Phone app. The SQL CE database in Windows Phone use code first approach instead of the creating table first like what you did in your desktop or web development. This mean you will write the classes for your table in database and all the table will be generated on runtime when you run your app on the phone. Yes, you can’t manually create the table and insert the data into the database during your development.

Although the database has all the necessary functions basic function that you need, but if you have a large set of data that you want to insert into the database then you will have a problem. Generate a large set of data and insert into the database when user run your app for the first time was a bad idea, because it might take too long time and it will create a bad first impression of user when they first use your app. You can use XML instead of SQL CE, but on a large data set, XML performance is much slower than SQL CE.

In my case is, I have a huge XML file for my Windows Phone app (over 40 MB). Accessing to the XML file take me more than 20 second to initialize, it was a pain. Finally I found an alternative way to insert a large volume of data to the database. first I need to create a new Windows Phone project and I program it to convert my huge XML data to the SQL CE database (Phew, it take me 30 minutes to run the conversation). Then, I copy out the database from isolated storage and put it into the project I want. Done, my app can now direct access to the database and save ton of startup time.

For step by step how to do this, please refer to How to: Deploy a Reference Database with a Windows Phone Application

by Ooi Keng Siang via Ooiks’s Blog