| The following sections discuss the process to convert the CD Library application from Chapter 7 into an enterprise solution that uses SQL Server replication. Creating the CD Library Database on the Server Before you can use SQL Server CE replication, you must create a publication on the server to subscribe to. You can create a SQL Server publication programmatically or through the Enterprise Manager. Obviously, before you can create a publication, you need to have a database. Figure 8.3 represents a model of the CD library data before the publishing as a replication. Listing 8.4 is the T-SQL that creates this database on the server. Figure 8.3. An entity relation diagram for CD library database before publication takes place.  Listing 8.4 CreateCDDatabase.sql: Creating the CD Database on the Server CREATE TABLE [dbo].[Artist] ([ArtistId] [int] IDENTITY (1, 1) NOT NULL , [ArtistName] [varchar] (64) , [DefaultGenre] [int] NULL) GO CREATE TABLE [dbo].[CD] ([CDId] [int] IDENTITY (1, 1) NOT NULL , [ArtistId] [int] NULL , [CDName] [varchar] (64) , [YearOfRelease] [int] NULL , [Price] [float] NULL , [GenreId] [int] NULL , [UserId] [int] NULL) GO CREATE TABLE [dbo].[Genre] ([GenreID] [int] IDENTITY (1, 1) NOT NULL , [GenreName] [varchar] (64)) GO CREATE TABLE [dbo].[Users] ([UserId] [int] IDENTITY (1, 1) NOT NULL , [UserName] [char] (20)) GO ALTER TABLE [dbo].[Artist] WITH NOCHECK ADD CONSTRAINT [PK_Artist] PRIMARY KEY CLUSTERED ([ArtistId]) ON [PRIMARY] GO ALTER TABLE [dbo].[CD] WITH NOCHECK ADD CONSTRAINT [PK_CD] PRIMARY KEY CLUSTERED ([CDId]) ON [PRIMARY] GO ALTER TABLE [dbo].[Genre] WITH NOCHECK ADD CONSTRAINT [PK_Genre] PRIMARY KEY CLUSTERED ([GenreID]) ON [PRIMARY] GO ALTER TABLE [dbo].[Users] WITH NOCHECK ADD CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([UserId]) ON [PRIMARY] GO ALTER TABLE [dbo].[Artist] ADD CONSTRAINT [FK_Artist_Genre] FOREIGN KEY ([DefaultGenre]) REFERENCES [dbo].[Genre] ([GenreID]) GO ALTER TABLE [dbo].[CD] ADD CONSTRAINT [FK_CD_Artist] FOREIGN KEY ([ArtistId]) REFERENCES [dbo].[Artist] ([ArtistId]), CONSTRAINT [FK_CD_Genre] FOREIGN KEY ([GenreId]) REFERENCES [dbo].[Genre] ([GenreID]), CONSTRAINT [FK_CD_Users] FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users] ([UserId]) GO After you complete your data definitions, create some data in the tables. Listing 8.5 shows the T-SQL to insert some sample data. Using ISQL/W from the SQL Server Tools, open MakeData.sql in ISQL/W and execute it. Listing 8.5 MakeData.sql: Creating Sample Data in the CD Database SET IDENTITY_INSERT Users ON INSERT INTO Users (UserId, UserName) VALUES (1, 'mobileuser1') INSERT INTO Users (UserId, UserName) VALUES (2, 'mobileuser2') INSERT INTO Users (UserId, UserName) VALUES (3, 'mobileuser3') SET IDENTITY_INSERT Users OFF GO SET IDENTITY_INSERT Genre ON INSERT INTO Genre (GenreID, GenreName) VALUES (1, 'Pop') INSERT INTO Genre (GenreID, GenreName) VALUES (2, 'Rock') INSERT INTO Genre (GenreID, GenreName) VALUES (3, 'Bluegrass') SET IDENTITY_INSERT Genre OFF GO SET IDENTITY_INSERT Artist ON INSERT INTO Artist (ArtistId, ArtistName, DefaultGenre) VALUES (1, 'Britney Spears', 1) INSERT INTO Artist (ArtistId, ArtistName, DefaultGenre) VALUES (2, 'Dave Matthews Band', 2) INSERT INTO Artist (ArtistId, ArtistName, DefaultGenre) VALUES (3, 'Allison Krauss', 3) SET IDENTITY_INSERT Artist OFF GO SET IDENTITY_INSERT CD ON INSERT INTO CD (CDId, ArtistId, CDName, YearOfRelease, Price, GenreId, UserId) VALUES (1, 1, 'Oops...', 2000, 15.99, 1, 1) INSERT INTO CD (CDId, ArtistId, CDName, YearOfRelease, Price, GenreId, UserId) VALUES (2, 1, 'Best Of Britney', 2001, 18.99, 1, 1) INSERT INTO CD (CDId, ArtistId, CDName, YearOfRelease, Price, GenreId, UserId) VALUES (3, 2, 'Under the Table and Dreaming', 1998, 10.99, 2, 3) INSERT INTO CD (CDId, ArtistId, CDName, YearOfRelease, Price, GenreId, UserId) VALUES (4, 2, 'Everyday', 2001, 15.99, 2, 2) INSERT INTO CD (CDId, ArtistId, CDName, YearOfRelease, Price, GenreId, UserId) VALUES (5, 3, 'Union Station', 1996, 12.99, 3, 1) INSERT INTO CD (CDId, ArtistId, CDName, YearOfRelease, Price, GenreId, UserId) VALUES (6, 3, 'Best Of...', 1999, 13.99, 3, 1) SET IDENTITY_INSERT CD OFF GO Creating a Publication for Replication to a Windows CE Device Now that you have the CD database created, you can create the publication for replication. This publication uses horizontal filtering to filter the User table with the HOST_NAME() function. The CD table records are then horizontally filtered by using JOIN to join the User table to the CD table. To invoke the Create Publication Wizard, right-click the CD database in the Enterprise Manager and choose New, Publication. When the wizard opens (see Figure 8.4), leave Show Advanced Options in this Wizard unchecked and click Next because you won't be modifying any advanced options in this publication. Figure 8.4. Creating a new publication is accomplished through the Create Publication Wizard.  Next, select the CD database in the Choose Publication Database step (see Figure 8.5) and click Next. Figure 8.5. Selecting the database from which the publication originates.  After selecting the correct database, you need to select the correct publication type. Select the only publication compatible with SQL Server CE, Merge Publication, as shown in Figure 8.6. Figure 8.6. The only publication type compatible with Windows CE is Merge Publication.  Next, specify what types of subscribers will subscribe to the publication. You can select any subscriber type, but you need to select Devices Running SQL Server CE as shown in Figure 8.7. Figure 8.7. You can choose the other options without adversely effecting the publication for SQL Server CE.  In the Specify Articles step, include all the tables by selecting them. If no tables are displayed, ensure that Show Unpublished Objects is selected. Your settings should resemble Figure 8.8. Figure 8.8. Specify the articles to be published in the publication. In this sample, all tables are published as articles.  An identity range needs to be defined for each article whose data will be modifiable on the subscriber. This identity range is a "block" of available primary keys for new records. Managing the identity range allows a subscriber to "block out" a range of identity column values each time it synchronizes with the publication. Identity ranges are managed on an article basis. To manage the identity range, click the ellipsis () right of the article name to open an article's properties dialog box. To create an identity range, display the Identity Range tab (see Figure 8.9). Select Automatically Assign and Maintain a Unique Identity Range for Each Subscription to enable the rest of the tab's controls. The defaults are an identity range size of 100 at both the publisher and the subscriber. This allows the subscriber to attempt 100 inserts for the article. Also, a default threshold of 80 percent is set for the publisher to start a new range when 80 percent of the range is exhausted. You can adjust these values to meet the needs of your solution. When the identity range for an article is exhausted on the subscriber, no more records may be added to the article until the subscriber syncs with the publisher. Figure 8.9. Managing an identity range for an article is specified under the Identity Range tab of the article's properties.  You need to create an identity range for each article: Artist, Genre, and CD. No identity range is needed for the Users table because the application won't be modifying records in that table. After going to the next step, the wizard informs you of any problems with the database for the type of publication you are creating. In this example, there are two problems: The wizard displays these problems, how it will fix them, and the implications of the fixes (see Figure 8.10). Figure 8.10. The Create Publication Wizard analyzes all publication articles for problems, resolves each problem, and explains implications of its resolution(s).  The wizard now prompts for the publication's name (see Figure 8.11). For this example, name the CDPublication and click Next. Figure 8.11. Naming the publication.  To do some filtering, choose Yes in the customize properties step (see Figure 8.12) and click Next. Because subscriptions from SQL Server CE are always anonymous, you need a method to inform the server which user is subscribing or synching. Using the HostName property on the SSCE.Replication object provides the server with the ability to query that value using the HOST_NAME() function. The publication is filtered to include only the Users table record (as created in MakeData.sql from Listing 8.5) with the UserName field matching the return value from the HOST_NAME() function. Also, the CD records are filtered by JOINing the filtered Users table to the CD table. Figure 8.12. The wizard has collected enough information to create the publication, but you can define further information before the publication is created.  To filter the articles, the publication needs to have a horizontal filtering set, as shown in Figure 8.13. Figure 8.13. To filter article rows for subscribers, use horizontal filtering. Vertical filtering applied to articles eliminates columns from the article.  After specifying either type of filtering, the wizard asks whether the filtering will be dynamic or static. The sample application uses dynamic filtering to minimize network bandwidth, so select Yes, Enable Dynamic Filters (see Figure 8.14). Figure 8.14. The sample application uses dynamic filtering.  The sample application sets the HostName property to the current user allowing the HOST_NAME() function to return the value. This value is compared to the Users.UserName column to filter the Users table to a single record. This record is then JOINed to the CD table to allow only the CD records for that user to be subscribed to. Select the Users table in the Table to Filter dropdown and add UserName = HOST_NAME() to the WHERE clause in the text box, as shown in Figure 8.15. Click Next. Figure 8.15. Filtering the Users article to the current user by using the HOST_NAME() function.  In the Filter Table Rows step, you can specify more filtering to take place on articles. The filter can be specified by adding a WHERE clause to any table (see Figure 8.16). This is accomplished by clicking the ellipsis next to the article name in the top grid. The second type of row filtering that you can apply to the articles is a JOIN condition specified in the lower grid. Figure 8.16. The Create Publication Wizard allows for horizontal filtering of any articles using a WHERE clause (top grid) or a JOIN clause (lower grid).  The sample application needs to filter the CD article by the UserId foreign key. This is accomplished by adding a JOIN condition to the CD article in the lower grid. As shown in Figure 8.16, choose the Users table (dbo.Users) in the lower grid's Filtered Table column. Next, in the Table to Filter column, choose the CD table (dbo.CD). The JOIN Filter Clause column then informs you to click the ellipsis to open a dialog box, as shown in Figure 8.17. Figure 8.17. The Specify Join dialog allows you to create the JOIN clause between a filtered table and any other table in the publication.  Enter the JOIN condition between the Users table and the CD table as [Users].[UserId] = [CD].[UserId] This causes the publication to publish only CD records that belong to the subscriber. After completing this step, your wizard should look like Figure 8.18. Click Next. Figure 8.18. The CD table is filtered to only publish records that match the User by specifying a JOIN to the filtered Users table.  The next step asks if you want to validate the subscriber information. Because SQL Server CE doesn't support this validation, choose No (see Figure 8.19). Figure 8.19. You don't need to specify validation of subscriber information because SQL Server CE doesn't validate it.  The next option provides the opportunity to minimize data sent over the network. This sounds like a great option for Windows CE, so choose Yes as shown in Figure 8.20. Figure 8.20. SQL Server can minimize network traffic for subscriptions by maintaining more sophisticated snapshots of the data at the server.  In the next step (see Figure 8.21), the wizard presents a single choice of Yes for allowing anonymous subscriptions. Because SQL Server CE supports only anonymous subscription, the No option is disabled. Click Next. Figure 8.21. SQL Server CE only allows for anonymous subscription.  The next step allows for the scheduling of the snapshots for new subscribers. Leaving the default schedule is fine, but be sure Create the First Snapshot Immediately is checked (see Figure 8.22). Click Next. Figure 8.22. The default schedule is weekly for the snapshot agent to run, but be sure the initial snapshot is created immediately.  Lastly, the publication is created as the last step of the wizard, as shown in Figure 8.23. Figure 8.23. The publication is created after clicking the Finish button.  Investigating the SQL Server CE Replication Object Now that you are more familiar with the Microsoft SQL Server replication model, let's look at the SQL Server CE replication object. To use SQL Server CE replication, your application will use the SSCE.Replication ActiveX DLL server. SSCE.Replication is one of three creatable SQL Server CE objects. SSCE.Replication provides access to a Microsoft SQL Server 2000 publisher. It requires quite a few properties configured to properly establish a subscription from the publisher. Table 8.1 shows the minimal properties that should be set to initialize a publication on SQL Server CE. Table 8.1. Minimal SQL Server CE Properties to Subscribe to a SQL Server Publication | Property | Description and Example | | InternetURL | URL of the sscesa10.dll: object .InternetURL = "http://www.myserver.com/ssce/sscesa10.dll" | | Publication | Name of the publication published on the publisher: object. Publication = "MyPublication" | | Publisher | Name of the Publisher (SQL Server Name): object. Publisher = "MySQLServer" | | PublisherAddress | Network address of the SQL Server publisher; the publisher can exist on a different server than the IIS server: object. PublisherAddress = "sql.myserver.com" or object. PublisherAddress = "192.0.0.1" | | PublisherDatabase | Name of the database where the publication resides; the database where the publication resides isn't necessarily the same as the name of the database where the publication originated: object. PublisherDatabase = "MyPublishedDB" | | PublisherLogin | User ID for the publisher. Depending on the security mode (PublisherSecurityMode) for the publisher, this can be either a SQL Server login or a Windows NT login: object. PublisherSecurityMode = "MyUserId" | | PublisherNetwork | Network configuration for the SQL Server serving as the publisher. Valid values are DEFAULT_NETWORK, MULTI_PROTOCOL, and TCPIP_SOCKETS: object. PublisherNetwork = TCPIP_SOCKETS | | PublisherPassword | Password for the user ID. Works in tandem with the PublisherLogin value: object. PublisherPassword = "MyPassword" | | PublisherSecurityMode | Security mode for the publisher, SQL, or Windows NT. Valid values are DB_AUTHENTICATION and NT_AUTHENTICATION: object. PublisherSecurityMode = DB_AUTHENTICATION | | Subscriber | Name of the subscriber. Although the publication may allow anonymous subscribers, a value is required: object. Subscriber = "MySubscriberName" | | SubscriberConnectionString | Connection string to the SQL Server CE database the subscription will be placed: object. SubscriberConnectionString = "Provider=Microsoft.SQLServer.OLEDB.CE.1.0;Data Source=\MySSCEDb.sdf" | After you properly set all the properties in Table 8.1, three different methods are called in the order listed here to create the subscription database (AddSubscription), initialize a subscription (Initialize), and synchronize the data (Run). After these methods are called, the subscriber should call the Terminate method. AddSubscription marks or creates a database that subscribes to a Microsoft SQL Server publication. It takes a single parameter of type SSCE.REPL_DBADDOPTION. The two different values are CREATE_DATABASE and EXISTING_DATABASE. Using CREATE_DATABASE actually creates a new SQL Server CE database based on the values in the SubscriberConnectionString value. If CREATE_DATABASE is used and the SQL Server CE database specified in the SubscriberConnectionString already exists, an error occurs. AddSubscription is called only once for each database and subscription. If the database already exists, whether created with the AddSubscription method or otherwise , call AddSubscription with the EXISTING_DATABASE value for the parameter. Initialize prepares for synchronization. It must always be called before the Run method is called. Note The database specified must not be open from any ADOCE objects.
Next, Run initiates the communication with the server and then retrieves the subscription. Each time Run is called, all data structures for the subscription are created or modified. If a structure change has occurred in any article of the publication, all modified data is uploaded first, and then the structure changes are implemented before the data comes down to the device. Lastly, Terminate is called to complete the merge process. In Listing 8.6, a simple example is given. This example creates a SQL Server CE database, Initializes a subscription, synchronizes with the Run method, and then completes the process by calling Terminate. Listing 8.6 Creating a SQL Server CE Database that Subscribes to a Publication Dim objSSCEReplication As SSCE.Replication ' create the SSCE.Replication object Set objSSCEReplication = CreateObject("SSCE.Replication.1.0") ' the URL for the SSCE dll running on IIS objSSCEReplication.InternetURL "http://192.0.0.1/ssce/sscesa10.dll" ' the SQL server network library setting objSSCEReplication.PublisherNetwork = TCPIP_SOCKETS ' name of the Publisher for replication objSSCEReplication.Publisher = "tblap" ' name of the database objSSCEReplication.PublisherDatabase = "CD" ' name of the publication objSSCEReplication.Publication = "CDPublication" ' security mode to authenticate to the SQL server objSSCEReplication.PublisherSecurityMode = DB_AUTHENTICATION ' sql server login objSSCEReplication.PublisherLogin = "sa" ' sql server password objSSCEReplication.PublisherPassword = "" ' IP address of the publisher objSSCEReplication.PublisherAddress = "192.0.0.1" ' Set connection properties objSSCEReplication.SubscriberConnectionString = "Data Source=\CD.sdf" ' set the subscriber name objSSCEReplication.Subscriber = "mobileuser1" ' if the first time, we need to create the DB ' while we're adding a subscription objSSCEReplication.AddSubscription CREATE_DATABASE ' initialize the subscription on the device objSSCEReplication.Initialize ' sync the subscription objSSCEReplication.Run ' close out and complete objSSCEReplication.Terminate Let's examine Listing 8.3 line by line. First, an object reference is declared as an SSCE.Replication object: Dim objSSCEReplication As SSCE.Replication The SSCE.Replication object is then created: Set objSSCEReplication = CreateObject("SSCE.Replication.1.0") Next, the InternetURL property is set to the URL of the SQL Server CE DLL residing on the server: objSSCEReplication.InternetURL "http://192.0.0.1/ssce/sscesa10.dll" Remember, this is the virtual directory you created, copied the DLLs into, and registered by using RegSrv32. The PublisherNetwork property is set to the value on which the server-side SQL Server listens: objSSCEReplication.PublisherNetwork = TCPIP_SOCKETS The Publisher property is set to the named instance of the SQL server that serves as the publication's publisher: objSSCEReplication.Publisher = "tblap" The PublisherDatabase property is set to the name of the database from which the publication originates: objSSCEReplication.PublisherDatabase = "CD" The Publication property is set to the name of the publication: objSSCEReplication.Publication = "CDPublication" The publication name can vary from the database from which it originates. There can even be multiple publications for a individual database. The PublisherSecurityMode property is set to the type of authentication the subscriber (devices) desires to use as its authentication method: objSSCEReplication.PublisherSecurityMode = DB_AUTHENTICATION The SQL Server must be configured to use the specified security mode. The PublisherLogin property is set to the SQL Server login that has access to the publication. In this example, the sa login is used; your security policies may dictate a different policy. objSSCEReplication.PublisherLogin = "sa" The PublisherPassword property is set to the valid password that corresponds to the value for PublisherLogin: objSSCEReplication.PublisherPassword = "" The PublisherAddress property is set to the network address of the publisher SQL Server: objSSCEReplication.PublisherAddress = "192.0.0.1" Remember, the publisher might be in a different location than the IIS server hosting the SQL Server CE DLLs. The SubscriberConnectionString property is set with the Data Source syntax for the location of the SQL Server database in which the subscription should be created: objSSCEReplication.SubscriberConnectionString = "Data Source=\CD.sdf" The Subscriber property is set to the subscriber's name: objSSCEReplication.Subscriber = "mobileuser1" The AddSubscription method is called by using the CREATE_DATABASE enumeration value: objSSCEReplication.AddSubscription CREATE_DATABASE If the SQL Server CE database exists, as specified as the Data Source in the SubscriberConnectionString property, the EXISTING_DATABASE enumeration value should be used instead. The Initialize method is called, preparing the object and database to synchronize: objSSCEReplication.Initialize The Run method is called to actually subscribe to the subscription, and receive the structures and data: objSSCEReplication.Run The Terminate method is called to perform cleanup and complete the merging process: objSSCEReplication.Terminate Converting the CD Library Application to Use SQL Server CE Replication At this point, you should have the CD database created, a publication ready, and a good understanding of the SQL Server CE replication object. Let's get started converting that application. If you didn't create the CD Library application, you can use the sample code from Chapter 7. This chapter presents only added and modified code from the material presented in Chapter 7. Project-Level Changes The application needs to reference three new ActiveX libraries. The first is SQL Server CE itself. To reference SQL Server CE, you need to actually reference a type library instead of a DLL. By using the Browse button in the project references dialog, navigate to type library file ca_merge.tlb. The location of this file using a default install is C:\Program Files\Microsoft SQL Server CE\Lib\ca_merge.tlb. You may be prompted that the selected library isn't for your current platform; add it anyway. SQL Server CE appears in the dialog box as Microsoft SQL Server CE Control 1.0, even if it's version 1.1, the most current version at the time of this writing. The second and third libraries that need a reference set should be available in your project references list already. The second library is Microsoft CE ADO Control 3.1, ADOCE version 3.1. SQL Server CE requires version 3.1 (versus Pocket Access compatibility to earlier versions) and should have been installed when you installed the developer tools for SQL Server CE from the install package. The third library needed is Microsoft CE ADO Ext 3.1 for DDL. This library is the CE version of ADOX, used for creating databases, tables, indexes, and other data structures in a SQL Server CE database. We will use this ADOXCE library to create a table for the storage of configuration parameters. Your project references dialog should now look similar to Figure 8.24. Figure 8.24. The CD Library Replication Application should have project references to Microsoft CE ADO Control 3.1, SQL Server CE Control 1.0 and Microsoft CE ADO Ext. for DDL 3.1.  Other project-level changes that you need to accomplish before digging in are to add a new module file named modSQLRepl.bas and a form named frmSQLProperties. After adding the module file, stub out a Sub Main in it, so you can change the Startup Object to Sub Main (see Figure 8.25). Also, add a FileSystem control named frmSQLProperties. Notice in Figure 8.25 that the project's Update Components settings are set to force down the SQL Server CE DLLs to a device as discussed for Figure 8.2. Figure 8.25. The CD Library Replication project needs to use Sub Main as its Startup Object.  Digging In: New Consts and Public Variables Some new Consts and Public variables need to be declared to make the CD Library application compliant to SQL Server CE. These are places in the general section of module file modSQlRepl.bas, as shown in Listing 8.7. Listing 8.7 modSQLRepl.bas: Consts and Public Variables ' the database path and name Public Const DB_PATH = "\CD.sdf" ' the config parms table Public Const CONFIG_PARAMS_TABLE = "ConfigParms" Public gblnFirstStartUp As Boolean Public gstrInternetURL As String Public gstrPublisherAddress As String Public gstrPublisherNetwork As SSCE.REPL_NETWORK_TYPE Public gstrPublisher As String Public gPublisherSecurityMode As SSCE.REPL_SECURITY_TYPE Public gstrPublisherLogin As String Public gstrPublisherPassword As String Public gstrSubscriberConnectionString As String Public gstrSubscriber As String Public gstrHostName As String Public gstrPublication As String Public gstrPublisherDatabase As String Public gstrIPAddress As String Public glngCurrentUser As Long ' the SQL Server CE replication object Public gSQLReplication As SSCE.Replication ' ADOXCE Catalog - Used to check for the ' config table's existence Public gobjADOXCatalog As ADOXCE.Catalog ' configuration recordset Public gConfigRecord As ADOCE.Recordset ' used to retrieve user id Public gobjUser As ADOCE.Recordset ' class names Public Const SSCE_REPLICATION_CLASS = "SSCE.Replication.1.0" Public Const ADOXCE_CATALOG_CLASS = "ADOXCE.Catalog.3.1" ' AddSubscription Consts Public Const EXISTING_DATABASE = 0 Public Const CREATE_DATABASE = 1 ' NetworkType Consts Public Const DEFAULT_NETWORK = 0 Public Const TCPIP_SOCKETS = 1 Public Const MULTI_PROTOCOL = 2 ' Authentication Consts Public Const DB_AUTHENTICATION = 0 Public Const NT_AUTHENTICATION = 1 Examining Listing 8.7 by sections, the first two Consts are the database path name and a table name for the configuration parameters table, ConfigParams. The configuration parameters table stores the property settings (SSCE.Replication object) used for a successful subscription initialization or synchronization. This allows ease of use for the user without having to manually configure the settings every time a synchronization is desired. The second section (starting Public gstrInternetURL As String) is a collection of Public variables that store the SSCE.Replication configuration settings from the initial hard-coded values, the configuration form (frmSQLProperties), or as retrieved from the ConfigParams table. The third section declares the references for the SSCE.Replication object, an ADOXCE.Catalog to check for the existence of the ConfigParams table, an ADOCE.Recordset to retrieve and update the configuration values in the ConfigParams table, and another recordset to retrieve the primary key value for the current user. The fourth section, labeled class names, declares two Consts for the SSCE.Replication and ADOXCE.Catalog class names and versions. These are present for maintainability; for more information about this strategy, please see Chapter 7. The final three sections declare Consts used as values for various SSCE.Replication properties. Note The Consts declared in the final three sections may not be necessary in your development environment. I've experienced some environments that require them and some that don't. I've included them here for you; if you receive errors when trying to access them, simply comment them out or remove them.
modSQLRepl.bas: Sub Main, Where the Journey Begins Because application initialization takes quite a bit of configuration before the subscription arrives, a Sub Main (see Listing 8.8) is implemented to decide where to go. Listing 8.8 modSQLRepl.bas: Initializing the Application Sub Main() On Error Resume Next ' initialize all global variables If Not InitializeGlobals() Then MsgBox "Error initializing global objects." Exit Sub End If ' default to first startup gblnFirstStartUp = True ' if the database exists then set first start up to false If Len(frmSQLProperties.FileSystem1.Dir(DB_PATH)) > 0 Then gblnFirstStartUp = False End If ' if we can't load the configs, then inform the ' user and exit If Not LoadConfigs() Then MsgBox "Error loading configuration." End If ' if first startup If gblnFirstStartUp Then ' let's show the sql form ' allow user to check/set the values frmSQLProperties.Show Else ' we're all set, show the main form frmMain.Show End If End Sub The code in Listing 8.8 starts by creating all the objects the application uses by calling InitializeGlobals. Code has been added to InitializeGlobals to create the objects added to CD Library Replication, as shown shortly in Listing 8.9. Next, a global Boolean flag, gblnFirstStartUp, is set to True, indicating this is the first time the application has been run on the device. This flag determines the first form to show and whether the subscription needs to be created. Next, the FileSystem object checks for the existence of the SQL Server CE database file used by the application. If the database file exists, the flag gblnFirstStartUp is set to False, indicating the application can proceed with a normal startup. LoadConfigs (coming up soon in Listing 8.10) is then called to populate all the Public variables that hold the configuration values for synchronizing the subscription to the publication on the SQL server. These values are used by the SSCE.Replication object, gSQLReplication. When LoadConfigs is called without a current subscription (the first time), it will populate the values from hard-coded values; otherwise, it will load them from the ConfigParams table. After the configuration parameters are loaded, Sub Main will display one of two forms. If the application is currently in a "first-run" state, as designated by the variable gblnFirstStartUp, the form frmSQLProperties is shown. If the application is a normal state, the form frmMain is shown. modGlobal.bas: InitializeGlobals, Where All Objects Are Created InitializeGlobals in Listing 8.9 creates all the ActiveX objects used in the CD Library Replication application. This function is the same with four new objects being created. For the original discussion of InitializeGlobals, see Listing 7.1 in Chapter 7. Listing 8.9 modGlobal.bas: Changes to InitializeGlobals Function InitializeGlobals() As Boolean On Error Resume Next InitializeGlobals = False ' initialize globals - this needs to take place ' only once, due to CreateObject memory leak ' ADOCE_CLASS_CONNECTION & ADOCE_CLASS_RECORDSET ' are used because ce references these classes ' by their version numbers, create Consts allows ' changing of these version quickly and accurately Set gobjConnection = CreateObject(ADOCE_CLASS_CONNECTION) Set gobjArtist = CreateObject(ADOCE_CLASS_RECORDSET) Set gobjCD = CreateObject(ADOCE_CLASS_RECORDSET) Set gobjGenre = CreateObject(ADOCE_CLASS_RECORDSET) Set gobjPKEY = CreateObject(ADOCE_CLASS_RECORDSET) Set gConfigRecord = CreateObject(ADOCE_CLASS_RECORDSET) Set gSQLReplication = CreateObject(SSCE_REPLICATION_CLASS) Set gobjADOXCatalog = CreateObject(ADOXCE_CATALOG_CLASS) Set gobjUser = CreateObject(ADOCE_CLASS_RECORDSET) If Err.Number = 0 Then InitializeGlobals = True End If End Function InitializeGlobals creates four new objects to support CD Library Replication. modSQLRepl.bas: LoadConfigs, Finding the Server LoadConfigs (see Listing 8.10) populates the Public variables used to configure the SSCE.Replication object gSQLReplication. On the application's first run, the values are populated from hard-coded values. Subsequent execution of CD Library Replication will retrieve these values from the ConfigParams table. These values are saved to the ConfigParams table using the function SaveConfiguration after a successful initialization of the subscription. Listing 8.10 modSQLRepl.bas: Populating the Configuration Parameters for Creating or Synchronizing the Subscription Public Function LoadConfigs() As Boolean On Error Resume Next ' set to error condition for early exit LoadConfigs = False ' are we here the first time ' if so, let's set the values If gblnFirstStartUp Then ' ip address of the web server gstrIPAddress = "192.0.0.1" ' URL for the SSCE dll gstrInternetURL = "http://" _ & gstrIPAddress & "/ssce/sscesa10.dll" ' name of the Publisher (SQL) gstrPublisher = "tblap" ' security mode that the publisher uses gPublisherSecurityMode = DB_AUTHENTICATION ' login id for the publisher gstrPublisherLogin = "sa" ' password gstrPublisherPassword = "" ' network library for the publisher gstrPublisherNetwork = TCPIP_SOCKETS ' name of the database for the publication (on the publisher) gstrPublisherDatabase = "CD" ' name of the subscriber gstrSubscriber = "mobileuser1" ' host name - used to filter the data gstrHostName = "mobileuser1" ' name of the publication gstrPublication = "CDPublication" Else ' open the database to get the SQL CE Replication configurations If Not OpenDatabase() Then ' error - get out Exit Function End If ' open the config table gConfigRecord.Open CONFIG_PARAMS_TABLE, gobjConnection, _ adOpenForwardOnly, adLockReadOnly, adCmdTableDirect ' get the values gstrInternetURL = gConfigRecord.Fields("InternetURL").Value gstrPublisherNetwork = gConfigRecord.Fields("PublisherNetwork").Value gstrPublisher = gConfigRecord.Fields("Publisher").Value gPublisherSecurityMode = _ gConfigRecord.Fields("PublisherSecurityMode").Value gstrPublisherLogin = gConfigRecord.Fields("PublisherLogin").Value gstrPublisherPassword = gConfigRecord.Fields("PublisherPassword").Value gstrSubscriber = gConfigRecord.Fields("Subscriber").Value gstrHostName = gConfigRecord.Fields("HostName").Value gstrPublication = gConfigRecord.Fields("Publication").Value gstrIPAddress = gConfigRecord.Fields("IPAddress").Value gstrPublisherDatabase = gConfigRecord.Fields("PublisherDB").Value ' close it gConfigRecord.Close End If ' set the return value to True if there ' was no error LoadConfigs = Err.Number = 0 End Function Outside the error handling and setting of the return value for the function, LoadConfigs is basically two code blocks. The code block to run is determined by an If statement as to status of the flag gblnFirstStartUp. If it's the first run of the application (gblnFirstStartUp = True), the configuration variables are set by the execution of the hard-coded code block. When entering the hard-coded code block, you can change the values to match your configuration, or enter them as they appear here. We'll be creating a form that displays all of them for configuration. After modifying their contents and receiving a valid subscription, they will be saved, so you shouldn't have to make the changes repeatedly. The second code block (after the Else) opens a recordset from the ConfigParams table and populates the values from this recordset. frmSQLProperties: Setting Configuration Properties at Runtime With the first startup, Sub Main shows the frmSQLProperties form, which allows configuration of the values for the SSCE.Replication object gSQLReplication. This form works by using a multiframe wizardlike interface. There are three frames to this wizard, as shown in Figures 8.26, 8.27 and 8.28. The form uses Next and Back buttons to navigate through the three frames. Figure 8.26. The first frame of frmSQLProperties allows for configuration of the IIS SQL Server CE URL, the publisher address, the name of the publisher, and the publisher login and password.  Figure 8.27. The second frame allows for configuration of the security mode for authentication on the publisher, the network protocol to communicate with the publisher, the name of the database on the publisher, the subscriber name, and the host name.  Figure 8.28. The third and last frame allows for configuration of the publication name and a button to initialize or synchronize the subscription.  The form frmSQLProperties is only one form, with three different frames placed onto it. This methodology provides speedy performance and was quite necessary in the days of the 70MHz MIPS processor. The performance need for this trick may no longer be needed with the 206MHz StrongArm processors out there, but if you ever create an application with a tab set, you'll want to pull it out of your Windows CE programming toolbox. Look at the form at design time in Figure 8.29; the three frames are randomly placed on the form and are shaded so that you can see their borders clearly. Figure 8.29. Form frmSQLProperties at design time shows all three frames clearly by shading their backgrounds.  It should be easiest for you to retrieve the visual portions of the form from the sample code files, but let's look at the code behind this form that makes it work. One Public variable, CurrentFrame, is declared to keep track of which frame should be currently displayed: ' currently displayed frame Public CurrentFrame As Integer Form.Load: Moving the Frames to Their Correct Positions In the Form.Load event of frmSQLProperties (see Listing 8.11), all the frames are moved to the form's upper-left corner. Listing 8.11 frmSQLProperties.frm: Moving the Frames Private Sub Form_Load() ' move all frames to proper position Me.Frame1.Top = 0 Me.Frame1.Left = 0 Me.Frame2.Top = 0 Me.Frame2.Left = 0 Me.Frame3.Top = 0 Me.Frame3.Left = 0 End Sub Form.Activate: Initializing the Controls In the Form.Activate event of frmSQLProperties (see Listing 8.12), all the controls are initialized to the current values of the Public replication configuration values. Listing 8.12 frmSQLProperties.frm: Initializing the Controls' Values Private Sub Form_Activate() ' set the current frame to 1 CurrentFrame = 1 ' Refresh the frames RefreshFrames ' set the values from the globals Me.txtURL.Text = gstrInternetURL Me.txtPublisherAddress.Text = gstrIPAddress Me.txtPublisherName.Text = gstrPublisher Me.txtPublisherLogin.Text = gstrPublisherLogin Me.txtPublisherPassword.Text = gstrPublisherPassword Me.txtDB.Text = gstrPublisherDatabase Me.txtSubscriberName.Text = gstrSubscriber Me.txtHostName.Text = gstrHostName Me.txtPublication.Text = gstrPublication ' set security mode radio buttons If gPublisherSecurityMode = DB_AUTHENTICATION Then Me.optSecDB = True Else Me.optSecWin = True End If ' set network protocol radio buttons Select Case gstrPublisherNetwork Case TCPIP_SOCKETS Me.optNetTCPIP = True Case MULTI_PROTOCOL Me.optNetMulti = True Case DEFAULT_NETWORK Me.optNetworkDef = True End Select End Sub The first item accomplished in Form.Activate is to set the Public variable CurrentFrame to 1 to ensure that the first frame is shown when the form displays. Then RefreshFrames (see Listing 8.13) is called to make the proper frame and buttons visible. Next, all controls on all three frames are set to the current values from their corresponding Public variables. First, all the textboxes are set, and then the two sets of option buttons are set. RefreshFrames: Displaying the Correct Frame and the Next/Back Buttons RefreshFrames (see Listing 8.13) uses three simple formulas to set the Visible property for the current frame to be displayed based on the value of CurrentFrame. It then sets the Enabled property of both the Next and Back buttons also from the value of CurrentFrame. Listing 8.13 frmSQLProperties.frm : Displaying the Correct Frame and Buttons Private Sub RefreshFrames() ' set each frames visible to true or ' false as to whether it is the current frame Me.Frame1.Visible = (CurrentFrame = 1) Me.Frame2.Visible = (CurrentFrame = 2) Me.Frame3.Visible = (CurrentFrame = 3) ' next should only be visible if frame 2 or 3 Me.cmdBack.Enabled = (CurrentFrame > 1) ' back should only be visible if frame 1 or 2 Me.cmdNext.Enabled = (CurrentFrame < 3)End Sub cmdBack.Click and cmdNext.Click: Navigating Through the Frames Both the cmdNext.Click and cmdBack.Click events (see Listing 8.14) change the value of CurrentFrame and refresh the form with RefreshFrames. Sub cmdNext.Click increments CurrentFrame by 1, whereas Sub cmdBack.Click decrements it by 1. Listing 8.14 frmSQLProperties.frm: Navigating Through the Frames Using cmdBack.Click and cmdNext.Click Private Sub cmdNext_Click() If CurrentFrame < 3 Then ' move forward one frame CurrentFrame = CurrentFrame + 1 ' refresh the frames RefreshFrames End IfEnd Sub Private Sub cmdBack_Click() If CurrentFrame > 1 Then ' move back one frame CurrentFrame = CurrentFrame - 1 ' refresh the frames RefreshFrames End IfEnd Sub cmdInitialize.Click: Initializing the Subscription In Listing 8.15, cmdInitialize.Click retrieves all the values from all controls located within the three frames and sets these values to the appropriate configuration Public variables. It then calls the Sync function to create the subscription. With a successful call to Sync, the current state of the configuration is saved to the ConfigParams table using the SaveConfiguration function. Listing 8.15 frmSQLProperties.frm: Setting the Configuration Values, Initializing the Subscription, and Saving the Configuration Private Sub cmdInitialize_Click() ' set the global properties from the ' form's values gstrInternetURL = Me.txtURL.Text gstrPublisherAddress = Me.txtPublisherAddress.Text gstrPublisher = Me.txtPublisherName.Text gstrPublisherLogin = Me.txtPublisherLogin.Text gstrPublisherPassword = Me.txtPublisherPassword.Text gstrSubscriber = Me.txtSubscriberName.Text gstrHostName = Me.txtHostName.Text gstrPublication = Me.txtPublication.Text gstrPublisherDatabase = Me.txtDB.Text gstrIPAddress = Me.txtPublisherAddress.Text ' radio button set Select Case True Case Me.optNetMulti.Value gstrPublisherNetwork = MULTI_PROTOCOL Case Me.optNetTCPIP.Value gstrPublisherNetwork = TCPIP_SOCKETS Case Me.optNetworkDef.Value gstrPublisherNetwork = DEFAULT_NETWORK End Select ' radio button set Select Case True Case Me.optSecDB.Value gPublisherSecurityMode = DB_AUTHENTICATION Case Me.optSecWin.Value gPublisherSecurityMode = NT_AUTHENTICATION End Select ' Sync If Not Sync() Then MsgBox "Errors occurred in creating subscription." Exit Sub End If If Not SaveConfiguration() Then MsgBox "Could not save configuration." Exit Sub End If ' show the main form frmMain.Show End Sub The majority of cmdInitialize.Click (up to the Sync call) sets the configuration values, which is basically a reversal of the Form.Activate event (refer to Listing 8.12), which sets the control values from the configuration values. Next, the Sync Function (shown soon in Listing 8.16) is called. If Sync isn't successful, a message box informs users of the failure and the routine is exited. Continuing on (Sync was successful), the configuration is saved using the SaveConfiguration function. modSQLRepl.bas: Sync, Getting at That Data The Sync function uses the SSCE.Replication object, gSQLReplication, to create the database and initialize the subscription (see Listing 8.16). Listing 8.16 modReplSQL.bas: Initializing the Subscription Function Sync() As Boolean On Error Resume Next 'set the error state so we can leave quickly Sync = False ' we have to close the database ' before we Sync to the remote server If Not CloseDatabase() Then ' problem closing - let's exit with ' an error condition Exit Function End If ' the URL for the SSCE dll running on IIS gSQLReplication.InternetURL = gstrInternetURL ' the SQL server network library setting gSQLReplication.PublisherNetwork = TCPIP_SOCKETS ' name of the Publisher for replication gSQLReplication.Publisher = gstrPublisher ' name of the database gSQLReplication.PublisherDatabase = gstrPublisherDatabase ' name of the publication gSQLReplication.Publication = gstrPublication ' security mode to authenticate to the SQL server gSQLReplication.PublisherSecurityMode = gPublisherSecurityMode ' login gSQLReplication.PublisherLogin = gstrPublisherLogin ' password gSQLReplication.PublisherPassword = gstrPublisherPassword ' IP address of the publisher gSQLReplication.PublisherAddress = gstrIPAddress ' Set Subscriber properties gSQLReplication.SubscriberConnectionString = "Data Source=" & DB_PATH ' set the subscriber name gSQLReplication.Subscriber = gstrSubscriber ' set the host name gSQLReplication.HostName = gstrSubscriber ' if the first time, we need to create the DB ' while we're adding a subscription If gblnFirstStartUp Then gSQLReplication.AddSubscription CREATE_DATABASE End If ' initialize the subscription on the device gSQLReplication.Initialize ' check for errors If gSQLReplication.ErrorRecords.Count > 0 Then ' display any errors ConnErrRoutine gSQLReplication Else ' Sync with server gSQLReplication.Run ' check for errors If gSQLReplication.ErrorRecords.Count > 0 Then ' display errors ConnErrRoutine gSQLReplication End If End If ' close out and complete gSQLReplication.Terminate ' open or reopen the database If Not OpenDatabase() Then Exit Function End If ' set the return value to true if no error Sync = Err.Number = 0 ' check the status If Sync Then MsgBox "Sync completed successfully.", , "CD Library" Else MsgBox "Sync completed with errors.", , "CD Library" End If End Function Let's break down the Sync function and investigate it. Setup is completed in Listing 8.17. Listing 8.17 Setting the Error Condition and Closing the Database 'set the error state so we can leave quickly Sync = False ' we have to close the database ' before we Sync to the remote server If Not CloseDatabase() Then ' problem closing - let's exit with ' an error condition Exit Function End If First, in Listing 8.17, the return value is set to an error condition so that you can make a quick exit. Then the database is closed using the CloseDatabase function. The database needs to be closed to successfully complete a synchronization process with the publication on the server. Although you don't have a database at this point (if this is the first synchronization), CloseDatabase is constructed in such a way that it won't error, but should still return a success value of True. Listing 8.18 sets all the configuration variables on the SSCE.Replication object gSQLReplication. It also uses the Const DB_PATH to set the Data Source attribute of the SubscriberConnectionString property. Listing 8.18 Transferring the Configuration Values to the Replication Object ' the URL for the SSCE dll running on IIS gSQLReplication.InternetURL = gstrInternetURL ' the SQL server network library setting gSQLReplication.PublisherNetwork = gstrPublisherNetwork ' name of the Publisher for replication gSQLReplication.Publisher = gstrPublisher ' name of the database gSQLReplication.PublisherDatabase = gstrPublisherDatabase ' name of the publication gSQLReplication.Publication = gstrPublication ' security mode to authenticate to the SQL server gSQLReplication.PublisherSecurityMode = gPublisherSecurityMode ' login gSQLReplication.PublisherLogin = gstrPublisherLogin ' password gSQLReplication.PublisherPassword = gstrPublisherPassword ' IP address of the publisher gSQLReplication.PublisherAddress = gstrIPAddress ' Set Subscriber properties gSQLReplication.SubscriberConnectionString = "Data Source=" & DB_PATH ' set the subscriber name gSQLReplication.Subscriber = gstrSubscriber ' set the host name gSQLReplication.HostName = gstrSubscriber Listings 8.18 and 8.19 correlate to the mandatory values to create a subscription to a publication or synchronize the subscription with the publication. Listing 8.19 Initializing the Subscription ' if the first time, we need to create the DB ' while we're adding a subscription If gblnFirstStartUp Then gSQLReplication.AddSubscription CREATE_DATABASE End If If it's determined that this is a first time initialization with the publication, you need to call AddSubscription to properly initialize the SSCE.Replication object. Also, the CREATE_DATABASE parameter is used for the AddSubscription method because you currently shouldn't have a SQL Server CE database file (if this is the first time you have attempted to subscribe to the publication). Listing 8.20 finally retrieves the subscription from the publication. Listing 8.20 Retrieving the Subscription ' initialize the subscription on the device gSQLReplication.Initialize ' check for errors If gSQLReplication.ErrorRecords.Count > 0 Then ' display any errors ConnErrRoutine gSQLReplication Else ' Sync with server gSQLReplication.Run ' check for errors If gSQLReplication.ErrorRecords.Count > 0 Then ' display errors ConnErrRoutine gSQLReplication End If End If Before a subscription can actually be initialized or synchronized using the Run method, the Initialize method needs to be called as shown in Listing 8.20. Initialize prepares the SSCE.Replication for synchronizing with the publication. At this point, the SQL Server CE database is created. If there are no ErrorRecords after calling the Initialize method, the Run method is called to actually retrieve the subscription or synchronize it. If there are ErrorRecords, ConnErrRoutine is called passing the SSCE.Replication object gSQLReplication. Listing 8.21 starts the cleanup for the Sync function. Listing 8.21 Finishing the Merge Replication and Cleaning Up ' close out and complete gSQLReplication.Terminate ' open or repopen the database If Not OpenDatabase() Then Exit Function End If The call to the Terminate method actually finishes the merging of data in the SQL Server CE database (if the database existed and had previously subscribed to the publication). Also in Listing 8.21, the database is opened, or reopened in the case that it was previously present and open using the OpenDatabase Function. In Listing 8.22, the return value is set to True if there were no errors. Also, the status of the sync process is displayed to users based on the return value. Listing 8.22 Setting the Return Value and Informing Users of the Synchronization's Status ' set the return value to true if no error Sync = (Err.Number = 0) ' check the status If Sync Then MsgBox "Sync completed successfully.", , "CD Library" Else MsgBox "Sync completed with errors.", , "CD Library" End If modSQLRepl.bas: SaveConfiguration, Saving the Configuration Information to Synchronize with the Publisher SaveConfiguration in Listing 8.23 allows any changes made at runtime to the hard-coded values in LoadConfigs (see Listing 8.10) to be saved. This allows quick and easy synchronization to occur without significant effort on the user's behalf . Listing 8.23 modReplSQL.bas: Storing the Replication Configuration for Future Syncs Function SaveConfiguration() As Boolean Dim objADOXTable As ADOXCE.Table Dim blnTableFound As Boolean On Error Resume Next ' set error condition so we can exit early SaveConfiguration = False ' set the ADOX catalog connection to the db connection Set gobjADOXCatalog.ActiveConnection = gobjConnection ' set flag that we have not found ' the configuration table blnTableFound = False ' cycle through each table in the DB For Each objADOXTable In gobjADOXCatalog.Tables ' check if it is the ConfigParams Table If objADOXTable.Name = CONFIG_PARAMS_TABLE Then ' if we found it, set the flag and get out blnTableFound = True Exit For End If Next ' if we did not find the table, let's create it If Not blnTableFound Then ' create the table gobjConnection.Execute "CREATE TABLE " _ & CONFIG_PARAMS_TABLE _ & " (InternetURL nvarchar(255), PublisherNetwork tinyint, " _ & "Publisher nvarchar(255), PublisherDB nvarchar(255), " _ & "PublisherSecurityMode tinyint, PublisherLogin nvarchar(255), " _ & "PublisherPassword nvarchar(255), Subscriber nvarchar(255), " _ & "HostName nvarchar(255), Publication nvarchar(255), " _ & "IPAddress nvarchar(255))" End If ' open it for adding/updating gConfigRecord.Open CONFIG_PARAMS_TABLE, gobjConnection, _ adOpenForwardOnly, adLockOptimistic, adCmdTableDirect ' there should only be one record, so if there ' are not, add a record If gConfigRecord.EOF Then ' add the record gConfigRecord.AddNew End If ' save all the configuration data gConfigRecord.Fields("InternetURL") = gstrInternetURL gConfigRecord.Fields("PublisherNetwork") = gstrPublisherNetwork gConfigRecord.Fields("Publisher") = gstrPublisher gConfigRecord.Fields("PublisherSecurityMode") = gPublisherSecurityMode gConfigRecord.Fields("PublisherLogin") = gstrPublisherLogin gConfigRecord.Fields("PublisherPassword") = gstrPublisherPassword gConfigRecord.Fields("Subscriber") = gstrSubscriber gConfigRecord.Fields("HostName") = gstrHostName gConfigRecord.Fields("Publication") = gstrPublication gConfigRecord.Fields("IPAddress") = gstrIPAddress gConfigRecord.Fields("PublisherDB") = gstrPublisherDatabase ' send the changes gConfigRecord.Update ' if we're done with no errors let's ' clear the first time flag and set ' return value to success If Err.Number = 0 Then gblnFirstStartUp = False SaveConfiguration = True End If End Function Let's break down SaveConfiguration. Listing 8.24 declares a local ADOXCE.Table object and Boolean. The ADOXCE.Table object objADOXTable is used with the global ADOXCE.Catalog object gobjADOXCatalog to determine whether the configuration parameters table ConfigParams is present. Next, the gobjADOXCatalog is given a reference to the database using its ActiveConnection property. Lastly, the Boolean variable blnTableFound is explicitly set to False, making the assumption that ConfigParams doesn't exist. Listing 8.24 Getting Set Up and Setting the Catalog's Connection Dim objADOXTable As ADOXCE.Table Dim blnTableFound As Boolean On Error Resume Next ' set error condition so we can exit early SaveConfiguration = False ' set the ADOX catalog connection to the db connection Set gobjADOXCatalog.ActiveConnection = gobjConnection ' set flag that we have not found ' the configuration table blnTableFound = False Listing 8.25 first presents a For...Each loop that enumerates through each table contained within the ADOXCE.Catalog's Tables collection. Each enumerated ADOXCE.Table object is checked to see if it's the ConfigParams table. If it is, the Boolean is set to True and the For...Each loop is exited. Next in Listing 8.25, if the table hasn't been found, a SQL DDL statement is issued to create it. Listing 8.25 Searching for the ConfigParams Table and Creating It If Not Found ' cycle through each table in the DB For Each objADOXTable In gobjADOXCatalog.Tables ' check if it is the ConfigParams Table If objADOXTable.Name = CONFIG_PARAMS_TABLE Then ' if we found it, set the flag and get out blnTableFound = True Exit For End If Next ' if we did not find the table, let's create it If Not blnTableFound Then ' create the table gobjConnection.Execute "CREATE TABLE " _ & CONFIG_PARAMS_TABLE _ & " (InternetURL nvarchar(255), PublisherNetwork tinyint, " _ & "Publisher nvarchar(255), PublisherDB nvarchar(255), " _ & "PublisherSecurityMode tinyint, PublisherLogin nvarchar(255), " _ & "PublisherPassword nvarchar(255), Subscriber nvarchar(255), " _ & "HostName nvarchar(255), Publication nvarchar(255), " _ & "IPAddress nvarchar(255))" End If In Listing 8.26, whether the table previously existed or was just created, the ADOCE.Recordset object Opens the table. If there's no record within the recordset, by testing the EOF property, a record is appended using the AddNew method of the ADOCE.Recordset object. Listing 8.26 Opening the ConfigParams Table and Ensuring a Record for Saving Values ' open it for adding/updating gConfigRecord.Open CONFIG_PARAMS_TABLE, gobjConnection, _ adOpenForwardOnly, adLockOptimistic, adCmdTableDirect ' there should only be one record, so if there ' are not, add a record If gConfigRecord.EOF Then ' add the record gConfigRecord.AddNew End If Listing 8.27 simply transfers the values from the global variables to the record and Updates the table. Listing 8.27 Setting the Record's Values and Updating the Table ' save all the configuration data gConfigRecord.Fields("InternetURL") = gstrInternetURL gConfigRecord.Fields("PublisherNetwork") = gstrPublisherNetwork gConfigRecord.Fields("Publisher") = gstrPublisher gConfigRecord.Fields("PublisherSecurityMode") = gPublisherSecurityMode gConfigRecord.Fields("PublisherLogin") = gstrPublisherLogin gConfigRecord.Fields("PublisherPassword") = gstrPublisherPassword gConfigRecord.Fields("Subscriber") = gstrSubscriber gConfigRecord.Fields("HostName") = gstrHostName gConfigRecord.Fields("Publication") = gstrPublication gConfigRecord.Fields("IPAddress") = gstrIPAddress gConfigRecord.Fields("PublisherDB") = gstrPublisherDatabase ' send the changes gConfigRecord.Update Listing 8.28 does the cleanup. If there are no errors, the return value is set to a success value of True. Also, the first time run flag is set to False now that you have the subscription synchronized. Listing 8.28 Cleaning Up ' if we're done with no errors let's ' clear the first time flag and set ' return value to success If Err.Number = 0 Then gblnFirstStartUp = False SaveConfiguration = True End If Changing the CD Form and Supporting Functions for SQL Server CE Compatibility The CD form frmCD needs some changes to be compatible with SQL Server CE. There are two key differences in frmCD for Pocket Access and SQL Server CE: -
Because SQL Server CE subscriptions offer identity columns, you need to remove the manual primary key generating function, GetNewPK, and any calls made to it. -
You need to modify any code that did in-place updates to recordsets created through SQL statements. (For more information about in-place updating, refer to "Updating SQL Server CE Recordsets" earlier in this chapter.) Go ahead and remove the function GetNewPK from the project as well as any calls to it. Then, let's investigate the changes for SaveCD function in Listing 8.29. Listing 8.29 frmCD.frm: Changes for SQL Server CE Compatibility Private Function SaveCD() As Boolean Dim lngArtistId As Long Dim lngGenreId As Long On Error Resume Next ' because we may have some cleanup ' work to do, let's set the ' default return value to true SaveCD = True ' do we have an existing or new genre If Me.cboGenre.ListIndex >= 0 Then ' existing genre ' get the pk value lngGenreId = Me.cboGenre.ItemData(Me.cboGenre.ListIndex) Else ' new genre ' ask user to add new genre If MsgBox("Would you like to add the genre: " _ & Me.cboGenre.Text, vbYesNo) = vbYes Then ' get a new genre pk value lngGenreId = AddGenre(Me.cboGenre.Text) ' is it valid? If lngGenreId < 0 Then ' outta here MsgBox "Error adding genre." SaveCD = False Exit Function End If Else ' can't save without a genre SaveCD = False MsgBox "You cannot save a CD without a genre." Exit Function End If End If ' do we have an existing or new artist If Me.cboArtist.ListIndex >= 0 Then ' existing genre ' get the pk value lngArtistId = Me.cboArtist.ItemData(Me.cboArtist.ListIndex) Else ' new genre ' ask user to add new genre If MsgBox("Would you like to add the artist: " _ & Me.cboArtist.Text, vbYesNo) = vbYes Then ' get a new artist pk value lngArtistId = AddArtist(Me.cboArtist.Text, lngGenreId) ' is it valid? If lngArtistId < 0 Then ' outta here SaveCD = False MsgBox "Error adding artist." Exit Function End If Else ' can't save without a artist SaveCD = False MsgBox "You cannot save a CD without a genre." Exit Function End If End If ' close the CD recordset, we're about to update If gobjCD.State = adStateOpen Then gobjCD.Close End If ' check mode If Not IsNull(mlngCDId) Then 'edit mode - let's get the record gobjCD.Open "CD", gobjConnection, _ adOpenForwardOnly, adLockOptimistic, adCmdTableDirect ' set the index so we can seek gobjCD.Index = "PK_CD" ' seek the value gobjCD.Seek mlngCDId Else ' new mode, no primary key value gobjCD.Open "cd", gobjConnection, adOpenForwardOnly, _ adLockOptimistic, adCmdTableDirect ' add the record gobjCD.AddNew End If ' are we at the end of the ' recordset (no record added ' or no record found) If gobjCD.EOF Then Exit Function End If ' assign the values gobjCD.Fields("ArtistID").Value = lngArtistId gobjCD.Fields("CDName").Value = Me.txtCDName.Text gobjCD.Fields("YearOfRelease").Value = CLng(Me.txtYear.Text) gobjCD.Fields("Price").Value = CCur(Me.txtPrice.Text) gobjCD.Fields("GenreID").Value = lngGenreId ' set the UserId gobjCD.Fields("UserId").Value = glngCurrentUser ' update the recordset gobjCD.Update ' check for errors If Err.Number <> 0 Then Exit Function End If ' everything's cool, return true SaveCD = True End Function Changes made to SaveCD are boldfaced in Listing 8.29. Let's investigate the first change in Listing 8.30. Listing 8.30 Updating an Existing CD Record 'edit mode - let's get the record gobjCD.Open "CD", gobjConnection, _ adOpenForwardOnly, adLockOptimistic, adCmdTableDirect ' set the index so we can seek gobjCD.Index = "PK_CD" ' seek the value gobjCD.Seek mlngCDId Listing 8.30 should accomplish finding a single CD record based on a primary key value. It uses the methodology to create an updateable recordset from SQL Server CE data. First, the recordset is opened using the adCmdTableDirect value for the Open method's Options parameter. Next, the Index property is set to the name of the primary key index for the table, allowing you to perform a Seek on the recordset. The Seek method is called using the primary key value for the record to update. The next change to SaveCD is setting the UserId field's value for the current user. Having this value set allows the server side SQL Server to filter the CD table by users. The Public variable glngCurrentUser is set in the OpenDatabase function, shown in Listing 8.32. ' set the UserId gobjCD.Fields("UserId").Value = glngCurrentUser The two changes that take place to make CD Library work with SQL Server CE occur in the modGlobal.bas functions AddGenre and AddArtist. Listing 8.31 indicates boldfaced changes to AddGenre. AddArtist follows the exact same changes; if necessary, retrieve it from the sample source code. Listing 8.31 modGlobal.bas: Changes for SQL Server CE Compatibility Function AddGenre(ByVal strGenre As String) As Long ' adds a genre to the table and returns ' its primary key value On Error Resume Next ' set the return value to error code ' in case we need to exit early AddGenre = -1 ' if the recordset is open ' let's check if it supports addnew If gobjGenre.State = adStateOpen Then ' if the current recordset does not ' support adding new records, let's close ' it and reopen it If Not gobjGenre.Supports(adAddNew) Then gobjGenre.Close End If End If ' if the recordset is closed above ' or closed before we get into this ' function, let's open it with the ' proper support for adding If gobjGenre.State = adStateClosed Then gobjGenre.Open "genre", gobjConnection, _ adOpenDynamic, adLockOptimistic, adCmdTableDirect End If ' add a new record gobjGenre.AddNew ' set the values gobjGenre.Fields("GenreName").Value = strGenre ' update the recordset gobjGenre.Update ' set the return value gobjGenre.Close gobjGenre.Open "SELECT MAX(GenreId) AS GenreId FROM Genre", _ gobjConnection, adOpenStatic, adLockOptimistic AddGenre = CLng(gobjGenre.Fields("GenreId").Value) End Function The GetNewPK function has been removed. In the first boldfaced change in Listing 8.31, the CursorType parameter for the Open method has been changed to adOpenDynamic. The second highlighted change made adds code to close the recordset, so it can be reused to find the identity value used for the record in the GenreId field. The identity value is retrieved using the MAX function, and then the return value is set from this result. Let's look at the last supporting function for frmCD that was changed, OpenDatabase (see in Listing 8.32). OpenDatabase is modified to use the SQL Server CE ConnectionString syntax used in the Open method of the ADOCE.Connection gobjConnection. It also makes a call to SetUserId when it opens the database. Listing 8.32 modGlobal.bas: Changes to the OpenDatabase Function for SQL Server CE Compatibility Function OpenDatabase() As Boolean On Error Resume Next ' set the return value to an error ' condition, so we can exit early ' if an error occurs OpenDatabase = False ' we need to check if the connection is ' open or close, because EnsureDatabaseExists may ' opened the database If gobjConnection.State = adStateClosed Then ' opens the SQL Server CE database gobjConnection.Open "Provider=Microsoft.SQLServer.OLEDB.CE.1.0;" _ & "Data Source=" & DB_PATH ' if we're opening the db ' let's set the UserId If Not SetUserId() Then Exit Function End If End If If Err.Number = 0 Then OpenDatabase = True End If End Function modSQLRepl.bas: SetUserId, Setting the User ID The SetUserId function opens the Users table and retrieves the single record in it. Remember, we used the HOST_NAME() function in the publication to filter down the Users table to one record. The Public variable glngCurrentUser is then set equal to the UserId field value. This variable is used when creating new CD records to specify which user they belong to. Listing 8.33 modSQLRepl.bas: Knowing Which User Data Is Being Used Function SetUserId() As Boolean On Error Resume Next ' set to error condition SetUserId = False ' close the record set If gobjUser.State = adStateClosed Then gobjUser.Open "Users", gobjConnection, _ adOpenForwardOnly, adLockReadOnly, adCmdTableDirect End If ' if end of file, there's a problem If gobjUser.EOF Then Exit Function End If ' set the current user - used to populate UserId in cd table glngCurrentUser = CLng(gobjUser.Fields("UserId").Value) ' set the return value SetUserId = Err.Number = 0 End Function frmMain: Wrapping Up the Last Changes There are a few more changes made to the CD Library Replication application. First, remove the call to InitializeGlobals from the frmMain.Load event. Next, add menu commands to the menu to call the synchronization routine and show the frmSQLProperties form. The following Consts are added to the general section of frmMain to support the new menu commands: Const MENU_TOOLS_SYNC_KEY = "TOOLSSYNC" Const MENU_TOOLS_SETTINGS_KEY = "SETTINGS" In Listing 8.34, two highlighted lines of code are added to create a menu command for Sync and Settings. Next, in the MenuBar1.Menu_Click event, you respond to these new prompts. Listing 8.34 frmMain.frm: Adding Menu Commands to the Menu for Sync and Settings Function PopulateMenus() As Boolean Dim objMenu As MenuBarLib.MenuBarMenu Dim objPrompt As MenuBarLib.Item On Error Resume Next PopulateMenus = False ' add tools menu Set objMenu = MenuBar1.Controls.AddMenu("Tools", MENU_ROOTPROMPT_TOOLS_KEY) ' add Sync prompt Set objPrompt = objMenu.Items.Add(, MENU_TOOLS_SYNC_KEY, "Sync") ' add settings prompt Set objPrompt = objMenu.Items.Add(, MENU_TOOLS_SETTINGS_KEY, "Settings") ' check for errors, if none, return True If Err.Number = 0 Then PopulateMenus = True End If End Function Listing 8.35 provides the new code to support the new menu commands. For the Sync menu command, the Sync method is called. After the synchronization is complete, the form is refreshed (there is potentially new data) by calling the PopulateArtistGrid function. The Settings menu command displays the frmSQLProperties form by calling the Show method. Listing 8.35 frmMain.frm: Adding Code to Respond to the New Menus Private Sub MenuBar1_MenuClick(ByVal Item As MenuBarLib.Item) Select Case Item.Key Case MENU_TOOLS_SYNC_KEY Sync PopulateArtistGrid Case MENU_TOOLS_SETTINGS_KEY frmSQLProperties.Show Case Else MsgBox "Unknown Menu!" End Select End Sub  | 
