public List <DocGia> getDocGia()
{
string sql = "SELECT * FROM DocGia";
string id, name, address, sdt, email;
List <DocGia> list = new List <DocGia>();
Connect();
try
{
SqlDataReader dr = myExecuteReader(sql);
while (dr.Read())
{
id = dr[0].ToString();
name = dr[1].ToString();
address = dr[2].ToString();
sdt = dr[3].ToString();
email = dr[4].ToString();
DocGia dg = new DocGia(id, name, address, sdt, email);
list.Add(dg);
}
dr.Close();
return(list);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
Disconnect();
}
}
public void Update(DocGia dg)
{
string sql = string.Format("update DOCGIA set HoTen = N'{0}', GioiTinh = N'{1}', NamSinh = '{2}', DiaChi = N'{3}' where MaDocGia = '{4}'",
dg.HoTen, dg.GioiTinh, dg.NamSinh, dg.DiaChi, dg.MaDocGia);
Excute(sql);
}
public void ThemDocGia(DocGia docGia)
{
using (QLThuVienDataContext db = new QLThuVienDataContext())
{
db.DocGias.InsertOnSubmit(docGia);
db.SubmitChanges();
}
}
public void XoaDocGia(String maDocGia)
{
using (QLThuVienDataContext db = new QLThuVienDataContext())
{
DocGia docGiaXoa = db.DocGias.Single(s => s.mssv == maDocGia);
docGiaXoa.Disable = true;
db.SubmitChanges();
}
}
public DocGia LayDocGia(string maDocGia)
{
DocGia docGia = null;
using (QLThuVienDataContext db = new QLThuVienDataContext())
{
docGia = db.DocGias.Single(dg => dg.mssv == maDocGia && dg.Disable == false);
}
return(docGia);
}
public void SuaDocGia(DocGia docGia)
{
using (QLThuVienDataContext db = new QLThuVienDataContext())
{
DocGia docGiaSua = db.DocGias.Single(dg => dg.mssv == docGia.mssv);
docGiaSua.HoTen = docGia.HoTen;
docGiaSua.GioiTinh = docGia.GioiTinh;
docGiaSua.NgaySinh = docGia.NgaySinh;
db.SubmitChanges();
}
}
public bool DeleteDG(DocGia dg)
{
string sql = "DELETE FROM DocGia WHERE MaDocGia = @id";
List <SqlParameter> Parameters = new List <SqlParameter>();
Parameters.Add(new SqlParameter("@id", dg.MaDG));
try
{
return(myExecuteNonQuery(sql, CommandType.Text, Parameters) > 0);
}
catch (SqlException ex)
{
throw ex;
}
}
public bool Insert(DocGia dg)
{
if (GetData("select* from DOCGIA where MaDocGia = '" + dg.MaDocGia + "'").Rows.Count > 0)
{
return(false);
}
string sql = string.Format("Insert Into ACCOUNT values('{0}', '{1}', '{2}', '{3}')", dg.MaDocGia, dg.TenDangNhap, "", 1);
Excute(sql);
sql = string.Format("Insert Into DOCGIA values('{0}','{1}',N'{2}','{3}','{4}','{5}')",
dg.MaDocGia, dg.TenDangNhap, dg.HoTen, dg.GioiTinh, dg.NamSinh, dg.DiaChi);
Excute(sql);
return(true);
}
public bool UpdateDG(DocGia docGia)
{
string sql = "UPDATE DocGia SET TenDocGia = @name, DiaChi = @address, SDT = @phone, Email = @email Where MaDocGia = @id";
List <SqlParameter> Parameters = new List <SqlParameter>();
Parameters.Add(new SqlParameter("@id", docGia.MaDG));
Parameters.Add(new SqlParameter("@name", docGia.TenDG));
Parameters.Add(new SqlParameter("@address", docGia.DiaChi));
Parameters.Add(new SqlParameter("@phone", docGia.SDT));
Parameters.Add(new SqlParameter("@email", docGia.Email));
try
{
return(myExecuteNonQuery(sql, CommandType.Text, Parameters) > 0);
}
catch (SqlException ex)
{
throw ex;
}
}
public int Add(DocGia docGia)
{
string sql = "INSERT INTO DocGia VALUES (@id,@name,@address,@phone,@email)";
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("@id", docGia.MaDG));
parameters.Add(new SqlParameter("@name", docGia.TenDG));
parameters.Add(new SqlParameter("@address", docGia.DiaChi));
parameters.Add(new SqlParameter("@phone", docGia.SDT));
parameters.Add(new SqlParameter("@email", docGia.Email));
try
{
return(myExecuteNonQuery(sql, CommandType.Text, parameters));
}
catch (SqlException ex)
{
throw ex;
}
}
public bool ThemDocGia(DocGia dg)
{
try
{
var conn = GetConnString();
// Query string - vì mình để TV_ID là identity (giá trị tự tăng dần) nên ko cần fải insert ID
var sql = "dbo.InsertDocGia";
SqlParameter[] pars =
{
new SqlParameter("@maDG", SqlDbType.Int)
{
Value = dg.Ma
},
new SqlParameter("@maLoaiDG", SqlDbType.Int)
{
Value = dg.MaLoai
}
};
var soLuongOK = SqlHelper.ExecuteNonQuery(conn, sql, CommandType.StoredProcedure, pars);
if (soLuongOK > 0)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
}
finally
{
// Dong ket noi
_conn.Close();
}
return(false);
}
public void SuaDG(DocGia docgia)
{
String query = "update DocGia set TenDocGia=N'" + docgia.TenDg + "', GioiTinh=N'" + docgia.Gt + "', DiaChi=N'" + docgia.DiaChi + "', SDT='" + docgia.Sdt + "' where MaDocGia=N'" + docgia.MaDg + "'";
DataProvider.Instance.ExecuteNonQuery(query);
}
public void ThemDG(DocGia docgia)
{
String query = "insert DocGia values(N'" + docgia.MaDg + "', N'" + docgia.TenDg + "', N'" + docgia.Gt + "', N'" + docgia.DiaChi + "', '" + docgia.Sdt + "')";
DataProvider.Instance.ExecuteNonQuery(query);
}