Sometimes I mine our online examples for ideas about blogs or to explore nooks and crannies of controls. A general utility-type solution I routinely find useful is a dynamic database table browser. The user picks the database and the table and a simple form is generated. The simplest kind of form is a Web page with an ASPxGridView bound to the data source. Creating an ASPxGridView dynamically is easy enough, but if you want paging to work and to be able to dynamically change the data source then a few extra steps are needed.
The sample described in this blog reads the SQL Server master database to list all of the databases in a server instance. Pick a database and that databases tables and schemas tables are read to figure out all of the tables with their schemas and these are listed. Pick a table and an ASPxGridView is created and a query that provides data to the ASPxGridView is bound to the grid. This example is a marginal use case—you may not need to actually create and bind an ASPxGridView routinely—it does permit you to explore querying the master, generating dynamic SQL, and look at the basic properties you need to manage to get data into a grid. (I also checked the support pages: some of you have inquired about this very scenario.)
When you dynamically create an ASPxGridView and dynamically bind data to it there is some caching that happens on the grid columns. If you dynamically change the data source then the ghost of the previous columns exist, and binding to the ASPxGridView (with another data source) can lead to a “a field or property with the name ‘xxxxx’ was not found on the selected source”, an HttpException. The field not found will be a field from the previous data source. The solution is to clear the old fields and auto-generate the new columns. Next, you will want paging to work so you need to set the KeyFieldName. The KeyFieldName has to be set last, after the DataBind(ing) happens.
It is an ideal to reduce dependencies on the order and timing of property settings, but permitting changes to state to happen in any order is not always possible. The commented code in Listing 1 shows you the orchestration and basic property settings that support a functioning, dynamic ASPxGridView with a dynamically set data source. The ASPX in Listing 2 uses a client-side JavaScript function to clear the second ASPxComboBox—the one containing the table. Both combo boxes auto-postback, so the ASPxGridView is created when both a database and table have been picked.
Listing 1: The code-behind showing—most importantly—showing the orchestration of ASPxGridView initialization.
Imports DevExpress.Web.ASPxGridView
Imports System.Data.SqlClient
Imports System.Data
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As EventArgs) Handles Me.Load
' Always populate the list of databases
PopulateDatabases()
' Create the ASPxGridView (conditionally), so that posts
' for paging and sorting, etc, have a grid
CreateGridView()
End Sub
''' <summary>
''' Select all of the SQL Server instance's databases from
''' the master table and bind the results to the ASPxComboBox1
''' </summary>
''' <remarks></remarks>
Private Sub PopulateDatabases()
ASPxComboBox1.ValueField = "name"
ASPxComboBox1.TextField = "name"
Dim sql As String = String.Format("select [name] from {0}.sys.{1}", _
"master", "databases")
ASPxComboBox1.DataSource = GetTable("master", sql)
ASPxComboBox1.DataBind()
End Sub
''' <summary>
''' Create the ASPxGridView
''' </summary>
''' <remarks></remarks>
Private Sub CreateGridView()
' If both a database and table have been selected
If (DatabaseName = "" Or TableName = "") Then Return
' Instantiate the ASPxGridView
Dim grid As ASPxGridView = New ASPxGridView()
' Define an ID/name
grid.ID = "ASPxGridView1"
' Add the grid to the Page's Controls collection
' This is where ghost columns will be added if the a
' grid with the same ID was previously bound to a
' different data source. (The ghost-column behavior
' doesn't happen with the Microsoft GridView
Page.Form.Controls.Add(grid)
grid.SettingsBehavior.AllowFocusedRow = True
' Define the SQL select command
Dim selectCommand = String.Format("select * from {0}", TableName)
' Clearing the columns gets rid of any ghost columns
grid.Columns.Clear()
' Set auto-generate to true to have the grid define the
' grid's columns collection
grid.AutoGenerateColumns = True
' Set the data source from a helper function
grid.DataSource = CreateData(DatabaseName, selectCommand)
' Bind
grid.DataBind()
' Set the key field here to support paging and sorting
grid.KeyFieldName = GetKeyFieldName(DatabaseName, TableName)
End Sub
''' <summary>
''' A not wholly satisfactory additional hit on the database
''' used to determine the primary key field; multiply-keyed tables
''' might need a different approach. We only need to
''' read the schema for this method
''' </summary>
''' <param name="databaseName"></param>
''' <param name="tableName"></param>
''' <returns></returns>
''' <remarks></remarks>
Private Function GetKeyFieldName(ByVal databaseName As String, _
ByVal tableName As String) As String
Dim connectionString As String = String.Format("Data Source=.\SQLEXPRESS;Initial " + _
"Catalog={0};Integrated Security=True", databaseName)
Dim table As DataTable = New DataTable()
Using connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Dim command As SqlCommand = New SqlCommand(String.Format( _
"select * from {0}", tableName), connection)
Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)
adapter.FillSchema(table, SchemaType.Source)
End Using
If (table.PrimaryKey.Count > 0) Then
Return table.PrimaryKey(0).ColumnName
Else
Return ""
End If
End Function
''' <summary>
''' A helper function creates a SQL data source from the
''' database and a query
''' </summary>
''' <param name="databaseName"></param>
''' <param name="sql"></param>
''' <returns></returns>
''' <remarks></remarks>
Private Function CreateData(ByVal databaseName As String, _
ByVal sql As String) As SqlDataSource
Dim connection As String = String.Format("Data Source=.\SQLEXPRESS;Initial " + _
"Catalog={0};Integrated Security=True", databaseName)
Return New SqlDataSource(connection, sql)
End Function
''' <summary>
''' Called surfacing constituent properties, this property
''' mitigates naming the combo box
''' </summary>
''' <value></value>
''' <returns></returns>
''' <remarks></remarks>
ReadOnly Property DatabaseName() As String
Get
Return ASPxComboBox1.Text
End Get
End Property
''' <summary>
''' Ditto
''' </summary>
''' <value></value>
''' <returns></returns>
''' <remarks></remarks>
ReadOnly Property TableName() As String
Get
Return ASPxComboBox2.Text
End Get
End Property
''' <summary>
''' When a database is selected in the first combo box
''' the second combo box is field with tables in the format
''' schema.tablename
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Protected Sub ASPxComboBox1_SelectedIndexChanged(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles ASPxComboBox1.SelectedIndexChanged
ASPxComboBox1.Text = ""
PopulateTables(DatabaseName)
End Sub
''' <summary>
''' Gets the DataTable containing the data for the selected
''' database and table
''' </summary>
''' <param name="databaseName"></param>
''' <param name="sql"></param>
''' <returns></returns>
''' <remarks></remarks>
Private Function GetTable(ByVal databaseName As String, _
ByVal sql As String) As DataTable
Dim connectionString As String = "Data Source=.\SQLEXPRESS;Initial " + _
"Catalog={0};Integrated Security=True"
connectionString = String.Format(connectionString, databaseName)
Dim table As DataTable = New DataTable()
Using connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Dim command As SqlCommand = New SqlCommand(sql, _
connection)
Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)
adapter.Fill(table)
End Using
Return table
End Function
''' <summary>
''' Actually populates the combo box contain table names.
''' A join is used to get the name from one table (tables)
''' and the schema name from the schema table; the
''' schema information is normalized in system tables for
''' the selected database
''' </summary>
''' <param name="databaseName"></param>
''' <remarks></remarks>
Private Sub PopulateTables(ByVal databaseName As String)
ASPxComboBox2.ValueField = "name"
ASPxComboBox2.TextField = "name"
Dim sql = String.Format("select s.name + '.' + t.name as name " + _
"from {0}.sys.tables t inner join {0}.sys.schemas s on t.schema_id =s.schema_id", _
databaseName)
ASPxComboBox2.DataSource = GetTable(databaseName, sql)
ASPxComboBox2.DataBind()
End Sub
End Class
Listing 2: The ASPX with a simple JavaScript client-side function for clearing the table combo box when the database name changes.
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<%@ Register assembly="DevExpress.Web.ASPxGridView.v9.2, Version=9.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Web.ASPxGridView" tagprefix="dxwgv" %>
<%@ Register assembly="DevExpress.Web.ASPxEditors.v9.2, Version=9.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Web.ASPxEditors" tagprefix="dxe" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<dxe:ASPxLabel ID="ASPxLabel1" runat="server" Text="Database">
</dxe:ASPxLabel>
<dxe:ASPxComboBox ID="ASPxComboBox1" runat="server" AutoPostBack="True"
ValueType="System.String">
<ClientSideEvents TextChanged="function(s, e) {
TableComboBox.SetText('');
}" />
</dxe:ASPxComboBox>
<br />
<dxe:ASPxLabel ID="ASPxLabel2" runat="server" Text="Table">
</dxe:ASPxLabel>
<dxe:ASPxComboBox ID="ASPxComboBox2" runat="server" AutoPostBack="True"
ClientInstanceName="TableComboBox">
</dxe:ASPxComboBox>
<br />
</div>
</form>
</body>
</html>
Rather than explain the code-behind separately in this example the code is explained with comments inline. When you are dynamically adding an ASPxGridView the order of initialization matters. I experimented with the code to make sure that the grid is fully functional as well as being dynamically added.
As an aside you may not know that there are a couple of relatively new features in Visual Basic, including XML comments (shown) and the upcoming elimination of the Visual Basic line continuation character (_). The latter being an anachronistic character I won’t miss.