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;
}
}
要根据给定的主行定位详细行,需要在数据集中添加这些表之间的关系。该关系定义了将详细行与其主行相关联的列。
如果详细级别名称与关系名称匹配,XtraGrid可以自动从关系中检索详细视图的数据。你可以使用关卡设计师或编程方式分配关卡名称。此外,如果数据集有关系并且ShowOnlyPredefinedDetails选项被设置为false, XtraGrid可以自动填充细节级别。