bai 4

namespace QLHocSinh

{

public partial class Form1 :

Form

{

//khai bao bien su dung voi csdl

private OleDbConnection

connection;

private OleDbDataAdapter

adapter;

private OleDbCommand

command;

private DataSet

dataset;

//cac bien

private string

maHS;

private string

tenHS;

private string

maLop;

private string

diaChi;

private float

dTB;

private DateTime

ngaysinh;

public

Form1()

{

InitializeComponent();

}

//Ham ket noi du lieu

private void ketnoi(string

file)

{

string conn_str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="

+ file;

connection =

new OleDbConnection

(conn_str);

}

private void Form1_Load(object sender, EventArgs

e)

{

//chon du lieu de ket noi

ketnoi(

"D:/visua on Pc09/Tuan4/QLHOCSINH.mdb"

);

//Load du lieu vao combo box Lop

cbLop.DataSource = getDSLop();

//Collumn se duoc hien thi

cbLop.DisplayMember =

"TenLop"

;

//Collumn se duoc luu gia tri

cbLop.ValueMember =

"MaLop"

;

//Load du lieu len datagridview hoc sinh

dgHocSinh.DataSource = getDSHocSinh();

//Thiet lap header cho datagridview hoc sinh

DinhDangLuoi();

}

private void

DinhDangLuoi()

{

dgHocSinh.ReadOnly =

false

;

dgHocSinh.Columns[0].HeaderText=

"M HS"

;

dgHocSinh.Columns[0].Width = 70;

dgHocSinh.Columns[1].HeaderText =

"Tn HS"

;

dgHocSinh.Columns[1].Width = 150;

dgHocSinh.Columns[2].HeaderText =

"Ngy sinh"

;

dgHocSinh.Columns[2].Width = 90;

dgHocSinh.Columns[3].HeaderText =

"a ch"

;

dgHocSinh.Columns[3].Width = 200;

dgHocSinh.Columns[4].HeaderText =

"Diem TB"

;

dgHocSinh.Columns[4].Width = 80;

dgHocSinh.Columns[5].HeaderText =

"Lp"

;

dgHocSinh.Columns[5].Width = 80;

}

//Lay danh sach lop de ket noi combobox

private DataTable

getDSLop()

{

adapter =

new OleDbDataAdapter("Select * from LOP"

, connection);

dataset =

new DataSet

();

adapter.Fill(dataset);

return

dataset.Tables[0];

}

//Lay danh sach hoc sinh

private DataTable

getDSHocSinh()

{

adapter =

new OleDbDataAdapter("Select h.MaHS, h.TenHS, h.NgaySinh,h.DiaChi, h.DTB, l.TenLop From HOCSINH h, LOP l Where h.MaLop=l.MaLop"

, connection);

dataset =

new DataSet

();

adapter.Fill(dataset);

return

dataset.Tables[0];

}

// button Thoat

private void button3_Click(object sender, EventArgs

e)

{

Application

.Exit();

}

// button luu

private void button1_Click(object sender, EventArgs

e)

{

getData();

bool

status = trangthai();

if (status == false)

//chua co trong db

insert();

else

update();

MessageBox.Show("Cap nhap thanh cong", "Thong Bao"

,

MessageBoxButtons.OK, MessageBoxIcon

.Information);

}

// ham lay du lieu

private void

getData()

{

maHS = txtMaHS.Text;

tenHS = txtTenHS.Text;

ngaysinh = dtNS.Value;

diaChi = txtDiaChi.Text;

dTB =

float

.Parse(txtTB.Text);

maLop = cbLop.SelectedValue.ToString();

}

//insert (them moi) du lieu

private void

insert()

{

connection.Open();

//mo ket noi

string insert_str = "INSERT INTO HOCSINH VALUES ('"

+

maHS +

"', '" + tenHS + "', '"

+

ngaysinh.ToShortDateString() +

"', '"

+

diaChi +

"', "

+

dTB +

", '" + maLop + "')"

;

command =

new OleDbCommand

(insert_str, connection);

command.ExecuteNonQuery();

connection.Close();

dgHocSinh.DataSource = getDSHocSinh();

}

//update (cap nhap) du lieu da co

private void

update()

{

connection.Open();

//mo ket noi

string insert_str = "UPDATE HOCSINH SET TenHS='"

+

tenHS +

"', NgaySinh='"

+

ngaysinh.ToShortDateString() +

"', DiaChi='"

+

diaChi +

"', DTB="

+

dTB +

", MaLop='" + maLop + "' WHERE MaHS='" + maHS+ "'"

;

command =

new OleDbCommand

(insert_str, connection);

command.ExecuteNonQuery();

connection.Close();

dgHocSinh.DataSource = getDSHocSinh();

}

//delete

private void

delete()

{

connection.Open();

string del_str = "DELETE FROM HOCSINH WHERE maHS = '" + maHS + "'"

;

command =

new OleDbCommand

(del_str, connection);

command.ExecuteNonQuery();

connection.Close();

}

// button xoa du lieu

private void button2_Click(object sender, EventArgs

e)

{

getData();

delete();

MessageBox.Show("Xoa du lieu thanh cong", "Thong Bao"

,

MessageBoxButtons.OK, MessageBoxIcon

.Information);

}

//ham kiem tra trang thai la insert hay update

//neu maHS da co trong bag HOCSINH thi update, con khong thi insert

private bool

trangthai()

{

bool tatkt = false

;

string

maHS = txtMaHS.Text;

//SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ConnectionString);

OleDbCommand cmd = new OleDbCommand("Select * from HOCSINH"

, connection);

connection.Open();

//SqlDataReader dr = cmd.ExecuteReader();

OleDbDataReader

dr = cmd.ExecuteReader();

while (dr.Read())

//doc de khi het cac hang trong bang du lieu

{

if

(maHS == dr.GetString(0))

{

tatkt =

true

;

break;

// tim thay hang' nao co maHS trung thi thoat

}

}

connection.Close();

return

tatkt;

}

//Cach khac de kiem tra lieu maHS da ton tai trong co so du lieu hay chua

private bool

kiemtratontai()

{

bool tatkt = false

;

string

maHS = txtMaHS.Text;

//SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ConnectionString);

//con.Open();

OleDbDataAdapter da_kiemtra = new OleDbDataAdapter("Select * from HOCSINH where MaHS='" + maHS + "'"

, connection);

DataTable dt_kiemtra = new DataTable

();

da_kiemtra.Fill(dt_kiemtra);

if

(dt_kiemtra.Rows.Count > 0)

{

tatkt =

true

;

}

da_kiemtra.Dispose();

return

tatkt;

}

//Xu ly su kien khi click chuot len datagrid view

private void dgHocSinh_CellContentClick(object sender, DataGridViewCellEventArgs

e)

{

int i = e.RowIndex;

//tra ve dong ma chua cell duoc click

txtMaHS.Text = dgHocSinh[0, i].Value.ToString();

txtTenHS.Text = dgHocSinh[1, i].Value.ToString();

dtNS.Value =

DateTime

.Parse(dgHocSinh[2, i].Value.ToString());

txtDiaChi.Text = dgHocSinh[3, i].Value.ToString();

cbLop.Text = dgHocSinh[4, i].Value.ToString();

txtTB.Text = dgHocSinh[5, i].Value.ToString();

}

}

}