在运行时更改 SqlDataSource.SelectCommand 会中断分页
我有一个 GridView 绑定到 SqlDataSource ，默认的 SelectCommand 定义如下：
<asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet"
ConnectionString="<%$ ConnectionStrings:MyConn %>"
ProviderName="MySql.Data.MySqlClient"
SelectCommand="select * from blah blah" />
在某些情况下，我必须在运行时动态更改此查询，所以我执行以下操作：
SqlDataSource1.SelectCommand = sql; // 'sql' is the new query
GridView1.PageIndex = 0;
GridView1.EditIndex = -1;
GridView1.SelectedIndex = -1;
GridView1.DataBind();
updatePanel.Update();
这实际上工作得很好，但是当我单击分页控件时，结果集默认返回到 SqlDataSource1 中定义的 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();
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?
Thanks,
Mark
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这里的问题是，当页面在 Pager 链接发出的提交后加载时，SqlDataSource 被重新创建。没有什么可以告诉它动态加载您设置的内容。如果您要使用带有参数的存储过程，那么 ASP 会将参数保存到 ViewState 中，并在页面加载时重新运行 SqlDataSource 中的选择。
因此，您要做的就是告诉 SqlDataSource 最后一次正确加载时它为 SQL 提供了什么。
最简单的方法是在设置 SqlDataSource 的 SelectCommand 时将 SQL 存储在 ViewState 中，然后在 Page_Load 事件中再次检索它并将其设置回来。
例如：假设您有一个用于某些条件的文本框和一个搜索按钮。当用户在文本框中输入一些文本，然后单击“搜索”按钮时，您希望它构建一些 SQL（顺便说一句，这会让您面临 SQL 注入攻击的巨大风险。请务必清理您的 SQL 注入攻击）条件。），然后设置 SqlDataSource 的 SelectCommand 属性。此时您可能希望保存 SQL。然后，在 Page_Load 事件中，您需要检索它并将 SelectCommand 属性设置为该值。
在单击按钮时，您可以存储 SQL：
然后在 Page_Load 事件中，您可以检索 SQL 并设置 SelectCommand 属性：
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.
So what you have to do is tell the the SqlDataSource what it had for SQL when it last loaded correctly.
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.
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.
In the Click of your button you could store the SQL:
Then in the Page_Load event you could retrieve the SQL and set the SelectCommand property:
1）尝试从页面预览中设置数据源，而不是动态设置。这是最明确的解决方案
如果不能，
2) 尝试在 page_load 之前生成列表。我认为将代码放在 page_init 下可能会起作用。我现在记不太清了，但是在page_load之前有一个方法。
1) Try set datasource from the page preview, not dynamically. This is the most clear solution
If you can't,
2) Try to generate the list before page_load. I think placing code under page_init might do the trick. I can't remember exactly at the moment, but there is a method before page_load.