private void toolStripButton1_Click(object sender, EventArgs e) { DataRow curr_row = GreenLight.Tools.FindCurrentRow(dgTableData); int curr_id = (int)curr_row["ID"]; DataTable dt_columns = DBFunctions.ReadFromDB("SELECT ColumnDBName FROM tableconfig WHERE TableDBName = '" + table_db_names[cbTables.SelectedIndex] + "'"); string fields = ""; foreach (DataRow row in dt_columns.Rows) { fields += row["ColumnDBName"]; if (dt_columns.Rows.IndexOf(row) + 1 < dt_columns.Rows.Count) { fields += ","; } } DBFunctions.ExecuteCommand("INSERT INTO table_" + table_db_names[cbTables.SelectedIndex] + " (" + fields + ") SELECT " + fields + " FROM table_" + table_db_names[cbTables.SelectedIndex] + " WHERE ID = " + Convert.ToString(curr_id)); int col = dgTableData.CurrentCell.ColumnIndex; FillDataGrid(); dgTableData.CurrentCell = dgTableData[col, dgTableData.Rows.Count - 1]; }
static void CreateMultiRefTable(string TableDBName, string ColumnDBName) { try//Вдруг такая таблица уже есть? { DBFunctions.ExecuteCommand("CREATE TABLE `MultiRef_" + TableDBName + "_" + ColumnDBName + "` (`TableID` int(11) NOT NULL,`RefID` int(11) NOT NULL, PRIMARY KEY (`TableID`,`RefID`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8"); } catch (Exception) {} }
private void SaveData() { if (!Auth.AuthModule.rights.string_replace.write) { return; } dgReplaceStrings.EndEdit(); Validate(); dt_replaces.AcceptChanges(); Dictionary <string, object> parameters = new Dictionary <string, object>(); parameters.Add("tkfv", key_field_values[current_key_field_index]); parameters.Add("trdn", reference_db_names[current_ref_db_names_index]); DBFunctions.ExecuteCommand("DELETE FROM replace_strings WHERE table_key_field_value = @tkfv AND table_reference_db_name = @trdn", parameters); foreach (DataRow replace_row in dt_replaces.Rows) { parameters = new Dictionary <string, object>(); parameters.Add("tkfv", key_field_values[current_key_field_index]); parameters.Add("trdn", reference_db_names[current_ref_db_names_index]); parameters.Add("refid", replace_row["table_reference_id"]); parameters.Add("refvalue", replace_row["table_reference_value"]); parameters.Add("replace_string", replace_row["replace_string"]); DBFunctions.ExecuteCommand("INSERT INTO replace_strings VALUES(@tkfv,@trdn,@refid,@refvalue,@replace_string)", parameters); } }
static void DropMultiRefTable(string TableDBName, string ColumnDBName) { try { DBFunctions.ExecuteCommand("DROP TABLE `MultiRef_" + TableDBName + "_" + ColumnDBName + "`"); } catch (Exception) { } }
void SaveElement() { if (tvReference.SelectedNode == null) { return; } foreach (DataColumn dc in dt_elem.Columns) { try { if (dc.DataType.Name == "String") { dt_elem.Rows[0][dc] = edit_controls[dc.ColumnName].Text; } else if (dc.DataType.Name == "Int32") { dt_elem.Rows[0][dc] = Convert.ToInt32(edit_controls[dc.ColumnName].Text); } else if (dc.DataType.Name == "Single") { dt_elem.Rows[0][dc] = Convert.ToSingle(edit_controls[dc.ColumnName].Text); } } catch (Exception) { }; } DBFunctions.WriteToDB(dt_elem, ref_table_struct); if (tvReference.SelectedNode.Tag == null) { tvReference.SelectedNode.Tag = Convert.ToInt32(DBFunctions.ReadScalarFromDB("SELECT LAST_INSERT_ID()")); //Добавим этот элемент во все мультирефы, в которых выбран его родитель Dictionary <string, object> parameters = new Dictionary <string, object>(); parameters.Add("ref_db_name", reference_db_name); DataTable dtMultirefFields = DBFunctions.ReadFromDB("SELECT tabledbname,columndbname FROM tableconfig WHERE referencemultiselect=1 and columnreference=@ref_db_name", parameters); foreach (DataRow multirefFieldRow in dtMultirefFields.Rows) { parameters.Clear(); parameters.Add("id_string", "{" + Convert.ToString(tvReference.SelectedNode.Tag) + "}"); parameters.Add("id", dt_elem.Rows[0]["ParentID"]); string query = @"UPDATE table_" + ((string)multirefFieldRow["tabledbname"]) + @" SET " + ((string)multirefFieldRow["columndbname"]) + @" = CONCAT(" + ((string)multirefFieldRow["columndbname"]) + @",@id_string) WHERE POSITION(CONCAT('{',(SELECT parentid FROM ref_data_" + reference_db_name + @" WHERE id=@id),'}') IN " + ((string)multirefFieldRow["columndbname"]) + ") > 0"; DBFunctions.ExecuteCommand(query, parameters); } } tvReference.SelectedNode.Text = (string)dt_elem.Rows[0]["RefName"]; new_node = null; }
private void tsbInvertUseInWhereClause_Click(object sender, EventArgs e) { DataRow curr_row = GreenLight.Tools.FindCurrentRow(dgColumnNames); if (curr_row != null) { curr_row["UseInWhereClause"] = !((bool)curr_row["UseInWhereClause"]); Dictionary <string, object> parameters = new Dictionary <string, object>(); parameters.Add("id", (int)curr_row["TableConfigID"]); parameters.Add("use_in_clause", (bool)curr_row["UseInWhereClause"]); DBFunctions.ExecuteCommand("UPDATE tableconfig SET UseInWhereClause = @use_in_clause WHERE TableConfigID = @id", parameters); } }
private void tvReference_DragDrop(object sender, DragEventArgs e) { if (e.Effect == DragDropEffects.Move) { TreeNode hoveringNode = GetHoveringNode(e.X, e.Y); if (hoveringNode != null) { TreeNode draggingNode = e.Data.GetData(typeof(TreeNode)) as TreeNode; if (draggingNode != null) { /*if (draggingNode.Level == hoveringNode.Level) * { * if (draggingNode.Level != 0) * { * draggingNode.Remove(); * hoveringNode.Nodes.Insert(hoveringNode.Index + 1, draggingNode); * } * if (draggingNode.Level == 0) * { * * } * * * }*/ if (!ContainsNode(draggingNode, hoveringNode)) { draggingNode.Remove(); hoveringNode.Nodes.Insert(0, draggingNode); DBFunctions.ExecuteCommand("UPDATE ref_data_" + reference_db_name + " SET ParentID = " + Convert.ToString(hoveringNode.Tag) + " WHERE ID = " + Convert.ToString(draggingNode.Tag) + ";"); } } } else { TreeNode draggingNode = e.Data.GetData(typeof(TreeNode)) as TreeNode; if (draggingNode != null) { draggingNode.Remove(); tvReference.Nodes.Add(draggingNode); DBFunctions.ExecuteCommand("UPDATE ref_data_" + reference_db_name + " SET ParentID ='0' WHERE ID = " + Convert.ToString(draggingNode.Tag) + ";"); } } } }
private void tbAddReference_Click(object sender, EventArgs e) { string ref_name = Microsoft.VisualBasic.Interaction.InputBox("Введите наименование справочника", "Ввод наименования"); if (ref_name == "") { return; } string ref_db_name = Microsoft.VisualBasic.Interaction.InputBox("Введите название справочника для базы данных", "Ввод наименования"); if (ref_db_name == "") { return; } ref_db_name = ref_db_name.ToLower(); SaveData(); DBFunctions.ExecuteCommand("INSERT INTO referencesconfig SET ReferenceName = '" + ref_name + "',ReferenceDBName = '" + ref_db_name + "',Hierarchycal = 1,ColumnName = 'Наименование',ColumnDBName = 'RefName',ColumnType = 'Строка50'"); DBStructure.UpdateDBStructure(); reference_list = DBFunctions.ReadFromDB("SELECT DISTINCT ReferenceName,ReferenceDBName,Hierarchycal FROM referencesconfig"); cbCurrentReference.Items.Clear(); foreach (DataRow row in reference_list.Rows) { cbCurrentReference.Items.Add((string)row["ReferenceName"]); } foreach (string Item in cbCurrentReference.Items) { if (Item == ref_name) { cbCurrentReference.SelectedIndex = cbCurrentReference.Items.IndexOf(Item); break; } } }
public static void ReorganizeMultiref() { DataTable dt_multirefcolumns = DBFunctions.ReadFromDB("SELECT TableDBName,ColumnDBName FROM tableconfig WHERE ReferenceMultiSelect = 1"); DBFunctions.ExecuteCommand("SET group_concat_max_len = 32000"); foreach (DataRow row_multirefcolumn in dt_multirefcolumns.Rows) { DataTable dt_values = DBFunctions.ReadFromDB( @"SELECT TableID, GROUP_CONCAT(CONCAT('{',CONVERT(RefID, CHAR),'}') SEPARATOR '') AS RefID FROM multiref_" + row_multirefcolumn["TableDBName"] + "_" + row_multirefcolumn["ColumnDBName"] + @" GROUP BY TableID"); foreach (DataRow row_value in dt_values.Rows) { DBFunctions.ExecuteCommand(@"UPDATE table_" + row_multirefcolumn["TableDBName"] + @" SET " + row_multirefcolumn["ColumnDBName"] + " = '" + row_value["RefID"] + @"' WHERE id = " + Convert.ToString(row_value["TableID"])); } } }
private void tbDeleteReference_Click(object sender, EventArgs e) { if (System.Windows.Forms.MessageBox.Show("Данные справочника будут безвозвратно удалены. Продолжить?", "Вопрос", System.Windows.Forms.MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No) { return; } string ref_db_name = (string)reference_list.Rows[cbCurrentReference.SelectedIndex]["ReferenceDBName"]; DBFunctions.ExecuteCommand("DELETE FROM referencesconfig WHERE ReferenceDBName='" + ref_db_name + "';" + "DROP TABLE `ref_data_" + ref_db_name + "`;" + "DROP TABLE `ref_hierarchy_" + ref_db_name + "`;"); cbCurrentReference.Items.Clear(); reference_list = DBFunctions.ReadFromDB("SELECT DISTINCT ReferenceName,ReferenceDBName,Hierarchycal FROM referencesconfig"); foreach (DataRow row in reference_list.Rows) { cbCurrentReference.Items.Add((string)row["ReferenceName"]); } cbCurrentReference.SelectedIndex = 0; }
private void SaveData() { if (!Auth.AuthModule.rights.questionary_editor.write) { return; } foreach (DataRow filter_row in dt_filter.Rows) { for (int col_num = 2; col_num < dt_filter.Columns.Count; col_num++) { //Очистим текущие данные DBFunctions.ExecuteCommand("DELETE FROM questionary_filter WHERE questionary_id = " + Convert.ToString(questionary_id) + " AND question_id = " + filter_row["ID вопроса"] + " AND filter_id = " + dt_filter.Columns[col_num].ColumnName); //Занесем новые при необходимости if ((bool)filter_row[col_num] == true) { DBFunctions.ExecuteCommand("INSERT INTO questionary_filter VALUES(" + Convert.ToString(questionary_id) + "," + dt_filter.Columns[col_num].ColumnName + "," + filter_row["ID вопроса"] + ")"); } } } dt_filter.AcceptChanges(); }
static void UpdateMainTableStructure(string TableDBName) { //Получим требуемую структуру DataTable neededStructure = DBFunctions.ReadFromDB("SELECT TableConfigID,ColumnDBName,ColumnDBName_Old,ColumnType,ReferenceMultiSelect FROM tableconfig WHERE TableDBName = '" + TableDBName + "'"); //Получим текущую структуру DataTable CurrentStrurture = new DataTable(); bool new_table = false; try { CurrentStrurture = DBFunctions.ReadFromDB("SHOW COLUMNS FROM table_" + TableDBName); } catch (Exception) { //Таблицы не существует, будем создавать new_table = true; } if (new_table) { string CommandText = "CREATE TABLE `table_" + TableDBName + "` ("; //ПК CommandText += "`ID` int(11) NOT NULL AUTO_INCREMENT"; foreach (DataRow row in neededStructure.Rows) { if ((string)row["ColumnType"] == "Справочник" && (bool)row["ReferenceMultiSelect"] == true) { CommandText += ",`" + row["ColumnDBName"] + "` MEDIUMTEXT DEFAULT NULL"; //CreateMultiRefTable(TableDBName, (string)row["ColumnDBName"]); } { string field_type = ConvertTypeToSQL((string)row["ColumnType"]); if (field_type != null) { CommandText += ",`" + row["ColumnDBName"] + "` " + field_type + " DEFAULT NULL"; } } } CommandText += ", PRIMARY KEY (`ID`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8"; DBFunctions.ExecuteCommand(CommandText); } else { //Создаем и меняем строки foreach (DataRow row in neededStructure.Rows) { string col_type; if ((string)row["ColumnType"] == "Справочник" && (bool)row["ReferenceMultiSelect"] == true) { //CreateMultiRefTable(TableDBName, (string)row["ColumnDBName"]); col_type = "MEDIUMTEXT"; } else { col_type = ConvertTypeToSQL((string)row["ColumnType"]); } { //Ищем колонку DataRow[] foundRows = CurrentStrurture.Select("Field = '" + row["ColumnDBName_Old"] + "'"); if (foundRows.Length == 0) { //Добавляем колонку DBFunctions.ExecuteCommand("ALTER TABLE table_" + TableDBName + " ADD `" + row["ColumnDBName"] + "` " + col_type); } else { //Проверяем соответствие имени if ((string)foundRows[0]["Field"] != (string)row["ColumnDBName"]) { //Переименовываем колонку try { DBFunctions.ExecuteCommand("ALTER TABLE `table_" + TableDBName + "` CHANGE `" + row["ColumnDBName_Old"] + "` `" + row["ColumnDBName"] + "` " + col_type); row["ColumnDBName_Old"] = row["ColumnDBName"]; } catch (Exception) { System.Windows.Forms.MessageBox.Show("Невозможно изменить тип столбца.", "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); } } else if ((string)foundRows[0]["Type"] != col_type) { //Меняем тип try { DBFunctions.ExecuteCommand("ALTER TABLE `table_" + TableDBName + "` MODIFY `" + row["ColumnDBName"] + "` " + col_type); } catch (Exception) { System.Windows.Forms.MessageBox.Show("Невозможно изменить тип столбца.", "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); } } } } } //Внесем изменения конфигурационную таблицу if (neededStructure.GetChanges() != null) { TableStruct ts = new TableStruct(); ts.TableName = "tableconfig"; string[] p_keys = { "TableConfigID" }; ts.p_keys = p_keys; string[] columns = { "ColumnDBName_Old" }; ts.columns = columns; DBFunctions.WriteToDB(neededStructure, ts); } //Удалим лишние колонки foreach (DataRow row in CurrentStrurture.Rows) { if ((string)row["Field"] == "ID") { continue; } DataRow[] foundRows = neededStructure.Select("ColumnDBName = '" + row["Field"] + "'"); if (foundRows.Length == 0) { //Удаляем колонку DBFunctions.ExecuteCommand("ALTER TABLE table_" + TableDBName + " DROP `" + row["Field"] + "`"); } } //Удалим лишние таблицы с множественным выбором //DataTable multi_ref_tables = DBFunctions.ReadFromDB("SHOW TABLES WHERE tables_in_" + DBFunctions.db_name + " LIKE 'MultiRef_" + TableDBName + "_%'"); //foreach(DataRow row in multi_ref_tables.Rows) //{ // DataRow[] foundRows = neededStructure.Select("'MultiRef_" + TableDBName + "_' + ColumnDBName = '" + row["tables_in_" + DBFunctions.db_name] + "' AND ReferenceMultiSelect = 1"); /* if (foundRows.Length == 0) * { * //Удаляем таблицу * DBFunctions.ExecuteCommand("DROP TABLE `" + row["tables_in_" + DBFunctions.db_name] + "`"); * * } * }*/ } }
public static void UpdateSelectorScript() { //Получим таблицу с формулами //Список формульных полей DataTable dt_formula_fields = DBFunctions.ReadFromDB("SELECT concat('table_',TableDBName,'.',ColumnDBName) AS field FROM tableconfig WHERE ColumnType = 'Формула'"); string formula_query_text = "SELECT ID"; foreach (DataRow row in dt_formula_fields.Rows) { formula_query_text += "," + Convert.ToString(row["field"]) + " AS '" + Convert.ToString(row["field"]) + "'"; } formula_query_text += " FROM table_credprogr"; DataTable dt_formulas = DBFunctions.ReadFromDB(formula_query_text); //Получим текст условия string clause_text; DataTable dt_clause = DBFunctions.ReadFromDB("SELECT * FROM where_clauses"); if (dt_clause.Rows.Count > 0) { clause_text = (string)dt_clause.Rows[0]["Clause"]; } else { return; } //Заменим макросы в условиях DataTable dtMacros = DBFunctions.ReadFromDB("SELECT * FROM macros"); bool macros_found = true; while (macros_found) { macros_found = false; foreach (DataRow macro in dtMacros.Rows) { if (clause_text.IndexOf("[" + (string)macro["name"] + "]") != -1) { macros_found = true; clause_text = clause_text.Replace("[" + (string)macro["name"] + "]", (string)macro["macro"]); } } } //Соберем текст запроса string query_text = "SELECT table_credprogr.ID"; DataTable all_fields = DBFunctions.ReadFromDB("SELECT concat('table_',TableDBName,'.',ColumnDBName) AS field_name,ColumnName,ColumnType,ReferenceMultiSelect,ShowInOffer,TableDBName,ColumnDBName FROM tableconfig WHERE ShowInOffer = 1 OR UseInWhereClause = 1 OR ShowInOfferShort = 1"); if (all_fields.Rows.Count == 0) { System.Windows.Forms.MessageBox.Show("Не выбраны поля для отображения", "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); return; } foreach (DataRow row in all_fields.Rows) { query_text += ","; if ((string)row["ColumnType"] == "Формула") { query_text += "CASE table_credprogr.ID "; foreach (DataRow formula_row in dt_formulas.Rows) { query_text += " WHEN " + formula_row["ID"] + " THEN " + formula_row[(string)row["field_name"]]; } query_text += " END AS '" + row["ColumnDBName"] + "'"; } else { query_text += Convert.ToString(row["field_name"]) + " AS '" + row["ColumnDBName"] + "'"; } } query_text += " FROM table_credprogr LEFT JOIN table_clients ON table_clients.id=" + "[ClientID] "; //Здесь формируем запрос по полям с ShowInOffer = 1 DataTable fields_to_show = DBFunctions.ReadFromDB("SELECT ColumnDBName,ColumnName,ColumnType,ColumnReference,ReferenceMultiSelect,ShowFullName FROM tableconfig WHERE ShowInOffer = 1 ORDER BY WebOrder"); string itog_query = "SELECT DISTINCT inner_select.ID"; string join_text = ""; foreach (DataRow row in fields_to_show.Rows) { itog_query += ","; if ((string)row["ColumnType"] == "Справочник" && (bool)row["ReferenceMultiSelect"] == false) { itog_query += "ref_name_" + Convert.ToString(row["ColumnReference"]) + "(" + row["ColumnDBName"] + "," + Convert.ToString(row["ShowFullName"]) + ") AS '" + row["ColumnName"] + "'"; } else if ((string)row["ColumnType"] == "Справочник" && (bool)row["ReferenceMultiSelect"] == true) { itog_query += "multiref_names_" + Convert.ToString(row["ColumnReference"]) + "(" + row["ColumnDBName"] + ")" + " AS '" + row["ColumnName"] + "'"; } else { itog_query += row["ColumnDBName"] + " AS '" + row["ColumnName"] + "'"; } } itog_query += " FROM (" + query_text + ") AS inner_select " + join_text + " WHERE "; itog_query += clause_text.Replace("@", "");; //Занесем результат в базу //Удалим текущий скрипт DBFunctions.ExecuteCommand("DELETE FROM scripts WHERE script_name = 'OfferSelect'"); DBFunctions.ExecuteCommand("INSERT INTO scripts VALUES('OfferSelect','" + itog_query.Replace("'", "\\'") + "')"); //Здесь формируем запрос по полям с ShowInOfferShort = 1 fields_to_show = DBFunctions.ReadFromDB("SELECT ColumnDBName,ColumnName,ColumnType,ColumnReference,ReferenceMultiSelect,ShowFullName FROM tableconfig WHERE ShowInOfferShort = 1 ORDER BY WebOrder"); itog_query = "SELECT DISTINCT inner_select.ID"; foreach (DataRow row in fields_to_show.Rows) { itog_query += ","; if ((string)row["ColumnType"] == "Справочник" && (bool)row["ReferenceMultiSelect"] == false) { itog_query += "ref_name_" + Convert.ToString(row["ColumnReference"]) + "(" + row["ColumnDBName"] + "," + Convert.ToString(row["ShowFullName"]) + ") AS '" + row["ColumnName"] + "'"; } else if ((string)row["ColumnType"] == "Справочник" && (bool)row["ReferenceMultiSelect"] == true) { itog_query += "multiref_names_" + Convert.ToString(row["ColumnReference"]) + "(" + row["ColumnDBName"] + ")" + " AS '" + row["ColumnName"] + "'"; } else { itog_query += row["ColumnDBName"] + " AS '" + row["ColumnName"] + "'"; } } itog_query += " FROM (" + query_text + ") AS inner_select WHERE "; itog_query += clause_text.Replace("@", ""); //Занесем результат в базу //Удалим текущий скрипт DBFunctions.ExecuteCommand("DELETE FROM scripts WHERE script_name = 'OfferSelectShort'"); DBFunctions.ExecuteCommand("INSERT INTO scripts VALUES('OfferSelectShort','" + itog_query.Replace("'", "\\'") + "')"); }
static void UpdateReferenceStructure(string ref_db_name) { //Получим требуемую структуру DataTable neededStructure = DBFunctions.ReadFromDB("SELECT * FROM referencesconfig WHERE ReferenceDBName = '" + ref_db_name + "'"); if (neededStructure.Rows.Count == 0) { System.Windows.Forms.MessageBox.Show("Неверное наименование справочника.", "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); return; } bool hierarchical = Convert.ToBoolean(neededStructure.Rows[0]["Hierarchycal"]); //Проверим есть ли такая таблица в БД bool ref_data_exists = Convert.ToBoolean(DBFunctions.ReadFromDB("Show tables like 'ref_data_" + ref_db_name + "'").Rows.Count); bool ref_hierarchy_exists = Convert.ToBoolean(DBFunctions.ReadFromDB("Show tables like 'ref_hierarchy_" + ref_db_name + "'").Rows.Count); if ((hierarchical && ref_data_exists && ref_hierarchy_exists) || //Иерархический, есть обе таблицы (!hierarchical && ref_data_exists && !ref_hierarchy_exists) //Не иерархический, есть одна таблица ) { //Всё ок, проверим соответствие состава колонок DataTable CurrentStrurture = DBFunctions.ReadFromDB("SHOW COLUMNS FROM ref_data_" + ref_db_name); //Создаем и меняем строки foreach (DataRow row in neededStructure.Rows) { //Ищем колонку DataRow[] foundRows = CurrentStrurture.Select("Field = '" + row["ColumnDBName_Old"] + "'"); if (foundRows.Length == 0) { //Добавляем колонку DBFunctions.ExecuteCommand("ALTER TABLE ref_data_" + ref_db_name + " ADD `" + row["ColumnDBName"] + "` " + ConvertTypeToSQL((string)row["ColumnType"])); } else { //Проверяем соответствие имени if ((string)foundRows[0]["Field"] != (string)row["ColumnDBName"]) { //Переименовываем колонку try { DBFunctions.ExecuteCommand("ALTER TABLE `ref_data_" + ref_db_name + "` CHANGE `" + row["ColumnDBName_Old"] + "` `" + row["ColumnDBName"] + "` " + ConvertTypeToSQL((string)row["ColumnType"])); row["ColumnDBName_Old"] = row["ColumnDBName"]; } catch (Exception) { System.Windows.Forms.MessageBox.Show("Невозможно изменить тип столбца.", "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); } } else if ((string)foundRows[0]["Type"] != ConvertTypeToSQL((string)row["ColumnType"])) { //Меняем тип try { DBFunctions.ExecuteCommand("ALTER TABLE `ref_data_" + ref_db_name + "` MODIFY `" + row["ColumnDBName"] + "` " + ConvertTypeToSQL((string)row["ColumnType"])); } catch (Exception) { System.Windows.Forms.MessageBox.Show("Невозможно изменить тип столбца.", "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); } } } } //Внесем изменения конфигурационную таблицу if (neededStructure.GetChanges() != null) { TableStruct ts = new TableStruct(); ts.TableName = "referencesconfig"; string[] p_keys = { "ReferenceConfigID" }; ts.p_keys = p_keys; string[] columns = { "ColumnDBName_Old" }; ts.columns = columns; DBFunctions.WriteToDB(neededStructure, ts); } //Удалим лишние колонки foreach (DataRow row in CurrentStrurture.Rows) { if ((string)row["Field"] == "id" || (string)row["Field"] == "ID") { continue; } if ((string)row["Field"] == "parentid" || (string)row["Field"] == "ParentID") { continue; } DataRow[] foundRows = neededStructure.Select("ColumnDBName = '" + row["Field"] + "'"); if (foundRows.Length == 0) { //Удаляем колонку DBFunctions.ExecuteCommand("ALTER TABLE ref_data_" + ref_db_name + " DROP `" + row["Field"] + "`"); } } //Создадим хранимые процедуры и триггеры string ref_create_script = (string)DBFunctions.ReadScalarFromDB("SELECT script FROM scripts WHERE script_name = 'Reference_Create'"); ref_create_script = ref_create_script.Replace("[RefDBName]", ref_db_name); DBFunctions.ExecuteScript(ref_create_script); return; } if (ref_data_exists || ref_hierarchy_exists) { if (System.Windows.Forms.MessageBox.Show("Сменился тип справочника " + ref_db_name + " данные справочника будут удалены. Продолжить?", "Вопрос", System.Windows.Forms.MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No) { return; } } //Удалим таблицы if (ref_data_exists) { DBFunctions.ExecuteCommand("DROP TABLE `ref_data_" + ref_db_name + "`"); } if (ref_hierarchy_exists) { DBFunctions.ExecuteCommand("DROP TABLE `ref_hierarchy_" + ref_db_name + "`"); } //Создадим новые таблицы //Таблица с данными string CommandText = "CREATE TABLE `ref_data_" + ref_db_name + "` ("; CommandText += "`ID` int(11) NOT NULL AUTO_INCREMENT"; CommandText += ",`ParentID` int(11) DEFAULT 0"; foreach (DataRow row in neededStructure.Rows) { if (row["ColumnType"] == System.DBNull.Value) { continue; } string field_type = ConvertTypeToSQL((string)row["ColumnType"]); if (field_type != null) { CommandText += ",`" + row["ColumnDBName"] + "` " + field_type + " DEFAULT NULL"; } } CommandText += ", PRIMARY KEY (`ID`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8"; DBFunctions.ExecuteCommand(CommandText); //Таблица с иерархией if (hierarchical) { CommandText = "CREATE TABLE `ref_hierarchy_" + ref_db_name + "` ("; CommandText += "`ElemID` int(11) NOT NULL"; CommandText += ",`ParentID` int(11) NOT NULL"; CommandText += ",`Level` int(11) NOT NULL"; CommandText += ", PRIMARY KEY (`ElemID`,`ParentID`,`Level`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8"; DBFunctions.ExecuteCommand(CommandText); } //Создадим хранимые процедуры и триггеры string ref_create_script_inner = (string)DBFunctions.ReadScalarFromDB("SELECT script FROM scripts WHERE script_name = 'Reference_Create'"); ref_create_script_inner = ref_create_script_inner.Replace("[RefDBName]", ref_db_name); DBFunctions.ExecuteScript(ref_create_script_inner); }
private void tsbCopy_Click(object sender, EventArgs e) { dgColumns.EndEdit(); dgTableData.EndEdit(); string query_text = "UPDATE "; query_text += "table_" + table_db_names[cbTables.SelectedIndex] + " AS dest, "; query_text += "table_" + table_db_names[cbTables.SelectedIndex] + " AS src SET "; bool first = true; foreach (DataRow column_row in dt_columns.Rows) { if (Convert.ToBoolean(column_row["Checked"])) { if (first) { first = false; } else { query_text += " , "; } query_text += "dest.`" + column_row["ColumnDBName"] + "` = src.`" + column_row["ColumnDBName"] + "`"; } } int source_id = -1; foreach (DataRow row in dt_table.Rows) { if (Convert.ToBoolean(row["Source"])) { if (source_id == -1) { source_id = (int)row["ID"]; } else { System.Windows.Forms.MessageBox.Show("Выбрано больше одного источника", "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); return; } } } if (source_id == -1) { System.Windows.Forms.MessageBox.Show("Не выбрано ни одного источника", "Ошибка", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); return; } query_text += " WHERE dest.id = [d_id] AND src.id = " + Convert.ToString(source_id); foreach (DataRow row in dt_table.Rows) { if (Convert.ToBoolean(row["Destination"])) { DBFunctions.ExecuteCommand(query_text.Replace("[d_id]", Convert.ToString(row["ID"]))); } } System.Windows.Forms.MessageBox.Show("Копирование завершено.", "Успешное завершение", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information); }