![]() |
#2
gameohyes2010-05-21 09:02
|
方法(1) private DataTable getMainChildTable(DataTable dt,out int maxNum)
{
DataTable table = new DataTable();
table.Columns.Add("月份", typeof(String));
table.Columns.Add("年龄阶段",typeof(string));
table.Columns.Add("月内增加值", typeof(Double));
maxNum = 0;
foreach (DataRow drr in dt.Rows)
{
DataRow dr = table.NewRow();
dr["月份"] = "";
dr["年龄阶段"] = "";
dr["月内增加值"] = 0;
if (int.Parse(drr["allcount"].ToString()) > maxNum)
{
maxNum = int.Parse(drr["allcount"].ToString());
}
switch (drr["BirthdayOption"].ToString())
{
case "30岁以下":
dr["月份"] = drr["M"]+"月";
dr["年龄阶段"] = "30岁以下";
dr["月内增加值"] = drr["allcount"];
break;
case "30-60岁":
dr["月份"] = drr["M"]+"月";
dr["年龄阶段"] = "30-60岁";
dr["月内增加值"] = drr["allcount"];
break;
case "60岁以上":
dr["月份"] = drr["M"]+"月";
dr["年龄阶段"] = "60岁以上";
dr["月内增加值"] = drr["allcount"];
break;
case "未知年龄":
dr["月份"] = drr["M"]+"月";
dr["年龄阶段"] = "未知年龄";
dr["月内增加值"] = drr["allcount"];
break;
}
table.Rows.Add(dr);
}
return table;
}
方法 (2)private DataTable getMainTable(out int maxNum)
{
string startDate = txtCheckDateStart.Value;
string organizationId = manageMHRA.SelectedCategoryId;
StringBuilder bStrSql = new StringBuilder();
bStrSql.Append("select ");
bStrSql.Append(" Case when DATEDIFF(YEAR,Bithday,GETDATE())<30 then '30岁以下' ");
bStrSql.Append(" when DATEDIFF(YEAR,Bithday,GETDATE())>30 And DATEDIFF(YEAR,Bithday,GETDATE())<60 then '30-60岁' ");
bStrSql.Append(" when DATEDIFF(YEAR,Bithday,GETDATE())>60 then '60岁以上' else '未知年龄' end BirthdayOption ,COUNT(0) allcount, ");
bStrSql.Append(" SUBSTRING(convert(char(10),TIH.IllDate,112) ,0,7) as M ");
bStrSql.Append(" from T_IllOldHistory TIH inner join T_FamilyMember ");
bStrSql.Append(" on TIH.MemberId=T_FamilyMember.MemberId where 1=1 and ClassifyId= 'b25f19ad5fd54b4eb88e0444c1f50400' and BlDefinite=1 ");
if (!string.IsNullOrEmpty(startDate))
{
bStrSql.AppendFormat(" and datediff(year,TIH.IllDate,'{0}')=0", DateTime.ParseExact (startDate, "yyyy", System.Globalization.CultureInfo.CurrentCulture).ToString("yyyy-MM-dd"));
}
if (!string.IsNullOrEmpty(organizationId))
{
bStrSql.AppendFormat(" and Exists (select 1 from OrganizationCategory WHERE T_FamilyMember.OCategoryId = OrganizationCategory.CategoryId AND CategoryNumber LIKE (Select CategoryNumber + '%' FROM OrganizationCategory WHERE CategoryId='{0}' and DeletionStateCode=0)) ", organizationId);
}
bStrSql.Append(" group by SUBSTRING(convert(char(10),TIH.IllDate,112) ,0,7) , Case when DATEDIFF(YEAR,Bithday,GETDATE())<30 then '30岁以下' when DATEDIFF(YEAR,Bithday,GETDATE())>30 And DATEDIFF(YEAR,Bithday,GETDATE())<60 then '30-60岁' ");
bStrSql.Append(" when DATEDIFF(YEAR,Bithday,GETDATE())>60 then '60岁以上' else '未知年龄' end order by M ASC,BirthdayOption asc");
DataTable dt = GSP.(bStrSql.ToString());//在这如果dt为空时,怎样不让它报错(如果没有数据出现溢出错误)???
return getMainChildTable(dt, out maxNum);//调用方法(一)
}
方法(2)返回getMainChildTable(DataTable ,out maxNum)
当方法二中的数据为空时想提示用户以下(不让它报错)