在我的应用程序中，它创建一个表并导入数据，然后将其写入数据库，如以下代码的一部分所示：
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();
}
}
}
之后，如前所述将其保存到数据库。创建之后。在我的代码的另一部分中，它将加载已创建表的comboBox，该表会将“进度”列更新为默认值零。为了更好地理解，这是代码片段：
{
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加载表，但是还可以加载表中存在的其他表。这是发生问题的地方。如果我在该comboBox列表中选择任何其他表，则应用程序崩溃。这是因为将进度列设置为默认值零的代码不存在，正如数据库中其他表所期望的那样。它只会出现在我最初从应用程序创建的表中。我将如何处理该错误，以便如果用户选择的表不是最初在我的应用程序中实际创建的表，那么它可以告诉他们“这是表选择无效”。基本上是这种错误消息。我将如何处理？
最佳答案
正如上面的评论中不止一次提到的，您的代码有很多问题，从Sql Injection到缺少正确名称的验证，都有很多问题。
解决所有这些问题可能会将答案扩展到整篇文章，因此，我只为您提供一种简单的方法，让您仅列出包含最终代码所需的两个字段的表。
使用此代码，您可以仅用有效的表名填充combobox4。
（具有“进度”列和“代码”列的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";
至少要确保所涉及的组合框不可从最终用户那里编辑。
关于c# - 根据无效的表选择生成，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43925360/