Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Christian Forsberg
Business anyplace
April 2005
Applies to:
.NET Compact Framework version 1.0
Visual Studio .NET 2003
eMbedded Visual Basic
ActiveX Data Objects for Windows CE (ADO CE)
Microsoft SQL Server CE
Windows Mobile software for Pocket PCs
Summary: This article is about migrating Pocket PC applications that are written in eMbedded Visual Basic to the .NET Compact Framework. The migration includes a change from using ADO CE for accessing data in a Pocket Access database to using ADO.NET for accessing data in SQL Server CE. The sample code is supplied in both Visual Basic and C#. (34 printed pages)
Download Database Migration from CEDB to SQL Server CE.msi Northwind Pocket Inventory - Logistics for Windows Mobile-based Pocket PC.msi from the Microsoft Download Center.
Introduction
Pocket Access to SQL Server CE
ADO CE to ADO.NET
eMbedded Visual Basic to Visual Basic .NET and C#
Synchronization
Mobile Sales .NET Sample
Code Walkthrough
Conclusion
Whether you have already decided to move your applications and data from earlier Pocket PC tools (Microsoft eMbedded Visual Basic and Pocket Access) to the current state of technology (Microsoft .NET Compact Framework and Microsoft SQL Server CE), or you are thinking about this decision, you need to research some of the difficult arguments for this move.
The first argument is that your device will gain performance. On a device processor, even the latest and greatest, performance is definitely very important. With your database correctly set up in SQL Server CE (with the appropriate keys, indices, and so on), it will simply outperform Pocket Access — sometimes many times over.
The second argument is that the richer type system in SQL Server CE will let your device store data more efficiently. Everything that you could store in Pocket Access can be stored in SQL Server CE — and more. Because the type system is a clean subset of the type system of SQL Server 2000, server synchronization will also be easier.
The third argument is that the current data–access middleware (ADO.NET) will give you more power to read and manipulate the data itself in addition to manipulating the definition of the data. The new and natively disconnected model (manifested by the DataSet) gives you more freedom in how the data is used. A DataSet can be sent to an XML Web service (when you create an XML Web service with ASP.NET, the DataSet is a natively supported type). You can save a DataSet to the file system as XML (with the XML schema information included or in a separate file) that can be read back into a DataSet again from the same files.
And the last argument is that the richer SQL syntax gives you more options in reading the data (with subqueries, outer joins, functions, and so on) in addition to the data manipulation. Much of the logic that you previously implemented in code after the Recordset was retrieved, and now you can do it directly in the SQL commands — where it belongs. Doing things like formatting the columns in a SELECT statement with LTRIM or a CASE...WHEN...THEN...ELSE...END statement was much more difficult, and most often slower, when done in earlier code.
When the migration from your earlier Pocket PC application involves the migration of both data and code, you perform the migration on three different levels:
- Database
- Database middleware
- Source code
On the database level, you migrate data and code from Pocket Access to SQL Server CE. The main differences are related to the data types that are supported. When you migrate data access code, the difference in the database middleware is mainly related to the objects that are supported and the syntax in using these objects. And when you migrate the source code in general, the differences are related both to the different languages and the class libraries that are supported. The following sections describe the differences among the different levels.
Commonly, the data in earlier Pocket PC applications originates from a desktop computer's Microsoft Access database. In cases where desktop computer replication is required, this data is normally replicated between the desktop computer and the device by means of the built–in synchronization functionality in Microsoft ActiveSync. When the data is migrated to SQL Server CE, the first step is to migrate the existing Access database to SQL Server 2000.
You can perform this migration from within Access by exporting a table by means of an Open Database Connectivity (ODBC) data source that connects to the SQL Server 2000 database. Then, you can replicate the data in SQL Server 2000 to SQL Server CE on the device by using Remote Data Access (RDA). For more details about how this replication is done, please see the "Synchronization" section in this article.
Table 1 shows the mapping of the different data types that each database supports.
Table 1. Data type mappings
| Access data type | Pocket Access type | SQL Server 2000 type | SQL Server CE type |
|---|---|---|---|
| Text | varchar | nvarchar | nvarchar |
| Memo | text | ntext | ntext |
| LongInt | integer | int | int |
| Int, Byte | smallint | smallint | smallint |
| Double, Single | double | float | float |
| Replication ID | varbinary | uniqueidentifier | uniqueidentifier |
| Date/Time | datetime | datetime | datetime |
| Currency | double | money | money |
| AutoNumber | integer | int | int |
| YesNo | boolean | bit | bit |
| OleObject | varbinary | image | image |
| HyperLink | text | ntext | ntext |
| Lookup | varchar | nvarchar | nvarchar |
SQL Server 2000 supports all of the Access data types, and because SQL Server CE supports (without conversion) all of the types that are generated in the export, no information is lost in the migration.
ActiveX Data Objects for Windows CE (ADO CE) was created as the device version of ADO, and the first version (3.0) only supported Pocket Access. When SQL Server CE was created, ADO CE was updated to support it. Although many earlier Pocket PC applications use the first version of ADO CE (3.0) to access Pocket Access databases, the information in this section relates to the latest version (3.1). However, the following code examples should work with ADO CE 3.0 as well.
ADO CE supports the following objects: Recordset, Field (and collection), Connection, and Error (and collection). However, ADO CE does not support the Command object found in the full (desktop) ADO implementation. As an extension to ADO CE, ADO XCE includes the functionality to manage the databases like manipulating tables, fields, and other database objects.
ADO.NET in the .NET Compact Framework is the device version of the full .NET Framework data access middleware. You can find ADO.NET for SQL Server CE, normally referred to as the managed provider for SQL Server CE, in the "System.Data.SqlServerCe" namespace. The most important objects in the managed provider are: SqlCeConnection (for connecting to the database), SqlCeDataAdapter (for accessing data in the database), SqlCeCommandBuilder (for generating SQL commands to save data in the database), SqlCeCommand (for issuing SQL commands to the database), SqlCeDataReader (for fast sequential reading of data from the database), SqlCeEngine (for managing the database), and SqlCeException (for error handling). More information about both ADO CE and ADO.NET can be found in the Microsoft Visual Studio .NET Help file.
There are several important things performed in ADO CE that you can do with ADO.NET. First, you need to open a database connection. In ADO CE, you can open a database as follows.
Dim connection AS ADOCE.Connection
Set connection = CreateObject("ADOCE.Connection.3.1")
connection.ConnectionString = "Data Source=\MyDatabase.cdb"
connection.Open
You can also provide the connection string as a parameter to the connection's Open method. A call to the Close method on the Connection object closes the connection.
If you are using ADO.NET and Visual Basic .NET, you can open a database connection as follows.
Dim connection As SqlCeConnection
connection = New SqlCeConnection("Data Source=\MyDatabase.sdf")
connection.Open()
Similar to ADO CE, the connection's Close method closes the connection.
In C#, the same code looks like the following.
SqlCeConnection connection
connection = new SqlCeConnection(@"Data Source=\MyDatabase.sdf");
connection.Open();
Because it is never too early to think about error handling, you can see how errors were handled in ADO CE. First, you need to set up the error handler as follows
On Error Resume Next
Err.Clear
After the application performs each database command, the error collection (Errors) on the Connection object contains any errors that have occurred. You can have the application report any errors by using the following code.
If (Err.Number <> 0) Then
For i = 1 To connection.Errors.Count
MsgBox connection.Errors(i).Number & " - " & _
connection.Errors(i).Description, vbCritical, "Error"
Next i
Err.Clear
End If
For each error, a message box appears with the error number and error description. When you use a Pocket Access database, the error objects in ADO CE also support properties for the native error (NativeError) and the error source (Source).
In Visual Basic .NET, you handle errors by using the Try…Catch construct. The following is the Visual Basic .NET code to catch and report ADO.NET errors.
Try
' Database code
Catch ex As SqlCeException
If Not ex.InnerException Is Nothing Then
MessageBox.Show("Inner Exception: " + ex.InnerException.ToString())
End If
Dim s As StringBuilder = New StringBuilder()
Dim error As SqlCeError
For Each error In ex.Errors
s.Append("Error Code: " + error.HResult.ToString("X"))
s.Append(vbCrLf + "Message : " + error.Message)
s.Append(vbCrLf + "Minor Err.: " + error.NativeError)
s.Append(vbCrLf + "Source : " + error.Source)
Dim numericErrorParameter As Integer
For Each numericErrorParameter In error.NumericErrorParameters
If numericErrorParameter <> 0 Then
s.Append(vbCrLf + "Num. Par. : " + numericErrorParameter)
End If
Next
Dim errorParameter As String
For Each errorParameter In error.ErrorParameters
If errorParameter.Length > 0 Then
s.Append(vbCrLf + "Err. Par. : " + errorParameter)
End If
Next
MessageBox.Show(s.ToString())
s.Remove(0, s.Length)
Next
End Try
In ADO.NET, the exceptions (errors) that occur include much more information than what was available with ADO CE. This information helps developers find bugs faster and also gives more control in handling each exception. First the application reports any inner exceptions in a message box, and then the application reports (with details) all errors in the collection (Errors) on the exception object (ex) in a message box for each error.
In C#, the code looks like the following.
try
{
// Database code
}
catch (SqlCeException ex)
{
if(ex.InnerException != null)
MessageBox.Show("Inner Exception: " + ex.InnerException.ToString());
StringBuilder s = new StringBuilder();
foreach (SqlCeError error in ex.Errors)
{
s.Append("Error Code: " + error.HResult.ToString("X"));
s.Append("\nMessage : " + error.Message);
s.Append("\nMinor Err.: " + error.NativeError);
s.Append("\nSource : " + error.Source);
foreach (int numericErrorParameter in
error.NumericErrorParameters)
if(numericErrorParameter != 0)
s.Append("\nNum. Par. : " + numericErrorParameter);
foreach (string errorParameter in error.ErrorParameters)
if(errorParameter.Length > 0)
s.Append("\nErr. Par. : " + errorParameter);
MessageBox.Show(s.ToString());
s.Remove(0, s.Length);
}
}
Now that you have a connection and can handle the errors returned, you can start interacting with the database. The most important action is to query the database for data. In ADO CE, you can query the database in two ways: by using the Open method on the Recordset object or by using the Execute method on the Connection object.
The code for using the Recordset object looks like the following.
Dim rs As ADOCE.Recordset
Dim sql As String
sql = "SELECT * FROM Customers"
Set rs = CreateObject("ADOCE.Recordset.3.1")
rs.Open sql, connection, adOpenDynamic, adLockOptimistic
If you used the Connection object, you can replace the last two lines with the following.
Set rs = connection.Execute(sql)
The corresponding Visual Basic .NET code that uses a data adapter looks like the following.
Dim sql As String = "SELECT * FROM Customers"
Dim da As New SqlCeDataAdapter(sql, connection)
Dim ds As New DataSet
da.Fill(ds, "Customers")
In the previous code, a new data adapter object (da) is created by passing the database (SQL) command and the Connection object as parameters to the constructor. Then the DataSet (ds) is filled with the data by using the Fill method on that data adapter. The second parameter to the Fill method set the name of the newly retrieved table in the DataSet. Behind the scenes, the data adapter uses a SqlCeCommand object (in its SelectCommand property) to query the database.
In C#, the code looks like the following.
string sql = "SELECT * FROM Customers";
SqlCeDataAdapter da = new SqlCeDataAdapter(sql, connection);
DataSet ds = new DataSet;
da.Fill(ds, "Customers");
Another way to read data that is available in ADO.NET is the SqlCeDataReader. You can use it to read data sequentially in a very efficient way. When performance is important and when data volumes are high, you should consider this option. The following code shows an example about how to use the SqlCeDataReader.
Dim sql As String = "SELECT * FROM Customers"
Dim cmd As New SqlCeCommand(sql, connection)
Dim dr As SqlCeDataReader = cmd.ExecuteReader()
While dr.Read
ListBox1.Items.Add(dr(1))
End While
When the reader is created from the command object, the second column (dr(1)) is used to add rows to a list box (ListBox1).
In C#, the code looks like the following.
string sql = "SELECT * FROM Customers";
SqlCeCommand cmd = new SqlCeCommand(sql, connection);
SqlCeDataReader dr = cmd.ExecuteReader();
In ADO CE, the only supported way to execute commands that do not return any data is to use the Execute method on the Connection object, and then ignore the return value by using the following code.
connection.Execute("DELETE Customers WHERE CustomerID=1")
You can use the SqlCeCommand object to perform commands in Visual Basic .NET as follows.
Dim cmd As SqlCeCommand = connection.CreateCommand()
cmd.CommandText = "DELETE Customers WHERE CustomerID=1"
cmd.ExecuteNonQuery()
In C#, the code looks like the following.
SqlCeCommand cmd = connection.CreateCommand();
cmd.CommandText = "DELETE Customers WHERE CustomerID=1";
cmd.ExecuteNonQuery();
You can also create the command object by supplying the command text and the connection to the constructor.
The command object has the ExecuteScalar method, which you can use when you need only a single value to be returned. If the command returns only one row with one column, you can use the following code.
Dim cmd As SqlCeCommand = connection.CreateCommand()
cmd.CommandText = "SELECT COUNT(*) FROM Customers"
Dim numberOfCustomers As Integer = cmd.ExecuteScalar()
In C#, the code looks like the following.
SqlCeCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM Customers";
int numberOfCustomers = (int)cmd.ExecuteScalar();
The command object also supports parameters (however, it does not support named parameters), which you may find very useful for using commands more than once because parameterized queries avoid the need to repeatedly calculate the query plan and, therefore, notably improve the overall performance of commands issued more then once.
You can also perform data manipulation by using SQL insert, update, and delete commands with the Execute method on the Connection object in ADO CE. You can manipulate data by using these commands with the SqlCeCommand object in ADO.NET with good performance. However, you have to handle the SQL syntax manually, which can sometimes be cumbersome. Both ADO CE and ADO.NET have more easily used constructs for doing data manipulation.
In ADO CE, you need to open a Recordset in a specific way to allow data manipulation. The command that you provide can include only a table name, and you need to supply a specific last parameter. The following is an example of code to add, update, and delete rows in ADO CE.
Dim rs As ADOCE.Recordset
Set rs = CreateObject("ADOCE.Recordset.3.1")
rs.Open "Customers", connection, adOpenDynamic, _
adLockOptimistic, adCmdTableDirect
' INSERT
rs.AddNew
rs("CustomerID").Value = 1
rs("CompanyName").Value = "My Company"
rs.Update
' UPDATE
rs.Find "CustomerID=2"
rs("CompanyName").Value = "Modified Name"
rs.Update
' DELETE
rs.Find "CustomerID=3"
If Not rs.EOF Then rs.Delete
rs.Close
The application opens a Recordset that contains the Customers table with the parameter for direct table manipulation (adCmdTableDirect). Then, you can manipulate data by using the Recordset's built–in functionality for adding new rows (AddNew), updating added or changed rows (Update), and removing rows (Delete).
The corresponding code in Visual Basic .NET looks like the following.
Dim da As New SqlCeDataAdapter("SELECT * FROM Customers", _
connection)
Dim cb As New SqlCeCommandBuilder(da)
da.InsertCommand = cb.GetInsertCommand()
da.UpdateCommand = cb.GetUpdateCommand()
da.DeleteCommand = cb.GetDeleteCommand()
Dim ds As DataSet = New DataSet
da.Fill(ds, "Customers")
' INSERT
Dim dr As DataRow = ds.Tables("Customers").NewRow()
dr("CustomerID") = 1
dr("CustmerName") = "My Company"
ds.Tables("Customers").Rows.Add(dr)
' UPDATE
dr = ds.Tables("Customers").Select("CustomerID=2")(0)
dr("CustomerName") = "Modified Name"
' DELETE
dr = ds.Tables("Customers").Select("CustomerID=3")(0)
dr.Delete()
da.Update(ds, "Customers")
Here, the application first initializes a new SqlCeDataAdapter by passing the constructor the select command and the connection. You can use the SqlCeCommandBuilder object to generate the necessary data manipulation commands (insert, update, and delete) from the select command in the data adapter. These commands are then set in the data adapter, which uses them to manipulate the data later when the changes that were made to the rows in the DataSet need to be saved in the database. Note how the ADO.NET model is more disconnected with the data adapter; it acts as the intelligent link between the completely disconnected DataSet and the database. The DataSet does not require any connection to the database, and after you fill the DataSet with data, you can close the data adapter. You can then manipulate the DataSet, and after you've completed the manipulation, you can create a new data adapter to update the database. Because of the new data manipulation model, you can do much more with ADO.NET than what was ever possible with ADO CE.
In C#, the same code looks like the following.
SqlCeDataAdapter da = new SqlCeDataAdapter(
"SELECT * FROM Customers", connection);
SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);
da.InsertCommand = cb.GetInsertCommand();
da.UpdateCommand = cb.GetUpdateCommand();
da.DeleteCommand = cb.GetDeleteCommand();
DataSet ds = new DataSet();
da.Fill(ds, "Customers");
// INSERT
DataRow dr = ds.Tables["Customers"].NewRow();
Dr["CustomerID"] = 1;
Dr["CustomerName"] = "My Company";
ds.Tables["Customers"].Rows.Add(dr);
// UPDATE
dr = ds.Tables["Customers"].Select("CustomerID=2")[0];
dr["CustomerName"] = "Modified Name";
// DELETE
dr = ds.Tables["Customers"].Select("CustomerID=3")[0];
dr.Delete();
da.Update(ds, "Customers");
So far, this article has covered how you can read and manipulate data. Next, it will cover some of the differences in SQL support in the two database middleware implementations. Actually, ADO CE implemented the SQL support in the earlier tools, and even if ADO.NET had some support for SQL, SQL Server CE itself supports most of the SQL syntax.
As a Pocket PC programmer, you will soon discover the richness of the SQL support in the new tools — in both the data manipulation language (DML) and data definition language (DDL) parts of SQL. For example, in ADO CE, when you need to know the number of rows in a table, you have to open a Recordset object with all of the actual rows loaded from the table. In ADO.NET, you can simply query a database table for the number of rows (SELECT COUNT(*) FROM Customers) and get the result in a single value (with the ExecuteScalar method on the SqlCeCommand object).
To help you visualize the extended support for SQL with the new tools, Figure 1 lists the reserved words in ADO CE and SQL Server CE.
.gif)
Figure 1. SQL reserved words in ADO CE and SQL Server CE. Click the thumbnail for a larger image.
The left column contains the reserved words in ADO CE, and the six columns on the right contain the reserved words in the SQL Server CE. For more information, you can browse the SQL Reference in the SQL Server CE Help file (named Books Online) that is also included in the Visual Studio .NET Help.
The article Moving from eMbedded Visual Basic to Visual Basic .NET covers most of the general issues about moving from eMbedded Visual Basic to Visual Basic .NET. The "Working with Databases" section of that article contains sample code that describes how to migrate code from eMbedded Visual Basic to Visual Basic .NET when you are using SQL Server CE.
This article presents the migrated code both in Visual Basic .NET and C#. The main reason is that a larger audience of developers can instantly benefit from the sample code. Also, if you are a traditional eMbedded Visual Basic developer who is beginning to use the .NET Compact Framework, you should also consider C#. The main reason to consider C# is that because there are so many new things in .NET, you may find it helpful to get a fresh start without the Visual Basic heritage.
Many earlier Pocket PC applications synchronize data from the desktop computer to the device by means of ActiveSync. ActiveSync includes some support for you to synchronize databases and select which tables you want to include in the synchronization. There is no native support in ActiveSync for synchronizing Microsoft Access or SQL Server 2000 databases to SQL Server CE databases on the device. However, you can use ActiveSync to allow the device to connect to the desktop computer by means of a pass-through connection.
With SQL Server CE, you need to use a different model. SQL Server 2000 supports synchronization as a plug–in to the Web server. A virtual directory in Internet Information Services (IIS) is set up during the installation of SQL Server CE Server Tools. This virtual directory contains a file (sscesa20.dll) known as the SQL Server CE Server Agent, which you can call through an HTTP request to synchronize data with SQL Server 2000. On the client side, you use the SQL Server CE Client Agent, which includes functionality for merge replication (setting up data subscriptions) and for Remote Data Access (RDA). For more information about merge replication, please see the SQL Server CE Help (Books Online); this article focuses on how to synchronize data by using RDA.
The upcoming sample, on which most of the code samples in this article are based, uses a database that originates from Access. The Access database was exported to SQL Server 2000, table by table, with the functionality provided in Access to export to an ODBC data source — in this case, a SQL Server 2000 database. (For details about how to export an Access database, please see Access Help or How To Convert an Access Database to SQL Server.) The next step is to download the tables from SQL Server 2000 to a SQL Server CE database by using the pull method in RDA, as shown in the following code.
Dim localDatabase As String = "\MobileSales.sdf"
Dim localConnectionString As String = "Data Source=" + localDatabase
Dim remoteConnectionString As String = "Provider=sqloledb;" + _
"Data Source=(local);Initial Catalog=MobileSales;" + _
"Integrated Security=SSPI;"
Dim tables() As String =
New String() {"Customers", "Orders", "Products"}
If File.Exists(localDatabase) Then
File.Delete(localDatabase)
End If
Dim engine As New SqlCeEngine(localConnectionString)
engine.CreateDatabase()
engine.Dispose()
Dim rda As New SqlCeRemoteDataAccess()
rda.InternetUrl = "https://server/ssce/sscesa20.dll"
rda.LocalConnectionString = localConnectionString
Dim table As String
For Each table In tables
Try
rda.Pull(table, "SELECT * FROM [" + table + "]", _
remoteConnectionString, _
RdaTrackOption.TrackingOffWithIndexes, "ErrorTable")
Catch ex As SqlCeException
MessageBox.Show(ex.Message);
End Try
Next
rda.Dispose()
The local connection string points to where the SQL Server CE database file will be created, and the remote connection string points to the SQL Server 2000 database (MobileSales) from which the application will retrieve data. If the database file already exists, the application deletes it, and then the application creates the database by using a SqlCeEngine object. The application sets up the RDA (SqlCeRemoteDataAccess) object to point to the virtual directory (ssce) on the server or desktop computer where the SQL Server CE Server Agent (sscesa20.dll) resides. The application uses the array of tables to pull the tables from SQL Server 2000 to the newly created local SQL Server CE database.
Note that the virtual directory uses anonymous access, and with the specified connection string, the anonymous user in IIS (for example, IUSR_MACHINENAME) is given the rights to access the database in SQL Server 2000. In a real–world scenario, the virtual directory would probably require a specified user, and the credentials are then set on the RDA object (in its InternetLogin and InternetPassword properties).
Note also that the application specifies the option to download indices and to turn off tracking (TrackingOffWithIndexes). If tracking is specified, the application can later use the RDA object's Push method to push back the tables to the server so that the changes are added to the SQL Server 2000 database. For more information, please see the SQL Server CE Help (Books Online).
In C#, the code looks like the following.
string localDatabase = @"\MobileSales.sdf";
string localConnectionString = "Data Source=" + localDatabase;
string remoteConnectionString = @"Provider=sqloledb;" +
"Data Source=(local);Initial Catalog=MobileSales;" +
"Integrated Security=SSPI;";
string[] tables = new string[] { "Customers", "Orders", "Products" };
if(File.Exists(localDatabase))
File.Delete(localDatabase);
using(SqlCeEngine engine = new SqlCeEngine(localConnectionString))
engine.CreateDatabase();
using(SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess())
{
rda.InternetUrl = "https://server/ssce/sscesa20.dll";
rda.LocalConnectionString = localConnectionString;
foreach (string table in tables)
{
try
{
rda.Pull(table, "SELECT * FROM [" + table + "]", remoteConnectionString,
RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");
}
catch (SqlCeException ex)
{
//MessageBox.Show(ex.Message);
showErrors(ex);
}
}
}
Instead of using the preceding code, you might want to continue to synchronize with an Access database on the desktop computer, or you might want to replicate with another database. The general solution is to set up an XML Web service on the server (or desktop computer). The Web service can publish methods to be consumed by the device that perform the data manipulation in the database.
The following code example uses the original Access database from an XML Web service (created as an ASP.NET Web service project in Visual Studio .NET).
<WebMethod> _
Public Function Sync(ByVal newCustomerNames() As String) As DataSet
' Set up connection and data adapter
OleDbConnection cn = New OleDbConnection(+
"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;" +
"Data Source=C:\Mobile Sales.mdb")
cn.Open()
' Set up command and insert new customers
Dim cmd As New OleDbCommand(
"INSERT INTO Customers (CustomerName) VALUES(?)", cn)
cmd.Parameters.Add("?", DbType.String)
For Each newCustomerName As String In newCustomerNames
cmd.Parameters(0).Value = newCustomerName
cmd.ExecuteNonQuery
Next
' Get all customers
Dim da As New OleDbDataAdapter("SELECT * FROM Customers", cn)
Dim ds As DataSet = New DataSet()
da.Fill(ds, "Customers")
' Send back all customers
Return ds
End Function
The XML Web service method (Web method) takes an array of new customer's names as parameters. After the connection and command objects are set up, the application uses this array to create new customer rows. Note how the code uses the parameters to speed up inserting several customers. Also note that the syntax for doing this action with command objects on the device (with ADO.NET in .NET Compact Framework) is very similar. Then the application queries the database again, and all of the customer names (including the new ones) are returned to the device. Note that this is synchronization functionality and all of the customers need to be returned to the device because other devices may have added customers while this device was disconnected.
In C#, the code looks like the following.
[WebMethod]
public DataSet Sync(string[] newCustomerNames)
{
// Set up connection and data adapter
OleDbConnection cn = new OleDbConnection( +
"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;" +
@"Data Source=C:\Mobile Sales.mdb");
cn.Open();
// Set up command and insert new customers
OleDbCommand cmd = new OleDbCommand(
"INSERT INTO Customers (CustomerName) VALUES(?)", cn);
cmd.Parameters.Add("?", DbType.String);
foreach(string newCustomerName in newCustomerNames)
{
cmd.Parameters[0].Value = newCustomerName;
cmd.ExecuteNonQuery();
}
// Get all customers
OleDbDataAdapter da = new OleDbDataAdapter(
"SELECT * FROM Customers", cn);
DataSet ds = new DataSet();
da.Fill(ds, "Customers");
// Send back all customers (including new)
return ds;
}
In a similar way, the application can access any OLE DB–compliant data source that has ADO.NET on the server — or another database that has its own managed provider (for example, Oracle).
The Mobile Sales .NET sample demonstrates how an application that is written in eMbedded Visual Basic, and that uses ADO CE to access data in a Pocket Access database, can be migrated to the .NET Compact Framework so that it uses ADO.NET to access data in a SQL Server CE database. The sample accompanies the article Data To Go written by Larry Roof in 2001.
The Mobile Sales .NET sample was created in Visual Studio .NET 2003. It consists of a single form, as shown in Figure 2.
.gif)
Figure 2. Mobile Sales .NET
The scenario is a delivery driver who is visiting a number of customers. At each stop, he takes an order. For each order, he selects a product, enters a quantity, and then taps Add. If the driver makes a mistake, he selects the item row, and then taps Delete. To save the order, the driver taps the Save this order menu command, and then he can continue with the next order. When the driver saves all customer orders, he exits the application. This sample works in the same way as the original eMbedded Visual Basic sample (Data To Go).
When the application starts, it runs the following code. In the original eVB sample, this code was in the Load event of the form.
Dim cnMobileSales As ADOCE.Connection
Private Sub Form_Load()
' Set up an error handler.
' NOTE: eMbedded Visual Basic supports only the On Error Resume
' Next method.
On Error Resume Next
Err.Clear
' Build the menu bar.
ConstructMenuBar
' Make a connection to the database.
Set cnMobileSales = CreateObject("ADOCE.Connection.3.0")
cnMobileSales.Open "data source=" & App.Path & "\Mobile Sales.cdb"
' Was the connection successful?
If cnMobileSales.Errors.Count > 0 Then
MsgBox "Error connecting to database." & vbCrLf & _
"Error " & Err.Number & vbCrLf & _
Err.Description, _
vbCritical, "Open Database"
Set cnMobileSales = Nothing
On Error GoTo 0
App.End
End If
' Load the customer and product information.
LoadComboBox cmbCustomers, "Customers", "CustomerID", _
"CustomerName", True
LoadComboBox cmbProducts, "Products", "ProductID", _
"Description", False
' Set up the selected product's list view control.
FormatListView
End Sub
As shown in the first line of the preceding code, the connection (cnMobileSales) is declared as a form variable of type ADOCE.Connection. The application creates an ADO CE Connection object and opens (Open) the object. The data source of the connection string is set to the Pocket Access database file located in the same directory as the application. Then, the application checks whether any errors occurred during the attempt to open the database. If any errors occurred (enumerated in the Errors collection on the Connection object), the first error appears in a message box, and the application closes. Then, two method calls (LoadComboBox) load data into the two ComboBox controls.
Note that two method calls belong in the design of the form: the design of the MenuBar control (ConstructMenuBar), and then the design of the ListView control (FormatListView). For details, please see the original eMbedded Visual Basic sample code.
When migrated, the code in Visual Basic .NET for the form's Load event looks like the following.
Private cn As SqlCeConnection
Private Sub MainForm_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Try
' Make the connection to the database
cn = New SqlCeConnection( _
"Data Source=" + appPath + "\MobileSales.sdf")
cn.Open()
Catch ex As SqlCeException
MessageBox.Show(ex.Message, Me.Text)
cn.Close()
Me.Close()
End Try
' Load the customer and product information.
loadComboBox(customerComboBox, "Customers", "CustomerID", _
"CustomerName", True)
loadComboBox(productComboBox, "Products", "ProductID", _
"Description", False)
End Sub
The error handling in the preceding code is highly structured. An exception is raised if an error occurs while the application tries to connect to the database, just as any other error is handled in the .NET Compact Framework. If you need more details about the exception, you can examine the error collection (Errors) on the exception object (ex) (for details, see the earlier "ADO CE to ADO.NET" section). The first line of the preceding code declares the connection (cn) as a class member variable of type System.Data.SqlServerCe.SqlCeConnection. The application passes the connection string to the constructor of the Connection object that also opens the connection to the database. Note that the design of the MainMenu (adding menu options) and ListView (adding columns) controls is made in the forms designer, whereas in earlier tools, you needed extensive code.
In C#, the same code looks like the following.
private SqlCeConnection cn;
private void MainForm_Load(object sender, System.EventArgs e)
{
try
{
// Make the connection to the database
cn = new SqlCeConnection(
@"Data Source=" + appPath + @"\MobileSales.sdf");
cn.Open();
}
catch (SqlCeException ex)
{
MessageBox.Show(ex.Message, this.Text);
cn.Close();
this.Close();
}
// Load the customer and product information
loadComboBox(customerComboBox, "Customers", "CustomerID",
"CustomerName", true);
loadComboBox(productComboBox, "Products", "ProductID",
"Description", false);
}
In the original eMbedded Visual Basic sample, the code for loading the data into the ComboBox controls is as follows.
Private Sub LoadComboBox(cmbObject As ComboBox, _
ByVal strTableName As String, _
ByVal strIDColumn As String, _
ByVal strNameColumn As String, _
ByVal bolSelect As Boolean)
Dim rsTable As ADOCE.Recordset
Dim strSQL As String
' Set up error handling.
On Error Resume Next
Err.Clear
' Build a RecordSet of all the information.
strSQL = "SELECT * FROM " & strTableName
Set rsTable = CreateObject("ADOCE.Recordset.3.0")
rsTable.Open strSQL, cnMobileSales, adOpenDynamic, _
adLockOptimistic
If (Err.Number) Then
MsgBox "Error retreiving " & strTableName & _
" information. ", vbCritical, "Fill ComboBox"
On Error GoTo 0
Exit Sub
End If
' Place the information into the combo box.
Do While Not (rsTable.EOF)
' Place name in combo box.
cmbObject.AddItem rsTable(strNameColumn).Value
' Place the ID number into the itemdata element for the
' name that was just added.
cmbObject.ItemData(cmbObject.NewIndex) = _
CLng(rsTable(strIDColumn).Value)
' Attempt to move to the next record.
rsTable.MoveNext
Loop
' Select the first item in the list.
If (cmbObject.ListCount > 0) And (bolSelect = True) Then
cmbObject.ListIndex = 0
End If
' Close the recordset.
rsTable.Close
Set rsTable = Nothing
On Error GoTo 0
End Sub
After the setup of error handling, the application loads all the rows in the table (strTableName) into ADOCE.RecordSet (rsTable). Then, the application loops the rows in the RecordSet, and for each row, the application inserts an item in the ComboBox control (cmbObject). Each inserted item also gets the identity that is attached to the ItemData property.
When migrated, the same code for Visual Basic .NET looks like the following.
Private Sub loadComboBox(ByVal comboBox As ComboBox, _
ByVal tableName As String, ByVal valueColumnName As String, _
ByVal displayColumnName As String, ByVal selectFirst As Boolean)
Try
Dim sql As String = "SELECT " + valueColumnName + ", " + _
displayColumnName + " FROM " + tableName + _
" ORDER BY " + displayColumnName
Dim da As New SqlCeDataAdapter(sql, cn)
Dim ds As New DataSet
da.Fill(ds, tableName)
comboBox.DisplayMember = displayColumnName
comboBox.ValueMember = valueColumnName
comboBox.DataSource = ds.Tables(tableName)
Catch ex As SqlCeException
MessageBox.Show("Could not load " + tableName + " (" + _
ex.Message + ")!", "Fill ComboBox")
End Try
End Sub
The approach in the .NET Compact Framework is somewhat different. Although you can add custom items to the ComboBox control, the more common approach is to use data binding. Data binding uses a SqlCeDataAdapter object (da) to load a DataSet object (ds) with the rows of the table (tableName). Then, the you can set the ComboBox property to select which column in the table to display (DisplayMember) and also which column in the table to use for retrieving the currently selected value (ValueMember). Finally, the ComboBox is bound to the only table in the DataSet.
In C#, the same code looks like the following.
private void loadComboBox(ComboBox comboBox, string tableName,
string valueColumnName, string displayColumnName,
bool selectFirst)
{
try
{
string sql = "SELECT " + valueColumnName + ", " +
displayColumnName + " FROM " + tableName +
" ORDER BY " + displayColumnName;
SqlCeDataAdapter da = new SqlCeDataAdapter(sql, cn);
DataSet ds = new DataSet();
da.Fill(ds, tableName);
comboBox.DisplayMember = displayColumnName;
comboBox.ValueMember = valueColumnName;
comboBox.DataSource = ds.Tables[0];
}
catch (SqlCeException ex)
{
MessageBox.Show("Could not load " + tableName + " (" +
ex.Message + ")!", "Fill ComboBox");
}
}
Now that you have set up the form and loaded the ComboBox controls, you are ready to add items to the order. In the original eMbedded Visual Basic sample, the code for adding an order item to the ListView control is as follows.
Private Sub cmdAdd_Click()
Dim curProductCost As Currency
Dim lngProductID As Long
Dim objDetails
' Make sure the user has selected a product.
If (cmbProducts.ListIndex < 0) Then
MsgBox "You must select a product before adding it" & _
" to the order.", _
vbInformation, _
"Add Product"
Exit Sub
End If
' Make sure the quantity is valid.
If (CInt(txtQuantity.Text) <= 0) Then
MsgBox "You must enter a quantity of 1 or more" & _
" before adding it to the order.", _
vbInformation, _
"Add Product"
Exit Sub
End If
' Add the current item to the list.
Set objDetails = lvwOrder.ListItems.Add(, , _
cmbProducts.List(cmbProducts.ListIndex))
' Get information about the current product and
' add this information to the current row (the row
' that was just added).
GetProductInfo cmbProducts.ItemData(cmbProducts.ListIndex), _
curProductCost
objDetails.SubItems(1) = curProductCost
objDetails.SubItems(2) = txtQuantity.Text
objDetails.SubItems(3) = cmbProducts.ItemData(cmbProducts.ListIndex)
' Reset the product fields.
cmbProducts.ListIndex = -1
txtQuantity.Text = "0"
hscQuantity.Value = 0
End Sub
The application checks to ensure that a product has been selected in the product's ComboBox control (cmbCustomer), and that a valid quantity is entered. Then, the application adds an item to the ListView control, with the product name as the first column. The application retrieves the price of the selected product by using a method call (GetProductInfo), and then it updates the other columns (price, quantity and product identity) of the new ListView item. Finally, the application resets the form controls so that it is ready for the next order item to be entered.
When migrated, the same code for Visual Basic .NET looks like the following.
Private Sub addButton_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles addButton.Click
' Make sure the user has selected a product.
If productComboBox.SelectedIndex < 0 Then
MessageBox.Show("You must select a product before adding it" + _
" to the order.", "Add Product")
Return
End If
' Create a new list row.
Dim lvi As ListViewItem = New ListViewItem(productComboBox.Text)
' Get information about the current product and
' add this information to the new row.
Dim curProductCost As Double = getProductInfo( _
Convert.ToInt32(productComboBox.SelectedValue))
lvi.SubItems.Add(String.Format("{0:##0.00}", curProductCost))
lvi.SubItems.Add(quantityNumericUpDown.Value.ToString())
lvi.SubItems.Add(productComboBox.SelectedValue.ToString())
' Add the new row to the list.
orderListView.Items.Add(lvi)
' Reset the product fields.
productComboBox.SelectedIndex = -1
quantityNumericUpDown.Value = 1
End Sub
You can use the ListView control in the .NET Compact Framework to add items (ListViewItem) to the list. Therefore, an item is first created with all the columns added before it is added to the ListView control. Note that the initial value of the quantity field is set to 1 (in contrast to the original eMbedded Visual Basic sample code) because this value is more probable.
In C#, the same code looks like the following.
private void addButton_Click(object sender, System.EventArgs e)
{
// Make sure the user has selected a product
if(productComboBox.SelectedIndex < 0)
{
MessageBox.Show("You must select a product before adding it" +
" to the order.", "Add Product");
return;
}
// No need to make sure the quantity is valid because the
// NumericUpDown control takes care of that
// Create a new list row
ListViewItem lvi = new ListViewItem(productComboBox.Text);
// Get information about the current product and
// add this information to the new row
double curProductCost = getProductInfo(Convert.ToInt32(
productComboBox.SelectedValue));
lvi.SubItems.Add(string.Format("{0:##0.00}", curProductCost));
lvi.SubItems.Add(quantityNumericUpDown.Value.ToString());
lvi.SubItems.Add(productComboBox.SelectedValue.ToString());
// Add the new row to the list
orderListView.Items.Add(lvi);
// Reset the product fields
productComboBox.SelectedIndex = -1;
quantityNumericUpDown.Value = 1;
}
In the original eMbedded Visual Basic sample, the code for getting the product information looks like the following.
Private Sub GetProductInfo(lngProductID As Long, _
curProductCost As Currency)
Dim rsProduct As ADOCE.Recordset
Dim strSQL As String
' Setup an error handler.
On Error Resume Next
Err.Clear
' Find the record of the specified product.
strSQL = "SELECT Cost FROM Products " & _
"WHERE ProductID = " & lngProductID
Set rsProduct = cnMobileSales.Execute(strSQL)
' Check to see if an error occurred.
If (Err.Number <> 0) Then
MsgBox "An error was encountered while attempting" & _
" to retrieve the product info." & vbCrLf & _
"Error #" & Err.Number & ", " & _
Err.Description, vbCritical, "Database Error"
curProductCost = 0
Else
curProductCost = rsProduct.Fields("Cost")
End If
' Clean up before exiting.
rsProduct.Close
Set rsProduct = Nothing
On Error GoTo 0
End Sub
The application retrieves the product ADOCE.Recordset (rsProduct), and if no error occurs, the application returns the price of the product.
When migrated, the same code for Visual Basic .NET looks like the following.
Private Function getProductInfo(ByVal productID As Integer) As Double
Dim curProductCost As Double
Try
' Find the record of the specified product.
Dim sql As String = "SELECT Cost FROM Products" + _
" WHERE ProductID = " + productID.ToString()
Dim cmd As SqlCeCommand = New SqlCeCommand(sql, cn)
curProductCost = Convert.ToDouble(cmd.ExecuteScalar())
Catch ex As SqlCeException
MessageBox.Show("An error was encountered while attempting" + _
" to retrieve the product info." + vbCrLf + _
"Error: " + ex.Message + "!", "Database Error")
curProductCost = 0
End Try
Return curProductCost
End Function
ADO.NET can use a SqlCeCommand object to get a result that contains only one value. The application uses that construct (ExecuteScalar) to get the product price.
In C#, the same code looks like the following. Note that the function returns the cost in the Visual Basic .NET code rather than an updated parameter, making the code easier to read and follow.
private double getProductInfo(int productID)
{
double curProductCost;
try
{
// Find the record of the specified product
string sql = "SELECT Cost FROM Products" +
" WHERE ProductID = " + productID.ToString();
SqlCeCommand cmd = new SqlCeCommand(sql, cn);
curProductCost = Convert.ToDouble(cmd.ExecuteScalar());
}
catch (SqlCeException ex)
{
MessageBox.Show("An error was encountered while attempting" +
" to retrieve the product info.\r\n" +
"Error: " + ex.Message + "!", "Database Error");
curProductCost = 0;
}
return curProductCost;
}
Although not shown in the preceding code, items can also be removed from the order. For details, you can read the sample code on the Click event of the deleteButton.
Now that the items are added to (and possibly removed from) the order, you can save the order. The code for saving the new order from the original eMbedded Visual Basic sample is as follows.
Private Sub SaveOrder()
' This routine saves this current order to the database.
Dim curProductCost As Currency
Dim intCounter As Integer
Dim intErrors As Integer
Dim lngProductID As Long
Dim strSQL As String
' Set up an error handler.
On Error Resume Next
Err.Clear
' Store the individual items for this order.
For intCounter = 1 To lvwOrder.ListItems.Count
' Place the information into the database.
strSQL = "INSERT INTO Orders (CustomerID, ProductID," & _
" Quantity) VALUES (" & cmbCustomers.ItemData(cmbCustomers.ListIndex) & ", " & _
lvwOrder.ListItems(intCounter).SubItems(3) & ", " & _
lvwOrder.ListItems(intCounter).SubItems(2) & ")"
cnMobileSales.Execute strSQL
' Check to see if an error occurred.
If (Err.Number <> 0) Then
For intErrors = 1 To cnMobileSales.Errors.Count
MsgBox cnMobileSales.Errors(intErrors).Number & _
" - " & _
cnMobileSales.Errors(intErrors).Description, _
vbCritical, _
"Save Order"
Next intErrors
Err.Clear
On Error GoTo 0
Exit Sub
End If
Next intCounter
' Clean up before exiting.
lvwOrder.ListItems.Clear
cmbCustomers.RemoveItem cmbCustomers.ListIndex
cmbCustomers.Refresh
If (cmbCustomers.ListCount > 0) Then
cmbCustomers.ListIndex = 0
Else
MsgBox "All customers have been processed.", _
vbInformation, "Save Order"
App.End
End If
cmbProducts.ListIndex = -1
txtQuantity.Text = "0"
MsgBox "The order has been saved.", vbInformation, _
"Save Order"
On Error GoTo 0
End Sub
For each row in the ListView, the application executes an SQL insert statement (Execute) by using the connection object (cnMobileSales) to insert a new order item. The application retrieves the customer identity from the ComboBox control and the product identity and quantity from each row in the ListView. When all the order items are added to the database, the application removes the customer from the ComboBox control and resets the form for the next order. After the last order, the application closes.
When migrated, the same code for Visual Basic .NET looks like the following.
Private Sub saveMenuItem_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles saveMenuItem.Click
' Store the individual items for this order.
Dim lvi As ListViewItem
For Each lvi In orderListView.Items
Try
Dim sql As String = "INSERT INTO Orders (CustomerID," + _
" ProductID, Quantity) VALUES (" + _
customerComboBox.SelectedValue.ToString() + ", " + _
lvi.SubItems(3).Text + ", " + _
lvi.SubItems(2).Text + ")"
Dim cmd As New SqlCeCommand(sql, cn)
cmd.ExecuteNonQuery()
Catch ex As SqlCeException
MessageBox.Show("An error was encountered while attempting" + _
" to save the order." + vbCrLf + _
"Error: " + ex.Message + "!", "Save Order")
End Try
Next
' Clean up before exiting.
orderListView.Items.Clear()
Dim dt As DataTable = (CType(customerComboBox.DataSource, DataTable))
dt.Rows(customerComboBox.SelectedIndex).Delete()
dt.AcceptChanges()
If dt.Rows.Count > 0 Then
customerComboBox.SelectedIndex = 0
Else
MessageBox.Show("All customers have been processed.", _
"Save Order")
Me.Close()
Return
End If
productComboBox.SelectedIndex = -1
quantityNumericUpDown.Value = 1
MessageBox.Show("The order has been saved.", "Save Order")
End Sub
The .NET Compact Framework uses a SqlCeCommand object's method for executing SQL statements. The method does not return any results (ExecuteNonQuery). Note that in .NET Compact Framework, the DataTable manages the data instead of relying on the user interface as a data store; this was the case in the original eMbedded Visual Basic implementation. Having the DataTable manage the data makes the code far more robust because it keeps data storage separate from the data display.
In C#, the same code looks like the following.
private void saveMenuItem_Click(object sender, System.EventArgs e)
{
// Store the individual items for this order
foreach(ListViewItem lvi in orderListView.Items)
{
try
{
string sql = "INSERT INTO Orders (CustomerID, ProductID," +
" Quantity) VALUES (" +
customerComboBox.SelectedValue.ToString() + ", " +
lvi.SubItems[3].Text + ", " +
lvi.SubItems[2].Text + ")";
SqlCeCommand cmd = new SqlCeCommand(sql, cn);
cmd.ExecuteNonQuery();
}
catch (SqlCeException ex)
{
MessageBox.Show("An error was encountered while attempting" +
" to save the order.\r\n" +
"Error: " + ex.Message + "!", "Save Order");
}
}
// Clean up before exiting
orderListView.Items.Clear();
DataTable dt = ((DataTable)customerComboBox.DataSource);
dt.Rows[customerComboBox.SelectedIndex].Delete();
dt.AcceptChanges();
if(dt.Rows.Count > 0)
customerComboBox.SelectedIndex = 0;
else
{
MessageBox.Show("All customers have been processed.",
"Save Order");
this.Close();
return;
}
productComboBox.SelectedIndex = -1;
quantityNumericUpDown.Value = 1;
MessageBox.Show("The order has been saved.", "Save Order");
}
As already shown, you can use a standard SQL insert statement to insert a row into the database. The same is true for SQL update and delete statements. However, the ADOCE.Recordset object generates the necessary SQL statements before updating the database. Although not included in the original eMbedded Visual Basic sample, the following code shows an alternative way of adding a new row in eMbedded Visual Basic by using ADO CE.
Dim rs As ADOCE.Recordset
Set rs = CreateObject("ADOCE.Recordset.3.0")
rs.Open "Orders", cnMobileSales, adOpenDynamic, _
adLockOptimistic, adCmdTableDirect
rs.AddNew
rs("CustomerID").Value = cmbCustomers.ItemData(cmbCustomers.ListIndex)
rs("ProductID").Value = lvwOrder.ListItems(intCounter).SubItems(3)
rs("Quantity").Value = lvwOrder.ListItems(intCounter).SubItems(2)
rs.Update
rs.Close
To update the Recordset, you need to specify the table name (not a SQL statement) that has a special parameter (adCmdTableDirect).
The equivalent Visual Basic .NET code looks like the following.
Dim da As New SqlCeDataAdapter("SELECT * FROM Orders", cn)
Dim cb As New SqlCeCommandBuilder(da)
da.InsertCommand = cb.GetInsertCommand()
da.UpdateCommand = cb.GetUpdateCommand()
da.DeleteCommand = cb.GetDeleteCommand()
Dim ds As DataSet = New DataSet
da.Fill(ds, "Orders")
Dim dr As DataRow = ds.Tables("Orders").NewRow()
dr("CustomerID") = Convert.ToInt32(customerComboBox.SelectedValue)
dr("ProductID") = Convert.ToInt32(lvi.SubItems(3).Text)
dr("Quantity") = Convert.ToInt32(lvi.SubItems(2).Text)
ds.Tables("Orders").Rows.Add(dr)
da.Update(ds, "Orders")
The ADO.NET command builder (SqlCeCommandBuilder) contains the data manipulation logic built into the ADOCE.Recordset object. The application uses the command builder (cb) object to generate the necessary commands (insert, update, and delete) from the select command (SELECT * FROM Orders) supplied to the data adapter (da). The generated commands look like the following:
INSERT INTO Orders (CustomerID,ProductID,Quantity) VALUES (?,?,?)
UPDATE Orders SET CustomerID=?,ProductID=?,Quantity=? WHERE OrderID=?
DELETE FROM Orders WHERE OrderID=?
The command builder uses the select command in conjunction with information retrieved from the database (primary key) to generate the other commands.
In C#, the same code looks like the following.
SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT * FROM Orders", cn);
SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);
da.InsertCommand = cb.GetInsertCommand();
da.UpdateCommand = cb.GetUpdateCommand();
da.DeleteCommand = cb.GetDeleteCommand();
DataSet ds = new DataSet();
da.Fill(ds, "Orders");
DataRow dr = ds.Tables["Orders"].NewRow();
dr["CustomerID"] = Convert.ToInt32(customerComboBox.SelectedValue);
dr["ProductID"] = Convert.ToInt32(lvi.SubItems[3].Text);
dr["Quantity"] = Convert.ToInt32(lvi.SubItems[2].Text);
ds.Tables["Orders"].Rows.Add(dr);
da.Update(ds, "Orders");
Many lines of code were removed in the migration of the sample because the forms designer in the Visual Studio .NET development environment now handles many tasks that previously had to be implemented in eMbedded Visual Basic. The fact that error handling is more structured in the .NET Compact Framework also saves many lines of code.
The reasons for migrating earlier Pocket PC applications to the current toolset are numerous. The toolset also presents new opportunities to you as a developer and to the users of your application. This article may provide you with enough arguments to convince your stakeholders that the migration is not only possible, but provides a significant return on investment.