private static void CallAStoredProcedure()
{
SoftuniContext context = new SoftuniContext();
string[] name = Console.ReadLine().Split();
var projects = context.Database.SqlQuery <ProjectViewModel>($"EXEC EmployeeProjects {name[0]}, {name[1]}");
foreach (var project in projects)
{
Console.WriteLine($"{project.Name} - {project.Description}, {project.StartDate}");
Console.WriteLine();
}
}
static void Main(string[] args)
{
var ctx = new SoftuniContext();
#region 17. Call a Stored Procedure
#region Procedure in MSSQL
// create PROCEDURE GetProjectsForEmployee
// (@firstName varchar(30), @lastName Varchar(30))
//As
//Begin
//select p.*from Employees as e
//inner join EmployeesProjects as ep on e.EmployeeID = ep.EmployeeID
//inner join Projects as p on ep.ProjectID = p.ProjectID
//where e.FirstName = @firstName and e.LastName = @lastName
//End
#endregion
Console.Write("Enter employee first and last name: ");
string[] names = Console.ReadLine().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
SqlParameter param1 = new SqlParameter("@firstName", names[0]);
SqlParameter param2 = new SqlParameter("@lastName", names[1]);
ctx.Database
.SqlQuery <Project>("exec dbo.GetProjectsForEmployee @firstName, @lastName", param1, param2)
.Select(p => new
{
p.Name, p.Description, p.StartDate
})
.ToList().ForEach(p =>
{
Console.WriteLine($"{p.Name} - {p.Description}, {p.StartDate}");
});
#endregion
#region 18. Employees Maximum Salaries
//ctx.Departments.Where(d => d.Employee.Salary > 70000 || d.Employee.Salary < 30000)
// .ToList().ForEach(d =>
// {
// Console.WriteLine($"{d.Name} {d.Employees.Max(e=>e.Salary):f2}");
// });
#endregion
}
private static void EmployeesMaximumSalaries()
{
SoftuniContext context = new SoftuniContext();
foreach (var department in context.Departments)
{
List <decimal> MaxSalary = new List <decimal>();
foreach (var salaries in department.Employees.Select(s => s.Salary))
{
if (salaries < 30000 || salaries > 70000)
{
MaxSalary.Add(salaries);
}
}
if (MaxSalary.Count > 0)
{
Console.WriteLine(department.Name);
Console.WriteLine($"{MaxSalary.Max():F2}");
}
Console.WriteLine();
}
}
