public List <NhaCungCap> layNhaCungCapTheoMa(string s)
{
List <NhaCungCap> arr = new List <NhaCungCap>();
KetNoiCSDL.moKetNoi();
string get = "select * from NhaCungCap where maNhaCungCap=@maNhaCungCap";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maNhaCungCap", s);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
NhaCungCap ncc = new NhaCungCap(dr["maNhaCungCap"] + "", dr["tenNhaCungCap"] + "", dr["diaChi"] + "", dr["soDienThoai"].ToString());
arr.Add(ncc);
}
KetNoiCSDL.dongKetNoi();
return(arr);
}
public bool CapNhat(NhaCungCap nhaCungCap)
{
try
{
var ketQua = (from item in ketNoi.NhaCungCaps where item.MaNhaCungCap == nhaCungCap.MaNhaCungCap select item).FirstOrDefault();
ketQua.MaNguyenLieu = nhaCungCap.MaNguyenLieu;
ketQua.GiaNhap = nhaCungCap.GiaNhap;
ketQua.TenNhaCungCap = nhaCungCap.TenNhaCungCap;
ketQua.DiaChiNhaCungCap = nhaCungCap.DiaChiNhaCungCap;
ketQua.DienThoaiNhaCungCap = nhaCungCap.DienThoaiNhaCungCap;
ketNoi.SubmitChanges();
return(true);
}
catch (Exception)
{
return(false);
}
}
public List <NhaCungCap> layNhaCungCapTheoMa(string maNhaCungCap)
{
List <NhaCungCap> arr = new List <NhaCungCap>();
Connect.openConnect();
string query = "select * from NhaCungCap where maNhaCungCap LIKE @maNhaCungCap + '%'";
SqlCommand cmd = new SqlCommand(query, Connect.connect);
cmd.Parameters.AddWithValue("maNhaCungCap", maNhaCungCap);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
NhaCungCap x = new NhaCungCap(int.Parse(dr["maNhaCungCap"] + ""), dr["tenNhaCungCap"] + "", dr["diaChi"] + "");
arr.Add(x);
}
Connect.closeConnect();
return(arr);
}
public List <NhaCungCap> danhSachMaNCC(string ma)
{
List <NhaCungCap> arr = new List <NhaCungCap>();
KetNoiCSDL.moKetNoi();
string get = "select NhaCungCap.maNhaCungCap, tenNhaCungCap from NhaCungCap";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("ma", ma);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
NhaCungCap ncc = new NhaCungCap(dr["maNhaCungCap"] + "", dr["tenNhaCungCap"] + "");
arr.Add(ncc);
}
arr.Count();
KetNoiCSDL.dongKetNoi();
return(arr);
}
// Tim Kiem Nha Cung Cap
public List <NhaCungCap> TimKiemNhaCungCap(NhaCungCap ncc)
{
try
{
OpenConnection();
List <NhaCungCap> ncclist = new List <NhaCungCap>();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "sp_TimKiemNhaCungCap";
sqlCommand.Connection = conn;
sqlCommand.Parameters.Add("@Search", SqlDbType.NVarChar).Value = ncc.Search;
SqlDataReader sqlReader = sqlCommand.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(sqlReader);
for (int i = 0; i < dt.Rows.Count; i++)
{
NhaCungCap nccs = new NhaCungCap();
nccs.MaCC = int.Parse(dt.Rows[i][0].ToString());
nccs.TenCC = dt.Rows[i][1].ToString();
nccs.DiaChi = dt.Rows[i][2].ToString();
nccs.Sdt = int.Parse(dt.Rows[i][3].ToString());
ncclist.Add(nccs);
}
sqlReader.Close();
return(ncclist);
}
catch
{
throw;
}
finally
{
CloseConnection();
}
}
public Boolean CapNhatNhaCungCap(NhaCungCap ncc)
{
OpenConn();
string sql = "update NhaCungCap set TenNCC = @tenncc,DiaChi=@diachi,Email=@email,SDT=@sdt where MaNCC = @mancc";
SqlCommand sqlComm = new SqlCommand(sql, conn);
sqlComm.Parameters.Add(new SqlParameter("@tenncc", SqlDbType.NVarChar)).Value = ncc.TenNCC;
sqlComm.Parameters.Add(new SqlParameter("@diachi", SqlDbType.NChar)).Value = ncc.DiaChi;
sqlComm.Parameters.Add(new SqlParameter("@email", SqlDbType.NChar)).Value = ncc.Email;
sqlComm.Parameters.Add(new SqlParameter("@sdt", SqlDbType.NChar)).Value = ncc.SDT;
sqlComm.Parameters.Add(new SqlParameter("@mancc", SqlDbType.NChar)).Value = ncc.MaNCC;
int kq = sqlComm.ExecuteNonQuery();
CloseConn();
if (kq != 0)
{
return(true);
}
return(false);
}
public Boolean ThemNhaCungCap(NhaCungCap ncc)
{
OpenConn();
string sql = "insert into NhaCungCap values(@mancc,@tenncc,@diachi,@email,@sdt)";
SqlCommand sqlComm = new SqlCommand(sql, conn);
sqlComm.Parameters.Add(new SqlParameter("@mancc", SqlDbType.NChar)).Value = ncc.MaNCC;
sqlComm.Parameters.Add(new SqlParameter("@tenncc", SqlDbType.NVarChar)).Value = ncc.TenNCC;
sqlComm.Parameters.Add(new SqlParameter("@diachi", SqlDbType.NVarChar)).Value = ncc.DiaChi;
sqlComm.Parameters.Add(new SqlParameter("@email", SqlDbType.NVarChar)).Value = ncc.Email;
sqlComm.Parameters.Add(new SqlParameter("@sdt", SqlDbType.NVarChar)).Value = ncc.SDT;
int kq = sqlComm.ExecuteNonQuery();
CloseConn();
if (kq != 0)
{
return(true);
}
return(false);
}
public List <NhaCungCap> GetAllListNhaCungCap()
{
try
{
OpenConnection();
List <NhaCungCap> ncclist = new List <NhaCungCap>();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = "SELECT *FROM dbo.NHACC";
sqlCommand.Connection = conn;
SqlDataReader sqlReader = sqlCommand.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(sqlReader);
for (int i = 0; i < dt.Rows.Count; i++)
{
NhaCungCap ncc = new NhaCungCap();
ncc.MaCC = int.Parse(dt.Rows[i][0].ToString());
ncc.TenCC = dt.Rows[i][1].ToString();
ncc.DiaChi = dt.Rows[i][2].ToString();
ncc.Sdt = int.Parse(dt.Rows[i][3].ToString());
ncclist.Add(ncc);
}
sqlReader.Close();
return(ncclist);
}
catch
{
throw;
}
finally
{
CloseConnection();
}
}
public List <NhaCungCap> LayNhaCungCap()
{
List <NhaCungCap> list = new List <NhaCungCap>();
OpenConn();
string sql = "select * from NhaCungCap";
SqlCommand sqlComm = new SqlCommand(sql, conn);
SqlDataReader sqlDr = sqlComm.ExecuteReader();
while (sqlDr.Read())
{
NhaCungCap ncc = new NhaCungCap();
ncc.MaNCC = sqlDr.GetString(0);
ncc.TenNCC = sqlDr.GetString(1);
ncc.DiaChi = sqlDr.GetString(2);
ncc.Email = sqlDr.GetString(3);
ncc.SDT = sqlDr.GetString(4);
list.Add(ncc);
}
sqlDr.Close();
CloseConn();
return(list);
}
public Boolean XoaSanPhamNhaCungCap(NhaCungCap ncc)
{
ChiTietHoaDonDAL cthdDAL = new ChiTietHoaDonDAL();
OpenConn();
string sqlLayMaSanPham = "select MaSanPham from SanPham where MaNCC = '" + ncc.MaNCC + "'";
SqlCommand sqlComm1 = new SqlCommand(sqlLayMaSanPham, conn);
SqlDataReader sqlDr = sqlComm1.ExecuteReader();
List <string> list = new List <string>();
while (sqlDr.Read())
{
list.Add(sqlDr.GetString(0));
}
foreach (string ele in list)
{
string sql2 = "delete ChiTietHoaDon where MaSanPham=@masp";
SqlCommand sqlComm2 = new SqlCommand(sql2, conn);
sqlComm2.Parameters.Add(new SqlParameter("@masp", SqlDbType.NChar)).Value = ele;
sqlComm2.ExecuteNonQuery();
}
string sql = "delete SanPham where MaNCC = @mancc";
SqlCommand sqlComm = new SqlCommand(sql, conn);
sqlComm.Parameters.Add(new SqlParameter("@mancc", SqlDbType.NChar)).Value = ncc.MaNCC;
int kq = sqlComm.ExecuteNonQuery();
CloseConn();
if (kq != 0)
{
return(true);
}
return(false);
}
// Loc Nha Cung Cap
public List <NhaCungCap> LocDanhSachNhaCungCap(NhaCungCap ncc)
{
try
{
OpenConnection();
List <NhaCungCap> ncclist = new List <NhaCungCap>();
SqlCommand sqlCommand = new SqlCommand();
SqlDataReader sqlReader = null;
DataTable dt = new DataTable();
if (ncc.Search == "Mã Cung Cấp")
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = "SELECT MACC FROM dbo.NHACC";
sqlCommand.Connection = conn;
sqlReader = sqlCommand.ExecuteReader();
dt.Load(sqlReader);
for (int i = 0; i < dt.Rows.Count; i++)
{
NhaCungCap nccs = new NhaCungCap();
nccs.MaCC = int.Parse(dt.Rows[i][0].ToString());
ncclist.Add(nccs);
}
}
else if (ncc.Search == "Tên Cung Cấp")
{
sqlCommand.CommandText = "SELECT TENCC FROM dbo.NHACC";
sqlCommand.Connection = conn;
sqlReader = sqlCommand.ExecuteReader();
dt.Load(sqlReader);
for (int i = 0; i < dt.Rows.Count; i++)
{
NhaCungCap nccs = new NhaCungCap();
nccs.TenCC = dt.Rows[i][0].ToString();
ncclist.Add(nccs);
}
}
else if (ncc.Search == "Số Điện Thoại")
{
sqlCommand.CommandText = "SELECT SDT FROM dbo.NHACC";
sqlCommand.Connection = conn;
sqlReader = sqlCommand.ExecuteReader();
dt.Load(sqlReader);
for (int i = 0; i < dt.Rows.Count; i++)
{
NhaCungCap nccs = new NhaCungCap();
nccs.Sdt = int.Parse(dt.Rows[i][0].ToString());
ncclist.Add(nccs);
}
}
sqlReader.Close();
return(ncclist);
}
catch
{
throw;
}
finally
{
CloseConnection();
}
}
// XÓA DỮ LIỆU
public void DeleteData(NhaCungCap ex)
{
KetNoi.ExecuteReader(@"DELETE FROM NhaCungCap Where MaNCC=N'" + ex.MaNCC + "'");
}
// SỬA DỮ LIỆU
public void EditData(NhaCungCap ex)
{
KetNoi.ExecuteReader(@"UPDATE NhaCungCap SET TenNCC =N'" + ex.TenNCC + "', DiaChi =N'" + ex.DiaChi +
"', DienThoai =N'" + ex.DienThoai + "',Email =N'" + ex.Email + "',TrangThai=N'" + ex.TrangThai + "' Where MaNCC=N'" + ex.MaNCC + "'");
}
// THÊM DỮ LIỆU
public void AddData(NhaCungCap ex)
{
KetNoi.ExecuteReader(@"INSERT INTO NhaCungCap(MaNCC, TenNCC, DiaChi, DienThoai, Email, TrangThai)
VALUES(N'" + ex.MaNCC + "',N'" + ex.TenNCC + "',N'" + ex.DiaChi +
"',N'" + ex.DienThoai + "',N'" + ex.Email + "',N'" + ex.TrangThai + "')");
}
