C# WinForms程序报错‘无效对象名F0DEMOTBLCARI’及数据加载问题求助

一、当前错误原因分析 #

1. ComboBox数据绑定冲突：设置DataSource后直接调用Items.Add("101")，导致ComboBox混合了DataTable数据源和手动添加的字符串项，获取SelectedValue时会得到DataRowView对象而非实际的IND字段值，最终生成错误的表名（比如用显示名称DEMO代替IND值，导致F0DEMOTBLCARI不存在）。
2. 表名生成逻辑错误：多处使用comboBox1.Text（显示名称KISAAD）生成表名，而非SelectedValue（实际IND字段值），导致表名与数据库实际表名不匹配。
3. SQL拼接风险：直接拼接用户输入到SQL语句中，存在SQL注入风险，且未处理表名合法性验证。

二、修复后的代码 #

namespace TopluCari
{
    public partial class Form1 : Form
    {
        private readonly string _conStr = "Server=.;Database=12;User Id=sa;Password=v1234;";
        private DataTable _initialDataTable = new DataTable();
        private DataTable _currentDataTable = new DataTable();
        private string _currentTableName = string.Empty;

        public Form1()
        {
            InitializeComponent();
            dataGridView1.CellValueChanged += DataGridView1_CellValueChanged;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            LoadCompanyData();
            // 默认选中最后一项
            if (comboBox1.Items.Count > 0)
            {
                comboBox1.SelectedIndex = comboBox1.Items.Count - 1;
            }
        }

        // 加载公司列表到ComboBox
        private void LoadCompanyData()
        {
            string query = "SELECT IND, KISAAD FROM TBLFIRMA";
            using (SqlConnection connection = new SqlConnection(_conStr))
            {
                connection.Open();
                using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
                {
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    
                    // 添加手动项到DataTable而非直接加Items，避免数据源冲突
                    DataRow newRow = dataTable.NewRow();
                    newRow["IND"] = "101";
                    newRow["KISAAD"] = "101";
                    dataTable.Rows.Add(newRow);

                    comboBox1.DataSource = dataTable;
                    comboBox1.DisplayMember = "KISAAD";
                    comboBox1.ValueMember = "IND";
                }
            }
        }

        // 根据选中的公司加载数据
        private void LoadCompanyAccounts(string indValue)
        {
            // 生成合法表名
            _currentTableName = $"F0{indValue.Trim()}TBLCARI";
            
            // 验证表是否存在，避免无效对象名错误
            if (!IsTableExists(_currentTableName))
            {
                MessageBox.Show($"表 {_currentTableName} 不存在！");
                _currentDataTable.Clear();
                dataGridView1.DataSource = _currentDataTable;
                return;
            }

            string query = $"SELECT IND, FIRMAADI, KOD1, KOD2, KOD3, KOD4, KOD5 FROM {_currentTableName};";
            using (SqlConnection connection = new SqlConnection(_conStr))
            {
                connection.Open();
                using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
                {
                    _initialDataTable.Clear();
                    adapter.Fill(_initialDataTable);
                    _currentDataTable = _initialDataTable.Copy();
                    dataGridView1.DataSource = _currentDataTable;
                }
            }
        }

        // 验证数据库中是否存在指定表
        private bool IsTableExists(string tableName)
        {
            string query = $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName";
            using (SqlConnection connection = new SqlConnection(_conStr))
            {
                connection.Open();
                using (SqlCommand cmd = new SqlCommand(query, connection))
                {
                    cmd.Parameters.AddWithValue("@TableName", tableName);
                    return (int)cmd.ExecuteScalar() > 0;
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(_currentTableName))
            {
                MessageBox.Show("请先选择公司！");
                return;
            }

            string query = $"SELECT IND, FIRMAADI, KOD1, KOD2, KOD3, KOD4, KOD5 FROM {_currentTableName};";
            using (SqlConnection connection = new SqlConnection(_conStr))
            {
                connection.Open();
                using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
                {
                    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                    try
                    {
                        adapter.Update(_currentDataTable);
                        // 更新初始数据，避免撤销时恢复旧数据
                        _initialDataTable = _currentDataTable.Copy();
                        MessageBox.Show("保存成功！");
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show($"保存失败：{ex.Message}");
                    }
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            _currentDataTable.Clear();
            _currentDataTable = _initialDataTable.Copy();
            dataGridView1.DataSource = _currentDataTable;
            MessageBox.Show("已撤销修改！");
        }

        private void DataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex < 0 || e.ColumnIndex < 0) return;
            dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Style.BackColor = Color.Green;
        }

        // 通用搜索方法，根据字段名过滤
        private void SearchByColumn(string columnName, string searchText)
        {
            if (string.IsNullOrEmpty(_currentTableName)) return;

            string query = $"SELECT IND, FIRMAADI, KOD1, KOD2, KOD3, KOD4, KOD5 FROM {_currentTableName}";
            if (!string.IsNullOrEmpty(searchText))
            {
                // 用参数化避免SQL注入（字段名无法参数化，需确保columnName是合法字段）
                query += $" WHERE {columnName} LIKE @SearchText";
            }

            using (SqlConnection connection = new SqlConnection(_conStr))
            {
                connection.Open();
                using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
                {
                    if (!string.IsNullOrEmpty(searchText))
                    {
                        adapter.SelectCommand.Parameters.AddWithValue("@SearchText", $"%{searchText}%");
                    }
                    DataTable filteredDataTable = new DataTable();
                    adapter.Fill(filteredDataTable);
                    dataGridView1.DataSource = filteredDataTable;
                }
            }
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            SearchByColumn("KOD1", textBox1.Text.Trim());
        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {
            SearchByColumn("KOD2", textBox2.Text.Trim());
        }

        private void textBox3_TextChanged(object sender, EventArgs e)
        {
            SearchByColumn("KOD3", textBox3.Text.Trim());
        }

        private void textBox4_TextChanged(object sender, EventArgs e)
        {
            SearchByColumn("KOD4", textBox4.Text.Trim());
        }

        private void textBox5_TextChanged(object sender, EventArgs e)
        {
            SearchByColumn("KOD5", textBox5.Text.Trim());
        }

        private void textBox6_TextChanged(object sender, EventArgs e)
        {
            SearchByColumn("FIRMAADI", textBox6.Text.Trim());
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboBox1.SelectedValue == null) return;
            string indValue = comboBox1.SelectedValue.ToString();
            LoadCompanyAccounts(indValue);
        }
    }
}

三、代码优化建议 #

1. 连接字符串配置：不要硬编码连接字符串，放到App.config中，方便修改：

   <connectionStrings>
       <add name="TopluCariDB" connectionString="Server=.;Database=12;User Id=sa;Password=v1234;" providerName="System.Data.SqlClient"/>
   </connectionStrings>
   

   然后通过ConfigurationManager.ConnectionStrings["TopluCariDB"].ConnectionString获取。

2. 避免SQL注入：虽然表名和字段名无法用参数化，但可以做白名单验证，比如检查字段名是否在允许的列表中（IND, FIRMAADI, KOD1-KOD5），表名通过IsTableExists验证。

3. 异步操作：数据库操作耗时，改用异步方法（OpenAsync、FillAsync）避免UI卡顿，比如：

   private async void LoadCompanyAccounts(string indValue)
   {
       // ...
       await connection.OpenAsync();
       await adapter.FillAsync(_initialDataTable);
       // ...
   }
   

4. 封装数据库操作：把重复的数据库逻辑封装成通用方法，减少代码冗余。

5. 导出Excel功能：可以使用EPPlus或NPOI库实现，示例思路：

   private void ExportToExcel()
   {
       using (SaveFileDialog sfd = new SaveFileDialog())
       {
           sfd.Filter = "Excel文件 (*.xlsx)|*.xlsx";
           if (sfd.ShowDialog() == DialogResult.OK)
           {
               using (var package = new ExcelPackage(new FileInfo(sfd.FileName)))
               {
                   ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("往来账户");
                   // 写入表头
                   for (int i = 0; i < _currentDataTable.Columns.Count; i++)
                   {
                       worksheet.Cells[1, i+1].Value = _currentDataTable.Columns[i].ColumnName;
                   }
                   // 写入数据
                   for (int row = 0; row < _currentDataTable.Rows.Count; row++)
                   {
                       for (int col = 0; col < _currentDataTable.Columns.Count; col++)
                       {
                           worksheet.Cells[row+2, col+1].Value = _currentDataTable.Rows[row][col];
                       }
                   }
                   package.Save();
                   MessageBox.Show("导出成功！");
               }
           }
       }
   }
   

   注意需要安装EPPlus NuGet包。

6. 错误处理：所有数据库操作添加try-catch块，捕获异常并友好提示用户，避免程序崩溃。

7. 命名规范：使用C#命名规范，比如私有字段用下划线开头（_conStr、_initialDataTable），方法名用帕斯卡命名法（LoadCompanyData）。

8. 数据验证：修改DataGridView单元格时，添加数据验证逻辑，确保输入符合要求。

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