public void ReloadControls(params String[] formNameList) { sqlCon.DoQuery("DELETE * FROM user_rol_access"); sqlCon.DoQuery("DELETE * FROM spr_user_controls"); DataTable dt = sqlCon.RefreshDataTable("spr_user_controls"); for (int i = 0; i < formNameList.Length; i++) { InsertControlsBySingle(formNameList[i]); } sqlCon.UpdateDataTable("spr_user_controls"); }
private void bnNext_Click(object sender, EventArgs e) { tabControlChanging = true; if (tabControl1.SelectedTab == tabPage1) { if (((DataTable)dgRegion.DataSource).Rows.Count == 0) { return; } int RowIndex = 0; RowIndex = dgRegion.CurrentRow.Index; regionIndex = RowIndex; region = dgRegion.Rows[RowIndex].Cells["id"].Value.ToString(); tabControl1.SelectedTab = tabPage2; } else if (tabControl1.SelectedTab == tabPage2) { if (((DataTable)dgDepartment.DataSource).Rows.Count == 0) { return; } int RowIndex = 0; RowIndex = dgDepartment.CurrentRow.Index; string department = dgDepartment.Rows[RowIndex].Cells["id"].Value.ToString(); String stringSQL = ""; try { stringSQL = (departmentID != null) ? "UPDATE system SET system_value = '" + department + "' WHERE (name = 'Department')" : "INSERT INTO system (name, system_value) values ('Department', '" + department + "')"; sqlCon.DoQuery(stringSQL); stringSQL = (departmentID != null) ? "UPDATE employee SET department_id = '" + department + "' WHERE (department_id = '" + departmentID + "')" : "UPDATE employee SET department_id = '" + department + "'"; sqlCon.DoQuery(stringSQL); stringSQL = (departmentID != null) ? "UPDATE spr_party_number SET department_id = '" + department + "' WHERE (department_id = '" + departmentID + "')" : "UPDATE spr_party_number SET department_id = '" + department + "'"; sqlCon.DoQuery(stringSQL); } catch (Exception er) { MessageBox.Show(er.Message, "Ошибка при обновлении справочников"); } Close(); } }
public EditUserRolForm() { InitializeComponent(); notSaved = false; sqlCon = new SQLDBConnect(); newDBcon = new SprDbConnect("spr_user_rol"); dgUserRol.DataSource = newDBcon.GetDataTable("spr_user_rol"); dgUserRol.Columns[0].HeaderText = "Код"; dgUserRol.Columns[0].Visible = false; dgUserRol.Columns[0].DataPropertyName = "id"; dgUserRol.Columns[1].HeaderText = "Наименование"; dgControls.AutoGenerateColumns = false; DataGridViewTextBoxColumn column = new DataGridViewTextBoxColumn(); /* * column.HeaderText = "Код"; * column.Visible = false; * column.DataPropertyName = "id"; * column.Name = "id"; * dgControls.Columns.Add(column); */ column = new DataGridViewTextBoxColumn(); column.HeaderText = "Наименование"; column.Visible = true; column.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; column.DataPropertyName = "opisanie"; dgControls.Columns.Add(column); DataGridViewCheckBoxColumn columnCheck = new DataGridViewCheckBoxColumn(); { columnCheck.Name = "enabled"; columnCheck.DataPropertyName = "enabled"; columnCheck.HeaderText = "Разрешено"; columnCheck.Width = 80; columnCheck.AutoSizeMode = DataGridViewAutoSizeColumnMode.None; } dgControls.Columns.Add(columnCheck); //Заполняем таблицу user_rol_access на случай появления новых контролов или групп пользователей sqlCon.DoQuery(@"INSERT INTO user_rol_access SELECT spr_user_controls.id AS id_user_controls, spr_user_rol.id AS id_user_rol FROM spr_user_controls, spr_user_rol WHERE spr_user_controls.id NOT IN (SELECT DISTINCT id_user_controls FROM user_rol_access) OR spr_user_rol.id NOT IN (SELECT DISTINCT id_user_rol FROM user_rol_access)"); DataTable dt = sqlCon.GetDataTable("user_access", @"SELECT user_rol_access.id, user_rol_access.id_user_rol, user_rol_access.enabled, spr_user_controls.opisanie FROM user_rol_access LEFT JOIN spr_user_controls ON (user_rol_access.id_user_controls = spr_user_controls.id)"); DataView dv = new DataView(dt); dgControls.DataSource = dv; }
private void Updating_1_0() { try { conSql.DoQuery("ALTER TABLE spec_psycho ADD date_doc DATETIME, name VARCHAR(50)"); conSql.DoQuery("ALTER TABLE penalty ALTER COLUMN reason varchar(255)"); conSql.DoQuery("ALTER TABLE penalty ALTER COLUMN removal varchar(255)"); conSql.DoQuery("DROP TABLE system"); conSql.DoQuery("CREATE TABLE system (id COUNTER not null primary key, name VARCHAR(50), system_value VARCHAR(50))"); conSql.DoQuery("INSERT INTO system (id, name, system_value) values (1, 'Database Version', '1.1')"); conSql.DoQuery("UPDATE system SET system_value = '1.1' WHERE (name = 'Database Version')"); } catch (Exception e) { MessageBox.Show(e.Message, "ќшибка при обновлении 1.0"); return; } }
//Процедура выгрузки public void UploadData(String strSQL, Boolean moveToArh) { String stringSQL = null; String stringSQL1 = null; String stringSQL2 = null; //spec stringSQL = "SELECT * FROM spec WHERE id IN (" + strSQL + ")"; DataTable dt = sqlConSrc.GetDataTable("spec", stringSQL); stringSQL1 = "SELECT DISTINCT edu_id FROM spec WHERE id IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_edu WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_edu", stringSQL2); stringSQL1 = "SELECT DISTINCT nation_id FROM spec WHERE id IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_nation WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_nation", stringSQL2); stringSQL1 = "SELECT DISTINCT mstatus_id FROM spec WHERE id IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_mstatus WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_mstatus", stringSQL2); stringSQL1 = "SELECT DISTINCT mstatus_id FROM spec WHERE id IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_mstatus WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_mstatus", stringSQL2); stringSQL1 = "SELECT DISTINCT profession_id FROM spec WHERE id IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_profession WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_profession", stringSQL2); //party_number //stringSQL1 = "SELECT DISTINCT party_id FROM spec WHERE id IN (" + strSQL + ")"; //stringSQL2 = "SELECT * FROM spr_party_number WHERE id IN (" + stringSQL1 + ")"; stringSQL2 = "SELECT * FROM spr_party_number"; sqlConSrc.GetDataTable("spr_party_number", stringSQL2); //relations stringSQL = "SELECT * FROM relations WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("relations", stringSQL); stringSQL1 = "SELECT DISTINCT degree_id FROM relations WHERE id_spec IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_degree WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_degree", stringSQL2); //bonus stringSQL = "SELECT * FROM bonus WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("bonus", stringSQL); stringSQL1 = "SELECT DISTINCT bonus_type_id FROM bonus WHERE id_spec IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_bonus_type WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_bonus_type", stringSQL2); stringSQL1 = "SELECT DISTINCT performer_id FROM bonus WHERE id_spec IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_performers WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_performers", stringSQL2); //penalty stringSQL = "SELECT * FROM penalty WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("penalty", stringSQL); stringSQL1 = "SELECT DISTINCT penalty_type_id FROM penalty WHERE id_spec IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_penalty_type WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_penalty_type", stringSQL2); //party stringSQL = "SELECT * FROM party WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("party", stringSQL); /* * stringSQL1 = "SELECT DISTINCT party_number_id FROM party WHERE id IN (" + strSQL + ")"; * stringSQL2 = "SELECT * FROM spr_party_number WHERE id IN (" + stringSQL1 + ")"; * sqlConSrc.GetDataTable("spr_party_number2", stringSQL2); */ //spec_psycho stringSQL = "SELECT * FROM spec_psycho WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("spec_psycho", stringSQL); //psycho_char stringSQL = "SELECT * FROM psycho_char WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("psycho_char", stringSQL); //prev_conv stringSQL = "SELECT * FROM prev_conv WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("prev_conv", stringSQL); stringSQL1 = "SELECT DISTINCT release_reason_id FROM prev_conv WHERE id_spec IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_release_reason WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_release_reason", stringSQL2); //profilact_ychet stringSQL = "SELECT * FROM profilact_ychet WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("profilact_ychet", stringSQL); stringSQL1 = "SELECT DISTINCT id_profilact_ychet FROM profilact_ychet WHERE id_spec IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_profilact_ychet WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_profilact_ychet", stringSQL2); //ivr stringSQL = "SELECT * FROM ivr WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("ivr", stringSQL); stringSQL1 = "SELECT DISTINCT employee_id FROM ivr WHERE id_spec IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM employee WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("employee", stringSQL2); stringSQL1 = "SELECT DISTINCT work_type FROM ivr WHERE id_spec IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_work_type WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_work_type", stringSQL2); //resolution stringSQL = "SELECT * FROM resolution WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("resolution", stringSQL); //system stringSQL = "SELECT * FROM system"; sqlConSrc.GetDataTable("system", stringSQL); //results stringSQL = "SELECT * FROM results WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("results", stringSQL); DirectoryInfo dir = new DirectoryInfo(tempDir); dir.Create(); CopyFoto(GetDBDirPath(), tempDir, dt); DataSet ds = sqlConSrc.GetDataSet(); ds.WriteXml(tempDir + @"\" + dataFile, XmlWriteMode.WriteSchema); Boolean svResult = SaveFile(tempDir); if (!svResult) { return; } if (moveToArh) { sqlConSrc.DoQuery("UPDATE spec SET is_present = false WHERE id IN (" + strSQL + ")"); } }