public List<NhanVien> getListNhanVien()
{
String sql = @"SELECT [MaNV]
,[HoTenNV]
,[GioiTinhNV]
,[NgaySinhNV]
,[DienThoaiNV]
,[DiaChiNV]
,[EmailNV]
,[NgayVaoLam]
,[MatKhau]
,[QuyenHan]
FROM[ThuVien].[dbo].[NhanVien]";
DataTable table = dbcnn.getdata(sql);
List<NhanVien> listNhanVien = new List<NhanVien>();
foreach (DataRow row in table.Rows)
{
NhanVien nv = new NhanVien();
nv.MaNV = (int) row["MaNV"];
nv.HoTenNV = (String)row["HoTenNV"];
nv.GioiTinhNV = (Boolean)row["GioiTinhNV"];
nv.NgaySinhNV = (DateTime)row["NgaySinhNV"];
nv.DienThoaiNV = (String)row["DienThoaiNV"];
nv.DiaChiNV = (String)row["DiaChiNV"];
nv.EmailNV = (String)row["EmailNV"];
nv.NgayVaoLam = (DateTime)row["NgayVaoLam"];
nv.MatKhau = (String)row["MatKhau"];
nv.QuyenHan = (Boolean)row["QuyenHan"];
listNhanVien.Add(nv);
}
return listNhanVien;
}
private void btnDelete_Click(object sender, EventArgs e)
{
objState = EntityState.Deleted;
if (MetroFramework.MetroMessageBox.Show(this, "Bạn có chắc muốn xóa không?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
try
{
DTO.NhanVien obj = nhanVienBindingSource.Current as DTO.NhanVien;
if (obj != null)
{
bool result = NhanVienService.Delete(obj.TaiKhoan);
if (result)
{
nhanVienBindingSource.RemoveCurrent();
//pContainer.Enabled = False
pic.Image = null;
objState = EntityState.Unchanged;
}
}
}
catch (Exception ex)
{
MetroFramework.MetroMessageBox.Show(this, ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void FrmNhanVien_Load(object sender, EventArgs e)
{
try
{
nhanVienBindingSource.DataSource = NhanVienService.GetAll();
//pContainer.Enabled = False
DTO.NhanVien obj = nhanVienBindingSource.Current as DTO.NhanVien;
if (obj == null)
{
if (!string.IsNullOrEmpty(obj.AnhNV))
{
pic.Image = Image.FromFile(obj.AnhNV);
}
}
else
{
pic.SizeMode = System.Windows.Forms.PictureBoxSizeMode.StretchImage;
pic.Image = Image.FromFile(obj.AnhNV);
}
}
catch (Exception ex)
{
MetroFramework.MetroMessageBox.Show(this, ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// update nhân viên
/// </summary>
/// <param name="nhanVien"></param>
/// <returns></returns>
public bool suaNhanVien(NhanVien nhanVien)
{
if (nhanVien.MatKhau.Length != 0)
{
nhanVien.MatKhau = mh.EncodeMD5(nhanVien.MatKhau);
}
return nhanVienDAL.updateNhanVien(nhanVien);
}
public bool editNhanVien(NhanVien nv)
{
String sql = @"UPDATE [dbo].[NhanVien]
SET [HoTenNV] = '" + nv.HoTenNV + @"'
,[GioiTinhNV] = " + Convert.ToInt16(nv.GioiTinhNV) +@"
,[NgaySinhNV] = '" + nv.NgaySinhNV + @"'
,[DienThoaiNV] = '" + nv.DienThoaiNV + @"'
,[DiaChiNV] = '" + nv.DiaChiNV + @"'
,[EmailNV] = '" + nv.EmailNV+@"'
,[NgayVaoLam] = '"+nv.NgayVaoLam+@"'
,[MatKhau] = "+nv.MatKhau+@"
,[QuyenHan] = "+Convert.ToInt16(nv.GioiTinhNV)+
@"WHERE [MaNV]= "+nv.MaNV;
return dbcnn.ThucThiSQL(sql);
}
private void btnBrowse_Click(object sender, EventArgs e)
{
using (OpenFileDialog ofd = new OpenFileDialog()
{
Filter = "JPEG|*.jpg|PNG|*.png", ValidateNames = true
})
{
if (ofd.ShowDialog() != DialogResult.OK)
{
//Read image from file, then load to picture box
pic.Image = Image.FromFile(ofd.FileName);
DTO.NhanVien obj = nhanVienBindingSource.Current as DTO.NhanVien;
//if (obj != null)
// obj.AnhNV = ofd.FileName;
}
}
}
private void metroGrid_CellClick(object sender, DataGridViewCellEventArgs e)
{
try
{
DTO.NhanVien obj = nhanVienBindingSource.Current as DTO.NhanVien;
pic.SizeMode = System.Windows.Forms.PictureBoxSizeMode.StretchImage;
pic.Image = Image.FromFile(obj.AnhNV);
if (obj == null)
{
if (!string.IsNullOrEmpty(obj.AnhNV))
{
pic.Image = Image.FromFile(obj.AnhNV);
}
}
} catch (Exception ex)
{
//MetroFramework.MetroMessageBox.Show(this, ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void Insert(NhanVien info)
{
cnn = helper.GetConnect();
sqlString = "Insert into NhanVien(id, Ten, NgaySinh, CMND, GioiTinh, SoDienThoai, DiaChi, Email, UserName, PassWord, Loai ) values ('" + info.id + "',N'" + info.Ten + "','" + info.NgaySinh + "','" + info.CMND + "','" + info.GioiTinh + "','" + info.SoDienThoai + "','" + info.DiaChi + "','" + info.Email + "','" + info.Username + "','" + info.Password + "',N'" + info.Loai + "')";
try
{
//Thực thi câu lệnh SQL
cmd = new SqlCommand(sqlString, cnn);
cmd.ExecuteNonQuery();
}
catch (System.Exception e)
{
cnn.Close();
throw new Exception("Lỗi Kết Nối Cơ sở dữ liệu.");
}
cnn.Close();
}
private void button1_Click(object sender, EventArgs e)
{
NhanVien nv = new NhanVien();
nv.HoTenNV = "hai";
nv.GioiTinhNV = false;
nv.DiaChiNV = "ha tinh";
nv.DienThoaiNV = "123";
nv.EmailNV = "*****@*****.**";
nv.MatKhau = "12345";
nv.QuyenHan = false;
nv.NgaySinhNV = DateTime.Now;
nv.NgayVaoLam = DateTime.Now;
NhanVienDAL nvdal = new NhanVienDAL();
if (nvdal.InsertNhanVien(nv))
{
MessageBox.Show("chen thanh cong!");
}
else
{
MessageBox.Show("chen that bai");
}
}
public void Insert(NhanVien info)
{
nvD.Insert(info);
}
//public virtual NhanVien NhanVien { get; set; }
public TraSach()
{
phieuMuon = new PhieuMuon();
nhanVien = new NhanVien();
}
/// <summary>
/// lấy ra nhân viên bằng email
/// </summary>
/// <param name="email">địa chỉ email</param>
/// <returns></returns>
public NhanVien getNhanVienByUsername(string email)
{
String sql = @"SELECT [MaNV]
,[HoTenNV]
,[GioiTinhNV]
,[NgaySinhNV]
,[DienThoaiNV]
,[DiaChiNV]
,[EmailNV]
,[NgayVaoLam]
,[MatKhau]
,[QuyenHan]
FROM[ThuVien].[dbo].[NhanVien] WHERE [EmailNV]='" + email+"'";
DataTable table = dbcnn.getdata(sql);
NhanVien nv = new NhanVien();
if (table.Rows.Count == 1)
{
foreach (DataRow row in table.Rows)
{
nv.MaNV = (int)row["MaNV"];
nv.HoTenNV = (String)row["HoTenNV"];
nv.GioiTinhNV = (Boolean)row["GioiTinhNV"];
nv.NgaySinhNV = (DateTime)row["NgaySinhNV"];
nv.DienThoaiNV = (String)row["DienThoaiNV"];
nv.DiaChiNV = (String)row["DiaChiNV"];
nv.EmailNV = (String)row["EmailNV"];
nv.NgayVaoLam = (DateTime)row["NgayVaoLam"];
nv.MatKhau = (String)row["MatKhau"];
nv.QuyenHan = (Boolean)row["QuyenHan"];
}
}
return nv;
}
/// <summary>
/// sửa nhân viên trong cơ sở dữ liệu
/// </summary>
/// <param name="nv">Nhân Viên</param>
/// <returns></returns>
public bool updateNhanVien(NhanVien nv)
{
string sql;
if (nv.MatKhau.Length == 0)
{
sql = @"UPDATE [dbo].[NhanVien]
SET [HoTenNV] = N'" + nv.HoTenNV + @"'
,[GioiTinhNV] = " + Convert.ToInt16(nv.GioiTinhNV) + @"
,[NgaySinhNV] = '" + nv.NgaySinhNV + @"'
,[DienThoaiNV] = N'" + nv.DienThoaiNV + @"'
,[DiaChiNV] = N'" + nv.DiaChiNV + @"'
,[EmailNV] = N'" + nv.EmailNV + @"'
,[NgayVaoLam] = '" + nv.NgayVaoLam + @"'
,[QuyenHan] = " + Convert.ToInt16(nv.QuyenHan) + @"
WHERE [MaNV]= " + nv.MaNV;
}
else
{
sql = @"UPDATE [dbo].[NhanVien]
SET [HoTenNV] = N'" + nv.HoTenNV + @"'
,[GioiTinhNV] = " + Convert.ToInt16(nv.GioiTinhNV) + @"
,[NgaySinhNV] = '" + nv.NgaySinhNV + @"'
,[DienThoaiNV] = N'" + nv.DienThoaiNV + @"'
,[DiaChiNV] = N'" + nv.DiaChiNV + @"'
,[EmailNV] = N'" + nv.EmailNV + @"'
,[NgayVaoLam] = '" + nv.NgayVaoLam + @"'
,[MatKhau] = N'" + nv.MatKhau + @"'
,[QuyenHan] = " + Convert.ToInt16(nv.QuyenHan) + @"
WHERE [MaNV]= " + nv.MaNV;
}
return (dbcnn.ThucThiSQL(sql));
}
public PhieuMuon()
{
docGia = new DocGia();
nhanVien = new NhanVien();
sach = new Sach();
}
public bool InsertNhanVien(NhanVien nv)
{
String sql = @"INSERT INTO [NhanVien] ([HoTenNV],[GioiTinhNV],[NgaySinhNV],[DienThoaiNV],[DiaChiNV],[EmailNV],[NgayVaoLam],[MatKhau],[QuyenHan]) VALUES ('" + nv.HoTenNV + "'," + Convert.ToInt16(nv.GioiTinhNV) + ",'" + nv.NgaySinhNV + "','" + nv.DienThoaiNV + "','" + nv.DiaChiNV + "','" + nv.EmailNV + "','" + nv.NgayVaoLam + "','" + nv.MatKhau + "'," + Convert.ToInt16(nv.GioiTinhNV) + ")";
return (dbcnn.ThucThiSQL(sql));
}
public bool deleteNhanVien(NhanVien nv)
{
String sql = @"DELETE FROM[dbo].[NhanVien]"+
@"WHERE [MaNV]= " + nv.MaNV;
return dbcnn.ThucThiSQL(sql);
}
/// <summary>
/// delete nhân viên
/// </summary>
/// <param name="nhanVien"></param>
/// <returns></returns>
public bool xoaNhanVien(NhanVien nhanVien)
{
return nhanVienDAL.deleteNhanVien(nhanVien);
}
/// <summary>
/// thêm nhân viên
/// </summary>
/// <param name="nhanVien"></param>
/// <returns></returns>
public bool ThemNhanVien(NhanVien nhanVien)
{
nhanVien.MatKhau = mh.EncodeMD5(nhanVien.MatKhau);
return nhanVienDAL.InsertNhanVien(nhanVien);
}
public void Update(NhanVien info)
{
nvD.Update(info);
}
public void Update(NhanVien info)
{
sqlString = "UPDATE NHANVIEN SET Ten=N'" + info.Ten +
"',NgaySinh='" + info.NgaySinh +
"',CMND='" + info.CMND +
"',GioiTinh=N'" + info.GioiTinh +
"',SoDienThoai='" + info.SoDienThoai +
"',DiaChi=N'" + info.DiaChi +
"',Email='" + info.Email +
"',UserName=N'" + info.Username +
"',PassWord=N'" + info.Password +
"',Loai=N'" + info.Loai +
"' WHERE id='" + info.id + "'";
cnn = helper.GetConnect();
try
{
//Thực thi câu lệnh SQL
cmd = new SqlCommand(sqlString, cnn);
cmd.ExecuteNonQuery();
}
catch (System.Exception e)
{
cnn.Close();
throw new Exception("Lỗi Kết Nối Cơ sở dữ liệu.");
}
cnn.Close();
}
