public Car Get(long id) { _connection = new SqlConnection(DalConfiguration.GetSqlConnectionString()); using (_connection) { _connection.Open(); string sql = "select * from car where id = @id"; SqlCommand cmd = new SqlCommand(sql, _connection); cmd.Parameters.AddWithValue("@id", id); var reader = cmd.ExecuteReader(); if (reader.Read()) { Car car = new Car() { Id = (long)reader["id"], ClientId = (long)reader["client_id"], Number = (string)reader["number"], Brand = (string)reader["brand"], Year = (Int16)reader["manufacturer_year"] }; return(car); } throw new Exception("Not found"); } }
public Contract Update(long contractId, DateTime startDate, DateTime endDate) { _connection = new SqlConnection(DalConfiguration.GetSqlConnectionString()); using (_connection) { string sql; SqlCommand cmd; _connection.Open(); if (endDate < DateTime.Parse("2000-01-01")) { sql = "update contract set end_date = null where id = @id"; cmd = new SqlCommand(sql, _connection); cmd.Parameters.AddWithValue("@id", contractId); } else { sql = "update contract set start_date = @startDate, end_date = @endDate where id = @id"; cmd = new SqlCommand(sql, _connection); cmd.Parameters.AddWithValue("@id", contractId); cmd.Parameters.AddWithValue("@startDate", startDate); cmd.Parameters.AddWithValue("@endDate", endDate); } cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); sql = "select * from contract where id = @id"; cmd = new SqlCommand(sql, _connection); cmd.Parameters.AddWithValue("@id", contractId); cmd.CommandType = CommandType.Text; var reader = cmd.ExecuteReader(); if (reader.Read()) { DateTime date = DateTime.MinValue; try { date = (DateTime)reader["end_date"]; } catch (Exception e) { } Contract contract = new Contract() { ContractId = (long)reader["id"], CarId = (long)reader["car_id"], StartDate = (DateTime)reader["start_date"], EndDate = date }; return(contract); } } throw new Exception("There is no contract with this id"); }
public int Delete(string phoneNumber) { _connection = new SqlConnection(DalConfiguration.GetSqlConnectionString()); using (_connection) { _connection.Open(); string sql = "delete from client where phone_number = @phoneNumber"; SqlCommand cmd = new SqlCommand(sql, _connection); cmd.Parameters.AddWithValue("@phoneNumber", phoneNumber); return(cmd.ExecuteNonQuery()); } }
public IEnumerable <ContractService> GetAllByContract(long contractId) { _connection = new SqlConnection(DalConfiguration.GetSqlConnectionString()); var result = new List <ContractService>(); using (_connection) { _connection.Open(); string sql = "select service_id, price, coefficient_id, coefficient, s.name as name, date_of_service from contract_services inner join coefficient c on contract_services.coefficient_id = c.id inner join service s on contract_services.service_id = s.id where contract_id = @contractId"; SqlCommand cmd = new SqlCommand(sql, _connection); cmd.Parameters.AddWithValue("@contractId", contractId); var reader = cmd.ExecuteReader(); while (reader.Read()) { DateTime d = DateTime.MinValue; try { d = (DateTime)reader["date_of_service"]; } catch (Exception e) { } ContractService service = new ContractService() { ServiceId = (long)reader["service_id"], Price = (double)reader["price"], CoefId = (long)reader["coefficient_id"], Date = d, Name = (string)reader["name"], Coef = (double)reader["coefficient"] }; result.Add(service); } return(result); } }
public IEnumerable <Contract> GetById(long id) { _connection = new SqlConnection(DalConfiguration.GetSqlConnectionString()); var result = new List <Contract>(); using (_connection) { _connection.Open(); string sql = "select * from contract where id = @id"; SqlCommand cmd = new SqlCommand(sql, _connection); cmd.Parameters.AddWithValue("@id", id); cmd.CommandType = CommandType.Text; var reader = cmd.ExecuteReader(); while (reader.Read()) { DateTime date = DateTime.MinValue; try { date = (DateTime)reader["end_date"]; } catch (Exception e) { } Contract contract = new Contract() { ContractId = (long)reader["id"], CarId = (long)reader["car_id"], StartDate = (DateTime)reader["start_date"], EndDate = date }; result.Add(contract); } } return(result.AsEnumerable()); }
public Client Create(string name, string lastName, string phoneNumber) { _connection = new SqlConnection(DalConfiguration.GetSqlConnectionString()); using (_connection) { _connection.Open(); string sql = "insert into client (name, last_name, phone_number) values (@name, @lastName, @phoneNumber)"; SqlCommand cmd = new SqlCommand(sql, _connection); cmd.Parameters.AddWithValue("@name", name); cmd.Parameters.AddWithValue("@lastName", lastName); cmd.Parameters.AddWithValue("@phoneNumber", phoneNumber); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); sql = "select * from client where phone_number = @phoneNumber"; cmd = new SqlCommand(sql, _connection); cmd.Parameters.AddWithValue("@phoneNumber", phoneNumber); var reader = cmd.ExecuteReader(); if (reader.Read()) { Client client = new Client() { Id = (long)reader["id"], Name = (string)reader["name"], LastName = (string)reader["last_name"], PhoneNumber = (string)reader["phone_number"], }; return(client); } throw new Exception("Phone number is already registered"); } //todo check exception }
public double GetContractPrice(long id) { _connection = new SqlConnection(DalConfiguration.GetSqlConnectionString()); double sum = 0; using (_connection) { _connection.Open(); SqlCommand cmd = new SqlCommand("ServicesSum", _connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ContractId", id); cmd.Parameters.AddWithValue("@Sum", sum).Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); sum = Convert.ToDouble(cmd.Parameters["@Sum"].Value); } return(sum); }
public EmployeeDao() { _connection = new SqlConnection(DalConfiguration.GetSqlConnectionString()); }
public ContractServicesDao() { _connection = new SqlConnection(DalConfiguration.GetSqlConnectionString()); }
public PartManufacturerDao() { _connection = new SqlConnection(DalConfiguration.GetSqlConnectionString()); }
public ServiceCoefficientDao() { _connection = new SqlConnection(DalConfiguration.GetSqlConnectionString()); }
public ClientDao() { _connection = new SqlConnection(DalConfiguration.GetSqlConnectionString()); }