ADO.NET 2.0 動態指定 ObjectDataSource 的 SelectMethod

效果如下圖，指定要查詢的資料後，再設定 ObjectDataSource 的 SelectMethod，使用 ObjectDataSource 的好處是不用再自行撰寫分頁、排序的code

 aspx的程式如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
  
<body>
    <form id="form1" runat="server">
    
   <script type="text/javascript">
    //<![CDATA[
         function WebForm_OnSubmit() {
             $.blockUI({ css: { 
                 border: 'none', padding: '15px', backgroundColor: '#000', opacity: .5, color: '#fff', 
                 '-webkit-border-radius': '10px', 
                 '-moz-border-radius': '10px'
             }
             });
             return true;
         }
    //]]>
    </script>

<fieldset style="width: 100%;">
    <legend><font color="darkblue" size="2"><b></b></font></legend>
    <table id="QryTable" border="0" style="width: 100%">
        <tr>
            <td class="TdTitle" style="width:11%">查詢資料: </td>
            <td class="TdContent" style="width:5%">
                <asp:DropDownList ID="QDDLReport" AutoPostBack="true" runat="server"></asp:DropDownList>
            </td>
            <td class="TdTitle"  style="width:10%" id = "Q2T" runat="server">資料區間: </td>
            <td class="TdContent" style="width:30%" id = "Q2" runat="server">
                <asp:TextBox ID="QtxtsDate" runat="server" Width="60pt" class="clscustxt"></asp:TextBox> ~ 
                <asp:TextBox ID="QtxteDate" runat="server" Width="60pt" class="clscustxt"></asp:TextBox>
            </td>
            <td>
                <asp:Button ID="btnQuery" runat="server" class="clsbtn3" Text="查 詢" 
                    OnClientClick="WebForm_OnSubmit();" onclick="btnQuery_Click"/>
            </td>
        </tr>
    </table>
</fieldset>
<div style="font-size:10pt;color:Blue" id="div_count" runat="server"></div>
     <div style="overflow:auto;width:960px;height:460px;">
    <asp:GridView ID="gvList" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" BackColor="White" 
        CellPadding="3" DataSourceID="ObjectDataSource1" EnableModelValidation="True" ForeColor="Black" GridLines="None" 
                                            CellSpacing="1" CssClass="clsTable" 
                                            Font-Size="12pt" Width="100%" 
                        PagerStyle-CssClass="pgr" ShowFooter="True" 
                            onpageindexchanging="gvList_PageIndexChanging">
                                            <AlternatingRowStyle BackColor="#DDDDFF" />
                                            <FooterStyle BackColor="#BBBBBB" ForeColor="White" HorizontalAlign="Center" />
                                            <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
                                            <PagerSettings FirstPageImageUrl="~/Images/Arrow_First.png" 
                                                LastPageImageUrl="~/Images/Arrow_End.png" Mode="NumericFirstLast" 
                                                NextPageImageUrl="~/Images/Arrow_Next.png" PageButtonCount="3" 
                                                Position="TopAndBottom" PreviousPageImageUrl="~/Images/Arrow_Back.png" />
                                            <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
                                            <RowStyle BackColor="#F7F7DE" />
                                            <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" />
                                        </asp:GridView>
        </div>
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" OldValuesParameterFormatString="original_{0}" 
            onselecting="ObjectDataSource1_Selecting" CacheDuration="1" EnableCaching="True">
        </asp:ObjectDataSource>

         <div style="text-align:center">
            <asp:Button ID="btnToExcel" runat="server" class="clsbtn3" Text="匯出Excel" 
                onclick="btnToExcel_Click" />
        </div>
    </div>
     <script type='text/javascript'>
            $(document).ready(function () {
                $.unblockUI();
            });
    </script>
    </form>
</body>

後端程式如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
  
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            QDDLReport.Items.Add("A QueryData");
            QDDLReport.Items.Add("B QueryData");
            QDDLReport.Items.Add("C QueryData");
            QtxtsDate.Text = DateTime.Now.Year.ToString() + "/1/1";
            QtxteDate.Text = DateTime.Now.ToShortDateString();
        }
        else
        {
            //不同的報表有不同的SelectMethod
            if (QDDLReport.SelectedValue == "A Data")
            {
                ObjectDataSource1.SelectMethod = "GetAQueryData";
                ObjectDataSource1.TypeName = "QueryDataBLL";
            }
            else if (QDDLReport.SelectedValue == "B Data")
            {
                ObjectDataSource1.SelectMethod = "GetBQueryData";
                ObjectDataSource1.TypeName = "QueryDataBLL";
            }
            else if (QDDLReport.SelectedValue == "C Data")
            {
                ObjectDataSource1.SelectMethod = "GetCQueryData";
                ObjectDataSource1.TypeName = "QueryDataBLL";
            }
        
        }
    }
    protected void ObjectDataSource1_Selected(object sender, ObjectDataSourceStatusEventArgs e)
    {
        System.Data.DataTable dt = e.ReturnValue as System.Data.DataTable;
        if (dt == null || dt.Rows.Count == 0)
            div_count.InnerHtml = string.Format("   查無資料!", "");
        else
            div_count.InnerHtml = string.Format("   共 <span style='color:red;font-size:12pt;'><b>{0}</b></span> 筆資料", dt.Rows.Count);
    }
    protected void btnToExcel_Click(object sender, EventArgs e)
    {
        if (gvList.Rows.Count == 0)
            return;

        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=ExportData.xls");
        Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter sw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htw = new HtmlTextWriter(sw);
        //關閉換頁跟排序    
        gvList.AllowSorting = false;
        gvList.AllowPaging = false;
        gvList.DataBind();     
        HtmlForm hf = new HtmlForm();
        Controls.Add(hf);
        hf.Controls.Add(gvList);
        hf.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();
    }
    protected void ObjectDataSource1_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
    {
        //首次進入頁面不Query
        if (!IsPostBack)
            e.Cancel = true;
    }
    protected void btnQuery_Click(object sender, EventArgs e)
    {
        QueryDataBLL bll = new QueryDataBLL();
        ObjectDataSource1.SelectParameters.Clear();
        if (QDDLReport.SelectedValue == "A Data")
        {
            this.createGVColumns(bll.GetAQueryDataSchema());
            //依Query的需求設定Parameter
            Parameter para1 = new Parameter("sdate");
            para1.DefaultValue = (QtxtsDate.Text.Trim() == "" ? "2000/1/1" : QtxtsDate.Text.Trim());
            ObjectDataSource1.SelectParameters.Add(para1);

            Parameter para2 = new Parameter("edate");
            para2.DefaultValue = (QtxteDate.Text.Trim() == "" ? "2099/12/31" : QtxteDate.Text.Trim());
            ObjectDataSource1.SelectParameters.Add(para2);
        }
        else if (QDDLReport.SelectedValue == "B Data")
        {
            this.createGVColumns(bll.GetBQueryDataSchema());
            //依Query的需求設定Parameter
            // 略
        }
        else if (QDDLReport.SelectedValue == "C Data")
        {
            this.createGVColumns(bll.GetCQueryDataSchema());
            //依Query的需求設定Parameter  
            // 略
        }
        
        
        this.DataBind();
    }
    protected void gvList_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        ObjectDataSource1.CacheDuration = 0;
    }

    private void createGVColumns(System.Data.DataTable dt)
    {
        //依不同的查詢結果設定gridview欄位
        gvList.Columns.Clear();
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            BoundField bf = new BoundField();
            bf.DataField = dt.Columns[i].ColumnName.ToString();
            bf.HeaderText = dt.Columns[i].ColumnName.ToString();
            gvList.Columns.Add(bf);
        }
    }

QueryDataBLL 的 GetAQueryData 與 GetAQueryDataSchema 都是回傳 Datatable，且 sql command 一樣，只差在 GetAQueryDataSchema 的 sql command where 條件只下 where 1=2 ，用來取回欄位資訊...

 

posted @ 2013-06-10 11:55  Jimmych  阅读(265)  评论(0)    收藏  举报