.net in-line script 做过的一个查询页，记录一下

1. 今天TOP 20 
2. 某一会员某一天统计 
3. 会员所有天记录统计

<script runat =\"server\">
void Page_Load(object sender, EventArgs e) {
 //查询时间默认为今天
 if(!Page.IsPostBack){
  txtReportTime.Text = System.DateTime.Today.ToString();
  bindGrid();
 }
 else{
  //开始进行查询
  if(ddlReportType.SelectedValue == \"0\"){//0为今天TOP20
   //if(QueryControls.Visible){//首进为今天天TOP20，隐藏查询条件面板
    QueryControls.Visible = false;
    QueryControls02.Visible = false;
   //}
  }
  bindGrid();
 }
}
void bindGrid(){
  System.Data.IDataReader dr;
  dr = MyQueryMethod();
  dgDotReport.DataSource = dr;
  dgDotReport.DataBind();
}
void disposeGrid(){
 //清掉datagrid
 dgDotReport.Dispose();
 dgDotReport.DataSource = \"\";
 dgDotReport.DataBind();
}
System.Data.IDataReader MyQueryMethod() {
string connectionString = \"server=/\'(local)/\'; user id=/\'sa/\'; password=/\'kemin%@)9999/\'; database=/\'diligencexxx/\'\";
System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
string queryString;
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
if(ddlReportType.SelectedValue == \"0\"){//默认今天TOP 20 
 queryString = \"SELECT TOP 20 incept AS \'会员\', u.usename AS [昵称], \" +
 \"[1朵鲜花] = SUM(CASE lpid WHEN \'5F016DD5-7C82-4155-A8A7-32EB3430B359\' THEN num ELSE 0 END),\" +
 \"[9朵鲜花]  = SUM(CASE lpid WHEN \'3BF9C5CC-4380-44FF-8488-AF20C8964FD4\' THEN num ELSE 0 END),\" +
 \"[99朵鲜花] = SUM(CASE lpid WHEN \'171BCE1A-DAC0-4898-894B-0EAAF9FE12B0\' THEN num ELSE 0 END),\" +
 \"[钻戒] = SUM(CASE lpid WHEN \'7F179E09-ADDA-4250-9DEA-249E045FE2C5\' THEN num ELSE 0 END),\" +
 \"[别墅] = SUM(CASE lpid WHEN \'E32870E9-EE54-44A7-A9FD-853C9BA03D58\' THEN num ELSE 0 END),\" +
 \"[总点值] = SUM(CASE lpid WHEN \'5F016DD5-7C82-4155-A8A7-32EB3430B359\' THEN num ELSE 0 END)*10 + SUM(CASE lpid WHEN \'3BF9C5CC-4380-44FF-8488-AF20C8964FD4\' THEN num ELSE 0 END)*80 + SUM(CASE lpid WHEN \'171BCE1A-DAC0-4898-894B-0EAAF9FE12B0\' THEN num ELSE 0 END) * 500 + SUM(CASE lpid WHEN \'7F179E09-ADDA-4250-9DEA-249E045FE2C5\' THEN num ELSE 0 END)*3000 + SUM(CASE lpid WHEN \'E32870E9-EE54-44A7-A9FD-853C9BA03D58\' THEN num ELSE 0 END)*8000\" +
 \"FROM gift_incept g \" +
 \"INNER JOIN use_userinfo u \" +
 \"ON g.incept = u.id\" +
 \" WHERE indate BETWEEN \'\"+ DateTime.Today.ToString() +\"\' AND \'\"+ DateTime.Today.AddDays(1).ToString() +\"\'\" +//.NET 时间
 \" GROUP BY incept, u.usename \" +
 \" ORDER BY [总点值] DESC\";
}else{
 string sReportTime;
 bool bReportOneDay;
 string sMemberId;
 //统计某一天，或所有天
 if(ddlReportType.SelectedValue == \"1\"){
  sReportTime = \" AND indate BETWEEN @theDate AND @theDateAfter\";
  bReportOneDay = true;
 }else{
  sReportTime = \" \";
  bReportOneDay = false;
 }
 
 disposeGrid();
 //打开查询条件面板
 if(ddlReportType.SelectedValue == \"1\"){
  QueryControls.Visible = true;
  QueryControls02.Visible = false;
  sMemberId = txtMemberId.Text;
 }else{
  QueryControls02.Visible = true;
  QueryControls.Visible = false;
  sMemberId = txtMemberId02.Text;
 }
 
 queryString = \"SELECT incept AS \'会员\', u.usename AS [昵称], \" +
 \"[1朵鲜花] = SUM(CASE lpid WHEN \'5F016DD5-7C82-4155-A8A7-32EB3430B359\' THEN num ELSE 0 END),\" +
 \"[9朵鲜花]  = SUM(CASE lpid WHEN \'3BF9C5CC-4380-44FF-8488-AF20C8964FD4\' THEN num ELSE 0 END),\" +
 \"[99朵鲜花] = SUM(CASE lpid WHEN \'171BCE1A-DAC0-4898-894B-0EAAF9FE12B0\' THEN num ELSE 0 END),\" +
 \"[钻戒] = SUM(CASE lpid WHEN \'7F179E09-ADDA-4250-9DEA-249E045FE2C5\' THEN num ELSE 0 END),\" +
 \"[别墅] = SUM(CASE lpid WHEN \'E32870E9-EE54-44A7-A9FD-853C9BA03D58\' THEN num ELSE 0 END),\" +
 \"[总点值] = SUM(CASE lpid WHEN \'5F016DD5-7C82-4155-A8A7-32EB3430B359\' THEN num ELSE 0 END)*10 + SUM(CASE lpid WHEN \'3BF9C5CC-4380-44FF-8488-AF20C8964FD4\' THEN num ELSE 0 END)*80 + SUM(CASE lpid WHEN \'171BCE1A-DAC0-4898-894B-0EAAF9FE12B0\' THEN num ELSE 0 END) * 500 + SUM(CASE lpid WHEN \'7F179E09-ADDA-4250-9DEA-249E045FE2C5\' THEN num ELSE 0 END)*3000 + SUM(CASE lpid WHEN \'E32870E9-EE54-44A7-A9FD-853C9BA03D58\' THEN num ELSE 0 END)*8000\" +
 \" FROM gift_incept g \" +
 \" INNER JOIN use_userinfo u \" +
 \" ON g.incept = u.id\" +
 \" WHERE incept = @MemberId \" +
 sReportTime +
 \" GROUP BY incept, u.usename \" +
 \" ORDER BY [总点值] DESC\";
 //MemberId
 System.Data.IDataParameter dbParam_MemberId = new System.Data.SqlClient.SqlParameter();
 dbParam_MemberId.ParameterName = \"@MemberId\";
 dbParam_MemberId.Value = sMemberId;
 dbParam_MemberId.DbType = System.Data.DbType.String;
 dbCommand.Parameters.Add(dbParam_MemberId);
 if(bReportOneDay){
  //theDate
  System.Data.IDataParameter dbParam_theDate = new System.Data.SqlClient.SqlParameter();
  dbParam_theDate.ParameterName = \"@theDate\";
  DateTime thDate = Convert.ToDateTime(txtReportTime.Text);
  dbParam_theDate.Value = thDate;
  dbParam_theDate.DbType = System.Data.DbType.Date;
  dbCommand.Parameters.Add(dbParam_theDate);
  //theDateAfter
  System.Data.IDataParameter dbParam_theDateAfter = new System.Data.SqlClient.SqlParameter();
  dbParam_theDateAfter.ParameterName = \"@theDateAfter\";
  DateTime thDateAfter = new DateTime();
  thDateAfter = thDate.AddDays(1);
  dbParam_theDateAfter.Value = thDateAfter;
  dbParam_theDateAfter.DbType = System.Data.DbType.Date;
  dbCommand.Parameters.Add(dbParam_theDateAfter);
 }
}

dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;
dbConnection.Open();
System.Data.IDataReader dataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

return dataReader;
}
///处理从日历读取时间数据
private void fillReportTime(object sender, System.EventArgs e)
{
 txtReportTime.Text = calReportTime.SelectedDate.ToString();
 calReportTime.Visible = false;
 //disposeGrid();
}
private void showCalendar(object sender, System.EventArgs e){
 calReportTime.Visible = true;
 disposeGrid();
}
</script>