公用代码实现两个表的拼接（部分代码）

来源：互联网发布：超人软件破解下载编辑：程序博客网时间：2024/05/16 12:27

//上面的则是执行的时候需要用到的一些内容，但它不是公用的方法，所以只是看看里面的参数的传值问题，不用考虑太多的内容问题
/// <summary>
        /// 获取需要加载的拟一级项目信息
        /// </summary>
        /// <returns></returns>
        private DataSet GetDataTable(string ProjectId)
        {
            DataSet ds = new DataSet();

            string conditionField = string.Empty;
            string conditionSql = string.Empty;
            //string ProjectId = this.hiProjectID.Value;

            //如果显示本级信息
            if (IsShowSelf)
            {
                conditionField = "BudgetProjectName";
            }
            else
            {
                conditionField = "ParentProjectName";
            }

            //为了避免存在相同名称的预算项目名称，以父级区分一下（但还存在问题如果父级名称也一样，会查出多条数据）
            if (!string.IsNullOrEmpty(ParentBudgetProjectName))
            {
                conditionSql += string.Format(" AND ParentProjectName='{0}'", ParentBudgetProjectName);
            }

            string sqlSelect = string.Format(@"DECLARE @FirstBudgetProjectEntityID nvarchar(36);
                                 DECLARE @Count INT
                                 SELECT @Count=COUNT(*) FROM BMIS_ProjectBudgetYearForm WHERE ProjectEntityId='{0}'
                                 IF @Count>0
                                 BEGIN
                                 ---查询项目对应的一级预算项目应该对应的执行ID(数据表中没有此数据)
                                 SET @FirstBudgetProjectEntityID=(
                                 SELECT TOP 1(FirstParentBudgetProjectExecuteEntityID) FROM table1 WHERE ProjectEntityId='{0}')

                                 SELECT BudgetProjectExecuteEntityID FROM USV_ProjectBudgetExe WHERE {3} IN ({1}) AND ProjectLevel='{2}' AND FirstParentBudgetProjectExecuteEntityID=@FirstBudgetProjectEntityID {4}
                                 SELECT @FirstBudgetProjectEntityID
                                 SELECT *,ParentExeID AS ParentID,NULL AS MonthExeValue,NULL YExeValue,NULL YAllExeValue FROM USV_ProjectBudgetExe WHEREFirstParentBudgetProjectExecuteEntityID=@FirstBudgetProjectEntityID
                                 ----查询执行的月度信息
                                 END
                                 ELSE
                                 BEGIN
                                 SELECT TOP 0(BudgetProjectExecuteEntityID) FROM USV_ProjectBudgetExe
                                 SELECT @FirstBudgetProjectEntityID
                                 SELECT TOP 0*,NULL as ParentID,NULL AS MonthExeValue,NULL YExeValue,NULL YAllExeValue FROM USV_ProjectBudgetExe
                                 END
                                  ", ProjectId, BudgetProjectName, ProjectLevel, conditionField, conditionSql);

            try
            {
                PortalDB.LoadDataSet(CommandType.Text, sqlSelect, ds, new string[] { "ShowBudgetInfo", "FirstBudgetProjectEntity", "ExeData" });
            }
            catch (Exception ex)
            {
                PortalLogging.HandleException(ex);
                ShowAlertMessage(ex.Message.ToString());
            }

return ds;
}

//下面为公用的一些代码的内容

/// <summary>
        /// 获取需要绑定的datatable dt1 dt2
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public DataTable GetBindDatatable(string ProjectId)
        {
            DataRow rowYear = InitYearInfo();
            DataTable dt = new DataTable();

DataSet dsProjectInfo = GetDataTable(ProjectId);

            string budgetExeID = string.Empty;
            DataTable dtShowProjectInfo = dsProjectInfo.Tables["ShowBudgetInfo"];
            DataTable dtFirst = dsProjectInfo.Tables["FirstBudgetProjectEntity"];
            DataTable dtExeData = dsProjectInfo.Tables["ExeData"];
            string parentExeID = string.Empty;

            foreach (DataRow row in dtShowProjectInfo.Rows)
            {
                parentExeID += string.Format("'{0}',", row["BudgetProjectExecuteEntityID"].ToString());
                budgetExeID += GetLowerBudgetInfoID(row["BudgetProjectExecuteEntityID"].ToString(), "'" + row["BudgetProjectExecuteEntityID"].ToString() + "',", dtExeData);
            }

            if (!string.IsNullOrEmpty(budgetExeID))
            {
                budgetExeID = budgetExeID.TrimEnd(',');
                parentExeID = parentExeID.TrimEnd(',');
                hiParentExeID.Value = parentExeID;

                //由于查询的预算科目不一定是一级预算科目，即ParentExeID 有不为NULL的情况
                //树的一级的ParenExeID必须为NULL,手动将ParentExeID不为NULL的数据置为NULL
                string sqlSelect = string.Format(@"
                                                ----项目预算信息
                                                SELECT Temp.*,NULL AS Project2ExeValue,NULL AS Rate,
                                                ----本年执行数
                                                ISNULL((SELECT SUM(ISNULL(MExeValue,0)) FROM table2 D WHERE D.BudgetProjectExecuteEntityID=Temp.BudgetProjectExecuteEntityID AND [Year]='{2}'),0) AS YExeValue
                                                FROM (
                                                SELECT *,NULL AS ParentID,ISNULL(Y,1) AS TotalValue FROM table3 WHERE BudgetProjectExecuteEntityID IN ({1})
                                                UNION
                                                SELECT *,ParentExeID AS ParentID,ISNULL(Y,1) AS TotalValue FROM table4 WHERE BudgetProjectExecuteEntityID IN ({0}) AND BudgetProjectExecuteEntityID NOT IN ({1})) Temp ORDER BY SortIndex
                                                ", budgetExeID, parentExeID, ddlYear.SelectedValue);

dt = PortalDB.ExecuteDataSet(CommandType.Text, sqlSelect).Tables[0];

                hiFirstExeID.Value = dtFirst.Rows[0][0].ToString();
            }
            return dt;
        }

//以下为绑定表1 dt1和表2 dt2
/// <summary>
        /// 绑定项目费用科目信息
        /// </summary>
        public void ProjectDataBind()
        {
            //暂未用到
            DataRow rowYear = InitYearInfo();

DataTable dt1 = GetBindDatatable(hiProjectID.Value);
DataTable dt2 = GetBindDatatable(hiProjectID1.Value);

//尤其是这里，里面的遍历的代码不太懂，所以应该仔细的看看
            foreach (DataRow dr in dt1.Rows)
            {
                object budgetProjectName = dr["BudgetProjectName"];
                DataRow[] selRows = dt2.Select(string.Format("BudgetProjectName='{0}'", budgetProjectName));
                if (selRows.Length > 0)
                {
                    dr["Project2ExeValue"] = selRows[0]["YExeValue"];

                }
                Decimal Project2Rate = Convert.ToDecimal(dr["YExeValue"]);
                Decimal rate1 = Convert.ToDecimal(dr["Project2ExeValue"]);

object rate = dr["Rate"];

                if (Project2Rate != 0)
                {
                    rate = (rate1 - Project2Rate) / Project2Rate;
                    Convert.ToDecimal(rate);
                }

else
{

                }
                dr["Rate"] = rate;
            }
            tgvProjectBudgetProject.UseDefaultDataSource = true;
            tgvProjectBudgetProject.DataSource = dt1;
            tgvProjectBudgetProject.PagingData();

}