/// <summary>
/// Insert data from source to target handler
/// If columns has image type ,we should pay attention and we will use another way to process is
/// </summary>
/// <param name="srcHandle"></param>
/// <param name="targetHandle"></param>
/// <param name="tableName"></param>
public static bool ExchangeDataBetweenAnyDbs(ICoreEAHander srcHandle, ICoreEAHander targetHandle, string tableName)
{
bool ret = false;
try
{
bool isContainImageColumn = false;
//read data form source table
DbCommand srcCommand = srcHandle.GetConnection().CreateCommand();
srcCommand.CommandText = "select * from " + targetHandle.GetMaskedTableName(tableName);
IDataReader srcReader = srcCommand.ExecuteReader();
object[] values;
while (srcReader.Read())
{
//This map is help manage the column name and column value
//for string builder to make it
Dictionary <string, object> valueMap = new Dictionary <string, object>();
BaseTableSchema schema = srcHandle.GetTableSchemaInfoObject(tableName);
StringBuilder st = new StringBuilder();
st.Append(string.Format("insert into {0} (", targetHandle.GetMaskedTableName(tableName)));
//These two vars to allow do not add , in last column and values
int allColumnCount = schema.Columns.Count;
int tempColumnCursor = 0;
foreach (var column in schema.Columns)
{
tempColumnCursor++;
st.Append(targetHandle.GetMaskedColumnName(column.ColumnName));
if (tempColumnCursor != allColumnCount)
{
st.Append(",");
}
valueMap.Add(column.ColumnName, null);
}
st.Append(") Values (");
#region Get Maps
values = new object[srcReader.FieldCount];
srcReader.GetValues(values);
for (int i = 0; i < srcReader.FieldCount; i++)
{
valueMap[srcReader.GetName(i)] = srcReader.GetValue(i);
}
#endregion
//These two vars to allow do not add , in last column and values
int last = valueMap.Count;
int tempCursor = 0;
foreach (var item in valueMap)
{
tempCursor++;
Type curObjectType = item.Value.GetType();
Debug.WriteLine("Current object type is " + curObjectType);
//If empty column append the ''
if ((item.Value == null) || (item.Value.ToString() == string.Empty))
{
st.Append(@"''");
}
else
{
#region All Kinds Filter (Vital)
string filtedValue = item.Value.ToString().Replace("'", "");
//filtedValue = filtedValue.Replace("\r", "char(10)");
//filtedValue = filtedValue.Replace("\n", "char(13)");
//filtedValue = filtedValue.Replace("\r\n", @"\r\n");
//if string type ,
//filter '
//append '' in header and footer
if ((curObjectType == typeof(string)) ||
(curObjectType == typeof(char))
)
{
st.Append(string.Format("'{0}'", filtedValue));
}
else if (curObjectType == typeof(bool))
{
if (item.Value.ToString().ToLower() == "true")
{
st.Append(string.Format("1"));
}
else
{
st.Append(string.Format("0"));
}
}
else if (curObjectType == typeof(Byte[]))
{
//Please pay attention on Image type.
//Looks like there is no way to process image (byte) data type directly in sql
//We recommend to use Sqlparameter to insert Image Data.
isContainImageColumn = true;
break;
//string tempstr = XLCS.Common.CharExchange.GetString((Byte[])item.Value);
//st.Append(string.Format("'{0}'", tempstr));
}
else if (curObjectType == typeof(DateTime))
{
st.Append(string.Format("'{0}'", filtedValue));
}
else if (curObjectType == typeof(System.Guid))
{
st.Append(string.Format("'{0}'", filtedValue));
}
else
{
st.Append(string.Format("{0}", filtedValue));
}
#endregion
}
if (tempCursor != last)
{
st.Append(",");
}
}
try
{
#if IGNORE_IMAGE_ERROR
st.Append(")");
string insertCmdText = st.ToString();
DbCommand targetCommand = targetHandle.GetConnection().CreateCommand();
targetCommand.CommandText = insertCmdText;
targetCommand.ExecuteNonQuery();
#else
if (isContainImageColumn)
{
throw new NotImplementedException();
DbCommand cmd = targetHandle.GetNewCommand();
//cmd.Parameters.Add("@images", SqlDbType.Image).Value = imageb;
}
else
{
st.Append(")");
string insertCmdText = st.ToString();
DbCommand targetCommand = targetHandle.GetConnection().CreateCommand();
targetCommand.CommandText = insertCmdText;
Debug.WriteLine(insertCmdText);
targetCommand.ExecuteNonQuery();
Debug.WriteLine("Execute ok --------->" + insertCmdText);
}
#endif
}
catch (Exception myEx)
{
throw myEx;
}
}
ret = true;
}
catch (Exception eee)
{
throw eee;
}
return(ret);
}
public virtual ContextMenu CreateMenuForTable()
{
ContextMenu ctMenu = new ContextMenu();
#region select all
MenuItem itemSelectAll = new MenuItem();
itemSelectAll.Header = "TitleSelectAll".GetFromResourece();
itemSelectAll.Click += (s, e) =>
{
string cmdStr = string.Format("Select * from {0}", App.MainEngineer.GetMaskedTableName(X_CurItemArgs.TableName));
X_CallNotifyEvent(cmdStr, true);
};
ctMenu.Items.Add(itemSelectAll);
#endregion
ctMenu.Items.Add(new Separator());
#region Select
MenuItem itemSelect = new MenuItem();
itemSelect.Header = "TitleSelect".GetFromResourece();
itemSelect.Click += (s1, e1) =>
{
X_CallNotifyEvent(String.Format("SELECT * FROM {0}", App.MainEngineer.GetMaskedTableName(X_CurItemArgs.TableName)), false);
};
ctMenu.Items.Add(itemSelect);
#endregion
#region Insert
MenuItem itemInsert = new MenuItem();
itemInsert.Header = "TitleInsert".GetFromResourece();
itemInsert.Click += delegate
{
itemInsert_Click(X_CurItemArgs.TableName);
};
ctMenu.Items.Add(itemInsert);
#endregion
#region Update
MenuItem itemUpdate = new MenuItem();
itemUpdate.Header = "TilteUpdate".GetFromResourece();
itemUpdate.Click += delegate
{
string cmdStr = string.Empty;
try
{
List <string> columnlist = App.MainEngineer.GetColumnNameListFromTable(X_CurItemArgs.TableName);
string columnP = string.Empty;
foreach (string item in columnlist)
{
columnP += string.Format("{0}={1},", item, "COLUMN_VALUE");
}
columnP = columnP.Substring(0, columnP.Length - 1);
columnP += " where WHERE_CALUSE";
cmdStr = String.Format("UPDATE {0} SET {1}", App.MainEngineer.GetMaskedTableName(X_CurItemArgs.TableName), columnP);
}
catch (Exception ee)
{
ee.HandleMyException();
return;
}
X_CallNotifyEvent(cmdStr, false);
};
ctMenu.Items.Add(itemUpdate);
#endregion
#region Delete
MenuItem itemDelete = new MenuItem();
itemDelete.Header = "TitleDelete".GetFromResourece();
itemDelete.Click += delegate
{
string cmdStr = String.Format("Delete from {0} WHERE (COLUMN_NAME) = (COLUMN_VALUE) ",
App.MainEngineer.GetMaskedTableName(X_CurItemArgs.TableName));
X_CallNotifyEvent(cmdStr, false);
};
ctMenu.Items.Add(itemDelete);
#endregion
ctMenu.Items.Add(new Separator());
#region Insert Row
MenuItem itemInsertDataRow = new MenuItem();
itemInsertDataRow.Header = "TitleInsertARow".GetFromResourece();
itemInsertDataRow.Click += delegate { itemInsertDataRow_Click(); };
ctMenu.Items.Add(itemInsertDataRow);
#endregion
#region NewTable
ctMenu.Items.Add(new Separator());
MenuItem itemCreateTable = new MenuItem();
itemCreateTable.Header = "CreatTableDialogTitle".GetFromResourece();
itemCreateTable.Click += new RoutedEventHandler(itemCreateTable_Click);
ctMenu.Items.Add(itemCreateTable);
#endregion
#region Rename Table
MenuItem itemRenameTable = new MenuItem();
itemRenameTable.Header = "TitleRenameTable".GetFromResourece();
itemRenameTable.Click += delegate
{
string newTableName = string.Empty;
InputValueWindows iv = new InputValueWindows("Table Name");
if (iv.ShowDialog() == true)
{
newTableName = iv.X_GetInputedValue.ToString();
}
else
{
return;
}
if (string.IsNullOrEmpty(newTableName))
{
"TitleCanNotEmptyNewTableName".GetFromResourece().Warning();
return;
}
string cmdStr = App.MainEngineer.CurrentCommandTextHandler.GetRenameTableCmdStr(X_CurItemArgs.TableName, newTableName);
X_CallNotifyEvent(cmdStr, true);
};
ctMenu.Items.Add(itemRenameTable);
MenuItem copyTableItem = new MenuItem();
copyTableItem.Header = "TitleCopyTable".GetFromResourece();
copyTableItem.Click += delegate
{
try
{
string newTableName = string.Empty;
InputValueWindows iv = new InputValueWindows("Table Name");
if (iv.ShowDialog() == true)
{
newTableName = iv.X_GetInputedValue.ToString();
}
if (string.IsNullOrEmpty(newTableName))
{
("TitleCanNotEmptyNewTableName".GetFromResourece()).Warning();
return;
}
List <string> allTableList = App.MainEngineer.GetTableListInDatabase();
if (allTableList.Contains(newTableName))
{
"InfoTableNameExisted".GetFromResourece().Show();
return;
}
// oldTableName = App.MainEngineer.GetMaskedTableName(oldTableName);
// newTableName = App.MainEngineer.GetMaskedTableName(newTableName);
//If copy successful
if (CopyTable(X_CurItemArgs.TableName, newTableName))
{
"TitleCopyDataSuccessful".GetFromResourece().Show();
if (RefreshAllNodeEvent != null)
{
RefreshAllNodeEvent(null, null);
}
}
}
catch (Exception ee)
{
ee.HandleMyException();
}
};
ctMenu.Items.Add(copyTableItem);
#endregion
#region Drop Table
MenuItem itemDeleteTable = new MenuItem();
itemDeleteTable.Header = "TitleDropTable".GetFromResourece();
itemDeleteTable.Click += delegate
{
if ("ConfirmTextDropTable".GetFromResourece().Confirm())
{
string cmdStr = string.Empty;
if ("ConfirmTextDropTableWithCascade".GetFromResourece().Confirm())
{
cmdStr = App.MainEngineer.CurrentCommandTextHandler.GetDropTableCmdStrWithCascade(X_CurItemArgs.TableName);
}
else
{
cmdStr = App.MainEngineer.CurrentCommandTextHandler.GetDropTableCmdStr(X_CurItemArgs.TableName);
}
X_CallNotifyEvent(cmdStr, true);
}
};
ctMenu.Items.Add(itemDeleteTable);
#endregion
#region Modify Schema
MenuItem itemModifySchema = new MenuItem();
itemModifySchema.Header = "TitleModifyTableSchema".GetFromResourece();
itemModifySchema.Click += delegate
{
//CreateTableDetailWindow ct = new CreateTableDetailWindow(CreateTableDetailWindow.EnteringType.ModifySchema,
//X_CurItemArgs.TableName);
//ct.ShowDialog();
//X_CurItemArgs.TableName
BaseTableSchema schemaInfo = App.MainEngineer.GetTableSchemaInfoObject(X_CurItemArgs.TableName);
CreateTableSchemaBaseWin win = GetCreateTableSchemaWindow();
win.CurSchemaInfo = schemaInfo;
win.IsModifyMode = true;
//If Successful then refresh table list
if ((bool)win.ShowDialog())
{
if (RefreshAllNodeEvent != null)
{
RefreshAllNodeEvent(null, null);
}
}
};
ctMenu.Items.Add(itemModifySchema);
#endregion
ctMenu.Items.Add(new Separator());
#region GetAllColumnInfo
MenuItem itemGetColumnsInfo = new MenuItem();
itemGetColumnsInfo.Header = "TitleGetColumnInfo".GetFromResourece();
itemGetColumnsInfo.Click += delegate
{
X_CallShowResultEvent(App.MainEngineer.GetColumnInfoFromTable(X_CurItemArgs.TableName));
};
ctMenu.Items.Add(itemGetColumnsInfo);
#endregion
#region GetAllIndexes
MenuItem itemGetIndexesInfo = new MenuItem();
itemGetIndexesInfo.Header = "TitleGetIndexesInfo".GetFromResourece();
itemGetIndexesInfo.Click += delegate
{
X_CallShowResultEvent(App.MainEngineer.GetIndexInfoFromTable(X_CurItemArgs.TableName));
};
ctMenu.Items.Add(itemGetIndexesInfo);
#endregion
ctMenu.Items.Add(new Separator());
#region GetSupportType
MenuItem itemProviderInfo = new MenuItem();
itemProviderInfo.Header = "TitleGetSupportedType".GetFromResourece();
itemProviderInfo.Click += delegate
{
X_CallShowResultEvent(App.MainEngineer.GetProviderInfoFromTable(X_CurItemArgs.TableName));
};
ctMenu.Items.Add(itemProviderInfo);
#endregion
return(ctMenu);
}
