void provsisonDB(SqlConnection clientConn)
{
try
{
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("CardsScope");
// get the description of the Products table from SyncDB dtabase
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("cards", clientConn);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tableDesc);
// create a server scope provisioning object based on the ProductScope
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);
// skipping the creation of table since table already exists on server
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// start the provisioning process
serverProvision.Apply();
}
catch (Exception exc)
{
Console.WriteLine(exc.ToString());
logger.Error(exc.ToString());
}
}
public static void ProvisionTableOnProvider(string pScopeName, string pTableName, string pProviderConnectionString)
{
try
{
// connect to server database
SqlConnection serverConn = new SqlConnection(pProviderConnectionString);
// connection string for Eskimos test
// SqlConnection serverConn = new SqlConnection("Data Source=q6.2eskimos.com; Initial Catalog=EskLeeTest; uid=test ; pwd=test1test");
// define a new scope named ProductsScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(pScopeName);
// get the description of the Products table from SyncDB dtabase
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(pTableName, serverConn);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tableDesc);
// create a server scope provisioning object based on the ProductScope
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
// skipping the creation of table since table already exists on server
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
serverProvision.Apply();
}
catch (Exception e)
{
string tempErrorMessage = "There was an exception whilst creating a provider provision: " + e;
Debug.WriteLine(tempErrorMessage);
Logs.ProvisioningLog.WriteLine(tempErrorMessage);
throw e;
}
}
private void ProvisionServer(string TableName)
{
try
{
// define a new scope named tableNameScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(TableName + _filter);
// get the description of the tableName
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(TableName, _serverConnection);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tableDesc);
// create a server scope provisioning object based on the tableNameScope
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(_serverConnection, scopeDesc);
// start the provisioning process
if (!serverProvision.ScopeExists(scopeDesc.ScopeName))
{
serverProvision.Apply();
//Console.WriteLine("Server " + TableName + " was provisioned.");
Log.WriteLogs("Server " + TableName + " was provisioned.");
}
else
{
//Console.WriteLine("Server " + TableName + " was already provisioned.");
Log.WriteLogs("Server " + TableName + " was already provisioned.");
}
}
catch (Exception ex)
{
Log.WriteErrorLogs(ex);
}
}
protected override void DefineLocalScope(DbSyncScopeDescription scopeDescription)
{
using (var connection = new SqlConnection(LocalConfiguration.ConnectionString))
{
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("obk_currencies", connection);
tableDesc.GlobalName = "currencies";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("obk_products", connection);
tableDesc.GlobalName = "products";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("obk_product_cost", connection);
tableDesc.GlobalName = "product_cost";
//tableDesc.Columns.Remove(tableDesc.Columns["cost_tenge"]);
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("obk_certifications", connection);
tableDesc.GlobalName = "certifications";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("obk_certification_copies", connection);
tableDesc.GlobalName = "certification_copies";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("obk_appendix", connection);
tableDesc.GlobalName = "appendix";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("obk_appendix_series", connection);
tableDesc.GlobalName = "appendix_series";
scopeDescription.Tables.Add(tableDesc);
}
}
protected override void DefineRemoteScope(DbSyncScopeDescription scopeDescription)
{
using (var connection = new SqlConnection(RemoteConfiguration.ConnectionString))
{
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("currencies", connection);
tableDesc.GlobalName = "currencies";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("products", connection);
tableDesc.GlobalName = "products";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("product_cost", connection);
tableDesc.GlobalName = "product_cost";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("certifications", connection);
tableDesc.GlobalName = "certifications";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("certification_copies", connection);
tableDesc.GlobalName = "certification_copies";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("appendix", connection);
tableDesc.GlobalName = "appendix";
scopeDescription.Tables.Add(tableDesc);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("appendix_series", connection);
tableDesc.GlobalName = "appendix_series";
scopeDescription.Tables.Add(tableDesc);
}
}
/// <summary>
/// Kiểm tra có SCOPE chưa
/// </summary>
/// <param name="connectionString"></param>
/// <param name="scope_name"></param>
/// <returns></returns>
public static int isHasScope(String connectionString, String scope_name, String[] tracking_tables)
{
SqlConnection serverConn = new SqlConnection(connectionString);
if (!isExist(serverConn))
{
return(-1);
}
try
{
// define a new scope named ProductsScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scope_name);
DbSyncTableDescription tableDesc = null;
foreach (String item in tracking_tables)
{
//parse
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(item, serverConn);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tableDesc);
}
SqlSyncScopeProvisioning tmp = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
return(tmp.ScopeExists(scope_name)?1:-1);
}
catch (Exception ex)
{
Debug.WriteLine(ex);
return(-1);
}
}
static void Main(string[] args)
{
//create connection to the server DB
SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncDB; Integrated Security=True");
// define the OrdersScope-NC filtered scope
// this scope filters records in the Orders table with OriginState set to NC"
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("OrdersScope-NC");
// get the description of the Orders table and add it to the scope
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Orders", serverConn);
scopeDesc.Tables.Add(tableDesc);
// create server provisioning object
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
// no need to create the Orders table since it already exists,
// so use the Skip parameter
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// set the filter column on the Orders table to OriginState
serverProvision.Tables["Orders"].AddFilterColumn("OriginState");
// set the filter value to NC
serverProvision.Tables["Orders"].FilterClause = "[side].[OriginState] = 'NC'";
// start the provisioning process
serverProvision.Apply();
}
public void ProvisionServer()
{
SqlConnection serverConn = new SqlConnection(sServerConnection);
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(sScope);
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("CUSTOMER", serverConn);
scopeDesc.Tables.Add(tableDesc);
DbSyncTableDescription productDescription2 =
SqlSyncDescriptionBuilder.GetDescriptionForTable("MOB",
serverConn);
scopeDesc.Tables.Add(productDescription2);
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
if (!serverProvision.ScopeExists(sScope))
{
serverProvision.Apply();
}
}
public void ActivateSqlSync()
{
SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=Balda; Integrated Security=True");
// Определить новую область с именем ProductsScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("BaldaScope");
// Получаем описание таблицы Изделия из SyncDB dtabase
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Dictionary", serverConn);
// Добавить описание таблицы для определения синхронизации области видимости
scopeDesc.Tables.Add(tableDesc);
// List<DbSyncTableDescription> tableDescD = new List<DbSyncTableDescription>();
for (int i = 3; i < MaxLengthWord; i++)
{
scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Dictionary" + i, serverConn));
}
// create a server scope provisioning object based on the ProductScope
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
// skipping the creation of table since table already exists on server
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// start the provisioning process
serverProvision.Apply();
}
private static void Initialize
(string table,
string serverConnectionString,
string clientConnectionString)
{
try
{
using (SqlConnection serverConnection = new
SqlConnection(serverConnectionString))
{
using (SqlConnection clientConnection = new
SqlConnection(clientConnectionString))
{
DbSyncScopeDescription scopeDescription = new
DbSyncScopeDescription(table);
DbSyncTableDescription tableDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable(table,
serverConnection);
scopeDescription.Tables.Add(tableDescription);
SqlSyncScopeProvisioning serverProvision = new
SqlSyncScopeProvisioning(serverConnection,
scopeDescription);
serverProvision.Apply();
SqlSyncScopeProvisioning clientProvision = new
SqlSyncScopeProvisioning(clientConnection,
scopeDescription);
clientProvision.Apply();
}
}
}
catch (Exception ex)
{
Common.WriteLog(System.DateTime.Now.ToString() + ": " + ex.ToString());
}
}
private static void createScope(SqlConnection connection)
{
var scopeDesc = new DbSyncScopeDescription("SyncScope");
// Definition for Customer.
DbSyncTableDescription customerDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable("Company", connection);
scopeDesc.Tables.Add(customerDescription);
customerDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable("Contact", connection);
scopeDesc.Tables.Add(customerDescription);
// Create a provisioning object for "SyncScope". We specify that
// base tables should not be created (They already exist in SyncSamplesDb_SqlPeer1),
// and that all synchronization-related objects should be created in a
// database schema named "Sync". If you specify a schema, it must already exist
// in the database.
var serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
// Configure the scope and change-tracking infrastructure.
serverConfig.Apply(connection);
connection.Close();
}
public void DescribeTableSchema(string tableName)
// Definition for the whole table
{
DbSyncTableDescription customerDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, this.ServerConn);
this.ScopeDesc.Tables.Add(customerDescription);
}
public void DescribePartialTableSchema(string tableName, Collection <string> columnsToInclude)
// Definition for part of the table
{
DbSyncTableDescription customerContactDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, columnsToInclude, this.ServerConn);
this.ScopeDesc.Tables.Add(customerContactDescription);
}
private DbSyncScopeDescription CrearAmbito(String nombreDeAmbito, string TablaDentroDelAmbito, IDbConnection conexionSql)
{
DbSyncScopeDescription Ambito = new DbSyncScopeDescription(nombreDeAmbito);
DbSyncTableDescription descripcionDeTabla = SqlSyncDescriptionBuilder.GetDescriptionForTable(TablaDentroDelAmbito, (SqlConnection)conexionSql);
Ambito.Tables.Add(descripcionDeTabla);
return(Ambito);
}
public static CodeTypeDeclaration GetEntityForTableDescription(DbSyncTableDescription tableDesc, bool addKeyAttributes, Dictionary <string, string> colsMapping)
{
CodeTypeDeclaration entityDeclaration = new CodeTypeDeclaration(SanitizeName(tableDesc.UnquotedGlobalName));
entityDeclaration.IsPartial = true;
entityDeclaration.IsClass = true;
foreach (DbSyncColumnDescription column in tableDesc.Columns)
{
string colName = column.UnquotedName;
if (colsMapping != null)
{
colsMapping.TryGetValue(column.UnquotedName.ToLowerInvariant(), out colName);
colName = colName ?? column.UnquotedName;
}
CodeTypeReference fieldTypeReference = GetTypeFromSqlType(tableDesc, column);
CodeMemberField colField = new CodeMemberField(fieldTypeReference, "_" + SanitizeName(colName));
colField.Attributes = MemberAttributes.Private;
CodeMemberProperty propertyField = new CodeMemberProperty();
propertyField.Attributes = MemberAttributes.Public | MemberAttributes.Final;
propertyField.Name = SanitizeName(colName);
propertyField.Type = fieldTypeReference;
propertyField.GetStatements.Add(new CodeMethodReturnStatement(new CodeArgumentReferenceExpression(colField.Name)));
propertyField.SetStatements.Add(new CodeAssignStatement(new CodeVariableReferenceExpression(colField.Name), new CodeVariableReferenceExpression("value")));
if (addKeyAttributes)
{
if (column.IsPrimaryKey)
{
//Add the Key attribute
propertyField.CustomAttributes.Add(new CodeAttributeDeclaration(Constants.ClientKeyAtributeType));
}
}
else
{
// This is service entity. Check to see if column mappings is present i.e colName is not the same as column.UnquotedName.
if (!colName.Equals(column.UnquotedName, StringComparison.Ordinal))
{
propertyField.CustomAttributes.Add(new CodeAttributeDeclaration(Constants.ServiceSyncColumnMappingAttribute,
new CodeAttributeArgument("LocalName", new CodeSnippetExpression("\"" + column.UnquotedName + "\""))));
}
// For a nullable data type, we add the [SyncEntityPropertyNullable] attribute to the property that is code-generated.
// This is required because some data types such as string are nullable by default in .NET and so there is no good way to
// later determine whether the type in the underlying data store is nullable or not.
if (column.IsNullable)
{
propertyField.CustomAttributes.Add(new CodeAttributeDeclaration(Constants.EntityPropertyNullableAttributeType));
}
}
entityDeclaration.Members.Add(colField);
entityDeclaration.Members.Add(propertyField);
}
return(entityDeclaration);
}
public static CodeTypeDeclaration GetEntityForTableDescription(DbSyncTableDescription tableDesc, bool addKeyAttributes, Dictionary<string, string> colsMapping)
{
CodeTypeDeclaration entityDeclaration = new CodeTypeDeclaration(SanitizeName(tableDesc.UnquotedGlobalName));
entityDeclaration.IsPartial = true;
entityDeclaration.IsClass = true;
foreach (DbSyncColumnDescription column in tableDesc.Columns)
{
string colName = column.UnquotedName;
if (colsMapping != null)
{
colsMapping.TryGetValue(column.UnquotedName.ToLowerInvariant(), out colName);
colName = colName ?? column.UnquotedName;
}
CodeTypeReference fieldTypeReference = GetTypeFromSqlType(tableDesc, column);
CodeMemberField colField = new CodeMemberField(fieldTypeReference, "_" + SanitizeName(colName));
colField.Attributes = MemberAttributes.Private;
CodeMemberProperty propertyField = new CodeMemberProperty();
propertyField.Attributes = MemberAttributes.Public | MemberAttributes.Final;
propertyField.Name = SanitizeName(colName);
propertyField.Type = fieldTypeReference;
propertyField.GetStatements.Add(new CodeMethodReturnStatement(new CodeArgumentReferenceExpression(colField.Name)));
propertyField.SetStatements.Add(new CodeAssignStatement(new CodeVariableReferenceExpression(colField.Name), new CodeVariableReferenceExpression("value")));
if (addKeyAttributes)
{
if (column.IsPrimaryKey)
{
//Add the Key attribute
propertyField.CustomAttributes.Add(new CodeAttributeDeclaration(Constants.ClientKeyAtributeType));
}
}
else
{
// This is service entity. Check to see if column mappings is present i.e colName is not the same as column.UnquotedName.
if (!colName.Equals(column.UnquotedName, StringComparison.Ordinal))
{
propertyField.CustomAttributes.Add(new CodeAttributeDeclaration(Constants.ServiceSyncColumnMappingAttribute,
new CodeAttributeArgument("LocalName", new CodeSnippetExpression("\"" + column.UnquotedName + "\""))));
}
// For a nullable data type, we add the [SyncEntityPropertyNullable] attribute to the property that is code-generated.
// This is required because some data types such as string are nullable by default in .NET and so there is no good way to
// later determine whether the type in the underlying data store is nullable or not.
if (column.IsNullable)
{
propertyField.CustomAttributes.Add(new CodeAttributeDeclaration(Constants.EntityPropertyNullableAttributeType));
}
}
entityDeclaration.Members.Add(colField);
entityDeclaration.Members.Add(propertyField);
}
return entityDeclaration;
}
private void tablesBox_ItemCheck(object sender, ItemCheckEventArgs e)
{
if (tablesBox.SelectedIndex > -1)
{
colsView.Rows.Clear();
var tableName = tablesBox.SelectedItem.ToString();
if (e.NewValue == CheckState.Unchecked)
{
// Remove it from the SyncTables collection
selectedScope.SyncTables.Remove(tableName);
filterClauseTxtBox.Text = string.Empty;
}
else if (e.NewValue == CheckState.Checked)
{
var table = new SyncTableConfigElement();
table.Name = tableName;
table.IncludeAllColumns = true;
var db = WizardHelper.Instance.SyncConfigSection.Databases.GetElementAt(dbsComboBox.SelectedIndex);
statusLbl.Visible = true;
try
{
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName,
new SqlConnection(db.GetConnectionString()));
// Issue a query to get list of all tables
foreach (var col in tableDesc.Columns)
{
var colConfig = new SyncColumnConfigElement
{
Name = col.UnquotedName,
IsPrimaryKey = col.IsPrimaryKey,
IsNullable = col.IsNullable,
SqlType = col.Type
};
table.SyncColumns.Add(colConfig);
}
DisplaySyncTableDetails(table);
}
catch (SqlException exp)
{
MessageBox.Show("Error in querying database. " + exp.Message, "Target Database Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
statusLbl.Visible = false;
}
// Add it to the sync table list
selectedScope.SyncTables.Add(table);
}
}
}
static void Main(string[] args)
{
SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQL2008; Initial Catalog=SyncDB; Integrated Security=true");
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("ProductsScope");
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Products", serverConn);
scopeDesc.Tables.Add(tableDesc);
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
serverProvision.Apply();
}
static void Main(string[] args)
{
SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQL2008; Initial Catalog=SyncDB; Integrated Security=true");
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("OrdersScope-NC");
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Orders", serverConn);
scopeDesc.Tables.Add(tableDesc);
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
serverProvision.Tables["Orders"].AddFilterColumn("OriginState");
serverProvision.Tables["Orders"].FilterClause = "[side].[OriginState] = 'NC'";
serverProvision.Apply();
}
protected virtual void ProvisionSyncScope(SqlSyncScopeProvisioning serverProvision, string syncScope, ICollection <string> syncTables, SqlConnection serverConnect, SqlSyncScopeProvisioningType provisionType)
{
// Create a sync scope if it is not existed yet
if (!string.IsNullOrEmpty(syncScope) && syncTables != null && syncTables.Any())
{
// Check if the sync scope or template exists
if (provisionType == SqlSyncScopeProvisioningType.Scope && serverProvision.ScopeExists(syncScope))
{
return;
}
if (provisionType == SqlSyncScopeProvisioningType.Template && serverProvision.TemplateExists(syncScope))
{
return;
}
// Define a new sync scope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(syncScope);
// Generate and add table descriptions to the sync scope
foreach (string tblName in syncTables)
{
// Get the description of a specific table
DbSyncTableDescription tblDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tblName, serverConnect);
// add the table description to the sync scope
scopeDesc.Tables.Add(tblDesc);
}
// Set the scope description from which the database should be provisioned
serverProvision.PopulateFromScopeDescription(scopeDesc);
if (provisionType == SqlSyncScopeProvisioningType.Template)
{
serverProvision.ObjectSchema = "Sync";
// apply dynamic filters
ApplyDynamicFilters(serverProvision, syncScope);
}
else
{
// apply static filters
ApplyStaticFilters(serverProvision, syncScope);
}
// Indicate that the base table already exists and does not need to be created
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
serverProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
// start the provisioning process
serverProvision.Apply();
}
}
private static void Initialize(string table, string serverConnectionString, string clientConnectionString)
{
using (SqlConnection serverConnection = new SqlConnection(serverConnectionString))
{
using (SqlConnection clientConnection = new SqlConnection(clientConnectionString))
{
DbSyncScopeDescription scopeDescription = new DbSyncScopeDescription(table);
DbSyncTableDescription tableDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable(table, serverConnection);
scopeDescription.Tables.Add(tableDescription);
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConnection, scopeDescription);
serverProvision.Apply();
SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConnection, scopeDescription);
clientProvision.Apply();
}
}
}
private void setupScopeToolStripMenuItem_Click(object sender, EventArgs e)
{
#if (DEBUG)
SqlConnection clientConn = new SqlConnection(Program.str_debug_clientConn);
#else
SqlConnection clientConn = new SqlConnection(Program.str_release_clientConn);
#endif
SqlConnection serverConn = new SqlConnection(Program.str_serverConn);
// define a new scope named PayPalSyncScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("PayPalSyncScope");
// get the description of the tblpaypal table from SERVER database
DbSyncTableDescription tblpaypalDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("tblpaypal", serverConn);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tblpaypalDesc);
// create a server scope provisioning object based on the PayPalSyncScope
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
// skipping the creation of table since table already exists on server
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// start the provisioning process
serverProvision.Apply();
Console.WriteLine("Server Successfully Provisioned.");
// get the description of SyncScope from the server database
DbSyncScopeDescription scopeDesc2 = SqlSyncDescriptionBuilder.GetDescriptionForScope("PayPalSyncScope", serverConn);
// create server provisioning object based on the SyncScope
SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc2);
// starts the provisioning process
clientProvision.Apply();
DialogResult result = DotNetPerls.BetterDialog.ShowDialog(
"Trans2Summa3060", //titleString
"Client and Server Successfully Provisioned.", //bigString
null, //smallString
null, //leftButton
"OK", //rightButton
global::Trans2Summa3060.Properties.Resources.Message_info); //iconSet
}
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();
}
protected override void DefineRemoteScope(DbSyncScopeDescription scopeDescription)
{
try
{
using (var connection = new SqlConnection(RemoteConfiguration.ConnectionString))
{
foreach (var tableMapping in _tableMappings)
{
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableMapping.TableNameSrc, connection);
tableDesc.GlobalName = tableMapping.GlobalName;
scopeDescription.Tables.Add(tableDesc);
}
}
}
catch (Exception e)
{
_logger.Error(e);
}
}
void provsisonDB(SqlConnection serverConn)
{
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("CardsScope");
// get the description of the Products table from SyncDB dtabase
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("cards", serverConn);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tableDesc);
// create a server scope provisioning object based on the ProductScope
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
// skipping the creation of table since table already exists on server
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// start the provisioning process
serverProvision.Apply();
}
private void CreateTemplate(string TableName, string filterColumn, string filterClause, SqlParameter param)
{
try
{
// Create a template named tableName + _Filter_Template
scopeDesc = new DbSyncScopeDescription(TableName + "_Filter_Template");
// Set a friendly description of the template.
scopeDesc.UserComment = "Filter template for " + TableName + ".";
// Definition for tables.
tableDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable(TableName, _serverConnection);
scopeDesc.Tables.Add(tableDescription);
// Create a provisioning object for "tableName_Filter_template" that can be used to create a template
// from which filtered synchronization scopes can be created.
serverTemplate = new SqlSyncScopeProvisioning(_serverConnection, scopeDesc, SqlSyncScopeProvisioningType.Template);
AddFilter(TableName, filterColumn, filterClause, param);
// create a new select changes stored proc for this scope
serverTemplate.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
// Create the tableName_Filter_template" template in the database.
if (!serverTemplate.TemplateExists(TableName + "_Filter_Template"))
{
serverTemplate.Apply();
//Console.WriteLine(TableName + " filter template was created.");
Log.WriteLogs(TableName + " filter template was created.");
}
else
{
//Console.WriteLine(TableName + " filter template was already exist.");
Log.WriteLogs(TableName + " filter template was already exist.");
}
}
catch (Exception ex)
{
Log.WriteErrorLogs(ex);
}
}
private static void FixPrimaryKeysForTable(DbSyncTableDescription description)
{
try
{
IEnumerator <DbSyncColumnDescription> enumerator = description.PkColumns.GetEnumerator();
while (enumerator.MoveNext())
{
DbSyncColumnDescription colDesc = enumerator.Current;
if (colDesc.IsPrimaryKey && colDesc.QuotedName != "SYNC_ID")
{
colDesc.IsPrimaryKey = false;
}
}
if (enumerator != null)
{
enumerator.Dispose();
}
enumerator = description.NonPkColumns.GetEnumerator();
while (enumerator.MoveNext())
{
DbSyncColumnDescription colDesc = enumerator.Current;
if (colDesc.UnquotedName == "SYNC_ID")
{
colDesc.IsPrimaryKey = true;
}
//if (colDesc.UnquotedName == "ID")
//{
// colDesc.IsPrimaryKey = true;
//}
}
if (enumerator != null)
{
enumerator.Dispose();
}
}
finally
{
//((IDisposable) description).Dispose();
}
}
/// <summary>
/// Cài đặt Sync SCOPE ở database chỉ định,
/// yêu cầu bắt buộc phải có cấu trúc CSDL trước,
/// Nếu Server có Scope trùng tên rồi thì không làm gì cả
/// </summary>
/// <param name="connectionString"></param>
/// <param name="scopeName"></param>
/// <param name="tracking_tables"></param>
public static int setup_sync_scope(String connectionString, String scope_name, String[] tracking_tables)
{
SqlConnection serverConn = new SqlConnection(connectionString);
try
{
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scope_name);
// get the description of the Products table from SyncDB dtabase
DbSyncTableDescription tableDesc = null;
foreach (String item in tracking_tables)
{
//parse
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(item, serverConn);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tableDesc);
}
// create a server scope provisioning object based on the ProductScope
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
// skipping the creation of table since table already exists on server
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// start the provisioning process
serverProvision.Apply();
return(1);
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString());
return(-1);
}
finally
{
serverConn.Dispose();
}
}
private void tablesBox_SelectedIndexChanged(object sender, EventArgs e)
{
this.filterClauseTxtBox.Text = string.Empty;
SyncTableConfigElement table = selectedScope.SyncTables.GetElement(this.tablesBox.SelectedItem.ToString());
if (table != null)
{
this.colsView.Rows.Clear();
this.filterClauseTxtBox.Text = table.FilterClause;
TargetDatabaseConfigElement db = WizardHelper.Instance.SyncConfigSection.Databases.GetElementAt(this.dbsComboBox.SelectedIndex);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(table.Name, new SqlConnection(db.GetConnectionString()));
// Display the list of currently selected items
this.DisplaySyncTableDetails(table);
}
else
{
colsView.Rows.Clear();
}
}
static void Main(string[] args)
{
SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncDB; Integrated Security=True");
// define a new scope named ProductsScope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("ProductsScope");
// get the description of the Products table from SyncDB dtabase
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Products", serverConn);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(tableDesc);
// create a server scope provisioning object based on the ProductScope
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
// skipping the creation of table since table already exists on server
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
// start the provisioning process
serverProvision.Apply();
}
static void Main(string[] args)
{
string strscopename = Properties.Settings.Default["ScopeName"].ToString();
string strserverConn = Properties.Settings.Default["serverConn"].ToString();
string strsyncTableEID = Properties.Settings.Default["syncTableEID"].ToString();
string strsyncTableVL = Properties.Settings.Default["syncTableVL"].ToString();
try
{
// connect to server database (The Public IP)
SqlConnection serverConn = new SqlConnection(strserverConn);
// define a new scope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(strscopename);
// get the description of the VLSummaryReport table from SERVER database
DbSyncTableDescription syncTableEID = SqlSyncDescriptionBuilder.GetDescriptionForTable(strsyncTableEID, serverConn);
DbSyncTableDescription syncTableVL = SqlSyncDescriptionBuilder.GetDescriptionForTable(strsyncTableVL, serverConn);
// add the table description to the sync scope definition
scopeDesc.Tables.Add(syncTableEID);
scopeDesc.Tables.Add(syncTableVL);
// create a server scope provisioning object based on the VLSyncScopeAlert
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
serverProvision.Apply();
Console.WriteLine("Server Successfully Provisioned.");
Console.ReadLine();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message + ex.Data.ToString());
Console.ReadLine();
}
}
private static DbSyncScopeDescription ParseCSDLDocument(ArgsParser parser, string uriString, XDocument document)
{
DbSyncScopeDescription scopeDescription = null;
Uri uri = new Uri(uriString);
// Assumption is that for OData Sync metadata document, the URI is of format http://foo/snc.svc/scopename/$metadata.
// In this case we are looking for the last but one segment.
string scopeName = uri.Segments[uri.Segments.Length - 2];
if (scopeName.EndsWith("/"))
{
scopeName = scopeName.Substring(0, scopeName.Length - 1);
}
if (parser.UseVerbose)
{
SyncSvcUtil.Log("Parsed ScopeName as {0}", scopeName);
}
// Its an CSDL document
XElement dataServicesElem = document.Root.Element(Constants.SyncScopeDataServicesElement);
if (dataServicesElem == null)
{
throw new CsdlException("No <DataServices> element found in the <edmx> document.");
}
XElement schemaElement = dataServicesElem.Element(Constants.SyncScopeSchemaElement);
if (schemaElement == null)
{
throw new CsdlException("No <Schema> element found in the <DataServices> document.");
}
scopeDescription = new DbSyncScopeDescription(scopeName);
// Loop over each <EntityType> element and add it as a DbSyncTableDescription
foreach (XElement entity in schemaElement.Elements(Constants.SyncScopeEntityTypeElement))
{
XAttribute nameAttr = entity.Attribute(Constants.SyncScopeEntityTypeNameAttribute);
if (nameAttr == null)
{
throw new CsdlException("<EntityType> has no Name attribute. \n" + entity.ToString());
}
// Parse each entity and create a DbSyncTableDescription
DbSyncTableDescription table = new DbSyncTableDescription(nameAttr.Value);
// Look for <Key> element
XElement keyElem = entity.Element(Constants.SyncScopeEntityTypeKeyElement);
if (keyElem == null)
{
throw new CsdlException("<EntityType> has no <Key> elements defined. \n" + entity.ToString());
}
List<string> keyNames = new List<string>();
// Loop over each <PropertyRef> element and add it to the list for lookup
foreach (XElement prop in keyElem.Elements(Constants.SyncScopeEntityTypeKeyRefElement))
{
XAttribute keyName = prop.Attribute(Constants.SyncScopeEntityTypeNameAttribute);
if (keyName != null)
{
keyNames.Add(keyName.Value);
}
}
// Loop over each <Property> element and add it as a DbSyncColumnDescription
foreach (XElement field in entity.Elements(Constants.SyncScopeEntityTypePropertyElement))
{
// Read Property name
XAttribute fieldName = field.Attribute(Constants.SyncScopeEntityTypeNameAttribute);
if (fieldName == null)
{
throw new CsdlException("<Property> has no Name attribute. \n" + field.ToString());
}
// Read Property Edm type
XAttribute fieldType = field.Attribute(Constants.SyncScopeEntityTypeTypeAttribute);
if (fieldType == null)
{
throw new CsdlException("<Property> has no Type attribute. \n" + field.ToString());
}
// Read Property Nullable attribute
XAttribute fieldNullable = field.Attribute(Constants.SyncScopeEntityTypeNullableAttribute);
DbSyncColumnDescription column = new DbSyncColumnDescription(fieldName.Value, GetSqlTypeForEdm(fieldType.Value));
if (fieldNullable != null && bool.Parse(fieldNullable.Value))
{
column.IsNullable = true;
}
column.IsPrimaryKey = keyNames.Contains(fieldName.Value);
table.Columns.Add(column);
}
scopeDescription.Tables.Add(table);
}
return scopeDescription;
}
/// <summary>
/// Denotes the mapping between the SQLType and the actual .NET CLR type
/// Uses the mapping defined in the following MSDN link http://msdn.microsoft.com/en-us/library/ms131092.aspx
/// </summary>
/// <param name="tableDesc">DbSyncTableDescription object</param>
/// <param name="colDesc">DbSyncColumnDescription object</param>
/// <returns> A .NET CLRT type name</returns>
private static CodeTypeReference GetTypeFromSqlType(DbSyncTableDescription tableDesc, DbSyncColumnDescription colDesc)
{
string sqltype = colDesc.Type;
bool isNullable = colDesc.IsNullable;
if(sqltype.Equals("sql_variant", StringComparison.OrdinalIgnoreCase))
{
sqltype = "variant";
}
SqlDbType type = (SqlDbType)Enum.Parse(typeof(SqlDbType), sqltype, true);
Type retType;
switch (type)
{
case SqlDbType.Bit:
retType = typeof(bool);
break;
case SqlDbType.BigInt:
retType = (typeof(Int64));
break;
case SqlDbType.Binary:
case SqlDbType.Image:
case SqlDbType.VarBinary:
case SqlDbType.Timestamp:
retType = (typeof(byte[]));
break;
case SqlDbType.Char:
retType = (typeof(string));
break;
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.SmallDateTime:
retType = (typeof(DateTime));
break;
case SqlDbType.DateTimeOffset:
retType = (typeof(DateTimeOffset));
break;
case SqlDbType.Decimal:
case SqlDbType.Money:
case SqlDbType.SmallMoney:
retType = (typeof(decimal));
break;
case SqlDbType.Float:
retType = (typeof(double));
break;
case SqlDbType.Int:
retType = (typeof(int));
break;
case SqlDbType.NChar:
case SqlDbType.Text:
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.VarChar:
case SqlDbType.Xml:
retType = (typeof(string));
break;
case SqlDbType.Real:
retType = (typeof(Single));
break;
case SqlDbType.SmallInt:
retType = (typeof(Int16));
break;
case SqlDbType.Time:
retType = (typeof(TimeSpan));
break;
case SqlDbType.TinyInt:
retType = (typeof(byte));
break;
case SqlDbType.UniqueIdentifier:
retType = (typeof(Guid));
break;
case SqlDbType.Variant:
default:
throw new NotSupportedException(string.Format("Column '{0}' in Table '{1}' has an unsupported SqlType - '{2}'",
colDesc.UnquotedName, tableDesc.UnquotedGlobalName, sqltype));
}
if (isNullable && retType.IsValueType)
{
CodeTypeReference ctr = new CodeTypeReference(typeof(Nullable<>));
ctr.TypeArguments.Add(retType);
return ctr;
}
else
{
return new CodeTypeReference(retType);
}
}
private void tablesBox_ItemCheck(object sender, ItemCheckEventArgs e)
{
if (tablesBox.SelectedIndex > -1)
{
this.colsView.Rows.Clear();
string tableName = this.tablesBox.SelectedItem.ToString();
if (e.NewValue == CheckState.Unchecked)
{
// Remove it from the SyncTables collection
selectedScope.SyncTables.Remove(tableName);
this.filterClauseTxtBox.Text = string.Empty;
}
else if (e.NewValue == CheckState.Checked)
{
SyncTableConfigElement table = new SyncTableConfigElement();
table.Name = tableName;
table.IncludeAllColumns = true;
TargetDatabaseConfigElement db = WizardHelper.Instance.SyncConfigSection.Databases.GetElementAt(this.dbsComboBox.SelectedIndex);
statusLbl.Visible = true;
try
{
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, new SqlConnection(db.GetConnectionString()));
// Issue a query to get list of all tables
foreach (DbSyncColumnDescription col in tableDesc.Columns)
{
SyncColumnConfigElement colConfig = new SyncColumnConfigElement()
{
Name = col.UnquotedName,
IsPrimaryKey = col.IsPrimaryKey,
IsNullable = col.IsNullable,
SqlType = col.Type,
};
table.SyncColumns.Add(colConfig);
}
this.DisplaySyncTableDetails(table);
}
catch (SqlException exp)
{
MessageBox.Show("Error in querying database. " + exp.Message, "Target Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
statusLbl.Visible = false;
}
// Add it to the sync table list
selectedScope.SyncTables.Add(table);
}
}
}
private void tablesBox_SelectedIndexChanged(object sender, EventArgs e)
{
this.filterClauseTxtBox.Text = string.Empty;
SyncTableConfigElement table = selectedScope.SyncTables.GetElement(this.tablesBox.SelectedItem.ToString());
if (table != null)
{
this.colsView.Rows.Clear();
this.filterClauseTxtBox.Text = table.FilterClause;
TargetDatabaseConfigElement db = WizardHelper.Instance.SyncConfigSection.Databases.GetElementAt(this.dbsComboBox.SelectedIndex);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(table.Name, new SqlConnection(db.GetConnectionString()));
// Display the list of currently selected items
this.DisplaySyncTableDetails(table);
}
else
{
colsView.Rows.Clear();
}
}
