Generate Script & Export Data From SQL CE (Compact Edition) 3.5

Several months ago I migrated my family financial and expense information from excel file to SQL CE 3.5.
SQL CE is indeed good enough for data entry purpose, but after a while, I felt that it is not good enough for reporting purpose.
So I decided that it's better to use the SQL Server Express instead.

Now the first problem that I had : How to migrate the tables along with the data from SQL CE 3.5 to SQL Server Express?

I have been googling around for a while, but haven't really found a good one for this, which make me realized that there are more ways to get the data from SQL Server to SQL CE but not the other way around duhh :|

So later, I decided to code it myself ;)

When browsing around the net, I found about Information Schema View that is available in SQL Server, and fortunately it is also available in the SQL CE 3.5 ;) (before I found out about the view, I was using Linq Attributes to generate the table creation ouccch)
So now by using the view, I generate the script for the SQL CE 3.5 database file (.sdf).

In summary, the code will :
1. Generate Table Creation Script
2. Generate Insert Statement for the data in all of the tables
3. Generate script to assign the Primary Keys for the tables
4. Generate script to assign the foreign keys / references for the tables

** Update 5. Generate script to assign Indexes for the tables

6. Save the script into the output file

So here's a little peek of how to use the tool, I'm using Northwind.sdf file in the example below :
There are 2 parameters to pass in ;
1. Connection String to the sdf file
2. Output File Name for the script

Note : If you get any access denied when trying to open the Northwind.sdf file, here is why and the solution.

After the Northwind.sql file has been generated :
1. Create the SQL Server database file (.mdf)
2. Open the Northwind.sql script into your SQL Server Management Studio or Visual Studio (Since I'm currently using SQL Epxress 2008 CTP, there is no management studio yet for now) and just execute them, if everything's ok (finger crossed), you will see this kind of message below and you're done :)

Please note :
1. The tool will not generate the database for you, it will only generate the scripts to execute
2. Currently I purposely convert nchar and nvarchar data type to varchar in the table creation script. I don't see any purpose of using them unless if storing unicode characters in the table.
3. I haven't handled Image / BLOB data type for generating data (Insert Statement).

Ok now, stop talking and just give me the download link :P

Here is the executable file :
**Removed: Please get the files from CodePlex site instead.

And here is the code, if you want to see what's running inside :
**Removed: Please get the files from CodePlex site instead.

**Updates: Erik EJ has provided the newer version of this at CodePlex website, I really recommend that you get the files from there. Huge thanks to Erik to make this utility tool better and to make it possible for others to contribute

Hope it helps ;)
and do leave a comment if you have any feedback or find any bugs, Thanks ;)

ASP.Net 3.5 Extension Dynamic Data, SQL Compact Edition 3.5, Linq, IIS7

It's been a while since my last blog, I went back to Indonesia visiting our families while celebrating Chinese New Year 2008 ;) It's never enough for holiday and i got the side effect of post holiday syndrome (need more holidays) :P Btw, happy chinese new year for you guyz, wish all good things happen this year of 2008 ;)

Back into the reality and the subject ;)

I have been tracking all my family financial and expense information in an excel file until last december 2007 where I thought that it will be great if i can create some .Net apps for this, while i can learn some new stuffs as well.
At that time, SQL Compact Edition 3.5 just came out so i downloaded and installed it, i got some problems with it but finally managed to use it and migrated all my data from excel file to the SQL CE (created a small app for this).

After the database is done, the next thing to do is the User Interface app.
I read Scott Gu's blog post about the ASP.Net Dynamic Data some time ago which is quite perfect for me since i only need a simple app to manage the data. So i downloaded the extension and watched the introduction video. You have to check it out to see what it can do.

Steps :

1. Install the ASP.Net 3.5 extension
2. Create a new project using a new template project for website : Dynamic Data Website, it will generate all the files needed for the site.
3. Since i'm using SQL CE, the linq to sql file has to be generated manually using SqlMetal.exe (use VS 2008 Command prompt and type sqlmetal.exe to see the user guide). Include the generated dbml files to the project
   
4. Open the [dbml file name].designer.cs file and add a parameterless constructor with the database connection string for the sdf file or get it from web.config file :
   public MyData() : base (@"Data Source='D:\MyDatabase.sdf';Password='mypassword'")
   {
   OnCreated();
   }
   
5. Since by default SQL CE is not intended for web application, you will need to add a global.ascx file and add this line below in the application_start to get it working
   AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
6. Compile the app and run ;)
   

The result is quite amazing, it displays all the tables and their relationships in the database (generated by sqlmetal) and it provides most of the functionalities such as edit, delete, insert that I need :D

When deploying the app into IIS 7, i encountered some problems and found the solutions below :
1. Configuring ASP.Net 3.5 into IIS7
2. Precompilation problem with Dynamic Data

The name "Dynamic Data" said it all, it is able to display your data dynamically no matter how many tables with different structures by using only few template files and also very extensible for your own customization.

Give it a try and see for your self ;)