public TestForStoredProcedure()
{
string storedProcName = "test_proc";
var database = _context.Database.GetDbConnection().Database;
if (database != null)
{
storedProcName = $"{database}.{storedProcName}";
}
else
{
storedProcName = $"{storedProcName}";
}
DbParameter outputParam;
IList <Departments> fooResults;
IList <Departments> barResults;
_context.LoadStoredProc(storedProcName)
.WithSqlParam("empNo", 11)
.WithSqlParam("salary", (dbParam) => {
dbParam.Direction = System.Data.ParameterDirection.Output;
dbParam.DbType = System.Data.DbType.Int32;
outputParam = dbParam;
})
.ExecuteStoredProc(_context, (handler) => {
fooResults = handler.ReadToList <Departments>();
handler.NextResult();
barResults = handler.ReadToList <Departments>();
});
}
public async Task <VwSalariesCurrent> PostAsync(VwSalariesCurrent createRequest)
{
string storedProcName = "sp_insert_salary";
// DbParameter outputParam;
VwSalariesCurrent spResults = null;
try{
await _context
.LoadStoredProc(storedProcName)
.WithSqlParam("empNo", createRequest.EmpNo)
.WithSqlParam("salary_new", createRequest.Salary)
// .WithSqlParam("result", (dbParam) =>
// {
// dbParam.Direction = System.Data.ParameterDirection.Output;
// dbParam.DbType = System.Data.DbType.String;
// outputParam = dbParam;
// })
.ExecuteStoredProcAsync(_context, (handler) => {
bool nr = handler.NextResult();
spResults = handler.ReadToList <VwSalariesCurrent>().FirstOrDefault();
});
} catch (Exception ex) {
throw ex;
}
return(spResults);
}
public async Task <VwDeptManagerCurrent> PostAsync(VwDeptManagerCurrent createRequest)
{
string storedProcName = "sp_insert_dept_manager";
// DbParameter outputParam;
VwDeptManagerCurrent spResults = null;
await _context
.LoadStoredProc(storedProcName)
.WithSqlParam("empNo", createRequest.EmpNo)
.WithSqlParam("deptNo", createRequest.DeptNo)
// .WithSqlParam("result", (dbParam) =>
// {
// dbParam.Direction = System.Data.ParameterDirection.Output;
// dbParam.DbType = System.Data.DbType.String;
// outputParam = dbParam;
// })
.ExecuteStoredProcAsync(_context, (handler) => {
bool nr = handler.NextResult();
spResults = handler.ReadToList <VwDeptManagerCurrent>().FirstOrDefault();
});
return(spResults);
}
