面向过程的.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>