public static bool Insert(Borrower b)
{
try
{
SqlConnection cn = new SqlConnection("Data Source=localhost;Initial Catalog=Library;Integrated Security=True");
SqlCommand cmd = new SqlCommand("Insert into Borrower(borrowerNumber, name, sex, address, telephone, email)"
+ "values(@borrowerNumber, @name, @sex, @address, @telephone, @email)", cn);
cmd.Parameters.AddWithValue("@borrowerNumber", b.BorrowerNumber);
cmd.Parameters.AddWithValue("@name", b.Name);
cmd.Parameters.AddWithValue("@sex", b.Sex);
cmd.Parameters.AddWithValue("@address", b.Address);
cmd.Parameters.AddWithValue("@telephone", b.Telephone);
cmd.Parameters.AddWithValue("@email", b.Email);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
return true;
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
}
private void buttonMAdd_Click(object sender, EventArgs e)
{
txtName.Enabled = true;
txtMemberCode.Enabled = false;
txtSex.Enabled = true;
txtAddress.Enabled = true;
txtTelephone.Enabled = true;
txtEmail.Enabled = true;
if (buttonMAdd.Text == "Finish")
{
//Check Sex
if (txtSex.Text != "M" && txtSex.Text != "F")
{
MessageBox.Show("Re-enter Sex for member : 'M' or 'F'");
txtSex.Clear();
txtSex.Focus();
return;
}
//Check telephone
if (txtTelephone.Text != "")
{
float n;
bool isNum = float.TryParse(txtTelephone.Text.Trim(), out n);
if (!isNum)
{
txtTelephone.Clear();
txtTelephone.Focus();
MessageBox.Show("Re-enter telephone number!");
return;
}
}
Borrower b = new Borrower();
b.BorrowerNumber = int.Parse(txtMemberCode.Text);
b.Name = txtName.Text;
b.Sex = char.Parse(txtSex.Text);
b.Address = txtAddress.Text;
b.Telephone = txtTelephone.Text;
b.Email = txtEmail.Text;
BorrowerBL.Insert(b);
refresh();
buttonMAdd.Text = "Add";
}
else
{
buttonMAdd.Text = "Finish";
BorrowerBL.SetBorrowerNumberMax();
int max = ++BorrowerBL.borrowerNumberMax;
txtMemberCode.Text = max.ToString();
txtName.Focus();
}
}
public static bool Delete(Borrower b)
{
try
{
SqlConnection cn = new SqlConnection("Data Source=localhost;Initial Catalog=Library;Integrated Security=True");
SqlCommand cmd = new SqlCommand("Delete Borrower where borrowerNumber = @borrowerNumber", cn);
cmd.Parameters.AddWithValue("@borrowerNumber", b.BorrowerNumber);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
}
private void buttonMDelete_Click(object sender, EventArgs e)
{
if (!isSelectedCopy())
{
MessageBox.Show("Please select a member to delete");
return;
}
Borrower c = new Borrower();
c.BorrowerNumber = int.Parse(dataGridView1.SelectedRows[0].Cells["borrowerNumber"].Value.ToString());
BorrowerBL.Delete(c);
refresh();
}
private void buttonMEdit_Click(object sender, EventArgs e)
{
if (!isSelectedCopy())
{
MessageBox.Show("Please select a member to edit!");
return;
}
if (buttonMEdit.Text == "Finish")
{
Borrower b = new Borrower();
b.BorrowerNumber = int.Parse(txtMemberCode.Text);
b.Name = txtName.Text;
b.Sex = char.Parse(txtSex.Text);
b.Address = txtAddress.Text;
b.Telephone = txtTelephone.Text;
b.Email = txtEmail.Text;
BorrowerBL.Update(b);
refresh();
buttonMEdit.Text = "Edit";
buttonMDelete.Enabled = true;
buttonMAdd.Enabled = true;
buttonMFilter.Enabled = true;
txtMemberCode.Enabled = true;
}
else
{
buttonMEdit.Text = "Finish";
txtName.Enabled = true;
txtMemberCode.Enabled = false;
txtSex.Enabled = true;
txtAddress.Enabled = true;
txtTelephone.Enabled = true;
txtEmail.Enabled = true;
txtName.Focus();
buttonMDelete.Enabled = false;
buttonMAdd.Enabled = false;
buttonMFilter.Enabled = false;
}
}
private void buttonMEdit_Click(object sender, EventArgs e)
{
if (buttonMEdit.Text == "Finish")
{
Borrower b = new Borrower();
b.BorrowerNumber = int.Parse(txtMemberCode.Text);
b.Name = txtName.Text;
b.Sex = char.Parse(txtSex.Text);
b.Address = txtAddress.Text;
b.Telephone = txtTelephone.Text;
b.Email = txtEmail.Text;
BorrowerBL.Update(b);
refresh();
buttonMEdit.Text = "Add";
}
else
{
buttonMEdit.Text = "Finish";
txtName.Enabled = true;
txtMemberCode.Enabled = false;
txtSex.Enabled = true;
txtAddress.Enabled = true;
txtTelephone.Enabled = true;
txtEmail.Enabled = true;
txtName.Focus();
}
}
public static bool Update(Borrower b)
{
try
{
SqlConnection cn = new SqlConnection("Data Source=localhost;Initial Catalog=Library;Integrated Security=True");
SqlCommand cmd = new SqlCommand("Update Borrower set name = @name, "
+ "sex = @sex, address = @address, telephone=@telephone, email=@email where borrowerNumber = @borrowerNumber", cn);
cmd.Parameters.AddWithValue("@borrowerNumber", b.BorrowerNumber);
cmd.Parameters.AddWithValue("@name", b.Name);
cmd.Parameters.AddWithValue("@sex", b.Sex);
cmd.Parameters.AddWithValue("@address", b.Address);
cmd.Parameters.AddWithValue("@telephone", b.Telephone);
cmd.Parameters.AddWithValue("@email", b.Email);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
}
