将多个数据集 写在一个存储过程中统一调用（性能优化）

在项目中经常会写 获取数据的方法，每个方法都要写sql，而如下这样写，每次调用都会调用一次数据库，效率和性能低，同时也不利于维护，修改起来十分地麻烦。

如下有七个程序集。思考：如何优化？

 //获取DataTable T1
        private DataTable ExcelGrid(string F_DelegationID)
        {
            string sql = "select Row_Number() over ( order by getdate() ) as init ,_des.F_LongName as 姓名 ,_desEng.F_LongName as 英文名, reg.F_FormerName as 曾用名,case reg.F_GenderID when '1' then '男' when '2' then '女' else '其他' end as 性别, convert(char,reg.F_Birth_Date,112) as 出生日期, F_IdNO as 身份证号,  dele.F_DelegationLongName as 运动员所属单位, convert(char,reg.F_AgreementBegin,112) as 协议起始时间,convert(char,reg.F_AgreementEnd,112) as 协议终止时间  ,reg.F_RegisteType as 注册类型  from TR_Register reg  left join  TR_Register_Des _des on _des.F_RegisterID=reg.F_RegisterID left join TR_Register_Des _desEng on _desEng.F_RegisterID=reg.F_RegisterID   left join TC_Delegation_Des dele on dele.F_DelegationID=reg.F_DelegationID   where _des.F_LanguageCode='CHN' AND _desEng.F_LanguageCode='ENG' AND reg.F_DelegationID = '" + F_DelegationID + "' and reg.F_RegisterID in (select DISTINCT F_RegisterID from (select F_RegisterID from  TR_RegFillFileLog where F_Target in('首次注册','年度注册') union select  F_RegisterID from TR_PreFillLog) a)";
            DataTable allDelegation = SqlHelper.GetDataTable(sql);
            return allDelegation;
        }
        //获取DataTableT1——2
        private DataTable ExcelGridTY(string F_DelegationID)
        {
            string sql = "select Row_Number() over ( order by getdate() ) as init ,_des.F_LongName , case reg.F_GenderID  when '1' then '男' when '2' then '女' else '其他' end as 性别,convert(char,reg.F_Birth_Date,112) as 出生日期, F_IdNO as 身份证号  from  TR_Register reg left join TR_Register_Des _des on _des.F_RegisterID=reg.F_RegisterID  where _des.F_LanguageCode='CHN' AND reg.F_DelegationID = '" + F_DelegationID + "' and  reg.F_InitCoach is not null and reg.F_InitCoach!=''";
            DataTable allDelegation = SqlHelper.GetDataTable(sql);
            return allDelegation;
        }
        //获取DataTableT1-3
        private DataTable ExcelGridTJL(string F_DelegationID)
        {
            string sql = "select Row_Number() over ( order by getdate() ) as init ,_des.F_LongName ,reg.F_FormerName as 曾用名, case reg.F_GenderID when '1' then '男'when '2' then '女'else '其他' end as 性别,convert(char,reg.F_Birth_Date,112) as 出生日期, reg.F_IdNO as 身份证号,dele.F_DelegationLongName as 原单位,dele.F_DelegationLongName as 目标单位, convert(char,fill.F_NewStartDate,112) as 交流起始时间 , convert(char,fill.F_NewEndDate,112) as 交流终止时间  from  TR_Register reg  left join TR_Register_Des _des on _des.F_RegisterID=reg.F_RegisterID  left join TC_Delegation_Des dele on dele.F_DelegationID=reg.F_DelegationID left join TR_FillOnlineBaseInfo fill on fill.F_RegisterID=reg.F_RegisterID where _des.F_LanguageCode='CHN' AND  reg.F_DelegationID = '" + F_DelegationID + "'   and reg.F_RegisterID in (select DISTINCT F_RegisterID from (select F_RegisterID from  TR_RegFillFileLog where F_Target ='交流注册'  union select  F_RegisterID from TR_PreFillLog) a)";
            DataTable allDelegation = SqlHelper.GetDataTable(sql);
            return allDelegation;
        }

        //获取DataTable T2
        private DataTable ExcelGrid2(string F_DelegationID)
        {
            string sql = " select Row_Number() over ( order by getdate() ) as init  ,_des.F_LongName as 姓名 ,_desEng.F_LongName as 英文名 ,reg.F_FormerName as 曾用名,case reg.F_GenderID when '1' then '男' when '2' then '女' else '其他' end as 性别,convert(char,reg.F_Birth_Date,112) as 出生日期,  F_IdNO as 身份证号,  dele.F_DelegationLongName as 运动员所属单位,  convert(char,reg.F_AgreementBegin,112) as 协议起始时间,convert(char,reg.F_AgreementEnd,112) as 协议终止时间  ,reg.F_RegisteType as 注册类型  from TR_Register reg  left join TR_Register_Des _desEng on _desEng.F_RegisterID=reg.F_RegisterID   left join TR_Register_Des _des on _des.F_RegisterID=reg.F_RegisterID   left join TC_Delegation_Des dele on dele.F_DelegationID=reg.F_DelegationID   where _des.F_LanguageCode='CHN' AND _desEng.F_LanguageCode='ENG' AND reg.F_DelegationID = '" + F_DelegationID + "' and reg.F_RegisterID in (select DISTINCT F_RegisterID from (select F_RegisterID from  TR_RegFillFileLog where F_Target in('首次备案','年度备案') union select  F_RegisterID from TR_PreFillLog) a)";
            DataTable allDelegation = SqlHelper.GetDataTable(sql);
            return allDelegation;
        }
        //获取DataTableT2-2
        private DataTable ExcelGridTY2(string F_DelegationID)
        {
            string sql = "select Row_Number() over ( order by getdate() ) as init ,_des.F_LongName , case reg.F_GenderID  when '1' then '男' when '2' then '女' else '其他' end as 性别,convert(char,reg.F_Birth_Date,112) as 出生日期, F_IdNO as 身份证号  from  TR_Register reg left join TR_Register_Des _des on _des.F_RegisterID=reg.F_RegisterID  where _des.F_LanguageCode='CHN' AND reg.F_DelegationID = '" + F_DelegationID + "' and  reg.F_InitCoach is not null and reg.F_InitCoach!=''";
            DataTable allDelegation = SqlHelper.GetDataTable(sql);
            return allDelegation;
        }
        //获取DataTableT2  -3
        private DataTable ExcelGridTJL2(string F_DelegationID)
        {
            string sql = "select Row_Number() over ( order by getdate() ) as init ,_des.F_LongName  ,reg.F_FormerName as 曾用名, case reg.F_GenderID when '1' then '男'when '2' then '女'else '其他' end as 性别,convert(char,reg.F_Birth_Date,112) as 出生日期, reg.F_IdNO as 身份证号,dele.F_DelegationLongName as 原单位,dele.F_DelegationLongName as 目标单位,  convert(char,fill.F_NewStartDate,112) as 交流起始时间 , convert(char,fill.F_NewEndDate,112) as 交流终止时间  from  TR_Register reg  left join TR_Register_Des _des on _des.F_RegisterID=reg.F_RegisterID  left join TC_Delegation_Des dele on dele.F_DelegationID=reg.F_DelegationID left join TR_FillOnlineBaseInfo fill on fill.F_RegisterID=reg.F_RegisterID where _des.F_LanguageCode='CHN' AND  reg.F_DelegationID = '" + F_DelegationID + "'   and reg.F_RegisterID in (select DISTINCT F_RegisterID from (select F_RegisterID from  TR_RegFillFileLog where F_Target ='交流备案'  union select  F_RegisterID from TR_PreFillLog) a)";
            DataTable allDelegation = SqlHelper.GetDataTable(sql);
            return allDelegation;
        }
        //获取DataTableT3
        private DataTable ExcelGridTH(string F_DelegationID)
        {
            string sql = "select Row_Number() over ( order by getdate() ) as init , _des.F_LongName  , case reg.F_GenderID when '1' then '男'when '2' then '女'else '其他' end as 性别, convert(char,reg.F_Birth_Date,112) as 出生日期, reg.F_IdNO as 身份证号,le.F_LevelDes as 职称,reg.F_Mobile, reg.F_RegisteType as 类别  from  TR_Register reg  left join TC_CoachLevel le on reg.F_LevelID=le.F_LevelID   left join TR_Register_Des _des on _des.F_RegisterID=reg.F_RegisterID    left join TR_Career  ca on ca.F_RegisterID=reg.F_RegisterID  where  _des.F_LanguageCode='CHN' and reg.F_DelegationID = '" + F_DelegationID + "'  and reg.F_RegisterID in (select DISTINCT F_RegisterID from (select F_RegisterID from  TR_RegFillFileLog   union select  F_RegisterID from TR_PreFillLog) a)";
            DataTable allDelegation = SqlHelper.GetDataTable(sql);
            return allDelegation;
        }

如何优化！

创建一个存储过程： Proc_GetRegExcelDownLoad 

**将存储过程依次写在上面：

USE [TestBD]
GO
--定义的三个参数
    @IsSummary int,
	@DelegationID int,
	@Year int
as
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[Proc_GetRegExcelDownLoad]
  
BEGIN

SET NOCOUNT OFF;
 
     select * from  A1

     select * from  A2
 
     select * from  A3
 
     select * from  A4
 
     select * from  A5
 
     select * from  A6
 
     select * from  A7
END

 c# 使用存储过程：

            SqlParameter[] para = new SqlParameter[] {
                new SqlParameter("@DelegationID",addRegFillFileLog.DelegationID),
                new SqlParameter("@IsSummary",2),
                new SqlParameter("@Year",addRegFillFileLog.Year),
    };
            DataSet ds = SqlHelper.BuildIntCommand("Proc_GetRegExcelDownLoad", para);

        DataTable dt0 = ds.Tables[0];
        DataTable dt1 = ds.Tables[1];
        DataTable dt2 = ds.Tables[2];
        DataTable dt3 = ds.Tables[3];
        DataTable dt4 = ds.Tables[4];
        DataTable dt5 = ds.Tables[5];
        DataTable dt6 = ds.Tables[6]; 
  

不仅好维护，还方便发布。

 

调试存储过程：

 Proc_GetRegExcelDownLoad 1,2,2020

分别是三个参数。