apply template pattern in report or data driven page.
1. create a base control
define the datagrid, query button, and export button or image;
define the binddata method as virtual
define the PrepareParam
the following is partial code:
1 public class DataControl : BaseControl
2 {
3 protected const string SQLKey = "SqlName";
4 protected const string ListKey = "ParamList";
5
6 protected ASPNetDatagridDecorator m_add = new ASPNetDatagridDecorator();
7
8 protected SortableGrid dgBasic;
9 protected string _cacheKey = "";
10 protected System.Web.UI.WebControls.ImageButton ibExport;
11 protected Button btnQuery;
12
13
14 private string _queryId = "OtherAll";
15 private string _xmlName = "Specific";
16 public string QueryID
17 {
18 get { return _queryId; }
19 set { _queryId = value; }
20 }
21
22 public string XmlName
23 {
24 set { _xmlName = value; }
25 }
26
27 virtual protected bool PrepareParam()
28 {
29 return true;
30 }
31
32 virtual protected void AddHeader()
33 {
34 }
35
36 virtual protected void BindData()
37 {
38 string sqlName = (string) ViewState[SQLKey];
39 ArrayList list = (ArrayList) ViewState[ListKey];
40 if(sqlName == "" || list == null)
41 return;
42
43 try
44 {
45 DataSet dsData = (DataSet) Cache[_cacheKey];
46 if(dsData == null)
47 {
48 IDataProviderBase dp = DataProvider.Instance();
49 try
50 {
51 dsData = new DataSet();
52 dsData = dp.GetDataSet(_xmlName, sqlName, list);
53 Cache.Insert(_cacheKey, dsData, null, DateTime.MaxValue, TimeSpan.FromMinutes(20));
54 }
55 catch(Exception ex)
56 {
57 EventLogHelper.LogError(ex.ToString());
58 }
59
60 if(Config.GetConfigValue("IsDebug", "0") == "1")
61 Response.Write(dp.SQLStatement);
62 }
63 DataView dv = dsData.Tables[0].DefaultView;
64 string str = dgBasic.SortExpression;
65 if(str != "")
66 {
67 dv.Sort = str;
68 if(!dgBasic.IsSortedAscending)
69 dv.Sort += " desc";
70 }
71 dgBasic.DataSource = dv;
72 dgBasic.DataBind();
73 }
74 catch(Exception ex)
75 {
76 EventLogHelper.LogError(ex.ToString());
77 }
78 }
79
80 virtual protected void ExportExcel(SortableGrid dgBasic)
81 {
82
83 dgBasic.GridLines = System.Web.UI.WebControls.GridLines.Both;
84 // dgBasic.EnableViewState = false;
85
86 Response.Clear();
87 Response.Buffer = true;
88 Response.ContentType = "application/vnd.ms-excel";
89 Response.Charset = "UTF-8";
90 Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
91 Response.AppendHeader("Content-Disposition", "attachment;filename=book1.xls");
92 this.Page.EnableViewState=false;
93
94 System.IO.StringWriter tw = new System.IO.StringWriter();
95 System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
96
97 AddHeader();
98 CRMEngine.Components.Util.ClearControls(dgBasic);
99 dgBasic.RenderControl(hw);
100
101 Response.Write(tw.ToString());
102 Response.End();
103 }
104
105
106 protected override void OnInit(EventArgs e)
107 {
108 base.OnInit (e);
109
110 _cacheKey = LogonUser.LoginName +"_specific";
111
112 ibExport.Click += new System.Web.UI.ImageClickEventHandler(ibExport_Click);
113 dgBasic.SortCommand += new System.Web.UI.WebControls.DataGridSortCommandEventHandler(dgBasic_SortCommand);
114 btnQuery.Click += new EventHandler(btnQuery_Click);
115 }
116
117
118 private void ibExport_Click(object sender, System.Web.UI.ImageClickEventArgs e)
119 {
120 this.ExportExcel(dgBasic);
121 }
122
123 private void dgBasic_SortCommand(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
124 {
125 BindData();
126 }
127
128
129 protected void btnQuery_Click(object sender, EventArgs e)
130 {
131
132 if(PrepareParam() == false)
133 return;
134
135 Cache.Remove(_cacheKey);
136 BindData();
137 }
138 }
139
140
2 {
3 protected const string SQLKey = "SqlName";
4 protected const string ListKey = "ParamList";
5
6 protected ASPNetDatagridDecorator m_add = new ASPNetDatagridDecorator();
7
8 protected SortableGrid dgBasic;
9 protected string _cacheKey = "";
10 protected System.Web.UI.WebControls.ImageButton ibExport;
11 protected Button btnQuery;
12
13
14 private string _queryId = "OtherAll";
15 private string _xmlName = "Specific";
16 public string QueryID
17 {
18 get { return _queryId; }
19 set { _queryId = value; }
20 }
21
22 public string XmlName
23 {
24 set { _xmlName = value; }
25 }
26
27 virtual protected bool PrepareParam()
28 {
29 return true;
30 }
31
32 virtual protected void AddHeader()
33 {
34 }
35
36 virtual protected void BindData()
37 {
38 string sqlName = (string) ViewState[SQLKey];
39 ArrayList list = (ArrayList) ViewState[ListKey];
40 if(sqlName == "" || list == null)
41 return;
42
43 try
44 {
45 DataSet dsData = (DataSet) Cache[_cacheKey];
46 if(dsData == null)
47 {
48 IDataProviderBase dp = DataProvider.Instance();
49 try
50 {
51 dsData = new DataSet();
52 dsData = dp.GetDataSet(_xmlName, sqlName, list);
53 Cache.Insert(_cacheKey, dsData, null, DateTime.MaxValue, TimeSpan.FromMinutes(20));
54 }
55 catch(Exception ex)
56 {
57 EventLogHelper.LogError(ex.ToString());
58 }
59
60 if(Config.GetConfigValue("IsDebug", "0") == "1")
61 Response.Write(dp.SQLStatement);
62 }
63 DataView dv = dsData.Tables[0].DefaultView;
64 string str = dgBasic.SortExpression;
65 if(str != "")
66 {
67 dv.Sort = str;
68 if(!dgBasic.IsSortedAscending)
69 dv.Sort += " desc";
70 }
71 dgBasic.DataSource = dv;
72 dgBasic.DataBind();
73 }
74 catch(Exception ex)
75 {
76 EventLogHelper.LogError(ex.ToString());
77 }
78 }
79
80 virtual protected void ExportExcel(SortableGrid dgBasic)
81 {
82
83 dgBasic.GridLines = System.Web.UI.WebControls.GridLines.Both;
84 // dgBasic.EnableViewState = false;
85
86 Response.Clear();
87 Response.Buffer = true;
88 Response.ContentType = "application/vnd.ms-excel";
89 Response.Charset = "UTF-8";
90 Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
91 Response.AppendHeader("Content-Disposition", "attachment;filename=book1.xls");
92 this.Page.EnableViewState=false;
93
94 System.IO.StringWriter tw = new System.IO.StringWriter();
95 System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
96
97 AddHeader();
98 CRMEngine.Components.Util.ClearControls(dgBasic);
99 dgBasic.RenderControl(hw);
100
101 Response.Write(tw.ToString());
102 Response.End();
103 }
104
105
106 protected override void OnInit(EventArgs e)
107 {
108 base.OnInit (e);
109
110 _cacheKey = LogonUser.LoginName +"_specific";
111
112 ibExport.Click += new System.Web.UI.ImageClickEventHandler(ibExport_Click);
113 dgBasic.SortCommand += new System.Web.UI.WebControls.DataGridSortCommandEventHandler(dgBasic_SortCommand);
114 btnQuery.Click += new EventHandler(btnQuery_Click);
115 }
116
117
118 private void ibExport_Click(object sender, System.Web.UI.ImageClickEventArgs e)
119 {
120 this.ExportExcel(dgBasic);
121 }
122
123 private void dgBasic_SortCommand(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
124 {
125 BindData();
126 }
127
128
129 protected void btnQuery_Click(object sender, EventArgs e)
130 {
131
132 if(PrepareParam() == false)
133 return;
134
135 Cache.Remove(_cacheKey);
136 BindData();
137 }
138 }
139
140
2. in any inherited page
override PerpareParam function to define your custom SQL statement;
ex.
1 override protected void AddHeader()
2 {
3 TableCell cell = null;
4 ArrayList header = new ArrayList();
5
6 cell = new TableCell();
7 cell.Text = "表头一";
8 cell.RowSpan = 2;
9 cell.HorizontalAlign = HorizontalAlign.Center;
10 header.Add(cell);
11
12
13 cell = new TableCell();
14 cell.Text = "表头二";
15 cell.ColumnSpan = 3;
16 cell.HorizontalAlign = HorizontalAlign.Center;
17 header.Add(cell);
18
19 m_add.AddMergeHeader(header);
20 }
21
22 protected override bool PrepareParam()
23 {
24 ArrayList list = new ArrayList();
25 list.Add(ctlDate.SelectedDate);
26 if(ctlCity.CityID != int.Parse(Config.GetConfigValue("ProvinceID")))
27 list.Add(" area_city="+ ctlCity.CityID);
28 else
29 list.Add(" 1=1");
30
31 ViewState[SQLKey] = this.QueryID;
32 ViewState[ListKey] = list;
33 return base.PrepareParam();
34 }
35
36 override protected void BindData()
37 {
38 AddHeader();
39 base.BindData();
40 }
41
2 {
3 TableCell cell = null;
4 ArrayList header = new ArrayList();
5
6 cell = new TableCell();
7 cell.Text = "表头一";
8 cell.RowSpan = 2;
9 cell.HorizontalAlign = HorizontalAlign.Center;
10 header.Add(cell);
11
12
13 cell = new TableCell();
14 cell.Text = "表头二";
15 cell.ColumnSpan = 3;
16 cell.HorizontalAlign = HorizontalAlign.Center;
17 header.Add(cell);
18
19 m_add.AddMergeHeader(header);
20 }
21
22 protected override bool PrepareParam()
23 {
24 ArrayList list = new ArrayList();
25 list.Add(ctlDate.SelectedDate);
26 if(ctlCity.CityID != int.Parse(Config.GetConfigValue("ProvinceID")))
27 list.Add(" area_city="+ ctlCity.CityID);
28 else
29 list.Add(" 1=1");
30
31 ViewState[SQLKey] = this.QueryID;
32 ViewState[ListKey] = list;
33 return base.PrepareParam();
34 }
35
36 override protected void BindData()
37 {
38 AddHeader();
39 base.BindData();
40 }
41
采用这种方法的好处是数据绑定/排序等常用操作封装在父类中进行, 每个具体的应用(页面)中只需要定义其SQL和对应的PAGE页面(因为显示的表格字段不同)即可. 如果要修改某些属性, 修改父类中的定义即可.
