private void tsbDown_Click(object sender, EventArgs e)
{
DataRow curr_row = GreenLight.Tools.FindCurrentRow(dgTableConfig);
int curr_id = (int)curr_row["TableConfigID"];
//Получим следующий номер
object next_id_obj = DBFunctions.ReadScalarFromDB("SELECT TableConfigID From tableconfig WHERE TableConfigID > " + Convert.ToString(curr_row["TableConfigID"]) +
" AND TableDBName = '" + curr_row["TableDBName"] + "' ORDER BY TableConfigID LIMIT 1");
int next_id;
if (next_id_obj != null)
{
next_id = (int)next_id_obj;
}
else
{
return;
}
DBFunctions.ExecuteScript("CALL SwapLinesInTableConfig(" + Convert.ToString(curr_id) + "," + Convert.ToString(next_id) + ")");
int rw = dgTableConfig.CurrentCell.RowIndex;
int col = dgTableConfig.CurrentCell.ColumnIndex;
FillDataGrid();
dgTableConfig.CurrentCell = dgTableConfig[col, rw + 1];
}
void DeleteElement()
{
if (tvReference.SelectedNode == null)
{
return;
}
DBFunctions.ExecuteScript("CALL sp_deletetree_" + reference_db_name + "(" + Convert.ToString(tvReference.SelectedNode.Tag) + ")");
tvReference.SelectedNode.Remove();
}
private void UpdateSessionInfo()
{
DBFunctions.ExecuteScript("DELETE FROM active_sessions WHERE SessionID = " + Convert.ToString(Session_ID));
DateTime curr_datetime = (DateTime)DBFunctions.ReadScalarFromDB("SELECT current_timestamp()");
DBFunctions.ExecuteScript("INSERT INTO active_sessions SET SessionID = " + Convert.ToString(Session_ID) + ","
+ "Computer_Name='" + Environment.MachineName + "',"
+ "DomainUser='******',"
+ "LastActivity='" + curr_datetime.ToString("s") + "',"
+ "SessionStart='" + session_start.ToString("s") + "'");
}
public static void RecreateRefStatements()
{
DataTable References = DBFunctions.ReadFromDB("SELECT DISTINCT ReferenceDBName FROM referencesconfig");
foreach (DataRow row in References.Rows)
{
//Создадим хранимые процедуры и триггеры
string ref_create_script = (string)DBFunctions.ReadScalarFromDB("SELECT script FROM scripts WHERE script_name = 'Reference_Create'");
ref_create_script = ref_create_script.Replace("[RefDBName]", (string)row["ReferenceDBName"]);
DBFunctions.ExecuteScript(ref_create_script);
}
}
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);
}
