/// <summary>
/// Добавление обычного счета
/// </summary>
/// <param name="item">Модель данных Personal</param>
internal static void AddPersonal(this Personal item)
{
try
{
string sql = "INSERT INTO Personal (Id, AccountNumber, Cash, ClientId, OpenDate, State, TypeId)" +
" VALUES (@Id, @AccountNumber, @Cash, @ClientId, @OpenDate, @State, @TypeId)";
ConnectionClient.ConOpen();
using (SqlCommand cmd = new SqlCommand(sql, ConnectionClient.connection))
{
cmd.Parameters.AddWithValue("@Id", item.Id);
cmd.Parameters.AddWithValue("@AccountNumber", item.AccountNumber);
cmd.Parameters.AddWithValue("@Cash", item.Cash);
cmd.Parameters.AddWithValue("@ClientId", item.ClientId);
cmd.Parameters.AddWithValue("@OpenDate", item.OpenDate);
cmd.Parameters.AddWithValue("@State", item.State);
cmd.Parameters.AddWithValue("@TypeId", item.TypeId);
cmd.ExecuteNonQuery();
ConnectionClient.ConClose();
}
}
catch (SqlException e)
{
errorMes.ErrorSQL("Personal new " + e.Message); ConnectionClient.ConClose();
}
catch (Exception e)
{
errorMes.ErrorSQL("Personal new " + e.Message); ConnectionClient.ConClose();
}
}
internal static void AddRepayment(this Repayment item)
{
try
{
string sql = "INSERT INTO Repayment (Id, CreditId, RepaymentDate, PlanningDate, Performed, Amount)" +
" VALUES ( @Id, @CreditId, @RepaymentDate, @PlanningDate, @Performed, @Amount)";
ConnectionClient.ConOpen();
using (SqlCommand cmd = new SqlCommand(sql, ConnectionClient.connection))
{
cmd.Parameters.AddWithValue("@Id", item.Id);
cmd.Parameters.AddWithValue("@CreditId", item.CreditId);
cmd.Parameters.AddWithValue("@RepaymentDate", item.RepaymentDate);
cmd.Parameters.AddWithValue("@PlanningDate", item.PlanningDate);
cmd.Parameters.AddWithValue("@Performed", item.Performed);
cmd.Parameters.AddWithValue("@Amount", item.Amount);
cmd.ExecuteNonQuery();
ConnectionClient.ConClose();
}
}
catch (SqlException e)
{
errorMes.ErrorSQL(e.Message);
ConnectionClient.ConClose();
}
catch (Exception e)
{
errorMes.ErrorSQL(e.Message);
ConnectionClient.ConClose();
}
}
/// <summary>
/// Загрузка дынных AccountType
/// </summary>
private static void LoadType()
{
try
{
string sql = "SELECT * FROM AccountType";
ConnectionClient.ConOpen();
using (SqlCommand commmand = new SqlCommand(sql, ConnectionClient.connection))
{
SqlDataReader reader = commmand.ExecuteReader();
while (reader.Read())
{
AccountType type = new AccountType((int)reader["Id"], (string)reader["Types"]);
Repository.GetTypes.Add(type);
}
}
ConnectionClient.ConClose();
}
catch (SqlException e)
{
errorMes.ErrorSQL(e.Message);
}
catch (Exception e)
{
errorMes.ErrorSQL(e.Message);
}
finally
{
ConnectionClient.ConClose();
}
}
/// <summary>
/// Добавление нового вклада
/// </summary>
/// <param name="item">Модель данных Deposit</param>
internal static void AddDeposit(this Deposit item)
{
try
{
string sql = "INSERT INTO Deposit (Id, AccountNumber, Cash, DepositAmount, ClientId, State, MonthsPeriod, Rate, OpenDate, DateClose, Capitalization, TypeId, MoneyEarned)" +
" VALUES (@Id, @AccountNumber, @Cash, @DepositAmount, @ClientId, @State, @MonthsPeriod, @Rate, @OpenDate, @DateClose, @Capitalization, @TypeId, @MoneyEarned)";
ConnectionClient.ConOpen();
using (SqlCommand cmd = new SqlCommand(sql, ConnectionClient.connection))
{
cmd.Parameters.AddWithValue("@Id", item.Id);
cmd.Parameters.AddWithValue("@AccountNumber", item.AccountNumber);
cmd.Parameters.AddWithValue("@Cash", item.Cash);
cmd.Parameters.AddWithValue("@DepositAmount", item.DepositAmount);
cmd.Parameters.AddWithValue("@ClientId", item.ClientId);
cmd.Parameters.AddWithValue("@State", item.State);
cmd.Parameters.AddWithValue("@MonthsPeriod", item.MonthsPeriod);
cmd.Parameters.AddWithValue("@Rate", item.Rate);
cmd.Parameters.AddWithValue("@OpenDate", item.OpenDate);
cmd.Parameters.AddWithValue("@DateClose", item.DateClose);
cmd.Parameters.AddWithValue("@Capitalization", item.Capitalization);
cmd.Parameters.AddWithValue("@TypeId", item.TypeId);
cmd.Parameters.AddWithValue("@MoneyEarned", item.MoneyEarned);
cmd.ExecuteNonQuery();
}
ConnectionClient.ConClose();
}
catch (SqlException e)
{
errorMes.ErrorSQL("Deposit new " + e.Message); ConnectionClient.ConClose();
}
catch (Exception e)
{
errorMes.ErrorSQL(e.Message); ConnectionClient.ConClose();
}
}
/// <summary>
/// Обновление данных
/// </summary>
/// <param name="item"></param>
internal static void EditRepayment(this Repayment item)
{
try
{
string sql = "UPDATE Repayment SET CreditId = @CreditId , RepaymentDate = @RepaymentDate, PlanningDate = @PlanningDate, Performed = @Performed, Amount = @Amount WHERE Id = @Id";
ConnectionClient.ConOpen();
using (SqlCommand cmd = new SqlCommand(sql, ConnectionClient.connection))
{
cmd.Parameters.AddWithValue("@Id", item.Id);
cmd.Parameters.AddWithValue("@CreditId", item.CreditId);
cmd.Parameters.AddWithValue("@RepaymentDate", item.RepaymentDate);
cmd.Parameters.AddWithValue("@PlanningDate", item.PlanningDate);
cmd.Parameters.AddWithValue("@Performed", item.Performed);
cmd.Parameters.AddWithValue("@Amount", item.Amount);
cmd.ExecuteNonQuery();
ConnectionClient.ConClose();
}
}
catch (SqlException e)
{
errorMes.ErrorSQL(e.Message);
}catch (Exception e)
{
errorMes.ErrorSQL(e.Message);
}
finally
{
ConnectionClient.ConClose();
}
}
/// <summary>
/// Обновление данных по кредитам
/// </summary>
/// <param name="item">Обновленная модель данных</param>
internal static void EditCredits(this Credits item)
{
try
{
string sql = "UPDATE Credits SET RepayALoan = @RepayALoan, Cash = @Cash, NextDate = @NextDate WHERE Id = @Id";
ConnectionClient.ConOpen();
using (SqlCommand cmd = new SqlCommand(sql, ConnectionClient.connection))
{
cmd.Parameters.AddWithValue("@Id", item.Id);
cmd.Parameters.AddWithValue("@Cash", item.Cash);
cmd.Parameters.AddWithValue("@RepayALoan", item.RepayALoan);
cmd.Parameters.AddWithValue("@NextDate", item.NextDate);
cmd.ExecuteNonQuery();
ConnectionClient.ConClose();
}
}
catch (SqlException e)
{
errorMes.ErrorSQL(e.Message);
}
catch (Exception e)
{
errorMes.ErrorSQL(e.Message);
}
finally
{
ConnectionClient.ConClose();
}
}
/// <summary>
/// Добавление новой записи Busines в базу
/// </summary>
/// <param name="item">Модель данных</param>
internal static void AddBusines(Busines item)
{
try
{
string sql = "INSERT INTO Busines (Id, OrganizationName, INNCode, StatusId, CityId, FIO, PositionName)" +
" VALUES (@Id, @OrganizationName, @INNCode, @StatusId, @CityId, @FIO, @PositionName)";
ConnectionClient.ConOpen();
using (SqlCommand cmd = new SqlCommand(sql, ConnectionClient.connection))
{
cmd.Parameters.AddWithValue("@Id", item.Id);
cmd.Parameters.AddWithValue("@OrganizationName", item.OrganizationName);
cmd.Parameters.AddWithValue("@INNCode", item.INNCode);
cmd.Parameters.AddWithValue("@StatusId", item.StatusId);
cmd.Parameters.AddWithValue("@CityId", item.CityId);
cmd.Parameters.AddWithValue("@FIO", item.FIO);
cmd.Parameters.AddWithValue("@PositionName", item.PositionName);
cmd.ExecuteNonQuery();
ConnectionClient.connection.Close();
}
}
catch (SqlException e) { errorMes.ErrorSQL(e.Message); }
catch (Exception e) { errorMes.ErrorSQL(e.Message); }
finally { ConnectionClient.ConClose(); }
}
/// <summary>
/// Обновление данных по счетам
/// </summary>
/// <param name="item">Обновленная модель данных</param>
internal static void EditPersonal(this Personal item)
{
try
{
string sql = "UPDATE Personal SET Cash = @Cash WHERE Id = @Id";
ConnectionClient.ConOpen();
using (SqlCommand cmd = new SqlCommand(sql, ConnectionClient.connection))
{
cmd.Parameters.AddWithValue("@Id", item.Id);
cmd.Parameters.AddWithValue("@Cash", item.Cash);
cmd.ExecuteNonQuery();
ConnectionClient.ConClose();
}
}
catch (SqlException e)
{
errorMes.ErrorSQL(e.Message);
}
catch (Exception e)
{
errorMes.ErrorSQL(e.Message);
}
finally
{
ConnectionClient.ConClose();
}
}
internal static void LoadRepayment()
{
try
{
string sql = "SELECT * FROM Repayment";
lock (o)
{
ConnectionClient.ConOpen();
using (SqlCommand commmand = new SqlCommand(sql, ConnectionClient.connection))
{
SqlDataReader reader = commmand.ExecuteReader();
while (reader.Read())
{
Repayment repay = new Repayment((int)reader["Id"], (int)reader["CreditId"], (DateTime)reader["RepaymentDate"], (DateTime)reader["PlanningDate"],
(bool)reader["Performed"], (decimal)reader["Amount"]);
CreditRepository.RepaymentsList.Add(repay);
}
}
ConnectionClient.ConClose();
}
}
catch (SqlException e)
{
errorMes.ErrorSQL(e.Message);
}
catch (Exception e)
{
errorMes.ErrorSQL(e.Message);
}
finally { ConnectionClient.ConClose(); }
}
/// <summary>
/// Добавление истории
/// </summary>
/// <param name="item"></param>
internal static void AddHistory(this OperationHistory item)
{
try
{
string sql = "INSERT INTO OperationHistory ( OperationId, Money, OperationDate, AccountNumber)" +
" VALUES ( @OperationId, @Money, @OperationDate, @AccountNumber)";
ConnectionClient.ConOpen();
using (SqlCommand cmd = new SqlCommand(sql, ConnectionClient.connection))
{
cmd.Parameters.AddWithValue("@OperationId", item.OperationId);
cmd.Parameters.AddWithValue("@Money", item.Money);
cmd.Parameters.AddWithValue("@OperationDate", item.OperationDate);
cmd.Parameters.AddWithValue("@AccountNumber", item.AccountNumber);
cmd.ExecuteNonQuery();
ConnectionClient.ConClose();
}
}
catch (SqlException e)
{
errorMes.ErrorSQL(e.Message); ConnectionClient.ConClose();
}
catch (Exception e)
{
errorMes.ErrorSQL(e.Message); ConnectionClient.ConClose();
}
}
/// <summary>
/// Загрузка истории
/// </summary>
internal static void LoadHistory()
{
try
{
string sql = "SELECT * FROM OperationHistory";
lock (o)
{
ConnectionClient.ConOpen();
using (SqlCommand commmand = new SqlCommand(sql, ConnectionClient.connection))
{
SqlDataReader reader = commmand.ExecuteReader();
while (reader.Read())
{
OperationHistory oper = new OperationHistory((int)reader["Id"], (int)reader["OperationId"], (decimal)reader["Money"], (DateTime)reader["OperationDate"], (long)reader["AccountNumber"]);
HIstoryRepository.OperationHistories.Add(oper);
}
}
ConnectionClient.ConClose();
}
}
catch (SqlException e)
{
errorMes.ErrorSQL(e.Message); ConnectionClient.ConClose();
}
catch (Exception e)
{
errorMes.ErrorSQL(e.Message); ConnectionClient.ConClose();
}
finally { ConnectionClient.ConClose(); }
}
/// <summary>
/// Загрузка списка операций
/// </summary>
internal static void LoadOperation()
{
try
{
string sql = "SELECT * FROM Operations";
lock (o)
{
ConnectionClient.ConOpen();
using (SqlCommand commmand = new SqlCommand(sql, ConnectionClient.connection))
{
SqlDataReader reader = commmand.ExecuteReader();
while (reader.Read())
{
Operations oper = new Operations((int)reader["Id"], (string)reader["OperationName"]);
HIstoryRepository.OperationsList.Add(oper);
}
}
ConnectionClient.ConClose();
}
}
catch (SqlException e)
{
errorMes.ErrorSQL(e.Message); ConnectionClient.ConClose();
}
catch (Exception e)
{
errorMes.ErrorSQL(e.Message); ConnectionClient.ConClose();
}
finally { ConnectionClient.ConClose(); }
}
/// <summary>
/// Обновление данных по вкладам
/// </summary>
/// <param name="item">Обновленная модель данных</param>
internal static void EditDeposit(this Deposit item)
{
try
{
string sql = "UPDATE Deposit SET DepositAmount = @DepositAmount, Cash = @Cash, MoneyEarned = @MoneyEarned, DateClose = @DateClose WHERE Id = @Id";
ConnectionClient.ConOpen();
using (SqlCommand cmd = new SqlCommand(sql, ConnectionClient.connection))
{
cmd.Parameters.AddWithValue("@Id", item.Id);
cmd.Parameters.AddWithValue("@DepositAmount", item.DepositAmount);
cmd.Parameters.AddWithValue("@Cash", item.Cash);
cmd.Parameters.AddWithValue("@MoneyEarned", item.MoneyEarned);
cmd.Parameters.AddWithValue("@DateClose", item.DateClose);
cmd.ExecuteNonQuery();
ConnectionClient.ConClose();
}
}
catch (SqlException e)
{
errorMes.ErrorSQL(e.Message);
}
catch (Exception e)
{
errorMes.ErrorSQL(e.Message);
}
finally
{
ConnectionClient.ConClose();
}
}
/// <summary>
/// Удалить запись физ.лиза
/// </summary>
/// <param name="Id">ИД Клиента</param>
internal static void DeletePhysical(int Id)
{
try
{
string sql = string.Format($"DELETE FROM PhysicalClient WHERE Id = { Id}");
ConnectionClient.ConOpen();
using (SqlCommand cmd = new SqlCommand(sql, ConnectionClient.connection))
{
cmd.ExecuteNonQuery();
ConnectionClient.ConClose();
}
}
catch (SqlException e) { errorMes.ErrorSQL(e.Message); }
catch (Exception e) { errorMes.ErrorSQL(e.Message); }
finally { ConnectionClient.ConClose(); }
}
/// <summary>
/// Загрузка данных по всем кредитам
/// </summary>
private static void LoadCredit()
{
try
{
string sql = "SELECT * FROM Credits";
int itemcount = 0;
Debug.WriteLine("Поток Крдиты");
lock (o)
{
ConnectionClient.ConOpen();
using (SqlCommand commmand = new SqlCommand(sql, ConnectionClient.connection))
{
SqlDataReader reader1 = commmand.ExecuteReader();
itemcount = reader1.Cast <object>().Count();
reader1.Close();
int count = 0;
SqlDataReader reader = commmand.ExecuteReader();
while (reader.Read())
{
Credits credits = new Credits((int)reader["Id"], (long)reader["AccountNumber"], (decimal)reader["Cash"], (decimal)reader["RepayALoan"],
(decimal)reader["AmountIssue"], (int)reader["ClientId"], (int)reader["Rate"], (decimal)reader["MonthlyPayment"], (bool)reader["State"],
(int)reader["MonthsPeriod"], (DateTime)reader["OpenDate"], (DateTime)reader["NextDate"], (int)reader["TypeId"]);
// Repository.GetCredits.Add(credits);
accounts.Add(AccountFactory.GetAccount(credits));
count++;
countAccount++;
ProgressBar.GetValue("Загрузка счетов", itemcount, count);
}
}
ConnectionClient.ConClose();
}
}
catch (SqlException e)
{
errorMes.ErrorSQL(e.Message); ConnectionClient.ConClose();
}
catch (Exception e)
{
errorMes.ErrorSQL(e.Message); ConnectionClient.ConClose();
}
finally { ConnectionClient.ConClose(); }
}
/// <summary>
/// Загрузка данных по всем вкладам
/// </summary>
private static void LoadDeposit()
{
try
{
string sql = "SELECT * FROM Deposit";
int itemcount = 0;
lock (o)
{
ConnectionClient.ConOpen();
using (SqlCommand commmand = new SqlCommand(sql, ConnectionClient.connection))
{
SqlDataReader reader1 = commmand.ExecuteReader();
itemcount = reader1.Cast <object>().Count();
reader1.Close();
int count = 0;
SqlDataReader reader = commmand.ExecuteReader();
while (reader.Read())
{
Deposit deposit = new Deposit((int)reader["Id"], (long)reader["AccountNumber"], (decimal)reader["Cash"], (decimal)reader["DepositAmount"], (int)reader["ClientId"],
(bool)reader["State"], (int)reader["MonthsPeriod"], (int)reader["Rate"], (DateTime)reader["OpenDate"],
(DateTime)reader["DateClose"], (bool)reader["Capitalization"], (int)reader["TypeId"], (decimal)reader["MoneyEarned"]);
// Repository.GetDeposits.Add(deposit);
accounts.Add(AccountFactory.GetAccount(deposit));
count++;
countAccount++;
ProgressBar.GetValue("Загрузка вкладов", itemcount, count);
}
}
ConnectionClient.ConClose();
}
}
catch (SqlException e)
{
errorMes.ErrorSQL(e.Message); ConnectionClient.ConClose();
}
catch (Exception e)
{
errorMes.ErrorSQL(e.Message); ConnectionClient.ConClose();
}
finally { ConnectionClient.ConClose(); }
}
/// <summary>
/// Загрузка данных по обычным счетам
/// </summary>
private static void LoadPersonal()
{
try
{
string sql = "SELECT * FROM Personal";
int itemcount = 0;
lock (o)
{
ConnectionClient.ConOpen();
using (SqlCommand commmand = new SqlCommand(sql, ConnectionClient.connection))
{
SqlDataReader reader1 = commmand.ExecuteReader();
itemcount = reader1.Cast <object>().Count();
reader1.Close();
int count = 0;
SqlDataReader reader = commmand.ExecuteReader();
while (reader.Read())
{
Personal personal = new Personal((int)reader["Id"], (long)reader["AccountNumber"], (decimal)reader["Cash"],
(int)reader["ClientId"], (DateTime)reader["OpenDate"], (bool)reader["State"], (int)reader["TypeId"]);
Repository.GetPersonals.Add(personal);
accounts.Add(AccountFactory.GetAccount(personal));
count++;
countAccount++;
ProgressBar.GetValue("Загрузка счетов", itemcount, count);
}
}
ConnectionClient.ConClose();
}
}
catch (SqlException e)
{
errorMes.ErrorSQL(e.Message); ConnectionClient.ConClose();
}
catch (Exception e)
{
errorMes.ErrorSQL(e.Message); ConnectionClient.ConClose();
}
finally { ConnectionClient.ConClose(); }
}
/// <summary>
/// Загрузка списка статусов
/// </summary>
private static void DataStatuses()
{
try
{
string sql = "SELECT * FROM Statuses";
ConnectionClient.ConOpen();
using (SqlCommand commmand = new SqlCommand(sql, ConnectionClient.connection))
{
SqlDataReader reader = commmand.ExecuteReader();
while (reader.Read())
{
Statuses statuses = new Statuses((int)reader["Id"], (int)reader["DepositRate"], (string)reader["Status"], (int)reader["CreditRate"]);
Repository.statuses.Add(statuses);
}
ConnectionClient.ConClose();
}
}
catch (SqlException e) { errorMes.ErrorSQL(e.Message); }
catch (Exception e) { errorMes.ErrorSQL(e.Message); }
finally { ConnectionClient.ConClose(); }
}
/// <summary>
/// Загрузка списка физическх лиц
/// </summary>
private static void DataPhysicals()
{
try
{
string sql = "SELECT * FROM PhysicalClient";
ConnectionClient.ConOpen();
using (SqlCommand commmand = new SqlCommand(sql, ConnectionClient.connection))
{
SqlDataReader reader = commmand.ExecuteReader();
while (reader.Read())
{
PhysicalClient physical = new PhysicalClient((int)reader["Id"], (string)reader["Name"], (string)reader["Surname"], (DateTime)reader["DateOfBirthday"], (int)reader["CityId"], (int)reader["StatusId"]);
Repository.physicalClients.Add(physical);
customersLoad.Add(CustomFactory.GetCustomerFactory(physical));
}
ConnectionClient.ConClose();
}
}
catch (SqlException e) { errorMes.ErrorSQL(e.Message); }
catch (Exception e) { errorMes.ErrorSQL(e.Message); }
finally { ConnectionClient.ConClose(); }
}
/// <summary>
/// Обновление данных PhysicalClient
/// </summary>
/// <param name="item">Модель обновленных данных</param>
internal static void UpdatePhysical(PhysicalClient item)
{
try
{
string sql = string.Format($"UPDATE PhysicalClient SET StatusId = @StatusId, Name = @Name, Surname = @Surname, CityId = @CityId, DateOfBirthday = @DateOfBirthday WHERE Id = @Id");
ConnectionClient.ConOpen();
using (SqlCommand cmd = new SqlCommand(sql, ConnectionClient.connection))
{
cmd.Parameters.AddWithValue("@Id", item.Id);
cmd.Parameters.AddWithValue("@Name", item.Name);
cmd.Parameters.AddWithValue("@Surname", item.Surname);
cmd.Parameters.AddWithValue("@DateOfBirthday", item.DateOfBirthday);
cmd.Parameters.AddWithValue("@CityId", item.CityId);
cmd.Parameters.AddWithValue("@StatusId", item.StatusId);
cmd.ExecuteNonQuery();
ConnectionClient.ConClose();
}
}
catch (SqlException e) { errorMes.ErrorSQL(e.Message); }
catch (Exception e) { errorMes.ErrorSQL(e.Message); }
finally { ConnectionClient.ConClose(); }
}
/// <summary>
/// Добавление нового кредита
/// </summary>
/// <param name="item">Модель данных Credits</param>
internal static void AddCredits(this Credits item)
{
try
{
string sql = "INSERT INTO Credits (Id, AccountNumber, Cash, RepayALoan, AmountIssue, ClientId, Rate, MonthlyPayment, State, MonthsPeriod, OpenDate, NextDate, TypeId)" +
" VALUES (@Id, @AccountNumber, @Cash, @RepayALoan, @AmountIssue, @ClientId, @Rate, @MonthlyPayment, @State, @MonthsPeriod, @OpenDate, @NextDate, @TypeId)";
ConnectionClient.ConOpen();
using (SqlCommand cmd = new SqlCommand(sql, ConnectionClient.connection))
{
cmd.Parameters.AddWithValue("@Id", item.Id);
cmd.Parameters.AddWithValue("@AccountNumber", item.AccountNumber);
cmd.Parameters.AddWithValue("@Cash", item.Cash);
cmd.Parameters.AddWithValue("@RepayALoan", item.RepayALoan);
cmd.Parameters.AddWithValue("@AmountIssue", item.AmountIssue);
cmd.Parameters.AddWithValue("@ClientId", item.ClientId);
cmd.Parameters.AddWithValue("@Rate", item.Rate);
cmd.Parameters.AddWithValue("@MonthlyPayment", item.MonthlyPayment);
cmd.Parameters.AddWithValue("@State", item.State);
cmd.Parameters.AddWithValue("@MonthsPeriod", item.MonthsPeriod);
cmd.Parameters.AddWithValue("@OpenDate", item.OpenDate);
cmd.Parameters.AddWithValue("@NextDate", item.NextDate);
cmd.Parameters.AddWithValue("@TypeId", item.TypeId);
cmd.ExecuteNonQuery();
ConnectionClient.ConClose();
}
}
catch (SqlException e)
{
errorMes.ErrorSQL("Credit new" + e.Message); ConnectionClient.ConClose();
}
catch (Exception e)
{
errorMes.ErrorSQL(e.Message); ConnectionClient.ConClose();
}
}
/// <summary>
/// Добавление новой записи PhysicalClient в базу
/// </summary>
/// <param name="item">Модель данных</param>
internal static void AddPhysical(PhysicalClient item)
{
try
{
string sql = "INSERT INTO PhysicalClient (Id, Name, Surname, DateOfBirthday, CityId, StatusId) " +
"VALUES (@Id, @Name, @Surname, @DateOfBirthday, @CityId, @StatusId)";
ConnectionClient.ConOpen();
using (SqlCommand cmd = new SqlCommand(sql, ConnectionClient.connection))
{
cmd.Parameters.AddWithValue("@Id", item.Id);
cmd.Parameters.AddWithValue("@Name", item.Name);
cmd.Parameters.AddWithValue("@Surname", item.Surname);
cmd.Parameters.AddWithValue("@DateOfBirthday", item.DateOfBirthday);
cmd.Parameters.AddWithValue("@CityId", item.CityId);
cmd.Parameters.AddWithValue("@StatusId", item.StatusId);
cmd.ExecuteNonQuery();
ConnectionClient.ConClose();
}
}
catch (SqlException e) { errorMes.ErrorSQL(e.Message); }
catch (Exception e) { errorMes.ErrorSQL(e.Message); }
finally { ConnectionClient.ConClose(); }
}
/// <summary>
/// Обновление данных Busines
/// </summary>
/// <param name="item">Модель обновленных данных</param>
internal static void UpdateBusines(Busines item)
{
try
{
string sql = string.Format($"UPDATE Busines SET StatusId = @StatusId, OrganizationName = @OrganizationName, INNCode = @INNCode, CityId = @CityId, FIO = @FIO, PositionName = @PositionName WHERE Id = @Id");
ConnectionClient.ConOpen();
using (SqlCommand cmd = new SqlCommand(sql, ConnectionClient.connection))
{
cmd.Parameters.AddWithValue("@Id", item.Id);
cmd.Parameters.AddWithValue("@OrganizationName", item.OrganizationName);
cmd.Parameters.AddWithValue("@INNCode", item.INNCode);
cmd.Parameters.AddWithValue("@StatusId", item.StatusId);
cmd.Parameters.AddWithValue("@CityId", item.CityId);
cmd.Parameters.AddWithValue("@FIO", item.FIO);
cmd.Parameters.AddWithValue("@PositionName", item.PositionName);
cmd.ExecuteNonQuery();
ConnectionClient.ConClose();
}
}
catch (SqlException e) { errorMes.ErrorSQL(e.Message); }
catch (Exception e) { errorMes.ErrorSQL(e.Message); }
finally { ConnectionClient.ConClose(); }
}
/// <summary>
/// Загрузка списка ую.лиц
/// </summary>
private static void DataBusines()
{
try
{
string sql = "SELECT * FROM Busines";
ConnectionClient.ConOpen();
using (SqlCommand commmand = new SqlCommand(sql, ConnectionClient.connection))
{
SqlDataReader reader = commmand.ExecuteReader();
while (reader.Read())
{
Busines busines = new Busines((int)reader["Id"], (string)reader["OrganizationName"],
(long)reader["INNCode"], (int)reader["StatusId"], (int)reader["CityId"], (string)reader["FIO"], (string)reader["PositionName"]);
Repository.busines.Add(busines);
customersLoad.Add(CustomFactory.GetCustomerFactory(busines));
}
ConnectionClient.ConClose();
}
}
catch (SqlException e) { errorMes.ErrorSQL(e.Message); }
catch (Exception e) { errorMes.ErrorSQL(e.Message); }
finally { ConnectionClient.ConClose(); }
}