通过存储过程实现报表复杂查询并以Excel格式输出
来源:互联网 发布:大量商家退出淘宝 编辑:程序博客网 时间:2024/05/21 10:51
本模块实现功能:用户自定义投诉报表查询并以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中存储过程的代码: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;
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
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 }
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();
}
至此该页面功能实现完毕。{
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();
}
- 通过存储过程实现报表复杂查询并以Excel格式输出
- 通过存储过程实现报表复杂查询并以Excel格式输出
- mongodb实现复杂报表查询
- 存储过程解析表菜单并输出JSON格式字符串
- sqlplus输出html或者excel格式报表
- 通过复杂的代码形成业务数据并显示的思路 游标 存储过程
- .net 以table输出格式导出excel
- Access如何制作复杂报表——利用Excel输出复杂报表
- Access如何制作复杂报表——利用Excel输出复杂报表
- delphi 用别名的方式访问excel写入数据,实现非标格式报表输出
- 报表查询之存储过程细节要点
- oracle 动态语句实现复杂SQL的存储过程分布查询
- SQLServer2000把指定查询输出为Excel文件的存储过程
- mysql存储过程以"数组"形式入参实现表间复制 并实现回滚
- oracle存储过程——按id查询相关数据,并输出
- 润乾报表实现导出excel并将分组的各个类别以目录的形式显示
- sql2005 通过存储过程生成 Excel
- 通过存储过程实现分页
- CDirectoryChangeWatcher - ReadDirectoryChangesW all wrapped up
- frameset 的一些使用技巧
- Java正则表达式详解
- 淡淡的友情纯净而又绵长
- C#的插件开发
- 通过存储过程实现报表复杂查询并以Excel格式输出
- 制作可引导的启动软盘
- Classpath和Codebase
- 天水伏羲庙
- 备份工具:使用Tcl/Tk 开发
- 《漂亮的流程图绘制》(ConceptDraw Professional)v7.4.0
- 欣赏是一种绝美的风景
- 统计重复数据的总数且只显示重复数据的一条记录
- 关于标准c++库函数的使用 一起讨论哈