//Create new account
public bool createAccount(string username, string password, string role)
{
MySqlCommand command = new MySqlCommand();
string insertQuery = "INSERT INTO `user`(`username`, `password`, `role`) VALUES (@usn,@pass,@role)";
command.CommandText = insertQuery;
command.Connection = conn.getConnection();
command.Parameters.Add("@usn", MySqlDbType.VarChar).Value = username;
command.Parameters.Add("@pass", MySqlDbType.VarChar).Value = password;
command.Parameters.Add("@role", MySqlDbType.VarChar).Value = role;
conn.openConnection();
if (command.ExecuteNonQuery() == 1)
{
conn.closeConnection();
return(true);
}
else
{
conn.closeConnection();
return(false);
}
}
//Add method
public bool addCategory(string name, string description)
{
MySqlCommand command = new MySqlCommand();
string insertQuery = "INSERT INTO `course_category`(`category_name`, `category_description`) VALUES (@name,@des)";
command.CommandText = insertQuery;
command.Connection = conn.getConnection();
command.Parameters.Add("@name", MySqlDbType.VarChar).Value = name;
command.Parameters.Add("@des", MySqlDbType.VarChar).Value = description;
conn.openConnection();
if (command.ExecuteNonQuery() == 1)
{
conn.closeConnection();
return(true);
}
else
{
conn.closeConnection();
return(false);
}
}
//Add method
public bool addTrainee(string name, string age, string DoB, string education, string proLanguage, string TOEIC, string expDetail, string department, string location, string course_id)
{
MySqlCommand command = new MySqlCommand();
string insertQuery = "INSERT INTO `trainee`(`name`, `age`, `DoB`, `education`, `proLanguage`, `TOEIC`, `expDetail`, `department`, `location`,`course_id`) VALUES (@name,@age,@dob,@edu,@pro,@toeic,@exp,@dept,@loc,@crsid)";
command.CommandText = insertQuery;
command.Connection = conn.getConnection();
//@name,@age,@dob,@edu,@pro,@toeic,@exp,@dept,@loc
command.Parameters.Add("@name", MySqlDbType.VarChar).Value = name;
command.Parameters.Add("@age", MySqlDbType.VarChar).Value = age;
command.Parameters.Add("@dob", MySqlDbType.VarChar).Value = DoB;
command.Parameters.Add("@edu", MySqlDbType.VarChar).Value = education;
command.Parameters.Add("@pro", MySqlDbType.VarChar).Value = proLanguage;
command.Parameters.Add("@toeic", MySqlDbType.VarChar).Value = TOEIC;
command.Parameters.Add("@exp", MySqlDbType.VarChar).Value = expDetail;
command.Parameters.Add("@dept", MySqlDbType.VarChar).Value = department;
command.Parameters.Add("@loc", MySqlDbType.VarChar).Value = location;
command.Parameters.Add("@crsid", MySqlDbType.VarChar).Value = course_id;
conn.openConnection();
if (command.ExecuteNonQuery() == 1)
{
conn.closeConnection();
return(true);
}
else
{
conn.closeConnection();
return(false);
}
}
//Add method
public bool addTopic(string name, string description)
{
MySqlCommand command = new MySqlCommand();
string insertQuery = "INSERT INTO `topic`(`name`, `description`) VALUES (@name,@des)";
command.CommandText = insertQuery;
command.Connection = conn.getConnection();
//@name,@age,@dob,@edu,@pro,@toeic,@exp,@dept,@loc
command.Parameters.Add("@name", MySqlDbType.VarChar).Value = name;
command.Parameters.Add("@des", MySqlDbType.VarChar).Value = description;
conn.openConnection();
if (command.ExecuteNonQuery() == 1)
{
conn.closeConnection();
return(true);
}
else
{
conn.closeConnection();
return(false);
}
}
//create a function to get a list of trainer's information
public DataTable getInformation()
{
MySqlCommand command = new MySqlCommand("SELECT `id`, `name`, `type`, `working place`, `telephone`, `email`, `topic_id` FROM `trainer` WHERE `username`=@usn", conn.getConnection());
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataTable table = new DataTable();
command.Parameters.Add("@usn", MySqlDbType.VarChar).Value = Form1.username;
adapter.SelectCommand = command;
adapter.Fill(table);
return(table);
}
//create a function to get category list
public DataTable CourseCategoryList()
{
MySqlCommand command = new MySqlCommand("SELECT * FROM `course_category`", conn.getConnection());
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataTable table = new DataTable();
adapter.SelectCommand = command;
adapter.Fill(table);
return(table);
}
private void button1_Click(object sender, EventArgs e)
{
username = textBox1.Text;
CONNECT conn = new CONNECT();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand();
String query = "SELECT * FROM `user` WHERE `username`=@usn AND `password`=@pass AND `role`=@role";
command.CommandText = query;
command.Connection = conn.getConnection();
command.Parameters.Add("@usn", MySqlDbType.VarChar).Value = textBox1.Text;
command.Parameters.Add("@pass", MySqlDbType.VarChar).Value = textBox2.Text;
try
{
command.Parameters.Add("@role", MySqlDbType.VarChar).Value = comboBox1.SelectedItem.ToString();
}
catch { }
adapter.SelectCommand = command;
adapter.Fill(table);
//if the username and the password exist or match
if (table.Rows.Count > 0)
{
MessageBox.Show("You are logged in as " + comboBox1.SelectedItem.ToString());
if (comboBox1.SelectedIndex == 0)
{
this.Hide();
Admin admin = new Admin();
admin.Show();
}
else if (comboBox1.SelectedIndex == 1)
{
this.Hide();
Staff staff = new Staff();
staff.Show();
}
else
{
this.Hide();
Trainer trainer = new Trainer();
trainer.Show();
}
}
else
{
if (textBox1.Text.Trim().Equals(""))
{
MessageBox.Show("Please enter Username!", "Empty Username", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (textBox2.Text.Trim().Equals(""))
{
MessageBox.Show("Please enter Password!", "Empty Password", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (comboBox1.SelectedItem == null)
{
MessageBox.Show("Please select a role to login", "Empty Role", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MessageBox.Show("No username or password matches for this role", "Login Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
username = textBox1.Text;
CONNECT conn = new CONNECT();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand();
String query = "SELECT * FROM `user` WHERE `username`=@usn AND `password`=@pass";
command.CommandText = query;
command.Connection = conn.getConnection();
command.Parameters.Add("@usn", MySqlDbType.VarChar).Value = textBox1.Text;
command.Parameters.Add("@pass", MySqlDbType.VarChar).Value = textBox2.Text;
adapter.SelectCommand = command;
adapter.Fill(table);
//if the username and the password exist or match
if (table.Rows.Count > 0)
{
foreach (DataRow dr in table.Rows)
{
if (dr["role"].ToString() == "Admin")
{
MessageBox.Show("You are logged in as Admin");
this.Hide();
Admin adminPanel = new Admin();
adminPanel.Show();
}
else if (dr["role"].ToString() == "Staff")
{
MessageBox.Show("You are logged in as Staff");
this.Hide();
Staff staffPanel = new Staff();
staffPanel.Show();
}
else if (dr["role"].ToString() == "Trainer")
{
MessageBox.Show("You are logged in as Trainer");
this.Hide();
Trainer trainerPanel = new Trainer();
trainerPanel.Show();
}
else
{
MessageBox.Show("This username or password doesnt exists!", "Wrong username or password", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
/*
* MessageBox.Show("You are logged in as " + comboBox1.SelectedItem.ToString());
* if (comboBox1.SelectedIndex == 0)
* {
* this.Hide();
* Admin admin = new Admin();
* admin.Show();
* }
* else if (comboBox1.SelectedIndex == 1)
* {
* this.Hide();
* Staff staff = new Staff();
* staff.Show();
* }
* else
* {
* this.Hide();
* Trainer trainer = new Trainer();
* trainer.Show();
* }*/
else
{
if (textBox1.Text.Trim().Equals(""))
{
MessageBox.Show("Please enter Username!", "Empty Username", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (textBox2.Text.Trim().Equals(""))
{
MessageBox.Show("Please enter Password!", "Empty Password", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MessageBox.Show("This username or password doesnt exists!", "Wrong username or password", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}