Develop Application using SQL Compact 3.5 SP1 with Visual Studio 2005

Introduction

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

We download the MS SQL Compact 3.5 sp1 from http://www.microsoft.com/sqlserver/2005/en/us/compact-downloads.aspx

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

For more helpful information, you can refer to http://www.symantec.com/community/article/5920/install-and-configure-sql-server-2008-express

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 tips.

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 thisSqlCeConnection _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.

References

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