using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class DataRelations : Page
{
   protected void Page_Load(object sender, EventArgs e)
   {
      if (!IsPostBack)
      {
         UpdateDetailsGrid();

         DataSet ds = CreateDataSet();
         OrderGridView.DataSource = ds.Tables["Orders"];
         OrderGridView.DataBind();

         // Tworzymy DataView i łączymy z DetailsView.
         DataView detailsView = new DataView(ds.Tables["Details"]);
         OrderDetailsGridView.DataSource = detailsView;
         Session["DetailsView"] = detailsView;
         OrderDetailsGridView.DataBind();

         // Łączymy siatkę zawierającą powiązania ze zbiorem powiązań.
         OrderRelationsGridView.DataSource = ds.Relations;
         OrderRelationsGridView.DataBind();
      }
   }

   protected void OrderGridView_SelectedIndexChanged(object sender, EventArgs e)
   {
      UpdateDetailsGrid();
   }

   private void UpdateDetailsGrid()
   {

      int index = OrderGridView.SelectedIndex;
      if (index != -1)
      {
         // Z siatki danych pobieramy identyfikator zamówienia.
         DataKey key = OrderGridView.DataKeys[index];
         int orderID = (int)key.Value;
         DataView detailsView = (DataView)Session["detailsView"];
         detailsView.RowFilter = "SalesOrderID = " + orderID;
         OrderDetailsGridView.DataSource = detailsView;
         OrderDetailsGridView.DataBind();
         OrderDetailsPanel.Visible = true;
      }
      else
      {
         OrderDetailsPanel.Visible = false;
      }
   }

   private DataSet CreateDataSet()
   {
      // Ciąg znaków połączenia w celu połączenia z bazą danych zamówień.
      string connectionString =
         "Data Source=(local)\\sql2k5;Initial Catalog=AdventureWorksLT;" +
          "Integrated Security=True";
      SqlConnection connection = new SqlConnection(connectionString);

      // Tworzenie obiektu DataAdapter dla kontrolki GridView SalesOrderHeader.
      SqlDataAdapter OrdersAdapter = CreateAdapterForOrders(connection);

      // Tworzenie drugiego obiektu DataAdapter dla kontrolki GridView SalesOrderDetail.
      SqlDataAdapter OrderDetailsAdapter = CreateAdapterForOrderDetails(connection);     

      // Tworzenie trzeciego obiektu DataAdapter dla tabeli Products.
      SqlDataAdapter ProductsAdapter = CreateAdapterForProducts(connection);
      
      // Tworzenie zbioru danych i użycie DataAdapter do jego wypełnienia. 
      DataSet dataSet = new DataSet();


      try
      {
         //connection.Open();
         OrdersAdapter.Fill(dataSet);
         OrderDetailsAdapter.Fill(dataSet);
         ProductsAdapter.Fill(dataSet);
      }
      finally
      {
         if (connection.State != ConnectionState.Closed)
         {
            connection.Close();
         }
      }

      CreateRelationsInDataSet(dataSet);

      return dataSet;
   }



   private static SqlDataAdapter CreateAdapterForOrders(SqlConnection connection)
   {
      // Budowanie polecenia SQL.
      StringBuilder s = new StringBuilder("select o.SalesOrderID, o.OrderDate, c.CompanyName, c.FirstName + ' ' + c.LastName as 'Contact', o.TotalDue");
      s.Append(" from SalesLT.SalesOrderHeader o ");
      s.Append("inner join SalesLT.Customer c on c.CustomerID = o.CustomerID");

      SqlDataAdapter OrdersAdapter = new SqlDataAdapter();
      OrdersAdapter.SelectCommand = new SqlCommand(s.ToString(), connection);
      OrdersAdapter.TableMappings.Add("Table", "Orders");
      return OrdersAdapter;
   }     

   private static SqlDataAdapter CreateAdapterForOrderDetails(SqlConnection connection)
   {
      // Budowanie polecenia SQL.
      StringBuilder s = new StringBuilder("Select d.SalesOrderId, p.Name, p.ProductID, d.OrderQty, d.UnitPrice, d.LineTotal ");
      s.Append("from SalesLT.SalesOrderDetail d ");
      s.Append("inner join SalesLT.Product p on d.productid = p.productid");

      SqlDataAdapter OrderDetailsAdapter = new SqlDataAdapter();
      OrderDetailsAdapter.SelectCommand = new SqlCommand(s.ToString(), connection);
      OrderDetailsAdapter.TableMappings.Add("Table", "Details");
      return OrderDetailsAdapter;
   }

   private static SqlDataAdapter CreateAdapterForProducts(SqlConnection connection)
   {
      string cmdString = "Select ProductID, Name from SalesLT.Product";

      SqlDataAdapter ProductsAdapter = new SqlDataAdapter();
      ProductsAdapter.SelectCommand = new SqlCommand(cmdString, connection);
      ProductsAdapter.TableMappings.Add("Table", "Products");
      return ProductsAdapter;
   }

   private static void CreateRelationsInDataSet(DataSet dataSet)
   {
      // Deklaracja obiektów DataRelation i DataColumn.         
      DataRelation dataRelation;
      DataColumn dataColumn1;
      DataColumn dataColumn2;
      // Ustawiamy dataColumns, aby utworzyć związki między
      // tabelami Orders i Order Details za pomocą klucza OrderID.            
      dataColumn1 = dataSet.Tables["Orders"].Columns["SalesOrderID"];
      dataColumn2 = dataSet.Tables["Details"].Columns["SalesOrderID"];
      dataRelation = new DataRelation("OrdersToDetails", dataColumn1, dataColumn2);
      // Dodajemy nowy obiekt DataRelation do zbioru danych.        
      dataSet.Relations.Add(dataRelation);

      // Ponownie używamy obiektów DataColumns i DataRelation
      // do utworzenia związku między tabelami Order Details i Products.      
      dataColumn1 = dataSet.Tables["Products"].Columns["ProductID"];
      dataColumn2 = dataSet.Tables["Details"].Columns["ProductID"];
      dataRelation = new DataRelation("ProductIDToName", dataColumn1, dataColumn2);
      dataSet.Relations.Add(dataRelation);
      return;
   }
}
