Codice di esempio per IBuySpy Delivery 2005
Il codice per l'applicazione IBuySpy Delivery 2005 è disponibile nei linguaggi Microsoft Visual C# e Microsoft Visual Basic nel percorso \Programmi\IBuySpyDelivery2005\Client\linguaggio\IBuySpyDevice, dove linguaggio corrisponde a cs oppure vb.
Negli esempi forniti in questo argomento viene illustrato il codice Visual C#. Il codice Visual Basic è molto simile.
Il codice di esempio è basato su due classi principali:
- IBuySpyData
Questa classe gestisce interamente l'accesso ai dati per l'applicazione, inclusa la sincronizzazione mediante replica e RDA (Remote Data Access). La classe include i metodi seguenti: ReplSync, RdaSync, LoadCustomers, LoadOrders e LoadOrderDetails. - Customers
Questa classe fornisce l'interfaccia utente e il codice di associazione dati per il controllo Customers e include il metodo cboCustomers_SelectedIndexChanged.
Metodo ReplSync
Il metodo ReplSync crea un nuovo oggetto Replication, ne imposta le proprietà e quindi esegue la sincronizzazione con il database del server. È supportata la replica sia di solo caricamento che bidirezionale. Il valore del parametro exchangeType determina il tipo utilizzato. In caso di replica di solo caricamento, le modifiche apportate al database per dispositivi portatili vengono inviate al database del server senza scaricare nuovi dati dal server.
Codice di esempio per il metodo 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();
}
}
Metodo RDASync
Analogamente alla replica, il metodo RDASync crea un nuovo oggetto RemoteDataAccess, ne imposta le proprietà e quindi esegue la sincronizzazione con il database del server. Sono supportati RDA sia di solo caricamento che bidirezionali. Il valore del parametro exchangeType determina il tipo utilizzato. In caso di sincronizzazione di RDA di solo caricamento, le modifiche apportate al database per dispositivi portatili vengono inviate al database del server senza scaricare nuove informazioni dal server. In caso di sincronizzazione di RDA bidirezionale, nel database del dispositivo vengono scaricati sia gli indici che i dati.
Codice di esempio per il metodo 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();
}
}
Metodi LoadCustomers, LoadOrders e LoadOrderDetails
Questi tre metodi caricano oggetti datatable con i dati dalle tabelle del database locale. Il nome dell'oggetto datatable corrisponde al nome della tabella nel database locale. I dati della tabella Customers, ad esempio, vengono archiviati nell'oggetto datatable Customers. Oltre all'utilizzo di set di dati e oggetti datatable, questi metodi illustrano l'utilizzo degli adattatori dati. Nel metodo LoadOrders vengono inoltre utilizzate query con parametri.
Codice di esempio per il metodo 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"];
}
Codice di esempio per il metodo 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"];
}
Codice di esempio per il metodo 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"];
}
Metodo cboCustomers_SelectedIndexChanged
Il metodo cboCustomers_SelectedIndexChanged popola una casella combinata in modo da consentire a un autista responsabile delle consegne di selezionare clienti diversi. Quando il responsabile delle consegne modifica il cliente selezionato, vengono visualizzati i dati appropriati. Questo metodo illustra come associare i controlli utente agli oggetti dati.
Codice di esempio per il metodo 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();
}
}