在DataGridView中添加一个ComboBox列

本文关键字:一个 ComboBox DataGridView 添加 | 更新日期: 2023-09-27 18:12:46

我正在使用一个winform datagridview控件来添加,编辑&删除MS Access数据库中的记录。当没有pk-fk关系时,效果很好。现在我想在UserDetails表中添加一个UserTypeId列。UserTypeId来自UserTypes表

Table: UserDetails
--------------------------------------
UserId | UserTypeId | Other fields...|
--------------------------------------
       |            |                |
--------------------------------------
Table: UserTypes
---------------------------
UserTypeId | UserTypeName |
---------------------------
           |              |
---------------------------

这些是我现有的代码-

public partial class frmUser : Form
{
    private String connectionString = null;
    private OleDbConnection connection = null;
    private OleDbDataAdapter da = null;
    private OleDbCommandBuilder commandBuilder = null;
    private DataTable dataTable = null;
    private BindingSource bindingSource = null;
    private String selectQueryString = null;
    public frmUser()
    {
        InitializeComponent();
    }
    private void frmUser_Load(object sender, EventArgs e)
    {
        connectionString = GlobalVariables.ConnectionString;// ConfigurationManager.AppSettings["connectionString"];
        connection = new OleDbConnection(connectionString);
        selectQueryString = "SELECT * FROM UserDetail";
        connection.Open();
        da = new OleDbDataAdapter(selectQueryString, connection);
        commandBuilder = new OleDbCommandBuilder(da);
        dataTable = new DataTable();
        da.Fill(dataTable);
        bindingSource = new BindingSource();
        bindingSource.DataSource = dataTable;
        dataGridViewTrial.DataSource = bindingSource;
        // if you want to hide Identity column
        dataGridViewTrial.Columns[0].Visible = false;
    }
    private void addUpadateButton_Click(object sender, EventArgs e)
    {
        try
        {
            da.Update(dataTable);
        }
        catch (Exception exceptionObj)
        {
            MessageBox.Show(exceptionObj.Message.ToString());
        }
    }
    private void deleteButton_Click(object sender, EventArgs e)
    {
        try
        {
            dataGridViewTrial.Rows.RemoveAt(dataGridViewTrial.CurrentRow.Index);
            da.Update(dataTable);
        }
        catch (Exception exceptionObj)
        {
            MessageBox.Show(exceptionObj.Message.ToString());
        }
    }
}

我怎么能添加一个查找列使用上面的代码?谢谢你。

在DataGridView中添加一个ComboBox列

如何在datagridview中生成列?如果列是动态生成的,那么你必须在网格中进行更改,首先手动创建所有列,并在每个DataGridView列的DataPropertyName中分配数据库字段名称。现在,将列类型DataGridViewComboboxColumn分配给UserTypeId列。

在完成上述所有过程之后,您需要在绑定DataGridView之前填充该列。

string _SQL = "Select UserTypeId,UserTypeName From UserTypes Order By UserTypeName";
//Dont include Order By UserTypeName if you have created clustered index on it.
SqlDataAdapter Da = New SqlDataAdapter(_SQL, Connection);
DataTable Dt = New DataTable();
Da.Fill(Dt);
DataGridViewComboBoxColumn colUserTypeId = (DataGridViewComboBoxColumn)DataGridView1.Columns["UserTypeId"];
colUserTypeId.DisplayMember = "UserTypeName";
colUserTypeId.ValueMember = "UserTypeId";
colUserTypeId.DataSource = Dt;
//CODE TO FILL GRIDVIEW

现在,您的网格将填充UserTypeID列中的用户类型。应用程序将从DataGridView1.Rows[RowIndex].Cells["UserTypeID"].Value属性返回UserTypeID

您需要2个BindingSource,一个BindingSource将连接到您的UserDetails表(就像您已经做的那样),而其他的将连接到UserTypes表。

然后你需要用DataGridViewDataGridViewComboBoxColumn附加第二个BindingSource

dataGridViewTrial.DataSource = bindingSource;
// after you are binding your DataGridridView
// assuming that the UserTypeId Column is at 1st index
var colUserTypes = this.dataGridViewTrial.Columns[1];
// by default columns are added as Text columns
// so we are removing the auto added column
this.dataGridViewTrial.Columns.Remove(colUserTypes);
// creating new combobox Column
var cmbColumn = new DataGridViewComboBoxColumn();
cmbColumn.DataPropertyName = "UserTypeId";      // this is the property in UserDetails table
cmbColumn.ValueMember = "UserTypeId";           // this is the property in UserTypes table
cmbColumn.DisplayMember = "UserTypeName";       // again this property is in UserTypes table
cmbColumn.DataSource = userTypesBindingSource;  // this binding source is connected with UserTypes table
this.dataGridViewTrial.Columns.Add(cmbColumn);

通常使用设计模式更容易做到这一点。你可以在谷歌上搜索,我想有很多文章可以做到这一点

我看到这个问题已经有了答案,但是如果我想看到DataGridViewComboBoxColumn中所有UserTypes的列表呢?

我们这样做:

  1. 你需要2个单独的Queries从每个Table。第一个是:
SELECT UserId, -- cellindex 0
    UserTypeID, -- cellindex 1
    UserTypeID.UserTypeName -- cellindex 2
From UserDetails  
    INNER JOIN UserTypes ON UserDetails.UserTypeID = UserTypes.UserTypeId
Order By UserTypeName
-- WHERE Conditions are applied
  • 对于第二个Query,你必须这样做:
  • SELECT 
        UserTypeId,
        UserTypeName
    FROM UserTypes 
    -- WHERE CONDITIONS ARE APPLIED
    
  • 现在,对于下一部分,您将使用此查询来填充combobox项。如下面的代码:
  • // After filling your DataSet with the aforementioned query at part(2) you need to create a column for that
    DataGridViewComboBoxColumn cmbx_clm = new DataGridViewComboBoxColumn();
    cmbx_clm.Name = "UserTypesCmbx";
    cmbx_clm.HeaderText = "User Types";
    // Uncomment the lines below if  you want
    //cmbx_clm.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft;
    //cmbx_clm.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
    // Now Let's bind the cmbx_clm DataSource to the DataSet/DataTable that we named it CreatedDs
    cmbx_clm.DataSource = createdDs.Tables[0]; 
    // Pay attention to the following lines because it gets tricky
    cmbx_clm.ValueMember = "UserTypeId";
    cmbx_clm.DisplayMember = "UserTypeName";
    // Now let's add the Column to the DataGridView
    Users_GRD.Columns.Add(cmbx_clm); // cellindex 3
    // For the beauty of our grid we hide the extra column that we do not need
    Users_GRD.Columns[2].Visible = false;
    // Now in order to show the "UserTypeName" in the combobox and also corresponding to the first query 
    foreach(DataGridViewRow row in Users_GRD.Rows)
    {
       // We add UserTypeID VALUE to the combobox ValueMember
        row.Cells[3].Value = row.Cells[1].Visible
    }