c# - 基于无效表选择生成

Question

在我的应用程序中，它创建一个表并导入数据，然后写入数据库，如下面的部分代码所示:

private void button3_Click(object sender, EventArgs e)
{
    if (textBox2.Text.Contains(" "))
    {
        MessageBox.Show("Name cannot be blank or contain spaces!");
    }
    else
    {
        if (string.IsNullOrEmpty(textBox2.Text))
        {
            MessageBox.Show("Name cannot be blank or contain spaces!");
        }
        else
        {
            MessageBox.Show("Currently importing " + textBox2.Text + "...\nA confirmation will be displayed when finished");

            string connectionString = "Data Source=bidb;Initial Catalog=STAGING;Integrated Security=True";
            string query = "CREATE TABLE [dbo].[" + textBox2.Text + "](" + "[Code] [varchar] (13) NOT NULL," +
           "[Description] [varchar] (255) NOT NULL," + "[NDC] [varchar] (255) NULL," +
            "[Supplier Code] [varchar] (38) NULL," + "[Supplier Description] [varchar] (255) NULL, " + "[UOM] [varchar] (8) NULL," + "[Size] [varchar] (8) NULL," + "[Progress][varchar](2) DEFAULT '0')";
}

更新上面的代码:

 private void button1_Click(object sender, EventArgs e)
    {
        if (textBox1.Text.Contains(" "))
        {
            MessageBox.Show("Name cannot be blank or contain spaces!");
        }
        if (string.IsNullOrEmpty(textBox1.Text))
        {
            MessageBox.Show("Name cannot be blank or contain spaces!");
        }
        else
        {
            string connectionString = "Data Source=bidb;Initial Catalog=STAGING;Integrated Security=True";
            string query = "CREATE TABLE [dbo].[" + textBox1.Text.Replace("'", "''") + "](" + "ID int IDENTITY (1,1)," + "[Code] [varchar] (13) NOT NULL," +
           "[Description] [varchar] (255) NOT NULL," + "[NDC] [varchar] (50) NULL," +
            "[Supplier Code] [varchar] (50) NULL," + "[Supplier Description] [varchar] (255) NULL," + "[UOM] [varchar] (8) NULL," + "[Size] [varchar] (8) NULL,)";


            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(query, connection);
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            MessageBox.Show("Table Created in Database successfully!");
            this.Close();

        }
    }
}

之后，如前所述，它会保存到数据库中。创建之后。在我的代码的另一部分中，它加载了已创建表的组合框，该组合框将“进度”列更新为默认值零。为了更好地理解这里是代码片段:

 {

    if (string.IsNullOrEmpty(comboBox4.Text))
    {
        MessageBox.Show("Cannot reset previous value on an empty record,\n please load a table!");
    }
    else
    {
        comboBox2.SelectedIndex -= 1;
        string connectionString2 = "Data Source=bidb;Initial Catalog=STAGING;Integrated Security=True";
        string query2 = "UPDATE dbo.[" + comboBox4.Text + "] SET Progress= '0' where code = '" + comboBox2.Text + "'; ";

填充相应表格的代码:

 private void FillCombo()
    {



        comboBox4.Items.Clear();



        try
        {

            string connectionString = "Data Source=bidb;Initial Catalog=STAGING;Integrated Security=True";
            using (SqlConnection con2 = new SqlConnection(connectionString))
            {
                con2.Open();
                string query = "SELECT * FROM INFORMATION_SCHEMA.TABLES ";
                SqlCommand cmd2 = new SqlCommand(query, con2);

                SqlDataReader dr2 = cmd2.ExecuteReader();
                while (dr2.Read())
                {
                    int col = dr2.GetOrdinal("TABLE_NAME");
                    comboBox4.Items.Add(dr2[col].ToString());
                    //con2.Close();
                }
                // comboBox4.SelectedIndex = 0;

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

    }

好的，很好，但我的问题是，是的，它从 comboBox 加载表，但它也加载表中存在的其他表。这就是问题发生的地方。如果我在该组合框中选择任何其他表，则应用程序会崩溃。这是因为将进度列设置为默认值零的代码并不像数据库中其他表中预期的那样存在。它只会在我最初从我的应用程序创建的表中。我将如何处理此错误，以便如果用户选择的表不是最初在我的应用程序中实际创建的表，那么它可以告诉他们“这是表选择无效”。基本上是那种错误信息。我将如何处理这个问题？

Answer 1

正如上面评论中不止一次所说，您的代码存在很多问题，从 SQL 注入(inject)到缺少正确名称的验证。
解决所有这些问题可能会将这个答案升级为整篇文章，因此我限制自己为您提供一个简单的方法来获得包含最终代码所需的两个字段的表的列表。

使用此代码，您可以仅使用有效的表名填充 combobox4。
(同时具有 Progress 列和 Code 列的 IE 表)

SqlDataAdapter da = new SqlDataAdapter(@"SELECT table_name, count(table_name) 
                                         FROM INFORMATION_SCHEMA.COLUMNS 
                                         WHERE column_name = 'Progress' OR 
                                               column_name = 'Code'
                                         GROUP BY table_name
                                         HAVING count(table_name) > 1",
                        connection);
DataTable dt = new DataTable();
da.Fill(dt);
combobox4.DataSource = dt;
combobox4.DisplayMember = "table_name";

确保至少涉及的组合框不能被最终用户编辑。