public int removeNewStaf(string nv)
{
DBKetNoi.initializeInstance();
DBKetNoi dbmg = DBKetNoi.getInstance();
SqlConnection con = dbmg.openDatabase();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "DELETE FROM NHANVIEN WHERE Id = @maNv";
cmd.Parameters.AddWithValue("@maNv", nv);
int ret = -1;
try
{
ret = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Debug.WriteLine("The product name is " + e.Message);
}
dbmg.closeDatabase();
return((ret > 0) ? ret : -1);
}
public int removeCV(string maCV)
{
DBKetNoi.initializeInstance();
DBKetNoi dbmg = DBKetNoi.getInstance();
SqlConnection con = dbmg.openDatabase();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "DELETE FROM CongViec WHERE MaCV = @mcv";
cmd.Parameters.AddWithValue("@mcv", maCV);
int ret = -1;
try
{
ret = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Debug.WriteLine("--------------> " + e.Message);
}
dbmg.closeDatabase();
return((ret > 0) ? ret : -1);
}
public static void initializeInstance()
{
lock (lockThis)
{
if (instance == null)
{
instance = new DBKetNoi();
}
}
}
public string addNewCV(CongViec nv, bool isNewNCV)
{
DBKetNoi.initializeInstance();
DBKetNoi dbmg = DBKetNoi.getInstance();
SqlConnection con = dbmg.openDatabase();
string newCongViec = "INSERT CONGVIEC (MaCV, MaCa, MoTaCV, XulyCV, DeXuat, GhiChu)"
+ " VALUES (@maCV, @maCa, @motaCV, @xuLyCV, @deXuat, @ghiChu)";
string updateCongViec = "UPDATE CONGVIEC SET MaCV = @maCV, MaCa = @maCa, MoTaCV = @motaCV, XulyCV = @xuLyCV "
+ ", DeXuat = @deXuat, GhiChu = @ghiChu WHERE MaCV = @maCV";
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
if (isNewNCV)
{
cmd.CommandText = newCongViec;
}
else
{
cmd.CommandText = updateCongViec;
}
cmd.Parameters.AddWithValue("@maCV", nv.maCV);
cmd.Parameters.AddWithValue("@maCa", nv.maCa);
cmd.Parameters.AddWithValue("@motaCV", nv.moTaCV);
cmd.Parameters.AddWithValue("@xuLyCV", nv.xuLyCV);
cmd.Parameters.AddWithValue("@deXuat", nv.deXuat);
cmd.Parameters.AddWithValue("@ghiChu", nv.ghiChu);
string ret = "";
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Debug.WriteLine("The product name is " + e.Message);
ret = e.Message;
}
dbmg.closeDatabase();
return(ret);
}
public void updateGridData(DataGridView dataGridNV)
{
DBKetNoi.initializeInstance();
DBKetNoi dbmg = DBKetNoi.getInstance();
SqlConnection con = dbmg.openDatabase();
string sql = "SELECT * FROM NhanVien";
SqlDataAdapter dataadapter = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
dataadapter.Fill(ds, "NhanVien");
dataGridNV.DataSource = ds;
dataGridNV.DataMember = "NhanVien";
dbmg.closeDatabase();
}
public void updateGridData(DataGridView dataGridNV, string caID)
{
DBKetNoi.initializeInstance();
DBKetNoi dbmg = DBKetNoi.getInstance();
SqlConnection con = dbmg.openDatabase();
string sql = "SELECT * FROM CONGVIEC WHERE MACA = '" + caID + "'";
SqlDataAdapter dataadapter = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
dataadapter.Fill(ds, "CONGVIEC");
dataGridNV.DataSource = ds;
dataGridNV.DataMember = "CONGVIEC";
dbmg.closeDatabase();
}
public string addNewStaf(NhanVien nv, bool isNewNhanVien)
{
DBKetNoi.initializeInstance();
DBKetNoi dbmg = DBKetNoi.getInstance();
SqlConnection con = dbmg.openDatabase();
string newNhanVien = "INSERT NHANVIEN (Id, MaKip, TenNhanVien, KipTruong) "
+ " VALUES (@maNV, @maKip, @tenNV, @KT)";
string updateNhanVien = "UPDATE NHANVIEN SET Id = @maNV, TenNhanVien = @tenNV, maKip = @maKip, KipTruong = @KT WHERE Id = @maNV";
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
if (isNewNhanVien)
{
cmd.CommandText = newNhanVien;
}
else
{
cmd.CommandText = updateNhanVien;
}
cmd.Parameters.AddWithValue("@maNv", nv.maNv);
cmd.Parameters.AddWithValue("@tenNv", nv.tenNV);
cmd.Parameters.AddWithValue("@maKip", nv.maKip);
cmd.Parameters.AddWithValue("@KT", nv.kipTruong);
string ret = "";
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Debug.WriteLine("The product name is " + e.Message);
ret = e.Message;
}
dbmg.closeDatabase();
return(ret);
}
public string addNewCaLamViec(CaLamViec caLV, bool isnewCaLV)
{
DBKetNoi.initializeInstance();
DBKetNoi dbmg = DBKetNoi.getInstance();
SqlConnection con = dbmg.openDatabase();
string newca = "INSERT CALAMVIEC (MaNv, MaCa, ThinhTrang, GhiChu) "
+ " VALUES (@mNv, @mC, @tinhTrang, @ghiChu)";
string updateca = "UPDATE CALAMVIEC SET MaNv = @mNv, MaCa = @mC, ThinhTrang = @tinhTrang, GhiChu = @ghiChu WHERE MaNv = @mNv";
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
if (!isnewCaLV)
{
cmd.CommandText = newca;
}
else
{
cmd.CommandText = updateca;
}
cmd.Parameters.AddWithValue("@mC", caLV.maNV);
cmd.Parameters.AddWithValue("@tG", caLV.maCa);
cmd.Parameters.AddWithValue("@mK", caLV.tinhTrang);
cmd.Parameters.AddWithValue("@mK", caLV.ghiChu);
string ret = "";
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Debug.WriteLine("The product name is " + e.Message);
ret = e.Message;
}
dbmg.closeDatabase();
return(ret);
}
public void updateComboBoxIdOnly(ComboBox cb)
{
DBKetNoi.initializeInstance();
DBKetNoi dbmg = DBKetNoi.getInstance();
SqlConnection con = dbmg.openDatabase();
string sql = "SELECT * FROM KIP";
SqlCommand sqlCmd = new SqlCommand(sql, con);
SqlDataReader sqlReader = sqlCmd.ExecuteReader();
while (sqlReader.Read())
{
cb.Items.Add(sqlReader["MaKip"].ToString() + " - " + sqlReader["TenKip"].ToString());
}
sqlReader.Close();
dbmg.closeDatabase();
}
public string addNewCa(Ca ca, bool isnewca)
{
DBKetNoi.initializeInstance();
DBKetNoi dbmg = DBKetNoi.getInstance();
SqlConnection con = dbmg.openDatabase();
string newca = "INSERT CA (MaCa, ThoiGian, MaKip) "
+ " VALUES (@mC, @tG, @mK)";
string updateca = "UPDATE CA SET ThoiGian = @tG, MaKip = @mK WHERE MaCa = @mC";
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
if (!isnewca)
{
cmd.CommandText = newca;
}
else
{
cmd.CommandText = updateca;
}
cmd.Parameters.AddWithValue("@mC", ca.maCa);
cmd.Parameters.AddWithValue("@tG", ca.thoiGian);
cmd.Parameters.AddWithValue("@mK", ca.maKip);
string ret = "";
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Debug.WriteLine("The product name is " + e.Message);
ret = e.Message;
}
dbmg.closeDatabase();
return(ret);
}
public string addNewKip(Kip kip, bool isNewKip)
{
DBKetNoi.initializeInstance();
DBKetNoi dbmg = DBKetNoi.getInstance();
SqlConnection con = dbmg.openDatabase();
string newKip = "INSERT KIP (MaKip, TenKip) "
+ " VALUES (@mK, @tK)";
string updateKip = "UPDATE KIP SET MaKip = @mK, TenKip = @tK WHERE MaKip = @mK";
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
if (isNewKip)
{
cmd.CommandText = newKip;
}
else
{
cmd.CommandText = updateKip;
}
cmd.Parameters.AddWithValue("@mK", kip.maKip);
cmd.Parameters.AddWithValue("@tK", kip.tenKip);
string ret = "";
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Debug.WriteLine("The product name is " + e.Message);
ret = e.Message;
}
dbmg.closeDatabase();
return(ret);
}
public bool isExisted(string caID, string maKip)
{
DBKetNoi.initializeInstance();
DBKetNoi dbmg = DBKetNoi.getInstance();
SqlConnection con = dbmg.openDatabase();
string sql = "SELECT * FROM CA WHERE MACA = '" + caID + "'";
SqlCommand sqlCmd = new SqlCommand(sql, con);
SqlDataReader sqlReader = sqlCmd.ExecuteReader();
bool existed = false;
while (sqlReader.Read())
{
existed = !sqlReader["MaKip"].ToString().Contains(maKip);
}
sqlReader.Close();
dbmg.closeDatabase();
return(existed);
}