加载两个/几个MS Access Table到c# Windows Form中

Question

我在问如何将MS Access表中的两个或多个表连接到c# Windows窗体中。

我得到错误，我们找不到第二个表:

public partial class Form1 : Form
{
    private OleDbConnection connection = new OleDbConnection();
    private OleDbConnection connection2 = new OleDbConnection();
    public Form1()
    {
        connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersbeDocumentsMitarbeiterDaten2.accdb;
        Persist Security Info=False;";
        connection2.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersbeDocumentsDatenbankAbteilung.accdb;
        Persist Security Info=False;";
        InitializeComponent();
    }
private void Form1_Load(object sender, EventArgs e)
    {
        try
        {
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            string query = "select ABTEILUNG from combo";
            command.CommandText = query;
            OleDbDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Abteilung.Items.Add(reader["ABTEILUNG"].ToString());
            }
            connection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error" + ex);
        }
        finally
        {
            connection.Close();
        }

有人有解决方案吗?

Answer 1

您可以重用这些对象，并且可以从各种表或数据库中获取数据，如:

private void Form1_Load(object sender, EventArgs e)
{
        try
        {
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            string query = "select ABTEILUNG from combo";
            command.CommandText = query;
            OleDbDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Abteilung.Items.Add(reader("ABTEILUNG").ToString());
            }
            reader.Close(); //' Always Close ther Reader. Don't left it open
            connection2.Open();
            command.Connection = connection2; //' Reusing Same Command Over New Connection
            command.CommandText = "Select Field2 from Table2";
            while (reader.Read)
            {
                if (!(Convert.IsDBNull(reader("Field2")))) //' Checking If Null Value is there
                {
                    Abteilung.Items.Add(reader("Field2").ToString());
                }
            }
            reader.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error" + ex);
        }
        finally
        {
            connection.Close();
            connection2.Close();
        }
    }

Answer 2

如何使用using块来处理命令和连接，然后使用DataAdapter来轻松完成工作。我使用如下代码:

DataSet ds = new DataSet();
using (OleDbConnection con = new OleDbConnection(MDBConnection.ConnectionString))
{
    con.Open();
    using (OleDbCommand cmd = new OleDbCommand("SELECT Column FROM Table1", con))
    {
        using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
        {
            da.Fill(ds);
        }
    }
    using (OleDbCommand cmd = new OleDbCommand("SELECT AnotherColumn FROM Table2", con))
    {
        using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
        {
            da.Fill(ds);
        }
    }
}
return ds;