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.
My code:
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; } } If there is a way to load all 3 tables in one SQL request it will improve significantly the load time of this page.
Thank you.
7 Answers
Answers 1
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>.
Answers 2
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.
Answers 3
Assuming you are using EntityFramwork you can use EF Stored procedure that returns multiple result sets. Then map the result tables to your class object. Take a look here or google it. It will take one round to the DB.
Answers 4
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"]... Answers 5
You can do something like this
SELECT Info1, Info2 FROM Table1Data UNION ALL SELECT Id, Info2 FROM Table2Data; Then you can parse the result. Depends on the case but you can also consider using a transaction.
Answers 6
May be this can helpful to you.
Select the three tables in a single procedure as a separate result sets, and then in the C# side get the result sets and convert it into JSON format, and then from the JSON you can get the each table data into your list.
DataSet dataSet = new DataSet("dataSet"); dataSet.Namespace = "NetFrameWork"; DataTable table = new DataTable(); DataColumn idColumn = new DataColumn("id", typeof(int)); idColumn.AutoIncrement = true; DataColumn itemColumn = new DataColumn("item"); table.Columns.Add(idColumn); table.Columns.Add(itemColumn); dataSet.Tables.Add(table); for (int i = 0; i < 2; i++) { DataRow newRow = table.NewRow(); newRow["item"] = "item " + i; table.Rows.Add(newRow); } dataSet.AcceptChanges(); string json = JsonConvert.SerializeObject(dataSet, Formatting.Indented); Console.WriteLine(json); //{ // "Table1": [ // { // "id": 0, // "item": "item 0" // }, // { // "id": 1, // "item": "item 1" // } // ] //"Table2": [ // { // "id": 0, // "item": "item 0", // "rate": 200.00 // }, // { // "id": 1, // "item": "item 1", // "rate": 225.00 //} // ] // "Table3": [ // { // "id": 0, // "item": "item 0", // "rate": 200.00, // "UOM" : "KG" // }, // { // "id": 1, // "item": "item 1", // "rate": 225.00, // "UOM" : "LTR" // } // ] //} Answers 7
you can use below generic code snippet
/// <method> /// Select Query /// </method> public DataTable executeSelectQuery(String _query, SqlParameter[] sqlParameter) { SqlCommand myCommand = new SqlCommand(); DataTable dataTable = new DataTable(); dataTable = null; DataSet ds = new DataSet(); try { myCommand.Connection = openConnection(); myCommand.CommandText = _query;//or proc myCommand.Parameters.AddRange(sqlParameter); myAdapter.SelectCommand = myCommand; myAdapter.Fill(ds); dataTable = ds.Tables[0];//as per your requirement } catch (SqlException e) {//any logger Console.Write("Error - Connection.executeSelectQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString()); return null; } return dataTable; } 
0 comments:
Post a Comment