private void Load_KQ()
{
string query = "select * from TESTADMIN.V_KETQUA";
DataTable data = OracleDataController.GetDatabase(query);
dataKQ.DataSource = data;
}
private void Load_TB()
{
string query = "select MA_THONGBAO,NOIDUNG,LOAITB from TESTADMIN.THONGBAO";
DataTable data = OracleDataController.GetDatabase(query);
dataTB.DataSource = data;
}
private void Load_TV_DB()
{
string query = "select MA_THANHVIEN, UCV1, UCV2, UCV3 from TESTADMIN.V_THEODOIPHIEUBAU";
DataTable data = OracleDataController.GetDatabase(query);
dataTVDB.DataSource = data;
}
private void btnEditPriInCol_Click(object sender, EventArgs e)
{
string grantTo = "";
if (checkBoxPriUserInCol.Checked)
{
grantTo = comboEditPriInColRole.SelectedItem.ToString();
comboEditPriInColUser.Enabled = false;
}
else if (checkBoxPriRoleInCol.Checked)
{
grantTo = comboEditPriInColUser.SelectedItem.ToString();
comboEditPriInColRole.Enabled = false;
}
string queryGrantInColumn = String.Format("GRANT {0}({1}) ON {2} TO {3}"
, comboEditPriInColPri.SelectedItem.ToString(),
comboEditPriInColCol.SelectedItem.ToString(),
comboEditPrivInColTable.SelectedItem.ToString(),
grantTo);
OracleDataController.ConnectDB(FormLogin.connectionStr);
bool checkIfGrant = OracleDataController.Grant(queryGrantInColumn);
if (checkIfGrant)
{
MessageBox.Show("Grant in column successfully!");
}
else
{
MessageBox.Show("Grant in column failed!");
return;
}
}
private void BOPHIEU_Click_1(object sender, EventArgs e)
{
if (idList.Count() == 3)
{
try
{
string query = "DELETE FROM TESTADMIN.PHIEUBAU WHERE MA_THANHVIEN = '" + User.Username + "'";
OracleDataController.Delete(query);
string insert = "INSERT INTO TESTADMIN.PHIEUBAU (MA_THANHVIEN, UCV1, UCV2, UCV3) VALUES('" + User.Username + "','" + idList[0] + "','" + idList[1] + "','" + idList[2] + "')";
OracleDataController.Insert(insert);
MessageBox.Show("Bỏ phiếu thành công");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
this.Load_PhieuBau();
this.Load_Form();
}
else
{
MessageBox.Show(" Vui lòng bỏ phiếu tối đa 3 ứng cử viên !");
}
}
private void Load_ToLap()
{
string query = "select * from TESTADMIN.TOLAP";
DataTable data = OracleDataController.GetDatabase(query);
dataToLap.DataSource = data;
}
private void Load_UCV()
{
string query = "select * from TESTADMIN.UNGCUVIEN";
DataTable data = OracleDataController.GetDatabase(query);
dataUCV.DataSource = data;
}
private void btnInsertUser_Click(object sender, EventArgs e)
{
OracleDataController.ConnectDB(FormLogin.connectionStr);
string queryUserInfor = "select USERNAME from SYS.V_DS_USER where account_status = " + "\'OPEN\'" + "and default_tablespace = " + "\'USERS\'";
DataTable dataTable = OracleDataController.GetDatabase(queryUserInfor);
string userName = txtInsertUsername.Text;
string passWord = txtInsertPassword.Text;
DataRow[] foundAuthors = dataTable.Select("USERNAME = '******'");
if (foundAuthors.Length != 0)
{
MessageBox.Show("User has already existed!. Please enter another name!");
return;
}
else
{
OracleDataController.ConnectDB(FormLogin.connectionStr);
string insertQuery = "create user " + userName + " identified by " + passWord;
bool checkInsert = OracleDataController.Insert(insertQuery);
if (checkInsert)
{
MessageBox.Show("Insert successfully!");
}
else
{
MessageBox.Show("Insert failed!");
}
}
}
private void LoadDataGridViewUserInfor()
{
string query = "select * from SYS.V_DS_USER WHERE USERNAME NOT LIKE 'TESTADMIN'";
DataTable dataTable = OracleDataController.GetDataGridView(query);
dataGVShowUserInfor.DataSource = dataTable;
}
private void Load_TheoDoi()
{
string query = "select * from TESTADMIN.THEODOI";
DataTable data = OracleDataController.GetDatabase(query);
dataTheoDoi.DataSource = data;
}
private void Load_GiamSat()
{
string query = "select * from TESTADMIN.GIAMSAT";
DataTable data = OracleDataController.GetDatabase(query);
dataGiamSat.DataSource = data;
}
private void Load_NguoiDiBau()
{
string query = "select * from TESTADMIN.V_DSNGUOIBAU";
DataTable data = OracleDataController.GetDatabase(query);
dataThanhVien.DataSource = data;
}
private void btnInsertRole_Click(object sender, EventArgs e)
{
OracleDataController.ConnectDB(FormLogin.connectionStr);
string query = "select ROLE from DBA_ROLES where common = \'NO\'";
DataTable dataTable = OracleDataController.GetDatabase(query);
DataRow[] foundAuthors = dataTable.Select("ROLE = '" + txtInsert.Text + "'");
if (foundAuthors.Length != 0)
{
MessageBox.Show("Role has already existed!. Please enter another name!");
return;
}
else
{
query = "create role " + txtInsert.Text;
bool checkIsInsert = OracleDataController.Insert(query);
if (checkIsInsert)
{
MessageBox.Show("Insert role successfully!");
}
else
{
MessageBox.Show("Insert role failed!");
}
}
}
private void btnEditRoleUser_Click(object sender, EventArgs e)
{
OracleDataController.ConnectDB(FormLogin.connectionStr);
string query = null;
if (comboEditUserInfor.SelectedIndex != -1)
{
if (comboOldRole.Items.Count > 0)
{
query = "revoke " + comboOldRole.Text + " from " + comboEditUserInfor.SelectedItem.ToString();
bool checkIsRevoke = OracleDataController.Revoke(query);
if (checkIsRevoke)
{
MessageBox.Show("Revoke role successfully!");
}
}
query = "grant " + comboEditNewRole.SelectedItem.ToString() + " to " + comboEditUserInfor.SelectedItem.ToString();
bool checkIsGrant = OracleDataController.Grant(query);
if (checkIsGrant)
{
MessageBox.Show("Grant role successfully!");
}
}
}
public static void LoadDataToGrid(DataGridView dgv, List <string> listOfColumns, StoredProcedure storedProcedure)
{
OracleDataController.ConnectDB(OracleDataController.GetDBConnection());
dgv.DataSource = OracleDataController.ExecuteStoredProcForDataTable(storedProcedure);
for (int i = 0; i < dgv.Columns.Count; i++)
{
dgv.Columns[i].HeaderText = listOfColumns[i].ToString();
}
}
public static void LoadDataToGrid(DataGridView dgv, List <string> listOfColumns, string tableName)
{
OracleDataController.ConnectDB(OracleDataController.GetDBConnection());
var query = $"SELECT * FROM {tableName}";
dgv.DataSource = OracleDataController.GetDataGridView(query);
for (int i = 0; i < dgv.Columns.Count; i++)
{
dgv.Columns[i].HeaderText = listOfColumns[i].ToString();
}
}
private void LoadUserInfor()
{
OracleDataController.ConnectDB(FormLogin.connectionStr);
string query = "select USERNAME from SYS.V_DS_USER where account_status = " + "\'OPEN\'" + "and default_tablespace = " + "\'USERS\'" + " and USERNAME NOT LIKE 'TESTADMIN' order by USERNAME";
DataTable dataTable = OracleDataController.GetDatabase(query);
comboUserDelete.Items.Clear();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
comboUserDelete.Items.Add(dataTable.Rows[i][0].ToString());
}
}
// Revoke privileges from user/role
private void button1_Click(object sender, EventArgs e)
{
string revokeTo = "";
comboWithGrantOption.Enabled = false;
if (checkBoxUserPri.Checked)
{
revokeTo = comboEditPriviRole.SelectedItem.ToString();
comboEditPrivUser.Enabled = false;
if (comboEditPriviRole.SelectedIndex == -1)
{
MessageBox.Show("You didn't choose the role yet!");
return;
}
}
else if (checkBoxRolePri.Checked)
{
revokeTo = comboEditPrivUser.SelectedItem.ToString();
comboEditPriviRole.Enabled = false;
if (comboEditPrivUser.SelectedIndex == -1)
{
MessageBox.Show("You didn't choose the user yet!");
return;
}
}
if (comboPrivi.SelectedIndex == -1)
{
MessageBox.Show("You didn't choose the privilege yet!");
return;
}
if (comboTargetObj.SelectedIndex == -1)
{
MessageBox.Show("You didn't choose the target object yet!");
return;
}
string pri = comboPrivi.SelectedItem.ToString();
string targetObj = comboTargetObj.SelectedItem.ToString();
string queryGrantPri = "revoke " + pri + " on " + targetObj + " from " + revokeTo;
OracleDataController.ConnectDB(FormLogin.connectionStr);
bool checkIsGrant = OracleDataController.Grant(queryGrantPri);
if (checkIsGrant)
{
MessageBox.Show("Revoke privilege successfully!");
}
}
private void Load_PhieuBau()
{
string query = "select * from TESTADMIN.V_PHIEUBAUTHANHVIEN";
DataTable data = OracleDataController.GetDatabase(query);
if (data.Rows.Count != 0)
{
idList.Add(data.Rows[data.Rows.Count - 1]["UCV1"].ToString());
idList.Add(data.Rows[data.Rows.Count - 1]["UCV2"].ToString());
idList.Add(data.Rows[data.Rows.Count - 1]["UCV3"].ToString());
}
}
private void comboEditPrivInColTable_SelectedIndexChanged(object sender, EventArgs e)
{
string queryColumn = "SELECT column_name" + " FROM USER_TAB_COLUMNS " +
"WHERE table_name = '" + comboEditPrivInColTable.SelectedItem.ToString() + "'";
OracleDataController.ConnectDB(FormLogin.connectionStr);
DataTable dataTable = OracleDataController.GetDatabase(queryColumn);
comboEditPriInColCol.Items.Clear();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
comboEditPriInColCol.Items.Add(dataTable.Rows[i][0]);
}
}
private void comboEditUserInfor_SelectedIndexChanged(object sender, EventArgs e)
{
if (comboEditUserInfor.SelectedIndex != -1)
{
OracleDataController.ConnectDB(FormLogin.connectionStr);
string queryOldRoleInfor = "select granted_role from dba_role_privs where GRANTEE = '" + comboEditUserInfor.SelectedItem.ToString() + "'";
DataTable dataTable = OracleDataController.GetDatabase(queryOldRoleInfor);
comboOldRole.Items.Clear();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
comboOldRole.Items.Add(dataTable.Rows[i][0].ToString());
}
}
}
private void btnCheckRolePrivileges_Click(object sender, EventArgs e)
{
string query = "";
if (comboRoleList.SelectedIndex != -1)
{
query = "select * from DBA_TAB_PRIVS WHERE GRANTEE ='" + comboRoleList.SelectedItem.ToString() + "'";
}
else
{
MessageBox.Show("Youd didn't choose the role yet!");
return;
}
DataTable dataTable = OracleDataController.GetDatabase(query);
dataGVShowUserInfor.DataSource = dataTable;
}
private void button1_Click(object sender, EventArgs e)
{
string query = "";
if (comboUserList.SelectedIndex != -1)
{
MessageBox.Show(comboUserList.SelectedItem.ToString());
query = "SELECT * FROM DBA_TAB_PRIVS DRP WHERE DRP.GRANTEE = '" + comboUserList.SelectedItem.ToString() + "'";
}
else
{
MessageBox.Show("Youd didn't choose the user yet!");
return;
}
DataTable dataTable = OracleDataController.GetDatabase(query);
dataGVShowUserInfor.DataSource = dataTable;
}
private void GetRoleInfor()
{
OracleDataController.ConnectDB(FormLogin.connectionStr);
string query = "select ROLE from DBA_ROLES where common = \'NO\'";
MessageBox.Show(query);
if (query == null || query == "")
{
return;
}
DataTable dataTable = OracleDataController.GetDatabase(query);
comboDeleteRole.Items.Clear();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
comboDeleteRole.Items.Add(dataTable.Rows[i][0].ToString());
}
}
private void Load_ThongTin()
{
string query = "select * from TESTADMIN.V_THONGTINTHANHVIEN";
DataTable data = OracleDataController.GetDatabase(query);
labelThongTin.Text = data.Rows[0]["MA_THANHVIEN"].ToString();
textHoTen.Text = data.Rows[0]["HOTEN"].ToString();
textGioiTinh.Text = data.Rows[0]["GIOITINH"].ToString();
textDonVi.Text = data.Rows[0]["DONVI"].ToString();
textCongTac.Text = data.Rows[0]["CONGTAC"].ToString();
textChiNhanh.Text = data.Rows[0]["CHINHANH"].ToString();
textDC.Text = data.Rows[0]["DC_TAMTRU"].ToString();
textDCTT.Text = data.Rows[0]["DC_THUONGTRU"].ToString();
textLuong.Text = data.Rows[0]["LUONG"].ToString();
textNamSinh.Text = data.Rows[0]["NAMSINH"].ToString();
textQueQuan.Text = data.Rows[0]["QUEQUAN"].ToString();
textQuocTich.Text = data.Rows[0]["QUOCTICH"].ToString();
textTamNghi.Text = data.Rows[0]["TAMNGHI"].ToString();
}
private void Delete_Click(object sender, EventArgs e)
{
string userName = null;
if (comboUserDelete.SelectedIndex != -1)
{
userName = comboUserDelete.SelectedItem.ToString();
}
string query = "drop user " + userName;
bool checkIsDeleted = OracleDataController.Delete(query);
if (checkIsDeleted)
{
MessageBox.Show("Delete user successfully!");
}
else
{
MessageBox.Show("Delete user failed!");
}
}
private void GetUserRoleCombobox()
{
OracleDataController.ConnectDB(FormLogin.connectionStr);
string queryUserInfor = "select USERNAME from SYS.V_DS_USER where account_status = " + "\'OPEN\'" + "and default_tablespace = " + "\'USERS\'" + " and USERNAME NOT LIKE 'TESTADMIN' order by USERNAME";
DataTable dataTable = OracleDataController.GetDatabase(queryUserInfor);
comboEditUserInfor.Items.Clear();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
comboEditUserInfor.Items.Add(dataTable.Rows[i][0].ToString());
}
string queryNewRoleInfor = "select ROLE from DBA_ROLES where common = \'NO\'";
dataTable = OracleDataController.GetDatabase(queryNewRoleInfor);
comboEditNewRole.Items.Clear();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
comboEditNewRole.Items.Add(dataTable.Rows[i][0].ToString());
}
}
public static void LoadDataToCombobox(ComboBox cbx, ComboBox cbxUpdate, string tableName, string valueName, string displayName)
{
OracleDataController.ConnectDB(OracleDataController.GetDBConnection());
var query = $"SELECT * FROM {tableName} WHERE LibraryTypeStatusID = 1";
if (tableName.Equals(TableConstants.LibraryTypeStatus))
{
query = $"SELECT * FROM {tableName}";
}
// Bind data to Combobox
var dt = OracleDataController.GetDataGridView(query);
cbx.DataSource = dt;
cbx.ValueMember = valueName;
cbx.DisplayMember = displayName;
cbxUpdate.DataSource = dt;
cbxUpdate.ValueMember = valueName;
cbxUpdate.DisplayMember = displayName;
}
private void btnDeleteRole_Click(object sender, EventArgs e)
{
if (comboDeleteRole.SelectedIndex != -1)
{
OracleDataController.ConnectDB(FormLogin.connectionStr);
string query = "drop role " + comboDeleteRole.SelectedItem.ToString();
bool checkIsDelete = OracleDataController.Delete(query);
if (checkIsDelete)
{
MessageBox.Show("Delete role successfully!");
}
else
{
MessageBox.Show("Delete role failed!");
}
}
else
{
MessageBox.Show("You didn't choose the role yet!");
return;
}
}
private void ButtonEdit_Click(object sender, EventArgs e)
{
string hoten = textHoTen.Text;
string gioitinh = textGioiTinh.Text;
string quequan = textQueQuan.Text;
int namsinh = Int32.Parse(textNamSinh.Text);
string quoctich = textQuocTich.Text;
string thuongtru = textDCTT.Text;
string tamtru = textDC.Text;
try
{
string query = "UPDATE TESTADMIN.V_THONGTINTHANHVIEN SET HOTEN= '" + hoten + "',GIOITINH='" + gioitinh + "',QUEQUAN = '" + quequan + "', NAMSINH = '" + namsinh + "',QUOCTICH = '" + quoctich + "', DC_THUONGTRU = '" + thuongtru + "',DC_TAMTRU = '" + tamtru + "'WHERE MA_THANHVIEN = '" + User.Username + "'";
OracleDataController.Update(query);
MessageBox.Show("Cập nhật thành công");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
this.Load_ThongTin();
}