Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 21, 2007

Programming SQL Server 2005 Compact Edition with ADO

By DatabaseJournal.com Staff

Microsoft® SQL Server 2005 Compact Edition
By Prashant Dhingra and Trent Swanson
Published by Sams
ISBN-10: 0-672-32922-0
ISBN-13: 978-0-672-32922-7
Buy this book


  • Introducing ADO.NET

  • Building Applications

  • Summary

SQL Server Compact Edition allows you to store data on a desktop or mobile device. A majority of application code consists of retrieving data from a database, updating it, and storing it back to the database. You can use an OLE DB Provider for accessing or manipulating the database or you can use an ADO.Net provider for managed access.

In this chapter, you will learn how to use ADO.NET to connect to the SQL Server Compact Edition database as well as how to read, update, and merge the changes back to the SQL Server Compact Edition database.

Introducing ADO.NET

ADO.NET is the data access component for .NET Framework and .NET Compact Framework. Using the namespace and classes available in ADO.NET, you can access, manipulate, and update the SQL Server Compact Edition database.

ADO.NET provides a standardized mechanism to access a database. It is independent of the data source. Once you have referred to a database namespace, you will use a consistent set of API objects and properties to access the database. If you have used ADO.NET to access an SQL Server database, then you will find accessing SQL Server Compact Edition very similar. Instead of referring to System.Data.SqlClient namespace, you will be referring to System.Data.SqlServerCE namespace.

The data access architecture consists of a physical data store and ADO.NET classes. ADO.NET classes are divided into two categories. The first category communicates with the physical data store and is called Data Provider. The second category represents the in-memory representation of data.

  • The physical data store can be an SQL Server database, XML file, or an SQL Server Compact Edition database.

  • The data provider layer consists of a connection object and a command object to create in-memory representation of data.

  • The uppermost layer is an in-memory representation of data consisting of tables and relationships.

The data provider layer is an abstract layer between the physical store and the in-memory representation on which the application works. Once the data set is created, it does not matter which is the physical data store. This is termed a connectionless or a disconnected solution.


ADO.NET provides its classes, methods, and properties through a set of namespaces. The classes combined together in these namespaces are termed ADO.NET. You should refer to the appropriate namespace to use classes in the respective namespace.


The ADO.NET objective is to provide data-access functionality. System.Data is perhaps the most important namespace among all the namespaces in ADO.NET. This namespace also contains the most important classes related to Data Access, such as, DataSet, DataTable, DataColumn, DataRow, and DataView.


As discussed earlier there are three layers—data source, data provider, and in-memory representation of the data layer. The System.Data.Common namespace consists of classes related to the data provider layer. This namespace has providers for ODBC, OLDB, SQL Server, and so on.


The System.Data.SqlClient is the data provider for SQL Server. The SQL Server data provider exists both in .NET Framework and .NET Compact Framework. For desktop-based applications. use a .NET Framework data provider for SQL Server. For a Smart device-based application, use a .NET Compact Framework data provider for SQL Server.

The System.Data.SqlClient namespace in .NET Compact Framework corresponds to the System.Data.SqlClient namespace in .NET Framework.

The SQL Server Data Provider has classes to access the SQL Server database with managed code using .NET Compact Framework. You can use SQL Server Data Provider to connect, execute the command, and retrieve the data from the SQL Server database.

The System.Data.SqlClient classes in .NET Compact Framework match with the System.Data.SqlClient classes in .NET Framework except for a few limitations including:

  • Connection pooling is not supported.

  • Distributed transactions are not supported.

  • Encrypted connections to SQL Server are not supported.

  • Connection string properties related to connection pooling, encryption, network library, and so on are not supported.

  • Windows Authentication should be used to connect to SQL Server from Smart devices. SQL Server authentication to connect to SQL Server from Smart device is not supported.

  • SqlClientPermission and SqlClientPermissionAttribute classes are not supported.

For complete details you should refer to the System.Data.SqlClient documentation.

The System.Data.SqlClient namespace consists of a data provider for SQL Server. The SQL Server Data Provider has classes to access the SQL Server database. You can use the SQL Server Data Provider to access the database to fill data into the DataSet.


The System.Data.SqlServerCe namespace is the data provider for SQL Server Compact Edition. The SQL Server Compact Edition Data Provider contains classes that are specific to the SQL Server Compact Edition data source. You should add the reference to SqlServerCe namespace in the program using SQL Server Compact Edition classes as shown in Figure 7.1.

Figure 7.1
ADO.NET Object Model

Listing 7.1 SqlServerCe Namespace C#

Using System.Data.SqlServerCe

Listing 7.1 SqlServerCe Namespace VB.NET

Imports System.Data.SqlServerCe

Introducing the ADO.NET Object Model

This section starts with a simplified model of ADO.NET. Figure 7.1 depicts the primary objects in ADO.NET.

The classes are divided into two categories:

  • Data provider that communicates with the SQL Server Compact Edition data source.

  • DataSets that store the memory representation of data.

Understanding Data Providers

The data provider category of classes interacts with a physical data store and provides the data to a DataSet category of classes. The data provider category of classes interacts directly with a physical store. This is the reason a data provider is specific to a data source. For SQL Server Compact Edition, .NET Framework and .NET Compact Framework provide two data providers:

  • OLE DB Data Provider

  • SQL Server Compact Edition Data Provider

As shown in Figure 7.1, a connection object connects to a data source. The Data Consumer can call the Execute Reader method to execute the Data Reader against the connect object. You can execute the command object independently or use it with Data Adapter. Data Adapter acts like glue between the connection object and DataSet.

Connection Object

The connection object represents the unique connection to the data source. The SqlCeConnection object manages the connection to the physical data store. The connection string that determines the path of the SQL Server Compact Edition database is the property of the connection object. Listing 7.2 describes the usage of Open and Close methods to open and close connections to the database.

The connection to the database does not close if the SqlCeConnection object goes out of scope. You must explicitly close the connection.

Listing 7.2 Connection Object C#

SqlCeConnection myConnection = null;
myConnection = new SqlCeConnection("Data Source=\\Mobile\\Northwind.sdf;");

Listing 7.2 Connection Object VB.NET

Dim myConn As SqlCeConnection = New SqlCeConnection("DataSource=\Mobile\Northwind.sdf")

NOTE - SQL Server Compact Edition 3.x supports multiple simultaneous connections to a database.

Table 7.1 lists the connection object methods.

Table 7.1 Commonly Used Connection Object Methods


Method Description


Begins a database transaction.


Changes the current database.


Closes a database connection


Creates an SqlCeCommand object.


Releases the resources used by the connection object.


Compares two connection objects and returns TRUE if objects are equal.


Serves as a hash function for a particular type.


Returns schema information for its data source.


Gets type of current instances.


Opens a database connection.


Compares two connection objects and returns TRUE if objects are the same.


Returns a string that represents the current object.

Table 7.2 lists the connection object properties.

Table 7.2 Commonly Used Connection Object Properties


Property Description


Gets or sets the string used to open a database.


Gets the amount of time to wait while attempting to open a connection before generating an error.


Gets the name of the database.


Gets the physical file name including path of data source.


Returns SQL Server Compact Edition version number.


Gets or sets the ISite of a component.


Gets the current state of a connection.

In the connection string, you can specify the location and size of the temporary database. To specify the temporary database, the temp file directory and temp file max size properties must be specified in the connection string.

Command Object

The SQLCeCommand object allows you to execute an SQL command against the SQL Server Compact Edition database. A data command object is simply an SQL command that is executed against a connection object. Like most of the object, the data command object can be created at design time or at runtime in code. Listing 7.3 describes how a command object can be created.

NOTE - Multiple commands can share the same connection object.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM