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 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 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 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 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 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 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 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 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 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 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 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);
}
}
public void deleteKhachHang(int id)
{
try
{
SqlConnection con = DBConnect.Connect();
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "xoakhachhang";
cmd.Parameters.Add(new SqlParameter {
ParameterName = "makh", Value = id
});
cmd.ExecuteNonQuery();
DBConnect.Close(con);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable listChuyen()
{
//tạo Sqlconnection để connect
SqlConnection conn = DBConnect.Connect();
//tạo cmd để chứ câu truy vấn vào csdl
SqlCommand cmd = conn.CreateCommand();
//cài đặt cmd để truy vấn các stored Procedure
cmd.CommandType = CommandType.StoredProcedure;
//Đưa tên Procedure vào cmd text
cmd.CommandText = "listChuyen";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
DBConnect.Close(conn);
return(dt);
}
public bool xoaTram(DTO_Tram tram)
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "xoaTram";
SqlParameter Param1 = new SqlParameter("@id_Tram", tram.Id_Tram);
cmd.Parameters.Add(Param1);
int row = cmd.ExecuteNonQuery();
DBConnect.Close(conn);
if (row > 0)
{
return(true);
}
return(false);
}
public bool InsertTramTG(DTO_TramTrungGian tramTG)
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "InsertTramTrungGian";
SqlParameter prm1 = new SqlParameter("@id_Tuyen", tramTG.TuyenIDTuyen);
SqlParameter prm2 = new SqlParameter("@id_Tram", tramTG.TramIDTram);
cmd.Parameters.Add(prm1);
cmd.Parameters.Add(prm2);
int row = cmd.ExecuteNonQuery();
DBConnect.Close(conn);
if (row > 0)
{
return(true);
}
return(false);
}
public DataTable getTenTrambyTuyen()
{
DataTable dt = new DataTable();
try
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "getTenTrambyTuyen";
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
DBConnect.Close(conn);
return(dt);
} catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable ListChuyenSearch(string tu, string den)
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "listChuyenSearch";
cmd.Parameters.Add(new SqlParameter {
ParameterName = "Tu", Value = tu
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "Den", Value = den
});
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
DBConnect.Close(conn);
return(dt);
}
public bool UpdateChuyen(DTO_Chuyen c)
{
try {
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "updateChuyen";
cmd.Parameters.Add(new SqlParameter {
ParameterName = "ID_Chuyen", Value = c.ID_Chuyen
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "Tuyen", Value = c.Tuyen_ID_Tuyen
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "GKH", Value = c.Gio_khoi_hanh
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "Ghichu", Value = c.Ghi_chu
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "Xe", Value = c.Xe_XeID
});
cmd.Parameters.Add(new SqlParameter {
ParameterName = "TaiXe", Value = c.Tai_xe_ID_TaiXe
});
int row = cmd.ExecuteNonQuery();
DBConnect.Close(conn);
if (row > 0)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public int insertKhachHang(DTO_KhachHang kh)
{
try
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "themkhachhang";
cmd.CommandType = CommandType.StoredProcedure;
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();
// return ID after inserted
int newID = Convert.ToInt32(cmd.ExecuteScalar());
DBConnect.Close(conn);
if (newID > 0)
{
return(newID);
}
return(0);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable timKhachHangTheoSDTVaTen(string ma)
{
try
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "timkhachhangtheotenhoacsdt";
cmd.Parameters.Add(new SqlParameter {
ParameterName = "ma", Value = ma
});
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
DBConnect.Close(conn);
return(dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public bool Delete(DTO_TuyenXe tx)
{
//tạo kết nối mới
SqlConnection conn = DBConnect.Connect();
//tạo command(select *....)
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "DeleteTuyenXe";
SqlParameter Param1 = new SqlParameter("@idTuyen", tx.ID_Tuyen);
cmd.Parameters.Add(Param1);
int row = cmd.ExecuteNonQuery();
DBConnect.Close(conn);
if (row > 0)
{
return(true);
}
return(false);
}
public DataTable ListKhachHangTheoTen(DTO_KhachHang khachhang)
{
try
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "timKhachHangTheoTen";
cmd.Parameters.Add(new SqlParameter {
ParameterName = "ten", Value = khachhang.HOTEN
});
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
DBConnect.Close(conn);
return(dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getGheById(int id)
{
DataTable dt = new DataTable();
try
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "getGheById";
cmd.Parameters.Add(new SqlParameter {
ParameterName = "id", Value = id
});
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
DBConnect.Close(conn);
return(dt);
} catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public bool DeleteChuyen(int ID_Chuyen)
{
try
{
SqlConnection conn = DBConnect.Connect();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "deleteChuyen";
cmd.Parameters.Add(new SqlParameter {
ParameterName = "ID_Chuyen", Value = ID_Chuyen
});
int row = cmd.ExecuteNonQuery();
DBConnect.Close(conn);
if (row > 0)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}