DevExpress GridView动态表：如何实现数据增改（无静态列）

在C# ASP.NET项目中，系统管理员可添加自定义模块，程序会自动在SQL Server中创建对应的数据表并动态添加列。目前已通过SQL查询将动态表的数据绑定到DevExpress ASPxGridView（未在aspx端配置静态列），现需实现该GridView的编辑现有记录、添加新记录功能。

现有代码 #

后端绑定代码 #

DataTable tbl = new DataTable();

SqlCommand cmdara = new SqlCommand("select * from " + tabloadi + " where TARIH BETWEEN @baslangic AND @bitis", db_baglanti);

cmdara.Parameters.AddWithValue("@baslangic", dateBasla.Value);
cmdara.Parameters.AddWithValue("@bitis", dateBitis.Value);

SqlDataAdapter adap = new SqlDataAdapter(cmdara);
adap.Fill(tbl);

gridModul.DataSource = tbl;
gridModul.DataBind();

前端GridView配置 #

<dx:ASPxGridView ID="gridModul" runat="server" KeyFieldName="id" ClientInstanceName="gridModul" 
        Theme="Office365" OnCustomColumnDisplayText="gridModul_CustomColumnDisplayText" OnDataBound="gridModul_DataBound" >
        <ClientSideEvents RowDblClick="function(s,e) { s.StartEditRow(e.visibleIndex); }" /> 
                    <SettingsEditing Mode="PopupEditForm">
                    </SettingsEditing>
                    <Settings ShowHeaderFilterBlankItems="False" ShowHeaderFilterButton="True" />
                    <SettingsBehavior AllowFocusedRow="true" AllowSelectByRowClick="true" AllowEllipsisInText="true" AllowDragDrop="false" />
        <SettingsPopup>
            <EditForm HorizontalAlign="Center">
            </EditForm>
        </SettingsPopup>
        <SettingsSearchPanel CustomEditorID="SearchButtonEdit" />
        <SettingsPager PageSize="15" EnableAdaptivity="true">
            <PageSizeItemSettings Visible="true"></PageSizeItemSettings>
        </SettingsPager>
        <SettingsExport EnableClientSideExportAPI="true" ExportSelectedRowsOnly="true" />
         <SettingsDataSecurity AllowDelete="False" />
</dx:ASPxGridView>

解决方案步骤 #

1. 修改前端配置，启用编辑与新增功能 #

更新GridView配置，添加命令列、启用编辑弹窗的底部按钮，并绑定更新/插入事件：

<dx:ASPxGridView ID="gridModul" runat="server" KeyFieldName="id" ClientInstanceName="gridModul" 
        Theme="Office365" OnCustomColumnDisplayText="gridModul_CustomColumnDisplayText" OnDataBound="gridModul_DataBound"
        OnRowUpdating="gridModul_RowUpdating" OnRowInserting="gridModul_RowInserting"
        OnRowUpdated="gridModul_RowUpdated" OnRowInserted="gridModul_RowInserted">
        <ClientSideEvents RowDblClick="function(s,e) { s.StartEditRow(e.visibleIndex); }" /> 
        <SettingsEditing Mode="PopupEditForm">
            <PopupEditFormSettings ShowFooter="True" /> <!-- 显示保存/取消按钮 -->
        </SettingsEditing>
        <!-- 添加命令列，显示新增、编辑按钮 -->
        <Columns>
            <dx:GridViewCommandColumn ShowNewButtonInHeader="True" ShowEditButton="True" VisibleIndex="0">
            </dx:GridViewCommandColumn>
        </Columns>
        <Settings ShowHeaderFilterBlankItems="False" ShowHeaderFilterButton="True" />
        <SettingsBehavior AllowFocusedRow="true" AllowSelectByRowClick="true" AllowEllipsisInText="true" AllowDragDrop="false" />
        <SettingsPopup>
            <EditForm HorizontalAlign="Center">
            </EditForm>
        </SettingsPopup>
        <SettingsSearchPanel CustomEditorID="SearchButtonEdit" />
        <SettingsPager PageSize="15" EnableAdaptivity="true">
            <PageSizeItemSettings Visible="true"></PageSizeItemSettings>
        </SettingsPager>
        <SettingsExport EnableClientSideExportAPI="true" ExportSelectedRowsOnly="true" />
        <SettingsDataSecurity AllowDelete="False" />
</dx:ASPxGridView>

2. 动态生成编辑模板（DataBound事件） #

在gridModul_DataBound事件中，为每个动态列匹配对应类型的编辑器：

protected void gridModul_DataBound(object sender, EventArgs e)
{
    ASPxGridView grid = sender as ASPxGridView;
    if (grid == null) return;

    foreach (GridViewDataColumn column in grid.Columns)
    {
        // 跳过主键列和命令列
        if (column.FieldName == "id" || column is GridViewCommandColumn)
            continue;

        // 为列绑定自定义编辑模板
        column.EditItemTemplate = new CustomEditItemTemplate(column.FieldName, column.ColumnType);
    }
}

// 自定义编辑模板类，根据数据类型生成对应编辑器
public class CustomEditItemTemplate : ITemplate
{
    private readonly string _fieldName;
    private readonly Type _columnType;

    public CustomEditItemTemplate(string fieldName, Type columnType)
    {
        _fieldName = fieldName;
        _columnType = columnType;
    }

    public void InstantiateIn(Control container)
    {
        GridViewEditItemTemplateContainer templateContainer = container as GridViewEditItemTemplateContainer;
        if (templateContainer == null) return;

        ASPxEdit editor = _columnType switch
        {
            Type t when t == typeof(DateTime) => new ASPxDateEdit { DisplayFormatString = "yyyy-MM-dd" },
            Type t when t == typeof(int) || t == typeof(decimal) => new ASPxSpinEdit(),
            _ => new ASPxTextBox { Width = Unit.Percentage(100) }
        };

        // 绑定字段值
        editor.ID = $"edt_{_fieldName}";
        editor.Value = DataBinder.Eval(templateContainer.DataItem, _fieldName);
        container.Controls.Add(editor);
    }
}

3. 实现更新与插入逻辑 #

添加事件处理方法，执行数据库的更新和插入操作：

// 封装数据绑定方法，复用刷新逻辑
private void BindGrid()
{
    DataTable tbl = new DataTable();
    using (SqlCommand cmdara = new SqlCommand($"select * from {tabloadi} where TARIH BETWEEN @baslangic AND @bitis", db_baglanti))
    {
        cmdara.Parameters.AddWithValue("@baslangic", dateBasla.Value);
        cmdara.Parameters.AddWithValue("@bitis", dateBitis.Value);
        SqlDataAdapter adap = new SqlDataAdapter(cmdara);
        adap.Fill(tbl);
    }
    gridModul.DataSource = tbl;
    gridModul.DataBind();
}

protected void gridModul_RowUpdating(object sender, DevExpress.Web.Data.ASPxDataUpdatingEventArgs e)
{
    ASPxGridView grid = sender as ASPxGridView;
    if (grid == null) return;

    int id = Convert.ToInt32(e.Keys["id"]);
    StringBuilder updateSql = new StringBuilder($"UPDATE {tabloadi} SET ");
    List<SqlParameter> parameters = new();

    foreach (var field in e.NewValues)
    {
        updateSql.Append($"{field.Key} = @{field.Key}, ");
        parameters.Add(new SqlParameter($"@{field.Key}", field.Value ?? DBNull.Value));
    }
    updateSql.Remove(updateSql.Length - 2, 2);
    updateSql.Append(" WHERE id = @id");
    parameters.Add(new SqlParameter("@id", id));

    using (SqlCommand cmd = new SqlCommand(updateSql.ToString(), db_baglanti))
    {
        cmd.Parameters.AddRange(parameters.ToArray());
        db_baglanti.Open();
        cmd.ExecuteNonQuery();
        db_baglanti.Close();
    }

    e.Cancel = true;
    grid.CancelEdit();
    BindGrid();
}

protected void gridModul_RowInserting(object sender, DevExpress.Web.Data.ASPxDataInsertingEventArgs e)
{
    ASPxGridView grid = sender as ASPxGridView;
    if (grid == null) return;

    StringBuilder insertSql = new StringBuilder($"INSERT INTO {tabloadi} (");
    StringBuilder valuesSql = new StringBuilder("VALUES (");
    List<SqlParameter> parameters = new();

    foreach (var field in e.NewValues)
    {
        insertSql.Append($"{field.Key}, ");
        valuesSql.Append($"@{field.Key}, ");
        parameters.Add(new SqlParameter($"@{field.Key}", field.Value ?? DBNull.Value));
    }
    insertSql.Remove(insertSql.Length - 2, 2);
    valuesSql.Remove(valuesSql.Length - 2, 2);
    insertSql.Append(") ").Append(valuesSql).Append(")");

    using (SqlCommand cmd = new SqlCommand(insertSql.ToString(), db_baglanti))
    {
        cmd.Parameters.AddRange(parameters.ToArray());
        db_baglanti.Open();
        cmd.ExecuteNonQuery();
        db_baglanti.Close();
    }

    e.Cancel = true;
    grid.CancelEdit();
    BindGrid();
}

protected void gridModul_RowUpdated(object sender, DevExpress.Web.Data.ASPxDataUpdatedEventArgs e)
{
    // 可选：添加更新成功后的提示或逻辑
}

protected void gridModul_RowInserted(object sender, DevExpress.Web.Data.ASPxDataInsertedEventArgs e)
{
    // 可选：添加插入成功后的提示或逻辑
}

4. 关键注意事项 #

• SQL注入防护：确保动态表名tabloadi来自系统可控的配置（如管理员创建的模块名称），禁止直接使用用户输入的表名；字段名需基于合法的动态表结构，避免恶意注入。
• 数据类型适配：根据动态列的实际数据类型调整编辑器，避免编辑时出现类型转换错误。
• 资源管理：使用using语句管理数据库连接和命令对象，确保资源自动释放。

内容的提问来源于stack exchange，提问作者Murat Gültekin