IBuySpy Delivery Sample Code
El código de la aplicación IBuySpy Delivery está disponible en los lenguajes Microsoft Visual C# y Microsoft Visual Basic, en \Archivos de programa\IBuySpyDelivery2005\Client\language\IBuySpyDevice, donde language es cs o vb.
En los ejemplos de este tema se muestra código de Visual C#, pero el código de Visual Basic es muy parecido.
El código de ejemplo se centra en dos clases:
IBuySpyData
Esta clase controla todo el acceso a datos de la aplicación, incluida la sincronización mediante el uso de replicación y acceso a datos remotos (RDA). La clase contiene los siguientes métodos: ReplSync, RdaSync, LoadCustomers, LoadOrders y LoadOrderDetails.
Customers
Esta clase proporciona la interfaz de usuario y el código de enlace de datos para el control Customers y contiene el método cboCustomers_SelectedIndexChanged.
Método ReplSync
El método ReplSync crea un nuevo objeto Replication, establece sus propiedades y, a continuación, se sincroniza con la base de datos del servidor. Se admiten la replicación de sólo carga y la bidireccional. El valor del parámetro exchangeType determina cuál se utiliza. Si se usa la replicación de sólo carga, los cambios realizados en la base de datos móvil se envían a la base de datos del servidor sin descargar ningún dato nuevo del servidor.
Código de ejemplo del método 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 database to populate the local subscription database.
//
repl.Synchronize();
}
finally
{
// Dispose of the Replication object.
//
repl.Dispose();
}
}
Método RDASync
De forma parecida a la replicación, el método RDASynccrea un nuevo objeto RemoteDataAccess, establece sus propiedades y se sincroniza con la base de datos del servidor. Se admiten la RDA de sólo carga y la bidireccional. El valor del parámetro exchangeType determina cuál se utiliza. Si se usa la sincronización RDA de sólo carga, los cambios realizados en la base de datos móvil se envían a la base de datos del servidor sin descargar ningún dato nuevo del servidor. Si se usa la sincronización RDA bidireccional, se descargan a la base de datos del dispositivo los índices y los datos.
Código de ejemplo del método 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();
}
}
Métodos LoadCustomers, LoadOrders y LoadOrderDetails
Estos tres métodos cargan los objetos datatable con datos de las tablas de la base de datos local. El nombre de datatable coincide con el nombre de la tabla de la base de datos local. Por ejemplo, los datos de la tabla Customers se almacenan en la datatable Customers. Además del conjunto de datos y los objetos datatable, estos métodos demuestran cómo usar los adaptadores de datos. En el método LoadOrders también se usan consultas con parámetros.
Código de ejemplo del método 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"];
}
Código de ejemplo del método 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"];
}
Código de ejemplo del método 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"];
}
Método cboCustomers_SelectedIndexChanged
El método cboCustomers_SelectedIndexChanged llena un cuadro combinado de forma que un repartidor pueda seleccionar distintos clientes. Cuando el repartidor cambia el cliente seleccionado, se muestran los datos adecuados. Este método demuestra cómo enlazar controles de usuario a objetos de datos.
Código de ejemplo del método 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();
}
}