ibuyspydata.cs

采用vc#.net和sqlce实现智能手机端和服务器数据交换

        ///
        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();
            }        
        }

        /// Checks to see whether the specified table is in the local database.
        ///
        private bool DoesTableExist(string table)
        {
            SqlCeCommand sqlCmd = this.cnIBuySpy.CreateCommand();

            /// Query system table to check whether the table is present.
            ///
            sqlCmd.CommandText = @"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_Name = ?";

            SqlCeParameter tableName = new SqlCeParameter();
            tableName.ParameterName  = "@TableName";
            tableName.SqlDbType      = SqlDbType.NVarChar;
            tableName.Size           = 128;
            tableName.Value          = table;

            sqlCmd.Parameters.Add(tableName); 

            try
            {
                return sqlCmd.ExecuteScalar().Equals(1);
            }
            catch(SqlCeException)
            {
                return false;
            }
        }

        /// Populate the Customers datatable with data from the Customers table in the local database.
        ///
        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"];
        }

        /// Populate the Orders datatable with data from the Orders table in the local database.
        ///
        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, the Original data row version should be used
                /// 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 data set with data from the Orders table in the local database.
            ///
            daOrders.Fill(dsCustomerOrders, "Orders");

            return dsCustomerOrders.Tables["Orders"];
        }
        

        /// Check whether the Customer datatable exists and is not empty.
        ///
        internal bool HasCustomerDataTable()
        {
            DataTable dtCustomers = dsCustomerOrders.Tables["Customers"];

            if (null != dtCustomers)
            {
                return (0 < dtCustomers.Rows.Count);
            }

            return false;
        }

        /// Check whether the Inventory datatable exists and is not empty.
        ///
        internal bool HasInventoryDataTable()
        {
            DataTable dtInventory = dsInventory.Tables["Inventory"];

            if (null != dtInventory)
            {
                return (0 < dtInventory.Rows.Count);
            }

            return false;
        }

		/// Save the value of the Status field in the Orders table in the local database.
        ///
        internal void SaveOrderStatus()
        {
            daOrders.Update(dsCustomerOrders, "Orders");
        }

        
		/// Populate the OrderDetails datatable with data from the OrderDetails table in the local database.
        ///
        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 select command 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;