ibuyspydata.cs

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

            SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess();

            /// Set the RDA properties.
            ///
            rda.LocalConnectionString = @"Data Source=" + connStringDirverIDs;
            rda.InternetUrl           = this.internetUrl;
            rda.InternetLogin         = this.internetLogin;
            rda.InternetPassword      = this.internetPassword;

            try 
            {
                /// Pull (download)the Driver IDs.
                ///
                rda.Pull("DriverIDs", @"EXEC LoadDriverIDs", this.remoteConnString, RdaTrackOption.TrackingOff);
            }
            finally
            {
                /// Dispose of the RDA Object.
                ///
                rda.Dispose();
            }        

            /// Create the SQL CE Connection to the DriverIDs database.
            ///
            cnDriverIDs = new SqlCeConnection(@"Data Source=" + connStringDirverIDs);

            /// Create a data adapter.
            ///
            daDriverIDs = new SqlCeDataAdapter();

            /// The code in the following three lines creates and specifies the SQL select command (in this case a stored
            /// procedure called LoadDriverIDs) to run in order to retrieve the DriverID records.
            ///
            daDriverIDs.SelectCommand             = cnDriverIDs.CreateCommand();
            daDriverIDs.SelectCommand.CommandText = @"SELECT DriverID FROM DriverIDs";

            /// Create a datatable to store the DriverID records.
            ///
            dtDriverIDs     = new DataTable("DriverIDs");

            /// Populates the datatable with the DriverID records.
            ///
            daDriverIDs.Fill(dtDriverIDs);

            return dtDriverIDs;
        }

        /// Load default configuration settings from the Settings table in the local database.
        ///
        internal void LoadSettings()
        {
            SqlCeCommand    cmdSettings     = null;
            SqlCeDataReader drSettings      = null;
            int             idxServerName;
            int             idxDriverID;
            int             idxSyncMethod;

            try
            {
                /// Connect to the local database.
                ///
                if (ConnectionState.Closed == cnIBuySpy.State)
                {
                    cnIBuySpy.Open();
                }

                /// The code in the following two lines create a command that selects the values for
				/// the default configuration settings (ServerName, DriverID, and SyncMethod) from the
                /// Settings table in the local database.
                ///
                cmdSettings             = cnIBuySpy.CreateCommand();
                cmdSettings.CommandText = @"SELECT ServerName, DriverID, SyncMethod FROM Settings";

                /// Execute the SQL SELECT command.
                ///
                drSettings = cmdSettings.ExecuteReader();
                if (!drSettings.Read())
                {
                    throw new Exception("Unable to load settings.");
                }

                /// Retrieve the index pointing to the configuration data (ServerName, DriverID, SyncMethod).
                ///
                idxServerName = drSettings.GetOrdinal("ServerName");
                idxDriverID   = drSettings.GetOrdinal("DriverID");
                idxSyncMethod = drSettings.GetOrdinal("SyncMethod");

                /// If the retrieved data is not valid (NULL), set the default configuration settings to empty
                /// values. For the SyncMethod, set the default configuration setting to replication.
                ///
                if (drSettings.IsDBNull(idxServerName) || 
                    drSettings.IsDBNull(idxDriverID) || 
                    drSettings.IsDBNull(idxSyncMethod))
                {
                    serverName = String.Empty;
                    driverID = -1;
                    syncMethod = SyncType.Replication;

                    throw new Exception("Invalid setting data.");
                }

                /// Set the default configuration settings.
                /// 
                serverName = drSettings.GetString(idxServerName);
                driverID   = drSettings.GetInt32(idxDriverID);
                syncMethod = ((SyncType)drSettings.GetByte(idxSyncMethod));

                if (null != drSettings)
                {
                    drSettings.Close();
                }
            }
            catch
            {
                if (null != drSettings)
                {
                    drSettings.Close();
                }

                if (ConnectionState.Open == cnIBuySpy.State)
                {
                    cnIBuySpy.Close();
                }

                throw;
            }

            UpdateProps();
        }


        /// Save the configuration properties to the Settings table in the local database.
        ///
        internal void SaveSettings()
        {
            SqlCeCommand cmdSettings = cnIBuySpy.CreateCommand();

            if (!DoesTableExist("Settings"))
            {
                /// Create the Settings table if the table does not already exist.
                ///
                cmdSettings.CommandText = @"CREATE TABLE Settings (ServerName NTEXT, DriverID INT, SyncMethod TINYINT)";
                cmdSettings.ExecuteNonQuery();
            }
            else
            {
                /// If the table exists, verify that there is an existing settings record to update.
                ///
                cmdSettings.CommandText = @"SELECT COUNT(*) FROM Settings";
                object count = cmdSettings.ExecuteScalar();

                if (Convert.ToInt32(count) > 0)
                {
                    /// Update the existing record in the Setings table with the new values for the
					/// configuration settings.
                    ///
                    cmdSettings.CommandText = @"UPDATE Settings SET ServerName = ?, DriverID = ?, SyncMethod = ?;";
                    cmdSettings.Parameters.Add("@ServerName", this.serverName);
                    cmdSettings.Parameters.Add("@DriverID",   this.driverID);
                    cmdSettings.Parameters.Add("@SyncMethod", (byte)this.syncMethod);

                    cmdSettings.ExecuteNonQuery();

                    UpdateProps();

                    return;
                }
            }

            /// If the Settings table did not previously exist and was created, or if there was no previous
            /// record in the Settings table, insert a new record into the table.
            ///
            cmdSettings.CommandText = @"INSERT INTO Settings (ServerName, DriverID, SyncMethod) VALUES (?, ?, ?);";
            cmdSettings.Parameters.Add("@ServerName", this.serverName);
            cmdSettings.Parameters.Add("@DriverID",   this.driverID);
            cmdSettings.Parameters.Add("@SyncMethod", (byte)this.syncMethod);
            cmdSettings.ExecuteNonQuery();
        }

        /// This method updates configuration properties based on user changes on the Configuration control.
        ///
        internal void UpdateProps()
        {
            /// Sets the new Subscriber name using the format Driver#<DriverID>.
            ///
            this.subscriber = String.Format(@"Driver#{0}", this.driverID);

            /// Sets the Internet URL based on the server name.
            ///
            this.internetUrl = String.Format(@"http://{0}/StoreCSVS/ssce/sqlcesa30.dll", this.serverName);

            /// Construct the remote connection strings from the application properties (ServerName and publisher database).
            ///
            this.remoteConnString = String.Format(@"Provider=sqloledb; Data Source={0}; Initial Catalog={1}; Integrated Security=SSPI",
                                                    this.serverName,
                                                    this.publisherDatabase);
        }


		/// This method performs a full bidirectional synchronization. Upload: All changes to the local database are sent
		/// to the server database. Download for replication: All changes to the server database are sent to the local database.
		/// Download for RDA: All data (regardless of whether it has changed) is sent to the local database.
		///
        internal void FullSync(SyncStatus syncStatus)
        {
            if (SyncStatus.InitSync == syncStatus)
            {
                /// If this is the initial synchronization, set the new subscriber name using the format Driver#<DriverID>.
                ///
                this.subscriber = String.Format(@"Driver#{0}", this.driverID);
            }

            try
            {
                /// If the connection is already open, close it to perform a synchronization.
                ///
                if (ConnectionState.Open == this.cnIBuySpy.State)
                {
                    this.cnIBuySpy.Close();
                }

                /// If this is an initial synchronization or reinitialization of the subscription is required (the driver id has been changed), 
                /// clear the dataset.
                ///
                if (SyncStatus.InitSync == syncStatus || SyncStatus.ReinitSync == syncStatus)
                {
                    if (null != this.dsCustomerOrders)
                    {
                        /// Clear the Customers and Orders datasets from the device memory.
                        ///
                        this.dsCustomerOrders.Clear();
                    }

                    if (null != this.dsInventory)
                    {
                        /// Clear the Inventory dataset from the device memory.
                        ///
                        this.dsInventory.Clear();
                    }
                }

                /// If this is an initial synchronization and the database exists, delete the local database.
                ///
                if (SyncStatus.InitSync == syncStatus && File.Exists(this.localDatabaseFile))
                {
                    File.Delete(this.localDatabaseFile);
                }

                if (SyncType.Replication == this.syncMethod)
                {
                    /// Perform synchronization using replication.
                    ///
                    ReplSync(syncStatus, ExchangeType.BiDirectional);
                }
                else if (SyncType.Rda == this.syncMethod)
                {
                    /// Perform synchronization using RDA.
                    ///
                    RdaSync(syncStatus, ExchangeType.BiDirectional);
                }
                else
                {
                    throw new Exception("Invalid synchronize method - " + this.syncMethod.ToString());
                }
            }
            finally
            {
                /// Reopen the connection to the local database after synchronization to
                /// access the local data.
                ///
                if (ConnectionState.Closed == this.cnIBuySpy.State)
                {
                    cnIBuySpy.Open();
                }
            }

            if (SyncStatus.InitSync == syncStatus || SyncStatus.ReinitSync == syncStatus)
            {
                /// Save the default configuration settings if this is an initial synchronization or the subscription is reinitialized.
                ///
                SaveSettings();
            }
        }

        /// This method performs an upload only synchronization. Only changes to the local database are
        /// sent to the server database.
        ///
        internal void QuickSync()
        {
            try
            {
                /// If the connection is already open, close it to perform a synchronization.
                ///
                if (ConnectionState.Open == cnIBuySpy.State)
                {
                    cnIBuySpy.Close();
                }

                if (SyncType.Replication == this.syncMethod)
                {
                    /// Perform synchronization using replication.
                    ///
                    ReplSync(SyncStatus.RegularSync, ExchangeType.Upload);
                }
                else if (SyncType.Rda == this.syncMethod)
                {
                    /// Perform synchronization using RDA.
                    ///
                    RdaSync(SyncStatus.RegularSync, ExchangeType.Upload);
                }
                else
                {
                    throw new Exception("Wrong synchronize method - " + this.syncMethod.ToString());
                }
            }
            finally
            {
                /// Reopen the connection to the local database after synchronization to
                /// access the local data.
                ///
                if (ConnectionState.Closed == this.cnIBuySpy.State)
                {
                    cnIBuySpy.Open();
                }
            }
        }

        /// Create a new Replication object, set its properties, and synchronize with the server database.
        ///
        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 subscriber 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();
            }
        }

        /// This method performs RDA synchronization (both upload only and bidirectional) to the server database.