通过存储过程实现报表复杂查询并以Excel格式输出

Posted on 2005-12-11 20:54 Aaron Chan 阅读(842) 评论(2)  编辑 收藏 网摘
本模块实现功能：用户自定义投诉报表查询并以Excel格式输出。

用户所自定义的查询条件有地点，县市，乡镇，网络（GSM,CDMA），日期以及查询类型，分别由以下变量纪录：
 1string address,city,town;
 2 int netid;
 3 string dateString1,dateString2;
 4 int queryStyle=0;
 5 address=TextBox1.Text.ToString().Trim();
 6 city=TextBox2.Text.ToString().Trim();
 7 town=TextBox3.Text.ToString().Trim();
 8 netid=DropDownList1.SelectedIndex-1;
 9 dateString1=this.date1.Text.ToString().Trim();
10dateString2=this.date2.Text.ToString().Trim();
11 if(RadioButton2.Checked==true)
12      queryStyle=1;
接着把这些参数传给存储过程sp_customReport，存储过程根据参数的不同值选择不同的查询，sql server中存储过程的代码：
 1CREATE      procedure sp_customReport
 2@address varchar(100),@city varchar(20),@town varchar(20),
 3@netid int,@queryStyle int,
 4@datestring1 varchar(20),@datestring2 varchar(20)
 5as
 6declare @sqlstring varchar(700)
 7if @queryStyle=0
 8begin
 9  select @sqlstring='select ad.messageid  as 编号,ad.address as 地点,ad.city as 县市,ad.town as 乡镇,ad.netid as 网络,cast(ad.netinfo as varchar) as 现网情况,cast(ad.explain as varchar) as 处理情况,cast(ad.appended as varchar) as 备注,count(ap.addtime) as 投诉次数 from address as ad inner join addpoint as ap on ad.messageid=ap.messageid where ad.address like '''
10+'%'+@address+'%'+''' and ad.city like '''+'%'+@city+'%'+'''and ad.town like '''+'%'+@town+'%'''
11  if @netid!=-1
12    select @sqlstring=@sqlstring+' and ad.netid='+str(@netid)
13  select @sqlstring=@sqlstring+' and ap.addtime between '''+convert(varchar(10),@datestring1,120)+''' and '''+convert(varchar(10),dateadd(day,1,@datestring2),120)
14    +''' group by ad.messageid,ad.address,ad.city,ad.town,ad.netid,cast(ad.netinfo as varchar),cast(ad.explain as varchar),cast(ad.appended as varchar)'
15end
16else
17 
18begin
19 select @sqlstring='select ad.messageid  as 编号,ad.address as 地点,ad.city as 县市,ad.town as 乡镇,ad.netid as 网络,ad.netinfo as 现网情况,ad.explain as 处理情况,ad.appended as 备注,ap.addtime as 投诉时间 from address as ad inner join addpoint as ap on ad.messageid=ap.messageid where ad.address like '''
20+'%'+@address+'%'+''' and ad.city like '''+'%'+@city+'%'+'''and ad.town like '''+'%'+@town+'%'''
21  if @netid!=-1
22    select @sqlstring=@sqlstring+' and ad.netid='+str(@netid)
23  select @sqlstring=@sqlstring+' and ap.addtime between '''+convert(varchar(10),@datestring1,120)+''' and '''+convert(varchar(10),dateadd(day,1,@datestring2),120)+''''
24end
25exec(@sqlstring)
26
27GO
28

需要注意的是：1，ntext,text类型字段不可以进行分组查询，需要用cast()进行格式转化；2，同一功能的查询尽量写在同一个存储过程中，比通过C#进行条件分类判断再调用几个存储过程清晰。

这样，编写ASP.NET代码时就编得清晰多了，不需要进行任何条件判断，直接把页面取得的参数传给存储过程执行就OK了：
 1try
 2            {
 3                //SqlConnection myConnection = new SqlConnection("server=aaron;database=dms;uid=sa;pwd=116596");
 4                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["MyConn"].ToString().Trim());
 5                SqlCommand mycommand = new SqlCommand("sp_customReport", myConnection);
 6                mycommand.CommandType = CommandType.StoredProcedure;
 7                mycommand.Connection=myConnection;
 8                mycommand.Parameters.Add("@address", SqlDbType.Char);
 9                mycommand.Parameters["@address"].Value = address;
10                mycommand.Parameters.Add("@city", SqlDbType.Char);
11                mycommand.Parameters["@city"].Value = city;
12                mycommand.Parameters.Add("@town", SqlDbType.Char);
13                mycommand.Parameters["@town"].Value = town;
14                mycommand.Parameters.Add("@netid", SqlDbType.Int);
15                mycommand.Parameters["@netid"].Value = netid;
16                mycommand.Parameters.Add("@datestring1", SqlDbType.Char);
17                mycommand.Parameters["@datestring1"].Value = dateString1;
18                mycommand.Parameters.Add("@datestring2", SqlDbType.Char);
19                mycommand.Parameters["@datestring2"].Value = dateString2;
20                mycommand.Parameters.Add("@queryStyle", SqlDbType.Int);
21                mycommand.Parameters["@queryStyle"].Value = queryStyle;
22                DataSet ds=new DataSet();
23                //mycommand(ds,"new");
24                SqlDataAdapter sqla=new SqlDataAdapter();
25                sqla.SelectCommand=mycommand;
26                sqla.Fill(ds);
27                //myConnection.Open();
28                //SqlDataReader result = mycommand.ExecuteReader();
29                //MyDataGrid.DataSource=result;
30                MyDataGrid.DataSource=ds.Tables[0];
31                MyDataGrid.DataBind();
32                myConnection.Close();
33            }
34            catch(SqlException er)
35            {
36                Label7.Text="Exception in main:"+er.Message;
37                return;
38            }

在DataGrid中得到数据后，用户可以Excel的格式下载得到查询后的文件，代码比较简单：
private void Button2_Click(object sender, System.EventArgs e)
        {
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");                
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + "result.xls");
            Response.Charset = "";

            this.EnableViewState = false ;
            System.IO.StringWriter tw = new System.IO.StringWriter();

            System.Web.UI.HtmlTextWriter hw = new HtmlTextWriter(tw);
            MyDataGrid.RenderControl(hw);
            Response.Write(tw.ToString());
            Response.End();
        }
至此该页面功能实现完毕。