public Mensalista getMensalistabyId_cliente(int id_cliente) { Mensalista returnMensalista = null; Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); SqlDataReader reader; string sql = "select * from Mensalistas m inner join Clientes c on m.Id_cliente = c.Id_Cliente where m.Id_cliente = " + id_cliente + " "; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); reader = command.ExecuteReader(); if (reader.Read()) { while (reader.Read()) { returnMensalista.id_mensalista = Int32.Parse(string.Format("{0}", reader[0])); returnMensalista.id_cliente = Int32.Parse(string.Format("{0}", reader[1])); returnMensalista.nome_cliente = string.Format("{0}", reader[4]); returnMensalista.data_termino = string.Format("{0}", reader[2]); } } return returnMensalista; }
public List<Cliente> buscar(String nome) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); SqlDataReader reader; string sql = "SELECT * FROM Clientes where Nome LIKE '%" + nome + "%'"; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); reader = command.ExecuteReader(); List<Cliente> listaClientes = new List<Cliente>(); while (reader.Read()) { Cliente cliente = new Cliente(); cliente.id_cliente = Int32.Parse(string.Format("{0}", reader[0])); cliente.nome = string.Format("{0}", reader[1]); cliente.cpf = string.Format("{0}", reader[2]); cliente.saldo = double.Parse(string.Format("{0}", reader[3])); listaClientes.Add(cliente); } con.closeConnection(); return listaClientes; }
public List<Mensalista> buscar(String nomeCliente) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); SqlDataReader reader; string sql = "SELECT * FROM Mensalistas m INNER JOIN Clientes c ON m.Id_cliente = c.Id_Cliente WHERE c.Nome like '%"+ nomeCliente +"%'"; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); reader = command.ExecuteReader(); List<Mensalista> listaMensalistas = new List<Mensalista>(); while (reader.Read()) { Mensalista mensalista = new Mensalista(); mensalista.id_mensalista = Int32.Parse(string.Format("{0}", reader[0])); mensalista.id_cliente = Int32.Parse(string.Format("{0}", reader[1])); Cliente cliente = new Cliente(); cliente.nome = string.Format("{0}", reader[4]); mensalista.nome_cliente = cliente.nome; mensalista.data_termino = string.Format("{0}", reader[2]); listaMensalistas.Add(mensalista); } con.closeConnection(); return listaMensalistas; }
public List<Carro> buscar(String nome) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); SqlDataReader reader; string sql = "SELECT * FROM Carro where modelo LIKE '%" + nome + "%' OR marca LIKE '%" + nome + "%'"; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); reader = command.ExecuteReader(); List<Carro> listaCarros = new List<Carro>(); while (reader.Read()) { Carro carro = new Carro(); carro.id_carro = Int32.Parse(string.Format("{0}", reader[0])); carro.modelo = string.Format("{0}", reader[1]); carro.marca = string.Format("{0}", reader[2]); carro.cor = string.Format("{0}", reader[3]); listaCarros.Add(carro); } con.closeConnection(); return listaCarros; }
public Boolean editar(Ticket ticket, Carro carro, Cliente cliente, Servicos servico) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); String sql = "UPDATE Tickets SET Id_cliente = " + cliente.id_cliente + ", id_Carro = " + carro.id_carro + ", Id_Servico = " + servico.id_servico + ", DataEntrada = '" + ticket.data_entrada + "', DataSaida = '" + ticket.data_saida + "', ValorTotal = " + ticket.valorTotal + ", Placa = '" + ticket.placa + "', UF = '" + ticket.uf + "', Cidade = '" + ticket.cidade + "'"; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); command.ExecuteNonQuery(); con.closeConnection(); return true; }
public Boolean editar(Mensalista mensalista) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); String sql = "UPDATE Mensalistas set Data_termino ='" + mensalista.data_termino +"' where Id_Mensalista = "+mensalista.id_mensalista; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); command.ExecuteNonQuery(); con.closeConnection(); return true; }
public Boolean editar(Carro carro) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); String sql = "UPDATE Carro set marca='" + carro.marca + "', modelo ='" + carro.modelo + "', cor = '" + carro.cor + "' where ID_carro = " + carro.id_carro; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); command.ExecuteNonQuery(); con.closeConnection(); return true; }
public Boolean debitarSaldo( Cliente cliente, Double valor) { Double saldo = (cliente.saldo - valor); Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); String sql = "UPDATE Clientes SET Saldo = " + saldo + " where Id_cliente = " + cliente.id_cliente; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); command.ExecuteNonQuery(); con.closeConnection(); return true; }
public Boolean deletar(Ticket ticket) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); SqlDataReader reader; string sql = "DELETE Tickets WHERE ID_ticket = " + ticket.id_ticket; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); reader = command.ExecuteReader(); con.closeConnection(); return true; }
public Boolean deletar(Mensalista mensalista) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); SqlDataReader reader; string sql = "DELETE Mensalistas WHERE Id_Mensalista = " + mensalista.id_mensalista; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); reader = command.ExecuteReader(); con.closeConnection(); return true; }
public Boolean deletar(Carro carro) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); SqlDataReader reader; string sql = "DELETE Carro WHERE ID_carro = " + carro.id_carro; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); reader = command.ExecuteReader(); con.closeConnection(); return true; }
public List<Ticket> buscar(String placa) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); SqlDataReader reader; string sql = "SELECT * FROM Tickets WHERE placa LIKE '%" + placa + "%' ORDER BY DataEntrada"; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); reader = command.ExecuteReader(); List<Ticket> listaTickets = new List<Ticket>(); while (reader.Read()) { Ticket ticket = new Ticket(); //Ticket ticket.id_ticket = int.Parse(string.Format("{0}", reader[0])); //Cliente ticket.id_cliente = int.Parse(string.Format("{0}", reader[1])); //Carro ticket.id_carro = Convert.ToInt32(string.Format("{0}", reader["id_Carro"])); //Servico ticket.id_servico = int.Parse(string.Format("{0}", reader["Id_Servico"])); //Ticket ticket.data_entrada = Convert.ToDateTime(reader["DataEntrada"]).ToString("dd/MM/yyyy HH:mm"); ticket.data_saida = Convert.ToDateTime(reader["DataSaida"]).ToString("dd/MM/yyyy HH:mm"); ticket.valorTotal = Convert.ToDouble(string.Format("{0}", reader[6])); //Placa carro ticket.placa = string.Format("{0}", reader[7]); ticket.uf = string.Format("{0}", reader[8]); ticket.cidade = string.Format("{0}", reader[9]); listaTickets.Add(ticket); } con.closeConnection(); return listaTickets; }
//delete public void Delete(Entities.Thue dvt1) { dvt = new Constants.ThueH(); Entities.Thue loaihanghoa = new Entities.Thue(); Sql = new Constants.Sql(); string sql = Sql.DeleteThue; Connection conn = new Connection(); SqlConnection cn = conn.openConnection(); SqlCommand cmd = new SqlCommand(sql, cn); cmd.Parameters.Add(dvt.HanhDong, SqlDbType.NVarChar, 20).Value = dvt1.HanhDong; cmd.Parameters.Add(dvt.ThueID, SqlDbType.Int).Value = dvt1.ThueID; cmd.ExecuteNonQuery(); cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); cn = null; conn = null; }
//delete public void Delete(Entities.LoaiHangHoa lhh1) { lhh = new Constants.LoaiHangHoa(); Entities.LoaiHangHoa loaihanghoa = new Entities.LoaiHangHoa(); Sql = new Constants.Sql(); string sql = Sql.DeleteLoaiHangHoa; Connection conn = new Connection(); SqlConnection cn = conn.openConnection(); SqlCommand cmd = new SqlCommand(sql, cn); cmd.Parameters.Add(lhh.HanhDong, SqlDbType.NVarChar, 20).Value = lhh1.HanhDong; cmd.Parameters.Add(lhh.LoaiHangID, SqlDbType.Int).Value = lhh1.LoaiHangID; cmd.ExecuteNonQuery(); cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); cn = null; conn = null; }
/// <summary> /// Insert Update Bảng /// </summary> public bool Insert(Entities.SoDuCongNo pxh) { try { bool kt = false; pt = new Constants.CongNo(); Sql = new Constants.Sql(); string sql = Sql.InsertSoDuCongNo; Connection conn = new Connection(); SqlConnection cn = conn.openConnection(); SqlCommand cmd = new SqlCommand(sql, cn); cmd.Parameters.Add(pt.MaSoDuCongNo, SqlDbType.VarChar, 20).Value = pxh.MaSoDuCongNo; cmd.Parameters.Add(pt.MaDoiTuong, SqlDbType.VarChar, 20).Value = pxh.MaDoiTuong; cmd.Parameters.Add(pt.TenDoiTuong, SqlDbType.NVarChar, 200).Value = pxh.TenDoiTuong; cmd.Parameters.Add(pt.DiaChi, SqlDbType.NVarChar, 200).Value = pxh.DiaChi; cmd.Parameters.Add(pt.SoDuDauKy, SqlDbType.Float).Value = pxh.SoDuDauKy; cmd.Parameters.Add(pt.NgayKetChuyen, SqlDbType.DateTime).Value = pxh.NgayKetChuyen; cmd.Parameters.Add(pt.SoDuCuoiKy, SqlDbType.Float).Value = pxh.SoDuCuoiKy; cmd.Parameters.Add(pt.LoaiDoiTuong, SqlDbType.Bit).Value = pxh.LoaiDoiTuong; cmd.Parameters.Add(pt.TrangThai, SqlDbType.Bit).Value = pxh.TrangThai; int i = cmd.ExecuteNonQuery(); if (i == 1) { kt = true; } else { kt = false; } cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); cn = null; conn = null; return(kt); } catch { return(false); } }
public int InsertUpdate(Entities.NhanVien nv1) { try { nv = new Constants.NhanVien(); Sql = new Constants.Sql(); string sql = Sql.InsertUpdateNhanVien; Connection conn = new Connection(); SqlConnection cn = conn.openConnection(); SqlCommand cmd = new SqlCommand(sql, cn); cmd.Parameters.Add(nv.HanhDong, SqlDbType.NVarChar, 20).Value = nv1.HanhDong; cmd.Parameters.Add(nv.NhanVienID, SqlDbType.Int).Value = nv1.NhanVienID; cmd.Parameters.Add(nv.MaNhanVien, SqlDbType.NVarChar, 20).Value = nv1.MaNhanVien; cmd.Parameters.Add(nv.TenNhanVien, SqlDbType.NVarChar, 200).Value = nv1.TenNhanVien; cmd.Parameters.Add(nv.MaPhongBan, SqlDbType.VarChar, 20).Value = nv1.MaPhongBan; cmd.Parameters.Add(nv.DCThuongTru, SqlDbType.NVarChar, 200).Value = nv1.DCThuongTru; cmd.Parameters.Add(nv.DCTamTru, SqlDbType.NVarChar, 200).Value = nv1.DCTamTru; cmd.Parameters.Add(nv.DienThoaiCD, SqlDbType.NVarChar, 20).Value = nv1.DienThoaiCD; cmd.Parameters.Add(nv.DienThoaiDD, SqlDbType.NVarChar, 20).Value = nv1.DienThoaiDD; cmd.Parameters.Add(nv.Email, SqlDbType.NVarChar, 20).Value = nv1.Email; cmd.Parameters.Add(nv.CMND, SqlDbType.NVarChar, 200).Value = nv1.CMND; cmd.Parameters.Add(nv.NgayCap, SqlDbType.DateTime).Value = nv1.NgayCap; cmd.Parameters.Add(nv.NoiCap, SqlDbType.NVarChar, 200).Value = nv1.NoiCap; cmd.Parameters.Add(nv.NgaySinh, SqlDbType.DateTime).Value = nv1.NgaySinh; cmd.Parameters.Add(nv.GhiChu, SqlDbType.NVarChar, 100).Value = nv1.GhiChu; cmd.Parameters.Add(nv.Deleted, SqlDbType.Bit).Value = nv1.Deleted; int i = cmd.ExecuteNonQuery(); cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); cn = null; conn = null; return(i); } catch (Exception ex) { string s = ex.Message; return(0); } }
//insert update public bool InsertUpdate(Entities.NhomHang nh) { bool kt = false; try { nhh = new Constants.NhomHangHoa(); Sql = new Constants.Sql(); string sql = Sql.InsertUpdateNhomHangHoa; Connection conn = new Connection(); SqlConnection cn = conn.openConnection(); SqlCommand cmd = new SqlCommand(sql, cn); cmd.Parameters.Add(nhh.HanhDong, SqlDbType.NVarChar, 20).Value = nh.HanhDong; cmd.Parameters.Add(nhh.NhomHangID, SqlDbType.Int).Value = nh.NhomHangID; cmd.Parameters.Add(nhh.MaNhomHang, SqlDbType.VarChar, 20).Value = nh.MaNhomHang; cmd.Parameters.Add(nhh.MaLoaiHang, SqlDbType.VarChar, 20).Value = nh.MaLoaiHang; cmd.Parameters.Add(nhh.TenNhomHang, SqlDbType.NVarChar, 200).Value = nh.TenNhomHang; cmd.Parameters.Add(nhh.GhiChu, SqlDbType.NVarChar, 100).Value = nh.GhiChu; cmd.Parameters.Add(nhh.Deleted, SqlDbType.Bit).Value = nh.Deleted; int i = cmd.ExecuteNonQuery(); if (i == 1) { kt = true; } else { kt = false; } cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); cn = null; conn = null; return(kt); } catch { return(kt); } }
/// <summary> /// vuong hung =================lay bang DonDatHang================ /// </summary> /// <returns></returns> public Entities.ThongTinNhanVien[] sp_LayBang_ThongTinNhanVien() { Entities.ThongTinNhanVien[] arrC = null; try { dh = new Constants.ThongTinNhanVien(); Sql = new Constants.Sql(); string sql = Sql.sp_LayBang_NhanViens; conn = new Connection(); cn = conn.openConnection(); cmd = new SqlCommand(sql, cn); dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); arr = new ArrayList(); while (dr.Read()) { nhanvien = new Entities.ThongTinNhanVien(); nhanvien.Manhanvien = dr[dh.MaNhanVien].ToString(); nhanvien.Tennhanvien = dr[dh.TenNhanVien].ToString(); arr.Add(nhanvien); } int n = arr.Count; if (n == 0) { return(null); } arrC = new Entities.ThongTinNhanVien[n]; for (int i = 0; i < n; i++) { arrC[i] = (Entities.ThongTinNhanVien)arr[i]; } } catch (Exception ex) { string s = ex.Message.ToString(); } finally { cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); } return(arrC); }
/// <summary> /// lay ban ghi kiem tra khi update /// </summary> /// <param name="truyen"></param> /// <returns></returns> public Entities.TraLaiNCC CheckTraLaiNhaCungCap(Entities.TruyenGiaTri truyen) { Entities.TraLaiNCC giatri = new Entities.TraLaiNCC(); try { string sql = "exec sp_LayBang_TheoTenBang @table,@values"; conn = new Connection(); cn = conn.openConnection(); cmd = new SqlCommand(sql, cn); cmd.Parameters.Add("table", SqlDbType.NVarChar, 100).Value = truyen.Giatritruyen; cmd.Parameters.Add("values", SqlDbType.NVarChar, 50).Value = truyen.Giatrithuhai; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (dr.Read()) { giatri.TraLaiNCCID = Convert.ToInt32(dr[0].ToString()); giatri.MaHDTraLaiNCC = dr[1].ToString(); giatri.Ngaytra = Convert.ToDateTime(dr[2].ToString()); giatri.MaNCC = dr[3].ToString(); giatri.NoHienThoi = dr[4].ToString(); giatri.NguoiNhanHang = dr[5].ToString(); giatri.HinhThucThanhToan = dr[6].ToString(); giatri.MaHoaDonNhap = dr[7].ToString(); giatri.MaKho = dr[8].ToString(); giatri.MaTienTe = dr[9].ToString(); giatri.TienBoiThuong = dr[10].ToString(); giatri.ThanhToanNgay = dr[11].ToString(); giatri.ThueGTGT = dr[12].ToString(); giatri.GhiChu = dr[13].ToString(); giatri.Deleted = Convert.ToBoolean(dr[14].ToString()); } } catch (Exception ex) { string s = ex.Message.ToString(); giatri = null; } finally { cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); } return(giatri); }
/// <summary> /// Insert Update Bảng /// </summary> public bool InsertUpdate(Entities.ChiTietXuatHuy ctxh1) { try { bool kt = false; ctxh = new Constants.ChiTietXuatHuy(); Sql = new Constants.Sql(); string sql = Sql.InsertUpdateChiTietXuatHuyMang; Connection conn = new Connection(); SqlConnection cn = conn.openConnection(); SqlCommand cmd = new SqlCommand(sql, cn); cmd.Parameters.Add(ctxh.HanhDong, SqlDbType.VarChar, 20).Value = ctxh1.HanhDong; cmd.Parameters.Add(ctxh.MaPhieuXuatHuy, SqlDbType.VarChar, 20).Value = ctxh1.MaPhieuXuatHuy; cmd.Parameters.Add(ctxh.MaHangHoa, SqlDbType.VarChar, 50).Value = ctxh1.MaHangHoa; cmd.Parameters.Add(ctxh.SoLuong, SqlDbType.Int, 20).Value = ctxh1.SoLuong; cmd.Parameters.Add(ctxh.GhiChu, SqlDbType.NVarChar, 20).Value = ctxh1.GhiChu; cmd.Parameters.Add(ctxh.Deleted, SqlDbType.Bit).Value = ctxh1.Deleted; int i = cmd.ExecuteNonQuery(); if (i == 1) { kt = true; } else { kt = false; } cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); cn = null; conn = null; return(kt); } catch (Exception ex) { string s = ex.Message; return(false); } }
/// <summary> /// Insert Update Bảng /// </summary> public bool Insert(Entities.SoDuKho pxh) { try { bool kt = false; sdk = new Constants.SoDuKho(); Sql = new Constants.Sql(); string sql = Sql.InsertUpdateSoDuKho; Connection conn = new Connection(); SqlConnection cn = conn.openConnection(); SqlCommand cmd = new SqlCommand(sql, cn); cmd.Parameters.Add(sdk.HanhDong, SqlDbType.VarChar, 20).Value = pxh.HanhDong; cmd.Parameters.Add(sdk.SoDuKhoID, SqlDbType.Int).Value = pxh.SoDuKhoID; cmd.Parameters.Add(sdk.MaSoDuKho, SqlDbType.VarChar, 20).Value = pxh.MaSoDuKho; cmd.Parameters.Add(sdk.MaKho, SqlDbType.VarChar, 20).Value = pxh.MaKho; cmd.Parameters.Add(sdk.MaHangHoa, SqlDbType.VarChar, 50).Value = pxh.MaHangHoa; cmd.Parameters.Add(sdk.SoDuDauKy, SqlDbType.Int).Value = pxh.SoDuDauKy; cmd.Parameters.Add(sdk.NgayKetChuyen, SqlDbType.DateTime).Value = pxh.NgayKetChuyen; cmd.Parameters.Add(sdk.SoDuCuoiKy, SqlDbType.Int).Value = pxh.SoDuCuoiKy; cmd.Parameters.Add(sdk.TrangThai, SqlDbType.Bit).Value = pxh.TrangThai; int i = cmd.ExecuteNonQuery(); if (i == 1) { kt = true; } else { kt = false; } cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); cn = null; conn = null; return(kt); } catch { return(false); } }
/// <summary> /// vuong hung =================xu ly don dat hang================ /// </summary> /// <returns></returns> public int sp_XuLy_KhachHangTraLai(Entities.KhachHangTraLai kkt) { int Trave = 0; try { khtl = new Constants.KhachHangTraLai(); Sql = new Constants.Sql(); string sql = Sql.sp_XuLy_KhachHangTraLai; conn = new Connection(); cn = conn.openConnection(); cmd = new SqlCommand(sql, cn); cmd.Parameters.Add(khtl.HanhDong, SqlDbType.NVarChar, 20).Value = kkt.Hanhdong; cmd.Parameters.Add("KhachHangTraLaiID", SqlDbType.Int).Value = kkt.KhachHangTraLaiID; cmd.Parameters.Add(khtl.MaKhachHangTraLai, SqlDbType.NVarChar, 20).Value = kkt.MaKhachHangTraLai; cmd.Parameters.Add(khtl.NgayNhap, SqlDbType.DateTime).Value = kkt.NgayNhap; cmd.Parameters.Add(khtl.MaKhachHang, SqlDbType.NVarChar, 20).Value = kkt.MaKhachHang; cmd.Parameters.Add(khtl.NoHienThoi, SqlDbType.Float).Value = kkt.NoHienThoi; cmd.Parameters.Add(khtl.NguoiTra, SqlDbType.NVarChar, 200).Value = kkt.NguoiTra; cmd.Parameters.Add(khtl.HinhThucThanhToan, SqlDbType.NVarChar, 200).Value = kkt.HinhThucThanhToan; cmd.Parameters.Add(khtl.HanThanhToan, SqlDbType.DateTime).Value = kkt.HanThanhToan; cmd.Parameters.Add(khtl.MaHoaDonMuaHang, SqlDbType.NVarChar, 20).Value = kkt.MaHoaDonMuaHang; cmd.Parameters.Add(khtl.MaKho, SqlDbType.NVarChar, 20).Value = kkt.MaKho; cmd.Parameters.Add(khtl.MaTienTe, SqlDbType.NVarChar, 20).Value = kkt.MaTienTe; cmd.Parameters.Add(khtl.TienBoiThuong, SqlDbType.Float).Value = kkt.TienBoiThuong; cmd.Parameters.Add(khtl.ThanhToanNgay, SqlDbType.Float).Value = kkt.ThanhToanNgay; cmd.Parameters.Add(khtl.ThueGTGT, SqlDbType.Float).Value = kkt.ThueGTGT; cmd.Parameters.Add(khtl.GhiChu, SqlDbType.NVarChar, 100).Value = kkt.GhiChu; cmd.Parameters.Add(khtl.Deleted, SqlDbType.Bit).Value = kkt.Deleted; Trave = cmd.ExecuteNonQuery(); } catch (Exception ex) { string s = ex.Message.ToString(); } finally { cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); } return(Trave); }
public bool updateTaiKhoan(Entities.TaiKhoan tk1) { try { bool kt = false; tk = new Constants.TaiKhoan(); Sql = new Constants.Sql(); string sql = Sql.updateTaiKhoan; Connection conn = new Connection(); SqlConnection cn = conn.openConnection(); SqlCommand cmd = new SqlCommand(sql, cn); cmd.Parameters.Add(tk.TenDangNhap, SqlDbType.VarChar, 200).Value = tk1.TenDangNhap; cmd.Parameters.Add(tk.MatKhauDangNhap, SqlDbType.VarChar, 200).Value = tk1.MatKhauDangNhap; cmd.Parameters.Add(tk.KhoaTaiKhoan, SqlDbType.Bit).Value = tk1.KhoaTaiKhoan; cmd.Parameters.Add(tk.NhanVienID, SqlDbType.VarChar, 20).Value = tk1.NhanVienID; cmd.Parameters.Add(tk.Administrator, SqlDbType.VarChar, 20).Value = tk1.Administrator; cmd.Parameters.Add(tk.TenNhomQuyen, SqlDbType.VarChar, 50).Value = tk1.TenNhomQuyen; int i = cmd.ExecuteNonQuery(); if (i >= 1) { kt = true; } else { kt = false; } cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); cn = null; conn = null; return(kt); } catch (Exception ex) { string s = ex.Message; return(false); } }
/// <summary> /// Select /// </summary> /// <returns></returns> public Entities.KhuyenMaiSoLuong[] Select(Entities.KhuyenMaiSoLuong item) { Entities.KhuyenMaiSoLuong[] list = null; try { string sql = "exec sp_SelectKMSLTheoMa @MaHangHoa"; conn = new Connection(); cn = conn.openConnection(); cmd = new SqlCommand(sql, cn); cmd.Parameters.Add("MaHangHoa", SqlDbType.VarChar, 50).Value = item.MaHangHoa; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); List <Entities.KhuyenMaiSoLuong> arr = new List <Entities.KhuyenMaiSoLuong>(); while (dr.Read()) { Entities.KhuyenMaiSoLuong row = new Entities.KhuyenMaiSoLuong(); row.Id = Convert.ToInt32(dr["Id"].ToString()); row.MaHangHoa = dr["MaHangHoa"].ToString(); row.TenHangHoa = dr["TenHangHoa"].ToString(); row.SoLuong = double.Parse(dr["SoLuong"].ToString()); row.NgayBatDau = DateTime.Parse(dr["NgayBatDau"].ToString()); row.NgayKetThuc = DateTime.Parse(dr["NgayKetThuc"].ToString()); row.GiaBanBuon = double.Parse(dr["GiaBanBuon"].ToString()); row.GiaBanLe = double.Parse(dr["GiaBanLe"].ToString()); arr.Add(row); } list = arr.ToArray(); } catch { list = null; } finally { cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); } return(list); }
public void Save(string idpatient, string iddoctor, string diagnose) { sqlCon = con.openConnection(); using (sqlCon) { sqlCon.Open(); string sql = "exec pcdTreatment @idpatient,@iddoctor,@diagnose"; sqlCom = new SqlCommand(sql, sqlCon); using (sqlCom) { sqlCom.Parameters.Add(new SqlParameter("@idpatient", idpatient)); sqlCom.Parameters.Add(new SqlParameter("@iddoctor", iddoctor)); sqlCom.Parameters.Add(new SqlParameter("@diagnose", diagnose)); sqlCom.ExecuteNonQuery(); } sqlCom.Clone(); } }
protected void Button1_Click(object sender, EventArgs e) { c.openConnection(); String str; str = ("insert into City(City_Name)values ('" + txtCityName.Text + "')"); int x = c.changeTable(str); if (x > 0) { Label2.Visible = true; Label2.Text = "City inserted successfully"; GridView1.DataBind(); Clear(); } else { Label2.Visible = true; Label2.Text = "City insert unsuccessful"; } }
public bool specialistAvailable(string specialist) { SqlDataReader sqlDr; SqlConnection sqlCon = con.openConnection(); using (sqlCon) { sqlCon.Open(); string query = "select count(Specialist) as Specialist from Doctor.Specialist where Specialist = '" + specialist + "'"; sqlCom = new SqlCommand(query, sqlCon); sqlDr = sqlCom.ExecuteReader(); while (sqlDr.Read()) { string specialistName = sqlDr["Specialist"].ToString(); if (specialistName == "0") { return(false); } } } sqlCon.Close(); return(true); }
/// <summary> /// lay so lupong mua theo hoa don ban hang /// </summary> /// <param name="giatri"></param> /// <returns></returns> public Entities.SoLuongTraLai[] sp_LaySoLuongDaMuaTheoHoaDonBanHang(Entities.TruyenGiaTri giatri) { Entities.SoLuongTraLai[] arrC = null; try { string sql = "exec sp_LaySoLuongDaMuaTheoHoaDonBanHang @MaHDBanHang"; conn = new Connection(); cn = conn.openConnection(); cmd = new SqlCommand(sql, cn); cmd.Parameters.Add("MaHDBanHang", SqlDbType.NVarChar, 20).Value = giatri.Giatritruyen; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); arr = new ArrayList(); while (dr.Read()) { Entities.SoLuongTraLai tao = new Entities.SoLuongTraLai(); tao.MaHangHoa = dr[0].ToString(); tao.SoLuong = int.Parse(dr[1].ToString()); arr.Add(tao); } int n = arr.Count; if (n == 0) { arrC = null; } arrC = new Entities.SoLuongTraLai[n]; for (int i = 0; i < n; i++) { arrC[i] = (Entities.SoLuongTraLai)arr[i]; } } catch (Exception ex) { string s = ex.Message.ToString(); arrC = null; } finally { cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); } return(arrC); }
/// <summary> /// vuong hung =================xu ly don dat hang================ /// </summary> /// <returns></returns> public int sp_XuLy_TraLaiNhaCungCap(Entities.TraLaiNCC ncc) { int Trave = 0; try { tlncc = new Constants.TraLaiNhaCungCap(); Sql = new Constants.Sql(); string sql = Sql.sp_XuLy_TraLaiNhaCungCap; conn = new Connection(); cn = conn.openConnection(); cmd = new SqlCommand(sql, cn); cmd.Parameters.Add(tlncc.HanhDong, SqlDbType.NVarChar, 20).Value = ncc.Hanhdong; cmd.Parameters.Add(tlncc.TraLaiNCCID, SqlDbType.Int).Value = ncc.TraLaiNCCID; cmd.Parameters.Add(tlncc.MaHDTraLaiNCC, SqlDbType.NVarChar, 20).Value = ncc.MaHDTraLaiNCC; cmd.Parameters.Add(tlncc.Ngaytra, SqlDbType.DateTime).Value = ncc.Ngaytra; cmd.Parameters.Add(tlncc.MaNCC, SqlDbType.NVarChar, 20).Value = ncc.MaNCC; cmd.Parameters.Add(tlncc.NoHienThoi, SqlDbType.Float).Value = ncc.NoHienThoi; cmd.Parameters.Add(tlncc.NguoiNhanHang, SqlDbType.NVarChar, 200).Value = ncc.NguoiNhanHang; cmd.Parameters.Add(tlncc.HinhThucThanhToan, SqlDbType.NVarChar, 200).Value = ncc.HinhThucThanhToan; cmd.Parameters.Add(tlncc.MaHoaDonNhap, SqlDbType.NVarChar, 20).Value = ncc.MaHoaDonNhap; cmd.Parameters.Add(tlncc.MaKho, SqlDbType.NVarChar, 20).Value = ncc.MaKho; cmd.Parameters.Add(tlncc.MaTienTe, SqlDbType.NVarChar, 20).Value = ncc.MaTienTe; cmd.Parameters.Add(tlncc.TienBoiThuong, SqlDbType.Float).Value = ncc.TienBoiThuong; cmd.Parameters.Add(tlncc.ThanhToanNgay, SqlDbType.Float).Value = ncc.ThanhToanNgay; cmd.Parameters.Add(tlncc.ThueGTGT, SqlDbType.Float).Value = ncc.ThueGTGT; cmd.Parameters.Add(tlncc.GhiChu, SqlDbType.NVarChar, 100).Value = ncc.GhiChu; cmd.Parameters.Add(tlncc.Deleted, SqlDbType.Bit).Value = ncc.Deleted; Trave = cmd.ExecuteNonQuery(); } catch (Exception ex) { string s = ex.Message.ToString(); } finally { cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); } return(Trave); }
public bool Insert(Entities.TheVip thevip) { bool trave = false; try { string sql = new Common.Constants.Sql().insertTheVip; conn = new Connection(); Constants.TheVip tv = new Constants.TheVip(); cn = conn.openConnection(); cmd = new SqlCommand(sql, cn); cmd.Parameters.Add(tv.MaKhachHang, SqlDbType.VarChar, 50).Value = thevip.MaKhachHang.ToUpper(); cmd.Parameters.Add(tv.MaThe, SqlDbType.VarChar, 50).Value = thevip.MaThe.ToUpper(); cmd.Parameters.Add(tv.GiaTriThe, SqlDbType.Float).Value = thevip.GiaTriThe; cmd.Parameters.Add(tv.GiaTriConLai, SqlDbType.Float).Value = thevip.GiaTriConLai; cmd.Parameters.Add(tv.GhiChu, SqlDbType.VarChar, 100).Value = thevip.GhiChu; cmd.Parameters.Add(tv.SoDiem, SqlDbType.VarChar, 100).Value = thevip.SoDiem; int i = cmd.ExecuteNonQuery(); if (i == 1) { trave = true; } cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); } catch (Exception ex) { string s = ex.Message.ToString(); trave = false; } finally { cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); } return(trave); }
public bool Restore(string duongdan) { try { try { SqlConnection.ClearAllPools(); string sql = "ALTER DATABASE SupermarketManagementDHT "; sql += "SET SINGLE_USER WITH ROLLBACK IMMEDIATE "; sql += " use master "; sql += "restore DATABASE SupermarketManagementDHT from DISK='" + duongdan + "' with replace"; sql += " ALTER DATABASE SupermarketManagementDHT "; sql += "SET MULTI_USER"; conn = new Connection(); cmd = new SqlCommand(); cmd.CommandText = sql; cmd.Connection = conn.openConnection(); cmd.ExecuteNonQuery(); cmd.Connection.Dispose(); cmd.Connection.Close(); cmd.Dispose(); } catch { return(false); } finally { cmd.Connection.Dispose(); cmd.Connection.Close(); cmd.Dispose(); conn.closeConnection(); } return(true); } catch { return(false); } }
private void doneBtn_Click(object sender, EventArgs e) { if (nameTxt.Text.Length < 1 || priceTxt.Text.Length < 1) { MessageBox.Show("All fields must be entered!", "Invalid Values", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } Connection connection = new Connection(); connection.openConnection(); OleDbCommand serviceCommand = new OleDbCommand(); serviceCommand.Connection = connection.connection; if (edit) { serviceCommand.CommandText = "UPDATE Services SET Service_Name = '" + nameTxt.Text + "', Price = " + priceTxt.Text + " WHERE ID = " + service.id; serviceCommand.ExecuteNonQuery(); service.name = nameTxt.Text; service.price = float.Parse(priceTxt.Text); } else { serviceCommand.CommandText = "INSERT INTO Services (Service_Name, Price) VALUES ('" + nameTxt.Text + "', " + priceTxt.Text + ")"; serviceCommand.ExecuteNonQuery(); serviceCommand.CommandText = "Select @@Identity"; string serviceID = serviceCommand.ExecuteScalar().ToString(); service = new CService(serviceID, nameTxt.Text, float.Parse(priceTxt.Text)); } connection.closeConnection(); DialogResult = System.Windows.Forms.DialogResult.OK; Close(); }
/// <summary> /// Insert Update Bảng /// </summary> public bool UpdateTrangThai(Entities.SoDuSoQuy pxh) { try { if (pxh.SoDuSoQuyID == 0) { Entities.SoDuSoQuy sdsq = new Entities.SoDuSoQuy("", pxh.MaSoDuSoQuy, "0", pxh.NgayKetChuyen, pxh.SoDuCuoiKy, true); return(Insert(sdsq)); } bool kt = false; pt = new Constants.SoQuy(); Sql = new Constants.Sql(); string sql = Sql.UpdateSoDuSoQuy; Connection conn = new Connection(); SqlConnection cn = conn.openConnection(); SqlCommand cmd = new SqlCommand(sql, cn); cmd.Parameters.Add(pt.SoDuSoQuyID, SqlDbType.Int).Value = pxh.SoDuSoQuyID; cmd.Parameters.Add(pt.SoDuCuoiKy, SqlDbType.Float).Value = pxh.SoDuDauKy; int i = cmd.ExecuteNonQuery(); if (i == 1) { kt = true; } else { kt = false; } cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); cn = null; conn = null; Insert(pxh); return(kt); } catch { return(false); } }
/// <summary> /// Select tat ca hang hoa trong kho /// </summary> /// <returns></returns> public Entities.ChiTietKhoHang[] SelectAll() { List <Entities.ChiTietKhoHang> arrC = new List <ChiTietKhoHang>(); try { string sql = ""; sql = "Select b.MaKho, b.MaHangHoa, a.TenHangHoa, b.NgayNhap,b.HanSuDung,b.SoLuong,b.GhiChu from HangHoa a join ChiTietKhoHang b on a.MaHangHoa=b.MaHangHoa where b.Deleted='false'"; conn = new Connection(); cn = conn.openConnection(); cmd = new SqlCommand(sql, cn); dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (dr.Read()) { Entities.ChiTietKhoHang ctkho = new Entities.ChiTietKhoHang(); ctkho.MaKho = dr["MaKho"].ToString(); ctkho.MaHangHoa = dr["MaHangHoa"].ToString(); ctkho.TenHangHoa = dr["TenHangHoa"].ToString(); ctkho.SoLuong = Convert.ToInt32(dr["SoLuong"].ToString()); ctkho.NgayNhap = DateTime.Parse(dr["NgayNhap"].ToString()); ctkho.HanSuDung = DateTime.Parse(dr["HanSuDung"].ToString()); ctkho.GhiChu = dr["GhiChu"].ToString(); arrC.Add(ctkho); } } catch (Exception ex) { string s = ex.Message.ToString(); } finally { cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); } return(arrC.ToArray()); }
//delete public int Delete(Entities.HangHoa hh1) { hh = new Constants.HangHoa(); Entities.HangHoa hanghoa = new Entities.HangHoa(); Sql = new Constants.Sql(); string sql = Sql.DeleteHangHoa; Connection conn = new Connection(); SqlConnection cn = conn.openConnection(); SqlCommand cmd = new SqlCommand(sql, cn); cmd.Parameters.Add(hh.HanhDong, SqlDbType.NVarChar, 20).Value = hh1.HanhDong; cmd.Parameters.Add(hh.HangHoaID, SqlDbType.Int).Value = hh1.HangHoaID; int i = cmd.ExecuteNonQuery(); cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); cn = null; conn = null; return(i); }
public void Delete(Entities.NhanVien nv1) { try { nv = new Constants.NhanVien(); Sql = new Constants.Sql(); string sql = Sql.DeleteNhanVien; Connection conn = new Connection(); SqlConnection cn = conn.openConnection(); SqlCommand cmd = new SqlCommand(sql, cn); cmd.Parameters.Add(nv.HanhDong, SqlDbType.NVarChar, 20).Value = nv1.HanhDong; cmd.Parameters.Add(nv.NhanVienID, SqlDbType.Int).Value = nv1.NhanVienID; cmd.ExecuteNonQuery(); cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); cn = null; conn = null; } catch (Exception ex) { string s = ex.Message; } }
/// <summary> /// /// </summary> /// <param name="gv"></param> /// <returns></returns> public Entities.GiaVonBanHang[] Select(Entities.GiaVonBanHang gv) { string sql = Common.GiaVonBanHang.Select; Connection conn = new Connection(); SqlConnection cn = conn.openConnection(); SqlCommand cmd = new SqlCommand(sql, cn); cmd.Parameters.Add(Common.GiaVonBanHang.HanhDong, SqlDbType.VarChar, 50).Value = gv.HanhDong; cmd.Parameters.Add(Common.GiaVonBanHang.MaHoaDon, SqlDbType.VarChar, 50).Value = gv.MaHoaDon; cmd.Parameters.Add(Common.GiaVonBanHang.MaHangHoa, SqlDbType.VarChar, 50).Value = gv.MaHangHoa; SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); //Insert Category into ArrayList List <Entities.GiaVonBanHang> arr = new List <Entities.GiaVonBanHang>(); while (dr.Read()) { Entities.GiaVonBanHang item = new Entities.GiaVonBanHang(); //item.HanhDong = dr[Common.GiaVonBanHang.HanhDong].ToString(); item.MaHoaDon = dr[Common.GiaVonBanHang.MaHoaDon].ToString(); item.MaHangHoa = dr[Common.GiaVonBanHang.MaHangHoa].ToString(); item.GiaVon = double.Parse(dr[Common.GiaVonBanHang.GiaVon].ToString()); arr.Add(item); } int n = arr.Count; if (n == 0) { return(null); } //Giai phong bo nho cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); cn = null; conn = null; return(arr.ToArray()); }
protected void btnsubmit_Click(object sender, EventArgs e) { c.openConnection(); String str; str = ("insert into State(State_Name)values('" + txtStateName.Text + "')"); int x = c.changeTable(str); if (x > 0) { Label1.Text = "State inserted successfully"; Label1.Visible = true; GridView1.DataBind(); clear(); } else { Label1.Text = "State insert unsuccessfully"; Label1.Visible = true; } c.closeConnection(); }
protected void btnSubmit_Click(object sender, EventArgs e) { if (txtUsername.Text == "Admin" && txtPassword.Text == "Admin") { Session["Admin"] = txtUsername.Text; Response.Redirect("~/Admin/AdminHome.aspx"); } else { c.openConnection(); SqlCommand cmd; cmd = new SqlCommand("select * from Register where Username='******' and Password='******'", sqlcon); //string str; //str = "select * from Register where Username='******' and Password='******'"; SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); try { da.Fill(dt); if (dt.Rows.Count > 0) { Session["Cust"] = txtUsername.Text; Session["Pass"] = txtUsername.Text; Response.Redirect("~/Customer/Home.aspx"); } else { Label1.Visible = true; Label1.Text = "Invalid Username"; } } catch (Exception ex) { ex.ToString(); Label1.Visible = true; Label1.Text = "unable to Connect Database"; } } }
public void Delete(Entities.PhieuDieuChuyenKhoNoiBo phieuDCK) { try { pdcknb = new Constants.PhieuDieuChuyenKhoNoiBo(); Sql = new Constants.Sql(); string sql = Sql.DeletePhieuDieuChuyenKhoNoiBo; Connection conn = new Connection(); SqlConnection cn = conn.openConnection(); SqlCommand cmd = new SqlCommand(sql, cn); cmd.Parameters.Add(pdcknb.HanhDong, SqlDbType.NVarChar, 20).Value = phieuDCK.HanhDong; cmd.Parameters.Add(pdcknb.PhieuDieuChuyenKhoID, SqlDbType.Int).Value = phieuDCK.PhieuDieuChuyenKhoID; cmd.ExecuteNonQuery(); cmd.Connection.Dispose(); cn.Close(); conn.closeConnection(); cn = null; conn = null; } catch (Exception ex) { string s = ex.Message; } }
protected void btnSubmit_Click(object sender, EventArgs e) { c.openConnection(); String str; str = ("UPDATE Register SET Password ='******' WHERE UserName='******' AND Password='******'"); int x = c.changeTable(str); if (x != 0) { Label3.Text = "Password changed successuful"; Label3.Visible = true; clear(); } else { Label3.Text = "Password not change"; clear(); } c.closeConnection(); }
//============Thêm Sửa Xóa=================================================== /// <summary> /// ThaoTac_GiaVon: /// - Select_GIAVON /// - Insert_GIAVON /// - Update_TheoMaKhoVaMaHangHoa_GIAVON /// - Delete_TheoMaKhoVaMaHangHoa_GIAVON /// </summary> /// <param name="thaotac"></param> /// <param name="set"></param> /// <param name="filter"></param> /// <returns></returns> public int ThaoTac_GiaVon(string thaotac, Entities.GiaVon set, Entities.GiaVon filter) { int bg = 0; try { con = new Connection(); cn = con.openConnection(); cmd = new SqlCommand("exec sp_GiaVon @ThaoTac,@MaKho,@MaHangHoa,@SoLuong,@Gia,@MaKhok,@MaHangHoak,@SoLuongk,@Giak", cn); cmd.Parameters.Add("ThaoTac", SqlDbType.NVarChar).Value = thaotac; //set cmd.Parameters.Add("MaKho", SqlDbType.NVarChar).Value = set.MaKho; cmd.Parameters.Add("MaHangHoa", SqlDbType.NVarChar).Value = set.MaHangHoa; cmd.Parameters.Add("SoLuong", SqlDbType.NVarChar).Value = set.SoLuong; cmd.Parameters.Add("Gia", SqlDbType.NVarChar).Value = set.Gia; //filter cmd.Parameters.Add("MaKhok", SqlDbType.NVarChar).Value = filter.MaKho; cmd.Parameters.Add("MaHangHoak", SqlDbType.NVarChar).Value = filter.MaHangHoa; cmd.Parameters.Add("SoLuongk", SqlDbType.NVarChar).Value = filter.SoLuong; cmd.Parameters.Add("Giak", SqlDbType.NVarChar).Value = filter.Gia; bg = cmd.ExecuteNonQuery(); } catch (Exception) { bg = 0; } finally { cmd.Connection.Dispose(); cn.Close(); con.closeConnection(); } if (bg == 0) { return(bg); } return(bg); }
public Carro getCarrobyId(Carro carro) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); SqlDataReader reader; string sql = "SELECT * FROM Carro where ID_carro =" + carro.id_carro; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); reader = command.ExecuteReader(); Carro returnCarro = new Carro(); while (reader.Read()) { returnCarro.id_carro = Int32.Parse(string.Format("{0}", reader[0])); returnCarro.modelo = string.Format("{0}", reader[1]); returnCarro.marca = string.Format("{0}", reader[2]); returnCarro.cor = string.Format("{0}", reader[3]); return returnCarro; } con.closeConnection(); return returnCarro; }
public List<Cliente> buscarTicket(Boolean mensalista,String busca) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); SqlDataReader reader; string sql = ""; if (mensalista == true) { sql = "SELECT C.Id_Cliente,C.Nome,C.Cpf,C.Saldo FROM Ticketcar.dbo.Clientes AS C LEFT JOIN Ticketcar.dbo.Mensalistas AS M ON C.Id_Cliente = M.Id_cliente WHERE M.Id_cliente IS NOT NULL AND DAY(M.Data_termino) = DAY(GETDATE()) AND C.Nome LIKE '%" + busca + "%'"; } else { sql = "SELECT C.Id_Cliente,C.Nome,C.Cpf,C.Saldo FROM Ticketcar.dbo.Clientes AS C LEFT JOIN Ticketcar.dbo.Mensalistas AS M ON C.Id_Cliente = M.Id_cliente WHERE M.Id_cliente IS NULL AND C.Nome LIKE '%" + busca+"%'"; } command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); reader = command.ExecuteReader(); List<Cliente> listaClientes = new List<Cliente>(); while (reader.Read()) { Cliente cliente = new Cliente(); cliente.id_cliente = Int32.Parse(string.Format("{0}", reader[0])); cliente.nome = string.Format("{0}", reader[1]); cliente.cpf = string.Format("{0}", reader[2]); cliente.saldo = double.Parse(string.Format("{0}", reader[3])); listaClientes.Add(cliente); } con.closeConnection(); return listaClientes; }
public List<Ticket> listaarCaixa() { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); SqlDataReader reader; string sql = "SELECT ID_ticket,Id_cliente,id_Carro,Id_Servico,DataEntrada,DataSaida,ValorTotal, Placa,UF,Cidade FROM Ticketcar.dbo.Tickets WHERE CAST(DataEntrada AS DATE) = CAST(GETDATE() AS DATE)"; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); reader = command.ExecuteReader(); List<Ticket> listaTickets = new List<Ticket>(); while (reader.Read()) { Ticket ticket = new Ticket(); //Ticket ticket.id_ticket = int.Parse(string.Format("{0}", reader[0])); //Cliente ticket.id_cliente = int.Parse(string.Format("{0}", reader[1])); //Carro ticket.id_carro = Convert.ToInt32(string.Format("{0}", reader["id_Carro"])); //Servico ticket.id_servico = int.Parse(string.Format("{0}", reader["Id_Servico"])); //Ticket ticket.data_entrada = Convert.ToDateTime(reader["DataEntrada"]).ToString("dd/MM/yyyy HH:mm"); ticket.data_saida = Convert.ToDateTime(reader["DataSaida"]).ToString("dd/MM/yyyy HH:mm"); ticket.valorTotal = Convert.ToDouble(string.Format("{0}", reader[6])); //Placa carro ticket.placa = string.Format("{0}", reader[7]); ticket.uf = string.Format("{0}", reader[8]); ticket.cidade = string.Format("{0}", reader[9]); listaTickets.Add(ticket); } con.closeConnection(); return listaTickets; }
public Boolean inserir(Carro carro) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); string sql = "INSERT INTO Carro (marca,modelo,cor) VALUES('" + carro.marca + "','" + carro.modelo + "','" + carro.cor + "')"; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); command.ExecuteNonQuery(); con.closeConnection(); return true; }
public Ticket getTicketbyId(Ticket ticket) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); SqlDataReader reader; string sql = "SELECT T.ID_ticket,Cl.Id_Cliente,Cl.Nome,Cl.Cpf,Ca.ID_carro,Ca.Marca,Ca.Modelo,T.Placa,T.UF,T.Cidade,S.ID_servico,S.Descricao,S.Preco,T.DataEntrada,T.DataSaida,T.ValorTotal FROM ticketcar.dbo.Tickets AS T INNER JOIN Clientes AS Cl ON T.Id_cliente = Cl.Id_Cliente INNER JOIN Carro AS Ca ON T.id_Carro = Ca.ID_carro INNER JOIN Servicos AS S ON T.Id_Servico = S.ID_servico WHERE T.ID_ticket =" + ticket.id_ticket; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); reader = command.ExecuteReader(); Ticket returnTicket = new Ticket(); while (reader.Read()) { //Ticket ticket.id_ticket = int.Parse(string.Format("{0}", reader[0])); //Cliente ticket.id_cliente = int.Parse(string.Format("{0}", reader[1])); //Carro ticket.id_carro = Convert.ToInt32(string.Format("{0}", reader["id_Carro"])); //Servico ticket.id_servico = int.Parse(string.Format("{0}", reader["Id_Servico"])); //Ticket ticket.data_entrada = Convert.ToDateTime(reader["DataEntrada"]).ToString("dd/MM/yyyy HH:mm"); ticket.data_saida = Convert.ToDateTime(reader["DataSaida"]).ToString("dd/MM/yyyy HH:mm"); ticket.valorTotal = Convert.ToDouble(string.Format("{0}", reader[6])); //Placa carro ticket.placa = string.Format("{0}", reader[7]); ticket.uf = string.Format("{0}", reader[8]); ticket.cidade = string.Format("{0}", reader[9]); return returnTicket; } con.closeConnection(); return returnTicket; }
public Boolean inserir(Mensalista mensalista) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); string sql = "INSERT INTO Mensalistas (Id_cliente, Data_termino) VALUES(" + mensalista.id_cliente + ",'" + mensalista.data_termino + "')"; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); command.ExecuteNonQuery(); con.closeConnection(); return true; }
public double getTotalCaixa() { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); SqlDataReader reader; string sql = "SELECT SUM(ValorTotal) AS TotalCaixa FROM Ticketcar.dbo.Tickets WHERE CAST(DataEntrada AS DATE) = CAST(GETDATE() AS DATE)"; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); reader = command.ExecuteReader(); Double total = 0; while (reader.Read()) { total = double.Parse(string.Format("{0}", reader["TotalCaixa"])); } return total; }
public Boolean editar(Cliente cliente) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); String sql = "UPDATE Clientes set Nome='" + cliente.nome + "', Cpf ='" + cliente.cpf + "', Saldo = " + cliente.saldo+ " where Id_cliente = " + cliente.id_cliente; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); command.ExecuteNonQuery(); con.closeConnection(); return true; }
public Boolean inserir(Cliente cliente) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); string sql = "INSERT INTO Clientes (Nome,Cpf,Saldo) VALUES('" + cliente.nome + "','" + cliente.cpf + "'," + cliente.saldo + ")"; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); command.ExecuteNonQuery(); con.closeConnection(); return true; }
public Cliente getClientebyId(Cliente cliente) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); SqlDataReader reader; string sql = "SELECT * FROM Clientes where Id_cliente =" + cliente.id_cliente; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); reader = command.ExecuteReader(); Cliente returnCliente = new Cliente(); while (reader.Read()) { returnCliente.id_cliente = Int32.Parse(string.Format("{0}", reader[0])); returnCliente.nome = string.Format("{0}", reader[1]); returnCliente.cpf = string.Format("{0}", reader[2]); returnCliente.saldo = double.Parse(string.Format("{0}", reader[3])); return returnCliente; } con.closeConnection(); return returnCliente; }
public Boolean inserir(Ticket ticket, Carro carro, Cliente cliente, Servicos servico) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); string sql = "INSERT INTO Tickets (Id_cliente,Id_carro,Id_Servico,DataEntrada,DataSaida,ValorTotal,Placa,UF,Cidade) VALUES(" + cliente.id_cliente + "," + carro.id_carro + "," + servico.id_servico + ",'" + ticket.data_entrada + "','" + ticket.data_saida + "'," + ticket.valorTotal + ",'" + ticket.placa + "','" + ticket.uf + "','" + ticket.cidade + "')"; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); command.ExecuteNonQuery(); con.closeConnection(); return true; }
public Boolean deletar(Cliente cliente) { Connection con = new Connection(); con.openConnection(); SqlCommand command = new SqlCommand(); SqlDataReader reader; string sql = "DELETE Clientes WHERE Id_cliente = " + cliente.id_cliente; command.CommandText = sql; command.CommandType = CommandType.Text; command.Connection = con.getConnection(); reader = command.ExecuteReader(); con.closeConnection(); return true; }