查询主、明细数据 构造两表主从关系实例

//******************** 查询事件DAL*****************//
  public DataSet GetBillByNo(string billNoFrom, string billNoTo)
  {
    DataSet ds = new DataSet();
    SqlDataAdapter sqlAdapter = new SqlDataAdapter();
    SqlConnection conn = null;
    int numKeywords = 2;
    string baseSql = DeptRequestSql.SQL_SELECT_KANJOUKAMOKU;
    string detailSql = DeptRequestSql.SQL_SELECT_MESSAGE1;
    try
    {
      conn = sqlHelper.DBConnect();
      // parameters
      SqlParameter[] baseParms = new SqlParameter[numKeywords];
      baseParms[0] = new SqlParameter("@SEIKYUSHOHAKKOUIRAINOFROM", SqlDbType.VarChar);
      baseParms[0].Value = billNoFrom;
      baseParms[1] = new SqlParameter("@SEIKYUSHOHAKKOUIRAINOTO", SqlDbType.VarChar);
      baseParms[1].Value = billNoTo;
      sqlAdapter.SelectCommand = sqlHelper.PrepareCommand(conn, null, CommandType.Text, baseSql, baseParms);
      sqlAdapter.Fill(ds, typeof(MST_KANJOUKAMOKU).Name);
      SqlParameter[] detailParms = CommUtility.CloneParms(baseParms);
      sqlAdapter.SelectCommand = sqlHelper.PrepareCommand(conn, null, CommandType.Text, detailSql, detailParms);
      sqlAdapter.Fill(ds, typeof(OPT_SEIKYUUSHOHAKKOUIRAISHO).Name);
      // Relation
      DataColumn parentColumn = ds.Tables[typeof(MST_KANJOUKAMOKU).Name].Columns[MST_KANJOUKAMOKU.KANJOUKAMOKUCODE.ToString()];
      DataColumn childColumn = ds.Tables[typeof(OPT_SEIKYUUSHOHAKKOUIRAISHO).Name].Columns[OPT_SEIKYUUSHOHAKKOUIRAISHO.KANJOUKAMOKUCODE.ToString()];
      DataRelation rel = new DataRelation("relation", parentColumn, childColumn);
      ds.Relations.Add(rel);
    }
    catch (Exception exc)
    {
      throw new SakaiException(MessageConst.STR_EXC_GET_BILL_LOST, exc);
    }
    finally
    {
      sqlAdapter.Dispose();
      sqlHelper.DBClose(conn);
    }
    return ds;
  }

// *********************页面获取***************************//
  // 主表获取
  DataRow baseDr = ds.Tables[typeof(MST_KANJOUKAMOKU).Name].Rows[currentPage - 1];
  // 主表数据封装
  SetBaseData(baseDr);
  // 明细表获取
  DataRow[] detailDrs = baseDr.GetChildRows(ds.Relations[0]);
  // 创建明细表
  DataTable dtBind = CommUtility.DataRowsToDataTable(detailDrs, ds.Tables[typeof(OPT_SEIKYUUSHOHAKKOUIRAISHO).Name]);
  // 明细表封装
  SetDetailData(dtBind);
//*****************************************************//
public static DataTable DataRowsToDataTable(DataRow[] drs, DataTable dt)
  {
    DataTable dtNew = dt.Clone();
    foreach (DataRow dr in drs)
    {
      dtNew.ImportRow(dr);
    }
    return dtNew;
  }
//*****************************************//
public static SqlParameter[] CloneParms(SqlParameter[] parms)
  {
    SqlParameter[] parmsNew = new SqlParameter[parms.Length];
    for (int i = 0; i < parms.Length; i++)
    {
    parmsNew = (SqlParameter)((ICloneable)parms).Clone();
    }
    return parmsNew;
  }
//***********************************************//

转载于:https://www.cnblogs.com/xinxinbb/archive/2008/08/13/1266817.html