/// <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); }