原文地址：http://hi.baidu.com/qdseashore/item/38f1153e9d0143637d034b7a
在做项目中，往往需要在查询基类模板窗口内做主细关系Grid，引用一下代码可减少工作量，只需给此过程传递主、细SQL语句以及关联的关键字段即可完成如图所示的效果:
#region 执行主细查询过程
public void ExeQuery(string sMSql,string sDSql,string RelaitionField)
{
int iMCountItemNum = 0; int iDCountItemNum = 0;
int iMSumItemNum = 0; int iDSumItemNum = 0;
//查询。
if (sMSql == null || sMSql == "" || sDSql == null || sDSql == "")
{
return;
}
#region 根据SQL填充主、细表
using (OracleConnection connection = new OracleConnection(DbHelperOra.connectionString))
{
_dtQuery = new DataSet();
try
{
connection.Open();
OracleDataAdapter command = new OracleDataAdapter(sMSql, connection);
command.Fill(_dtQuery, "MA");
command.SelectCommand.CommandText=sDSql;
command.Fill(_dtQuery,"DE");
command.Dispose();
}
catch (System.Data.OracleClient.OracleException ex)
{
}
finally
{
connection.Close();
}
}
#endregion
#region 主表动态构造字段。
try
{
//获取到表结构。
if (_dtQuery.Tables["MA"].Rows.Count >= 0)
{
gvMainList.Columns.Clear();
gvMainList.GroupSummary.Clear();
gvMainList.ClearGrouping();
//gvMainList.OptionsBehavior.Editable = true;
int iVisible = 0;
//根据表结构动态加载到GridControl控件中。
foreach (DataColumn _dcQuery in _dtQuery.Tables["MA"].Columns)
{
iVisible++;
//动态添加
DevExpress.XtraGrid.Columns.GridColumn gcQueryList = new DevExpress.XtraGrid.Columns.GridColumn();
gcQueryList.Name = "_dc" + _dcQuery.ColumnName.ToUpper();
gcQueryList.Caption = _dcQuery.ColumnName;
gcQueryList.FieldName = _dcQuery.ColumnName;
//数字类型
if ((_dcQuery.DataType.ToString().ToUpper() == "SYSTEM.DECIMAL" || _dcQuery.DataType.ToString().ToUpper() == "SYSTEM.DOUBLE") &&
((gcQueryList.Caption.IndexOf("金额") >= 0) || (gcQueryList.Caption.IndexOf("价格") >= 0)
|| (gcQueryList.Caption.IndexOf("人民币") >= 0) || (gcQueryList.Caption.IndexOf("美元") >= 0)
|| (gcQueryList.Caption.IndexOf("单价") >= 0) || (gcQueryList.Caption.IndexOf("个") >= 0)
|| (gcQueryList.Caption.IndexOf("件") >= 0) || (gcQueryList.Caption.IndexOf("箱") >= 0)
|| (gcQueryList.Caption.IndexOf("数量") >= 0)) && iMSumItemNum < 1)
{
iMSumItemNum++;
gcQueryList.SummaryItem.FieldName = _dcQuery.ColumnName;
gcQueryList.SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
gvMainList.GroupSummary.Add(DevExpress.Data.SummaryItemType.Sum, _dcQuery.ColumnName, null, " 小计:{0}");
}
else if ((_dcQuery.DataType.ToString().ToUpper() == "SYSTEM.DECIMAL" || _dcQuery.DataType.ToString().ToUpper() == "SYSTEM.STRING") &&
((gcQueryList.Caption.IndexOf("序号") >= 0) || (gcQueryList.Caption.IndexOf("流水") >= 0)
|| (gcQueryList.Caption.IndexOf("ID") >= 0) || (gcQueryList.Caption.IndexOf("编号") >= 0)
|| (gcQueryList.Caption.IndexOf("箱号") >= 0) || (gcQueryList.Caption.IndexOf("尺寸") >= 0)
|| (gcQueryList.Caption.IndexOf("序") >= 0) || (gcQueryList.Caption.IndexOf("单号") >= 0)) && iMCountItemNum < 1)
{
iMCountItemNum++;
gcQueryList.SummaryItem.FieldName = _dcQuery.ColumnName;
gcQueryList.SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Count;
gvMainList.GroupSummary.Add(DevExpress.Data.SummaryItemType.Count, _dcQuery.ColumnName, null, " 小计:{0}");
}
if (gcQueryList.FieldName != null && RelaitionField != null && gcQueryList.FieldName.ToUpper() != RelaitionField.ToUpper())
{
gcQueryList.VisibleIndex = iVisible;
gcQueryList.Visible = true;
}
else
{
gcQueryList.VisibleIndex = -1;
gcQueryList.Visible = false;
}
//gcQueryList.ColumnEdit.ReadOnly = true;
gvMainList.Columns.Add(gcQueryList);
}
}
gcMainList.MainView = gvMainList;
}
catch (System.Exception ex)
{
MsgBox.ErrorMsg("执行查询失败! 失败原因:" + ex.Message);
}
#endregion
#region 子表构造字段
try
{
//获取到表结构。
if (_dtQuery.Tables["DE"].Rows.Count >= 0)
{
DevExpress.XtraGrid.Views.Grid.GridView gvMainDetail = new DevExpress.XtraGrid.Views.Grid.GridView();
// gvMainDetail.Columns.Clear();
// gvMainDetail.GroupSummary.Clear();
// gvMainDetail.ClearGrouping();
// gvMainDetail.OptionsBehavior.Editable = true;
int iVisible = 0;
//根据表结构动态加载到GridControl控件中。
foreach (DataColumn _dcQueryDetail in _dtQuery.Tables["DE"].Columns)
{
iVisible++;
//动态添加
DevExpress.XtraGrid.Columns.GridColumn gcDetialQueryList = new DevExpress.XtraGrid.Columns.GridColumn();
gcDetialQueryList.Name = "_dcD" + _dcQueryDetail.ColumnName.ToUpper();
gcDetialQueryList.Caption = _dcQueryDetail.ColumnName;
gcDetialQueryList.FieldName = _dcQueryDetail.ColumnName;
//数字类型
if ((_dcQueryDetail.DataType.ToString().ToUpper() == "SYSTEM.DECIMAL" || _dcQueryDetail.DataType.ToString().ToUpper() == "SYSTEM.DOUBLE") &&
((gcDetialQueryList.Caption.IndexOf("金额") >= 0) || (gcDetialQueryList.Caption.IndexOf("价格") >= 0)
|| (gcDetialQueryList.Caption.IndexOf("人民币") >= 0) || (gcDetialQueryList.Caption.IndexOf("美元") >= 0)
|| (gcDetialQueryList.Caption.IndexOf("单价") >= 0) || (gcDetialQueryList.Caption.IndexOf("个") >= 0)
|| (gcDetialQueryList.Caption.IndexOf("件") >= 0) || (gcDetialQueryList.Caption.IndexOf("箱") >= 0)
|| (gcDetialQueryList.Caption.IndexOf("数量") >= 0)) && iDSumItemNum < 1)
{
iDSumItemNum++;
gcDetialQueryList.SummaryItem.FieldName = _dcQueryDetail.ColumnName;
gcDetialQueryList.SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
gvMainDetail.GroupSummary.Add(DevExpress.Data.SummaryItemType.Sum, _dcQueryDetail.ColumnName, null, " 小计:{0}");
}
else if ((_dcQueryDetail.DataType.ToString().ToUpper() == "SYSTEM.DECIMAL" || _dcQueryDetail.DataType.ToString().ToUpper() == "SYSTEM.STRING") &&
((gcDetialQueryList.Caption.IndexOf("序号") >= 0) || (gcDetialQueryList.Caption.IndexOf("流水") >= 0)
|| (gcDetialQueryList.Caption.IndexOf("ID") >= 0) || (gcDetialQueryList.Caption.IndexOf("编号") >= 0)
|| (gcDetialQueryList.Caption.IndexOf("箱号") >= 0) || (gcDetialQueryList.Caption.IndexOf("尺寸") >= 0)
|| (gcDetialQueryList.Caption.IndexOf("序") >= 0) || (gcDetialQueryList.Caption.IndexOf("单号") >= 0)) && iDCountItemNum < 1)
{
iDCountItemNum++;
gcDetialQueryList.SummaryItem.FieldName = _dcQueryDetail.ColumnName;
gcDetialQueryList.SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Count;
gvMainDetail.GroupSummary.Add(DevExpress.Data.SummaryItemType.Count, _dcQueryDetail.ColumnName, null, " 小计:{0}");
}
if (gcDetialQueryList.FieldName != null && RelaitionField != null && gcDetialQueryList.FieldName.ToUpper() != RelaitionField.ToUpper())
{
gcDetialQueryList.VisibleIndex = iVisible;
gcDetialQueryList.Visible = true;
}
else
{
gcDetialQueryList.VisibleIndex = -1;
gcDetialQueryList.Visible = false;
}
//gcDetialQueryList.ColumnEdit.ReadOnly = true;
gvMainDetail.Columns.Add(gcDetialQueryList);
}
gcMainList.ViewCollection.Add(gvMainDetail);
}
}
catch (System.Exception ex)
{
MsgBox.ErrorMsg("执行查询失败! 失败原因:" + ex.Message);
}
#endregion
#region 建立关系并填充数据源
_dtQuery.Relations.Add("RMD", _dtQuery.Tables["MA"].Columns[RelaitionField], _dtQuery.Tables["DE"].Columns[RelaitionField], false);
gcMainList.DataSource = _dtQuery.Tables["MA"];
gvMainList.BestFitColumns();
#endregion
}
#endregion
效果如图:
