private void butSync_Click(object sender, RoutedEventArgs e)
{
string server = txtServername.Text;
string targetDb = this.txtDbName.Text;
string username = this.txtUsername.Text;
string pwd = passwordBox1.Password;
bool isTrustedConn = (bool)chkIsTrustedConn.IsChecked;
bool isSql05Express = (bool)chkIsSql05Expresss.IsChecked;
if (string.IsNullOrEmpty(server) || string.IsNullOrEmpty(targetDb) ||
string.IsNullOrEmpty(username))
{
"ImportEachElement".GetFromResourece().Notify();
return;
}
ICoreEAHander targetCore = new CoreEA.CoreE(CoreEA.CoreE.UsedDatabaseType.SqlServer).X_Handler;
LoginInfo_SqlServer info = new LoginInfo_SqlServer()
{
X_Server = server, X_Database = targetDb, X_UserName = username, X_Pwd = pwd
, IsTrustedConn = (bool)chkIsTrustedConn.IsChecked
};
if (isSql05Express)
{
info.X_CurDbConnectionMode = CurDbServerConnMode.SqlServer2005Express;
}
try
{
targetCore.Open(info);
SqlBulkCopy cp = new SqlBulkCopy((SqlConnection)targetCore.GetConnection());
foreach (string item in tableList)
{
if (targetCore.GetTableListInDatabase(targetDb).Contains(item))
{
cp.DestinationTableName = item;
DataRowCollection coll = srcCore.GetAllDataFromTable(item).Rows;
DataRow[] rows = new DataRow[coll.Count];
int i = 0;
foreach (DataRow subRow in coll)
{
rows[i] = subRow;
}
cp.WriteToServer(rows);
"Complete".Notify();
}
else
{
//Create New Table and do .
}
}
}
catch (Exception ee)
{
ee.HandleMyException();
}
}
/// <summary>
/// Mose Like Excel
/// Just different in Connection String
/// </summary>
/// <param name="csvFile"></param>
/// <param name="sdfFile"></param>
/// <param name="p"></param>
/// <param name="p_4"></param>
/// <returns></returns>
internal static bool ConvertCSVToSdf(string csvFile, string sdfFile, bool NeedCopyData, string targetDbPwd, bool isFirstRowIsColumnName)
{
bool result = false;
if (!File.Exists(csvFile))
{
"ImportData_FileNotFound".GetFromResourece().Notify();
return(false);
}
ICoreEAHander srcEngine = new CoreEA.CoreE(CoreE.UsedDatabaseType.CSV).X_Handler;
srcEngine.Open(new LoginInfo_CSV()
{
Database = csvFile, IsFirstRowIsColumnName = isFirstRowIsColumnName
});
if (!srcEngine.IsOpened)
{
"ImportData_ReadError".GetFromResourece().Notify();
return(false);
}
List <string> tableList = srcEngine.GetTableListInDatabase();
ICoreEAHander destEngine = new CoreEA.CoreE(CoreE.UsedDatabaseType.SqlCE35).X_Handler;
//IF the ce database not existed ,then create it .
if (!File.Exists(sdfFile))
{
if (!destEngine.CreateDatabase(new LoginInfo_SSCE()
{
DbName = sdfFile
}))
{
"ImportData_CreateSSCEFileFailure".GetFromResourece().Notify();
return(false);
}
}
destEngine.Open(new LoginInfo_SSCE()
{
DbName = sdfFile, Pwd = "", IsEncrypted = false, CurOpenMode = OpenMode.ReadWrite
});
List <string> targetDbList = destEngine.GetTableListInDatabase();
try
{
foreach (string tableName in tableList)
{
//Don't import table which name has existed.
if (targetDbList.Contains(tableName))
{
continue;
}
string sqlCeTableName = tableName;
string strconnection = CoreEA.ConnSTR.DbConnectionString.TxtFile.OleDb_DelimitedColumns(csvFile, true);
ADODB.Connection conn = new ADODB.ConnectionClass();
//conn.ConnectionString = strconnection;
conn.Open(strconnection, "", "", 0);
//Prepare to retrive schema info from access via COM
ADOX.Catalog catelog = new ADOX.CatalogClass();
catelog.let_ActiveConnection(conn);
ADOX.Table tempTable = catelog.Tables[tableName];
//Start Generate the Create Sdf table command
string tempCreateTableCmd = string.Empty;
tempCreateTableCmd = String.Format("CREATE TABLE [{0}] ", sqlCeTableName);
string tempSechma = string.Empty;
for (int i = 0; i < tempTable.Columns.Count; i++)
{
Debug.WriteLine("Source Field Name ------>" + tempTable.Columns[i].Name);
tempSechma += String.Format("{0} {1},",
tempTable.Columns[i].Name,
CoreEA.Utility.TypeConvertor.ParseADODbTypeToSqlCeDbType(tempTable.Columns[i].Type.ToString(),
tempTable.Columns[i].DefinedSize)
);
}
tempSechma = tempSechma.Substring(0, tempSechma.Length - 1);
tempCreateTableCmd = String.Format("{0} ({1})", tempCreateTableCmd, tempSechma);
if (destEngine.DoExecuteNonQuery(tempCreateTableCmd) != -1)
{
throw new Exception(string.Format("Create table {0} error", tableName));
}
if (NeedCopyData)
{
CopyTable(srcEngine.GetConnection(), (SqlCeConnection)destEngine.GetConnection(),
string.Format("Select * from [{0}]", tableName), sqlCeTableName);
}
}
result = true;
}
catch (Exception ee)
{
ee.HandleMyException();
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="core"></param>
/// <param name="targetCeDBFile"></param>
/// <param name="prcessTableList"></param>
/// <param name="isNeedCopyData"></param>
/// <returns></returns>
public static List <SyncResultArgs> SyncDataFromSqlServerToSSCE(CoreEA.ICoreEAHander srcEngine, string targetCeDBFile,
List <string> prcessTableList, bool isNeedCopyData)
{
List <SyncResultArgs> resultInfo = null;
if (!srcEngine.IsOpened)
{
throw new ArgumentException("Need opened core object");
}
ICoreEAHander destEngine = new CoreEA.CoreE(CoreE.UsedDatabaseType.SqlCE35).X_Handler;
//IF the ce database not existed ,then create it .
if (!File.Exists(targetCeDBFile))
{
if (!destEngine.CreateDatabase(new LoginInfo_SSCE()
{
DbName = targetCeDBFile, Pwd = ""
}))
{
"ImportData_CreateSSCEFileFailure".GetFromResourece().Notify();
return(null);
}
}
destEngine.Open(new LoginInfo_SSCE()
{
DbName = targetCeDBFile, Pwd = "", IsEncrypted = false, CurOpenMode = OpenMode.ReadWrite
});
try
{
List <string> tableList = srcEngine.GetTableListInDatabase();
if (tableList.Count <= 0)
{
"ImportData_NoTable".GetFromResourece().Notify();
return(null);
}
resultInfo = new List <SyncResultArgs>();
foreach (string srcSqlServerTableName in tableList)
{
if (prcessTableList.Count > 0)
{
//If not in the need process table list ,then do not process it .
if (!prcessTableList.Contains(srcSqlServerTableName))
{
continue;
}
}
string sqlCeTableName = srcSqlServerTableName;
DataTable tempDs = srcEngine.GetColumnInfoFromTable(srcSqlServerTableName);
DataTable tempTable = tempDs;
#if DEBUG
tempDs.WriteXml(GlobalDefine.MyGlobal.GlobalDebugFolder + "SourceSchema.xml");
#else
#endif
SyncResultArgs args = new SyncResultArgs();
args.TableName = srcSqlServerTableName;
//Start Generate the Create Sdf table command
string tempCreateTableCmd = string.Empty;
tempCreateTableCmd = String.Format("CREATE TABLE [{0}] ", sqlCeTableName);
string tempSechma = string.Empty;
for (int i = 0; i < tempTable.Rows.Count; i++)
{
Debug.WriteLine("Source Field Name ------>" + tempTable.Rows[i]["COLUMN_NAME"].ToString());
//获取每个字段的类型和长度,If null then Each Type Define the size themself
//in ParseSqlServerDbTypeToSqlCeDbType method
int?length = null;
string lenNode =
CoreEA.Utility.TypeConvertor.ParseSqlServerLengthNodeNameFromTypeName(
tempTable.Rows[i]["DATA_TYPE"].ToString()
);
if ((!string.IsNullOrEmpty(lenNode)) && ((tempTable.Rows[i][lenNode] != DBNull.Value)))
{
length = int.Parse(tempTable.Rows[i][lenNode].ToString());
}
//建上述结果转换成SSCE 类型和语法
string appendix = CoreEA.Utility.TypeConvertor.ParseSqlServerDbTypeToSqlCeDbType(tempTable.Rows[i]["DATA_TYPE"].ToString(), length);
tempSechma += String.Format("{0} {1},", tempTable.Rows[i]["COLUMN_NAME"].ToString(), appendix);
}
tempSechma = tempSechma.Substring(0, tempSechma.Length - 1);
tempCreateTableCmd = String.Format("{0} ({1})", tempCreateTableCmd, tempSechma);
if (destEngine.DoExecuteNonQuery(tempCreateTableCmd) != -1)
{
args.LastErrorMsg = "Can't Create Target Table";
args.ProcessStatus = false;
resultInfo.Add(args);
//如果出错，继续执行下一次转换
continue;
}
if (isNeedCopyData)
{
CommonUtil.CopyTable(srcEngine.GetConnection(), (SqlCeConnection)destEngine.GetConnection(),
String.Format("select * from {0}", srcSqlServerTableName), sqlCeTableName);
}
args.ProcessStatus = true;
resultInfo.Add(args);
}
}
catch (Exception ee)
{
ee.HandleMyException();
//((SqlCeDatabase)destEngine.DbHandler).CloseSharedConnection();
if (File.Exists(targetCeDBFile))
{
File.Delete(targetCeDBFile);
}
}
return(resultInfo);
}
/// <summary>
/// Accesss to sqlce
/// </summary>
/// <param name="p"></param>
/// <param name="sdfFile"></param>
public static bool SyncMdbToSdf(string mdbFile, string sdfFile, bool NeedCopyData, string targetDbPwd)
{
bool result = false;
if (!File.Exists(mdbFile))
{
"ImportData_FileNotFound".GetFromResourece().Notify();
return(false);
}
ICoreEAHander srcEngine = new CoreEA.CoreE(CoreE.UsedDatabaseType.OleDb).X_Handler;
srcEngine.Open(new LoginInfo_Oledb()
{
Database = mdbFile
});
if (!srcEngine.IsOpened)
{
"ImportData_ReadError".GetFromResourece().Notify();
return(false);
}
//Filter system table
List <string> tableList = new List <string>();
foreach (string item in srcEngine.GetTableListInDatabase())
{
if (!item.StartsWith("MSys"))
{
tableList.Add(item);
}
}
if (tableList == null)
{
"ImportData_NoTable".GetFromResourece().Notify();
return(false);
}
ICoreEAHander destEngine = new CoreEA.CoreE(CoreE.UsedDatabaseType.SqlCE35).X_Handler;
if (!File.Exists(sdfFile))
{
if (!destEngine.CreateDatabase(new LoginInfo_SSCE()
{
DbName = sdfFile, IsEncrypted = false, IsCaseSensitive = false
}))
{
"ImportData_CreateSSCEFileFailure".GetFromResourece().Notify();
return(false);
}
}
destEngine.Open(new LoginInfo_SSCE()
{
DbName = sdfFile, Pwd = targetDbPwd
});
List <string> targetDBList = destEngine.GetTableListInDatabase();
try
{
foreach (string tableName in tableList)
{
//Don't import table which name has existed.
if (targetDBList.Contains(tableName))
{
continue;
}
string sqlCeTableName = tableName;
//if (Properties.Settings.Default.IsAllowAutoParseInvalidCharsInTableName)
//{
// sqlCeTableName= sqlCeTableName.Replace(" ", "");
//}
string strconnection = string.Format("provider=microsoft.jet.oledb.4.0;data source={0}", mdbFile);
ADODB.Connection conn = new ADODB.ConnectionClass();
//conn.ConnectionString = strconnection;
conn.Open(strconnection, "Admin", "", 0);
//Prepare to retrive schema info from access via COM
ADOX.Catalog catelog = new ADOX.CatalogClass();
catelog.let_ActiveConnection(conn);
ADOX.Table tempTable = catelog.Tables[tableName];
//Start Generate the Create Sdf table command
string tempCreateTableCmd = string.Empty;
tempCreateTableCmd = String.Format("CREATE TABLE [{0}] ", sqlCeTableName);
string tempSechma = string.Empty;
for (int i = 0; i < tempTable.Columns.Count; i++)
{
Debug.WriteLine("Source Field Name ------>" + tempTable.Columns[i].Name);
tempSechma += String.Format("[{0}] {1},",
tempTable.Columns[i].Name,
CoreEA.Utility.TypeConvertor.ParseADODbTypeToSqlCeDbType(tempTable.Columns[i].Type.ToString(),
tempTable.Columns[i].DefinedSize)
);
}
tempSechma = tempSechma.Substring(0, tempSechma.Length - 1);
tempCreateTableCmd = String.Format("{0} ({1})", tempCreateTableCmd, tempSechma);
if (destEngine.DoExecuteNonQuery(tempCreateTableCmd) != -1)
{
return(false);
}
if (NeedCopyData)
{
CopyTable(srcEngine.GetConnection(), (SqlCeConnection)destEngine.GetConnection(),
string.Format("Select * from [{0}]", tableName), sqlCeTableName);
}
}
result = true;
}
catch (Exception ee)
{
ee.HandleMyException();
//((SqlCeDatabase)destEngine.DbHandler).CloseSharedConnection();
}
return(result);
}
/// <summary>
/// mysql to sqlce
/// </summary>
/// <param name="server"></param>
/// <param name="database"></param>
/// <param name="username"></param>
/// <param name="pwd"></param>
/// <param name="sdfFile"></param>
/// <param name="isCopyData"></param>
/// <returns></returns>
public static bool SyncDataFromMysqlToSqlCE(string server, string database, string username, string pwd, string sdfFile, bool isCopyData)
{
bool result = false;
if (string.IsNullOrEmpty(server) || string.IsNullOrEmpty(database) || string.IsNullOrEmpty(username) || string.IsNullOrEmpty(sdfFile))
{
throw new Exception("Not valid parameters");
}
ICoreEAHander targetEnginer = new CoreEA.CoreE(CoreE.UsedDatabaseType.SqlCE35).X_Handler;
if (!File.Exists(sdfFile))
{
if (!targetEnginer.CreateDatabase(new LoginInfo_SSCE()
{
DbName = sdfFile
}))
{
"ImportData_CreateSSCEFileFailure".GetFromResourece().Notify();
return(false);
}
}
targetEnginer.Open(new LoginInfo_SSCE()
{
DbName = sdfFile, Pwd = "", IsEncrypted = false, CurOpenMode = OpenMode.ReadWrite
});
ICoreEAHander srcEngineer = new CoreEA.CoreE(CoreE.UsedDatabaseType.MySql).X_Handler;
try
{
srcEngineer.Open(new LoginInfo_MySql()
{
Server = server, Database = database, Username = username, Pwd = pwd
});
List <string> tableList = srcEngineer.GetTableListInDatabase(database);
if (tableList.Count <= 0)
{
"ImportData_NoTable".GetFromResourece().Notify();
return(false);
}
List <string> targetDbList = targetEnginer.GetTableListInDatabase();
foreach (string tableName in tableList)
{
//Don't import table which name has existed.
if (targetDbList.Contains(tableName))
{
continue;
}
string sqlCeTableName = tableName;
DataTable tempTable = srcEngineer.GetColumnInfoFromTable(tableName);
//Start Generate the Create Sdf table command
string tempCreateTableCmd = string.Empty;
tempCreateTableCmd = String.Format("CREATE TABLE [{0}] ", sqlCeTableName);
string tempSechma = string.Empty;
for (int i = 0; i < tempTable.Rows.Count; i++)
{
Debug.WriteLine("Source Field Name ------>" + tempTable.Rows[i]["COLUMN_NAME"].ToString());
tempSechma += String.Format("{0} {1},", tempTable.Rows[i]["COLUMN_NAME"].ToString(),
CoreEA.Utility.TypeConvertor.ParseMySqlDbTypeToSqlCeDbType(tempTable.Rows[i]["COLUMN_TYPE"].ToString()));
}
tempSechma = tempSechma.Substring(0, tempSechma.Length - 1);
tempCreateTableCmd = String.Format("{0} ({1})", tempCreateTableCmd, tempSechma);
if (targetEnginer.DoExecuteNonQuery(tempCreateTableCmd) != -1)
{
throw new Exception(string.Format("Create table {0} error", tableName));
}
if (isCopyData)
{
CommonUtil.CopyTable(srcEngineer.GetConnection(),
(SqlCeConnection)targetEnginer.GetConnection(),
String.Format("select * from `{0}`", tableName), sqlCeTableName);
}
result = true;
}
}
catch (Exception ee)
{
ee.HandleMyException();
//((SqlCeDatabase)targetEnginer.DbHandler).CloseSharedConnection();
if (File.Exists(sdfFile))
{
File.Delete(sdfFile);
}
}
return(result);
}