ibuyspydata.cs

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

                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 insert command 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"];
        }

        /// This method saves the Orders and OrderDetails datasets to the Orders and OrderDetails tables in the local database.
        ///
        internal void SaveOrderDetails(int orderID)
        {
            DataTable dtOrders = null;

            if (null != this.dsCustomerOrders)
            {
                dtOrders = this.dsCustomerOrders.Tables["Orders"];

                /// Search by order ID for the specified order in the Orders dataset.
                ///
                DataRow[] orderRows = dtOrders.Select(String.Format(@"OrderID = {0}", orderID));
                if (1 == orderRows.Length)
                {
                    /// Change the status to "Delivered" in the Orders table.
                    ///
                    orderRows[0].BeginEdit();
                    orderRows[0]["Status"] = OrderStatus.Delivered;
                    orderRows[0]["ShipDate"] = DateTime.Now;
                    orderRows[0].EndEdit();
                }
                else if (0 == orderRows.Length)
                {
                    throw new Exception(String.Format("cannot find order {0} in Order dataset", orderID));
                }
                else
                {
                    throw new Exception(String.Format("The prmary key (Order ID {0}) in Order dataset are not unique", orderID));
                }

                /// Saves the OrderDetails dataset to the OrderDetails table in the local database.
                ///
                this.daOrderDetails.Update(this.dsCustomerOrders, "OrderDetails");

                /// Saves the Orders dataset to the Orders table in the local database.
                ///
                this.daOrders.Update(this.dsCustomerOrders, "Orders");

                /// Accept the changes to the CustomerOrders dataset in memory.
                /// Note: the CustomerOrders dataset holds data for the Customers, Orders, and OrderDetails.
                ///
                this.dsCustomerOrders.AcceptChanges();
            }

            if (null != this.dsInventory)
            {
                /// Accept the changes to the Inventory dataset in memory.
                /// Note: the CustomerOrders dataset holds information for the Customers, Orders, and OrderDetails.
                ///
                this.dsInventory.AcceptChanges();
            }
        }

        /// Discards all changes to the CustomerOrders and Inventory datasets and reverts back to the
        /// original values.
        ///
        internal void ResetOrderDetails()
        {
            this.dsCustomerOrders.RejectChanges();
            this.dsInventory.RejectChanges();
        }

        /// Check whether there are any changes to the Orders or OrderDetails tables in memory.
        ///
        internal bool HasChanges()
        {
            if (null == this.dsCustomerOrders)
            {
                return false;
            }

            return this.dsCustomerOrders.HasChanges();
        }

        /// Compute the total cost for the orders.
        ///
        internal decimal OrderTotal()
        {
            DataTable dtOrderDetails = dsCustomerOrders.Tables["OrderDetails"];
            if (null == dtOrderDetails)
            {
                return 0;
            }

            object totalCost = dtOrderDetails.Compute("SUM(Total)", "");

			if (totalCost.Equals(DBNull.Value))
			{
				return 0;
			}

            return Convert.ToDecimal(totalCost);
        }


        /// Load the appropriate byte array (based on the OrderID) containing the captured signature coordinates.
        ///
        internal byte[] LoadSignature(int orderID)
        {
            SqlCeCommand    cmdSignature = null;
            SqlCeDataReader drSignature  = null;

            cmdSignature = this.cnIBuySpy.CreateCommand();
            cmdSignature.CommandText = "SELECT Signature FROM Orders WHERE OrderID = ?";
            cmdSignature.Parameters.Add("@OrderID", orderID);
            
            try
            {
                drSignature = cmdSignature.ExecuteReader();
                if (drSignature.Read()) 
                {
                    if (!drSignature.IsDBNull(0)) 
                    {
                        return (byte[])drSignature.GetValue(0);
                    } 
                }
            }
            finally
            {
                drSignature.Close();
            }

            return null;
        }

        /// Save the byte array of signature coordinates to the local database.
        ///
        internal void SaveSignature(int orderID, byte[] signatureData)
        {
            SqlCeCommand cmdSignature = null;

            cmdSignature = this.cnIBuySpy.CreateCommand();
            cmdSignature.CommandText = @"UPDATE Orders SET Signature = ? WHERE OrderID = ?";

            cmdSignature.Parameters.Add("@Signature", SqlDbType.Binary);
            cmdSignature.Parameters["@Signature"].Value = signatureData;

            cmdSignature.Parameters.Add("@OrderID", SqlDbType.Int);
            cmdSignature.Parameters["@OrderID"].Value = orderID;
            
            cmdSignature.ExecuteNonQuery();
        }

        /// Populate the Categories datatable with data from the Categories table in the local database.
        ///
        internal DataTable LoadCategories()
        {
            if (null == this.dsInventory)
            {
                /// Creates a new dataset if needed.
                ///
                this.dsInventory = new DataSet("Inventory");
            }
            else
            {
                DataTable dtCategories = dsInventory.Tables["Categories"];
                if (null != dtCategories)
                {
                    /// Clear the Categories datatable if it already exists.
                    ///
                    dtCategories.Clear();
                }
            }

            if (null == this.daCategories)
            {
                string sqlCmd;

				/// Categories are loaded only if they  contain products that are part of the available inventory.
				/// Products are considered part of the inventory if they belong to orders with a status of "Failed".
				///
                sqlCmd = @"SELECT C.CategoryID, C.CategoryName " +
                         @"FROM Categories AS C " +
                         @"WHERE C.CategoryID IN " +
                         @"(SELECT DISTINCT P.CategoryID FROM Orders AS O " +
                         @"JOIN OrderDetails AS OD ON O.OrderID = OD.OrderID " +
                         @"JOIN Products AS P ON OD.ProductID = P.ProductID " +
                         @"WHERE O.Status = 0)";

                /// Create a SqlCeDataAdapter to populate the Categories datatable.
                ///
                daCategories = new SqlCeDataAdapter(sqlCmd, this.cnIBuySpy);
            }

            /// Populate the Categories dataset with data from the Inventory table in the local database.
            ///
            daCategories.Fill(this.dsInventory, "Categories");

            return this.dsInventory.Tables["Categories"];
        }

        /// Populate the Inventory datatable with data from the Orders, OrderDetails, and Products tables in the local database.
        ///
        internal DataTable LoadInventory()
        {
            if (null == this.dsInventory)
            {
                /// Creates a new dataset if needed.
                ///
                this.dsInventory = new DataSet("Inventory");
            }
            else
            {
                DataTable dtInventory = this.dsInventory.Tables["Inventory"];
                if (null != dtInventory)
                {
                    /// Clear the Categories datatable if it already exists.
                    ///
                    dtInventory.Clear();
                }
            }

            if (null == this.daInventory)
            {
                string sqlCmd;

                /// Products are considered part of inventory if they belong to orders with a status of "Failed".
                ///
                sqlCmd = @"SELECT OD.ProductID, P.ModelName, P.ProductImage, P.CategoryID, P.UnitCost, SUM(OD.Quantity) AS Quantity " +
                         @"FROM Orders AS O JOIN OrderDetails AS OD ON O.OrderID = OD.OrderID JOIN Products AS P ON OD.ProductID = P.ProductID " +
                         @"WHERE O.Status = 0 " +
                         @"GROUP BY OD.ProductID, P.ModelName, P.ProductImage, P.CategoryID, P.UnitCost";

                /// Create a SqlCeDataAdapter to populate the Inventories datatable.
                ///
                daInventory = new SqlCeDataAdapter(sqlCmd, this.cnIBuySpy);
            }

            /// Populate the Inventory dataset with data from the Inventory table in the local database
            ///
            this.daInventory.Fill(this.dsInventory, "Inventory");

            return this.dsInventory.Tables["Inventory"];
        }

        /// This method adds a product from the Inventory dataset to the OrderDetails datatable.
        ///
        internal void AddProduct(int orderID, int productID, string modelName, int quantity, decimal unitCost)
        {
            DataTable dtOrderDetails = null;
            DataTable dtInventory    = null;

            dtOrderDetails = dsCustomerOrders.Tables["OrderDetails"];
            dtInventory    = dsInventory.Tables["Inventory"];

            DataRow[] orderRows = dtOrderDetails.Select(String.Format("OrderID = {0} AND ProductID = {1}", orderID, productID));

            /// Check if the Order already contains the product to be added.
            ///
            if (0 == orderRows.Length)
            {
                /// If the product is not already part of the order, add a new record to the OrderDetails table.
                ///
                dtOrderDetails.Rows.Add(new object[] { orderID, productID, modelName, quantity, unitCost, quantity*unitCost });
            }
            else if (1 == orderRows.Length)
            {
                /// Else, update the quantity and total price on the record.
                ///
                orderRows[0].BeginEdit();

                orderRows[0]["Quantity"] = Convert.ToInt32(orderRows[0]["Quantity"]) + quantity;
                orderRows[0]["Total"]    = Convert.ToInt32(orderRows[0]["Total"]) + quantity*unitCost;

                orderRows[0].EndEdit();
            }
            else
            {
                throw new Exception(String.Format("The prmary key (Order ID {0} & Product ID {1} ) in Order Details dataset are not unique", orderID, productID));
            }
        }

        /// Drops a table in the local database.
        ///
        private void DropTable(string table)
        {
            SqlCeCommand sqlCmd = this.cnIBuySpy.CreateCommand();

            sqlCmd.CommandText = @"DROP TABLE " + table;
            sqlCmd.ExecuteNonQuery();
        }

        /// Error handling routine for SQL Server CE exceptions.
        ///
        internal static void ShowErrors(SqlCeException e) 
        {
            /// Show SQL Server CE error information. 
            ///
            SqlCeErrorCollection errorCollection = e.Errors;

            StringBuilder bld = new StringBuilder();
            Exception   inner = e.InnerException;

            if (null != inner) 
            {
                MessageBox.Show("Inner Exception: " + inner.ToString(), "IBuySpy Delivery");
            }

            foreach (SqlCeError err in errorCollection) 
            {
                bld.Append("\n Error Code: " + err.HResult.ToString("X"));
                bld.Append("\n Message   : " + err.Message);
                bld.Append("\n Minor Err.: " + err.NativeError);
                bld.Append("\n Source    : " + err.Source);
                
                foreach (int numPar in err.NumericErrorParameters) 
                {
                    if (0 != numPar) bld.Append("\n Num. Par. : " + numPar);
                }
                
                foreach (string errPar in err.ErrorParameters) 
                {
                    if (String.Empty != errPar) bld.Append("\n Err. Par. : " + errPar);
                }

                MessageBox.Show(bld.ToString(), "IBuySpy Delivery");
                bld.Remove(0, bld.Length);
            }
        }
    }
}