public int CapNhatHocKi(QLHS_DTO hs)
{
int nRow = 0;
DBConnect provider = new DBConnect();
try
{
string strSql = "UPDATE HOCKI SET MaHocKi = @MaHocKi, TenHocKi = @TenHocKi";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@MaHocKi", Value = hs.MaHocKi
},
new SqlParameter {
ParameterName = "@TenHocKi", Value = hs.TenHocKi
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public void updateKhachHang(DTO_KhachHang kh)
{
try
{
SqlConnection con = DBConnect.Connect();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "suakhachhang";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter {
ParameterName = "makh", Value = kh.ID_KHACHHANG
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "tenkh", Value = kh.HOTEN
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "sdt", Value = kh.DIENTHOAI
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "email", Value = kh.EMAIL
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "loai", Value = kh.LOAI
});
cmd.ExecuteNonQuery();
DBConnect.Close(con);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public int CapNhatMonHoc(string mamon, string tenmon)
{
int nRow = 0;
DBConnect provider = new DBConnect();
try
{
string strSql = "UPDATE MONHOC SET MaMonHoc = @MaMonHoc, TenMonHoc = @TenMonHoc WHERE MaMonHoc = @MaMonHoc";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@MaMonHoc", Value = mamon
},
new SqlParameter {
ParameterName = "@TenMonHoc", Value = tenmon
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public int CapNhatLop(QLHS_DTO hs)
{
int nRow = 0;
DBConnect provider = new DBConnect();
try
{
string strSql = "UPDATE DANHSACHLOP SET MaLop = @MaLop, TenLop = @TenLop, SiSo = @SiSo, MaKhoiLop = @MaKhoiLop WHERE MaLop = @MaLop";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@MaLop", Value = hs.MaLop
},
new SqlParameter {
ParameterName = "@TenLop", Value = hs.TenLop
},
new SqlParameter {
ParameterName = "@SiSo", Value = hs.SiSo
},
new SqlParameter {
ParameterName = "@MaKhoiLop", Value = hs.MaKhoiLop
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public DataTable getTenTrambyTuyen()
{
//tạo kết nối mới
SqlConnection conn = DBConnect.Connect();
//tạo command(select *....)
SqlCommand cmd = conn.CreateCommand();
//khai bao se dung store cho cmd
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "getTenTrambyTuyen";
//thuc thi cmd
//khi dung datatable hoac dataset thi moi dung SQLdataAdapter
//cmd.ExecuteNonQuery();//thuc thi tra ve so dong bi anh huong
SqlDataAdapter da = new SqlDataAdapter(cmd);//thuc thi tra ve table
DataTable dt = new DataTable();
//toàn bộ dữ liệu trong da se đổ (Fill) vao dt(dang table)
da.Fill(dt);
//
DBConnect.Close(conn);
return(dt);
}
public bool kiemTraTenTramInTuyen(DTO_TuyenXe tx)
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "kiemTraTenTramInTuyen";
SqlParameter prm1 = new SqlParameter("@id_TramDi", tx.Tram_ID_Tram1);
SqlParameter prm2 = new SqlParameter("@id_TramDen", tx.Tram_ID_Tram);
cmd.Parameters.Add(prm1);
cmd.Parameters.Add(prm2);
SqlDataAdapter da = new SqlDataAdapter(cmd);//thuc thi tra ve table
DataTable dt = new DataTable();
da.Fill(dt);
DBConnect.Close(conn);
if (dt.Rows.Count > 0)
{
return(true);
}
return(false);
}
public int ThemCTDSLOP(QLHS_DTO hs)
{
int nRow = 0;
DBConnect provider = new DBConnect();
try
{
string strSql = "INSERT INTO CHITIETDSLOP(MaChiTietDSLop, MaHocSinh) VALUES(@MaHocSinh, @MaHocSinh)";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@MaHocSinh", Value = hs.MaHocSinh
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
//public DataTable updateTramDen(DTO_TuyenXe tx)
//{
// SqlConnection conn = DBConnect.Connect();
// SqlCommand cmd = conn.CreateCommand();
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.CommandText = "updateTramDen";
// SqlParameter prm1 = new SqlParameter("@id_Tuyen", tx.ID_Tuyen);
// SqlParameter prm2 = new SqlParameter("@id_TramDen", tx.Tram_ID_Tram);
// cmd.Parameters.Add(prm1);
// cmd.Parameters.Add(prm2);
// SqlDataAdapter da = new SqlDataAdapter(cmd);//thuc thi tra ve table
// DataTable dt = new DataTable();
// da.Fill(dt);
// DBConnect.Close(conn);
// return dt;
//}
public bool Insert(DTO_TuyenXe tx)
{
//tạo kết nối mới
SqlConnection conn = DBConnect.Connect();
//tạo command
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "newTuyen";
SqlParameter prm2 = new SqlParameter("@khoangCach", tx.KhoangCach);
SqlParameter prm3 = new SqlParameter("@thoigianChay", tx.ThoiGianChay);
SqlParameter prm4 = new SqlParameter("@tram_id_Tram1", tx.Tram_ID_Tram1);
SqlParameter prm5 = new SqlParameter("@tram_id_Tram2", tx.Tram_ID_Tram);
cmd.Parameters.Add(prm2);
cmd.Parameters.Add(prm3);
cmd.Parameters.Add(prm4);
cmd.Parameters.Add(prm5);
int row = cmd.ExecuteNonQuery();
DBConnect.Close(conn);
if (row > 0)
{
return(true);
}
return(false);
}
public int ThemLop(QLHS_DTO hs)
{
int nRow = 0;
DBConnect provider = new DBConnect();
try
{
string strSql = "INSERT INTO DANHSACHLOP(MaLop, TenLop, SiSo, MaKhoiLop) VALUES(@MaLop, @TenLop, @SiSo, @MaKhoiLop)";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@MaLop", Value = hs.MaLop
},
new SqlParameter {
ParameterName = "@TenLop", Value = hs.TenLop
},
new SqlParameter {
ParameterName = "@SiSo", Value = hs.SiSo
},
new SqlParameter {
ParameterName = "@MaKhoiLop", Value = hs.MaKhoiLop
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public int ThemHocKi(QLHS_DTO hs)
{
int nRow = 0;
DBConnect provider = new DBConnect();
try
{
string strSql = "INSERT INTO HOCKI(MaHocKi, TenHocKi) VALUES(@MaHocKi, @TenHocKi)";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@MaHocKi", Value = hs.MaHocKi
},
new SqlParameter {
ParameterName = "@TenHocKi", Value = hs.TenHocKi
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public int ThemKhoiLop(QLHS_DTO hs)
{
int nRow = 0;
DBConnect provider = new DBConnect();
if (Check_MaKL(hs.MaKhoiLop) == true)
{
try
{
string strSql = "INSERT INTO KHOILOP(MaKhoiLop, TenKhoiLop) VALUES(@MaKhoiLop, @TenKhoiLop)";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@MaKhoiLop", Value = hs.MaKhoiLop
},
new SqlParameter {
ParameterName = "@TenKhoiLop", Value = hs.TenKhoiLop
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
return(nRow);
}
public int CapNhatDiem(int d)
{
int nRow = 0;
DBConnect provider = new DBConnect();
try
{
string strSql = "UPDATE THAMSO SET DiemDatMon = @DiemDatMon";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@DiemDatMon", Value = d
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public int CapNhatSiSo(int ss)
{
int nRow = 0;
DBConnect provider = new DBConnect();
try
{
string strSql = "UPDATE THAMSO SET SiSoToiDa = @SiSo";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@SiSo", Value = ss
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public int CapNhatTuoi(int tuoinho, int tuoilon)
{
int nRow = 0;
DBConnect provider = new DBConnect();
try
{
string strSql = "UPDATE THAMSO SET TuoiToiThieu = @TuoiToiThieu, TuoiToiDa = @TuoiToiDa";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@TuoiToiThieu", Value = tuoinho
},
new SqlParameter {
ParameterName = "@TuoiToiDa", Value = tuoilon
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public int XoaHocSinh(string ma)
{
int nRow = 0;
DBConnect provider = new DBConnect();
try
{
string strSql = "DELETE FROM CHITIETDSLOP WHERE MaHocSinh = @MaHocSinh DELETE FROM HOCSINH WHERE MaHocSinh = @MaHocSinh";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@MaHocSinh", Value = ma
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public bool suaTram(DTO_Tram tram)
{
//tạo kết nối mới
SqlConnection conn = DBConnect.Connect();
//tạo command
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "suaTram";
SqlParameter prm1 = new SqlParameter("@id_Tram", tram.Id_Tram);
SqlParameter prm2 = new SqlParameter("@ten_Tram", tram.Ten_Tram);
SqlParameter prm3 = new SqlParameter("@dia_Diem", tram.Dia_Diem);
cmd.Parameters.Add(prm1);
cmd.Parameters.Add(prm2);
cmd.Parameters.Add(prm3);
int row = cmd.ExecuteNonQuery();
DBConnect.Close(conn);
if (row > 0)
{
return(true);
}
return(false);
}
public int XoaKhoiLop(string ma)
{
int nRow = 0;
DBConnect provider = new DBConnect();
try
{
string strSql = "DELETE FROM KHOILOP WHERE MaKhoiLop = @MaKhoiLop;";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@MaKhoiLop", Value = ma
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public bool Update(DTO_TuyenXe tx)
{
try
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "UpdateTuyenXe";
SqlParameter Param1 = new SqlParameter("@idTuyen", tx.ID_Tuyen);
SqlParameter Param2 = new SqlParameter("@khoangCach", tx.KhoangCach);
SqlParameter Param3 = new SqlParameter("@thoiGianChay", tx.ThoiGianChay);
SqlParameter Param4 = new SqlParameter("@tram_id_Tram1", tx.Tram_ID_Tram1);
SqlParameter Param5 = new SqlParameter("@tram_id_Tram2", tx.Tram_ID_Tram);
cmd.Parameters.Add(Param1);
cmd.Parameters.Add(Param2);
cmd.Parameters.Add(Param3);
cmd.Parameters.Add(Param4);
cmd.Parameters.Add(Param5);
cmd.ExecuteNonQuery();
DBConnect.Close(conn);
return(true);
}
catch
{
return(false);
}
}
public int XoaHocKi(QLHS_DTO ma)
{
int nRow = 0;
DBConnect provider = new DBConnect();
try
{
string strSql = "DELETE FROM HOCKI WHERE MaHocKi = @MaHocKi;";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@MaHocKi", Value = ma.MaHocKi
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public DataTable GetTuyenById(DTO_TuyenXe tx)
{
//tạo kết nối mới
SqlConnection conn = DBConnect.Connect();
//tạo command(select *....)
SqlCommand cmd = conn.CreateCommand();
//khai bao se dung store cho cmd
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetTuyenById";
SqlParameter prm1 = new SqlParameter("@idTuyen", tx.ID_Tuyen);
cmd.Parameters.Add(prm1);
//thuc thi cmd
//khi dung datatable hoac dataset thi moi dung SQLdataAdapter
//cmd.ExecuteNonQuery();//thuc thi tra ve so dong bi anh huong
SqlDataAdapter da = new SqlDataAdapter(cmd);//thuc thi tra ve table
DataTable dt = new DataTable();
//toàn bộ dữ liệu trong da se đổ (Fill) vao dt(dang table)
da.Fill(dt);
//
DBConnect.Close(conn);
return(dt);
}
public bool checkExistsTenTramInTram(DTO_Tram tr)
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "checkExistsTenTramInTram";
SqlParameter prm1 = new SqlParameter("@tenTram", tr.Ten_Tram);
SqlParameter prm2 = new SqlParameter("@diaDiem", tr.Dia_Diem);
cmd.Parameters.Add(prm1);
cmd.Parameters.Add(prm2);
SqlDataAdapter da = new SqlDataAdapter(cmd);//thuc thi tra ve table
DataTable dt = new DataTable();
da.Fill(dt);
DBConnect.Close(conn);
if (dt.Rows.Count > 0)
{
return(true);
}
return(false);
}
public List <int> getGheByChuyenAndXe(int id_chuyen, int id_xe)
{
try
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "getGheByChuyenAndXe";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter {
ParameterName = "id_chuyen", Value = id_chuyen
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "id_xe", Value = id_xe
});
SqlDataReader dr = cmd.ExecuteReader();
List <int> IDsGhe = new List <int>();
while (dr.Read())
{
IDsGhe.Add(Convert.ToInt32(dr["ID_Ghe"]));
}
return(IDsGhe);
} catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public int ThemDSLop(string malop, string mahs)
{
int nRow = 0;
DBConnect provider = new DBConnect();
try
{
string strSql = "EXEC USP_CHITIETDSLOP @Malop , @MaHS";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@Malop", Value = malop
},
new SqlParameter {
ParameterName = "@MaHS", Value = mahs
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public bool deleteVe(int idVe)
{
try
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "deleteVe";
cmd.Parameters.Add(new SqlParameter {
ParameterName = "id", Value = idVe
});
int row = Convert.ToInt32(cmd.ExecuteNonQuery());
DBConnect.Close(conn);
if (row >= 1)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable listChonGhe(int idTuyen, string ngayDi)
{
DataTable dt = new DataTable();
try
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "listChonGhe";
cmd.Parameters.Add(new SqlParameter {
ParameterName = "idTuyen", Value = idTuyen
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "ngayKH", Value = ngayDi
});
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
DBConnect.Close(conn);
return(dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable ListChuyenDK(int tuyen, int chuyen, string loai)
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "listChuyenDK";
cmd.Parameters.Add(new SqlParameter {
ParameterName = "Tuyen", Value = tuyen
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "Chuyen", Value = chuyen
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "Loai", Value = loai
});
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
DBConnect.Close(conn);
return(dt);
}
public DataTable ListXe()
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "listXe";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
DBConnect.Close(conn);
return(dt);
}
public DataTable getTuyenIdTuyenByTuyen()
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "getTuyenIDTuyenbyTuyen";
SqlDataAdapter da = new SqlDataAdapter(cmd);//thuc thi tra ve table
DataTable dt = new DataTable();
da.Fill(dt);
DBConnect.Close(conn);
return(dt);
}
public DataTable getXeById(int id)
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "getXeById";
cmd.Parameters.Add(new SqlParameter {
ParameterName = "id", Value = id
});
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
DBConnect.Close(conn);
return(dt);
}
public bool newVe(DatVe ve)
{
try
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "newVe";
cmd.Parameters.Add(new SqlParameter {
ParameterName = "id_ghe", Value = ve.IDGhe
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "id_chuyen", Value = ve.IDChuyen
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "tinhTrang", Value = ve.TinhTrang
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "giaTien", Value = ve.GiaTien
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "id_KhachHang", Value = ve.IDKhachHang
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "ngayXuatVe", Value = ve.NgayXuatVe
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "ghiChu", Value = ve.GhiChu
});
int newID = cmd.ExecuteNonQuery();
DBConnect.Close(conn);
if (newID > 0)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}