Master-detail grid with 2 dataTable (C# & DevExpress)

本文关键字:amp DevExpress grid with dataTable Master-detail | 更新日期: 2023-09-27 18:09:06

我尝试以编程方式将2 dataTable编码为Master-Detail网格。不幸的是,已经一个星期了,我试图谷歌解决方案,我仍然失败。我使用c#和DevExpress,我设法将额外的Level1设置为gridView2。主要是gridview1和gridContorl1下的所有内容。下面是我做的代码,并寻求建议,希望我能进行我的实验程序的最后一步。主表是tablesesorder &detail是tableDetail。如何关联tablealesorder和tableDetail,当选择tablealesorder ItemCode时,tableDetail将只在Master-Detail视图网格下显示ItemCode

    private void btnLoadSO_Click(object sender, EventArgs e)
    {
        LoadOutstandingSO();
        LoadDetailDS();
        gridControl1.DataSource = tableSalesOrder;
    }
   private void LoadOutstandingSO()
    {
        if (tableSalesOrder == null)
            tableSalesOrder = new DataTable("dtSO");
        else
            tableSalesOrder.Clear();
        string sqlSelect2a = "select 0 AS [Check], k.ItemCode, k.[Data Count], l2.Description, l2.ItemGroup, l2.ItemType,"
            + " CASE WHEN l2.CostingMethod = 0 THEN 'Fixed Cost' WHEN l2.CostingMethod=1 THEN 'Weighted Average'"
            + " WHEN l2.CostingMethod=2 THEN 'F.I.F.O' WHEN l2.CostingMethod=3 THEN 'L.I.F.O' ELSE 'Most Recently' END AS [CostingMethod],"
            + " k.UOM, k.Location, K.[Purchased Qty],K.[Sold Qty], K.[Adj Qty], K.[Total Qty], l.BalQty, l2.Desc2, l2.Discontinued, l2.IsActive"
            + " from (Select g.ItemCode, count(*) as [Data Count], g.UOM, g.Location,"
            + " Sum(g.InQty) as [Purchased Qty], Sum(g.OutQty) as [Sold Qty], SUM(g.AdjQty) as [Adj Qty],"
            + " Sum((g.InQty - g.OutQty) + g.AdjQty) as [Total Qty]"
            + " From (Select ivd.ItemCode as ItemCode, ivd.UOM, ivd.Location, 0 as [InQty], ivd.Qty as [OutQty], 0 as [AdjQty]"
            + " From IV ak Join IVDtl ivd on ak.DocKey = ivd.DocKey where ak.Cancelled = 'F'";
        string sqlSelect3a = " Union All"
            + " Select csd.ItemCode as ItemCode, csd.UOM, csd.Location, 0 as [InQty], csd.Qty as [OutQty], 0 as [AdjQty]"
            + " From CS ak Join CSDtl csd on ak.DocKey = csd.DocKey where ak.Cancelled = 'F'";
        string sqlSelect4a = " Union All"
            + " Select pid.ItemCode as ItemCode, pid.UOM, pid.Location, pid.Qty as [InQty], 0 as [OutQty], 0 as [AdjQty]"
            + " From PI ak Join PIDtl pid on ak.DocKey = pid.DocKey where ak.Cancelled = 'F'";
        string sqlSelect5a = " Union All"
            + " Select cpd.ItemCode as ItemCode, cpd.UOM, cpd.Location, cpd.Qty as [InQty], 0 as [OutQty], 0 as [AdjQty]"
            + " From CP ak Join CPDtl cpd on ak.DocKey = cpd.DocKey where ak.Cancelled = 'F'";
        string sqlSelect6a = " Union All"
            + " Select adjd.ItemCode as ItemCode, adjd.UOM, adjd.Location, 0 as [InQty], 0 as [OutQty], adjd.Qty as [AdjQty]"
            + " From ADJ ak Join ADJDTL adjd on ak.DocKey = adjd.DocKey where ak.Cancelled = 'F'";
        string sqlSelect2b = ") g join Item z on g.ItemCode=z.ItemCode where z.StockControl='T'";
        string sqlSelect2c = " Group By g.ItemCode, g.UOM, g.Location) k"
                        + " join ItemBalQty l on k.ItemCode=l.ItemCode and k.UOM=l.UOM and k.Location=l.Location"
                        + " join Item l2 on k.ItemCode=l2.ItemCode";
        System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(mydbset.ConnectionString);
        System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand();
        sqlCmd.Connection = conn;
        BCE.AutoCount.SearchFilter.SearchCriteria crit1a = new BCE.AutoCount.SearchFilter.SearchCriteria();
        BCE.AutoCount.SearchFilter.SearchCriteria crit1b = new BCE.AutoCount.SearchFilter.SearchCriteria();
        crit1a.AddFilter(ucDate.Filter);
        crit1b.AddFilter(ucItem.Filter);
        string critSQL1a = crit1a.BuildSQL(sqlCmd);
        string critSQL1b = crit1b.BuildSQL(sqlCmd);
        if (critSQL1a != "")
            sqlSelect2a += " AND " + critSQL1a + sqlSelect3a +" AND " + critSQL1a
                    + sqlSelect4a + " AND " + critSQL1a + sqlSelect5a + " AND " + critSQL1a
                    + sqlSelect6a + " AND " + critSQL1a + sqlSelect2b;
        else
            sqlSelect2a += sqlSelect3a + sqlSelect4a + sqlSelect5a + sqlSelect6a + sqlSelect2b;
        if (critSQL1b != "")
            sqlSelect2a += " AND " + critSQL1b + sqlSelect2c;
        else
            sqlSelect2a += sqlSelect2c;
        string sqlSelect = sqlSelect2a;
        sqlCmd.CommandText = sqlSelect + " ORDER BY ItemCode";
        System.Data.SqlClient.SqlDataAdapter adpt = new System.Data.SqlClient.SqlDataAdapter(sqlCmd);
        try
        {
            adpt.Fill(tableSalesOrder);
        }
        catch (BCE.Application.AppException ex)
        {
            throw ex;
        }
    }
    private void LoadDetailDS()
    {
        if (tableDetail == null)
            tableDetail = new DataTable("dtDT");
        else
            tableDetail.Clear();
        string sqlSelect2be = "select ak.ItemCode, ak.UOM, ak.Location, ak.DocDate, ak.DocNo, ak.Qty, ak.UnitPrice from"
            + " (select g.ItemCode, g.UOM, g.Location, ak.DocDate, ak.DocNo, g.Qty, g.UnitPrice"
            + " from PI ak right join PIDTL g on ak.DocKey=g.DocKey"; 
        string sqlSelect3be = " union all"
            + " select g.ItemCode, g.UOM, g.Location, ak.DocDate, ak.DocNo, g.Qty , g.UnitPrice"
            + " from CP ak right join CPDTL g on ak.DocKey=g.DocKey";
        string sqlSelect4be = ") ak JOIN Item z on z.ItemCode=ak.ItemCode where z.StockControl='T'";

        System.Data.SqlClient.SqlConnection conn2 = new System.Data.SqlClient.SqlConnection(mydbset.ConnectionString);
        System.Data.SqlClient.SqlCommand sqlCmd2 = new System.Data.SqlClient.SqlCommand();
        sqlCmd2.Connection = conn2;
        BCE.AutoCount.SearchFilter.SearchCriteria crit2be = new BCE.AutoCount.SearchFilter.SearchCriteria();
        BCE.AutoCount.SearchFilter.SearchCriteria crit3be = new BCE.AutoCount.SearchFilter.SearchCriteria();
        crit2be.AddFilter(ucDate.Filter);
        crit3be.AddFilter(ucItem.Filter);
        string critSQL2be = crit2be.BuildSQL(sqlCmd2); 
        string critSQL3be = crit3be.BuildSQL(sqlCmd2);
        if (critSQL3be != "")
            sqlSelect2be += " WHERE " + critSQL3be + sqlSelect3be + " WHERE " + critSQL3be + sqlSelect4be;
        else
            sqlSelect2be += sqlSelect3be + sqlSelect4be;

        if (critSQL2be != "")
            sqlSelect2be += " AND " + critSQL2be;
        sqlCmd2.CommandText = sqlSelect2be + " order by ItemCode, DocDate, DocNo";
        System.Data.SqlClient.SqlDataAdapter adpt = new System.Data.SqlClient.SqlDataAdapter(sqlCmd2);
        try
        {
            adpt.Fill(tableDetail);
        }
        catch (BCE.Application.AppException ex)
        {
            throw ex;
        }
    }

Master-detail grid with 2 dataTable (C# & DevExpress)

要根据给定的主行定位详细行,需要在数据集中添加这些表之间的关系。该关系定义了将详细行与其主行相关联的列。

如果详细级别名称与关系名称匹配,XtraGrid可以自动从关系中检索详细视图的数据。你可以使用关卡设计师或编程方式分配关卡名称。

此外,如果数据集有关系并且ShowOnlyPredefinedDetails选项被设置为false, XtraGrid可以自动填充细节级别。