public IEnumerable <Department> GetDepartments()
{
IEnumerable <Department> departments = new List <Department>();
using (EmployeeDBContextCF DB = new EmployeeDBContextCF())
{
departments = DB.Departments.Include("Employees").ToList();
}
return(departments);
}
internal void InvokeStoreProcedure()
{
using (EmployeeDBContextCF DB = new EmployeeDBContextCF())
{
// Calling sp with single parameter and returning model
SqlParameter DepartmentID = new SqlParameter("@DepartmentID", 2);
var department = DB.Departments
.SqlQuery("spGetDepartmentDetails @DepartmentID", DepartmentID)
.FirstOrDefault();
//calling sp without parameter and returning AnonymousType
var AnonymousType = DB.Database
.SqlQuery <spGetanonymousType_ResultType>("spGetanonymousType")
.ToList();
//Calling sp without parameter and returning string value
var ScalarValueString = DB.Database
.SqlQuery <string>("spGetScalarValue")
.FirstOrDefault();
//Calling sp without parameter and returning int value
var ScalarValueInt = DB.Database
.SqlQuery <int>("spGetScalarValueInt")
.FirstOrDefault();
//pass multiple parameter
SqlParameter FirstName = new SqlParameter("@FirstName", "Sumit");
SqlParameter LastName = new SqlParameter("@LastName", "Shitole");
SqlParameter Gender = new SqlParameter("@Gender", "Male");
SqlParameter Salary = new SqlParameter("@Salary", "5000");
SqlParameter DepartmentId = new SqlParameter("@DepartmentID", "2");
//var AffectedRows = DB.Database.SqlQuery<int>("spCreateEmployee @FirstName @LastName @Gender @Salary @DepartmentID",
// parameters.ToArray());
//This Worked fine
var AffectedRows = DB.Database.SqlQuery <int>("EXEC spCreateEmployee @FirstName,@LastName,@Gender,@Salary,@DepartmentID",
new object[] { FirstName, LastName, Gender, Salary, DepartmentId }).ToList();
string CommandText = "spCreateEmployee @FirstName=@FirstName_Param,@LastName=@LastName_Param,";
CommandText += "@Gender=@Gender_Param,@Salary=@Salary_Param,@DepartmentID=@DepartmentID_Param";
var sqlparams = new[]
{
new SqlParameter("@FirstName_Param", "SumitNew"),
new SqlParameter("@LastName_Param", "ShitoleNew"),
new SqlParameter("@Gender_Param", "GenderNew"),
new SqlParameter("@Salary_Param", "55555"),
new SqlParameter("@DepartmentID_Param", "3")
};
var AffectedRowsNew = DB.Database.SqlQuery <int>(CommandText, sqlparams).FirstOrDefault();
}
}
