Wednesday, August 24, 2011

Using SQL Server Compact 4.0 Local database with visual studio 2010

If we consider using Embedded database and the environment is Microsoft development platform, there are couple of choices.

  • SQL Server compact 4.0
  • SQLite 3.0
  • Firebird
  • Vista DB

There could be more options for embedded database but considering embedded database those four is most popular in the web. 

I have posted few blog post on how to use SQLite with visual studio to gain good amount of development ease. In this post we are going to see how to use SQL Server Compact 4.0 with Visual studio 2010. SQL Server compact 4.0 is a file based database and has native visual studio 2010 support. It’s a simple way of data store and off Couse pretty handy in few development scenario.

Adding the database is pretty simple. Right click on the project folder and select add new item, from the add new item wizard select Data category, and you would find the item template named “SQL Server Compact 4.0 Local Database”, which is your target template. Now select the item and give a suitable name. Now click add.

image

If you are trying to add the database file in a asp.net project you would be presented with a message box dialog like bellow. And you should select “Yes”. By Asp.net convention any data related stuff should go to App_Data Folder which is a special folder for asp.net project.

image

Now after that you should find a file with the same name of your given database name in app_data folder and as well as a data connection would be added to the server explorer. That’s it you are good to go.

image

Now then if we try to use the same database with WPF Project you would see the warning like Bellow screen shot. It simply says that you can not use this version of database with dataset designer. I mean if you thinking if using Type Dataset and data adapter to fill your data, you shouldn’t probably use this. Visual studio 2010 dataset designer does not have native support for this.

 

image

In this case you should use local Database template rather that version 4.0. And after selecting the local database template click add, a data source configuration wizard will be lunched the finish the steps of the wizard you will get the data file added in you project.

image

In this post we have see how we can add the SQL Server Compact4.0 Local Database in visual studio 2010. In my next post we would see how we can design a effective database quickly using visual studio or perhaps SQL Server management studio.

No comments:

Post a Comment