Programming SQL Server 2005 Compact Edition with ADO
August 21, 2007
IN THIS CHAPTER
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.
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 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 layersdata 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:
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.
Listing 7.1 SqlServerCe Namespace C#
Listing 7.1 SqlServerCe Namespace VB.NET
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:
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:
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.
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()
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.
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.