none
Creating a view using datatables contained in one dataset RRS feed

  • Question

  • I have a dataset that i'm generating at run time.  it contains 5 datatables.  i created relations joining all the tables.  is there a way i can return a view of all the data linked together?

     

    SqlDataAdapter da = new SqlDataAdapter("SELECT TOP 120 dt FROM WORK_CALENDAR WHERE isWorkDay = 1 and dt >='" + d.ToShortDateString() + "'", c);

    da.Fill(ds, "WORK_CALENDAR");

    da = new SqlDataAdapter("SELECT * FROM SALES_ORDER_HEAD",c);

    da.Fill(ds, "SALES_ORDER_HEAD");

    da = new SqlDataAdapter("SELECT * FROM LOCKS_ALLOCATED_BY_DATE_V WHERE productionDate >='" + d.ToShortDateString() + "'", c);

    da.Fill(ds, "LOCKS_ALLOCATED_BY_DATE_V");

    da = new SqlDataAdapter("SELECT * FROM LOCK_CAPACITY_BY_DATE_D_V WHERE productionDate >='" + d.ToShortDateString() + "'", c);

    da.Fill(ds, "LOCK_CAPACITY_BY_DATE_D_V");

    da = new SqlDataAdapter("SELECT * FROM LOCK_CAPACITY_BY_DATE_M_V WHERE productionDate >='" + d.ToShortDateString() + "'", c);

    da.Fill(ds, "LOCK_CAPACITY_BY_DATE_M_V");

    da = new SqlDataAdapter("SELECT * FROM LOCK_CAPACITY_BY_DATE_K_V WHERE productionDate >='" + d.ToShortDateString() + "'", c);

    da.Fill(ds, "LOCK_CAPACITY_BY_DATE_K_V");

    c.Close();

    ds.Tables["SALES_ORDER_HEAD"].Columns.Add("FinalShipDate",Type.GetType("System.DateTime"));

    foreach (DataRow dr in ds.Tables["SALES_ORDER_HEAD"].Rows)

    {

    dr["FinalShipDate"] = ken230p.frmMain.calculateFinalShipDate(Convert.ToDateTime(dr["customerInHouseDate"]), (int)dr["shippingTravelDays"], c);

    }

    ds.Relations.Add(new DataRelation("calendarToHeader",

    ds.Tables["WORK_CALENDAR"].Columns["dt"],

    ds.Tables["SALES_ORDER_HEAD"].Columns["FinalShipDate"],

    false));

    ds.Relations.Add(new DataRelation("HeaderToLocksAllocatedToday",

    ds.Tables["SALES_ORDER_HEAD"].Columns["FinalShipDate"],

    ds.Tables["LOCKS_ALLOCATED_BY_DATE_V"].Columns["productionDate"],

    false));

    ds.Relations.Add(new DataRelation("HeaderToCapacityD",

    ds.Tables["SALES_ORDER_HEAD"].Columns["FinalShipDate"],

    ds.Tables["LOCK_CAPACITY_BY_DATE_D_V"].Columns["productionDate"],

    false));

    ds.Relations.Add(new DataRelation("HeaderToCapacityM",

    ds.Tables["SALES_ORDER_HEAD"].Columns["FinalShipDate"],

    ds.Tables["LOCK_CAPACITY_BY_DATE_M_V"].Columns["productionDate"],

    false));

    ds.Relations.Add(new DataRelation("HeaderToCapacityK",

    ds.Tables["SALES_ORDER_HEAD"].Columns["FinalShipDate"],

    ds.Tables["LOCK_CAPACITY_BY_DATE_K_V"].Columns["productionDate"],

    false));

    Wednesday, June 20, 2007 9:29 PM

Answers