Connecting to Microsoft SQL Server Compact 3.5 with Visual Studio

Build robust and feature-rich mobile data-driven applications with the help of real-world examples

(For more resources on Microsoft, see here.)

If you are familiar with SQL Server you already know much of SQL Server Compact. It can be administered from SSMS and, using SQL Syntax and ADO.NET technology you can be immediately productive with SQL Server Compact.It is free to download (also free to deploy and redistribute) and comes in the form of just one code-free file. Its small foot print makes it easily deployable to a variety of device sizes and requires no administration. It also supports a subset of T-SQL and a rich set of data types. It can be used in creating desktop/web applications using Visual Studio 2008 and Visual Studio 2010. It also comes with a sample Northwind database.

Download details

Microsoft SQL Server Compact 3.5 may be downloaded from this site here. Make sure you download detailed features of this program from the same site. Also several bugs have been fixed in the program as detailed in the two SP's. Link to the latest service pack SP2 is here. By applying SP2 the installed version on the machine is upgraded to the latest version.

Connecting to SQL Server Compact from Windows and Web projects

You can use the Server Explorer in Visual Studio to drag and drop objects from SQL Server Compact provided you add a connection to the SQL Server Compact. In fact, in Visual Studio 2008 IDE you can configure a data connection without even starting a project from the View menu as shown here.

When you click Add Connection... the following window will be displayed.

This brings up the Add Connection dialog shown here.

Click Change... to choose the correct data source for SQL Server Compact. The default is SQL Server client.

The Change Data Source window is displayed as shown.

Highlight Microsoft SQL Server Compact 3.5 and click OK.

You are returned to Add Connection where you can browse or create a database or, choose also from a ActiveSync connected device such as a Smart phone which has a SQL Server Compact for devices installed. Presently connect to one on the computer (My Computer default option)-the sample database Northwind.

Click Browse....

The Select SQL Server Compact 3.5 Database File dialog opens where your sample database Northwind is displayed as shown.

Click Open. The database file is entered in the Add Connection dialogue. You may test the connection. You should get a Test connection succeeded message from Microsoft Visual Studio.

Click OK. The Northwind.sdf file is displayed as a tree with Tables and View as shown in the next figure.

Right click Northwind.sdf in the Server Explorer above and click Properties drop-down menu item.

You will see the connection string for this conneciton as shown here.

(For more resources on Microsoft, see here.)

Connecting to SQL Server Compact using code

Using code to connect to SQL Server Compact provides you with more fine-grained control over what you want to achieve. The basis for coding SQL Server Compact lies in the namespace that has the required class(s). SQLServerCE is a non-inheritable class in the System.Data.SqlServerCe namespace as shown in the Object Browser.

It is relatively easy to connect to SQL Server Compact if proper reference is made to this class as shown in the following code. The connection to SQL Server Compact is opened from a button click on a form. The connection's state property is displayed with 1 for connection open and 0 for connection closed.

Imports System.Data.SqlServerCe
Public Class CodeCompact
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim compactStr As String = _
"Data Source=C:\Program Files\Microsoft SQL Server Compact Edition" & _
"\v3.5\Samples\Northwind.sdf"
Dim conCompact As New SqlCeConnection
conCompact.ConnectionString = compactStr
conCompact.Open()
MessageBox.Show(conCompact.State)
conCompact.Close()
MessageBox.Show(conCompact.State)

End Sub
End Class

Connecting to SQL Server Compact using Client and Server APIs

As you might have noticed the connection to SQL Server Compact did not involve any other .NET providers such as ODBC or OLE DB. One could test connecting to SQL Server Compact using ADODB objects through other interfaces. You can get connected to SQL Server Compact from the older ADODB objects as shown in the following.

At first you need to add a reference to your project by right clicking project as shown in the next figure. Here CompactSQL is a new Windows Forms Application in Visual Studio 2008 program.

This opens the Add Reference dialog as shown.

With this you will now be able to connect to SQL Server Compact using the following code. Make sure you have the Imports ADODB statement in place. The code shows what happens when the button on the form is clicked (you could also have the code in the Form's load event.

Imports ADODB
Public Class SrvrCompact

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim conAdodb As New ADODB.Connection
Dim rsAdodb As New ADODB.Recordset
Dim cmd As New ADODB.Command
conAdodb.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;" & _
"Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.5" & _
"\Samples\Northwind.sdf"

Try
conAdodb.Open()
MsgBox(conAdodb.State)
conAdodb.Close()
MsgBox(conAdodb.State)
Catch ex As OleDb.OleDbException
MsgBox(ex.ErrorCode)
End Try
End Sub
End Class

Connecting to SQL Server Compact 3.5 from Visual Studio 2010 Express

Visual Studio 2010 Express does not contain references to System.Data.SqlServerCe nor does the data connection wizards support the Microsoft SQL Server Compact 3.5 data source shown in the next figure.

The work around if you are trying to connect to SQL Server Compact 3.5 using Visual Studio 2010 Express is to fall back and use the ActiveX Data Objects library as in the above code.

Regarding Server APIs connection to SQL Server Compact using SQL Server Management Objects (SMO) is not supported for this version.

(For more resources on Microsoft, see here.)

Connecting to SQL Server Compact from BIDS

Business Intelligence projects that use Visual Studio 2008 SP1 have data connection manager wizards that facilitate connecting to a variety of databases, objects etc. You can establish a data connection to SQL Server Compact on your computer using BIDS by using the following steps.

Create a SQL Server Integration Services project from Visual Studio 2008(or BIDS).

With this you will create a project folder shown in the next figure.

Right click Data Sources in the project folder to display drop-down as shown.

Click New Data Source... menu item. This opens the Data Source Wizard's Welcome to the Data Source Wizard page. Read information on this page.

Click Next >.

This opens Select how to define the connection page.

Click the New... button on the right.

The Connection Manager page is displayed. Click the handle for the Provider and pick the Microsoft SQL Server Compact edition Client Data Provider and click OK.

The Provider gets added to the Connection Manager as shown.

Click Browse.... The search file finds the sample in the down load as shown. You could also create a database if you wanted to in this window. After you highlight Northwind, click Open. The database gets added.

Click Test Connection button. You should get a Test Connection succeeded message. Click OK followed by Click Finish on the Data Source Wizard page.

The Completing the wizard page is displayed as shown.

Click Finish.

You will be returned to the VS 2008 IDE where you see the database in Datasource (Northwind.ds) node and it becomes a part of the Business Intelligence project as shown.

Summary

The article described in detail connecting to SQL Server Compact 3.5 with several technologies both new and old. Connecting to SQL Server Compact in SQL Server Management Studio is discussed in detail. Connecting to SQL Server Compact from Business Intelligence Development Studio; connecting from Windows and Web projects both during design as well as at run time using code; are also described.


Further resources on this subject:


Books to Consider

comments powered by Disqus