在运行时更改 SqlDataSource.SelectCommand 会破坏分页 [英] Changing SqlDataSource.SelectCommand at runtime breaks pagination

问题描述

我有一个 GridView 绑定到一个 SqlDataSource 和一个默认的 SelectCommand 定义如下:

I have a GridView bound to a SqlDataSource with a default SelectCommand defined as such:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet"
      ConnectionString="<%$ ConnectionStrings:MyConn %>" 
      ProviderName="MySql.Data.MySqlClient" 
      SelectCommand="select * from blah blah" />

在某些情况下，我必须在运行时动态更改此查询，因此我执行以下操作:

There are cases where I have to change this query dynamically at runtime, so I do the following:

SqlDataSource1.SelectCommand = sql; // 'sql' is the new query
GridView1.PageIndex = 0;
GridView1.EditIndex = -1;
GridView1.SelectedIndex = -1;
GridView1.DataBind();
updatePanel.Update();

这实际上工作得很好，但是当我单击分页控件时，结果集默认返回到 SqlDataSource1 中定义的 SelectCommand.

This works just fine actually, but when I click the pagination controls, the result set defaults back to the SelectCommand defined in the SqlDataSource1.

有什么办法可以解决这个问题吗?

Any way around this?

谢谢，标记

推荐答案

这里的问题是，当页面加载时， SqlDataSource 正在重新创建，因为 Pager 链接发出提交.没有什么可以告诉它加载您动态设置的内容.如果您要使用带参数的存储过程，则 ASP 会将参数保存到 ViewState 并在页面加载时重新运行 SqlDataSource 中的选择.

The issue here is that the SqlDataSource is getting re-created when the page loads upon the submit issued by the Pager links. There is nothing to tell it to load what you had set dynamically. If you were to use a stored procedure with parameters then ASP would save off the parameters to ViewState and re-run the select in the SqlDataSource when the page loaded.

所以你需要做的是告诉 SqlDataSource 它上次正确加载 SQL 时它有什么.

So what you have to do is tell the the SqlDataSource what it had for SQL when it last loaded correctly.

最简单的方法是在设置 SqlDataSource 的 SelectCommand 时将 SQL 存储在 ViewState 中，然后在 Page_Load 事件中再次检索它并将其设置回来.

The simplest way to do that is to store the SQL in ViewState when you set the SelectCommand of the SqlDataSource and then retrieve it again in the Page_Load event and set it back.

例如:假设您有一个用于某些条件的文本框和一个搜索按钮.当用户在 TextBox 中输入一些文本，然后单击搜索"按钮时，您希望它构建一些 SQL(顺便说一下，这会让您面临大量 SQL 注入攻击.确保您擦洗您的标准.)然后设置 SqlDataSource 的 SelectCommand 属性.正是在这一点上，您想要保存 SQL.然后在 Page_Load 事件中，您希望检索它并将 SelectCommand 属性设置为该值.

For instance: Let's say you have a TextBox for some criteria and a Search button. When the user enters some text into the TextBox and then clicks on the "Search" button, you want it to build up some SQL (This, by the way, leaves you with a huge exposure to SQL Injection attacks. Make sure you scrub your criteria well.) and then set the SqlDataSource's SelectCommand property. It is a t this point that you would want to save off the SQL. Then in the Page_Load event you would want to retrieve it and set the SelectCommand property to that value.

点击你的按钮，你可以存储 SQL:

In the Click of your button you could store the SQL:

Dim sSQL as String

sSQL = "SELECT somefields FROM sometable WHERE somefield = '" & Me.txtCriteria.Text & "'"
SqlDataSource1.SelectCommand = sSQL
ViewState("MySQL") = sSQL

然后在 Page_Load 事件中，您可以检索 SQL 并设置 SelectCommand 属性:

Then in the Page_Load event you could retrieve the SQL and set the SelectCommand property:

Dim sSQL as String

If Me.IsPostBack() Then
    sSQL = ViewState("MySQL")
    SqlDataSource1.SelectCommand = sSQL
End If

这篇关于在运行时更改 SqlDataSource.SelectCommand 会破坏分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！