在一个呼叫中从多个表中选择 [英] Select from multiple tables in one call
问题描述
在我的代码中,我有一个页面,其中包含来自3个不同表的信息.为了显示此信息,我进行了3次SQL select调用,并将它们组合在一个列表中,以作为Model传递到我的视图.我可以通过一个SQL调用来做到吗?数据之间没有任何联系.
In my code I have a page that includes information from 3 different tables. To show this information I make 3 SQL select calls and unite them in one list to pass as Model to my view. Can I do it with one SQL call? Data has no connection with one another.
我的代码:
public ActionResult Index()
{
StorePageData PageData = new StorePageData();
return View(PageData);
}
public class StorePageData
{
public List<Table1Data> Table1 { get; set; }
public List<Table2Data> Table2 { get; set; }
public List<Table3Data> Table3 { get; set; }
public StorePageData()
{
Table1 = //loading from Database1
Table2 = //loading from Database2
Table3 = //loading from Database3
}
}
public class Table1Data
{
public int Id { get; set; }
public double Info1 { get; set; }
public string Info2 { get; set; }
}
public class Table2Data
{
public int Id { get; set; }
public List<int> Info1 { get; set; }
public List<int> Info2 { get; set; }
}
public class Table3Data
{
public int Id { get; set; }
public List<string> Info1 { get; set; }
public List<string> Info2 { get; set; }
}
如果有一种方法可以在一个SQL请求中加载所有3个表,则将大大缩短此页面的加载时间.
If there is a way to load all 3 tables in one SQL request it will improve significantly the load time of this page.
谢谢.
推荐答案
您可以使用DataReader
在单个请求中获得多个结果集.您可以在有或没有实体框架的情况下使用它.
You can get multiple result sets in a single request using a DataReader
. You can use it with or without entity framework.
如果使用的是Entity Framework,则可以传递 ObjectContext.Translate
方法将多个结果集转换为请求的对象类型.用于创建数据读取器的命令可以是存储过程,也可以仅使用包含查询的命令来塑造多个结果集.
If you are using Entity Framework, you can pass a DbDataReader
to ObjectContext.Translate
method to translate multiple result set to requested object types. The command which is used to create the data reader can be a stored procedure, or you can simply use a command containing your queries to shape multiple result set.
示例
List<Table1> list1;
List<Table2> list2;
using (var cn = new SqlConnection(@"Connection String"))
{
cn.Open();
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Table1; SELECT * FROM Table2";
var reader = cmd.ExecuteReader();
using (var db = new YourDbContext())
{
var context = ((IObjectContextAdapter)db).ObjectContext;
list1 = context.Translate<Table1>(reader).ToList();
reader.NextResult();
list2 = context.Translate<Table2>(reader).ToList();
}
}
}
If you are using SqlDataAdapter
, you can simply pass a command containing your queries and then using Fill
, fill a data set. The data adapter itself will use DataReader
behind the scene.
示例
var connectionString = @"Connection String";
var commandText = "SELECT * FROM Table1; SELECT * FROM Table2;";
var ds = new DataSet();
using (var da = new SqlDataAdapter(commandText, connectionString))
{
da.Fill(ds);
}
然后,您可以将结果整形为List<Table1>
和List<Table2>
.
Then you can shape the results to List<Table1>
and List<Table2>
.
这篇关于在一个呼叫中从多个表中选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!