WinForms行程计算程序报错：连接未关闭，当前状态为打开

问题描述 #

我开发了一款用于行程费用计算的Windows Forms应用程序，点击计算按钮执行费用计算时，明明已经尝试关闭所有数据库连接，却还是弹出错误提示：The connection was not closed. The connection's current status is open.。我排查过是否有未关闭的连接，但没找到问题，求帮忙解决。

相关代码 #

public void ltcalculate(string vehicleno, string packagetype, DateTime startdate, DateTime enddate, int startkmreading, int endkmreading)
{
    try
    {
        TimeSpan noofD1 = enddate - startdate;
        double noofD2 = noofD1.TotalDays;
        int noofD3 = Convert.ToInt32(noofD2);

        totalkms = endkmreading - startkmreading;

        conn.Open();

        string serchquery = "select * from Vehicle where Vehicle_NO = '" + vehicleno + "'";
        SqlCommand comm = new SqlCommand(serchquery, conn);
        SqlDataReader dataReader = comm.ExecuteReader();

        if (dataReader.Read())
        {
            double driverrate = Convert.ToDouble(dataReader["Driver_Rate"]);
        }
        else
        {
            MessageBox.Show("Vehicle not a found");
            
        }
        
        conn.Close();
        dataReader.Close();

        conn.Open();

        string searchquearypackage = "select * from Long_Tour_Package where Package_Type = '" + packagetype + "'";
        SqlCommand com = new SqlCommand(searchquearypackage, conn);
        SqlDataReader packdatareder = com.ExecuteReader();

        if (packdatareder.Read())
        {
            double packagerate = Convert.ToDouble(packdatareder["Package_Rate"]);
            double driverovernightrate = Convert.ToDouble(packdatareder["Driver_Overmight_Rate"]);
            double vehiclenightparkrate = Convert.ToDouble(packdatareder["Vehicle_Night_Park_Rate"]);
            int maximumkmlimit = Convert.ToInt32(packdatareder["Max_Km"]);
            int rateperextrakm = Convert.ToInt32(packdatareder["Extra_Km_Rate"]);

            int maxnokmsperpack = Convert.ToInt32(maximumkmlimit * noofD3); //This is the limit per the whole trip, not a a day

            if (totalkms > maxnokmsperpack)
            {
                extrakmfortrip = totalkms - maxnokmsperpack; 
                extrakmfee = Convert.ToDouble(extrakmfortrip * rateperextrakm);  

            }
            else
            {
                extrakmfee = 0;
            }
            int noofnt = noofD3 - 1;
            double toOntfee = (driverovernightrate + vehiclenightparkrate) * noofnt;
            double totpackrate = packagerate * noofD3;

            double totcharge = totpackrate + toOntfee + extrakmfee;

            txtbasehirerate.Text = totpackrate.ToString();
            txtextrakmrate.Text = extrakmfee.ToString();
            txtovernightstayfee.Text = driverovernightrate.ToString();
            txttotalamount.Text = totcharge.ToString();

            MessageBox.Show(noofnt + "\n" + toOntfee + "\n" + extrakmfee + "\n");

        }

       conn.Close();
       packdatareder.Close();
        

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

private void btncalculate_Click(object sender, EventArgs e)
{
    string vehicleno = txtvehicleno.Text;
    string packagetype = txtpackagetype.Text;
    int startkmreading = Convert.ToInt32(txtstartkmreading.Text);
    int endkmreading = Convert.ToInt32(txtendkmreading.Text);
    DateTime startdate = dtpstartdate.Value;
    DateTime enddate = dtpenddate.Value;

    ltcalculate(vehicleno, packagetype, startdate, enddate,startkmreading,endkmreading);
}

问题原因分析 #

1. 资源释放顺序错误：你先关闭了数据库连接conn.Close()，再关闭DataReaderdataReader.Close()，但DataReader依赖于连接存在，这种顺序会导致资源释放异常，且如果中间出现异常，连接和DataReader都无法正常关闭。
2. 全局连接对象风险：使用全局的conn对象多次打开关闭，容易出现连接状态不一致的情况，比如第一次打开后发生异常未关闭，第二次再打开就会触发连接已打开的错误。
3. 未使用自动资源管理：没有用using语句包裹连接、命令和DataReader，无法保证异常情况下资源被正确释放。
4. SQL注入风险：直接拼接用户输入到SQL语句中，不仅存在安全问题，还可能因输入特殊字符导致SQL语法错误。

修复后的代码 #

public void ltcalculate(string vehicleno, string packagetype, DateTime startdate, DateTime enddate, int startkmreading, int endkmreading)
{
    try
    {
        TimeSpan noofD1 = enddate - startdate;
        double noofD2 = noofD1.TotalDays;
        int noofD3 = Convert.ToInt32(noofD2);

        totalkms = endkmreading - startkmreading;

        // 使用using自动管理连接，代码块结束后自动关闭并释放资源
        using (SqlConnection conn = new SqlConnection("你的数据库连接字符串"))
        {
            conn.Open();

            // 参数化查询避免SQL注入
            string searchQuery = "select Driver_Rate from Vehicle where Vehicle_NO = @VehicleNo";
            using (SqlCommand comm = new SqlCommand(searchQuery, conn))
            {
                comm.Parameters.AddWithValue("@VehicleNo", vehicleno);
                using (SqlDataReader dataReader = comm.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        double driverrate = Convert.ToDouble(dataReader["Driver_Rate"]);
                    }
                    else
                    {
                        MessageBox.Show("未找到该车辆");
                    }
                } // DataReader自动关闭
            } // SqlCommand自动释放

            // 第二个查询同样使用using管理资源
            string searchPackageQuery = "select Package_Rate, Driver_Overmight_Rate, Vehicle_Night_Park_Rate, Max_Km, Extra_Km_Rate from Long_Tour_Package where Package_Type = @PackageType";
            using (SqlCommand com = new SqlCommand(searchPackageQuery, conn))
            {
                com.Parameters.AddWithValue("@PackageType", packagetype);
                using (SqlDataReader packdatareder = com.ExecuteReader())
                {
                    if (packdatareder.Read())
                    {
                        double packagerate = Convert.ToDouble(packdatareder["Package_Rate"]);
                        double driverovernightrate = Convert.ToDouble(packdatareder["Driver_Overmight_Rate"]);
                        double vehiclenightparkrate = Convert.ToDouble(packdatareder["Vehicle_Night_Park_Rate"]);
                        int maximumkmlimit = Convert.ToInt32(packdatareder["Max_Km"]);
                        int rateperextrakm = Convert.ToInt32(packdatareder["Extra_Km_Rate"]);

                        int maxnokmsperpack = maximumkmlimit * noofD3;
                        extrakmfee = totalkms > maxnokmsperpack 
                            ? Convert.ToDouble((totalkms - maxnokmsperpack) * rateperextrakm) 
                            : 0;

                        int noofnt = noofD3 - 1;
                        double toOntfee = (driverovernightrate + vehiclenightparkrate) * noofnt;
                        double totpackrate = packagerate * noofD3;
                        double totcharge = totpackrate + toOntfee + extrakmfee;

                        txtbasehirerate.Text = totpackrate.ToString();
                        txtextrakmrate.Text = extrakmfee.ToString();
                        txtovernightstayfee.Text = driverovernightrate.ToString();
                        txttotalamount.Text = totcharge.ToString();

                        MessageBox.Show($"{noofnt}\n{toOntfee}\n{extrakmfee}\n");
                    }
                } // DataReader自动关闭
            } // SqlCommand自动释放
        } // 连接自动关闭

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

private void btncalculate_Click(object sender, EventArgs e)
{
    try
    {
        string vehicleno = txtvehicleno.Text;
        string packagetype = txtpackagetype.Text;
        int startkmreading = Convert.ToInt32(txtstartkmreading.Text);
        int endkmreading = Convert.ToInt32(txtendkmreading.Text);
        DateTime startdate = dtpstartdate.Value;
        DateTime enddate = dtpenddate.Value;

        ltcalculate(vehicleno, packagetype, startdate, enddate, startkmreading, endkmreading);
    }
    catch (FormatException)
    {
        MessageBox.Show("请输入有效的里程数值");
    }
}

关键修复说明 #

• 自动资源管理：用using语句包裹SqlConnection、SqlCommand、SqlDataReader，确保无论是否发生异常，资源都会被自动释放，连接也会被正确关闭。
• 避免全局连接：每次查询创建新的连接对象，ADO.NET的连接池会自动复用连接，不会影响性能，同时避免全局连接的状态冲突问题。
• 参数化查询：替换字符串拼接方式，彻底避免SQL注入风险，同时解决特殊字符导致的SQL语法错误。
• 优化异常处理：在按钮点击事件中增加格式异常捕获，处理用户输入非数字的情况，提升程序健壮性。

内容的提问来源于stack exchange，提问作者sajithwi