MSSQL Compact Edition - Visual Studio generated datasets vs. my own custom code using SqlCommand etc. or some other options available?

Question

Sorry for maybe not so clear title of this question but I'll try to explain it better, so bear with me a little longer :)

We have some C# app on a device with Windows Mobile and it uses MSSQL CE database to store its data (no surprise here, I presume). The problem is that any change in db schema (new columns, different type of data in column, etc.) forces change in VS generated classes working with that db. Maybe I don't know how to use it properly but I have a feeling that these classes are somewhat heavy and maybe my own custom code base on SqlCommand objects would be a little more handy and suitable for maintenance. Also time of initialization and getting data from db is rather long, at least at first time use of particular tables and I don't know if this is an effect of not so optimized (generated) code or that just the way it works. After all, underneath classes generated by Visual Studio are in fact simple SqlCommands at work so it is possible that it's only my NIH syndrome not a real problem.

So, any thoughts on that matter?

And please, don't discard this question as a seeking of opinions not a real solution because from my point of view either I'm wrong and need to be corrected or code generated by VS is bloated and custom tailored bunch of direct commands (or some other way of getting and saving data in db) would be better solution in costs of time of execution and relatively easier maintenance and development.

Answer 1

This is a general problem tbh with maintenance costs due to schema changes

Maybe you could use entity framework as your ORM or maybe linq2sql.

I would be skeptical of using sqlcommand objects to make your life simpler. You will just be moving from spending time updating a schema to spending timew tracking down bugs due to not finding all the necessary changes due to a schema change. It sounds liek hat you are doign is strongly typed ... if so that is really the better way to work. You want your errors to be visible at compile time as it makes life so much easier.

If you cna use an ORM with CE id strongly suggest using one - they make your life so much easier. Options are perhaps Entity framework, linq2sql or nhibernate.

Answer 2

Have a look a http://orm.codeplex.com