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

问题描述

我使用默认的 SelectCommand SqlDataSource 的 GridView c $ c>定义为：

pre $ lt; code>< asp：SqlDataSource ID =SqlDataSource1runat =serverDataSourceMode = DataSet
ConnectionString =<％$ ConnectionStrings：MyConn％>
ProviderName =MySql.Data.MySqlClient
SelectCommand =select * from blah blah/>

在某些情况下，我必须在运行时动态更改此查询，所以我执行以下操作： / p>

  SqlDataSource1.SelectCommand = sql; //'sql'是新查询
 GridView1.PageIndex = 0; 
 GridView1.EditIndex = -1; 
 GridView1.SelectedIndex = -1; 
 GridView1.DataBind（）; 
 updatePanel.Update（）; 
  

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

这是什么？

谢谢，
Mark

解决方案

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

所以你需要做的是告诉SqlDataSource它在上次加载的时候对SQL有什么用处。

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

<例如：比方说，你有一个TextBox的一些条件和一个搜索按钮。当用户在文本框中输入一些文本，然后点击搜索按钮时，你希望它建立一些SQL（顺便说一句，这会给你带来SQL注入攻击的巨大风险。条件很好）。然后设置SqlDataSource的SelectCommand属性。这一点你会想要保存SQL。然后在Page_Load事件中，您将希望检索它并将SelectCommand属性设置为该值。

在Click of your按钮中，您可以存储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属性：

  Dim sSQL as String 
 
如果Me.IsPostBack（）然后
 sSQL = ViewState（MySQL）
 SqlDataSource1.SelectCommand = sSQL 
结束如果
  

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

解决方案

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:

Dim sSQL as String

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

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屋！