面向过程的.NET(一工作代码)

.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>