public IEnumerable <Receipt> GetAll()
{
var result = new List <Receipt>();
using (var connection = MssqlCon.GetDbConnection())
{
const string sql = "select id, id_bill, bill_date, amount, debt, balance from receipt";
var cmd = new SqlCommand(sql, connection);
connection.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var id = (int)reader["id"];
var idBill = (string)reader["id_bill"];
var billDate = (DateTime)reader["bill_date"];
var amount = (int)reader["amount"];
var debt = (int)reader["debt"];
var balance = (int)reader["balance"];
var temp = new Receipt(id, idBill, billDate, amount, debt, balance);
result.Add(temp);
}
}
return(result);
}
public IEnumerable <House> GetAll()
{
var result = new List <House>();
using (var connection = MssqlCon.GetDbConnection())
{
const string sql = "select id_address, count_podezd, count_floor, id_district, id_company from house";
var cmd = new SqlCommand(sql, connection);
connection.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var idAddress = (string)reader["id_address"];
var countPodezd = (int)reader["count_podezd"];
var countFLoor = (int)reader["count_floor"];
var idDistrict = (string)reader["id_district"];
var idCompany = (string)reader["id_company"];
var temp = new House(idAddress, countPodezd, countFLoor, idDistrict, idCompany);
result.Add(temp);
}
}
return(result);
}
public Jilez GetById(string id)
{
using (var connection = MssqlCon.GetDbConnection())
{
const string sql =
"select fsl, passport_id, number_flat, house_address from jilez where passport_id=@id";
var cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@id", id);
connection.Open();
var reader = cmd.ExecuteReader();
reader.Read();
var fsl = (string)reader["fsl"];
var passportId = (string)reader["passport_id"];
var numberFlat = (int)reader["number_flat"];
var houseAddress = (string)reader["house_address"];
return(new Jilez(passportId, fsl, numberFlat, houseAddress));
}
}
public int Delete(string id)
{
try
{
using (var connection = MssqlCon.GetDbConnection())
{
const string sql = "delete from house_counter where id = @id";
var cmd = new SqlCommand(sql, connection);
connection.Open();
cmd.Parameters.AddWithValue("@id", id);
return(cmd.ExecuteNonQuery());
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(-1);
}
}
public int Add(District district)
{
using (var connection = MssqlCon.GetDbConnection())
{
try
{
connection.Open();
const string sql = "INSERT INTO district Values (@name);";
var cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@name", district.IdName);
return(cmd.ExecuteNonQuery());
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
return(-1);
}
}
}
public House GetById(string address)
{
using (var connection = MssqlCon.GetDbConnection())
{
connection.Open();
const string sql = "select id_address, count_podezd, count_floor, id_district, id_company from house where id_address = @address";
var cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@address", address);
var reader = cmd.ExecuteReader();
reader.Read();
var idAddress = (string)reader["id_address"];
var countPodezd = (int)reader["count_podezd"];
var countFLoor = (int)reader["count_floor"];
var idDistrict = (string)reader["id_district"];
var idCompany = (string)reader["id_company"];
return(new House(idAddress, countPodezd, countFLoor, idDistrict, idCompany));
}
}
public int Add(TsjService T)
{
using (var connection = MssqlCon.GetDbConnection())
{
try
{
connection.Open();
const string sql = "INSERT INTO tsj_service Values (@id_name, @price);";
var cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@id_name", T.IdName);
cmd.Parameters.AddWithValue("@price", T.Price);
return(cmd.ExecuteNonQuery());
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
return(-1);
}
}
}
public IEnumerable <District> GetAll()
{
var result = new List <District>();
using (var connection = MssqlCon.GetDbConnection())
{
const string sql = "select id_name from district";
var cmd = new SqlCommand(sql, connection);
connection.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var temp = new District((string)reader["id_name"]);
result.Add(temp);
}
}
return(result);
}
public int Add(ListServices T)
{
using (var connection = MssqlCon.GetDbConnection())
{
try
{
connection.Open();
const string sql = "INSERT INTO list_services Values (@id_bill, @id_services, @amount);";
var cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@id_bill", T.IdBill);
cmd.Parameters.AddWithValue("@id_services", T.IdService);
cmd.Parameters.AddWithValue("@amount", T.Amount);
return(cmd.ExecuteNonQuery());
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
return(-1);
}
}
}
public int Add(Bill T)
{
using (var connection = MssqlCon.GetDbConnection())
{
try
{
connection.Open();
const string sql = "INSERT INTO bill Values (@id, @id_company, @id_jilez);";
var cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@id", T.Id);
cmd.Parameters.AddWithValue("@id_company", T.IdCompany);
cmd.Parameters.AddWithValue("@id_jilez", T.IdJilez);
return(cmd.ExecuteNonQuery());
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
return(-1);
}
}
}
public int Add(Jilez T)
{
using (var connection = MssqlCon.GetDbConnection())
{
try
{
connection.Open();
const string sql = "INSERT INTO jilez Values (@fsl, @passport_id, @number_flat, @house_address);";
var cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@fsl", T.Fsl);
cmd.Parameters.AddWithValue("@passport_id", T.PassportId);
cmd.Parameters.AddWithValue("@number_flat", T.NumberFlat);
cmd.Parameters.AddWithValue("@house_address", T.HouseAddress);
return(cmd.ExecuteNonQuery());
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
return(-1);
}
}
}
public int Add(HouseCounter T)
{
using (var connection = MssqlCon.GetDbConnection())
{
try
{
connection.Open();
const string sql = "INSERT INTO house_counter Values (@id_owner, @water, @gas, @electricity);";
var cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@id_owner", T.Id);
cmd.Parameters.AddWithValue("@water", T.Water);
cmd.Parameters.AddWithValue("@gas", T.Gas);
cmd.Parameters.AddWithValue("@electricity", T.Electricity);
return(cmd.ExecuteNonQuery());
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
return(-1);
}
}
}
public HouseCounter GetById(string id)
{
if (id == null)
{
throw new ArgumentNullException(nameof(id));
}
using (var connection = MssqlCon.GetDbConnection())
{
connection.Open();
const string sql = "select id, water, gas, electricity from house_counter where id = @id_param";
var cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@id_param", id);
var reader = cmd.ExecuteReader();
reader.Read();
var idOwner = (string)reader["id"];
var water = (int)reader["water"];
var gas = (int)reader["gas"];
var electricity = (int)reader["electricity"];
var result = new HouseCounter(idOwner, water, gas, electricity);
return(result);
}
}
public int Add(ManageCompany T)
{
using (var connection = MssqlCon.GetDbConnection())
{
try
{
connection.Open();
const string sql = "INSERT INTO manage_company Values (@id_name, @fsl_owner, @office_address, @count_house);";
var cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@id_name", T.IdName);
cmd.Parameters.AddWithValue("@fsl_owner", T.FslOwner);
cmd.Parameters.AddWithValue("@office_address", T.OfficeAddress);
cmd.Parameters.AddWithValue("@count_house", T.CountHouse);
return(cmd.ExecuteNonQuery());
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
return(-1);
}
}
}
public IEnumerable <TsjService> GetAll()
{
var result = new List <TsjService>();
using (var connection = MssqlCon.GetDbConnection())
{
const string sql = "select id_name, price from tsj_service";
var cmd = new SqlCommand(sql, connection);
connection.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var idName = (string)reader["id_name"];
var price = (int)reader["price"];
var temp = new TsjService(idName, price);
result.Add(temp);
}
}
return(result);
}
public int Add(Receipt T)
{
using (var connection = MssqlCon.GetDbConnection())
{
try
{
connection.Open();
const string sql = "INSERT INTO receipt Values (@id_bill, @bill_date, @amount, @debt, @balance);";
var cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@id_bill", T.IdBill);
cmd.Parameters.AddWithValue("@bill_date", T.BillDate);
cmd.Parameters.AddWithValue("@amount", T.Amount);
cmd.Parameters.AddWithValue("@debt", T.Debt);
cmd.Parameters.AddWithValue("@balance", T.Balance);
return(cmd.ExecuteNonQuery());
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
return(-1);
}
}
}
public IEnumerable <Bill> GetAll()
{
var result = new List <Bill>();
using (var connection = MssqlCon.GetDbConnection())
{
const string sql = "select id, id_company, id_jilez from bill";
var cmd = new SqlCommand(sql, connection);
connection.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var id = (string)reader["id"];
var idCompany = (string)reader["id_company"];
var idJilez = (string)reader["id_jilez"];
var temp = new Bill(id, idCompany, idJilez);
result.Add(temp);
}
}
return(result);
}
public IEnumerable <HouseCounter> GetAll()
{
var result = new List <HouseCounter>();
using (var connection = MssqlCon.GetDbConnection())
{
const string sql = "select id, water, gas, electricity from house_counter";
var cmd = new SqlCommand(sql, connection);
connection.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var idOwner = (string)reader["id"];
var water = (int)reader["water"];
var gas = (int)reader["gas"];
var electricity = (int)reader["electricity"];
var temp = new HouseCounter(idOwner, water, gas, electricity);
result.Add(temp);
}
}
return(result);
}
public IEnumerable <ManageCompany> GetAll()
{
var result = new List <ManageCompany>();
using (var connection = MssqlCon.GetDbConnection())
{
const string sql = "select id_name, fsl_owner, office_address, count_house from manage_company";
var cmd = new SqlCommand(sql, connection);
connection.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var idName = (string)reader["id_name"];
var fslOwner = (string)reader["fsl_owner"];
var officeAddress = (string)reader["office_address"];
var countHouse = (int)reader["count_house"];
var temp = new ManageCompany(idName, fslOwner, officeAddress, countHouse);
result.Add(temp);
}
}
return(result);
}
public IEnumerable <Jilez> GetAll()
{
var result = new List <Jilez>();
using (var connection = MssqlCon.GetDbConnection())
{
const string sql = "select fsl, passport_id, number_flat, house_address from jilez";
var cmd = new SqlCommand(sql, connection);
connection.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var fsl = (string)reader["fsl"];
var passportId = (string)reader["passport_id"];
var numberFlat = (int)reader["number_flat"];
var houseAddress = (string)reader["house_address"];
var temp = new Jilez(passportId, fsl, numberFlat, houseAddress);
result.Add(temp);
}
}
return(result);
}
public int Add(House T)
{
using (var connection = MssqlCon.GetDbConnection())
{
try
{
connection.Open();
const string sql =
"INSERT INTO house Values (@id_address, @count_podezd, @count_floor, @id_district, @id_company);";
var cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@id_address", T.IdAddress);
cmd.Parameters.AddWithValue("@count_podezd", T.CountPodezd);
cmd.Parameters.AddWithValue("@count_floor", T.CountFloor);
cmd.Parameters.AddWithValue("@id_district", T.IdDistrict);
cmd.Parameters.AddWithValue("@id_company", T.IdCompany);
return(cmd.ExecuteNonQuery());
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
return(-1);
}
}
}
public IEnumerable <ListServices> GetAll()
{
var result = new List <ListServices>();
using (var connection = MssqlCon.GetDbConnection())
{
const string sql = "select id, id_bill, id_service, amount from list_services";
var cmd = new SqlCommand(sql, connection);
connection.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var id = (int)reader["id"];
var idBill = (string)reader["id_bill"];
var idService = (string)reader["id_service"];
var amount = (int)reader["amount"];
var temp = new ListServices(id, idBill, idService, amount);
result.Add(temp);
}
}
return(result);
}
