// Form Login Load
private void Login_Load(object sender, EventArgs e)
{
this.MaximizeBox = false;
cmbMID.DropDownStyle = ComboBoxStyle.DropDownList;
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
String cmd = "select * from Account";
SqlCommand comm = new SqlCommand(cmd, conn);
comm.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(comm); // lấy dữ liệu về
DataTable dt = new DataTable(); // tạo kho ảo chưa dữ liệu
da.Fill(dt);
cmbMID.DataSource = dt;
cmbMID.DisplayMember = "mid";
cmbMID.ValueMember = "mid";
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void loadDID()
{
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
String cmd = "select * from Divice";
SqlCommand comm = new SqlCommand(cmd, conn);
comm.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(comm); // lấy dữ liệu về
DataTable dt = new DataTable(); // tạo kho ảo chưa dữ liệu
da.Fill(dt);
conn.Close();
cmbDName.DataSource = dt;
cmbDName.DisplayMember = "dname";
}
catch (Exception ex)
{
Console.Write(ex.ToString());
}
}
private void cmbBDDID_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
String cmd = "select DQuantity, DBorrowed, DID from Divice where dname = N'" + cmbDName.Text + "'";
SqlCommand comm = new SqlCommand(cmd, conn);
comm.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(comm); // lấy dữ liệu về
DataTable dt = new DataTable(); // tạo kho ảo chưa dữ liệu
da.Fill(dt);
conn.Close();
int quantity = Int32.Parse(dt.Rows[0]["dquantity"].ToString());
borrowed = Int32.Parse(dt.Rows[0]["dborrowed"].ToString());
int remain = quantity - borrowed;
txtBDAmount.Maximum = remain;
txtMaxAmount.Text = remain.ToString();
cmbBDDID.Text = dt.Rows[0]["did"].ToString();
txtBDID.Text = txtBDMember.Text + "_" + cmbBDDID.Text;
}
catch (Exception ex)
{
Console.Write(ex);
}
}
// Login
private void btnLogin_Click(object sender, EventArgs e)
{
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
String str = "select * from Account where mid = '" + cmbMID.Text + "' and password = '******'";
SqlCommand cmd = new SqlCommand(str, conn);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
conn.Close();
int count = ds.Tables[0].Rows.Count;
// if count == 1 is success
if (count == 1)
{
// Login success
typeLogin = ds.Tables[0].Rows[0]["type"].ToString();
idLogin = ds.Tables[0].Rows[0]["mid"].ToString();
if (typeLogin == "True")
{
ManagementForm managementForm = new ManagementForm();
managementForm.Show();
this.Hide();
}
else
{
ManagementForm managementForm = new ManagementForm();
managementForm.Show();
this.Hide();
}
}
else
{
// Login failed
MessageBox.Show("failed");
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("error: " + ex);
}
}
// Remove inProject
public static void removeInProject(String pid, String mid)
{
String cmd = "delete from inProject where PID = '" + pid + "' and MID = '" + mid + "'";
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
SqlCommand comm = new SqlCommand(cmd, conn);
comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Console.Write(ex);
}
}
// When borrow divice
public static void changeBorrowed(String did, int borrowed)
{
String cmd = "update Divice set dborrowed = '" + borrowed + "' where DID = '" + did + "'";
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
SqlCommand comm = new SqlCommand(cmd, conn);
comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Console.Write(ex);
}
}
// Delete divice out database - Xóa thiết bị
public static void deleteDivice(String did)
{
String cmd = "delete from Divice where DID = '" + did + "'";
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
SqlCommand comm = new SqlCommand(cmd, conn);
comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("This divice has been borrowed, you can't delete it!!!");
}
}
// Delete Borrowed
public static void deleteBorrowed(String bdid)
{
String cmd = "delete from BorrowedDetails where BDID = '" + bdid + "'";
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
SqlCommand comm = new SqlCommand(cmd, conn);
comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Console.Write(ex);
}
}
// Delete Member
public static void deleteMember(String mid)
{
String cmd = "delete from Member where MID = '" + mid + "'";
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
SqlCommand comm = new SqlCommand(cmd, conn);
comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("This member has joined Project, remove from Project before Delete member");
}
}
// Add member to Project
public static void addinProject(String pid, String mid, String type)
{
String cmd = "insert into inProject values ('" + pid + "','" + mid + "', '" + type + "')";
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
SqlCommand comm = new SqlCommand(cmd, conn);
comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Console.Write(ex);
}
}
// Update divice info into Databasse - Sữa thông tin thiết bị
public static void editDivice(String currentDID, String DID, String DName, String DQuantity, String DBorrowed, String DDescription)
{
String cmd = "update Divice set DID = '" + DID + "', DName = '" + DName + "', DQuantity = " + DQuantity + ", DBorrowed = "
+ DBorrowed + ", DDescription = N'" + DDescription + "' where DID = '" + currentDID + "'";
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
SqlCommand comm = new SqlCommand(cmd, conn);
comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Console.Write(ex);
}
}
// Edit Member
public static void editMember(String currMID, String MID, String MName, String Gender, String Birth, String Adress, String Phone, String Email)
{
String cmd = "update Member set MID = '" + MID + "', MName = N'" + MName + "', MGender = '" + Gender + "', MBirth = '"
+ Birth + "', MAdress = N'" + Adress + "', MPhone = '" + Phone + "', MEmail = '" + Email + "' where MID = '" + currMID + "'";
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
SqlCommand comm = new SqlCommand(cmd, conn);
comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Console.Write(ex);
}
}
// Add Borrowed
public static void addBorrowed(String BDID, String DID, String MID, String StartDate, String EndData, String amount, String manager)
{
String cmd = "insert into BorrowedDetails values ('" + BDID + "', '" + DID + "', '" + MID + "', '" + StartDate + "', '"
+ EndData + "', " + amount + ", '" + manager + "')";
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
SqlCommand comm = new SqlCommand(cmd, conn);
comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(cmd);
}
}
// Edit Project
public static void editProject(String currPID, String PID, String PName, String PStartingDate, String PEndDate, String PLeader, String PDescription)
{
String cmd = "update Project set PID = '" + PID + "', PName = N'" + PName + "', PStartingDate = '" + PStartingDate + "', PEndDate = '"
+ PEndDate + "', PLeader = '" + PLeader + "', PDescription = N'" + PDescription + "' where PID = '" + currPID + "'";
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
SqlCommand comm = new SqlCommand(cmd, conn);
comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Console.Write(ex);
}
}
// Edit Borrowed
public static void editBorrowed(String currBDID, String BDID, String DID, String MID, String StartDate, String EndData, String amount, String manager)
{
String cmd = "update BorrowedDetails set BDID = '" + BDID + "', DID = '" + DID + "', MID = '"
+ MID + "', BDStartingDate = '" + StartDate + "', BDEndDate = '" + EndData + "', AmountBorrowed = "
+ amount + ", BDManager = '" + manager + "' where BDID = '" + currBDID + "'";
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
SqlCommand comm = new SqlCommand(cmd, conn);
comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Console.Write(ex);
}
}
// Add Project
public static void addProject(String PID, String PName, String PStartingDate, String PEndDate, String PLeader, String PDescription)
{
String cmd = "insert into Project values ('" + PID + "', N'" + PName + "', '" + PStartingDate
+ "', '" + PEndDate + "', '" + PLeader + "', N'" + PDescription + "')";
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
SqlCommand comm = new SqlCommand(cmd, conn);
comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Console.Write(ex);
}
}
// Add divice into Database - Thêm thiết bị vào CSDL
public static void addDivice(String DID, String DName, String DQuantity, String DBorrowed, String DDescription)
{
String cmd = "insert into Divice values ('" + DID + "', N'" + DName + "', " + DQuantity
+ ", " + DBorrowed + ", N'" + DDescription + "')";
// Chưa thực thi - cần thêm Connection
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
SqlCommand comm = new SqlCommand(cmd, conn);
comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Console.Write(ex);
}
}
// Add member
public static void addMember(String MID, String MName, String Gender, String Birth, String Adress, String Phone, String Email)
{
String cmd = "insert into Member values ('" + MID + "', N'" + MName + "', '" + Gender
+ "', '" + Birth + "', N'" + Adress + "', '" + Phone + "', '" + Email + "')";
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
SqlCommand comm = new SqlCommand(cmd, conn);
comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Console.Write(ex);
}
}
// Add Account
public static void addAccount(String AID, String MID, String Pass, String Type)
{
String cmd = "insert into Account values ('" + AID + "', '" + MID + "', '" + Pass + "', " + Type + ")";
MessageBox.Show("Add member success!!!");
try
{
SqlConnection conn = ConnectSQL.getDBConnection();
conn.Open();
SqlCommand comm = new SqlCommand(cmd, conn);
comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Console.Write(ex);
}
}