Hi friends,
after a big big break I come back again.
I come up with some new topics.
Some of days back I am working on Web Api. using code first approch because client dont want to give me database.
So I just have signature of stored proc and return structure.
Everything is working fine.
I am building Web Api for Mobile applications with Ionic framework.
Everything is simple upto this. the main 'yow' thing comes when client give me multiple result sets return from his stored proc.
I have never done this before in code first accespting multiple result sets.
I have started looking to it and yeesss I got it..
So the solution is We can achive this by using command reader and IObjectContextAdapter
The whole concept is first of all we need to create classes
Each class for each resultset.
for testing pourpose I have used AdventureWorks 2012 Database. Created a small stored proc which returns me Department detail and its employee list.
As per above request my classes will because
public class Employee
{
public DateTime BirthDate { get; set; }
public string JobTitle { get; set; }
}
[ComplexType]
public class DepartmentEmpBase
{
public string Name { get; set; }
public string GroupName { get; set; }
public List<Employee> EmployeeList { get; set; }
}
Next I have to create Context object inherited by Dbcontext And we need to add complex object in Context object while model building
public class DepartmentContext : NovaContext
{
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.ComplexType<DepartmentEmpBase>();
}
}
Next and final task is to call stored proc and read one by one dataset
DepartmentEmpBase bs = new DepartmentEmpBase();
using (var db = new DepartmentContext())
{
// Create a SQL command to execute the sproc
var cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = Sql;
try
{
db.Database.Connection.Open();
var reader = cmd.ExecuteReader();
var depts = ((IObjectContextAdapter)db)
.ObjectContext
.Translate<DepartmentEmpBase>(reader);
bs = depts.FirstOrDefault();
reader.NextResult();
var emp = ((IObjectContextAdapter)db)
.ObjectContext
.Translate<Employee>(reader);
bs.EmployeeList = emp.ToList();
}
catch (Exception ex)
{
}
finally
{
db.Database.Connection.Close();
}
}
Thats It!!
after a big big break I come back again.
I come up with some new topics.
Some of days back I am working on Web Api. using code first approch because client dont want to give me database.
So I just have signature of stored proc and return structure.
Everything is working fine.
I am building Web Api for Mobile applications with Ionic framework.
Everything is simple upto this. the main 'yow' thing comes when client give me multiple result sets return from his stored proc.
I have never done this before in code first accespting multiple result sets.
I have started looking to it and yeesss I got it..
So the solution is We can achive this by using command reader and IObjectContextAdapter
The whole concept is first of all we need to create classes
Each class for each resultset.
for testing pourpose I have used AdventureWorks 2012 Database. Created a small stored proc which returns me Department detail and its employee list.
As per above request my classes will because
public class Employee
{
public DateTime BirthDate { get; set; }
public string JobTitle { get; set; }
}
[ComplexType]
public class DepartmentEmpBase
{
public string Name { get; set; }
public string GroupName { get; set; }
public List<Employee> EmployeeList { get; set; }
}
Next I have to create Context object inherited by Dbcontext And we need to add complex object in Context object while model building
public class DepartmentContext : NovaContext
{
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.ComplexType<DepartmentEmpBase>();
}
}
Next and final task is to call stored proc and read one by one dataset
DepartmentEmpBase bs = new DepartmentEmpBase();
using (var db = new DepartmentContext())
{
// Create a SQL command to execute the sproc
var cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = Sql;
try
{
db.Database.Connection.Open();
var reader = cmd.ExecuteReader();
var depts = ((IObjectContextAdapter)db)
.ObjectContext
.Translate<DepartmentEmpBase>(reader);
bs = depts.FirstOrDefault();
reader.NextResult();
var emp = ((IObjectContextAdapter)db)
.ObjectContext
.Translate<Employee>(reader);
bs.EmployeeList = emp.ToList();
}
catch (Exception ex)
{
}
finally
{
db.Database.Connection.Close();
}
}
Thats It!!
