Select from multiple tables in one call
DO NOT USE UNION. DataAdapter is weapon of choise.
var commandText = "SELECT * FROM Table1; SELECT * FROM Table2;";
var ds = new DataSet();
using (var da = new SqlDataAdapter(commandText, "your cn"))
{
da.Fill(ds);
}
Using:
ds.Tables["Table1"]...
ds.Tables["Table2"]...
You can get multiple result sets in a single request using a DataReader
. You can use it with or without entity framework.
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.
Example
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.
Example
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);
}
Then you can shape the results to List<Table1>
and List<Table2>
.
You can use UNION ALL
to merge multiple queries.
Do something like this:
SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2
Edit:
You can do this if you want to know where a single record is from:
SELECT *, 1 AS TableName FROM Table1
UNION ALL
SELECT *, 2 AS TableName FROM Table2
This will add another column that can be used to split the array into 3 lists.