主页 / 问题 / 1108035
Accepted
Дмитрий
Asked:2020-04-10 03:10:38 +0000 UTC2020-04-10 03:10:38 +0000 UTC

使用数据库表中的数据填充 Combobox，具体取决于在另一个 Combobox 中选择的值

  • 772

我需要帮助解决一个小问题。我自己已经尝试了很多东西，但没有成功（我正在为课程作业编写程序（为酒店经理）。我有一个带有 room_id 和 room_type 字段的 Rooms 表，我需要所有 id来自具有这种类型的 Rooms 表的房间（例如，Single。我希望我能清楚地解释它。下面是将数据加载到组合框的代码（来自 form_load）。

在此处输入图像描述

// loading data to combobox3
DataTable Rooms = new DataTable();
using (SqlConnection coon = new SqlConnection(conString))
{
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = coon;
    cmd.CommandText = "select room_type from Rooms where reservation_id = 0";
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    adapter.Fill(Rooms);
}

for (int i = 0; i < Rooms.Rows.Count; i++)
{
    comboBox3.Items.Add(Rooms.Rows[i]["room_type"]);
}

// loading data to combobox2
using (SqlConnection coon = new SqlConnection(conString))
{
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = coon;
    cmd.CommandText = "select froom_id from Rooms where reservation_id = 0"
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    adapter.Fill(Rooms);
}

for (int i = 0; i < Rooms.Rows.Count; i++)
{
    comboBox2.Items.Add(Rooms.Rows[i]["froom_id"]);
}

我试图做这样的事情，但是为了更新带有 room_id 的组合框中的数据，您需要更新表单，然后重置带有 room_type 的组合框中的值，我尝试保存它并替换再次加载表单后，它没有任何反应。有人可以建议如何做到这一点吗？

cmd.CommandText = "select froom_id from Rooms where reservation_id = 0 AND room_type = " + "'" + comboBox3.Text + "'";
c#

1 个回答

  1. Best Answer

    工作示例

    首先，房间类型应该存储在一个单独的表中，模式是 数据库模式

    其次，要填充组合框，我们需要这样一个类

    class ComboItem
{
    public int Id { get; set; }
    public string Text { get; set; }
}

    然后表格将如下所示

    public partial class FormMain : Form
{
    private const string _conString = @"Data Source=(localdb)\MSSQLLocalDB;" +
        "Initial Catalog=RoomsDB;Integrated Security=True;" +
        "Connect Timeout=60;Encrypt=False;" +
        "TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

    //источник данных для комбобокса типов комнат
    private BindingSource _bsTypes;
    //источник данных для комбобокса комнат
    private BindingSource _bsNumbers;

    public FormMain()
    {
        InitializeComponent();

        //привязка к комбобоксу выбора тип комнат
        _bsTypes = new BindingSource();
        _bsTypes.DataSource = typeof(ComboItem);
        _comboBoxTypes.DataSource = _bsTypes;
        _comboBoxTypes.DisplayMember = nameof(ComboItem.Text);

        //привязка к комбобоксу выбора номеров комнат
        _bsNumbers = new BindingSource();
        _bsNumbers.DataSource = typeof(ComboItem);
        _comboBoxNumbers.DataSource = _bsNumbers;
        _comboBoxNumbers.DisplayMember = nameof(ComboItem.Text);

        this.Load += FormMain_Load;
    }

    private async void FormMain_Load(object sender, EventArgs e)
    {
        _bsTypes.Add(new ComboItem { Text = "Любой" });
        _bsNumbers.Add(new ComboItem { Text = "Нет выбора" });

        //извлекаем из БД типы комнат, заполняем комбобокс
        await LoadRoomTypesAsync();
        //подписка на событие выбора типа комнаты
        _bsTypes.CurrentChanged += OnRoomTypeSelected;
    }

    /// <summary>
    /// После выбора типа комнат
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private async void OnRoomTypeSelected(object sender, EventArgs e)
    {
        //определяем Id выбранного пункта
        int selectedId = (_bsTypes.Current as ComboItem).Id;
        //очищаем комбобокс
        _bsNumbers.Clear();

        //в случае выбора "Любой"
        if (selectedId == 0)
        {
            _bsNumbers.Add(new ComboItem { Text = "Нет выбора" });
            return;
        }

        await LoadRoomNumbersByTypeIdAsync(selectedId);
    }

    /// <summary>
    /// Заполение комбобкса типов комнат
    /// </summary>
    /// <returns></returns>
    private async Task LoadRoomTypesAsync()
    {
        try
        {
            using (var con = new SqlConnection(_conString))
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandText = @"SELECT Id
                                 , Name
                                FROM RoomTypes
                                ORDER BY Name;";
                await con.OpenAsync();
                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var ci = new ComboItem
                        {
                            Id = reader.GetInt32(0),
                            Text = reader.GetString(1)
                        };
                        _bsTypes.Add(ci);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Ошибка",
                MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

    /// <summary>
    /// Заполнение комбобокса номерами комнат нужного типа
    /// </summary>
    /// <param name="typeId"></param>
    /// <returns></returns>
    private async Task LoadRoomNumbersByTypeIdAsync(int typeId)
    {
        try
        {
            using (var con = new SqlConnection(_conString))
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandText = @"SELECT r.Id, r.Number
                                    FROM dbo.Rooms AS r
                                    INNER JOIN dbo.RoomTypes AS t
                                        ON r.RoomTypeId = t.Id
                                    WHERE ReservationId = 0 AND t.Id = @id
                                    ORDER BY Number;";

                var param = new SqlParameter();
                param.ParameterName = "@id";
                param.SqlDbType = SqlDbType.Int;
                param.Value = typeId;
                cmd.Parameters.Add(param);

                await con.OpenAsync();
                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    //если есть свободные комнаты данного типа
                    if (reader.HasRows)
                    {
                        while (await reader.ReadAsync())
                        {
                            var ci = new ComboItem
                            {
                                Id = reader.GetInt32(0),
                                Text = reader.GetInt32(1).ToString()
                            };
                            _bsNumbers.Add(ci);
                        }
                    }
                    else
                    {
                        //иначе нет свободных комнат
                        _bsNumbers.Add(new ComboItem { Text = "Нет свободных" });
                    }
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Ошибка",
                MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }
}

    好吧，第三，作为一个愿望，使用数据库的代码应该放在一个单独的类中，因为 您需要将可视化界面的工作和存储层的工作分开。所以想想吧。这是为了未来，可以这么说。

    所有代码都在这里。

    • 1

相关问题