在项目中经常会写 获取数据的方法,每个方法都要写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
分别是三个参数。