Programming SQL Server 2005 Compact Edition with ADO

August 21, 2007

IN THIS CHAPTER

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

Namespace

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.

System.Data

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.

System.Data.Common

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.

System.Data.SqlClient

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.

System.Data.SqlServerCe

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;");
myConnection.Open();
..
..
myConnection.Close();

Listing 7.2 Connection Object VB.NET

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

---

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

Table 7.2 lists the connection object properties.

Table 7.2 Commonly Used Connection Object Properties

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.

---