Beispielcode für IBuySpy Delivery 2005
Der Code für die Anwendung IBuySpy Delivery 2005 steht für die Sprachen Microsoft Visual C# und Microsoft Visual Basic unter \Programme\IBuySpyDelivery2005\Client\language\IBuySpyDevice zur Verfügung. language steht hier für cs oder vb.
In den Beispielen in diesem Thema wird Visual C#-Code verwendet, der Visual Basic-Code ist jedoch sehr ähnlich.
In den Codebeispielen werden zwei Klassen behandelt:
- IBuySpyData
Mit dieser Klasse wird der gesamte Datenzugriff für die Anwendung verarbeitet, einschließlich der Synchronisierung über Replikation und Remotedatenzugriff (Remote Data Access – RDA). Die Klasse schließt die folgenden Methoden ein: ReplSync, RdaSync, LoadCustomers, LoadOrders und LoadOrderDetails. - Customers
Diese Klasse stellt den Code für die Benutzeroberfläche und die Datenbindung für das Customers-Steuerelement bereit und schließt die cboCustomers_SelectedIndexChanged-Methode ein.
ReplSync-Methode
Die ReplSync-Methode erstellt ein neues Replication-Objekt, legt seine Eigenschaften fest und synchronisiert es dann mit der Serverdatenbank. Sowohl die reine Uploadreplikation als auch die bidirektionale Replikation werden unterstützt. Der Wert des exchangeType-Parameters bestimmt, welche Art der Replikation verwendet wird. Wenn Sie die reine Uploadreplikation verwenden, werden an der mobilen Datenbank vorgenommene Änderungen an die Serverdatenbank gesendet. Dabei werden aber keine neuen Daten vom Server gedownloadet.
Beispielcode für die ReplSync-Methode
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-Methode
Ähnlich wie bei der Replikation erstellt die RDASync-Methode ein neues RemoteDataAccess-Objekt, legt seine Eigenschaften fest und führt dann die Synchronisierung mit der Serverdatenbank aus. Sowohl der reine Upload-RDA als auch der bidirektionale RDA werden unterstützt. Der Wert des exchangeType-Parameters bestimmt, welche Art der Replikation verwendet wird. Wenn Sie die reine Upload-RDA-Synchronisierung verwenden, werden an der mobilen Datenbank vorgenommene Änderungen an die Serverdatenbank gesendet. Dabei werden aber keine neuen Daten vom Server gedownloadet. Bei der bidirektionalen RDA-Synchronisierung werden Indizes und Daten auf die Datenbank des Geräts gedownloadet.
Beispielcode für die RDASync-Methode
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- und LoadOrderDetails-Methode
Mit diesen drei Methoden werden datatable-Objekte mit Daten aus Tabellen in der lokalen Datenbank geladen. Der datatable-Name stimmt mit dem Tabellennamen in der lokalen Datenbank überein. Beispielsweise sind die Daten aus der Customers-Tabelle in der Customers-datatable gespeichert. Außer den dataset- und datatable-Objekten zeigen diese Methoden auch, wie Datenadapter verwendet werden. Parametrisierte Abfragen werden in der LoadOrders-Methode ebenfalls verwendet.
Beispielcode für die LoadCustomers-Methode
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"];
}
Beispielcode für die LoadOrders-Methode
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"];
}
Beispielcode für die LoadOrderDetails-Methode
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-Methode
Mit der cboCustomers_SelectedIndexChanged-Methode wird ein Kombinationsfeld gefüllt, sodass ein Lieferant verschiedene Kunden auswählen kann. Wenn der Lieferant die ausgewählten Kunden ändert, werden die entsprechenden Daten angezeigt. Mit dieser Methode wird gezeigt, wie Benutzersteuerelemente an Datenobjekte gebunden werden.
Beispielcode für die cboCustomers_SelectedIndexChanged-Methode
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();
}
}