public static IList GetList()
{
ArrayList ds = new ArrayList();
OleDbConnection cn;
cn = DataProvider.ConnectionData();
string strSQL;
strSQL = "Select * From DocGia";
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
OleDbDataReader dr;
dr = cmd.ExecuteReader();
while (dr.Read())
{
DocGiaDto docGia = new DocGiaDto();
docGia.MDocGia = (int)dr["MDocGia"];
docGia.HoTen = (String)dr["HoTen"];
docGia.GioiTinh = (int)dr["GioiTinh"];
docGia.NgaySinh = (DateTime)dr["NgaySinh"];
docGia.CMND = (String)dr["CMND"];
docGia.DiaChi = (String)dr["DiaChi"];
docGia.Email = (String)dr["Email"];
docGia.SoDienThoai = (String)dr["SoDienThoai"];
ds.Add(docGia);
}
dr.Close();
cn.Close();
return ds;
}
public static int Add(DocGiaDto docGia)
{
OleDbConnection cn;
cn = DataProvider.ConnectionData();
string strSQL;
strSQL = "Insert into DocGia(HoTen,GioiTinh,NgaySinh,CMND,DiaChi,Email,SoDienThoai) values (?,?,?,?,?,?,?)";
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
cmd.Parameters.Add("@HoTen", OleDbType.WChar);
cmd.Parameters.Add("@GioiTinh", OleDbType.Integer);
cmd.Parameters.Add("@NgaySinh", OleDbType.Date);
cmd.Parameters.Add("@CMND", OleDbType.WChar);
cmd.Parameters.Add("@DiaChi", OleDbType.WChar);
cmd.Parameters.Add("@Email", OleDbType.WChar);
cmd.Parameters.Add("@SoDienThoai", OleDbType.WChar);
cmd.Parameters["@HoTen"].Value = docGia.HoTen;
cmd.Parameters["@GioiTinh"].Value = docGia.GioiTinh;
cmd.Parameters["@NgaySinh"].Value = docGia.NgaySinh;
cmd.Parameters["@CMND"].Value = docGia.CMND;
cmd.Parameters["@DiaChi"].Value = docGia.DiaChi;
cmd.Parameters["@Email"].Value = docGia.Email;
cmd.Parameters["@SoDienThoai"].Value = docGia.SoDienThoai;
cmd.ExecuteNonQuery();
strSQL = "Select @@IDENTITY";
cmd = new OleDbCommand(strSQL, cn);
docGia.MDocGia = (int)cmd.ExecuteScalar();
cn.Close();
return docGia.MDocGia;
}
private void frmLapPhieuMuonSach_Load(object sender, EventArgs e)
{
cmbTimkiem.SelectedIndex = 0;
if (type == 1)
{
docgia = BUS.DocGiaBus.TimDocgiaTheoPhieu(mPhieu);
DataTable dt = new DataTable();
dt = new DataTable();
dt.Columns.Add("Chọn", System.Type.GetType("System.Boolean"));
dt.Columns.Add("Mã sách", System.Type.GetType("System.Int16"));
dt.Columns.Add("Tên sách", System.Type.GetType("System.String"));
for (int count = 0; count < sachlist.Tables[0].Rows.Count; count++)
{
DataRow rowdes = dt.NewRow();
rowdes[0] = true;
rowdes[1] = (int)sachlist.Tables[0].Rows[count][0];
rowdes[2] = sachlist.Tables[0].Rows[count][1].ToString();
dt.Rows.Add(rowdes);
}
if (docgia != null)
{
txtHoten.Text = docgia.HoTen;
txtNgaysinh.Text = docgia.NgaySinh.ToShortDateString();
txtSoDT.Text = docgia.SoDienThoai.ToString();
txtCMND.Text = docgia.CMND;
txtDiachi.Text = docgia.DiaChi;
txtEmail.Text = docgia.Email;
txtGioitinh.Text = docgia.GioiTinh == 1 ? "Nam" : "Nữ";
}
dtgSachchon.DataSource = dt;
dtgSachchon.Columns[2].Width = 200;
dayNgaymuon.Value = ngaymuon;
numSongaymuon.Value = (int)songay;
lbTitle.Text = "Sửa phiếu mượn sách";
}
}
public static void Update(DocGiaDto docGia)
{
DocGiaDao.Update(docGia);
}
public static DataSet Search(DocGiaDto maDocGia)
{
return DocGiaDao.Search(maDocGia);
}
public static int Add(DocGiaDto docGia)
{
return DocGiaDao.Add(docGia);
}
void Find()
{
try
{
int pos = cmbTimkiem.SelectedIndex;
DocGiaDto docgia = new DocGiaDto();
docgia.HoTen = null;
docgia.GioiTinh = -1;
docgia.SoDienThoai = docgia.Email = docgia.DiaChi = docgia.CMND = null;
lbThongbao.Text = "";
switch (pos)
{
case 0:
lbTimkiem.Text = "";
docgia.HoTen ="";
break;
case 1:
lbTimkiem.Text = "Tên độc giả";
docgia.HoTen = txtTenCMND.Text;
if (docgia.HoTen.ToString().Length == 0)
{
lbThongbao.Text = "Bạn chưa nhập độc giả cần tìm !!";
return;
}
break;
case 2:
lbTimkiem.Text = "Giới tính";
docgia.GioiTinh = 1 - cmbGioitinh.SelectedIndex;
break;
case 3:
lbTimkiem.Text = "Ngày sinh";
docgia.NgaySinh = dayNgaysinh.Value;
break;
case 4:
lbTimkiem.Text = "CMND";
docgia.CMND = txtTenCMND.Text;
if (docgia.CMND.ToString().Length == 0)
{
lbThongbao.Text = "Bạn chưa nhập số CMND cần tìm !!";
return;
}
break;
case 5:
lbTimkiem.Text = "Địa chỉ";
docgia.DiaChi = txtTenCMND.Text;
if (docgia.DiaChi.ToString().Length == 0)
{
lbThongbao.Text = "Bạn chưa nhập địa chỉ cần tìm !!";
return;
}
break;
case 6:
lbTimkiem.Text = "Số ĐT";
docgia.SoDienThoai = txtTenCMND.Text;
if (docgia.SoDienThoai.ToString().Length == 0)
{
lbThongbao.Text = "Bạn chưa nhập số điện thoại cần tìm !!";
return;
}
break;
case 7:
lbTimkiem.Text = "Email";
docgia.Email = txtTenCMND.Text;
if (docgia.Email.ToString().Length == 0)
{
lbThongbao.Text = "Bạn chưa nhập email cần tìm !!";
return;
}
break;
}
DataSet ds = BUS.DocGiaBus.Search(docgia);
dtgDanhsach.DataSource = ds.Tables[0];
dtgDanhsach.Columns[1].Width = 290;
dtgDanhsach.Columns[0].HeaderText = "Mã độc giả";
dtgDanhsach.Columns[1].HeaderText = "Họ tên";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void dtgDanhsach_SelectionChanged(object sender, EventArgs e)
{
if (dtgDanhsach.SelectedRows.Count>0)
{
int madocgia = (int)dtgDanhsach.SelectedRows[0].Cells[0].Value;
DocGiaDto docgia = BUS.DocGiaBus.Search(madocgia);
lbThongtinchitiet.Text = "Tên độc giả: " + docgia.HoTen;
lbThongtinchitiet.Text += "\nNgày sinh: " + docgia.NgaySinh.ToShortDateString();
lbThongtinchitiet.Text += "\nGiới tính: " + (docgia.GioiTinh==1?"Nam":"Nữ");
lbThongtinchitiet.Text += "\nCMND: " + docgia.CMND.ToString();
lbThongtinchitiet.Text += "\nĐịa chỉ: " + docgia.DiaChi.ToString();
lbThongtinchitiet.Text += "\nEmail: " + docgia.Email.ToString();
lbThongtinchitiet.Text += "\nSố điện thoại: " + docgia.SoDienThoai.ToString();
docgiainfo = docgia;
DataSet ds = BUS.DocGiaBus.Timsach(madocgia);
dtgSachmuon.DataSource = ds.Tables[0];
dtgSachmuon.Columns[0].HeaderText = "Mã sách";
dtgSachmuon.Columns[1].HeaderText = "Tên sách";
dtgSachmuon.Columns[2].HeaderText = "Tác giả";
dtgSachmuon.Columns[1].Width = 160;
dtgSachmuon.Columns[2].Width = 160;
}
}
public static void Update(DocGiaDto docGia)
{
OleDbConnection cn;
cn = DataProvider.ConnectionData();
string strSQL;
strSQL = "Update DocGia Set HoTen = ? , GioiTinh = ? , NgaySinh =?, CMND=?, DiaChi=?, Email=?, SoDienThoai=? Where MDocGia = ?";
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
cmd.Parameters.Add("@HoTen", OleDbType.WChar);
cmd.Parameters.Add("@GioiTinh", OleDbType.Integer);
cmd.Parameters.Add("@NgaySinh", OleDbType.Date);
cmd.Parameters.Add("@CMND", OleDbType.WChar);
cmd.Parameters.Add("@DiaChi", OleDbType.WChar);
cmd.Parameters.Add("@Email", OleDbType.WChar);
cmd.Parameters.Add("@SoDienThoai", OleDbType.WChar);
cmd.Parameters.Add("@MDocGia", OleDbType.Integer);
cmd.Parameters["@HoTen"].Value = docGia.HoTen;
cmd.Parameters["@GioiTinh"].Value = docGia.GioiTinh;
cmd.Parameters["@NgaySinh"].Value = docGia.NgaySinh;
cmd.Parameters["@CMND"].Value = docGia.CMND;
cmd.Parameters["@DiaChi"].Value = docGia.DiaChi;
cmd.Parameters["@Email"].Value = docGia.Email;
cmd.Parameters["@SoDienThoai"].Value = docGia.SoDienThoai;
cmd.Parameters["@MDocGia"].Value = docGia.MDocGia;
cmd.ExecuteNonQuery();
cn.Close();
}
public static DocGiaDto TimDocgiaTheoPhieu(int mPhieu)
{
DocGiaDto docGia = null;
OleDbConnection cn;
cn = DataProvider.ConnectionData();
string strSQL;
strSQL = "Select d.MDocGia, HoTen, GioiTinh, NgaySinh, CMND, DiaChi, Email, SoDienThoai From DocGia d, PhieuMuon p Where p.MPhieu= ? and p.MDocGia = d.MDocGia ";
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
cmd.Parameters.Add("@MPhieu", OleDbType.Integer);
cmd.Parameters["@MPhieu"].Value = mPhieu;
OleDbDataReader dr;
dr = cmd.ExecuteReader();
while (dr.Read())
{
docGia = new DocGiaDto();
docGia.MDocGia = (int)dr["MDocGia"];
docGia.HoTen = (String)dr["HoTen"];
docGia.GioiTinh = (int)dr["GioiTinh"];
docGia.NgaySinh = (DateTime)dr["NgaySinh"];
docGia.CMND = (String)dr["CMND"];
docGia.DiaChi = (String)dr["DiaChi"];
docGia.Email = (String)dr["Email"];
docGia.SoDienThoai = (String)dr["SoDienThoai"];
}
dr.Close();
cn.Close();
return docGia;
}
public static DataSet Search(DocGiaDto docgia)
{
DataSet docGia = new DataSet();
DataTable dt = new DataTable();
OleDbConnection cn;
cn = DataProvider.ConnectionData();
string strSQL;
strSQL = "Select MDocGia, HoTen From DocGia Where";
if (docgia.HoTen != null)
{
strSQL += " HoTen like '%" + docgia.HoTen + "%'";
}
else if (docgia.GioiTinh != -1)
{
strSQL += " GioiTinh = " + docgia.GioiTinh;
}
else if (docgia.CMND != null)
{
strSQL += " CMND like '%" + docgia.CMND + "%'";
}
else if (docgia.DiaChi != null)
{
strSQL += " DiaChi like '%" + docgia.DiaChi + "%'";
}
else if (docgia.Email != null)
{
strSQL += " Email like '%" + docgia.Email + "%'";
}
else if (docgia.SoDienThoai != null)
{
strSQL += " SoDienThoai like '%" + docgia.SoDienThoai + "%'";
}
else
{
strSQL += " NgaySinh = ? ";
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
cmd.Parameters.Add("@ngaysinh", OleDbType.Date);
cmd.Parameters["@ngaysinh"].Value = docgia.NgaySinh;
OleDbDataReader dr;
dr = cmd.ExecuteReader();
dt.Columns.Add("MDocgia", System.Type.GetType("System.Int32"));
dt.Columns.Add("Hoten", System.Type.GetType("System.String"));
while (dr.Read())
{
DataRow row = dt.NewRow();
row[0] = (int)dr[0];
row[1] = dr[1].ToString();
dt.Rows.Add(row);
}
docGia.Tables.Add(dt);
return docGia;
}
OleDbDataAdapter adp = new OleDbDataAdapter(strSQL, cn);
adp.Fill(docGia);
return docGia;
}
public static DocGiaDto Search(int maDocGia)
{
DocGiaDto docGia = null;
OleDbConnection cn;
cn = DataProvider.ConnectionData();
string strSQL;
strSQL = "Select * From DocGia Where MDocGia= ? ";
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
cmd.Parameters.Add("@MDocGia", OleDbType.Integer);
cmd.Parameters["@MDocGia"].Value = maDocGia;
OleDbDataReader dr;
dr = cmd.ExecuteReader();
while (dr.Read())
{
docGia = new DocGiaDto();
docGia.MDocGia = (int)dr["MDocGia"];
docGia.HoTen = (String)dr["HoTen"];
docGia.GioiTinh = (int)dr["GioiTinh"];
docGia.NgaySinh = (DateTime)dr["NgaySinh"];
docGia.CMND = (String)dr["CMND"];
docGia.DiaChi = (String)dr["DiaChi"];
docGia.Email = (String)dr["Email"];
docGia.SoDienThoai = (String)dr["SoDienThoai"];
}
dr.Close();
cn.Close();
return docGia;
}
