Connecting to Microsoft SQL Server Compact 3.5 with Visual Studio

Exclusive offer: get 50% off this eBook here
.NET Compact Framework 3.5 Data Driven Applications

.NET Compact Framework 3.5 Data Driven Applications — Save 50%

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

$29.99    $15.00
by Jayaram Krishnaswamy | July 2010 | Enterprise Articles Microsoft

SQL Server Compact Edition 3.5 can be used to create applications that are useful for a number of business uses such as: Portable applications; Occasionally connected clients and embedded applications and devices. SQL Server Compact differs from other SQL Servers in that there is just one file which can be password protected and features 128-bit file level encryption. It is referential integrity compliant; supports multiple connections; has transactions support with rich data types.

In this tutorial by Jayaram Krishnaswamy, various scenarios where you may need to connect to SQL Server Compact using Visual Studio IDE (both 2008 and 2010) are described in detail. Connecting to SQL Server Compact 4.5 using Visual Studio 2010 Express (free version of Visual Studio) is also described. The connection is the starting point for any database related program and therefore mastering the connection task is crucial to work with the SQL Server Compact.

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

.NET Compact Framework 3.5 Data Driven Applications Build robust and feature-rich mobile data-driven applications with the help of real-world examples
Published: April 2010
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

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

Learning SQL Server 2008 Reporting Services A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008
Published: March 2009
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

(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:


About the Author :


Jayaram Krishnaswamy

Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.

He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.

He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.

Contact Jayaram Krishnaswamy

Books From Packt


Refactoring with Microsoft Visual Studio 2010
Refactoring with Microsoft Visual Studio 2010

Microsoft Silverlight 4 and SharePoint 2010 Integration
Microsoft Silverlight 4 and SharePoint 2010 Integration

Microsoft Silverlight 4 Data and Services Cookbook
Microsoft Silverlight 4 Data and Services Cookbook

.NET Compact Framework 3.5 Data Driven Applications
.NET Compact Framework 3.5 Data Driven Applications

Least Privilege Security for Windows 7, Vista and XP
Least Privilege Security for Windows 7, Vista and XP

Microsoft Dynamics GP 2010 Cookbook
Microsoft Dynamics GP 2010 Cookbook

Software Testing with Visual Studio Team System 2008
Software Testing with Visual Studio Team System 2008

Applied Architecture Patterns on the Microsoft Platform
Applied Architecture Patterns on the Microsoft Platform


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software