sqldatasource bind gridview (date between)

最新推荐文章于 2018-02-07 17:53:00 发布

原创最新推荐文章于 2018-02-07 17:53:00 发布 · 827 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

asp.net

本文介绍如何使用ASP.NET中的SQLDataSource组件动态设置查询参数，包括通过控件属性和页面属性传递参数值的方法，并展示了如何在Selecting事件中手动更改查询命令。

sql datasource:

<asp:SqlDataSource ID="SqlDataSourceGrid" runat="server" ConnectionString="<%$ ConnectionStrings:EnergiaConnectionString %>"
            
            CancelSelectOnNullParameter="False"
            
            SelectCommand="SELECT [id], [customer_email], [customer_number], [date] FROM [tbl_submit_records] WHERE 
            (  ([date] >= @date) AND ([date] <= @date2 )) ORDER BY [date] DESC" 
            
            onselecting="SqlDataSourceGrid_Selecting">
            <SelectParameters>
                <asp:Parameter Name="date" Type="DateTime" />
                <asp:Parameter Name="date2" Type="DateTime" />
            </SelectParameters>
        </asp:SqlDataSource>

change the selectCommand or parameter manually in the datasource.selecting event. (happen before the datasource select data)

protected void SqlDataSourceGrid_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        if (RadDatePickerFrom.SelectedDate != null)
        {
            e.Command.Parameters["@date"].Value = RadDatePickerFrom.SelectedDate.Value.Date.ToString();
        //  SqlDataSourceGrid.SelectParameters["date"].DefaultValue = RadDatePickerFrom.SelectedDate.Value.Date.ToString();

        }
        else
        {
           e.Command.Parameters["@date"].Value = DateTime.Now.Date.AddMonths(-1).ToString();
            //SqlDataSourceGrid.SelectParameters["date"].DefaultValue = DateTime.Now.AddMonths(-1).ToString(); 
        }

        if (RadDatePickerTo.SelectedDate != null)
        {
            e.Command.Parameters["@date2"].Value = RadDatePickerTo.SelectedDate.Value.Date.AddDays(1).AddSeconds(-1).ToString();
            //SqlDataSourceGrid.SelectParameters["date2"].DefaultValue = RadDatePickerTo.SelectedDate.Value.Date.AddDays(1).AddSeconds(-1).ToString();
        }
        else
        {
            e.Command.Parameters["@date2"].Value = DateTime.Now.Date.AddDays(1).AddSeconds(-1).ToString();
        }
    }

rebind datasource (submit button click), datasource re select, fire datasource.selecting event etc..

GridView1.Rebind();

/////// second way : create property on the page. pass the page property to the parameter.

// create the property based on the select value.
public DateTime FilterDateTo
    {
        get
        {
            if (dtmTo.SelectedDate.HasValue)
                return
                    dtmTo.SelectedDate.Value.AddDays(1).AddSeconds(-1);
            else
                return SystemSettingsManager.GetNow().Date.AddDays(1).AddSeconds(-1);
        }
    }

// controlID= "--Page"

<data:CustomerBalanceDataSource runat="server" ID="CustomerBalanceDS" SelectMethod="GetByDateToAndStatusAndCurrencyAndAccountType" >
        <Parameters>
            <asp:Parameter Name="dateFrom" />
            
            <asp:ControlParameter Name="dateTo" ControlID="__Page" PropertyName="FilterDateTo"  />
            <asp:ControlParameter Name="isDebtor" ControlID="ddl_State" />
            <asp:ControlParameter Name="currencyCode" ControlID="ddlCurrency" />
            <asp:ControlParameter Name="accountType" ControlID="ddlAccountType" />
        </Parameters>
    </data:CustomerBalanceDataSource>