Search This Blog

Sunday, February 15, 2009

Develop application using SQL Compact 3.5 SP1 with Visual Studio 2005

Can MS SQL Compact 3.5 SP1 work together with VS 2005? The answer is yes.

We are using Visual Studio 2005 (C#) and wish to create a window desktop application. After some consideration, the decision is for for MS SQL Compact 3.5 SP1. 

To enable me to work on MS SQL Compact 3.5 SP1:
  • Install MS SQL Compact 3.5 SP1 
  • Install MS SQL 2008 Express - to get SQL Server Management Studio to work with SQL Compact for table creation and scripting.
  • Add MS SQL Compact 3.5 SP1 engine to VS 2005 project
  • Connect to MS SQL Compact 3.5 SP1 engine in VS 2005 (C#) project

Install MS SQL Compact 3.5 SP1


The installation is easy and straightforward. I assume there will be no problem facing here.


Install MS SQL 2008 Express - to get SQL Server Management Studio to work with SQL Compact for table creation and scripting

In order to install MS SQL 2008 Express, you need to have below installed prio to that
  • Microsoft .NET Framework 3.5 Service Pack 1
  • Windows Installer 4.5
  • Windows PowerShell 1.0

After finish installation, you can connect to the MS SQL Compact 3.5 SP1 using SSMS. Open SSMS, choose Server type: "SQL Server Compact Edition" as shown below.


After that you can choose to create a new MS SQL Compact database or use the existing one as shown below:


After the connection, you can create table and etc. 

p/s: if you hit error "'' is not a valid login or you do not have permission" during installation, please refer to my previous post.


Add MS SQL Compact 3.5 SP1 engine to VS 2005 project

Open the project for the application, right click on Reference.



Browse on SQL Server Compact 3.5 sp1 engine (System.Data.SqlServerCe.dll) and add as reference. 

My location is in "C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop\System.Data.SqlServerCe.dll"

Connect to MS SQL Compact 3.5 SP1 engine in VS 2005 (C#) project

remember to add reference to your C# code

using System.Data;
using System.Data.SqlServerCe;  // <-- Add this

SqlCeConnection _connection;

string fileName = "mycompactDB.sdf";
           string password = "AbCd1@3$5^7*";

           string connectionString = string.Format(
               "DataSource=\"{0}\"; Password='{1}'", fileName, password);

           _connection = new SqlCeConnection(connectionString);
           _connection.Open();

DONE! Hope this can help.

For other reading, can refer to 

SQL Server Compact 3.5
http://technet.microsoft.com/en-us/sqlserver/bb671088.aspx

SQL Server Compact 3.5 How-to Tutorials
http://msdn.microsoft.com/en-us/sqlserver/bb895908.aspx 


SQLCE 3.5 Database Tutorial
http://dotnetperls.com/Content/SQLCE-Database-Use.aspx

1 comment:

Anonymous said...

If you are just trying to create the sdf file, then you can skip the install of Sql Express 2008 and instead use the built in tools of VS 2005 to create the file. Here are some instructions:

http://arcanecode.com/2007/04/10/creating-a-sql-server-compact-edition-database-using-visual-studio-server-explorer/