如何检查ID是否已存在于MS Access数据库文件中，以及如何使用TextBox中的TextChanged检查它？

Question

我正在处理我的程序的一部分，我正在使用提供的条目 ID 删除条目。

截至目前，我正在删除用户指定的任何条目。这很好用，但是，我想做的是通知用户没有这样的 ID 可以删除。另外，我正在使用文本框 TextChanged，它允许我在用户键入时检查用户输入中的某些内容。

现在，我如何检查条目 ID 是否已存在？为此，我应该在 if 语句中包含什么内容？

另外，有没有办法可以使用 TextChanged 事件处理程序进行检查？我对此不确定，因为我知道如果我在 TextChanged 事件中打开和关闭连接，那么每次用户键入时都会打开/关闭连接，所以我不'我认为这是个好主意。但我怎样才能避免这种情况，以便我可以实时做到这一点？也许当用户停止输入，然后花一两秒钟检查条目 ID 时？

这是我的删除输入窗口的代码：

public partial class DeleteEntryWindow : Form
{
    string user, pass, filePath;

    // Initializing MainWindow form.
    MainWindow mainWindow;

    public DeleteEntryWindow()
    {
        InitializeComponent();

        txtEntryID.TextChanged += new EventHandler(ValidateInput);
    }

    public DeleteEntryWindow(MainWindow viaParameter, 
        string user, string pass, string filePath)
        : this()
    {
        mainWindow = viaParameter;
        this.user = user;
        this.pass = pass;
        this.filePath = filePath;
    }

    private void ValidateInput(object sender, EventArgs e)
    {
        int intNumber;

        if (!string.IsNullOrEmpty(txtEntryID.Text) &&
            int.TryParse(txtEntryID.Text, out intNumber) &&
            intNumber > 0)
        {
            lblMessage.Text = "Entry ID is valid.";
            lblMessage.ForeColor = Color.Green;
            btnDeleteEntry.Enabled = true;
        }
        else
        {
            lblMessage.Text = "You must enter Entry ID number!";
            lblMessage.ForeColor = Color.IndianRed;
            btnDeleteEntry.Enabled = false;
        }
    }

    private void btnDeleteEntry_Click(object sender, EventArgs e)
    {
        DialogResult result = MessageBox.Show
            ("Are you sure you want to remove this entry?",
            "Information", MessageBoxButtons.YesNo,
            MessageBoxIcon.Information);

        if (result == DialogResult.Yes)
        {
            // SQL query which will delete entry by using entry ID.
            string sql = "DELETE FROM PersonalData WHERE DataID = " +
                txtEntryID.Text;

            DeleteData(sql);

            lblMessage.Text = "Entry was deleted!";
            lblMessage.ForeColor = Color.Green;
        }
        else
        {
            // Do nothing.
        }
    }

    private void DeleteData(string sql)
    {
        HashPhrase hash = new HashPhrase();

        string hashShortPass = hash.ShortHash(pass);

        // Creating a connection string. Using placeholders make code
        // easier to understand.
        string connectionString =
            @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0};
              Persist Security Info=False; Jet OLEDB:Database Password={1};";

        using (OleDbConnection connection = new OleDbConnection())
        {
            // Creating command object.
            // Using a string formatting let me to insert data into
            // place holders I have used earlier.
            connection.ConnectionString =
                string.Format(connectionString, filePath, hashShortPass);

            using (OleDbCommand command = new OleDbCommand(sql, connection))
            {
                OleDbParameter prmDataID = new OleDbParameter
                    ("@DataID", txtEntryID.Text);

                command.Parameters.Add(prmDataID);

                try
                {
                    connection.Open();
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error: " + ex.Message);
                }
            }
        }
    }
}

Answer 1

要检查 ID 是否已存在，您需要像删除方法一样使用 SQL。以下内容可能会给您一个起点：

private bool DoesIDExist(string ID)
{
    string filePath = ""; //TODO
    string hashShortPass = ""; //TODO
    DataTable temp = new DataTable();
    bool result = false;
    string connectionString =""; //TODO

    using (OleDbConnection connection = new OleDbConnection(ConnectionString))
    {

            string sql = @"SELECT * FROM PersonalData WHERE DataID = @DataID";


            using (OleDbCommand command = new OleDbCommand(sql, connection))
            {
                command.Parameters.Add(new OleDbParameter("@DataID", ID));

                using (OleDbDataAdapter oda = new OleDbDataAdapter(command))
                {
                    try
                    {
                        oda.Fill(temp);

                        if (temp != null && temp.Rows.Count > 0)
                            result = true; //ID exists

                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Error: " + ex.Message);
                    }
                }
            }
    }
return result;
}