예제 #1
0
        //Get Data From Client Provision
        public static void ProvisionClient()
        {
            SqlConnection serverConn = new SqlConnection(sServerConnection);
            SqlConnection clientConn = new SqlConnection(sClientConnection);

            //Drop scope_Info Table
            string cmdText = @"IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='scope_info') DROP table scope_info";

            clientConn.Open();
            SqlCommand cmd = new SqlCommand(cmdText, clientConn);

            cmd.ExecuteScalar();
            clientConn.Close();


            List <string> tables = new List <string>();

            // tables.Add("Accounts"); // Add Tables in List
            // tables.Add("Drinks");
            // tables.Add("Roles");
            // tables.Add("Tables");
            tables.Add("Toppings");

            var scopeDesc = new DbSyncScopeDescription("MainScope");

            foreach (var tbl in tables) //Add Tables in Scope
            {
                scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable(tbl, clientConn));
            }

            SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc); //Provisioning

            //skip creating the user tables
            clientProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);

            //skip creating the change tracking tables
            clientProvision.SetCreateTrackingTableDefault(DbSyncCreationOption.Skip);

            //skip creating the change tracking triggers
            clientProvision.SetCreateTriggersDefault(DbSyncCreationOption.Skip);

            //skip creating the insert/update/delete/selectrow SPs including those for metadata
            clientProvision.SetCreateProceduresDefault(DbSyncCreationOption.Skip);

            //create new SelectChanges SPs for selecting changes for the new scope
            //the new SelectChanges SPs will have a guid suffix
            clientProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);


            clientProvision.Apply();
        }
        public static void SetUp(string _pTableName)
        {
            // Connection to  SQL Server database
            SqlConnection serverConn =
                new SqlConnection(ServerConnString);
            // Connection to SQL client database
            SqlConnection clientConn =
                new SqlConnection(ClientConnString);

            // Create a scope named "product" and add tables to it.
            Console.WriteLine(_pTableName);
            DbSyncScopeDescription productScope = new DbSyncScopeDescription(_pTableName + "_SCOP");
            // Define the Products table.
            DbSyncTableDescription productDescription =
                SqlSyncDescriptionBuilder.GetDescriptionForTable(_pTableName, serverConn);

            // Add the Table to the scope object.
            productScope.Tables.Add(productDescription);
            // Create a provisioning object for "product" and apply it to the on-premise database if one does not exist.
            SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, productScope);

            serverProvision.ObjectSchema = ".dbo";
            serverProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
            serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
            serverProvision.SetCreateProceduresDefault(DbSyncCreationOption.CreateOrUseExisting);
            serverProvision.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting);
            serverProvision.SetCreateTriggersDefault(DbSyncCreationOption.CreateOrUseExisting);
            if (!serverProvision.ScopeExists(_pTableName + "_SCOP"))
            {
                serverProvision.Apply();
            }
            // Provision the SQL client database from the on-premise SQL Server database if one does not exist.
            SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, productScope);

            if (!clientProvision.ScopeExists(_pTableName + "_SCOP"))
            {
                clientProvision.Apply();
            }
            // Shut down database connections.
            serverConn.Close();
            serverConn.Dispose();
            clientConn.Close();
            clientConn.Dispose();
        }
예제 #3
0
        //Set Data To Server Provision
        public static void ProvisionServer()
        {
            SqlConnection serverConn = new SqlConnection(sServerConnection);

            string cmdText = @"IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='scope_info') DROP table scope_info";

            serverConn.Open();
            SqlCommand cmd = new SqlCommand(cmdText, serverConn);

            cmd.ExecuteScalar();
            serverConn.Close();

            List <string> tables = new List <string>();

            // tables.Add("Accounts"); // Add Tables in List
            // tables.Add("Drinks");
            // tables.Add("Roles");
            // tables.Add("Tables");
            tables.Add("Toppings");

            var scopeDesc = new DbSyncScopeDescription("MainScope");

            foreach (var tbl in tables)
            {
                scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable(tbl, serverConn));
            }

            SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc); // Create Provision From All Tables

            //skip creating the user tables
            serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);

            //skip creating the change tracking tables
            serverProvision.SetCreateTrackingTableDefault(DbSyncCreationOption.Skip);

            //skip creating the change tracking triggers
            serverProvision.SetCreateTriggersDefault(DbSyncCreationOption.Skip);

            //skip creating the insert/update/delete/selectrow SPs including those for metadata
            serverProvision.SetCreateProceduresDefault(DbSyncCreationOption.Skip);

            serverProvision.Apply();
        }
예제 #4
0
        private void UpdateExistingScopeProvision(SqlSyncScopeProvisioning provision, SqlConnection conn, bool isServer)
        {
            string alterScopeSql = string.Empty;

            provision.SetCreateProceduresDefault(DbSyncCreationOption.Create);
            provision.SetUseBulkProceduresDefault(true);

            provision.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting);
            var provisioningScript = provision.Script();
            //alterScopeSql = provision.Script();

            var stringBuilder = new StringBuilder();
            var changedTables = tables.Where(t => t.InScope.Value == 1).ToList();

            foreach (var changedTable in changedTables)
            {
                var tableName = isServer ? changedTable.ServerTableName : changedTable.ClientTableName;
                stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_bulkinsert];");
                stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_bulkupdate];");
                stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_bulkdelete];");
                stringBuilder.AppendLine("DROP TYPE [" + tableName + "_BulkType];");
                stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_selectchanges];");
                stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_selectrow];");
                stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_insert];");
                stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_update];");
                stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_delete];");
                stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_insertmetadata];");
                stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_updatemetadata];");
                stringBuilder.AppendLine("DROP PROCEDURE [" + tableName + "_deletemetadata];");
            }

            // append the sync provisioning script after the drop statements
            alterScopeSql = stringBuilder.Append(provisioningScript).ToString();

            int x           = alterScopeSql.IndexOf("N'<SqlSyncProviderScopeConfiguration");
            int y           = alterScopeSql.IndexOf("</SqlSyncProviderScopeConfiguration>");
            var configEntry = alterScopeSql.Substring(x, y - x) + "</SqlSyncProviderScopeConfiguration>'";

            x             = alterScopeSql.IndexOf("- BEGIN Add scope");
            y             = alterScopeSql.IndexOf("- END Add Scope");
            alterScopeSql = alterScopeSql.Remove(x, y - x);

            alterScopeSql = alterScopeSql.Replace("scope_status = 'C'", "config_data=" + configEntry);

            x             = alterScopeSql.IndexOf("WHERE [config_id] =");
            alterScopeSql = alterScopeSql.Remove(x, alterScopeSql.Length - x);

            alterScopeSql = alterScopeSql
                            + " WHERE [config_id] = (SELECT scope_config_id FROM scope_info WHERE sync_scope_name='"
                            + configuration.ScopeName + "')";

            using (var connection = new SqlConnection(conn.ConnectionString))
            {
                connection.Open();
                string[] commands = alterScopeSql.Split(new string[] { "GO\r\n", "GO ", "GO\t", "GO" }, StringSplitOptions.RemoveEmptyEntries);
                foreach (var c in commands)
                {
                    var command = new SqlCommand(c, connection);
                    command.ExecuteNonQuery();
                }
            }
        }