IBuySpy Delivery 2005 示例代码
IBuySpy Delivery 2005 应用程序的代码可以在 Microsoft Visual C# 和 Microsoft Visual Basic 语言中找到，位置是：\Program Files\IBuySpyDelivery2005\Client\language\IBuySpyDevice，其中 language 是 cs 或 vb。
本主题中的示例说明了 Visual C# 代码，但 Visual Basic 代码非常相似。
示例代码重点说明了两个类：
- IBuySpyData
该类处理应用程序的所有数据访问，包括使用复制和远程数据访问 (RDA) 的同步。该类包括下列方法：ReplSync、RdaSync、LoadCustomers、LoadOrders 和 LoadOrderDetails。 - Customers
该类为“客户”控件提供用户界面和数据绑定代码，并且包括方法 cboCustomers_SelectedIndexChanged。
ReplSync 方法
ReplSync 方法创建新的 Replication 对象、设置其属性，然后与服务器数据库同步。同时支持仅限上载和双向复制。exchangeType 参数的值确定使用哪种方法。当使用仅限上载复制时，对移动数据库所做的更改将发送到服务器数据库，而且不会从该服务器下载任何新数据。
ReplSync 方法的示例代码
private void ReplSync(SyncStatus syncStatus, ExchangeType exchangeType)
{
SqlCeReplication repl = new SqlCeReplication();
// Set Internet properties.
//
repl.InternetUrl = this.internetUrl;
repl.InternetLogin = this.internetLogin;
repl.InternetPassword = this.internetPassword;
// Set Publisher properties.
//
repl.Publisher = this.serverName;
repl.PublisherDatabase = this.publisherDatabase;
repl.Publication = this.publication;
// Set Publisher security properties.
//
repl.PublisherSecurityMode = this.publisherSecurityMode;
repl.PublisherLogin = this.publisherLogin;
repl.PublisherPassword = this.publisherPassword;
// Set Subscriber properties.
//
repl.SubscriberConnectionString = this.localConnString;
repl.Subscriber = this.subscriber;
// Add dynamic filter (filter by driver IDs).
//
repl.HostName = this.driverID.ToString();
// Bidirectional or upload-only?
//
repl.ExchangeType = exchangeType;
try
{
if (SyncStatus.InitSync == syncStatus)
{
// Create the local database subscription.
//
repl.AddSubscription(AddOption.CreateDatabase);
}
if (SyncStatus.ReinitSync == syncStatus)
{
// If the driver ID has been changed, reinitialize the subscription.
// Set the uploadBeforeReInit to True so that changes in the subscription database
// are uploaded to the Publisher before the snapshot is applied to the subscription database.
//
repl.ReinitializeSubscription(true);
}
// Synchronize to the SQl Server 2000 database to populate the local subscription database.
//
repl.Synchronize();
}
finally
{
// Dispose of the Replication object.
//
repl.Dispose();
}
}
RDASync 方法
与复制相似，RDASync 方法创建新的 RemoteDataAccess 对象、设置其属性，然后与服务器数据库同步。同时支持仅限上载和双向 RDA。exchangeType 参数的值确定使用哪种方法。当使用仅限上载 RDA 同步时，对移动数据库所做的更改会发送到服务器数据库，而且不会从该服务器下载任何新信息。当使用双向 RDA 同步时，索引和数据都将下载到设备数据库上。
RDASync 方法的示例代码
private void RdaSync(SyncStatus syncStatus, ExchangeType exchangeType)
{
string sqlCmd;
SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess();
// Set RDA properties.
//
rda.LocalConnectionString = this.localConnString;
rda.InternetUrl = this.internetUrl;
rda.InternetLogin = this.internetLogin;
rda.InternetPassword = this.internetPassword;
try
{
if (SyncStatus.InitSync == syncStatus)
{
// Create the local database.
//
SqlCeEngine en = new SqlCeEngine(this.localConnString);
en.CreateDatabase();
}
else
{
// Push (upload) the Orders table.
// Columns: All.
//
rda.Push("Orders", this.remoteConnString);
// Push (upload) the OrderDetails table.
// Columns: All.
//
rda.Push("OrderDetails", this.remoteConnString);
// If this is upload-only (Quick Sync), then return.
//
if (ExchangeType.Upload == exchangeType)
{
return;
}
// Open the connection to the local database to drop the table.
// To perform a pull (download), first drop the local database tables.
//
if (ConnectionState.Closed == cnIBuySpy.State)
{
cnIBuySpy.Open();
}
// Drop the Customers table if it exists.
//
if (DoesTableExist("Customers"))
{
DropTable("Customers");
}
// Drop the Orders table if it exists.
//
if (DoesTableExist("Orders"))
{
DropTable("Orders");
}
// Drop the OrderDetails table if it exists.
//
if (DoesTableExist("OrderDetails"))
{
DropTable("OrderDetails");
}
// Drop the Products table if it exists.
//
if (DoesTableExist("Products"))
{
DropTable("Products");
}
// Drop the Categories table if it exists.
//
if (DoesTableExist("Categories"))
{
DropTable("Categories");
}
// Close the database connection.
//
if (ConnectionState.Open == cnIBuySpy.State)
{
cnIBuySpy.Close();
}
}
// Pull (download) the Customers table.
// Columns: All.
// Index: All. The RdaTrackOption.TrackingOffWIthIndexes parameter specifies that indexes are downloaded from the server to the device (index pull).
// Tracking: off.
//
sqlCmd = String.Format(@"SELECT CustomerID, FullName, EmailAddress, Password, Address, City, Region, Zip, Phone, DriverID FROM Customers WHERE DriverID = {0}", this.driverID);
rda.Pull("Customers", sqlCmd, this.remoteConnString, RdaTrackOption.TrackingOffWithIndexes);
// Pull (download) the Orders table.
// Columns: All.
// Index: All.
// Tracking: on.
//
sqlCmd = String.Format(@"SELECT OrderID, CustomerID, OrderDate, ShipDate, Status, Signature FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE DriverID = {0})", this.driverID);
rda.Pull("Orders", sqlCmd, this.remoteConnString, RdaTrackOption.TrackingOnWithIndexes);
// Pull (download) the OrderDetails table.
// Columns: All.
// Index: All.
// Tracking: on.
//
sqlCmd = String.Format(@"SELECT OrderID, ProductID, Quantity, UnitCost FROM OrderDetails WHERE OrderID IN (SELECT OrderID FROM Orders AS O JOIN Customers AS C ON O.CustomerID = C.CustomerID WHERE C.DriverID = {0})", this.driverID);
rda.Pull("OrderDetails", sqlCmd, this.remoteConnString, RdaTrackOption.TrackingOnWithIndexes);
// Pull (download) the Products table.
// Columns: All.
// Index: All.
// Tracking: off.
//
sqlCmd = @"SELECT ProductID, CategoryID, ModelNumber, ModelName, ProductImage, UnitCost, Description FROM Products";
rda.Pull("Products", sqlCmd, this.remoteConnString, RdaTrackOption.TrackingOffWithIndexes);
// Pull (download) the Categories table.
// Columns: All.
// Index: All.
// Tracking: off.
//
sqlCmd = @"SELECT CategoryID, CategoryName FROM Categories";
rda.Pull("Categories", sqlCmd, this.remoteConnString, RdaTrackOption.TrackingOffWithIndexes);
}
finally
{
// Dispose of the RemoteDataAccess object.
//
rda.Dispose();
}
}
LoadCustomers、LoadOrders 和 LoadOrderDetails 方法
这三种方法加载带有本地数据库表中数据的 datatable 对象。datatable 名称与本地数据库中的表名相匹配。例如，Customers 表中的数据存储在 Customers datatable 中。除数据集和 datatable 对象外，这些方法还说明了如何使用数据适配器。参数化查询还用于 LoadOrders 方法中。
LoadCustomers 方法的示例代码
internal DataTable LoadCustomers()
{
if (null == this.dsCustomerOrders)
{
// Creates a new dataset if needed. The same dataset is also used to hold data from the Orders table.
//
this.dsCustomerOrders = new DataSet("CustomerOrders");
}
else
{
DataTable dtCustomers = dsCustomerOrders.Tables["Customers"];
if (null != dtCustomers)
{
// Clear the Customers datatable if it already exists.
//
dtCustomers.Clear();
}
}
if (null == this.daCustomers)
{
// Create a SqlCeDataAdapter to populate the Customers dataset.
//
this.daCustomers = new SqlCeDataAdapter(@"SELECT CustomerID, FullName, Address, City, Region, Zip, Phone " +
@"FROM Customers " +
@"ORDER BY FullName",
cnIBuySpy);
}
// Populate the Customers dataset with data from the Customers table in the local database.
//
daCustomers.Fill(dsCustomerOrders, "Customers");
return dsCustomerOrders.Tables["Customers"];
}
LoadOrders 方法的示例代码
internal DataTable LoadOrders()
{
if (null == dsCustomerOrders)
{
// Creates a new dataset if needed. The same dataset is also used to hold data from the Customers table.
//
dsCustomerOrders = new DataSet("CustomerOrders");
}
else
{
DataTable dtOrders = dsCustomerOrders.Tables["Orders"];
if (null != dtOrders)
{
// Clear the Orders datatable if it already exists.
//
dtOrders.Clear();
}
}
if (null == daOrders)
{
// Create a SqlCeDataAdapter to populate the Orders dataset.
//
daOrders = new SqlCeDataAdapter(@"SELECT OrderID, CustomerID, Status, OrderDate, ShipDate " +
@"FROM Orders " +
@"ORDER BY OrderID",
cnIBuySpy);
daOrders.UpdateCommand = new SqlCeCommand();
daOrders.UpdateCommand.Connection = cnIBuySpy;
// Change the Status field.
//
daOrders.UpdateCommand.CommandText = @"UPDATE Orders SET Status = ?, ShipDate = ? WHERE (OrderID = ?)";
// Set the UpdateCommand parameters for the Status field.
//
System.Data.SqlServerCe.SqlCeParameter paramStatus = new System.Data.SqlServerCe.SqlCeParameter();
paramStatus.ParameterName = "@Status";
paramStatus.SqlDbType = System.Data.SqlDbType.TinyInt;
paramStatus.Size = 1;
paramStatus.SourceColumn = "Status";
daOrders.UpdateCommand.Parameters.Add(paramStatus);
// Set the UpdateCommand parameters for the ShipDate field.
//
System.Data.SqlServerCe.SqlCeParameter paramShipDate = new System.Data.SqlServerCe.SqlCeParameter();
paramShipDate.ParameterName = "@ShipDate";
paramShipDate.SqlDbType = System.Data.SqlDbType.DateTime;
paramShipDate.Size = 8;
paramShipDate.SourceColumn = "ShipDate";
daOrders.UpdateCommand.Parameters.Add(paramShipDate);
// Set the UpdateCommand parameters for the OrderID field. To ensure that the search finds
// the original record in the database, use the Original data row version
// within the WHERE clause when performing a search.
//
System.Data.SqlServerCe.SqlCeParameter paramOrderID = new System.Data.SqlServerCe.SqlCeParameter();
paramOrderID.ParameterName = "@Original_OrderID";
paramOrderID.SqlDbType = System.Data.SqlDbType.Int;
paramOrderID.Size = 4;
paramOrderID.IsNullable = false;
paramOrderID.Precision = 0;
paramOrderID.Scale = 0;
paramOrderID.SourceColumn = "OrderID";
paramOrderID.SourceVersion = System.Data.DataRowVersion.Original;
daOrders.UpdateCommand.Parameters.Add(paramOrderID);
}
// Populate the Orders dataset with data from the Orders table in the local database.
//
daOrders.Fill(dsCustomerOrders, "Orders");
return dsCustomerOrders.Tables["Orders"];
}
LoadOrderDetails 方法的示例代码
internal DataTable LoadOrderDetails(int orderID)
{
if (null == dsCustomerOrders)
{
// Create a new dataset if needed.
//
dsCustomerOrders = new DataSet("CustomerOrders");
}
else
{
DataTable dtOrderDetails = dsCustomerOrders.Tables["OrderDetails"];
if (null != dtOrderDetails)
{
// Clear the OrderDetails datatable if it already exists.
//
dtOrderDetails.Clear();
}
}
if (null == daOrderDetails)
{
// Create a SqlCeDataAdapter to populate the OrderDetails dataset.
//
daOrderDetails = new SqlCeDataAdapter();
// Create a SelectCommand to select order details information from the OrderDetails and
// Products tables in the local database.
//
daOrderDetails.SelectCommand = this.cnIBuySpy.CreateCommand();
daOrderDetails.SelectCommand.CommandText = @"SELECT O.OrderID, O.ProductID, P.ModelName, O.Quantity, O.UnitCost, O.Quantity*O.UnitCost AS Total " +
@"FROM OrderDetails AS O JOIN Products AS P " +
@"ON O.ProductID = P.ProductID " +
@"WHERE O.OrderID = ?";
// Set the SelectCommand parameters for the OrderID field.
//
SqlCeParameter paramOrderID1 = new SqlCeParameter();
paramOrderID1.ParameterName = "@Original_OrderID";
paramOrderID1.SqlDbType = System.Data.SqlDbType.Int;
paramOrderID1.Size = 4;
paramOrderID1.SourceColumn = "OrderID";
paramOrderID1.Value = -1;
paramOrderID1.SourceVersion = System.Data.DataRowVersion.Original;
daOrderDetails.SelectCommand.Parameters.Add(paramOrderID1);
// Create an UpdateCommand to update the OrderDetails table in the local database.
//
daOrderDetails.UpdateCommand = this.cnIBuySpy.CreateCommand();
daOrderDetails.UpdateCommand.CommandText = @"UPDATE OrderDetails " +
@"SET Quantity = ?, UnitCost = ? " +
@"WHERE (OrderID = ? AND ProductID = ?)";
// Set the UpdateCommand parameters for the Quantity field.
//
SqlCeParameter paramQuantity2 = new SqlCeParameter();
paramQuantity2.ParameterName = "@Quantity";
paramQuantity2.SqlDbType = System.Data.SqlDbType.Int;
paramQuantity2.Size = 4;
paramQuantity2.SourceColumn = "Quantity";
// Set the UpdateCommand parameters for the UnitCost field.
//
SqlCeParameter paramUnitCost2 = new SqlCeParameter();
paramUnitCost2.ParameterName = "@UnitCost";
paramUnitCost2.SqlDbType = System.Data.SqlDbType.Money;
paramUnitCost2.SourceColumn = "UnitCost";
// Set the UpdateCommand parameters for the OrderID field.
//
SqlCeParameter paramOrderID2 = new SqlCeParameter();
paramOrderID2.ParameterName = "@Original_OrderID";
paramOrderID2.SqlDbType = System.Data.SqlDbType.Int;
paramOrderID2.Size = 4;
paramOrderID2.SourceColumn = "OrderID";
paramOrderID2.SourceVersion = System.Data.DataRowVersion.Original;
// Set the UpdateCommand parameters for the ProductID field.
//
SqlCeParameter paramProductID2 = new SqlCeParameter();
paramProductID2.ParameterName = "@Original_ProductID";
paramProductID2.SqlDbType = System.Data.SqlDbType.Int;
paramProductID2.Size = 4;
paramProductID2.SourceColumn = "ProductID";
paramProductID2.SourceVersion = System.Data.DataRowVersion.Original;
daOrderDetails.UpdateCommand.Parameters.Add(paramQuantity2);
daOrderDetails.UpdateCommand.Parameters.Add(paramUnitCost2);
daOrderDetails.UpdateCommand.Parameters.Add(paramOrderID2);
daOrderDetails.UpdateCommand.Parameters.Add(paramProductID2);
// Create an InsertCommand to insert data into the OrderDetails table in the local database.
//
daOrderDetails.InsertCommand = this.cnIBuySpy.CreateCommand();
daOrderDetails.InsertCommand.CommandText = @"INSERT INTO OrderDetails (OrderID, ProductID, Quantity, UnitCost) " +
@"VALUES (?, ?, ?, ?)";
// Set the InsertCommand parameters for the OrderID field.
//
SqlCeParameter paramOrderID3 = new SqlCeParameter();
paramOrderID3.ParameterName = "@OrderID";
paramOrderID3.SqlDbType = System.Data.SqlDbType.Int;
paramOrderID3.Size = 4;
paramOrderID3.SourceColumn = "OrderID";
// Set the InsertCommand parameters for the ProductID field.
//
SqlCeParameter paramProductID3 = new SqlCeParameter();
paramProductID3.ParameterName = "@ProductID";
paramProductID3.SqlDbType = System.Data.SqlDbType.Int;
paramProductID3.Size = 4;
paramProductID3.SourceColumn = "ProductID";
// Set the InsertCommand parameters for the Quantity field.
//
SqlCeParameter paramQuantity3 = new SqlCeParameter();
paramQuantity3.ParameterName = "@Quantity";
paramQuantity3.SqlDbType = System.Data.SqlDbType.Int;
paramQuantity3.Size = 4;
paramQuantity3.SourceColumn = "Quantity";
// Set the InsertCommand parameters for the UnitCost field.
//
SqlCeParameter paramUnitCost3 = new SqlCeParameter();
paramUnitCost3.ParameterName = "@UnitCost";
paramUnitCost3.SqlDbType = System.Data.SqlDbType.Money;
paramUnitCost3.SourceColumn = "UnitCost";
daOrderDetails.InsertCommand.Parameters.Add(paramOrderID3);
daOrderDetails.InsertCommand.Parameters.Add(paramProductID3);
daOrderDetails.InsertCommand.Parameters.Add(paramQuantity3);
daOrderDetails.InsertCommand.Parameters.Add(paramUnitCost3);
}
this.daOrderDetails.SelectCommand.Parameters["@Original_OrderID"].Value = orderID;
// Populate the OrderDetails dataset with data from the OrderDetails table in the local database.
//
this.daOrderDetails.Fill(this.dsCustomerOrders, "OrderDetails");
return this.dsCustomerOrders.Tables["OrderDetails"];
}
cboCustomers_SelectedIndexChanged 方法
cboCustomers_SelectedIndexChanged 方法填充组合框，以便送货司机可以选择不同的客户。当送货司机更改所选客户时，就会显示相应的数据。此方法说明了如何将用户控件绑定到数据对象。
cboCustomers_SelectedIndexChanged 方法的示例代码
private void cboCustomers_SelectedIndexChanged(object sender, System.EventArgs e)
{
if (0 <= this.cboCustomers.SelectedIndex &&
this.customerID != Convert.ToInt32(this.cboCustomers.SelectedValue))
{
DataRowView row = null;
// If the current order has been modified in any way and the user selects a different customer, then
// the user's changes are discarded: The CustomerOrders and Inventory datasets are reset.
//
if (this.dataIBuySpy.HasChanges())
{
if (DialogResult.OK == MessageBox.Show(String.Format("You have modified order {0}. Switching customers will discard all changes.", this.orderID),
"IBuySpy Delivery",
MessageBoxButtons.OKCancel,
MessageBoxIcon.Asterisk,
MessageBoxDefaultButton.Button1))
{
this.dataIBuySpy.ResetOrderDetails();
}
else
{
this.cboCustomers.SelectedValue = this.customerID;
return;
}
}
// Set the current binding position.
//
BindingContext[dtCustomers].Position = this.cboCustomers.SelectedIndex;
// Load the selected customer information from the Customer datatable.
//
row = (DataRowView)BindingContext[dtCustomers].Current;
this.customerID = Convert.ToInt32(row["CustomerID"]);
// Displays the customer's address information.
//
this.lblAddressValue1.Text = row["Address"].ToString();
this.lblAddressValue2.Text = String.Format(@"{0}, {1} {2}", row["City"], row["Region"], row["Zip"]);
this.lblAddressValue3.Text = row["Phone"].ToString();
// Set the data viewer to filter by the selected customer.
//
this.dvOrders.RowFilter = String.Format("CustomerID = '{0}'", this.customerID);
UpdateOrderStatus();
}
}