Export (0) Print
Expand All

Converting an eMbedded Visual Basic 3.0 Application from SQL Server CE 1.x to SQL Server CE 2.0

SQL Server 2000
 

Microsoft Corporation

November 2002

Summary:   Microsoft SQL Server 2000 Windows CE Edition 2.0 (SQL Server CE) uses the same file format and data access options as the previous version. This makes it extremely easy to convert an existing Microsoft eMbedded Visual Basic application to SQL Server CE 2.0. (6 printed pages)

Contents

Introduction
Installing SQL Server CE 2.0
Converting the Application
Converting Local Database Access Code
Converting RDA Synchronization Code
Converting Replication Synchronization Code
Conclusion

Introduction

Microsoft® SQL Server™ 2000 Windows® CE Edition (SQL Server CE) 2.0 uses the same file format and data access APIs as the 1.x version. This allows for an extremely easy conversion to SQL Server CE 2.0. This document will guide you through the changes that need to be made at the application code level to access the local database and to do synchronization. This document will reference both the Microsoft eMbedded Visual Basic® Northwind Remote Data Access (RDA) sample and the eMbedded Visual Basic Northwind Replication application that ship with SQL Server CE 1.0, and convert them to SQL Server CE 2.0.

Installing SQL Server CE 2.0

Before continuing with the actual conversion of the Northwind RDA sample application, the developer must install the SQL Server CE development tools and the SQL Server CE Server components. These are two separate installs and are required to be completed before proceeding with the conversion.

Converting the Application

To convert either the Remote Data Access (RDA) or the merge replication application project to SQL Server CE, the developer needs to change the reference of SQL Server CE 1.0 to SQL Server CE 2.0. In the eMbedded Visual Basic project, choose Project, and then References. The following figure shows what the references look like for SQL Server CE 1.0

Figure 1.   SQL Server CE 1.0 References

To convert the application reference to SQL Server CE 2.0, simply clear the Microsoft CE SQL Server Control 1.0 check box and select the Microsoft CE SQL Server Control 2.0 check box as shown in the following figure.

Figure 2.   SQL Server CE 1.0 References checkboxes

After changing the reference, SQL Server CE 2.0 DLLs will be copied to the device during debugging. If the developer chooses to use the Application Install Wizard, the correct SQL Server CE 2.0 DLLs will be included in the .cab file.

Converting Local Database Access Code

To convert application code that only accesses SQL Server CE database and does not use SQL Server CE sync, the developer must change the OLE DB connection string. Following is the original code from both Northwind database applications that reference SQL Server CE 1.x in the OLE DB connection string.

Figure 3.   SQL Server OLE DB connection string

In order to convert to SQL Server CE 2.0, the user must change the following code

Provider=Microsoft.SQLServer.OLEDB.CE.1.0 to
Provider=Microsoft.SQLServer.OLEDB.CE.2.0

Following is a representation of how the code looks in the editor:

Figure 4.   SQL Server OLE DB connection string in the editor

Notice that the database path is the same for both applications because both SQL Server CE 1.0 and SQL Server CE 2.0 can access and modify the same database.

Note   SQL Server CE 2.0 allows for 249 indexes per table, whereas SQL Server CE 1.0 allowed for only 32 indexes per table. If a SQL Server CE 2.0 database has more than 32 indexes per table, the SQL Server CE 1.0 application cannot access the table.

Converting RDA Synchronization Code

The first code change is in reference to the Remote Data Access (RDA) object. Following is a representation of how the original code looks.

Figure 5.   Remote Data object reference

To convert the Remote Data Access object to version 2.0 the developer needs only to convert the version number from 1.0 to 2.0. Following is a representation of how the code looks after converting.

Figure 6.   Remode Data Access object conversion

The final code to change is in reference to the SQL Server CE Server Agent. This is referenced in the property called InternetURL. The property typically has a value in the form of http://servername/virtualdirectory/SSCESA10.DLL. The Northwind sample takes the HTTP address from user input on the application, so there is no code in this application to convert.

Figure 7.   The InternetURL property

The Northwind Remote Data Access (RDA) sample application stores many of the sync properties in a table. InternetURL is one of those properties. In order to have the application sync successfully, the developer will either need to delete the NorthWindRDA.SDF file from the root directory of the device, or edit the system table and change the column to reference the SSCESA20.DLL. The InternetURL value should be similar to http://servername/virtualdirectory/SSCESA20.DLL.

At this point, the application will successfully run using SQL Server CE 2.0.

Converting Replication Synchronization Code

The first code change is in reference to the replication object. Following is a representation of how original code looks.

Figure 8.   The Replication object

To convert the replication object to version 2.0 the developer needs only to convert the version number from 1.0 to 2.0. Following is a representation of how the code looks after converting.

Figure 9.   Conversion of the Replication object

The final code to change is in reference to the SQL Server CE Server Agent. This is referenced in the property called InternetURL. The property typically has a value in the form of http://servername/virtualdirectory/SSCESA10.DLL. The Northwind sample takes the HTTP address from user input on the application, so there is no code in this application to convert.

Figure 10. Change in reference to the SQL Server CE Server Agent

The Northwind Replication sample application stores many of the sync properties in a table. The InternetURL is one of those properties. In order to have the application sync successfully, the developer must either delete the NorthWindRepl.SDF file from the root directory of the device, or edit the system table and change the column to reference the SSCESA20.DLL. The InternetURL value should be similar to http://servername/virtualdirectory/SSCESA20.DLL.

At this point, the application will successfully run, using SQL Server CE 2.0.

Conclusion

Following is a listing of compatibilities and incompatibilities:

  • SQL Server CE 1.0 and SQL Server CE 2.0 applications can run side-by-side on the same device. The developer can simply save the eMbedded Visual Basic application to another name after converting to SQL Server CE 2.0. This will allow for compatibility testing and the ability to see the performance improvements with SQL Server CE 2.0.
  • SQL Server CE 1.0 server side components and SQL Server CE 2.0 server side components can be installed on the same IIS server. It is recommended that each version be installed in its own virtual directory.
  • An eMbedded Visual Basic application cannot reference both SQL Server CE 1.0 and SQL Server CE 2.0 at the same time.
  • A SQL Server CE 1.0 application can create, access and modify a database that was created with SQL Server CE 2.0, and a SQL Server CE 2.0 application can create, access and modify a database that was created with SQL Server CE 1.0. The exception to this is a SQL Server CE 2.0 application created with more than 32 indexes on a table. This includes referential integrity constraints.
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft