public static int MyloanLogin(int Login)
{
using (var connection = SqlClientModel.GetNewSqlConnection())
using (var command = new SqlCommand($"select * from Customers c " +
$"join " +
$"Credits cr " +
$"on " +
$"c.Id = cr.ClientId " +
$"where " +
$"c.Login = {Login}", connection))
{
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine($"First Name: {reader["FirstName"]} \n" +
$"Last Name: {reader["LastName"]}, \n" +
$"Middle Name: {reader["MiddleName"]}, \n" +
$"Document Number: {reader["DocumentNumber"]},\n" +
$"Marital Status: {reader["MaritalStatus"]}, \n" +
$"Age: {reader["Age"]}, \n" +
$"The Sum Of Whole Incomes: {reader["TheSumOfWholeIncomes"]}, \n" +
$"Expiry Of Credit History: {reader["ExpiryOfCreditHistory"]}, \n" +
$"Credit History: {reader["CreditHistory"]}, \n" +
$"Credit Goal: {reader["CreditGoal"]}, \n" +
$"Credit DeadLine: {reader["CreditDeadLine"]} \n");
}
}
Console.WriteLine("Press any key to continue...");
Console.ReadKey();
return(0);
}
public static void DeleteCustomer()
{
using (var connection = SqlClientModel.GetNewSqlConnection())
using (var command = new SqlCommand("Delete Customers where Id = @Id ", connection))
{
try
{
Console.Write("Введите ID пользователя которого хотите удалить = ");
int Id = int.Parse(Console.ReadLine());
command.Parameters.AddWithValue("Id", Id);
connection.Open();
var result = command.ExecuteNonQuery();
if (result > 0)
{
Console.WriteLine("This User is successfully deleted");
}
if (result <= 0)
{
Console.WriteLine("Error while deleting a User");
}
}
catch (Exception ex)
{
Console.WriteLine($"ERROR {ex.Message}");
Console.ReadLine();
}
finally
{
connection.Close();
}
Console.ReadKey();
};
}
public int UpdateCustomer()
{
using (var connection = SqlClientModel.GetNewSqlConnection())
using (var command = new SqlCommand("Update Customers set FirstName = @FirstName, LastName = @LastName, MiddleName = @MiddleName, MaritalStatus = @MaritalStatus, Citizenship = @Citizenship, DocumentNumber = @DocumentNumber, Login = @Login, Password = @Password " +
"where Id = @Id", connection))
{
try
{
command.Parameters.AddWithValue("FirstName", this.FirstName);
command.Parameters.AddWithValue("LastName", this.LastName);
command.Parameters.AddWithValue("MiddleName", this.MiddleName);
command.Parameters.AddWithValue("MaritalStatus", this.MaritalStatus);
command.Parameters.AddWithValue("Citizenship", this.Citizenship);
command.Parameters.AddWithValue("DocumentNumber", this.DocumentNumber);
command.Parameters.AddWithValue("Login", this.Login);
command.Parameters.AddWithValue("Password", this.Password);
command.Parameters.AddWithValue("Id", this.Id);
connection.Open();
var result = command.ExecuteNonQuery();
return(result);
}
catch (Exception ex)
{
Console.WriteLine($"ERROR {ex.Message}");
Console.ReadLine();
}
finally
{
connection.Close();
}
}
return(0);
}
public static int ShowCustomersId(int Id)
{
using (var connection = SqlClientModel.GetNewSqlConnection())
using (var command = new SqlCommand($"Select * from Customers where Id = {Id}", connection))
{
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine($"Id: {reader["Id"]}, \n" +
$"FirstName: {reader["FirstName"]}, \n" +
$"LastName: {reader["LastName"]}, \n" +
$"MiddleName: {reader["MiddleName"]}, \n" +
$"DateOfBirth{reader["DateOfBirth"]}, \n" +
$"Gender: {reader["Gender"]},\n" +
$"MaritalStatus: {reader["MaritalStatus"]},\n" +
$"Citizenship: {reader["Citizenship"]},\n" +
$"DocumentNumber{reader["DocumentNumber"]} ");
}
}
Console.WriteLine("Press any key to continue...");
Console.ReadKey();
return(0);
}
public int CreateCreditModel()
{
using (var connection = SqlClientModel.GetNewSqlConnection())
using (var command = new SqlCommand("Insert into Credits(Gender, MaritalStatus, Age, Citizenship, TheSumOfWholeIncomes, ExpiryOfCreditHistory, CreditHistory, CreditGoal, CreditDeadLine)" +
" Values(@Gender, @MaritalStatus, @Age, @Citizenship, @TheSumOfWholeIncomes, @ExpiryOfCreditHistory, @CreditHistory , @CreditGoal, @CreditDeadLine)", connection))
{
try
{
command.Parameters.AddWithValue("Gender", this.Gender);
command.Parameters.AddWithValue("MaritalStatus", this.MaritalStatus);
command.Parameters.AddWithValue("Age", this.Age);
command.Parameters.AddWithValue("Citizenship", this.Citizenship);
command.Parameters.AddWithValue("TheSumOfWholeIncomes", this.TheSumOfWholeIncomes);
command.Parameters.AddWithValue("ExpiryOfCreditHistory", this.ExpiryOfCreditHistory);
command.Parameters.AddWithValue("CreditHistory", this.CreditHistory);
command.Parameters.AddWithValue("CreditGoal", this.CreditGoal);
command.Parameters.AddWithValue("CreditDeadLine", this.CreditDeadLine);
connection.Open();
var result = command.ExecuteNonQuery();
return(result);
}
catch (Exception ex)
{
Console.WriteLine($"ERROR {ex.Message}");
Console.WriteLine("Press any key to continue...");
Console.ReadLine();
}
finally
{
connection.Close();
}
}
return(0);
}
public static int ShowCustomers()
{
using (var connection = SqlClientModel.GetNewSqlConnection())
using (var command = new SqlCommand("Select * from Customers", connection))
{
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine($"Id: {reader["Id"]},\n" +
$"FirstName: {reader["FirstName"]} " +
$"LastName: {reader["LastName"]}," +
$"MiddleName: {reader["MiddleName"]}," +
$"Gender: {reader["Gender"]}," +
$"MaritalStatus: {reader["MaritalStatus"]}," +
$"Citizenship: {reader["Citizenship"]}," +
$"DateOfBirth: {reader["DateOfBirth"]}," +
$"DocumentNumber: {reader["DocumentNumber"]}");
}
return(0);
}
}
public int CreateCustomer()
{
using (var connection = SqlClientModel.GetNewSqlConnection())
using (var command = new SqlCommand("Insert into Customers(FirstName, LastName, MiddleName, Gender, MaritalStatus, Citizenship, DateOfBirth, DocumentNumber, Login, Password)" +
" Values(@FirstName, @LastName, @MiddleName, @Gender, @MaritalStatus, @Citizenship, @DateOfBirth, @DocumentNumber, @Login, @Password)", connection))
{
try
{
command.Parameters.AddWithValue("FirstName", this.FirstName);
command.Parameters.AddWithValue("LastName", this.LastName);
command.Parameters.AddWithValue("MiddleName", this.MiddleName);
command.Parameters.AddWithValue("Gender", this.Gender);
command.Parameters.AddWithValue("MaritalStatus", this.MaritalStatus);
command.Parameters.AddWithValue("Citizenship", this.Citizenship);
command.Parameters.AddWithValue("DateOfBirth", this.DateOfBirth);
command.Parameters.AddWithValue("DocumentNumber", this.DocumentNumber);
command.Parameters.AddWithValue("Login", this.Login);
command.Parameters.AddWithValue("Password", this.Password);
connection.Open();
var result = command.ExecuteNonQuery();
return(result);
}
catch (Exception ex)
{
Console.WriteLine($"ERROR {ex.Message}");
}
finally
{
connection.Close();
}
}
return(0);
//
}