public IEnumerable <Projects> getOneProject(int projectID)
{
var projects = new List <Projects>();
using (var context = new ProjectsContext())
{
projects = context.Projects.Where(p => p.ProjectId != projectID).ToList();
}
return(projects);
}
public IEnumerable <Projects> GetProjects()
{
var projects = new List <Projects>();
using (var context = new ProjectsContext())
{
projects = context.Projects.ToList();
}
return(projects);
}
static void LazyLoading()
{
using (var ctx = new ProjectsContext())
{
var project = ctx.Projects.Find(1);
var customerLoaded = ctx.Entry(project).Reference(x => x.Customer).IsLoaded;
var customer = project.Customer;
customerLoaded = ctx.Entry(project).Reference(x => x.Customer).IsLoaded;
}
using (var ctx = new ProjectsContext())
{
ctx.Configuration.LazyLoadingEnabled = false;
//load a project
var project = ctx.Projects.Find(1);
//see if the ProjectResources collection is loaded
var resourcesLoaded = ctx.Entry(project).Collection(x => x.ProjectResources).IsLoaded;
if (resourcesLoaded == false)
{
//explicitly load the ProjectResources collection
ctx.Entry(project).Collection(x => x.ProjectResources).Load();
}
//see if the Customer property is loaded
var customerLoaded = ctx.Entry(project).Reference(x => x.Customer).IsLoaded;
var customer = project.Customer;
customerLoaded = ctx.Entry(project).Reference(x => x.Customer).IsLoaded;
ctx.Entry(project).Reference(x => x.Customer).Load();
customerLoaded = ctx.Entry(project).Reference(x => x.Customer).IsLoaded;
customer = project.Customer;
}
using (var ctx = new ProjectsContext())
{
ctx.Configuration.LazyLoadingEnabled = false;
var customer = ctx.Customers.Find(1);
ctx.Entry(customer).Collection(x => x.Projects).Query().Where(x => x.End != null).Load();
var projectsCount = customer.Projects.Count;
}
}
public void TestProjectContainer_Delete_Project()
{
// Arrange
ProjectsContext pContext = new ProjectsContext();
// Act
pContext.DeleteProject(8);
var test = pContext.GetProject(8);
// Assert
Assert.AreEqual(null, test);
}
public IEnumerable <Projects> DeleteProject(Projects projects)
{
using (var context = new ProjectsContext())
{
context.Remove(projects);
context.SaveChanges();
}
return(new List <Projects> {
projects
});
}
static void Main(string[] args)
{
using (ProjectsContext context = new ProjectsContext())
{
Project firstProject = new Project
{
Name = "First Project",
EstimatedCost = 100000,
};
firstProject.Task.Add(new Task {
Name = "First Task", DateStarted = new DateTime(2019, 1, 1), DateFinished = new DateTime(2020, 1, 1)
});
firstProject.Task.Add(new Task {
Name = "Second Task", DateStarted = new DateTime(2019, 2, 1), DateFinished = null
});
firstProject.Task.Add(new Task {
Name = "Third Task"
});
Project secondProject = new Project
{
Name = "Second Project"
};
secondProject.Task.Add(new Task {
Name = "First Task", DateStarted = new DateTime(2019, 3, 1), DateFinished = new DateTime(2020, 2, 1)
});
secondProject.Task.Add(new Task {
Name = "Second Task", DateStarted = new DateTime(2019, 4, 1), DateFinished = null
});
context.Project.Add(firstProject);
context.Project.Add(secondProject);
context.SaveChanges();
}
using (ProjectsContext context = new ProjectsContext())
{
var stats = from p in context.Project
select new
{
ProjectId = p.ID,
ProjectName = p.Name,
TaskCount = p.Task.Count(),
FirstTask = p.Task.Min(t => t.DateStarted)
};
Console.WriteLine(System.Text.Json.JsonSerializer.Serialize(stats));
}
}
public IEnumerable <Projects> updateProject(Projects projects)
{
calculateKPI(projects);
using (var context = new ProjectsContext())
{
context.Update(projects);
context.SaveChanges();
}
return(new List <Projects> {
projects
});
}
static void Deleting()
{
using (var tx = new TransactionScope())
using (var ctx = new ProjectsContext())
{
//load a project by id
var p = ctx.Projects.Find(1);
//var p = new Project() { ProjectId = 1/*, Customer = new Customer() { CustomerId = 1 }*/ };
ctx.Delete(p);
var result = ctx.SaveChanges();
}
}
public void TestProjectContainer_Get_Project_By_Id()
{
// Arrange
ProjectsContext pContext = new ProjectsContext();
IEnumerable <ProjectsDTO> projects = new List <ProjectsDTO>();
List <ProjectsDTO> projectsListTwo = projects.ToList();
// Act
pContext.GetProject(1);
projectsListTwo.Add(pContext.GetProject(3));
// Assert
Assert.AreEqual(projectsListTwo.Count(), 1);
}
public Context()
{
var connection = new SqliteConnection("DataSource=:memory:");
connection.Open();
dbContextOptions = new DbContextOptionsBuilder <ProjectsContext>()
.UseSqlite(connection)
.Options;
using (var context = new ProjectsContext(dbContextOptions))
{
context.Database.EnsureCreated();
}
}
static void Modifying()
{
using (var ctx = new ProjectsContext())
{
using (var tx = new TransactionScope())
{
ctx.Tools.AddOrUpdate(x => x.Name, new DevelopmentTool()
{
Name = "Visual Studio 2012", Language = "C#"
});
var result = ctx.SaveChanges();
}
}
}
public ActionResult RemoveAll()
{
int i = 0;
using (var db = new ProjectsContext())
{
foreach (var item in db.Projects.ToList())
{
db.Projects.Remove(item);
i++;
}
db.SaveChanges();
}
ViewBag.Message = i + " projects are removed.";
return(View());
}
public void TestProjectContainer_Edit_Project()
{
// Arrange
ProjectsContext pContext = new ProjectsContext();
IEnumerable <ProjectsDTO> projects = new List <ProjectsDTO>();
List <ProjectsDTO> projectsListTwo = projects.ToList();
// Act
pContext.EditProject(new ProjectsDTO()
{
ProjectId = 9, UserId = 1, ProjectName = "Project edited by Test in TestProjectsContext", ProjectDescription = "Project Description Edited By Test"
});
projectsListTwo.Add(pContext.GetProject(9));
var lastProject = projectsListTwo.Last();
// Assert
Assert.AreEqual(lastProject.ProjectName, "Project edited by Test in TestProjectsContext");
}
public void TestProjectContainer_Add_Project()
{
// Arrange
ProjectsContext pContext = new ProjectsContext();
IEnumerable <ProjectsDTO> projects = new List <ProjectsDTO>();
ProjectsDTO project = new ProjectsDTO()
{
UserId = 1,
ProjectName = "Project from Test in TestProjectsContext",
ProjectDescription = "Test Description from Test in TestProjectsContext"
};
// Act
pContext.AddProject(project);
projects = pContext.GetAllProjects();
var lastProject = projects.Last();
// Assert
Assert.AreEqual(lastProject.ProjectName, "Project from Test in TestProjectsContext");
}
public IEnumerable <Projects> AddProject(Projects projects)
{
if (projects.CustomerName == "1")
{
projects.CustomerName = "SICK AG";
projects.ContactPerson = "Max Müller";
projects.Address = "Beispielstraße 10";
projects.BusinessField = "Sensortechnik";
projects.PhoneNumber = "+4915158654";
projects.EMail = "*****@*****.**";
}
else if (projects.CustomerName == "2")
{
projects.CustomerName = "Allgeier ES";
projects.ContactPerson = "Sara mack";
projects.Address = "Beispielstraße 11";
projects.BusinessField = "SAP Support";
projects.PhoneNumber = "+49171555654";
projects.EMail = "*****@*****.**";
}
else if (projects.CustomerName == "3")
{
projects.CustomerName = "Hekatron Manufacturing";
projects.ContactPerson = "Niklas Engelhardt";
projects.Address = "Beispielstraße 12";
projects.BusinessField = "EMS";
projects.PhoneNumber = "+4915186151552";
projects.EMail = "*****@*****.**";
}
projects.ProjectId = 0;
calculateKPI(projects);
using (var context = new ProjectsContext())
{
context.Add(projects);
context.SaveChanges();
}
return(new List <Projects> {
projects
});
}
private async Task <SeriesCollection> BuildOrdersChart(DateTime?beginningDateTime, DateTime?endDateTime)
{
SeriesCollection sc = new SeriesCollection();
string orderReportQuery = "EXECUTE NAT02.dbo.sp_EOI_EngineeringProduction_Orders @StartDate = {0}, @EndDate = {1}";
string[] dates = new string[] { beginningDateTime.Value.ToShortDateString(), endDateTime.Value.ToShortDateString() };
List <OrdersReport> ordersReport = new List <OrdersReport>();
using var _ = new ProjectsContext();
await Task.Run((Action)(() =>
{
ordersReport = _.OrdersReport.FromSqlRaw(orderReportQuery, dates).ToList();
}));
_.Dispose();
sc.Add(new RowSeries
{
Title = "Orders Scanned In",
Values = new ChartValues <int>(ordersReport.Select(or => or.OrdersIn))
});
sc.Add(new RowSeries
{
Title = "Orders Scanned Out",
Values = new ChartValues <int>(ordersReport.Select(or => or.OrdersOut))
});
sc.Add(new RowSeries
{
Title = "Orders To Office",
Values = new ChartValues <int>(ordersReport.Select(or => or.OrdersToOffice))
});
YAxis.Labels = ordersReport.Select(or => or.Employee).ToList();
YAxis.FontSize = 25;
YAxis.Foreground = new SolidColorBrush(Colors.Black);
return(sc);
}
public void TestTrackedEntities()
{
ContactInformation contactInformation1 = new ContactInformation()
{
Phone = "phone1", Email = "*****@*****.**"
};
var customer1 = new Customer()
{
Name = "Customer1", ContactInformation = contactInformation1
};
ContactInformation contactInformation2 = new ContactInformation()
{
Phone = "phone2", Email = "*****@*****.**"
};
var customer2 = new Customer()
{
Name = "Customer2", ContactInformation = contactInformation2
};
Technology technology = new Technology();
technology.Name = "Name";
var context = new ProjectsContext(dbContextOptions);
customerRepository.Add(customer1);
customerRepository.Add(customer2);
technologyRepository.Add(technology);
var customersTracked = customerRepository.UnitOfWork.Local();
customersTracked.Should().NotBeEmpty()
.And.HaveCount(5);
customersTracked.AsQueryable().OfType <Customer>().Should().NotBeEmpty()
.And.HaveCount(2);
customersTracked.AsQueryable().OfType <Technology>().Should().NotBeEmpty()
.And.HaveCount(1);
customersTracked.AsQueryable().OfType <ContactInformation>().Should().NotBeEmpty()
.And.HaveCount(2);
}
static void Tracing()
{
using (var ctx = ProjectsContext.CreateTracingContext("ProjectsContext", x =>
{
System.Console.WriteLine(x.ToFlattenedTraceString());
}, true, "MyContext.output"))
{
ctx.Customers.AddOrUpdate(x => x.Name, new Customer {
Name = "Big Customer"
});
//load a project by id<
var p = ctx.Projects.Find(1);
var developersOnly = ctx.Entry(p).Collection(x => x.ProjectResources).Query().Where(x => x.Role == Role.Developer).ToList();
//count an entity's collection entities without loading them
var developersCount = ctx.Entry(p).Collection(x => x.ProjectResources).Query().Count(x => x.Role == Role.Developer);
var resourcesProjectsCustomers = ctx.Resources.Include(x => x.ProjectResources.Select(y => y.Project.Customer)).ToList();
//two include paths
var resourcesProjectResourcesAndTechnologies = ctx.Resources.Include(x => x.ProjectResources).Include(x => x.Technologies).ToList();
var projectsAndTheirCustomers = ctx.Projects.Include("Customer").ToList();
//access the customer
var c = p.Customer;
ctx.Entry(p).Reference(x => x.Customer).Load();
var customer = ctx.Customers.Find(1);
var projects = customer.Projects;
ctx.Projects.Where(x => x.Name.Contains(".net")).ToList();
}
}
public ProjectsController(IUserService userService, ProjectsContext db)
{
_userService = userService;
_db = db;
}
public UserQueryHandler(ProjectsContext db)
{
this.db = db;
}
static void Querying()
{
using (var ctx = new ProjectsContext())
{
//id
var bigProject = ctx.Projects.Find(1);
//LINQ
var usersInProjectWithLINQ = (from r in ctx.Resources
from p in ctx.Projects
where p.Name == "Big Project" &&
r.ProjectResources.Select(x => x.Project).Contains(p)
select r).ToList();
var projectsByName = ctx.Projects.Where(x => x.Name == "Big Project").ToList();
var customersWithoutProjects = ctx.Customers.Where(c => c.Projects.Any() == false).ToList();
//or
var resourcesKnowingVBOrCS = ctx.Technologies.Where(t => t.Name == "VB.NET" || t.Name == "C#").SelectMany(x => x.Resources).Select(x => x.Name).ToList();
//grouping
var resourcesGroupedByProjectRole = ctx.Projects.SelectMany(x => x.ProjectResources).Select(x => new { Role = x.Role, Resource = x.Resource.Name }).GroupBy(x => x.Role).Select(x => new { Role = x.Key, Resources = x }).ToList();
//grouping and counting
var projectsByCustomer = ctx.Projects.GroupBy(x => x.Customer).Select(x => new { Customer = x.Key.Name, Count = x.Count() }).ToList();
//top 10 customers having more projects in descending order
var top10CustomersWithMoreProjects = ctx.Projects.GroupBy(x => x.Customer.Name).Select(x => new { x.Key, Count = x.Count() }).OrderByDescending(x => x.Count).Take(10).ToList();
//grouping by date part and counting
var countOfProjectsByMonth = ctx.Projects.GroupBy(x => EntityFunctions.CreateDateTime(x.Start.Year, x.Start.Month, 1, 0, 0, 0)).Select(x => new { Month = x.Key, Count = x.Count() }).ToList();
//group and count the days between two dates
var projectsGroupedByDurationDays = ctx.Projects.Where(x => x.End != null).GroupBy(x => EntityFunctions.DiffDays(x.Start, x.End.Value)).Select(x => new { Duration = x.Key, List = x }).ToList();
//order by extension method
var technologiesSortedByName = ctx.Technologies.OrderBy("Name").ThenBy("TechnologyId").ToList();
//create a base query
var projectsQuery = from p in ctx.Projects select p;
//add sorting
var projectsSortedByDateQuery = projectsQuery.OrderBy(x => x.Start);
//execute and get the sorted results
var projectsSortedByDateResults = projectsSortedByDateQuery.ToList();
//add paging
var projectsWithPagingQuery = projectsQuery.OrderBy(x => x.Start).Take(5).Skip(0);
//execute and get the first 5 results
var projectsWithPagingResults = projectsWithPagingQuery.ToList();
//add a restriction
var projectsStartingAWeekAgoQuery = projectsQuery.Where(x => x.Start >= EntityFunctions.AddDays(DateTime.Today, -7));
//execute and get the projects that started a week ago
var projectsStartingAWeekAgoResults = projectsStartingAWeekAgoQuery.ToList();
//eager load properties
var resourcesIncludingTechnologies = ctx.Resources.Include(x => x.Technologies).ToList();
var projectsIncludingCustomers = ctx.Projects.Include("Customer").ToList();
//distinct
var roles = ctx.Resources.SelectMany(x => x.ProjectResources).Where(x => x.Resource.Name == "Ricardo Peres").Select(x => x.Role).Distinct().ToList();
//check existence
var existsProjectBySomeCustomer = ctx.Projects.Any(x => x.Customer.Name == "Some Customer");
//count
var numberOfClosedProjects = ctx.Projects.Where(x => x.End != null && x.End < DateTime.Now).Count();
//average
var averageProjectDuration = ctx.Projects.Where(x => x.End != null).Average(x => EntityFunctions.DiffDays(x.Start, x.End));
//sum
var sumProjectDurationsByCustomer = ctx.Projects.Where(x => x.End != null).Select(x => new { Customer = x.Customer.Name, Days = EntityFunctions.DiffDays(x.Start, x.End) }).GroupBy(x => x.Customer).Select(x => new { Customer = x.Key, Sum = x.Sum(y => y.Days) }).ToList();
//return the resources and project names only
var resourcesXprojects = ctx.Projects.SelectMany(x => x.ProjectResources).Select(x => new { Resource = x.Resource.Name, Project = x.Project.Name }).ToList();
//return the customer names and their project counts
var customersAndProjectCount = ctx.Customers.Select(x => new { x.Name, Count = x.Projects.Count() }).ToList();
//subquery
var usersKnowingATechnology = (from r in ctx.Resources where r.Technologies.Any(x => (ctx.Technologies.Where(t => t.Name == "ASP.NET")).Contains(x)) select r).ToList();
var usersKnowingATechnology2 = (from r in ctx.Resources where r.Technologies.Any(x => (from t in ctx.Technologies where t.Name == "ASP.NET" select t).Contains(x)) select r).ToList();
//contains
var customersToFind = new String[] { "Some Customer", "Another Customer" };
var projectsOfCustomers = ctx.Projects.Where(x => customersToFind.Contains(x.Customer.Name)).ToList();
//spatial
var location = DbGeography.FromText("POINT(41 8)");
var area = DbGeography.MultiPointFromText("MULTIPOINT(53.095124 -0.864716, 53.021255 -1.337128, 52.808019 -1.345367, 52.86153 -1.018524)", 4326);
/*var pointInsideArea = ctx.Venues.Where(x => area.Intersects(x.Location)).ToList();
*
* var venuesAndDistanceToLocation = (from v in ctx.Venues
* orderby v.Location.Distance(location)
* select new { Venue = v, Distance = v.Location.Distance(location) }).ToList();*/
//Entity-SQL
ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;
//filtering
var usersInProjectWithESQL = octx.CreateQuery <Resource>("SELECT VALUE pr.Resource FROM ProjectResources AS pr WHERE pr.Project.Name = @name", new ObjectParameter("name", "Big Project")).ToList();
//contains
var usersKnowingATechnologyESQL = octx.CreateQuery <Resource>("SELECT VALUE r FROM Resources AS r WHERE EXISTS (SELECT VALUE t FROM Technologies AS t WHERE t.Name = @name AND r IN t.Resources)", new ObjectParameter("name", "ASP.NET")).ToList();
//flatten
var userTechnologiesESQL = octx.CreateQuery <Technology>("FLATTEN(SELECT VALUE r.Technologies FROM Resources AS r)").ToList();
//paging
var pagedResourcesESQL = octx.CreateQuery <Resource>("SELECT VALUE r FROM Resources AS r ORDER BY r.Name SKIP 5 LIMIT(5)").ToList();
//paging with parameters
var pagedResourcesWithParametersESQL = octx.CreateQuery <Resource>("SELECT VALUE r FROM Resources AS r ORDER BY r.Name SKIP @skip LIMIT(@limit)", new ObjectParameter("skip", 5), new ObjectParameter("limit", 5)).ToList();
//top
var lastProjectESQL = octx.CreateQuery <Project>("SELECT VALUE TOP(1) p FROM Projects AS p ORDER BY p.Start DESC").SingleOrDefault();
//between
var projectsStartingInADateIntervalESQL = octx.CreateQuery <Project>("SELECT VALUE p FROM Projects AS P WHERE p.Start BETWEEN @start AND @end", new ObjectParameter("start", DateTime.Today.AddDays(-14)), new ObjectParameter("end", DateTime.Today.AddDays(-7))).ToList();
//in
var projectsStartingInSetOfDatesESQL = octx.CreateQuery <Project>("SELECT VALUE p FROM Projects AS P WHERE p.Start IN MULTISET(DATETIME '2013-12-25 0:0:0', DATETIME '2013-12-31 0:0:0')").ToList();
//projection
var projectNameAndDurationESQL = octx.CreateQuery <Object>("SELECT p.Name, DIFFDAYS(p.Start, p.[End]) FROM Projects AS p WHERE p.[End] IS NOT NULL").ToList();
//count
var numberOfClosedProjectsESQL = octx.CreateQuery <Int32>("SELECT VALUE COUNT(p.ProjectId) FROM Projects AS p WHERE p.[End] IS NOT NULL AND p.[End] < @now", new ObjectParameter("now", DateTime.Now)).Single();
//group
var customersAndProjectCountIndicatorESQL = octx.CreateQuery <Object>("SELECT p.Customer.Name, COUNT(p.Name) FROM Projects AS p GROUP BY p.Customer").ToList();
//case
var customersAndProjectRangeESQL = octx.CreateQuery <Object>("SELECT p.Customer.Name, CASE WHEN COUNT(p.Name) > 10 THEN 'Lots' ELSE 'Few' END AS Amount FROM Projects AS p GROUP BY p.Customer").ToList();
if (customersAndProjectRangeESQL.Any() == true)
{
var r = customersAndProjectRangeESQL.OfType <IExtendedDataRecord>().First();
var nameIndex = r.GetOrdinal("Name");
var name = r.GetString(nameIndex);
}
//max number of days
var maxDurationESQL = octx.CreateQuery <Int32?>("SELECT VALUE MAX(DIFFDAYS(p.Start, p.[End])) FROM Projects AS p WHERE p.[End] IS NOT NULL").SingleOrDefault();
//string contains
var technologiesContainingNetESQL = octx.CreateQuery <String>("SELECT VALUE t.Name FROM Technologies AS T WHERE CONTAINS(t.Name, '.NET')").ToList();
//SQL
var projectFromSQL = ctx.Projects.SqlQuery("SELECT * FROM Project WHERE Name = @p0", "Big Project").ToList();
//stored procedure
var projectFromProcedure = ctx.Projects.SqlQuery("SELECT * FROM dbo.GetProjectById(@p0)", 1).SingleOrDefault();
var result = ctx.Database.ExecuteSqlCommand("UPDATE Project SET [End] = null WHERE ProjectId = {0}", 100);
//current date and time
var now = ctx.Database.SqlQuery <DateTime>("SELECT GETDATE()").Single();
var model = ctx.Database.SqlQuery(typeof(Byte[]), "SELECT Model FROM __MigrationHistory").OfType <Object>().Single();
//call function
var serverTimestamp = ctx.ExecuteScalar <DateTime>("SELECT GETDATE()");
//update records
var updateCount = ctx.ExecuteNonQuery("UPDATE ProjectDetail SET Budget = Budget * 1.1 WHERE ProjectId = {0}", 1);
//extensions
var projectsBetweenTodayAndBeforeToday = ctx.Projects.Between(x => x.Start, DateTime.Today.AddDays(-1), DateTime.Today).ToList();
//projects with 10 to 20 resources
var projectsWithTwoOrThreeResources = ctx.Projects.Select(x => new { x.Name, ResourceCount = x.ProjectResources.Count() }).Between(x => x.ResourceCount, 10, 20).ToList();
//extension method
var soundex = ctx.Projects.Select(x => x.Name.Soundex()).ToList();
//first level cache
var user = ctx.Resources.Local.SingleOrDefault(x => x.Technologies.Any(y => y.Name == "ASP.NET"));
//no caching
var technologies = ctx.Technologies.AsNoTracking().ToList();
}
}
static void ConcurrencyControl()
{
using (var ctx = new ProjectsContext())
{
using (var tx = new TransactionScope())
{
//first one wins
var p = ctx.Projects.Find(1);
var r = ctx.Database.ExecuteSqlCommand("UPDATE Project SET Name = 'a' WHERE ProjectId = @p0", p.ProjectId);
p.Name = "sadsa";
//to prevent a validation error
p.ProjectManager.ToString();
p.Customer.ToString();
try
{
r = ctx.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
//the record was changed in the database, warn the user and fail
}
}
using (var tx = new TransactionScope())
{
//first one wins
var projects = ctx.Projects.ToList();
var p = ctx.Projects.Find(1);
var r = ctx.Database.ExecuteSqlCommand("UPDATE Project SET Name = 'a' WHERE ProjectId = @p0", p.ProjectId);
p.Name = "sadsa";
//to prevent a validation error
p.ProjectManager.ToString();
p.Customer.ToString();
var failed = true;
do
{
try
{
ctx.SaveChanges();
failed = false;
}
catch (DbUpdateConcurrencyException ex)
{
var entity = ex.Entries.Single();
var databaseValues = entity.GetDatabaseValues();
entity.OriginalValues.SetValues(databaseValues);
}
}while (failed);
}
}
}
static void CreateInitialData()
{
//check if the database identified by a named connection string exists
var existsByName = Database.Exists("Name=ProjectsContext");
//check if the database identified by a connection string exists
var existsByConnectionString = Database.Exists(@"Data Source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=Succinctly;MultipleActiveResultSets=true");
using (var ctx = new ProjectsContext())
{
var created = ctx.Database.CreateIfNotExists();
if (ctx.Database.CompatibleWithModel(false) == true)
{
return;
}
new DropCreateDatabaseAlways <ProjectsContext>().InitializeDatabase(ctx);
using (var tx = new TransactionScope())
{
//venues
var home = new Venue()
{
Name = "Home, Sweet Home", Location = DbGeography.FromText("POINT(40.2112 8.4292)")
};
var somePlace = new Venue()
{
Name = "Some Place Else", Location = DbGeography.FromText("POINT(41 8)")
};
/*ctx.Venues.Add(home);
* ctx.Venues.Add(somePlace);*/
//customers
var bigCustomer = new Customer()
{
Name = "Big Customer"
};
bigCustomer.Contact.Email = "*****@*****.**";
bigCustomer.Contact.Phone = "00 1 555 111 333";
var smallCustomer = new Customer()
{
Name = "Small Customer"
};
smallCustomer.Contact.Email = "*****@*****.**";
smallCustomer.Contact.Phone = "00 351 111 222 333";
ctx.Customers.Add(bigCustomer);
ctx.Customers.Add(smallCustomer);
var developer = new Resource()
{
Name = "Ricardo Peres"
};
developer.Contact.Email = "*****@*****.**";
developer.Contact.Phone = "?";
var projectManager = new Resource()
{
Name = "Succinct Project Manager"
};
projectManager.Contact.Email = "*****@*****.**";
var tester = new Resource()
{
Name = "Succinct Tester"
};
tester.Contact.Email = "*****@*****.**";
ctx.Resources.Add(developer);
ctx.Resources.Add(projectManager);
ctx.Resources.Add(tester);
//start technologies
var aspNet = new Technology()
{
Name = "ASP.NET"
};
var entityFramework = new Technology()
{
Name = "Entity Framework"
};
var selenium = new Technology()
{
Name = "Selenium"
};
aspNet.Resources.Add(developer);
entityFramework.Resources.Add(developer);
selenium.Resources.Add(tester);
developer.Technologies.Add(aspNet);
developer.Technologies.Add(entityFramework);
tester.Technologies.Add(selenium);
ctx.Technologies.Add(aspNet);
ctx.Technologies.Add(entityFramework);
ctx.Technologies.Add(selenium);
//end technologies
//start tools
var developmentTool = new DevelopmentTool()
{
Name = "Visual Studio 2012", Language = "C#"
};
var managementTool = new ManagementTool()
{
Name = "Project 2013", CompatibleWithProject = true
};
var testingTool = new TestingTool()
{
Name = "Selenium", Automated = true
};
ctx.Tools.Add(developmentTool);
ctx.Tools.Add(managementTool);
ctx.Tools.Add(testingTool);
//end tools
//start big project
var bigProject = new Project()
{
Name = "Big Project", Start = DateTime.Today, Customer = bigCustomer
};
var bigProjectDetail = new ProjectDetail()
{
Project = bigProject, Budget = 10000M, Critical = true
};
bigProject.Detail = bigProjectDetail;
ctx.SaveChanges();
var bigProjectDeveloperResource = new ProjectResource()
{
Project = bigProject, Resource = developer, Role = Role.Developer
};
var bigProjectProjectManagerResource = new ProjectResource()
{
Project = bigProject, Resource = projectManager, Role = Role.ProjectManager
};
var bigProjectTesterResource = new ProjectResource()
{
Project = bigProject, Resource = tester, Role = Role.Tester
};
bigProject.ProjectResources.Add(bigProjectDeveloperResource);
bigProject.ProjectResources.Add(bigProjectProjectManagerResource);
bigProject.ProjectResources.Add(bigProjectTesterResource);
developer.ProjectResources.Add(bigProjectDeveloperResource);
projectManager.ProjectResources.Add(bigProjectProjectManagerResource);
tester.ProjectResources.Add(bigProjectTesterResource);
bigCustomer.Projects.Add(bigProject);
//end big project
//small project
var smallProject = new Project()
{
Name = "Small Project", Start = DateTime.Today.AddDays(-7), End = DateTime.Today.AddDays(-1), Customer = smallCustomer
};
var smallProjectDetail = new ProjectDetail()
{
Project = smallProject, Budget = 5000M, Critical = false
};
var smallProjectDeveloperResource = new ProjectResource()
{
Project = smallProject, Resource = developer, Role = Role.Developer
};
var smallProjectProjectManagerResource = new ProjectResource()
{
Project = smallProject, Resource = projectManager, Role = Role.ProjectManager
};
var smallProjectTesterResource = new ProjectResource()
{
Project = smallProject, Resource = tester, Role = Role.Tester
};
smallProject.Detail = smallProjectDetail;
smallProject.ProjectResources.Add(smallProjectDeveloperResource);
smallProject.ProjectResources.Add(smallProjectProjectManagerResource);
smallProject.ProjectResources.Add(smallProjectTesterResource);
developer.ProjectResources.Add(smallProjectDeveloperResource);
projectManager.ProjectResources.Add(smallProjectProjectManagerResource);
tester.ProjectResources.Add(smallProjectTesterResource);
smallCustomer.Projects.Add(smallProject);
//end small project
ctx.SaveChanges();
tx.Complete();
}
}
}
public GetUserCommandHandler(ProjectsContext db)
{
this.db = db;
}
public ProjectController(ProjectsContext context)
{
db = context;
}
public ActressesController(ProjectsContext context)
{
_context = context;
}
public TechnologyRepository(IUnitOfWork unitOfWork)
{
this.projectsContext = unitOfWork as ProjectsContext ?? throw new ArgumentNullException();
}
private void FillNotifications()
{
NM_DockPanel.Children.Clear();
using var _ = new NAT02Context();
List <EoiNotificationsActive> active = _.EoiNotificationsActive.Where(n => n.User == user.DomainName).OrderBy(a => a.Timestamp).ToList();
List <EoiNotificationsViewed> viewed = _.EoiNotificationsViewed.Where(n => n.User == user.DomainName).OrderBy(a => a.Timestamp).ToList();
List <(int Id, string Number, string CustomerName, string Message, bool Bl, string Type)> notifications = new List <(int, string, string, string, bool, string)>();
foreach (EoiNotificationsActive a in active)
{
using var __ = new NAT01Context();
using var ___ = new NECContext();
using var _projectsContext = new ProjectsContext();
string acctNo = "";
string custName = "";
if (a.Type == "Project")
{
if (_projectsContext.EngineeringProjects.Any(ep => ep.ProjectNumber == a.Number))
{
string endUserName = _projectsContext.EngineeringProjects.First(ep => ep.ProjectNumber == a.Number).EndUserName;
string customerName = _projectsContext.EngineeringProjects.First(ep => ep.ProjectNumber == a.Number).CustomerName;
string shipToName = _projectsContext.EngineeringProjects.First(ep => ep.ProjectNumber == a.Number).ShipToName;
custName =
!string.IsNullOrEmpty(endUserName) && !string.IsNullOrWhiteSpace(endUserName) ? endUserName :
!string.IsNullOrEmpty(customerName) && !string.IsNullOrWhiteSpace(customerName) ? customerName :
!string.IsNullOrEmpty(shipToName) && !string.IsNullOrWhiteSpace(shipToName) ? shipToName : "";
}
else if (_projectsContext.EngineeringArchivedProjects.Any(eap => eap.ProjectNumber == a.Number))
{
string rev = _projectsContext.EngineeringArchivedProjects.Where(eap => eap.ProjectNumber == a.Number).Max(p => p.RevNumber);
string endUserName = _projectsContext.EngineeringArchivedProjects.First(eap => eap.ProjectNumber == a.Number && eap.RevNumber == rev).EndUserName;
string customerName = _projectsContext.EngineeringArchivedProjects.First(eap => eap.ProjectNumber == a.Number && eap.RevNumber == rev).CustomerName;
string shipToName = _projectsContext.EngineeringArchivedProjects.First(eap => eap.ProjectNumber == a.Number && eap.RevNumber == rev).ShipToName;
custName =
!string.IsNullOrEmpty(endUserName) && !string.IsNullOrWhiteSpace(endUserName) ? endUserName :
!string.IsNullOrEmpty(customerName) && !string.IsNullOrWhiteSpace(customerName) ? customerName :
!string.IsNullOrEmpty(shipToName) && !string.IsNullOrWhiteSpace(shipToName) ? shipToName : "";
}
}
else
{
acctNo = __.OrderHeader.Single(o => o.OrderNo / 100 == double.Parse(a.Number)).UserAcctNo;
custName = ___.Rm00101.Single(r => r.Custnmbr.Trim() == acctNo.Trim()).Custname;
}
notifications.Add((a.Id, a.Number, custName, a.Message, true, a.Type));
__.Dispose();
___.Dispose();
_projectsContext.Dispose();
}
foreach (EoiNotificationsViewed v in viewed)
{
using var __ = new NAT01Context();
using var ___ = new NECContext();
using var _projectsContext = new ProjectsContext();
string acctNo = "";
string custName = "";
if (v.Type == "Project")
{
if (_projectsContext.EngineeringProjects.Any(ep => ep.ProjectNumber == v.Number))
{
string endUserName = _projectsContext.EngineeringProjects.First(ep => ep.ProjectNumber == v.Number).EndUserName;
string customerName = _projectsContext.EngineeringProjects.First(ep => ep.ProjectNumber == v.Number).CustomerName;
string shipToName = _projectsContext.EngineeringProjects.First(ep => ep.ProjectNumber == v.Number).ShipToName;
custName =
!string.IsNullOrEmpty(endUserName) && !string.IsNullOrWhiteSpace(endUserName) ? endUserName :
!string.IsNullOrEmpty(customerName) && !string.IsNullOrWhiteSpace(customerName) ? customerName :
!string.IsNullOrEmpty(shipToName) && !string.IsNullOrWhiteSpace(shipToName) ? shipToName : "";
}
else if (_projectsContext.EngineeringArchivedProjects.Any(eap => eap.ProjectNumber == v.Number))
{
string rev = _projectsContext.EngineeringArchivedProjects.Where(eap => eap.ProjectNumber == v.Number).Max(p => p.RevNumber);
string endUserName = _projectsContext.EngineeringArchivedProjects.First(eap => eap.ProjectNumber == v.Number && eap.RevNumber == rev).EndUserName;
string customerName = _projectsContext.EngineeringArchivedProjects.First(eap => eap.ProjectNumber == v.Number && eap.RevNumber == rev).CustomerName;
string shipToName = _projectsContext.EngineeringArchivedProjects.First(eap => eap.ProjectNumber == v.Number && eap.RevNumber == rev).ShipToName;
custName =
!string.IsNullOrEmpty(endUserName) && !string.IsNullOrWhiteSpace(endUserName) ? endUserName :
!string.IsNullOrEmpty(customerName) && !string.IsNullOrWhiteSpace(customerName) ? customerName :
!string.IsNullOrEmpty(shipToName) && !string.IsNullOrWhiteSpace(shipToName) ? shipToName : "";
}
}
else
{
acctNo = __.OrderHeader.Single(o => o.OrderNo / 100 == double.Parse(v.Number)).UserAcctNo;
custName = ___.Rm00101.Single(r => r.Custnmbr.Trim() == acctNo.Trim()).Custname;
}
notifications.Add((v.NotificationId, v.Number, custName, v.Message, false, v.Type));
__.Dispose();
___.Dispose();
_projectsContext.Dispose();
}
if (notifications.Count > 0)
{
notifications.OrderBy(n => n.Item1);
foreach ((int Id, string Number, string CustomerName, string Message, bool Bl, string Type)notification in notifications)
{
ContentControl contentControl = new ContentControl()
{
Style = notification.Bl ? FindResource("ActiveNotificationGrid") as Style :
FindResource("InactiveNotificationGrid") as Style
};
contentControl.ApplyTemplate();
(VisualTreeHelper.GetChild(contentControl as DependencyObject, 0) as Grid).Children.OfType <Grid>().First().Children.OfType <TextBlock>().Single(tb => tb.Name == "OrderNumberTextBlock").Text = notification.Number;
(VisualTreeHelper.GetChild(contentControl as DependencyObject, 0) as Grid).Children.OfType <Grid>().First().Children.OfType <TextBlock>().Single(tb => tb.Name == "CustomerNameTextBlock").Text = notification.CustomerName;
(VisualTreeHelper.GetChild(contentControl as DependencyObject, 0) as Grid).Children.OfType <Grid>().First().Children.OfType <TextBlock>().Single(tb => tb.Name == "NotificationMessageTextBlock").Text =
notification.Message.Replace("Document", notification.Type);
NM_DockPanel.Children.Add(contentControl);
}
}
else
{
TextBlock textBlock = new TextBlock
{
Text = "No new notifications." + Environment.NewLine + "Check back later.",
Style = Application.Current.Resources["BoldTextBlock"] as Style,
FontSize = 20,
HorizontalAlignment = HorizontalAlignment.Center,
VerticalAlignment = VerticalAlignment.Center,
TextAlignment = TextAlignment.Center,
Opacity = .5,
Cursor = Cursors.Arrow
};
NotificationWindowGrid.Children.Add(textBlock);
}
//List<EoiAllOrdersView> orders = _.EoiAllOrdersView.OrderBy(o => o.OrderNumber).ToList();
//foreach (EoiAllOrdersView order in orders)
//{
// ContentControl contentControl = new ContentControl()
// {
// Style = FindResource("OrderGrid") as Style
// };
// contentControl.ApplyTemplate();
// Grid grid = (VisualTreeHelper.GetChild(contentControl as DependencyObject, 0) as Grid).Children.OfType<Grid>().First();
// string location = "";
// string state = "";
// if (order.BeingEntered == 1)
// {
// location = "Order Entry";
// state = "Being Converted to Order";
// }
// else if (order.EnteredUnscanned == 1)
// {
// location = "Order Entry/Eng.";
// state = "Ready for Engineering";
// }
// else if (order.InEngineering == 1)
// {
// location = "Engineering";
// if (order.BeingChecked == 1)
// {
// state = "Being Checked";
// }
// else if (order.MarkedForChecking == 1)
// {
// state = "Ready to be Checked";
// }
// else
// {
// state = "Being Drawn";
// }
// }
// else if (order.ReadyToPrint == 1)
// {
// location = "Engineering";
// state = "Ready to Print";
// }
// else if (order.Printed == 1)
// {
// location = "Engineering";
// state = "Printed/Ready for Production";
// }
// else if (order.InTheOffice == 1)
// {
// location = "Office";
// state = "Sent to Office";
// }
// if (order.DoNotProcess == 1)
// {
// grid.Background = (SolidColorBrush)(new BrushConverter().ConvertFrom("#55FFC0CB"));
// }
// else if (order.DoNotProcess == 1)
// {
// grid.Background = (SolidColorBrush)(new BrushConverter().ConvertFrom("#55FFC0CB"));
// }
// if (order.RushYorN == "Y" || order.PaidRushFee == "Y")
// {
// foreach (TextBlock textBlock in grid.Children.OfType<TextBlock>()) { textBlock.Foreground = FindResource("Tertiary.Dark") as SolidColorBrush; }
// }
// grid.Children.OfType<TextBlock>().Single(tb => tb.Name == "OrderNumberTextBlock").Text = order.OrderNumber.ToString();
// grid.Children.OfType<TextBlock>().Single(tb => tb.Name == "QuoteNumberTextBlock").Text = order.QuoteNumber.ToString();
// grid.Children.OfType<TextBlock>().Single(tb => tb.Name == "QuoteRevNumberTextBlock").Text = order.QuoteRev.ToString();
// grid.Children.OfType<TextBlock>().Single(tb => tb.Name == "CustomerNameTextBlock").Text = order.CustomerName.Trim();
// grid.Children.OfType<TextBlock>().Single(tb => tb.Name == "NumDaysToShipTextBlock").Text = order.NumDaysToShip.ToString();
// grid.Children.OfType<TextBlock>().Single(tb => tb.Name == "NumDaysInDeptTextBlock").Text = order.DaysInDept.ToString();
// grid.Children.OfType<TextBlock>().Single(tb => tb.Name == "EmployeeTextBlock").Text = order.EmployeeName;
// grid.Children.OfType<TextBlock>().Single(tb => tb.Name == "LocationTextBlock").Text = location;
// grid.Children.OfType<TextBlock>().Single(tb => tb.Name == "StateTextBlock").Text = state;
// NM_DockPanel.Children.Add(contentControl);
//}
//for (int i = 0; i < 10; i++)
//{
// ContentControl contentControl = new ContentControl()
// {
// Style = FindResource("ActiveNotificationGrid") as Style
// };
// NM_DockPanel.Children.Add(contentControl);
//}
_.Dispose();
}
public UserService(IOptions <AppSettings> appSettings, ProjectsContext db)
{
_appSettings = appSettings.Value;
_db = db;
_users = _db.Users.ToList();
}
public ActorsController(ProjectsContext context)
{
_context = context;
}
