CREATE PROCEDURE GetAllData
AS
BEGIN
SET NOCOUNT ON;
SELECT ID, Name, Age FROM dbo.Clients;
SELECT ID, Name, Quantity FROM dbo.Products;
END
The result of this query is two tables:
ID Name Age
----------- ------------------ ----------
1 Client 1 20
2 Client 2 22
3 Client 3 34
4 Client 4 12
5 Client 5 55
ID Name Quantity
----------- ------------------ -----------
1 Product 1 12.33
2 Product 2 1.00
3 Product 3 45.70
4 Product 4 100.56
5 Product 5 87.00
By default, if you try to import this SP into Entity Framework 6 (EF6), only the first query will be considered. There are two ways of solving this problem; let's have a look.
public class AllData
{
public List<Client> AllClients { get; set; }
public List<Product> AllProducts { get; set; }
}
public class Client
{
public int ID { get; set; }
public String Name { get; set; }
public int Age { get; set; }
}
public class Product
{
public int ID { get; set; }
public String Name { get; set; }
public decimal Quantity { get; set; }
}
public AllData GetAllData()
{
AllData result = new AllData()
{
AllClients = new List<Model.Client>(),
AllProducts = new List<Model.Product>()
};
using (var db = new EFMultipleResultSetEntities())
{
// Create a SQL command to execute the sproc
var cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "[dbo].[GetAllData]";
try
{
db.Database.Connection.Open();
// Run the sproc
var reader = cmd.ExecuteReader();
// Read Blogs from the first result set
var customers = ((IObjectContextAdapter)db)
.ObjectContext
.Translate<Client>(reader, "Clients", MergeOption.AppendOnly);
foreach (var item in customers)
{
result.AllClients.Add(
new Model.Client()
{
ID = item.ID,
Name = item.Name,
Age = item.Age
});
}
// Move to second result set and read Posts
reader.NextResult();
var products = ((IObjectContextAdapter)db)
.ObjectContext
.Translate<Product>(reader, "Products", MergeOption.AppendOnly);
foreach (var item in products)
{
result.AllProducts.Add(
new Model.Product()
{
ID = item.ID,
Name = item.Name,
Quantity = item.Quantity
});
}
}
finally
{
db.Database.Connection.Close();
}
}
return result;
}
public AllData GetAllData()
{
AllData result = new AllData()
{
AllClients = new List<Model.Client>(),
AllProducts = new List<Model.Product>()
};
using (var db = new EFMultipleResultSetEntities())
{
// Create a SQL command to execute the sproc
var cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "[dbo].[GetAllData]";
try
{
db.Database.Connection.Open();
// Run the sproc
var reader = cmd.ExecuteReader();
while (reader.Read())
{
result.AllClients.Add(
new Model.Client() {
ID = reader.GetInt32(0),
Name = reader.GetString(1),
Age = reader.GetInt32(2)
});
}
reader.NextResult();
while (reader.Read())
{
result.AllProducts.Add(
new Model.Product()
{
ID = reader.GetInt32(0),
Name = reader.GetString(1),
Quantity = reader.GetDecimal(2)
});
}
}
finally
{
db.Database.Connection.Close();
}
}
return result;
}
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented:
i have use the second tricks of you mention this article but the error is bind the
==> Invalid attempt to read when no data is present.
can you please help this issue.
**********The Store Procedure result is ******
**********These are Model ************
public class UserDashboard
{
public List<UserMenuItem> DealOfTheDay { get; set; }
public List<UserMenuItem> UserMenuList { get; set; }
public List<MenuItemImage> MenuImageGallery { get; set; }
public int TotalCartCount { get; set; }
}
public class UserMenuItem
{
public long ItemId { get; set; }
public string ItemCode { get; set; }
public string ItemName { get; set; }
public decimal OutletPrice { get; set; }
public decimal Calorie { get; set; }
public string Discription { get; set; }
public string UnitName { get; set; }
public string ImageTitle { get; set; }
public bool isFavorite { get; set; }
}
public class MenuItemImage
{
public int ImageID { get; set; }
public long ItemId { get; set; }
public string ImageTitle { get; set; }
}
************* This is the function of code first multiple select Statement*************
public UserDashboard GetAllUserDashboardData(lo
{
UserDashboard result = new UserDashboard();
var command = _Context.Database.Connecti
command.CommandText = "[dbo].[APIUserDashBoardDa
command.CommandType = CommandType.StoredProcedur
var userid = new SqlParameter("@UserId", UserId);
var to = new SqlParameter("@WalletType"
command.Parameters.Add(use
command.Parameters.Add(to)
try
{
_Context.Database.Connecti
// Run the sproc
var reader = command.ExecuteReader();
#region First Method to bind the entity by ef
//// Read Blogs from the first result set
//var DealOfTheDayList = ((IObjectContextAdapter)_C
// .ObjectContext
// .Translate<UserMenuItem>(r
//foreach (var item in DealOfTheDayList)
//{
// result.DealOfTheDay.Add(
// new UserMenuItem()
// {
// ItemId = item.ItemId,
// ItemCode = item.ItemCode,
// ItemName = item.ItemName,
// OutletPrice = item.OutletPrice,
// Calorie = item.Calorie,
// Discription = item.Discription,
// UnitName = item.UnitName,
// ImageTitle = item.ImageTitle,
// isFavorite = item.isFavorite,
// });
//}
//// Move to second result set and read Posts
//reader.NextResult();
//var MenuImageGalleryList = ((IObjectContextAdapter)_C
// .ObjectContext
// .Translate<MenuItemImage>(
//foreach (var item in MenuImageGalleryList)
//{
// result.MenuImageGallery.Ad
// new MenuItemImage()
// {
// ImageID = item.ImageID,
// ImageTitle = item.ImageTitle,
// ItemId = item.ItemId,
// });
//}
//// Move to third result set and read Posts
//reader.NextResult();
//var UserMenuList = ((IObjectContextAdapter)_C
// .ObjectContext
// .Translate<UserMenuItem>(r
//foreach (var item in UserMenuList)
//{
// result.UserMenuList.Add(
// new UserMenuItem()
// {
// ItemId = item.ItemId,
// ItemCode = item.ItemCode,
// ItemName = item.ItemName,
// OutletPrice = item.OutletPrice,
// Calorie = item.Calorie,
// Discription = item.Discription,
// UnitName = item.UnitName,
// ImageTitle = item.ImageTitle,
// isFavorite = item.isFavorite,
// });
//}
#endregion
#region Second Method to bind the entity by ef
// Read Blogs from the first result set
while (reader.Read())
{
result.DealOfTheDay.Add(
new UserMenuItem()
{
ItemId = reader.GetInt64(0),
ItemCode = reader.GetString(1),
ItemName = reader.GetString(2),
OutletPrice = reader.GetDecimal(3),
Calorie = reader.GetDecimal(4),
Discription = reader.GetString(5),
UnitName = reader.GetString(6),
ImageTitle = reader.GetString(7),
isFavorite = reader.GetBoolean(8),
});
}
//// Move to Second result set and read Posts
reader.NextResult();
while (reader.Read())
{
result.MenuImageGallery.Ad
new MenuItemImage()
{
ImageID = reader.GetInt32(0),
ItemId = reader.GetInt64(1),
ImageTitle = reader.GetString(2)
});
}
//// Move to third result set and read Posts
reader.NextResult();
while (reader.Read())
{
result.UserMenuList.Add(
new UserMenuItem()
{
ItemId = reader.GetInt64(0),
ItemCode = reader.GetString(1),
ItemName = reader.GetString(2),
OutletPrice = reader.GetDecimal(3),
Calorie = reader.GetDecimal(4),
Discription = reader.GetString(5),
UnitName = reader.GetString(6),
ImageTitle = reader.GetString(7),
isFavorite = reader.GetBoolean(8),
});
}
reader.NextResult();
while (reader.Read())
{
result.TotalCartCount = reader.GetInt32(0);
}
#endregion
}
catch (Exception ex)
{
ex.ToString();
}
finally
{
_Context.Database.Connecti
}
return result;
}
*********For more detail see the file attachments***************
Thanks
VS2017.png