public void Update(Customer customer)
{
SqlCommand command = new SqlCommand("spUpdateCustomer");
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@ID", customer.ID));
command.Parameters.Add(new SqlParameter("@FirstName", customer.Name.FirstName));
command.Parameters.Add(new SqlParameter("@LastName", customer.Name.LastName));
command.Parameters.Add(new SqlParameter("@Address", customer.Address.Street));
command.Parameters.Add(new SqlParameter("@City", customer.Address.City));
command.Parameters.Add(new SqlParameter("@ZIPcode", customer.Address.ZIPcode));
command.Parameters.Add(new SqlParameter("@PhoneNumber", customer.PhoneNumber));
command.Parameters.Add(new SqlParameter("@Email", customer.Email));
DatabaseController.ExecuteNonQuerySP(command);
}
public Employee Create(Employee employee)
{
SqlCommand command = new SqlCommand("spInsertEmployee");
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@FirstName", employee.Name.FirstName));
command.Parameters.Add(new SqlParameter("@LastName", employee.Name.LastName));
command.Parameters.Add(new SqlParameter("@EmployeeType", employee.Type.ToString()));
int employeeID = Convert.ToInt32(DatabaseController.ExecuteScalarSP(command));
employee.ID = employeeID;
UpdateQualificationsForEmployee(employee);
return(employee);
}
public void Update(Worksheet worksheet)
{
SqlCommand command = new SqlCommand("spUpdateWorksheet");
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@WorksheetID", worksheet.ID));
command.Parameters.Add(new SqlParameter("@CustomerID", worksheet.Customer.ID));
command.Parameters.Add(new SqlParameter("@Workplace", worksheet.Workplace));
command.Parameters.Add(new SqlParameter("@StartDateTime", worksheet.StartDateTime));
command.Parameters.Add(new SqlParameter("@EndDateTime", worksheet.EndDateTime));
command.Parameters.Add(new SqlParameter("@WorkDescription", worksheet.WorkDescription));
command.Parameters.Add(new SqlParameter("@IsGuarantee", worksheet.IsGuarantee));
command.Parameters.Add(new SqlParameter("@StatusType", worksheet.Status.ToString()));
UpdateAssignedEmployees(worksheet);
DatabaseController.ExecuteNonQuerySP(command);
}
public Worksheet Retrieve(int ID)
{
Worksheet worksheet = null;
SqlCommand command = new SqlCommand("spGetWorksheetByID");
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@ID", ID));
List <object[]> table = DatabaseController.ExecuteReaderSP(command);
CustomerRepository customerRepository = new CustomerRepository();
if (table?.Count > 0) // If the list is not null or empty
{
worksheet = ConvertRowToWorksheet(table[0]);
}
return(worksheet);
}
public Customer Create(Customer customer)
{
SqlCommand command = new SqlCommand("spInsertCustomer");
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@FirstName", customer.Name.FirstName));
command.Parameters.Add(new SqlParameter("@LastName", customer.Name.LastName));
command.Parameters.Add(new SqlParameter("@Address", customer.Address.Street));
command.Parameters.Add(new SqlParameter("@ZIPcode", customer.Address.ZIPcode));
command.Parameters.Add(new SqlParameter("@City", customer.Address.City));
command.Parameters.Add(new SqlParameter("@PhoneNumber", customer.PhoneNumber));
command.Parameters.Add(new SqlParameter("@Email", customer.Email));
int customerID = Convert.ToInt32(DatabaseController.ExecuteScalarSP(command));
customer.ID = customerID;
return(customer);
}
public Termsheet Create(Termsheet termsheet)
{
SqlCommand command = new SqlCommand("spInsertTermsheet");
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@WorksheetID", termsheet.WorksheetID));
command.Parameters.Add(new SqlParameter("@AcceptDate", termsheet.AcceptDate));
command.Parameters.Add(new SqlParameter("@StartDate", termsheet.StartDate));
command.Parameters.Add(new SqlParameter("@EndDate", termsheet.EndDate));
command.Parameters.Add(new SqlParameter("@Entrepreneur", termsheet.Entrepreneur));
command.Parameters.Add(new SqlParameter("@WorkDescription", termsheet.WorkDescription));
command.Parameters.Add(new SqlParameter("@PaymentType", termsheet.PaymentType.ToString()));
command.Parameters.Add(new SqlParameter("@IsDraft", termsheet.IsDraft));
int termsheetID = Convert.ToInt32(DatabaseController.ExecuteScalarSP(command));
termsheet.ID = termsheetID;
return(termsheet);
}
public Worksheet Create(Worksheet worksheet)
{
SqlCommand command = new SqlCommand("spInsertWorksheet");
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@CustomerID", worksheet.Customer.ID));
command.Parameters.Add(new SqlParameter("@Workplace", worksheet.Workplace));
command.Parameters.Add(new SqlParameter("@StartDateTime", worksheet.StartDateTime));
command.Parameters.Add(new SqlParameter("@EndDateTime", worksheet.EndDateTime));
command.Parameters.Add(new SqlParameter("@WorkDescription", worksheet.WorkDescription));
command.Parameters.Add(new SqlParameter("@IsGuarantee", worksheet.IsGuarantee));
command.Parameters.Add(new SqlParameter("@StatusType", worksheet.Status.ToString()));
int worksheetID = Convert.ToInt32((DatabaseController.ExecuteScalarSP(command)));
worksheet.ID = worksheetID;
UpdateAssignedEmployees(worksheet);
return(worksheet);
}
public List <Worksheet> RetrieveAssignedWorksheetsByEmployeeID(int employeeID)
{
List <Worksheet> worksheets = new List <Worksheet>();
SqlCommand command = new SqlCommand("spGetAssignedWorksheetsByEmployeeID");
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@EmployeeID", employeeID));
List <object[]> table = DatabaseController.ExecuteReaderSP(command);
if (table?.Count > 0) // If the list is not null or empty
{
foreach (object[] row in table)
{
worksheets.Add(ConvertRowToWorksheet(row));
}
}
return(worksheets);
}
private List <QualificationType> RetrieveEmployeeQualificationsByID(int ID)
{
List <QualificationType> qualifications = new List <QualificationType>();
SqlCommand command = new SqlCommand("spGetEmployeeQualificationsByID");
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@ID", ID));
List <object[]> table = DatabaseController.ExecuteReaderSP(command);
if (table?.Count > 0) // If the list is not null or empty
{
foreach (object[] row in table)
{
QualificationType qualificationType;
Enum.TryParse(row[1].ToString(), out qualificationType);
qualifications.Add(qualificationType);
}
}
return(qualifications);
}
/// <summary>
/// Main - точка входа в программу, существует только для демонстрации работы
/// класса-контроллера
/// </summary>
/// <param name="MyWareHouse">Создает класс-контроллер для выполнения ращличных операций с бд</param>
/// <param name="CommandList">Список команд для выполнения</param>
static void Main(string[] args)
{
string DatabaseName = "mysql";
DatabaseController MyWarehouse;
List <String> CommandList = new List <String>();
CommandList.Add("ImplementSQLCommand");
CommandList.Add("CreateObject");
CommandList.Add("CreateUser");
CommandList.Add("DropObject");
try
{
switch (DatabaseName.ToLower())
{
case "oracle":
MyWarehouse = new DatabaseController(new OracleWarehouse("Some connection string"));
Console.WriteLine("Connected to Oracle Database");
break;
case "postgresql":
MyWarehouse = new DatabaseController(new PostgreSQLWarehouse("Some connection string"));
Console.WriteLine("Connected to PostgeSQL Database");
break;
case "mysql":
MyWarehouse = new DatabaseController(new MySQLWarehouse("Some connection string"));
Console.WriteLine("Connected to MySQL Database");
break;
default:
MyWarehouse = new DatabaseController(null);
break;
}
Console.WriteLine("-----------------------");
foreach (string command in CommandList)
{
try
{
switch (command)
{
case "ImplementSQLCommand":
MyWarehouse.CurrentWarehouse.ImplementSQLCommand();
break;
case "CreateObject":
MyWarehouse.CurrentWarehouse.CreateObject();
break;
case "CreateUser":
MyWarehouse.CurrentWarehouse.CreateUser();
break;
case "DropObject":
MyWarehouse.CurrentWarehouse.DropObject();
break;
case "ConnectToDB":
MyWarehouse.CurrentWarehouse.ConnectToDB();
break;
case "CreateDatabase":
MyWarehouse.CurrentWarehouse.CreateDatabase();
break;
case "SelectCommand":
MyWarehouse.CurrentWarehouse.SelectCommand();
break;
case "GetServerDate":
MyWarehouse.CurrentWarehouse.GetServerDate();
break;
case "DeleteTable":
MyWarehouse.CurrentWarehouse.DeleteTable();
break;
case "UpdateTable":
MyWarehouse.CurrentWarehouse.UpdateTable();
break;
default:
throw new Exception("Wrong command name");
}
} catch (Exception ex) { Console.WriteLine(ex.Message); }
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadKey();
}