public IEnumerable <ServicesHistory> GetAll()
{
List <ServicesHistory> serviceHistories = new List <ServicesHistory>();
string expression = @"SELECT ServicesHistory.Id AS ServicesHistoryId ,
ServicesHistory.Date AS ServicesHistoryDate, ServicesHistory.ServiceId AS ServicesHistoryServiceId,
ServicesHistory.EmployeeId AS ServicesHistoryEmployeeId, ServicesHistory.ClientId AS ServicesHistoryClientId,
ServicesDirectory.Id AS 'ServicesDirectoryId', ServicesDirectory.Name AS 'ServicesDirectoryName',
Employee.Id AS EmployeeId, Employee.FirstName AS EmployeeFirstName, Employee.SecondName AS EmployeeSecondName,
Employee.MiddleName AS EmployeeMiddleName, Employee.BirthDay AS EmployeeBirthDay, Employee.BranchId AS EmployeeBranchId,
Employee.PositionId AS EmployeePositionId,
Client.Id AS ClientId, Client.FirstName AS ClientFirstName, Client.SecondName AS ClientSecondName,
Client.MiddleName AS ClientMiddleName, Client.BirthDay AS ClientBirthDay, Client.Address AS ClientAddress,
Client.PassportSeriesAndNumber AS ClientPassportSeriesAndNumber, Client.CardNumber AS ClientCardNumber
FROM ServicesHistory
JOIN ServicesDirectory ON ServicesDirectory.Id = ServicesHistory.ServiceId
JOIN Employee ON Employee.Id = ServicesHistory.EmployeeId
JOIN Client ON Client.Id = ServicesHistory.ClientId";
SqlCommand sqlCommand = new SqlCommand(expression, sqlConnection);
DataTable dataTable = GetDataTable(sqlCommand);
foreach (DataRow row in dataTable.Rows)
{
ServicesHistory serviceHistory = serviceHistoryMapper.MapFromRow(row);
serviceHistory.ServicesDirectory = serviceMapper.MapFromRow(row);
serviceHistory.Employee = employeeMapper.MapFromRow(row);
serviceHistory.Client = clientMapper.MapFromRow(row);
serviceHistories.Add(serviceHistory);
}
return(serviceHistories);
}
public void Insert(ServicesHistory serviceHistory)
{
using (dataBase = new FinancialInstitutionEntities())
{
dataBase.ServicesHistories.Add(serviceHistory);
dataBase.SaveChanges();
}
}
public void Delete(int serviceHistoryId)
{
using (dataBase = new FinancialInstitutionEntities())
{
ServicesHistory sh = dataBase.ServicesHistories.Find(serviceHistoryId);
dataBase.ServicesHistories.Remove(sh);
}
}
public ServicesHistory MapFromRow(DataRow row)
{
ServicesHistory serviceHistory = new ServicesHistory();
serviceHistory.Id = row.Field <int>("ServicesHistoryId");
serviceHistory.Date = row.Field <DateTime>("ServicesHistoryDate");
serviceHistory.ServicesDirectoryId = row.Field <int>("ServicesHistoryServiceId");
serviceHistory.EmployeeId = row.Field <int>("ServicesHistoryEmployeeId");
serviceHistory.ClientId = row.Field <int>("ServicesHistoryClientId");
return(serviceHistory);
}
public ServicesHistory MapFromReader(SqlDataReader reader)
{
ServicesHistory serviceHistory = new ServicesHistory();
serviceHistory.Id = (int)reader["ServicesHistoryId"];
serviceHistory.Date = (DateTime)reader["ServicesHistoryDate"];;
serviceHistory.ServicesDirectoryId = (int)reader["ServicesHistoryServiceId"];;
serviceHistory.EmployeeId = (int)reader["ServicesHistoryEmployeeId"];;
serviceHistory.ClientId = (int)reader["ServicesHistoryClientId"];;
return(serviceHistory);
}
public void Insert(ServicesHistory serviceHistory)
{
string expression = "INSERT [dbo].[ServicesHistory] (Date,EmployeeID,ServiceID,ClientID) VALUES(@date, @employeeid, @serviceid, @clientid)";
SqlCommand command = new SqlCommand(expression, sqlConnection);
SqlParameter dateParam = new SqlParameter("@date", serviceHistory.Date);
SqlParameter employeeidParam = new SqlParameter("@employeeid", serviceHistory.EmployeeId);
SqlParameter serviceidParam = new SqlParameter("@serviceid", serviceHistory.ServicesDirectoryId);
SqlParameter clientidParam = new SqlParameter("@clientid", serviceHistory.ClientId);
SqlParameter[] parametrs = new SqlParameter[] { dateParam, employeeidParam, serviceidParam, clientidParam };
command.Parameters.AddRange(parametrs);
ExecuteNonQueryCommand(command);
}
public void Update(int serviceHistoryId, ServicesHistory serviceHistory)
{
string expression = "UPDATE [dbo].[ServicesHistory] SET Date = @date,EmployeeID = @employeeid,ServiceID = @serviceid,ClientID = @clientid WHERE Id = @serviceHistoryId";
SqlCommand command = new SqlCommand(expression, sqlConnection);
SqlParameter dateParam = new SqlParameter("@date", serviceHistory.Date);
SqlParameter employeeidParam = new SqlParameter("@employeeid", serviceHistory.EmployeeId);
SqlParameter serviceidParam = new SqlParameter("@serviceid", serviceHistory.ServicesDirectoryId);
SqlParameter clientidParam = new SqlParameter("@clientid", serviceHistory.ClientId);
SqlParameter serviceHistoryIdParam = new SqlParameter("@serviceHistoryId", serviceHistoryId);
SqlParameter[] parametrs = new SqlParameter[] { dateParam, employeeidParam, serviceidParam, clientidParam, serviceHistoryIdParam };
command.Parameters.AddRange(parametrs);
ExecuteNonQueryCommand(command);
}
public void Insert(ServicesHistory serviceHistory)
{
using (dataBase = new FinancialInstitutionEntities())
{
dataBase.ServicesHistories.Add(serviceHistory);
try
{
dataBase.SaveChanges();
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
}
}
public void Update(int serviceHistoryId, ServicesHistory serviceHistory)
{
using (dataBase = new FinancialInstitutionEntities())
{
ServicesHistory sh = dataBase.ServicesHistories.Find(serviceHistoryId);
ServicesHistory newsh = new ServicesHistory
{
Id = serviceHistory.Id,
Date = serviceHistory.Date,
ServiceDirectoryId = serviceHistory.ServiceDirectoryId,
EmployeeId = serviceHistory.EmployeeId,
ClientId = serviceHistory.ClientId
};
dataBase.SaveChanges();
}
}
public void Update(int serviceHistoryId, ServicesHistory serviceHistory)
{
using (dataBase = new FinancialInstitutionEntities())
{
ServicesHistory sh = dataBase.ServicesHistories.Find(serviceHistoryId);
sh.Date = serviceHistory.Date;
sh.ServicesDirectoryId = serviceHistory.ServicesDirectoryId;
sh.EmployeeId = serviceHistory.EmployeeId;
sh.ClientId = serviceHistory.ClientId;
sh.Client = serviceHistory.Client;
sh.ServicesDirectory = serviceHistory.ServicesDirectory;
sh.Employee = serviceHistory.Employee;
dataBase.SaveChanges();
}
}
public IEnumerable <ServicesHistory> GetList()
{
List <ServicesHistory> serviceHistories = new List <ServicesHistory>();
string expression = @"SELECT ServicesHistory.Id AS ServicesHistoryId ,
ServicesHistory.Date AS ServicesHistoryDate, ServicesHistory.ServiceId AS ServicesHistoryServiceId,
ServicesHistory.EmployeeId AS ServicesHistoryEmployeeId, ServicesHistory.ClientId AS ServicesHistoryClientId
FROM ServicesHistory";
SqlCommand sqlCommand = new SqlCommand(expression, sqlConnection);
DataTable dataTable = GetDataTable(sqlCommand);
foreach (DataRow row in dataTable.Rows)
{
ServicesHistory serviceHistory = serviceHistoryMapper.MapFromRow(row);
serviceHistories.Add(serviceHistory);
}
return(serviceHistories);
}
public ServicesHistory GetItem(int serviceHistoryId)
{
SqlParameter serviceIdParam = new SqlParameter("@serviceHistoryId", serviceHistoryId);
string expression = @"SELECT ServicesHistory.Id AS ServicesHistoryId ,
ServicesHistory.Date AS ServicesHistoryDate, ServicesHistory.ServiceId AS ServicesHistoryServiceId,
ServicesHistory.EmployeeId AS ServicesHistoryEmployeeId, ServicesHistory.ClientId AS ServicesHistoryClientId,
ServicesDirectory.Id AS 'ServicesDirectoryId', ServicesDirectory.Name AS 'ServicesDirectoryName',
Employee.Id AS EmployeeId, Employee.FirstName AS EmployeeFirstName, Employee.SecondName AS EmployeeSecondName,
Employee.MiddleName AS EmployeeMiddleName, Employee.BirthDay AS EmployeeBirthDay, Employee.BranchId AS EmployeeBranchId,
Employee.PositionId AS EmployeePositionId,
Client.Id AS ClientId, Client.FirstName AS ClientFirstName, Client.SecondName AS ClientSecondName,
Client.MiddleName AS ClientMiddleName, Client.BirthDay AS ClientBirthDay, Client.Address AS ClientAddress,
Client.PassportSeriesAndNumber AS ClientPassportSeriesAndNumber, Client.CardNumber AS ClientCardNumber
FROM ServicesHistory
JOIN ServicesDirectory ON ServicesDirectory.Id = ServicesHistory.ServiceId
JOIN Employee ON Employee.Id = ServicesHistory.EmployeeId
JOIN Client ON Client.Id = ServicesHistory.ClientId
WHERE ServicesHistory.Id = @serviceHistoryId";
SqlCommand command = new SqlCommand(expression, sqlConnection);
command.Parameters.Add(serviceIdParam);
ServicesHistory serviceHistory = new ServicesHistory();
using (sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
serviceHistory = serviceHistoryMapper.MapFromReader(reader);
serviceHistory.ServicesDirectory = serviceMapper.MapFromReader(reader);
serviceHistory.Employee = employeeMapper.MapFromReader(reader);
serviceHistory.Client = clientMapper.MapFromReader(reader);
}
}
return(serviceHistory);
}
