service.asmx.cs

微软的行业应用解决方案示例

        private SyncAdapter GetInventoryTableAdapter()
        {
            //server to client incremental updates
            SyncAdapter inventorySyncAdapter = GetDownloadOnlyTableAdapter("Inventory", "InventoryId", null);

            //client to server : only updates allowed (no inserts or deletes to propogate in this direction)
            SqlCommand invUpdateCommand = new SqlCommand();
            invUpdateCommand.CommandText = "spUpdateInventory";
            invUpdateCommand.CommandType = CommandType.StoredProcedure;
            invUpdateCommand.Parameters.Add("@Name", SqlDbType.VarChar, 100);
            invUpdateCommand.Parameters.Add("@Picture", SqlDbType.Image);
            invUpdateCommand.Parameters.Add("@Price", SqlDbType.Money);
            invUpdateCommand.Parameters.Add("@InStock", SqlDbType.Int);
            invUpdateCommand.Parameters.Add("@DistributionCenter", SqlDbType.Int);
            invUpdateCommand.Parameters.Add("@Bin", SqlDbType.Int);
            invUpdateCommand.Parameters.Add("@InventoryId", SqlDbType.Int);
            invUpdateCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Int);
            invUpdateCommand.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
            inventorySyncAdapter.UpdateCommand = invUpdateCommand;

            return inventorySyncAdapter;
        }
        
        /// <summary>
        /// Generic table adapter builder function for tables
        /// that are download only (with incremental updates) and that 
        /// use all the columns 
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="idColumnName"></param>
        /// <param name="serverConnection"></param>
        /// <returns>SyncAdapter for the table with the inser/update/delete commands setup and the 
        /// connection object attached
        /// </returns>
        private SyncAdapter GetDownloadOnlyTableAdapter(string tableName, string idColumnName, string[] columnsToSync)
        {
            //name it same as the table
            SyncAdapter syncAdapter = new SyncAdapter(tableName);

            //get string that can be used in a sql statement
            string columnString = GetColumnsString(columnsToSync, "originalTable");

            //Using the SQL Server 2008 feature of Change Tracking. 
            //No special columns are created for the tables. The in built mechanism 
            //takes care of this for us. Using the Changes() function we can track the
            //rows that have changed since the last version.
            //Tracking must be enable both at database and individual table scope 

            //select inserts from the server
            SqlCommand incrementalInsertCommand = new SqlCommand();
            incrementalInsertCommand.CommandText = String.Format(
                            @"SELECT {0} FROM {1} as originalTable
                            INNER JOIN ChangeTable(changes {1}, {2} ) ct 
                            ON ct.{3} = originalTable.{3}
                            where ct.SYS_CHANGE_OPERATION = '{4}'
                            ", columnString , tableName, "@" + SyncSession.SyncLastReceivedAnchor, idColumnName, OPERATION_INSERT);
            incrementalInsertCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
            syncAdapter.SelectIncrementalInsertsCommand = incrementalInsertCommand;

            //Select updates from the server.
            SqlCommand incrementalUpdateCommand = new SqlCommand();
            incrementalUpdateCommand.CommandText = String.Format(
                            @"SELECT {0} FROM {1} as originalTable
                            INNER JOIN ChangeTable(changes {1}, {2} ) ct 
                            ON ct.{3} = originalTable.{3}
                            where ct.SYS_CHANGE_OPERATION = '{4}'
                            ", columnString, tableName, "@" + SyncSession.SyncLastReceivedAnchor, idColumnName, OPERATION_UPDATE);
            incrementalUpdateCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
            syncAdapter.SelectIncrementalUpdatesCommand = incrementalUpdateCommand;

            //Select deletes from the server.
            SqlCommand incrementalDeleteCommand = new SqlCommand();
            incrementalDeleteCommand.CommandText = String.Format(
                            @"SELECT {0} FROM ChangeTable(changes {1}, {2} ) ct 
                            where ct.SYS_CHANGE_OPERATION = '{3}'
                            ", idColumnName, tableName, "@" + SyncSession.SyncLastReceivedAnchor, OPERATION_DELETE);
            incrementalDeleteCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
            syncAdapter.SelectIncrementalDeletesCommand = incrementalDeleteCommand;

            return syncAdapter;

        }

        /// <summary>
        /// returns a comma delimited string of columns that can be plugged into a SQL statement
        /// </summary>
        /// <param name="columnsToSync"></param>
        /// <returns></returns>
        private static string GetColumnsString(string[] columnsToSync, string prefix)
        {
            string columnString;

            //for non-empty column array, build a comma delimited string 
            if (columnsToSync != null && columnsToSync.Length > 0)
            {
                columnString = String.Join(",", columnsToSync);

                //append the prefix to all the elements
                columnString = columnString.Replace(",", "," + prefix + ".");

                //and to the first one
                columnString = prefix + columnString;
            }

            else
            {
                columnString = prefix + ".*";
            }

            return columnString;
        }


        /// <summary>
        /// Use the database specific arhitecture to determine the anchor function
        /// Here we use the latest SQL 2008 feature of Change Tracking to anchor the version
        /// </summary>
        /// <param name="serverConn"></param>
        /// <returns></returns>
        private IDbCommand GetNewAnchorCommand(SqlConnection serverConn)
        {
            SqlCommand selectNewAnchorCommand = new SqlCommand();


            string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;

            //SQL Server 2008 supports change_tracking_current_version() that returns 
            //the latest version number for the database
            selectNewAnchorCommand.CommandText =
                "SELECT " + newAnchorVariable + " = change_tracking_current_version()";
            selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Int);
            selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;

            //hook up the connection
            selectNewAnchorCommand.Connection = serverConn;

            return selectNewAnchorCommand;
        }

        [WebMethod]
        public SyncServerInfo GetServerInfo(SyncSession session)
        {
            SyncServerInfo si ;
            try
            {

                si = _serverSyncProvider.GetServerInfo(session);
            }
            catch (Exception e)
            {
                throw;
            }

            return si;
        }

        [WebMethod]
        public SyncSchema GetSchema(System.Collections.ObjectModel.Collection<string> tableNames,
           SyncSession session)
        {
            SyncSchema ss = null;

            try
            {
                ss = _serverSyncProvider.GetSchema(tableNames, session);
            }
            catch (Exception e)
            {
                throw;
            }

            return ss;
        }

        [WebMethod]
        public SyncContext GetChanges(SyncGroupMetadata groupMetadata,
           SyncSession syncSession)
        {
            SyncContext context = null;
            try
            {

                context = _serverSyncProvider.GetChanges(groupMetadata,
                                                      syncSession);
            }
            catch (Exception e)
            {
                throw;
            }

            return context;
        }

        [WebMethod]
        public SyncContext ApplyChanges(SyncGroupMetadata groupMetadata,
           DataSet dataSet, SyncSession syncSession)
        {
            SyncContext sc = null;
            try
            {
                sc = _serverSyncProvider.ApplyChanges(groupMetadata, dataSet,
                   syncSession);
            }
            catch (Exception e)
            {
                throw;
            }

            return sc;
        }


    }
}