public bool Sua(TheLoai tl)
{
try
{
string query = @"UPDATE dbo.TheLoai set TENTHELOAI=N'" + tl.TenTheLoai + "' WHERE MaTheLoai='" + tl.MaTheLoai + "'";
OpenConection();
ExecuteQueries(query);
CloseConnection();
return(true);
}
catch (Exception ex)
{
SetEx(ex);
return(false);
}
}
public string createCategory(TheLoai c)
{
try
{
SqlCommand command = truyVan1("insert into TheLoai5 values(@MaTLoai, @TenTLoai,@GhiChu)");
command.Parameters.Add("@MaTLoai", SqlDbType.NVarChar).Value = c.MaTLoai;
command.Parameters.Add("@TenTLoai", SqlDbType.NVarChar).Value = c.TenTLoai;
command.Parameters.Add("@GhiChu", SqlDbType.NVarChar).Value = c.GhiChu;
command.ExecuteNonQuery();
return("Thêm thành công !");
}
catch (Exception ex)
{
return("Err: " + ex.Message);
}
}
public List <TheLoai> DSTL()
{
SqlDataReader d = DALHelper.ExecuteReader(DALHelper.ConnectionString, CommandType.StoredProcedure, "tblTheLoai_DS", null);
List <TheLoai> mylist = new List <TheLoai>();
while (d.Read())
{
TheLoai lv = new TheLoai();
lv.MaTheLoai = d[0].ToString();
lv.TenTheLoai = d[1].ToString();
lv.MoTa = d[2].ToString();
mylist.Add(lv);
}
d.Dispose();
return(mylist);
}
public bool Them(TheLoai tl)
{
try
{
string query = @"INSERT INTO TheLoai( matheloai ,Tentheloai )
VALUES ( '" + tl.MaTheLoai + "',N'" + tl.TenTheLoai + "')";
OpenConection();
ExecuteQueries(query);
CloseConnection();
return(true);
}
catch (Exception ex)
{
SetEx(ex);
return(false);
}
}
public TheLoai findByID(string MaTLoai)
{
TheLoai category = new TheLoai();
SqlCommand command = truyVan1("select * from TheLoai5 where MaTLoai =@MaTLoai");
command.Parameters.Add("@MaTLoai", SqlDbType.NVarChar).Value = MaTLoai;
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
category.MaTLoai = reader.GetString(0);
category.TenTLoai = reader.GetString(1);
category.GhiChu = reader.GetString(2);
}
reader.Close();
return(category);
}
public bool xoaTheLoai(TheLoai theLoai)
{
if (checkMa(theLoai.MaTLoai))
{
SqlCommand command = conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "delete TheLoai5 where MaTLoai=@ma";
command.Parameters.Add("@ma", SqlDbType.NVarChar).Value = theLoai.MaTLoai;
command.Connection = conn;
command.ExecuteNonQuery();
return(true);
}
else
{
return(false);
}
}
public TheLoai LayTL(string Ma)
{
SqlParameter[] parm = new SqlParameter[]
{
new SqlParameter(PARM_MATHELOAI, SqlDbType.NVarChar, 30)
};
parm[0].Value = Ma;
SqlDataReader d = DALHelper.ExecuteReader(DALHelper.ConnectionString, CommandType.StoredProcedure, "tblTheLoai_Lay1", parm);
TheLoai dv = new TheLoai();
if (d.Read())
{
dv.MaTheLoai = d[0].ToString();
dv.TenTheLoai = d[1].ToString();
dv.MoTa = d[2].ToString();
}
return(dv);
}
public bool suaTheLoai(TheLoai theLoai)
{
if (checkMa(theLoai.MaTLoai))
{
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.Text;
command.CommandText = "update TheLoai5 set TenTLoai=@ten,GhiChu=@ghiChu where MaTLoai=@ma";
command.Parameters.Add("@ma", SqlDbType.NVarChar).Value = theLoai.MaTLoai;
command.Parameters.Add("@ten", SqlDbType.NVarChar).Value = theLoai.TenTLoai;
command.Parameters.Add("@ghiChu", SqlDbType.NVarChar).Value = theLoai.GhiChu;
command.Connection = conn;
command.ExecuteNonQuery();
return(true);
}
else
{
return(false);
}
}
public bool themTheloai(TheLoai tl)
{
if (checkMa(tl.MaTLoai))
{
return(false);
}
else
{
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.Text;
command.CommandText = "insert into TheLoai5 values(@ma, @ten,@ghiChu)";
command.Parameters.Add("@ma", SqlDbType.NVarChar).Value = tl.MaTLoai;
command.Parameters.Add("@ten", SqlDbType.NVarChar).Value = tl.TenTLoai;
command.Parameters.Add("@ghiChu", SqlDbType.NVarChar).Value = tl.GhiChu;
command.Connection = conn;
command.ExecuteNonQuery();
return(true);
}
}
public List <TheLoai> getListCategory()
{
List <TheLoai> list = new List <TheLoai>();
openConnection();
SqlCommand sqlCommand = truyVan1("select * from TheLoai5");
SqlDataReader rd = sqlCommand.ExecuteReader();
while (rd.Read())
{
TheLoai category = new TheLoai();
category.MaTLoai = rd.GetString(0);
category.TenTLoai = rd.GetString(1);
category.GhiChu = rd.GetString(2);
list.Add(category);
}
return(list);
}
public string suaTheLoai(TheLoai theLoai)
{
try
{
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.Text;
command.CommandText = "update TheLoai5 set TenTLoai=@ten,GhiChu=@ghiChu where MaTLoai=@ma";
command.Parameters.Add("@ma", SqlDbType.NVarChar).Value = theLoai.MaTLoai;
command.Parameters.Add("@ten", SqlDbType.NVarChar).Value = theLoai.TenTLoai;
command.Parameters.Add("@ghiChu", SqlDbType.NVarChar).Value = theLoai.GhiChu;
command.Connection = conn;
command.ExecuteNonQuery();
return("Sửa thành công !");
}
catch (Exception ex)
{
return("ERR: " + ex.Message);
}
}
public List <TheLoai> DSTL_Sach(string maSach)
{
SqlParameter[] parm = new SqlParameter[]
{
new SqlParameter(PARM_MASACH, SqlDbType.NVarChar, 30)
};
parm[0].Value = maSach;
SqlDataReader d = DALHelper.ExecuteReader(DALHelper.ConnectionString, CommandType.StoredProcedure, "tblTheLoai_LayTheoSach", parm);
List <TheLoai> mylist = new List <TheLoai>();
while (d.Read())
{
TheLoai lv = new TheLoai();
lv.MaTheLoai = d[0].ToString();
lv.TenTheLoai = d[1].ToString();
lv.MoTa = d[2].ToString();
mylist.Add(lv);
}
d.Dispose();
return(mylist);
}
public bool ThemTheLoai(TheLoaiDTO theLoaiDTO)
{
try
{
TheLoai theLoai = new TheLoai
{
MaTheLoai = theLoaiDTO.MaTheLoai,
TenTheLoai = theLoaiDTO.TenTheLoai,
TrangThai = true,
};
data.TheLoais.Add(theLoai);
data.SaveChanges();
return(true);
}
catch (Exception)
{
return(false);
}
}
public List <TheLoai> getListCategory(string ten)
{
List <TheLoai> list = new List <TheLoai>();
openConnection();
SqlCommand sqlCommand = truyVan1("select * from TheLoai5 WHERE TenTLoai like @ten");
sqlCommand.Parameters.Add("@ten", SqlDbType.NVarChar).Value = ten;
SqlDataReader rd = sqlCommand.ExecuteReader();
while (rd.Read())
{
TheLoai category = new TheLoai();
category.MaTLoai = rd.GetString(0);
category.TenTLoai = rd.GetString(1);
category.GhiChu = rd.GetString(2);
list.Add(category);
}
return(list);
}
public List <TheLoai> dsTheLoai()
{
openConnection();
List <TheLoai> dsTL = new List <TheLoai>();
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM TheLoai5";
command.Connection = conn;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
TheLoai tl = new TheLoai();
tl.MaTLoai = reader.GetString(0);
tl.TenTLoai = reader.GetString(1);
tl.GhiChu = reader.GetString(2);
dsTL.Add(tl);
}
reader.Close();
return(dsTL);
}
public List <TheLoai> LayDanhSachTheLoai()
{
List <TheLoai> list = new List <TheLoai>();
OpenConnection();
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.Text;
command.CommandText = "select * from TheLoai";
command.Connection = conn;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
TheLoai temp = new TheLoai();
temp.MaTheLoai = reader.GetInt32(0);
temp.TenTheLoai = reader.GetString(1);
list.Add(temp);
}
reader.Close();
return(list);
}
public List <TheLoai> dsTheLoaiTimKiem(string ma)
{
openConnection();
List <TheLoai> dsTL = new List <TheLoai>();
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM TheLoai5 where MaTLoai=@ma";
command.Parameters.Add("@ma", SqlDbType.NVarChar).Value = ma;
command.Connection = conn;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
TheLoai tl = new TheLoai();
tl.MaTLoai = reader.GetString(0);
tl.TenTLoai = reader.GetString(1);
tl.GhiChu = reader.GetString(2);
dsTL.Add(tl);
}
reader.Close();
return(dsTL);
}
public List <TheLoai> ThongKeTheLoai()
{
List <TheLoai> LS = new List <TheLoai>();
OpenConnection();
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.Text;
command.CommandText = "select * from ThongKeTheLoai";
command.Connection = conn;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
TheLoai temp = new TheLoai();
temp.TenTG = reader.GetString(0);
if (!reader.IsDBNull(1))
{
temp.SoDauSach = reader.GetInt32(1);
}
else
{
temp.SoDauSach = 0;
}
if (!reader.IsDBNull(2))
{
temp.SoCuonSach = reader.GetInt32(2);
}
else
{
temp.SoCuonSach = 0;
}
LS.Add(temp);
}
reader.Close();
return(LS);
}