System.InvalidOperationException："连接未关闭。连接的当前状态为打开。

Question

基本上我想要的是"cbRoomType"组合框过滤出所选类型的房间(以及根据数据库可用的房间)。文本框。

https://i.stack.imgur.com/Bpnlf.png

起初，当我用fillRoomcombo()方法过滤掉可用的房间时，它工作得很好，然后我决定将它与"房间类型"结合起来。所以我将整个方法的代码移动到cbRoomType组合框的动作中，并稍微修改一下以获得我想要的结果。但是每次我选择的值与我在这里声明的值不同时它就会直接给我这个错误cbRoomType.SelectedIndex = 1;

我检查了，所有的连接都关闭了，但我仍然得到这个错误。

甚至尝试了我在网上找到的修复程序，但仍然无法修复它。

代码如下:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace HotelManagementSystem
{
    public partial class Reservations : Form
    {
        SqlConnection Con = new SqlConnection(@"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=D:LectureProgrammingASMASM2Hotel Management SystemHotelManagementSystemdbdb_hotel.mdf;Integrated Security=True;Connect Timeout=30");
        public Reservations()
        {
            InitializeComponent();
        }
        private void timer1_Tick(object sender, EventArgs e)
        {
            lbDate.Text = DateTime.Now.ToLongTimeString();
        }
        public void Populate()
        {
            Con.Open();
            string MyQuery = "Select * from Reservation_tbl";
            SqlDataAdapter da = new SqlDataAdapter(MyQuery, Con);
            SqlCommandBuilder cbuilder = new SqlCommandBuilder(da);
            var ds = new DataSet();
            da.Fill(ds);
            ReservationGridView.DataSource = ds.Tables[0];
            Con.Close();
        }
        DateTime today;
        private void Reservations_Load(object sender, EventArgs e)
        {
            lbDate.Text = DateTime.Now.ToLongTimeString();
            timer1.Start();
            Populate();
            today = DateIn.Value;
            //fillRoomcombo();
            fillClientcombo();
        }
        public void UpdateRoomState()
        {
            string newstate = "Unavailable";
            Con.Open();
            string myquery = "UPDATE Room_tbl set RoomStatus = '"+ newstate + "' where RoomID = '"+Convert.ToInt32(cbRoomNumber.SelectedValue.ToString()) +"';";
            SqlCommand cmd = new SqlCommand(myquery, Con);
            cmd.ExecuteNonQuery();
            Con.Close();
            //fillRoomcombo();
        }
        public void UpdateRoomState2()
        {
            string newstate2 = "Available";
            int roomid = Convert.ToInt32(ReservationGridView.SelectedRows[0].Cells[2].Value.ToString());
            Con.Open();
            string myquery = "UPDATE Room_tbl set RoomStatus = '" + newstate2 + "' where RoomID = '" + roomid + "';";
            SqlCommand cmd = new SqlCommand(myquery, Con);
            cmd.ExecuteNonQuery();
            Con.Close();
            //fillRoomcombo();
        }
        private void bttnReservationAdd_Click(object sender, EventArgs e)
        {
            if (tbReservationID.Text == "")
            {
                MessageBox.Show("Reservation ID cannot be empty.");
            }
            else
            {
                Con.Open();
                SqlCommand cmd = new SqlCommand("Insert into Reservation_tbl values (" + tbReservationID.Text + ", '" + cbClient.Text + "', '" + cbRoomNumber.Text + "', '"+ cbRoomType.SelectedValue.ToString() + "', '" + DateIn.Value + "', '" + DateOut.Value + "')", Con);
                cmd.ExecuteNonQuery();
                MessageBox.Show("Reservation added successfully.");
                Con.Close();
                UpdateRoomState();
                Populate();
            }
        }
        private void DateIn_ValueChanged(object sender, EventArgs e)
        {
            int res = DateTime.Compare(DateIn.Value, today);
            if (res < 0)
            {
                MessageBox.Show("Invalid check-in day.");
            }
        }
        private void DateOut_ValueChanged(object sender, EventArgs e)
        {
            int res = DateTime.Compare(DateOut.Value, today);
            if (res < 0)
            {
                MessageBox.Show("Invalid check-out day.");
            }
        
        }
        public void fillClientcombo()
        {
            Con.Open();
            SqlCommand cmd = new SqlCommand("select ClientName from Client_tbl", Con);
            SqlDataReader rdr;
            rdr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Columns.Add("ClientName", typeof(string));
            dt.Load(rdr);
            cbClient.ValueMember = "ClientName";
            cbClient.DataSource = dt;
            Con.Close();
        }
        /* public void fillRoomcombo()
        {
            Con.Open();
            string roomstate = "Available";
            SqlCommand cmd = new SqlCommand("select RoomID from Room_tbl where RoomStatus ='" + roomstate + "'", Con);
            SqlDataReader rdr;
            rdr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Columns.Add("RoomID", typeof(int));
            dt.Load(rdr);
            cbRoomNumber.ValueMember = "RoomID";
            cbRoomNumber.DataSource = dt;
            Con.Close();
        }*/
        private void bttnReservationEdit_Click(object sender, EventArgs e)
        {
            if (tbReservationID.Text == "")
            {
                MessageBox.Show("Reservation ID cannot be empty.");
            }
            else
            {
                Con.Open();
                string myquery = "UPDATE Reservation_tbl set Client ='" + cbClient.Text + "', DateIn ='" + DateIn.Value + "', DateOut ='" + DateOut.Value + "' where ResID = '" + tbReservationID.Text + "';";
                SqlCommand cmd = new SqlCommand(myquery, Con);
                cmd.ExecuteNonQuery();
                MessageBox.Show("Reservation edited successfully.");
                Con.Close();
                Populate();
            }
        }
        private void ReservationGridView_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            tbReservationID.Text = ReservationGridView.SelectedRows[0].Cells[0].Value.ToString();
            cbClient.Text = ReservationGridView.SelectedRows[0].Cells[1].Value.ToString();
            cbRoomNumber.Text = ReservationGridView.SelectedRows[0].Cells[2].Value.ToString();
        }
        private void bttnReservationRemove_Click(object sender, EventArgs e)
        {
            if (tbReservationID.Text == "")
            {
                MessageBox.Show("Reservation ID cannot be empty.");
            }
            else
            {
                Con.Open();
                string query = "delete from Reservation_tbl where ResID = " + tbReservationID.Text + "";
                SqlCommand cmd = new SqlCommand(query, Con);
                cmd.ExecuteNonQuery();
                MessageBox.Show("Reservation deleted successfully.");
                Con.Close();
                UpdateRoomState2();
                Populate();
            }
        }
        private void bttnRoomReset_Click(object sender, EventArgs e)
        {
            Populate();
        }
        private void bttnReservationSearch_Click(object sender, EventArgs e)
        {
            Con.Open();
            string MyQuery = "Select * from Reservation_tbl where ResID = '"+tbReservationSearch.Text+"';";
            SqlDataAdapter da = new SqlDataAdapter(MyQuery, Con);
            SqlCommandBuilder cbStaffuilder = new SqlCommandBuilder(da);
            var ds = new DataSet();
            da.Fill(ds);
            ReservationGridView.DataSource = ds.Tables[0];
            Con.Close();
        }

        private void bttnBack_Click(object sender, EventArgs e)
        {
            MainForm mainForm = new MainForm();
            mainForm.Show();
            this.Hide();
        }
        private void cbRoomType_SelectedIndexChanged(object sender, EventArgs e)
        {
            Con.Open();
            string roomstate = "Available";
            cbRoomType.SelectedIndex = 1;
            SqlCommand cmd = new SqlCommand("select RoomID from Room_tbl where RoomType ='" + cbRoomType.SelectedItem.ToString() + "' and RoomStatus ='" + roomstate + "'", Con);
            SqlDataReader rdr;
            rdr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Columns.Add("RoomID", typeof(int));
            dt.Load(rdr);
            cbRoomNumber.ValueMember = "RoomID";
            cbRoomNumber.DataSource = dt;
            Con.Close();
        }
    }
}

Answer 1

您应该使用Using语句进行数据库查询，因为它们可以防止连接未关闭等类型的错误。下面是一个使用语句从数据库获取数据的示例:

public static List<CalendarParts> GetParts_ByDate()
        {
            using (IDbConnection cnn = new SQLiteConnection(DBConnection.Conn()))
            {
                var output = cnn.Query<CalendarParts>("Select p.id,c.Name as Calendar,p.PartName," +
                    "p.MaintenanceDate from CalendarParts_Table p inner join Calendars_Table as c " +
                    "on c.id=p.Calendar order by case when p.MaintenanceDate='' or " +
                    "p.MaintenanceDate is null then 1 else 0 end,date(p.MaintenanceDate)",
                    new DynamicParameters() );
                return output.ToList();
            }
        }