public Plan Save(Plan plan)
{
try
{
PlanModuleDB.ConnectionDB.Open();
SqlCommand command = PlanModuleDB.ConnectionDB.CreateCommand();
SqlTransaction transaction = PlanModuleDB.ConnectionDB.BeginTransaction();
command.Connection = PlanModuleDB.ConnectionDB;
command.Transaction = transaction;
string query;
var interestedUsers = plan.InterestedUsers.Select(i => new PlanInterestedUser {
Plan = plan, User = i
}).ToList();
if (plan.Id == 0)
{
query = @"
INSERT INTO PLANS (NAME, ID_TYPE, ID_USER, ID_STATUS, START_DATE, END_DATE, DESCRIPTION, COST)
VALUES (@Name, @IdType, @IdUser, 1, @StartDate, @EndDate, @Description, @Cost);
SELECT CAST(SCOPE_IDENTITY() as int);";
var planTypeInserted = command.Connection
.Query <int>(query, param: new { plan.Name, IdType = plan.Type.Id, IdUser = plan.User.Id, plan.StartDate, plan.EndDate, plan.Description, plan.Cost }, command.Transaction);
plan.Id = planTypeInserted.Single();
DataPlanInterestedUsers.Save(interestedUsers, command);
command.Transaction.Commit();
return(plan);
}
else
{
query = @"
UPDATE PLANS
SET
NAME = @Name,
ID_TYPE = @IdType,
ID_STATUS = @IdStatus,
ID_USER = @IdUser,
START_DATE = @StartDate,
END_DATE = @EndDate,
DESCRIPTION = @Description,
COST = @Cost
WHERE ID = @Id";
int affectedLines = command.Connection.Execute(query, param: new { plan.Id, plan.Name, IdType = plan.Type.Id, IdStatus = plan.Status.Id, IdUser = plan.User.Id, plan.StartDate, plan.EndDate, plan.Description, plan.Cost }, command.Transaction);
DataPlanInterestedUsers.Delete(plan.Id, command);
DataPlanInterestedUsers.Save(interestedUsers, command);
command.Transaction.Commit();
return(affectedLines > 0 ? plan : throw new ArgumentException($"There's no Plan with id = {plan.Id} in database."));
}
}
catch (SqlException e)
{
throw e;
}
finally
{
PlanModuleDB.ConnectionDB.Close();
}
}
public PlanModuleDB(string conn)
{
if (conn.Equals(""))
{
throw new ArgumentException("A string connection is required and cannot be empty.");
}
DataPlan = new DataPlan();
DataUser = new DataUser();
DataPlanStatus = new DataPlanStatus();
DataPlanType = new DataPlanType();
DataPlansHistory = new DataPlansHistory();
DataUsersHistory = new DataUserHistory();
DataPlanInterestedUsers = new DataPlanInterestedUsers();
ViewInterestedUsersByPlan = new ViewInterestedUsersByPlan();
ViewTotalPlansByUser = new ViewTotalPlansByUser();
ViewPlansByUsers = new ViewPlansByUsers();
ConnectionDB = new SqlConnection(conn);
}
