| To use SQL Server CE data, your application uses ADOCE, just as Pocket Access does. The major difference in consuming SQL Server CE data versus Pocket Access data is in the ConnectionString value structure. Here is an example of opening an ADOCE.Connection object using SQL Server CE: Dim objConnection As ADOCE.Connection Set objConnection = CreateObject("ADOCE.Connection.3.1") objConnection.ConnectionString = _ "Provider=Microsoft.SQLServer.OLEDB.CE.1.0;" _ & "Data Source=\Program Files\My Program\MySSCEDb.sdf" objConnection.Open Notice the difference from Pocket Accessthe Provider element of the ConnectionString value is required for SQL server CE. Updating SQL Server CE Recordsets One of the most surprising limitations of SQL Server CE is that recordsets created from a SQL statement aren't updatable. When I first started working with SQL Server CE, I found this mind-boggling. The workaround isn't incredibly familiar to Visual Basic programmers, but far too familiar to former FoxPro programmers, so it came as second nature to me and will to you soon. Let's investigate the workaround necessary to mimic SELECTing a single record and updating it (see Listing 8.1). What is accomplished here is similar to that used with ActiveX Data Objects (from desktop and server PCs). Listing 8.1 Typical UPDATE in Place for Non-SQL Server CE Recordset Dim objConnection As New ADODB.Connection Dim objRecordset As New ADODB.Recordset objConnection.ConnectionString = _ "Provider=SQLOLEDB.1;Password=;" _ & "User ID=sa;Initial Catalog=CD;Data Source=MyServer" objConnection.Open objRecordset.Open _ "SELECT * FROM MyTable WHERE MyPrimaryKey = 1001", _ objConnection, _ adOpenDynamic, _ adLockOptimistic objRecordset.Fields("MyField").Value = "MyValue" objRecordset.Update You may remember in Chapter 7, "Storing and Retrieving Data with ActiveX Data Objects CE," that areas of the application used the table name instead of a SQL statement as the Source parameter to the Open method of the ADOCE.Recordset object. This table name was paired with adCmdTableDirect enumeration as the Options parameter. This may have seemed like a difficult or unnecessary methodology with Pocket Access, but is a great primer to creating updateable recordsets with SQL Server CE. Listing 8.2 reviews creating an updateable recordset using this approach. Listing 8.2 Opening a SQL Server CE for an UPDATE in Place Dim objADOCEConnection As ADOCE.Connection Dim objRecordset As ADOCE.Recordset Set objADOCEConnection = CreateObject("ADOCE.Connection.3.1") Set objRecordset = CreateObject("ADOCE.Recordset.3.1") objADOCEConnection.ConnectionString = _ "Provider=Microsoft.SQLServer.OLEDB.CE.1.0;" _ & "Data Source=\CD.sdf" objRecordset.Open "MyTable", _ objADOCEConnection, adOpenDynamic, adLockOptimistic Now that you have an updateable recordset, let's investigate how you can do an in-place update (see Listing 8.3). Listing 8.3 Creating an Updateable Recordset from SQL Server CE Data Dim objADOCEConnection As ADOCE.Connection Dim objRecordset As ADOCE.Recordset Set objADOCEConnection = CreateObject("ADOCE.Connection.3.1") Set objRecordset = CreateObject("ADOCE.Recordset.3.1") objADOCEConnection.ConnectionString = _ "Provider=Microsoft.SQLServer.OLEDB.CE.1.0;" _ & "Data Source=\CD.sdf" objRecordset.Open "MyTable", _ objADOCEConnection, adOpenDynamic, adLockOptimistic objRecordset.Index = "MyPrimaryKeyIndex" objRecordset.Seek 1001 objRecordset.Fields("MyField").Value = "MyValue" objRecordset.Update After you have an updateable recordset, the Index property is set to the name of index from the data definition. In other words, this is the name of the index created on the primary key field. Next, the Seek method is called with the primary value of the record to be updated. Then, things return to normal with the updates to the record and the Update method is called. SQL Server CE Merge Replication and Remote Data Access Whether your application uses SQL Server CE replication data, RDA data or data defined directly on the device, the data is stored in a file with the default extension of .sdf (for example, \Program Files\My Program\MySSCEDb.sdf). All types of SQL Server CE data (replication data, RDA data, and data defined on the device) can coexist in a single database. Distinct databases are maintained in separate files. SQL Server CE Merge Replication SQL Server CE merge replication allows SQL Server CE to subscribe to Microsoft SQL Server 2000 publications ; replication isn't compatible with previous versions of Microsoft SQL Server. Publications are a collection of articles that reside within a SQL server. Articles are simply tables that can have both horizontal and vertical filtering applied. Horizontal filtering is the process of filtering rows by static or dynamic criteria. Vertical filtering is the process of defining a subset of available table columns. When defining the article, you specify which columns to include in the replication. You can filter rows in an article based on static or dynamic attributes. In the sample application, dynamic filtering is accomplished by using the HOST_NAME() value to filter rows to the current subscriber. This is a potential strategy for applications such as an order entry system where the salesperson should only subscribe to customer records that belong to him or her. Also, you can filter records in subsequent articles based on filtered rows in other articles. In other words, if a horizontal filter is applied to one article, subsequent articles can be filtered by using a JOIN condition on the originally filtered article. In the salesperson example, a dynamic filter is applied to a customer table and then propagated to an orders or sales table with a JOIN from customers to orders and a JOIN from customers to sales leads. At the same time horizontal filters are being applied to articles, vertical filters can be applied to the articles. When defining the article, you define which column(s) are available or not available. Both horizontal and vertical filtering can be an important strategy to limit the amount of data being exchanged with the enterprise SQL Server. Limiting the data can be an important aspect to a Windows CE solution, ensuring effective use of limited memory on the device and minimizing time to exchange data via a slower or less reliable wireless connection. Using replication with SQL Server CE allows for automatic identity column management. SQL Server CE replication provides an easy integration into those existing SQL Server solutions using identity columns. To manage identity columns, you configure "blocks" of keys for each article to be downloaded to the subscriber device. To use merge replication, you must specify articles for a SQL Server CE publication. Merge replication allows a record updated in two locations (two mobile devices or a mobile device and the server) to be merged back together. Microsoft SQL Server merge replication provides a default merge handler, other various merge handlers (see Microsoft SQL Server documentation), and the capability to write a custom COM merge handler. Tables published as articles for merge replication have certain requirements: -
Any identity columns must be set to Not For Replication (that choice of words seems completely counterintuitive to me, too). -
When completing publication of a table as an article, a column named ROWGUID will be added of type uniqueidentifier if there's currently no column of type uniqueidentifier (there can be only one column of this type per table). This column is a 128-bit data type uniquely identifying a record, even across tables and databases. These database changes shouldn't be taken lightly because any application(s) currently accessing the data may not be compatible with these changes. (Remember all those warnings from senior-level developers and DBAs to not use SELECT * FROM table ?) SQL Server CE Remote Data Access SQL Server CE remote data access (RDA) allows for direct SQL queries to Microsoft SQL Server databases, including versions 6.5, 7.0, and 2000. If your solution needs to use version 6.5 or 7.0, remote data access is the only choice in which to integrate SQL Server CE. With SQL Server CE RDA, a standard SQL query is sent to the server and a table is created in the SQL Server CE database from the resultset. The table created from this resultset can be configured to allow updates to be sent back to the server or to essentially be read-only using what is known as the tracking option. A key difference between replication and RDA is that your application doesn't get automatic primary key management features with RDA. But, RDA does allow a higher degree of management and flexibility from the client side. Replication must be defined and managed from the server, yet RDA allows the management to be dynamically defined and configured from the client side. Also, you can use RDA to send data definition language (DDL) statements directly to the server to be executed on the server.  | 
