Using-Data-Relations


            <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" 	Inherits="Default_aspx" %>



    Untitled Page


    
    
                        runat="server"
              CellPadding="4" 
        ForeColor="#333333" 
        GridLines="None" 
        DataKeyNames="OrderID" 
        AutoGenerateColumns="False" 
        PagerSettings-Mode="Numeric"
        AllowPaging="true"
        PageSize="5"
        OnSelectedIndexChanged="OnSelectedIndexChangedHandler" >
        
        
        
        
        
        
        
        
          
          
          
          
          
          
        
     
     
    
                    AutoGenerateColumns="False" 
            BackColor="LightGoldenrodYellow" 
            BorderColor="Tan" 
            BorderWidth="1px" 
            CellPadding="2" 
            ForeColor="Black" 
            GridLines="None">
              
              
                
                
                
                
              
                          ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
            
            
            
        
    
     
             BackColor="White" 
        BorderColor="#CC9966"
        BorderStyle="None" 
        BorderWidth="1px" 
        CellPadding="4">
        
        
        
        
        
     
    

    


File: Default.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;  
using System.Data.SqlClient;
public partial class Default_aspx : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
     DataSet ds = CreateDataSet();
     GridView1.DataSource = ds.Tables[0];
     GridView1.DataBind();
     DataView detailsView = new DataView(ds.Tables[1]);
     DetailsGridView.DataSource = detailsView;
     Session["DetailsView"] = detailsView;
     DetailsGridView.DataBind();
     OrderRelationsGridView.DataSource = ds.Relations;
     OrderRelationsGridView.DataBind();
    }
   private DataSet CreateDataSet()
   {
     string connectionString = "Data Source=LocalHost;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=sa_0001";
     System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString);
     connection.Open();
     System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
     command.Connection = connection;
     StringBuilder s = new StringBuilder("select OrderID, c.CompanyName, c.ContactName, ");
     s.Append(" c.ContactTitle, c.Phone, orderDate");
     s.Append(" from orders o ");
     s.Append("join customers c on c.CustomerID = o.CustomerID");
     command.CommandText = s.ToString();
     SqlDataAdapter dataAdapter = new SqlDataAdapter();
     dataAdapter.SelectCommand = command;
     dataAdapter.TableMappings.Add("Table", "Orders");
     DataSet dataSet = new DataSet();
     dataAdapter.Fill(dataSet);
     System.Data.SqlClient.SqlCommand command2 =
       new System.Data.SqlClient.SqlCommand();
     command2.Connection = connection;
     StringBuilder s2 = new StringBuilder("Select od.OrderID, OrderDate, p.ProductID, ");
     s2.Append(" ProductName, od.UnitPrice, Quantity ");
     s2.Append("from Orders o ");
     s2.Append("join [Order Details] od on o.orderid = od.orderid ");
     s2.Append("join products p on p.productID = od.productid ");
     command2.CommandText = s2.ToString();
     SqlDataAdapter dataAdapter2 = new SqlDataAdapter();
     dataAdapter2.SelectCommand = command2;
     dataAdapter2.TableMappings.Add("Table", "Order Details");
     dataAdapter2.Fill(dataSet);
     System.Data.SqlClient.SqlCommand command3 =
       new System.Data.SqlClient.SqlCommand();
     command3.Connection = connection;
     string strCommand3 = "Select ProductID, ProductName from Products";
     command3.CommandText = strCommand3;
     SqlDataAdapter dataAdapter3 = new SqlDataAdapter();
     dataAdapter3.SelectCommand = command3;
     dataAdapter3.TableMappings.Add("Table", "Products");
     dataAdapter3.Fill(dataSet);
     System.Data.DataRelation dataRelation;
     System.Data.DataColumn dataColumn1;
     System.Data.DataColumn dataColumn2;
     dataColumn1 = dataSet.Tables["Orders"].Columns["OrderID"];
     dataColumn2 = dataSet.Tables["Order Details"].Columns["OrderID"];
     dataRelation =
       new System.Data.DataRelation(
       "OrdersToDetails",
       dataColumn1,
       dataColumn2);
     dataSet.Relations.Add(dataRelation);
     dataColumn1 = dataSet.Tables["Products"].Columns["ProductID"];
     dataColumn2 = dataSet.Tables["Order Details"].Columns["ProductID"];
     dataRelation =
       new System.Data.DataRelation(
       "ProductIDToName",
       dataColumn1,
       dataColumn2);
     dataSet.Relations.Add(dataRelation);
     return dataSet;
   }
  public void OnSelectedIndexChangedHandler(Object sender, EventArgs e)
  {
    UpdateDetailsGrid();
  }
  private void UpdateDetailsGrid()
  {
    int index = GridView1.SelectedIndex;
    if (index != -1)
    {
      DataKey key = GridView1.DataKeys[index];
      int orderID = (int)key.Value;
      DataView detailsView = (DataView)Session["detailsView"];
      detailsView.RowFilter = "OrderID = " + orderID;
      DetailsGridView.DataSource = detailsView;
      DetailsGridView.DataBind();
      OrderDetailsPanel.Visible = true;
    }
    else
    {
      OrderDetailsPanel.Visible = false;
    }
  }
}
ADO Net Database ASP.Net Tutorial
