IBuySpy Delivery 2005 範例程式碼
您可以在 Microsoft Visual C# 和 Microsoft Visual Basic 兩種語言的下列位置找到 IBuySpy Delivery 2005 應用程式的程式碼：\Program Files\IBuySpyDelivery2005\Client\language\IBuySpyDevice；其中的 language 是 cs 或 vb。
此主題中的範例顯示的是 Visual C# 程式碼，不過 Visual Basic 程式碼也非常相似。
範例程式碼以下列兩個類別為重點：
- IBuySpyData
這個類別可處理應用程式的所有資料存取，包括使用複寫與遠端資料存取 (RDA) 的同步處理。此類別包含下列方法：ReplSync、RdaSync、LoadCustomers、LoadOrders 和 LoadOrderDetails。 - Customers
這個類別提供 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();
}
}