Paul Kimmel's Blog

Believe it or not customers do write DevExpress and evangelists directly and we love to help them. Its fun to play in the puzzle palace—someone needs help, maybe it is tricky for that person, maybe it is tricky for me too and I get to help them figure out a solution. Recently someone from the ‘hood (East Lansing) asked me if it was possible to dynamically change the data source of an ASPxGridView on an AJAX call, a callback. The challenge is that the ASPxGridView is going to have one set of columns on a page load for the current datasource and then the callback will try to bind an alternate dataset. You could just use a postback. You could use multiple grids and multiple datasources and play control hide and seek, but this customer stated that he had ten possible datasets and didn’t want to manage ten pairs of datasources and ASPxGridViews. (We want what we want and we need what we need.)

By default the ASPxGridView’s AutoGenerateColumns is set to true. My solution reads the schema of the new datasource, dynamically adds the columns to the ASPxGridView, indicates which field is the primary key, and then binds the datasource. So, if you want an arbitrary number of datasources displayed on your web pages then you can try this solution. Session is used to manage the current datasource, and the Page_Load relads the current datasource, so updates should work too.

The solution—the code behind is shown in Listing 1—uses a ScriptManager, two buttons, a single ASPxGridView, and two AccessDataSources. Click one or the other of the buttons and the datasource is changed.

Listing 1: The code-behind for the solution.

Imports System.Data.OleDb
Imports System.Data
Imports DevExpress.Web.ASPxGridView


Partial Class Default2
    Inherits System.Web.UI.Page

  Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
    DataSource = AccessDataSource1
    BuildGridView()

  End Sub

  Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
    DataSource = AccessDataSource2
    BuildGridView()
  End Sub


  Private Sub BuildGridView()
    ASPxGridView1.Columns.Clear()
    ASPxGridView1.AutoGenerateColumns = False

    Dim sql As String = DataSource.SelectCommand
    Dim table As DataTable = New DataTable()

    Using connection As OleDbConnection = New OleDbConnection(DataSource.ConnectionString)
      connection.Open()
      Dim command As OleDbCommand = New OleDbCommand(DataSource.SelectCommand, connection)
      Dim Adapter As OleDbDataAdapter = New OleDbDataAdapter(command)
      Adapter.FillSchema(table, SchemaType.Source)
    End Using

    For Each column As DataColumn In table.Columns
      Dim co As GridViewDataColumn = New GridViewDataColumn()
      co.FieldName = column.ColumnName
      ASPxGridView1.Columns.Add(co)

    Next

    If (table.PrimaryKey.Count > 0) Then
      ASPxGridView1.KeyFieldName = table.PrimaryKey(0).ColumnName
    End If

    ASPxGridView1.DataSource = DataSource
    ASPxGridView1.DataBind()
  End Sub

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If (DataSource Is Nothing) Then Return
    BuildGridView()
  End Sub

  Private _dataSource As AccessDataSource
  Private KEY As String = "DATASOURCE"
  Public Property DataSource As AccessDataSource
    Get
      If (Session(KEY) Is Nothing) Then Return Nothing
      Return Session(KEY)
    End Get
    Set(ByVal Value As AccessDataSource)
      Session(KEY) = Value
    End Set
  End Property
End Class

The solution I uploaded for the demo uses the beta version 10.2. You can switch all references to the newer version with find and replace, but it is more fun to download a trial version of DXperience. The two buttons are used for the AJAX triggers.